一、基础架构
1.Server层:
核心服务功能、所有的内置函数、所有跨存储引擎的功能(存储过程、触发器、试图等)
- 连接器: 负责建立连接、获取权限、维持和管理连接。认证通过后会到权限表查出你的权限，也就意味着建立连接后，管理员对你权限的修改不会影响已存在的连接。
- 查询缓存：
- 建立连接后的执行到这里，缓存的是查询语句和对应的返回值
- 多数情况不建议使用查询缓存，因为查询缓存的失效很频繁，只要有对一个表更新，这个表上的所有查询缓存都会被清空。
- 分析器:
- 词法分析：识别每个字符串代表什么
- 语法分析：根据语法规则，判断是否满足MySql语法
- 判断sql中不存在某个列的错误，也是在这里校验到的
- 优化器：优化语句的执行策略，比如用哪个索引、多表关联时候的连接顺序
- 执行器：判断是否有执行权限后开始执行。（取满足条件的第一行之后循环取后面的行最终返回全部行数据）
2.存储引擎
负责数据的存储和提取。架构模式是插件式的，支持InnoDB、MyISAM、Memory等
二、日志系统
1. 重做日志 redo log
- InnoDB引擎独有的，为补充binlog没有crash-safe的能力而设计的日志系统
- 当一条记录需要更新的时候，innoDB先把记录写到redo log，并更新内存，这个时候更新就算完成了，innoDB会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是系统空闲的时候。优化IO成本和查找成本
- redo log是固定大小的，从头开始写，写到末尾又回到开头循环写。在这个环上一个标记写位置的指针，一个标记当前擦除位置的指针，当快追上的时候就开始移动指针删除数据。
- 有了redo log，就可以保证数据库发生异常重启，之前的记录不会丢失，这个能力称为crash-safe
2. 归档日志 binlog
- 位于server层，所有引擎都可用
3.两个日志的不同点
- server和引擎层
- redo log是物理日志，记录某个数据页做了什么改动；binlog是逻辑日志记录的是原始逻辑（记录sql语句或者修改前后的内容）
- redo log是村换写，空间固定会用完； bin log追加写，文件写到一定大小不会覆盖，而是切换到下一个。
- 数据恢复 时使用bin log日志: 找到最近一次全量备份，然后从备份时间点将备份的bin log依次取出，重放到需要的时刻
三、事务
1. 事物特性 (ACID)： 原子性，一致性，隔离性，持久性
2. 事务隔离级别：
- 读未提交：一个事物可以读到其他事物没有提交的数据
- 读提交：一个事物修改的数据只有提交时后他事物才能读到（每个sql执行前创建的视图实现）
- 可重复读：一个事物内读取的数据不受其他事物对这个数据修改的影响，读到的数据是不变的（事物启动时创建视图实现）此情况未提交的数据其他事物也不可见
- 串行化：我的事物未提交前，其他事物无法修改数据（加锁实现）
mysql默认是可重复读，oracle默认是读提交
3.多事物执行时可能出现的问题
- 脏读：一个事务修改了数据还未提交，另一个事务读取并使用了这个数据。当第一个事务回滚，另一个事务使用的数据就是脏数据(发生在读未提交) （由于其他事务没有提交造成）
- 不可重复读: 一个事务内多次读同一个数据，两次读之间数据被另一个事务修改，导致这两次读到的数据不一致。（由于其他事务提交造成）
- 幻读：事务1对所有数据行执行修改，同时事务2添加了一条数据。事务1发现自己并没有实现全部数据的修改，好像发生了幻觉。(解决: 一个事务完成了处理前，其他事务不可以插入数据) （需要插入）
4. 事务隔离的实现:
每条记录更新时都同时记录一个回滚操作，同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制(MVCC)
5. 为什么尽量不要使用长事务？
长事务意味在系统里存在很老的事务视图，事务提交之前都会保存，导致占用大量存储空间，长事务还占用锁资源，可能拖垮库
6. autocommit”为事务的自动提交，设置为”off”时需要手动，开启和提交事务
begin # 或 start Transaction
update xxxx
commit # rollback
四、索引
1. 数据结构的考虑:
- 哈希表: 类似数组+链表，key的hash值确定在数组(桶)中的位置，出现hash冲突时，拉链。但这种对于范围查询不适用，需要全部扫描，比较适合等值查询
- 有序数组: 查询利用二分查询,时间复杂度是O(log(N)); 也适用范围查询。但插入元素，需要移动后面的全部元素，效率很低。适合静态存储引擎，不会修改数据的场景
- 二叉树:
- 特点是，每个节点的左二子小于这个节点，右二子大于当前节点；这样查询的效率是O(log(N)),为了维持这个查询复杂度，需要保持这棵树是平衡二叉树，所以更新时间复杂度也是O(log(N))。
- 实际索引使用的是多叉树(一个树有多个节点),原因是索引需要操作磁盘，尽量要减少数据块的寻址，让树高小一点。 N叉树的N取决于数据块的大小。
- 跳表（参考）:redis中用于代替平衡树的结构，插入/删除/搜索都是O(log(n))。一种双向链表的发展，多层指向下一节点的指针。
- LSM（参考）:相比下，B+树是随机磁盘访问，比顺序读写要慢。LSM顺序写，牺牲了一些读性能
2. InnoDB索引模型
每一个所以在InnoDB里对应一棵B+树
3. 索引字段问题
- 如图， 主键索引(又叫聚簇索引，叶子节点存的是整行数据)、非主键索引(又叫二级索引，叶子节点存的是主键，所以还需要再查询一次主键索引的树，这个过程叫回标)；我们应尽量使用主键查询。
- 页分裂: 参考上图，新插入数据时，所在的数据页满了，根据B+树的算法需要申请一个新的数据页，然后挪动部分数据过去，这个过程就称为页分裂。
- 页合并: 当相邻的两个页由于删除了数据，利用率很低之后，会将数据也合并，这个过程叫页合并。
- 选择自增id作为主键的优点：
- 性能方面：每次插入数据都是追加操作，不涉及挪动其他记录，也不会出现页分裂和页合并
- 存储空间方面：长度小，对应普通索引的叶子节点就越小，普通索引的空间也就越小
- 自增id的缺点：
- 分库分表
- 两张自增id的表合并会冲突
- 索引定位到page，page内部是一个有序数组，通过二分法查询
4. 索引的选择
- 覆盖索引： 普通索引需要查询的列字段已经在索引的叶子节点上，而不需要通过主键回表，从而提升性能。这种索引叫做覆盖索引
- 覆盖索引场景，比如需要经常根据身份证号查询姓名，那么建立身份号和姓名的联合索引就是有必要的，避免回表提高性能。但由此的索引字段维护代价，需要平衡。
- 最左前缀原则: 联合索引的最左N个字段，也可以是字符串索引的最左M个字符。（ 比如两个字段的联合索引，查询时可以根据第一个字段定位到某一个位置，再向后遍历得到所需结果）
5. 如何安排索引内的字段顺序
- 第一原则是，如果通过调整顺序可以少维护一个索引，那这个顺序就是优先考虑的
- 如果必须维护联合索引和单字段索引，可以考虑占用空间问题
6. 索引下推
like ‘hello%’ and age >10检索，mysql5.6之前，会对匹配的数据进行回表查询，5.6以后，回先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度。
7. 为什么用B+存储索引?(演变过程) 参考
- hash: 查询很快，适合固定值查询。不适合范围查询。
- 有序数组: 查询和范围查询都很快，插入需要移动后面全部元素，效率较低。
- 二叉搜索树: 存在极端情况，出现链表结构，搜索效率较低
- 平衡二叉树: 解决了上面的问题，但对于索引大部分节点存在磁盘，每个节点是非连续的空间，每个节点的寻址操作是影响搜索速度的关键。基于此尽量需要减小树的高度
- B树与B+树: 每个节点多放一些数据，对应数据库读取的最小单位页
- B+树跟B树不同，B+树叶子节点冗余了所有非叶子节点的数据
- B+树每个叶子节点增加了指向相邻的节点的指针
- 优点:
- 非叶子节点不会带上指向记录的指针，这样，一个块中可以容纳更多的索引项，一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点
- 叶子节点间有序的链表，有利于范围查找。
- 遍历效率更高，只要遍历叶子节点就可以。b树需要中序遍历进行扫库
8.哪些方式无法应用到索引
- ①函数
- ②非左匹配 like '%5400%'; like '5400%'是可以的
- ③in , not in
- ④>, <, is null, is not null
- ⑤UNION
五、数据库中的锁
1.全局锁
- 整个库加锁
- mysql提供加锁全局读锁的方法:Flush tables with read lock(FTWRL)这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句会被阻塞
- 使用场景: 适用与非所有的表都使用事务引擎的数据的全库逻辑备份。
- 使用事务引擎的库，可以使用逻辑备份工具mysqldump，使用single-transaction参数，导数据前启动一个事务，来确保一致性视图。
- 如果全库只读为什么不使用set global readonly=true的方式？
- readonly在有些系统被用来其他逻辑，比如判断主备库，所以修改的话影响较大
- 异常机制有差异，FTWRL在发生异常时，会释放全局锁，readonly一直保持
2. 表级锁
- mysql有两种表级锁:一种是表锁；一种是元数据锁(meta data lock, MDL)
- 表锁语法：lock tables..read/write
- MDL:不需要显示使用，访问一个表时字段加上
- 读写锁互斥，写写锁互斥，读读锁不互斥
- MDL会直到事务提交才会释放；所以在做表结构变更时要小心，因为此时会锁住线上读写操作，如果表变更时间较长，线上客户端不断重试，这个库的线程很快会爆满； 所以避免长事务然后表变更时设置超时时间，超时后重试即可。
- 表级锁限制只能当前线程的操作，比如，线程A执行lock t1 read,t2 write ，则其他线程写t1。读写t2都会被阻塞
- 表锁分为：意向共享锁和意向排他锁，他们是一种锁的标志，为了使加表锁效率高，因为有存在任何行锁就不能加表锁，全表扫描有没有锁效率很低；这种意向锁是存在任何锁时，更新这个标志
- 表锁一般在数据库引擎不支持行锁时候才会被用到
3. 行级锁
- 行锁是在由引擎实现的，MyISAM不支持行锁，InnoDB支持行锁
- 两阶段锁：在InnoDB事务中，行锁是在需要的时候加上的，但并不是不需要就释放，而是等到事务结束时才释放
- 针对两阶段，如果你的事务中需要锁多个行，要把最可能造成锁冲突，最可能影响并发度的锁尽量放后面
- 死锁：两个事务在持有对方需要的锁的同时，互相等待对方释放自己需要的资源
- 解决方式： ①设置超时时间 ②发起死锁检测，发现死锁后，主动回滚死锁中的某一事务 -通常用第二种， 配置innodb_deadlock_detect为on
4. 补充
共享锁（行锁）
- 又称为读锁，S锁，多个是事务对与同一数据共享一把锁，都只能访问到数据，只能读不能修改
- 加锁释放锁方式:
select * from student where id =1 LOCK IN SHARE MODE
commit/rollback
排他锁(行锁)
- 又称为写锁，X锁，不能与其他锁并存，如一个事务获取了一个数据行的 排他锁，其他事务不能获取该行的锁(共享锁，排他锁)，只有获取了该排他锁的事务可以对数据行读和修改
- 加锁释放锁方式L:
自动:
delete / update / insert 默认加X锁
手动:select * from student where id=1 FOR UPDATE commit / rollback
乐观锁与悲观锁 参考
- 悲观锁: 假设一定会发生冲突，所以每次拿数据之前都要加锁 (select for update 锁定当前行)
- 乐观锁: 每次拿数据都认为别人不会修改，只在提交时检查数据是不是已经被别人更新过了，乐观锁适用于读多写少的场景，这样可以提高吞吐量。(cas方式,字段值没有被其他线程修改，则更新操作)
- 版本字段控制: 增加一个数字类型的字段’version’,每次更新加1。 CAS方式实现
- 使用时间戳，CAS，比较时间
悲观锁和乐观锁是用法，而不是划分的锁类型
锁住了什么，行数据 / 列？
- 没有用到索引时，排他锁，直接锁表，为什么？
- 没有创建索引时，存在一个默认索引，扫描索引就所住了整张表
- 锁住的是索引
- 记录锁锁住的是该记录，where id = 4, 锁住的是id=4那一条记录(唯一性索引，等值查询，精确匹配)
- 间隙锁(只在可重复读隔离级别中)，左开右开区间，比如1，4，7，10 。(-,1),(1,4),(4,7)… 对于等值查询没有命中和范围查询，锁住对应的范围；间隙锁间不互斥
- 临键锁，左开右闭区间
