大纲
一.InnoDB的内存结构和更新机制
二.InnoDB的存储模型
三.并发事务原理
四.索引原理和索引优化
深入了解其内存模型、存储模型、事务和索引原理、锁机制原理等。
一.InnoDB的内存模型
1.SQL的执行流程
2.InnoDB的内存模型
3.Buffer Pool中的空闲缓存页与free链表
4.Buffer Pool中的脏页和flush链表
5.Buffer Pool通过LRU链表来淘汰缓存页
6.Buffer Pool的缓存页以及几个链表总结
7.LRU链表的冷数据区域的缓存页何时刷盘
8.增大Buffer Pool来提升MySQL的并发能力
1.SQL的执行流程
MySQL驱动 -> 数据库连接池 -> 网络IO线程 -> SQL接口 -> 查询解析器 -> 查询优化器 -> 执行器 -> 调用存储引擎接口
SQL的执行流程：
一.由MySQL工作线程去监听网络请求和读取网络连接的SQL数据
二.MySQL工作线程读取出SQL语句后会将SQL语句转交给SQL接口去执行
三.通过查询解析器Parser解析SQL语句让MySQL能看得懂SQL逻辑
四.通过查询优化器Optimizer选择最优的查询路径
五.由执行器按照查询优化器选择的执行计划不停调用存储引擎接口
六.存储引擎接口会查询内存缓存数据、查询磁盘数据、更新磁盘数据等
2.InnoDB的内存模型
Buffer Pool就是数据库的一个内存组件，里面缓存了磁盘上的真实数据。当执行更新时，会写undo日志、修改Buffer Pool数据、写redo日志。当提交事务时，会将redo日志刷磁、binlog刷盘、添加commit标记。最后后台IO线程会随机把Buffer Pool里的脏数据刷入到磁盘数据文件中。
(1)缓冲池Buffer Pool的默认大小是128MB
缓冲池Buffer Pool的大小根据服务器的配置来调整。比如服务器的配置是16核32GB，可以给缓冲池Buffer Pool分配20GB内存。
(2)数据页是MySQL抽象出来的数据单位
磁盘文件中有很多数据页，每一页中放了很多行数据。如果数据库要更新某一行数据，首先会找到这行数据所在的数据页，然后把这行数据页加载到缓冲池Buffer Pool中。缓冲池Buffer Pool中存放的一个一个的数据页，也被称为缓存页。数据页默认大小为16KB，数据页和缓存页的大小是一样的。
(3)Buffer Pool中每个缓存页都有对应的描述数据
描述数据包括：缓存页所属的表空间、数据页的编号、缓存页在Buffer Pool中的地址等。每个缓存页的描述数据放Buffer Pool最前面，各个缓存页放在后面。Buffer Pool里的一个描述数据大小相当于一个缓存页的5%，约800字节。
3.Buffer Pool中的空闲缓存页与free链表
数据库的Buffer Pool里会包含很多个缓存页，同时每个缓存页还有对应的描述数据。
数据库启动时，会按照设置的Buffer Pool大小，去操作系统申请一块内存区域，作为Buffer Pool的内存区域。申请完毕后，数据库会按照默认的缓存页大小及对应的描述数据大小，在Buffer Pool中划分一个个缓存页和对应的描述数据。
然后当数据库把Buffer Pool划分完毕后，里面的缓存页都是空的。需要等数据库运行起来后执行增删改查操作时：才会把对应的数据页从磁盘里读取出来，放入Buffer Pool中的缓存页里。
(1)数据库启动时会按照设置的Buffer Pool大小向OS申请内存
当数据库向OS申请到设置的Buffer Pool大小的内存后，就会在缓冲池中划分出一个个空闲缓存页和相应的描述数据块。
(2)Buffer Pool有一个叫free链表的双向链表
free链表的每个节点是一个空闲缓存页的描述数据块的地址，通过free链表可知哪些缓存页是空闲的。
(3)根据free链表的节点可得到一个空闲缓存页
从free链表中获取一个节点后，根据该节点就能找到对应的空闲缓存页。接着就可以将磁盘中的数据页读取到该空闲缓存页里。同时把该数据页的描述数据写到该空闲缓存页对应的描述数据块里。以及把表空间号 + 数据页号作为key，缓存页地址作为value，写到哈希表。这样下次读取该数据页时可通过key查哈希表，直接从缓冲池里进行读取。
(4)增删改查一条数据时InnoDB引擎会怎么处理
首先InnoDB会获取到对应数据的"表空间号 + 数据页号"。然后根据"表空间号 + 数据页号"作为key，去哈希表中进行查询。如果能查到缓存页地址，则去Buffer Pool中读取对应的缓存页数据。如果哈希表查不到，则说明要将磁盘的数据页读取到缓冲区的缓存页里。
于是会先从free链表里获取一个节点，然后找到其描述数据块的地址。通过该地址可得到一个空闲缓存页，就能把数据页读取到该空闲缓存页里。同时会把描述数据也写到该缓存页的描述数据块里，以及把表空间号 + 数据页号作为key，缓存页地址作为value，写到哈希表。
4.Buffer Pool中的脏页和flush链表
MySQL在执行增删改语句时，如果在哈希表中发现数据页没有缓存，则会基于free链表找到一个空闲的缓存页，然后将数据页读取到缓存页里。如果在哈希表中发现数据页已缓存，那么会直接使用缓存页。
因此，无论如何，要更新的数据页都会在Buffer Pool的缓存页里。MySQL是基于Buffer Pool内存来执行具体的增删改查操作的。
所以，当MySQL去更新Buffer Pool的缓存页中的数据时，一旦更新完，则缓存页里的数据和磁盘上数据页的数据就不一致了。这时就说该缓存页是脏数据，或者脏页。
在Buffer Pool里，有些缓存页经过修改是脏页，有些则只有查而不是脏页。所以为了方便数据库从缓存页中区分出脏页，数据库引入了一个跟free链表类似的flush链表。
凡是被修改过的缓存页，都会把它的描述数据块加入到flush链表中。flush的意思就是这些都是脏页，后续都是要flush刷新到磁盘上去的。
(1)通过free链表来管理所有空闲的数据页
加载磁盘的数据页时，先通过free链表拿到空闲的缓存页地址，然后再把磁盘的数据页写到这个Buffer Pool中的缓存页里。
(2)通过哈希表来管理在Buffer Pool缓存的数据页
根据哈希表可快速从Buffer Pool查出缓存的数据页。
(3)通过flush链表来管理更新后等待被刷盘的缓存页
free链表和flush链表都通过使用地址指针来大大减少内存的占用。free链表和flush链表的节点都是由缓存页的描述数据块来实现的。free链表和flush链表都通过两个指针来构成双向链表。
5.Buffer Pool通过LRU链表来淘汰缓存页
要知道哪些缓存页经常被访问、哪些缓存页很少被访问，可借助LRU链表。LRU就是Least Recently Used，最近最少使用的意思。
(1)简单LRU链表的工作原理
假设InnoDB从磁盘加载一个数据页到缓存页时，就把这个缓存页的描述数据块放到LRU链表头部去。
那么只要一个缓存页有数据，那么该缓存页就会在LRU里。并且最新加载数据的缓存页，会被放到LRU链表的头部。
假设某个缓存页的描述数据块本来在LRU链表的尾部，后面只要查询或者修改了这个缓存页的数据，也会把其描述数据块挪动到LRU链表头部。
总之，就是保证最近被访问过的缓存页，一定在LRU链表的头部。这样当缓冲区没有空闲的缓存页时，可以在LRU链表尾部找一个缓存页。而这个缓存页就是最近最少被访问的那个缓存页。然后把LRU链表尾部的那个缓存页刷入磁盘从而腾出一个空闲的缓存页，最后把需要的磁盘数据页加载到这个空闲的缓存页中即可。
这个LRU链表需要一定长度，不能只有2个节点。否则如果先是节点1被访问100次，接着到节点2被访问。这样虽然链表尾部是节点1，但实际上节点1是最近最少被访问的。
(2)简单LRU链表可能存在的问题
问题一：预读机制导致相邻数据页也一块被加载到缓冲池。此时在LRU链表中排前面的，可能都是通过预读机制加载进来的。
问题二：全表扫描可能会一下子把一个表的所有数据页都加载到缓冲池，此时在LRU链表中排前面的，可能都是通过全表扫描加载进来的。
触发预读机制的情况：
情况一：参数innodb_read_ahead_threshold的默认值是56。如果顺序访问一个区里多个数据页，访问的数据页的数量超过此阈值。那么就会触发预读机制，将下一个相邻区中所有数据页加载到缓冲池。
情况二：如果缓冲池中缓存了一个区里的13个连续的被频繁访问的数据页，那么就会触发预读机制，将这个区里其他数据页也加载到缓冲池。这种情况由参数innodb_random_read_ahead控制，默认关闭。
(3)基于冷热数据分离思想设计LRU链表
这套冷热数据分离的机制包含三个方案：
方案一：缓存页分冷热数据加载
方案二：冷数据转化为热数据进行时间限制
方案三：淘汰缓存页时优先淘汰冷数据区域
为解决简单LRU链表带来的预读和全表扫描问题，InnoDB设计LRU链表时用了冷热数据分离的思想。InnoDB的LRU链表，会被拆分为两个部分。一部分是热数据，一部分是冷数据。冷热数据的比例由innodb_old_blocks_pct参数控制，默认是37。
当数据页第一次被加载到内存时，缓存页对应的描述数据块节点会被放在LRU链表的冷数据区域的头部。被加载到内存的数据页，如果在默认1s后继续被访问，则该缓存页对应的描述数据块节点会被挪动到热数据区域的链表头部。对应的innodb_old_blocks_time参数默认就是设置为1s。
如果数据加载到缓存页之后过了1s+的时间，该缓存页被访问，则对应的描述数据块会被放入热数据区域的链表头部。如果数据加载到缓存页之后在1s内，该缓存页被访问，则对应的描述数据块不会被放入热数据区域。
如果访问了热数据区域中的一个缓存页，是否应该马上把它移动到热数据区域的链表头部？由于热数据区域里的缓存页可能是被经常访问的，所以不建议频繁移动，否则影响性能。
因此LRU链表的热数据区域的访问规则是：只有在热数据区域的后3/4部分的缓存页被访问了，才会移动到链表头部。如果是热数据区域的前面1/4部分的缓存页被访问了，那么不需要移动。这样尽可能减少链表中的节点频繁移动。
6.Buffer Pool的缓存页以及几个链表总结
Buffer Pool在被使用时，会频繁从磁盘上加载数据页到缓存页里。然后free链表、flush链表、LRU链表都会被同时使用，这三个链表都是双向链表。
(1)当加载一个数据页到一个缓存页时
InnoDB就会从free链表里移除这个缓存页。然后会把这个缓存页放入到LRU链表的冷数据区域头部。
(2)当修改一个缓存页时
InnoDB就会在flush链表中记录这个脏页。而且可能会把该缓存页从LRU链表的冷数据区域移动到热数据区域头部。
(3)当查询一个缓存页时
InnoDB可能会把该缓存页从LRU链表冷数据区域移动到热数据区域头部，或者从LRU链表的热数据区域其他位置移动到热数据区域头部。
总之，MySQL在执行增删改查时：首先会大量操作缓存页以及对应的几个链表。然后当缓存页满时，会基于LRU链表淘汰缓存页。也就是先把要淘汰的缓存页刷入磁盘，然后清空该缓存页。接着再把需要的数据页加载到空闲的缓存页中。
7.LRU链表的冷数据区域的缓存页何时刷盘
时机一：定时把LRU尾部的部分缓存页刷入磁盘
第一个时机并不是在缓存页满的时候，才会将缓存页刷入磁盘。而是有一个后台定时任务线程，该线程会定时把LRU链表的冷数据区域尾部的一些缓存页刷入磁盘。然后清空几个缓存页，并将这些缓存页加回free链表。
时机二：把flush链表中的一些缓存页定时刷入磁盘
如果仅仅是把LRU链表中冷数据区域的缓存页刷入磁盘，还是不够的。因为在LRU链表的热数据区域里很多缓存页可能也会被频繁的修改，这些缓存页不可能永远都不刷入磁盘中。
所以这个后台线程同时也会在MySQL不怎么繁忙时，找个时间把flush链表中的缓存页都刷入磁盘中。
只要flush链表中的缓存页被刷入磁盘，则这些缓存页也会从flush链表和LRU链表中移除，然后加入到free链表中。
时机三：实在没有空闲缓存页时
假设所有的free链表都被使用，同时flush链表中有很多被修改过的缓存页，以及LRU链表中也有很多缓存页进行冷热数据分离。此时如果要从磁盘加载数据页到一个空闲缓存页中，就会从LRU链表的冷数据区域尾部找到一个缓存页，刷入磁盘和清空。
8.增大Buffer Pool来提升MySQL的并发能力
当Buffer Pool用完时，此时需要先把一个缓存页刷入磁盘腾出空闲缓存页，再从磁盘读取数据页。这种情况要执行两次磁盘IO，性能低下。一次是缓存页刷入磁盘，一次是从磁盘读取数据页加载到缓存页。
由于InnoDB在使用缓存页的过程中，会有一个后台线程定时地把LRU链表冷数据区域的一些缓存页刷入磁盘。所以缓存页是一边被使用，一边被后台线程定时地释放。如果缓存页被使用得很快，而后台线程释放缓存页的速度很慢，那么必然频繁出现缓存页被使用完的情况。
从InnoDB角度看，它无法控制缓存页被使用的速度。因为缓存页被使用的速度依赖于外部服务调用的并发程度。另外InnoDB的后台线程会定时释放一批缓存页，这个过程也很难优化。因为如果频繁释放也会造成磁盘IO频繁，从而影响性能。
所以最后可以依靠InnoDB的Buffer Pool的大小来避免。如果MySQL要抗高并发的访问，那么机器必然要配置很大的内存空间，起码是32G+、64GB、128GB。此时就可以设置Buffer Pool很大的内存空间，如20GB、48GB，80GB。
这样在高并发场景下：虽然Buffer Pool的缓存页被频繁使用，但后台线程也在定时释放缓存页。由于Buffer Pool内存很大，所以可能需要较长时间才会导致缓存页用完。需要的时间越长，那么就越可以撑到数据库访问高峰期已过去。只要高峰一过，后台线程又不停地基于flush链表和LRU链表释放缓存页，那么空闲的缓存页数量又会慢慢多起来。
MySQL的生产经验，就是给MySQL设置多个Buffer Pool来优化并发能力。如果Buffer Pool的内存小于1GB，MySQL默认只会给一个Buffer Pool。如果Buffer Pool的内存较大如8G，那么可给MySQL设置多个Buffer Pool。每个Buffer Pool负责管理一部分缓存页和描述数据块，每个Buffer Pool拥有独立的free、flush、LRU链表。这时即便多个线程并发来访问也可以把压力分开，比如有的线程访问这个Buffer Pool，有的线程访问另外的Buffer Pool。
通过多个Buffer Pool，MySQL多线程并发访问的性能就会得到提升，多个线程可以在不同的Buffer Pool中加锁和执行自己的操作。
二.InnoDB的存储模型
1.InnoDB的存储模型以及对应的读写机制
2.提交事务时会将redo日志写入磁盘中
3.MySQL的redo log和binlog对比
4.提交事务时同时也会写入binlog
5.在redo日志中写入commit标记的意义
6.后台IO线程随机将内存更新后的脏数据刷回磁盘
7.InnoDB的执行流程总结
8.redo日志和redo log机制的作用
9.redo日志会写入日志文件里的Redo Log Blcok
10.Redo Log Buffer和Redo Log文件
11.redo日志从Redo Log Buffer中刷盘时机
12.undo log回滚日志原理
13.系统和数据库能抗多少QPS
14.性能压测指标和命令
15.简单总结增删改SQL语句的实现原理
1.InnoDB的存储模型以及对应的读写机制
在逻辑层面上，InnoDB的数据是插入一个一个的表中。在物理层面上，InnoDB的数据是插入一个一个的表空间。
表空间对应着磁盘文件，磁盘文件里存放的就是数据。磁盘文件存放数据时，会被拆分为一个一个的数据区组。每个数据区组包含256个数据区，每个数据区包含64个数据页。每个数据页包含一行行的数据。
数据页又包含了：文件头、数据页头、最小记录和最大记录、多个数据行、空闲空间、数据页目录、文件尾部。
每个数据行又附加了真实数据外的很多信息：变长字段的长度列表、null值列表、数据头、真实数据和隐藏字段。
通过数据页、数据区、数据行附加的特殊信息，可以让InnoDB在磁盘文件里实现B+索引、事务等复杂的机制。
当数据库执行增删改查时：必须把磁盘文件里的一个数据页加载到内存Buffer Pool中的缓存页里，然后增删改查都针对缓存页里的数据进行。
所以要读写数据时：会根据表找到一个表空间，通过表空间就可以找到对应的磁盘文件。通过磁盘文件就可以从里面找一个数据区组中的一个数据区。然后从该数据区中找一个数据页出来。最后就可以把这个数据页从磁盘加载到Buffer Pool缓存页里。
当执行更新时，会写undo日志、修改Buffer Pool数据、写redo日志。
当提交事务时，会将redo日志刷磁、binlog刷盘、添加commit标记。最后后台IO线程会随机把Buffer Pool里的脏数据刷入到磁盘数据文件中。
2.提交事务时会将redo日志写入磁盘中
如果InnoDB想要提交一个事务，就会根据一定的策略把redo日志从Redo Log Buffer中刷入到磁盘文件里，这个策略是通过如下这个参数来配置的：innodb_flush_log_at_trx_commit。
(1)当innodb_flush_log_at_trx_commit = 0时
那么进行事务提交时，不会把Redo Log Buffer的数据刷入到磁盘文件里。这时即便提交了事务，但如果MySQL宕机了，内存里的数据也会全部丢失而且redo日志里没有数据。
(2)当innodb_flush_log_at_trx_commit = 1时
那么进行事务提交时，会把内存中的redo log刷入到磁盘文件里。只要事务提交成功，那么redo log就必然在磁盘里。哪怕此时Buffer Pool中更新过的数据还没刷新到磁盘，系统崩溃重启后，也可以根据磁盘中的redo log恢复。
(3)当innodb_flush_log_at_trx_commit = 2时
那么进行事务提交时，会把内存中的redo log写入到OS Cache缓存里。OS Cache缓存里的数据可能在1秒后才会被写入到磁盘文件中。
在这种模式下，当InnoDB存储引擎提交事务后，redo log可能还停留在OS Cache缓存里，还没实际进入到磁盘文件。而此时MySQL所在机器宕机了，那么OS Cache里的redo log也会丢失。从而出现即便提交了事务，但是数据还是丢失了的情况。
3.MySQL的redo log和binlog对比
MySQL的redo log，是一种偏向物理性的重做日志。因为其记录的是：对哪个数据页中的哪条记录做了什么修改。而且redo log是属于InnoDB存储引擎特有的日志文件。
MySQL的binlog，是一种偏向于逻辑性的日志，也叫归档日志。类似"对users表中id=1的一行记录做了更新操作，更新后的值是什么"。binlog不是InnoDB存储引擎特有的日志文件，binlog是属于MySQL数据库层面的日志文件。
4.提交事务时同时也会写入binlog
提交事务时，除了会把redo日志写入到磁盘文件中，还会把这次SQL更新对应的binlog日志写入到磁盘文件中。执行器这个组件，它会负责和InnoDB存储引擎进行如下交互：
步骤1：从磁盘加载数据到Buffer Pool缓存
步骤2：写入undo日志
步骤3：更新Buffer Pool里的数据
步骤4：写入redo日志到Redo Log Buffer
步骤5：redo日志刷入磁盘
步骤6：写入binlog日志
步骤7：在redo日志中写入commit标记
其中步骤1、2、3、4是执行更新语句的阶段，而步骤5、6、7是属于提交事务的阶段。
当MySQL把binlog写入磁盘后，接着就会完成最终的事务提交。此时会把本次更新对应的binlog文件名称和位置，都写入到redo日志里，同时在redo日志文件里写入一个commit标记。在完成这个事情后，才算是最终完成事务的提交。
5.在redo日志中写入commit标记的意义
写入commit标记是用来保持redo日志与binlog日志一致。也就是说，在提交事务的时候，上述的步骤5、6、7必须都执行完毕，才算是提交了事务。
(1)如果刚完成步骤5时，redo日志刚刷入到磁盘文件，MySQL宕机了
这时因为在redo日志没有最终的事务commit标记，所以此次事务不成功。因为不允许出现这样的情况：redo日志文件里有更新日志，但是binlog日志文件里没有对应的更新日志。否则就会导致数据不一致。
(2)如果在完成步骤6时，binlog日志已写入磁盘，MySQL宕机了
这时因为在redo日志没有最终的事务commit标记，所以此次事务也失败。所以必须要在redo日志写入最终的事务commit标记，才算事务提交成功。
这样redo日志有本次更新的日志，binlog日志也有本次更新的日志，从而实现redo日志和binlog日志完全一致。
6.后台IO线程随机将内存更新后的脏数据刷回磁盘
当完成事务提交后，MySQL已把内存中的Buffer Pool缓存数据更新了，同时磁盘里也有redo日志和binlog日志，但磁盘上的数据文件还是旧值。
这时MySQL会有一个后台IO线程，在事务提交后的某个时间，随机把内存Buffer Pool中修改后的脏数据刷回到磁盘上的数据文件里。
当IO线程把Buffer Pool里修改后的脏数据刷回磁盘后，磁盘上的数据才会跟内存里的数据一样，都是修改后的值。
当IO线程把脏数据刷回磁盘之前，即便MySQL宕机也没关系。因为重启后会根据redo日志恢复提交事务时所做的修改到内存里。之后IO线程还是会把修改后的数据刷到磁盘的数据文件里。
7.InnoDB的执行流程总结
InnoDB存储引擎会使用Buffer Pool、Redo Log Buffer来缓存数据。InnoDB存储引擎有属于自己的undo日志文件、redo日志文件，MySQL也有属于自己的binlog日志文件。
执行更新时：会修改Buffer Pool里的数据、写undo日志、写Redo Log Buffer。
提交事务时：会把binlog刷入磁盘、在redo日志中写入事务标记，把redo日志刷入磁盘。最后InnoDB后台的IO线程会随机把Buffer Pool的脏数据刷入到磁盘文件。
(1)MySQL宕机重启如何确定是否需要从redo日志恢复数据
MySQL宕机重启，如何确定脏数据在宕机前是否已全部刷写回磁盘文件。
MySQL宕机重启，InnoDB会首先去查看数据页中LSN的数值。LSN就是InnoDB使用的一个版本标记的计数。如果数据页中的LSN异于redo日志的commit标记，那么就去查看redo日志的LSN大小。如果数据页的LSN值大，则说明数据页领先redo日志，不需要恢复，反之则需要从redo日志中恢复。
(2)从redo日志恢复数据时是全量恢复还是指定位置后恢复
redo日志是划归于一个redo日志组的。默认一个redo日志组有两个redo日志文件。写redo日志时是循环写入，写满一个redo日志文件再写另外一个。
在写满切换redo日志文件时，会触发数据库的检查点checkpoint。checkpoint所做的事就是把脏页刷新回磁盘。
当DB重启恢复时只需要恢复checkpoint之后的数据即可。所以redo日志文件大小不宜过大，不然导致恢复时需要更长的时间。redo日志文件大小也不宜过小，不然导致频繁切换触发检测点降低性能。
(3)既然有redo日志来保证崩溃恢复，为什么还要有binlog日志
binlog日志其实就是归档日志，主要用来做数据恢复的。MySQL最开始设计时只有MyISAM引擎只有binlog，不支持InnoDB。此外数据库备份以及hadoop系统数据分析都是binlog来实现的，所以还需要binlog。
(4)redo日志和binlog日志的数据结构是怎样的
redo日志是循环写，会把redo日志分为0,1,2,3四个区间，有两个指针。writepos指针是一边写一边向后移动，checkpoint指针是一边擦除一边向后移动。所以redo日志是不能保存很多记录的，必须持久化到磁盘中。binlog日志是追加写，不会覆盖之前的日志。
(5)binlog日志和redo日志是怎么保持一致性的
binlog日志和redo日志是通过两阶段提交来保持一致性的。否则如果数据库系统发生crash，则通过redo日志恢复的数据库和通过binlog日志恢复出来的临时库不一致。
8.redo日志和redo log机制的作用
(1)redo log保证事务提交后修改的数据不丢失
更新完缓存页后，必须要写一条redo log，这样才能记录对数据库的修改。
redo log可以保证事务提交后：如果事务中由增删改SQL更新的缓存页还没刷新到磁盘时MySQL宕机，那么MySQL重启后，就可以把redo log重做一遍，恢复事务在当时更新的缓存页，然后再把缓存页刷新到磁盘。所以redo log的本质是保证事务提交后，修改的数据绝对不会丢失。
(2)redo log出现的步骤分析
步骤一：MySQL在执行增删改SQL语句时，都是针对一个表中的某些数据执行的。此时首先会找到这个表对应的表空间，然后找到表空间对应的磁盘文件。接着从磁盘文件里把需要更新的数据所在的数据页从磁盘读取出来，也就是将磁盘上的数据页放到Buffer Pool中的缓存页里。
步骤二：读取磁盘文件的数据页到Buffer Pool的缓存页后，MySQL就会根据增删改SQL语句对缓存页执行更新操作。
步骤三：在MySQL更新缓存页时，会更新free链表、会更新flush链表、会更新LRU链表。然后后台有专门的IO线程，不定时根据flush链表、LRU链表，把更新过的缓存页(脏页)刷新回磁盘文件的数据页里。
但这种机制有个漏洞：万一事务里有增删改SQL语句更新了缓存页，事务提交了但还没来得及让IO线程把缓存页刷新到磁盘而MySQL宕机。这时Buffer Pool内存里的数据就会丢失，刚做完的事务更新数据也丢失。但也不可能每次提交一个事务，就把事务更新的缓存页刷新回磁盘文件。因为将缓存页刷新到磁盘文件里，是对磁盘随机写的，性能很差。这会导致数据库的性能和并发能力都很弱，因此才引入了这个redo log机制。
步骤四：提交事务时把MySQL对缓存页的修改以日志形式写入redo log日志文件。这种redo log日志的格式大致为：对表空间XX中的数据页XX中的偏移量为XXXX的地方更新了数据XXX。
只要事务提交时将所做修改以日志形式写入redo log，则宕机也没关系。因为重启后可以根据redo log，在Buffer Pool里恢复宕机前的事务更新。
(3)修改过的缓存页刷盘和redo log刷盘的差别
事务提交时把修改过的缓存页刷入磁盘，和事务提交时把所做修改的redo log写入日志文件的差别：
一.如果把修改过的缓存页都刷入磁盘
由于一个缓存页是16K，数据还是比较大的，将其刷入磁盘会比较耗时，且修改的缓存页可能仅有几字节的改动，把完整缓存页刷入磁盘不划算。
二.将缓存页刷入磁盘时是对磁盘进行随机写
这时由于一个缓存页对应的位置可能在磁盘文件的一个随机位置，比如偏移量为45336的地方，所以只能进行性能很差的磁盘随机写。
三.如果是将redo log写入日志文件
由于一行redo log只占几十字节，所以写入磁盘日志文件的速度会很快。其中redo log只包含表空间号、数据页号、磁盘文件偏移量、更新值。此外将redo log写入日志时是对磁盘进行顺序写，速度也很快。其中每次进行顺序写时都是直接追加到磁盘文件尾部的。所以提交事务时，使用数据量少 + 顺序写的redo log记录所做的修改，性能会远超直接刷新缓存页到磁盘，这可以让数据库的并发能力更强。
9.redo日志会写入日志文件里的Redo Log Blcok
redo log日志本质上记录的是：对某个表空间的某个数据页的某个偏移量的地方修改了几个字节的值。
所以redo日志需要记录的就是：表空间号 + 数据页号 + 偏移量 + 修改几个字节的值 + 具体的值。
MySQL内有一个数据结构，叫做Redo Log Blcok。redo日志是用一个Redo Log Blcok来存放多个单行日志的。
一个Redo Log Block是512字节，分为3个部分：一是12字节的Header，二是496字节的Body，三是4字节的Trailer。
往一个文件里写数据，可认为是从第一行开始从左往右写，会有很多行。假设现在要写第一条redo日志：首先会把该日志数据放到内存中的一个叫Redo Log Block的数据结构里，然后不断往这个Redo Log Block的数据结构添加一条条redo日志，直到内存里的这个Redo Log Block满了，已经达到512字节。当一个Redo Log Block满时，再一次性把它写入到磁盘文件。
10.Redo Log Buffer和Redo Log文件
已知MySQL执行完增删改的SQL语句后：会先让redo日志进入Redo Log Block，然后再写入磁盘的redo日志文件。
Redo Log Buffer是MySQL启动时向操作系统申请的一块连续内存空间。Buffer Pool也是MySQL启动时向操作系统申请的一块连续内存空间。Buffer Pool会在申请内存后划分很多空的缓存页和一些链表结构。Redo Log Buffer也会在申请内存后，划分很多空的Redo Log Block。
innodb_log_buffer_size可配置Redo Log Buffer的大小，默认是16MB。其实16MB已经够大了，毕竟一个Redo Log Block才512字节，每条redo日志也就几个到几十个字节而已。
从Redo Log Buffer结构可知：当要写一条redo日志时，就会从第一个Redo Log Block开始写入。写满了一个Redo Log Block，就会继续写下一个Redo Log Block。以此类推，直到所有Redo Log Block写满。当Redo Log Buffer里的所有Redo Log Block都被写满后，就会强制把Redo Log Block刷入到磁盘中。
当一个Redo Log Block满512字节后，也会被追加到redo日志文件里。然后在磁盘文件里不停地追加一个又一个的Redo Log Block。
此外，MySQL平时执行一个事务的过程中，每个事务都会有多个增删改操作，这样就会有多条redo日志。这多条redo日志就是一组Redo Log Group，每次一组Redo Log Group都先在别的地方暂存，执行完后再把一组redo日志写到Redo Log Block里。
如果一组Redo Log Group中的redo日志太多，那么就可能会将其存放在两个Redo Log Block中。如果一组Redo Log Group比较小，那么也可能多个Redo Log Group是在一个Redo Log Block里。
11.redo日志从Redo Log Buffer中刷盘时机
(1)Redo Log Block什么时候刷盘
时机一：Redo Log Buffer已使用过半时
如果Redo Log Buffer的日志已占据Redo Log Buffer总容量16M的一半，即超过了8MB的redo日志在缓冲里，此时就会把它们刷入磁盘文件中。
时机二：事务被提交时
一个事务提交时，要把其redo日志所在的Redo Log Block刷入磁盘文件。这样它修改的数据才不会丢失，随时可通过redo日志恢复事务所做修改。
(innodb_flush_log_at_trx_commit的值为1)
时机三：后台线程定时刷新
有个后台线程会每秒把Redo Log Buffer的Redo Log Block刷到磁盘文件。
时机四：关闭MySQL时
当关闭MySQL时，Redo Log Buffer的Redo Log Block都会刷入到磁盘里。
(2)redo log日志刷盘的场景
场景一：MySQL瞬间执行了大量高并发SQL，1秒就产生了超过8MB的redo日志。此时这些redo日志占据了Redo Log Buffer的一半空间，于是就会刷盘。
这种redo日志刷盘，在MySQL承受高并发请求时是比较常见的。比如每秒执行上万个增删改SQL，每个SQL的redo日志有几百个字节。此时是可能1s生成超8MB的redo日志的，从而触发刷新redo日志到磁盘。但是这种高并发请求的情况一般不常见。
场景二：正常情况执行一个事务，一般会在几十毫秒到几百毫秒间执行完毕。通常MySQL单事务性能一般不会超过1秒，否则就太慢了。所以执行完一个事务，也会马上把这个事务的redo日志刷入磁盘。这种情况则比较常见，当一个短事务提交时往往会发生redo日志刷盘。
场景三：后台线程每秒自动刷新redo日志到磁盘去。
总而言之：一个事务执行时，事务对应的redo日志都进入到Redo Log Buffer。一个事务提交时，事务对应的redo日志刷入磁盘文件才算事务提交成功。这样才能确保事务提交后，数据不会丢，只要有redo日志在磁盘里就行。
(3)磁盘上到底有几个redo日志文件
大量的redo日志是否都放在一个文件里，磁盘空间是否会越占越多？默认情况下，redo log都会写入一个目录中的文件里。这个目录可通过show variables like 'datadir'来查看，可通过innodb_log_group_home_dir参数来进行设置。
redo日志文件是有多个的，写满了一个就会写下一个redo日志文件。可以限制redo日志文件的数量。通过innodb_log_file_size可指定每个redo日文件的大小，默认48MB。通过innodb_log_files_in_group可指定redo日志文件的数量，默认2个。
所以，默认情况下，目录里就两个日志文件，分别为ib_logfile0和ib_logfile1，每个48MB。先写满第一个再进行写满第二个，一个写满了交替覆盖式去写另外一个。
因此，MySQL最多只保留最近的96MB的redo日志而已。事实上这已足够多了，毕竟一条redo log通常就几个字节到几十个字节，96MB已足够存储上百万条redo log了。
12.undo log回滚日志原理
(1)redo log应对的场景—事务提交数据丢失
已知对Buffer Pool里的缓存页执行增删改操作时，必须要写对应的redo log日志记录下要做的哪些修改。redo log日志都会先进入Redo Log Buffer中的一个Redo Log Blcok，然后事务提交时会将Redo Log Block刷入到磁盘的redo日志文件里。
万一事务已提交，而事务修改的缓存页还没刷入磁盘上的数据页文件。此时MySQL宕机，那么Buffer Pool里被事务修改过的数据就全部丢失。
但只要有redo log，MySQL重启后又可以把那些还没刷入磁盘的缓存页它们所对应的redo log都加载出来，在Buffer Pool的缓存页里重做一遍，这样就可以保证事务提交之后，修改的数据绝对不会丢。
(2)undo log应对的场景—进行事务回滚
假设现在在一个事务里要执行一些增删改操作：那么需要先把对应的数据页从磁盘加载出来放到Buffer Pool的缓存页，然后在缓存页进行增删改，同时记录redo log。
万一这个事务里的增删改操作执行了一半时，需要进行事务回滚。比如一个事务里有4个增删改操作，结果已经执行了2个增删改SQL。已经更新了一些Buffer Pool的数据，但还有2个增删改SQL还没执行。此时如果要回滚事务，就必须对已经在Buffer Pool缓存页里执行过的增删改SQL操作进行回滚。所以才必须引入另外一种日志，就是undo log回滚日志。
(3)undo log回滚日志的作用
执行事务时，很多insert、update和delete语句都在更新缓存页的数据。如果事务回滚，需要根据每条SQL对应的undo log回滚日志恢复数据。
13.系统和数据库能抗多少QPS
4核8G的机器部署普通的Java系统，每秒能抗下几百的请求，从每秒一两百请求到每秒七八百请求都有可能。
8核16G的机器部署的MySQL数据库，每秒能抗一两千请求。16核32G的机器部署的MySQL数据库，每秒能抗两到四千的请求。
14.性能压测指标和命令
IO相关的压测性能指标—IOPS、吞吐量、latency
压测时要关注的其他性能指标—CPU、网络、内存
(1)IOPS
IOPS指机器的随机IO并发处理能力。比如机器可以达200的IOPS，表示每秒可以执行200个随机IO读写请求。
InnoDB更新内存中的脏数据时，最后会由后台IO线程在不确定的时间刷回到磁盘，这就涉及随机IO。如果IOPS指标太低，那么会导致内存里的脏数据刷回磁盘的效率不高。
(2)吞吐量
吞吐量指的是机器的磁盘存储每秒可以读写多少字节的数据量。MySQL在执行各种SQL语句、提交事务时，会大量写redo、binlog日志，这些日志都会写到磁盘文件。所以一台机器的磁盘存储每秒可以读写多少字节的数据量，就决定了它每秒可以把多少日志写入到磁盘。
一般写redo日志，都会对磁盘文件进行顺序写入，也就是一行接着一行的写，而不会进行随机读写。普通磁盘的顺序写入的吞吐量每秒都可以达到200MB左右。通常而言，机器的磁盘吞吐量都是足够承载高并发请求的。
(3)latency
latency指的是往磁盘里写入一条数据的延迟。MySQL在执行一条SQL语句和提交事务时，都需要顺序写一条redo日志和一条binlog日志到磁盘文件。所以写一条日志到磁盘文件里，到底是延迟1ms还是延迟100us，很影响SQL语句的执行性能。
一般来说，磁盘读写延迟越低，数据库的性能就越高，执行每个SQL语句和事务的时候速度就会越快。
(4)相关命令
CPU负载：top
内存负载：top下的Mem
磁盘IO吞吐量：dstat -d (每秒上百MB)
磁盘IOPS：dstat -r (每秒两三百)
网卡流量：dstat -n (千兆网卡每秒100MB左右)
15.简单总结增删改SQL语句的实现原理
其中会涉及MySQL的Buffer Pool机制，redo log机制和undo log机制。MySQL执行增删改时，首先从磁盘加载数据页到Buffer Pool的缓存页。然后更新缓存页，同时会记录undo log回滚日志和redo log重做日志。
三.并发事务原理
1.并发执行多个MySQL事务可能遇到的问题
2.多个事务并发更新或查询时可能出现的问题
3.SQL标准中对事务的4个隔离级别
4.uodo log多版本链介绍
5.基于undo log多版本链实现ReadView机制
6.RC隔离级别如何基于ReadView机制实现
7.RR隔离级别如何基于ReadView机制实现
8.多事务并发运行的隔离机制总结
9.多事务更新同一行数据如何加锁避免脏写
10.数据库出现不确定的性能抖动的原因
11.如何优化数据库不确定性的性能抖动
1.并发执行多个MySQL事务可能遇到的问题
问题一：多个事务并发执行时，可能会同时对缓存页里的同一行数据进行更新。这里并发更新同一行数据的冲突如何处理，是否通过加锁进行处理。
问题二：多个事务并发执行时，可能有的事务在进行更新，有的事务在进行查询。这里并发更新和查询同一行数据的冲突应该如何处理。
要解决这些问题，就涉及同时写和同时读写的并发冲突处理机制。其中就包括了MySQL事务的隔离级别、MVCC多版本隔离、锁机制等。
2.多个事务并发更新或查询时可能出现的问题
(1)脏写
脏写的定义就是事务B修改还没提交的事务A修改过的数据。因为事务A随时会回滚，所以会导致事务B修改的值也没了。
(2)脏读
脏读的定义就是事务B查询还没提交的事务A修改过的数据。因为事务A随时会回滚，可能导致事务B再次查询就读不到之前的数据。也就是导致事务B在前后时间点查询同一行数据时出现脏读。
(3)不可重复读(不可重复读到同一个值)
所谓不可重复读，就是事务A多次查询一条数据，每次读到的值不一样。这个过程中可能别的事务会修改这条数据，且修改后这些事务也提交了。在避免脏读的前提下，也导致事务A每次查询到的值都不一样。
所谓脏写，就是事务A和B没提交的情况下，都修改同一条数据。结果其中事务A回滚了，把另外一个事务B修改的值也给撤销了。所以脏写就是事务A和事务B在没提交的情况下修改同一个值。
所谓脏读，就是事务A修改了一条数据的值，结果还没提交。另外一个事务B就读到了事务A修改的值，然后事务A却又回滚了。那么事务B再次读该数据时就读不到刚才读到那个要修改的值了。所以脏读就是事务B读到了事务A修改某条数据后还没提交的值。
(4)幻读
所谓幻读，指的是一个事务用一样的SQL多次查询一批数据，结果每次查询都会发现查到了一些之前没看到过的数据。
3.SQL标准中对事务的4个隔离级别
SQL标准的事务隔离级别，并不是MySQL的事务隔离级别。MySQL在具体实现事务隔离级别时会有点差别。SQL标准规定了4种事务隔离级别。规定多个事务并发运行时互相是如何隔离的，从而避免事务并发问题。
这4中级别包括：Read Uncommitted(读未提交)、Read Committed(读已提交)、Repeatable Read(可重复读)、Serializable(串行化)。不同的隔离级别可以避免不同的事务并发问题。
(1)Read Uncommitted隔离级别—读未提交
不允许发生脏写，可以去读一些事务里未提交的数据。这种隔离级别下，两个事务不能改未提交。但是可能发生脏读、不可重复读、幻读。一般来说，不会把事务隔离级别设置为读未提交。
(2)Read Committed隔离级别—读已提交
不会发生脏写和脏读，只能读事务中已提交的数据。这种隔离级别下，是无法读取事务在没提交情况下修改的值。但是可能发生不可重复读、幻读，该隔离级别的简称是RC。把事务隔离级别设置成RC指的就是设置读已提交级别。
(3)Repeatable Read隔离级别—可重复读
对同一行数据，在事务中随时可重复读出同样的值。这种隔离级别下，不会发生脏写、脏读和不可重复读，但可能发生幻读。该隔离级别简称RR，把事务隔离级别设置成RR指的是设置可重复读。RR隔离级别，只保证对同一行数据的多次查询不会被读到不一样的值。
(4)Serializable隔离级别—串行化执行
不允许多个事务并发执行。这种隔离级别下，多个事务只能串行起来执行。所以不会出现脏写、脏读、不可重复读、幻读的问题。一般来说，也不会把事务隔离级别设置为串行化级别。
(5)MySQL是如何支持4种事务隔离级别的
SQL标准下的4种事务隔离级别，平时用的比较多的是RC和RR两种级别。在MySQL中也支持这4中事务隔离级别。MySQL默认的事务隔离级别是RR级别，且MySQL的RR级别可避免幻读。SQL标准里的RR级别是会发生幻读的，但MySQL的RR级别避免了幻读。所以MySQL事务默认不会发生脏写、脏读、不可重复读和幻读的问题。
MySQL事务的执行都是并行的，各个事务互相不影响。事务A执行中出现事务B，事务A不会读到事务B未提交的修改值。即使事务B提交了修改值事务A也不会读到。即使事务B提交了插入的一行值事务A也依然不会读到。
MySQL为了实现这种事务之间互不影响的效果，使用的是MVCC机制—多版本并发控制隔离机制。依托MVCC机制，MySQL就可以让RR级别避免不可重复读和幻读的问题。MySQL的默认事务隔离级别是RR，一般不需修改。
4.uodo log多版本链介绍
MySQL默认的RR隔离级别，不会出现脏写、脏读、不可重复读、幻读。每个事务执行时，跟别的事务是没有关系的。不管别的事务怎么更新和插入，查到的值都是不变、都是一致的。而这就是由经典的MVCC多版本并发控制机制实现的。
MySQL每条数据都有两个隐藏字段：一个是trx_id，一个是roll_pointer。trx_id就是最近一次更新这条数据的事务id。roll_pointer就是指向更新这个事务之前生成的undo log。
多个事务串行执行时：每个事务的修改，都会更新隐藏字段trx_id和roll_pointer。同时多个数据快照对应的undo log，会通过roll_pointer指针串联起来，从而形成一个重要的undo log版本链。
5.基于undo log多版本链实现ReadView机制
(1)ReadView的关键内容
MySQL执行一个事务时会生成一个ReadView，里面关键的内容有4个：
一.m_ids，表示此时的活跃事务
二.min_trx_id，表示m_ids里最小的值
三.max_trx_id，表示此时的最大事务ID
四.creator_trx_id，表示当前这个事务自己的id
(2)基于undo log多版本链实现ReadView机制
通过undo log多版本链条，加上事务开启时创建的一个ReadView。当有查询时，事务就能根据ReadVIew机制判断应读取哪个版本的数据，这个ReadVIew机制可以确保一个事务只能读到：它自己开启前其他事务进行更新并已经提交的值，以及它自己更新的值。
假如事务开启前，有其他的事务已经正在运行，那么当事务开启后，其他事务更新了值并已提交，这时该事务读取不到那些事务更新的值。
假如事务开启后，才有其他事务开启并更新了值以及进行了提交，那么这时该事务也是读取不到那些事务更新的值。
通过这个ReadView机制就可以实现多个事务并发执行时的数据隔离。
6.RC隔离级别如何基于ReadView机制实现
(1)RC隔离级别与ReadView机制
一.RC隔离级别就是读已提交的隔离级别
指的是一个事务在运行期间，只要别的事务修改数据并且提交了。那么这个事务就可以读取到别的事务修改的数据，所以RC隔离是会发生不可重复读、幻读的问题。
二.ReadView机制是基于undo log版本链条实现的一套读视图机制
指的是事务开启时生成一个ReadVIew：如果值是事务本身更新的，是可以读取到的。如果值是在事务生成ReadView之前提交的事务修改的，也可以读取。如果值是生成ReadView后再开启事务修改并提交的，则是读取不到的。
三.基于ReadView机制来实现RC隔离级别的核心
设置RC隔离级别的一个事务，每次发起查询都重新生成一个ReadView。
(2)基于ReadView机制实现RC隔离级别
实现RC隔离级别的关键点在于事务每次查询时都生成新的ReadView。如果一个事务在这次查询之前，有其他事务修改了数据而且还提交了。那么其生成ReadView的m_ids列表，当然就不包含这个已提交的事务。既然不包含已提交的事务，那么就可以读取到已提交事务修改过的值，这就是基于ReadView实现的RC隔离级别的原理。
7.RR隔离级别如何基于ReadView机制实现
(1)MySQL的RR隔离级别避免不可重复读 + 幻读
MySQL的RR级别下，一个事务读一条数据，无论读多少次都是一个值。其他事务修改数据后哪怕提交了，该事务也无法看到其他事务修改的值。同时如果其他事务插入了一些新的数据，该事务也是读取不到。这样就分别避免了出现不可重复读的问题，以及避免了出现幻读的问题。
(2)基于ReadView机制实现避免不可重复读
在RR隔离级别下，ReadView一旦生成了就不会改变了。事务A多次读同一个数据，每次读到的都是一样的值。除非是它自己修改了值，否则读到的都是一样的值。不管别的事务如何修改数据，事务A的ReadView始终是不变的。所以事务A基于这个ReadView，可以读取到的值始终是一样的。
基于undo log多版本链条 + ReadView机制实现的RC + RR隔离级别，就是数据库MVCC多版本并发控制机制。
8.多事务并发运行的隔离机制总结
(1)多个事务并发运行读写同一数据时的问题
一.脏写：事务A和B更新同一数据，事务A回滚时把事务B的更新也回滚了。
二.脏读：事务A先读事务B没提交时修改的数据，然后事务B回滚后再读就读不到。
三.不可重复读：事务A多次读同一数据，其他事务修改数据并提交，于是读到不同的值。
四.幻读：每次范围查询查到的数据不同，其他事务插入新值，就会读到更多数据。
(2)事务隔离级别：RU、RC、RR和串行化
一.RU隔离级别：可以读到其他事务未提交的修改数据，只能避免脏写。
二.RC隔离级别：可以读到其他事务已提交的修改数据，可以避免脏写和脏读。
三.RR隔离级别：不会读到其他事务已提交的修改数据，可以避免脏写、脏读和不可重复读。
四.串行隔离级别：指的是让事务都串行执行，可以避免所有问题。
(3)MySQL的MVCC机制
MySQL中多事务并发运行的隔离原理，就是MVCC机制——multi version concurrent control，专门控制多个事务并发运行时，互相之间会如何影响。
基于undo log多版本链条 + ReadView机制，可实现MySQL的MVCC。MySQL默认的RR隔离级别，就是基于这套机制来实现的。除了避免脏写、脏读、不可重复读，还能避免幻读问题。因此一般来说，MySQL使用默认的RR隔离级别即可。
9.多事务更新同一行数据如何加锁避免脏写
依靠锁机制让多个事务更新一行数据时串行化，避免同时更新一行数据。
(1)独占锁让多事务对同一行数据写写操作互斥
多个事务同时更新一行数据时，每个事务都会加锁，然后都会排队等待。必须等事务执行完毕提交了并释放了锁，才能唤醒其他事务继续执行。这个过程中，多个事务更新时所加的锁，就是独占锁——X锁。
(2)MVCC机制避免对同一行数据读写操作加锁
此外当有事务更新数据时，其他的事务是可以读取该数据的。但读取该数据的事务是不用加锁的，只有更新该数据的事务才需要加锁。默认情况会开启MVCC机制，让事务更新数据时其他事务能读取该数据。所以一行数据的读和写两个操作，默认不会加锁互斥。
MySQL通过MVCC机制来实现避免频繁加锁互斥。一个事务读取数据时，完全可以根据该事务创建的ReadView内容，去undo log版本链找一个能读取的版本，不用考虑其他事务的并发修改。
(3)共享锁让多事务对同一行数据读写操作互斥
如果希望事务在执行查询操作的时候也加锁，那么MySQL也可以支持。MySQL支持一种共享锁——S锁，只需要在一个查询语句后面加上lock in share mode，就表示查询时需要对一行数据加共享锁。
需要注意的是，共享锁和独占锁是互斥的。也就是说，如果有事务正在更新一行数据，已经加了独占锁，就不能对查询这行数据的事务也加共享锁。同样道理，如果有事务对一行数据先加了共享锁，其他事务也不能来更新加独占锁。
需要注意的是，共享锁和共享锁是不会互斥的。如果一个事务给一行数据加了共享锁，其他事务也可以对该行数据继续加共享锁。
(4)查询时加独占锁
MySQL的查询操作还可以加互斥锁，语法是在语句后面加上for update。这样查询时就会加上独占锁，直到事务提交后，其他事务才能更新数据。
(5)总结
更新数据时必然加独占锁，独占锁和独占锁是互斥的，此时其他事务不能更新。进行查询时默认是不加锁的，会通过MVCC机制读快照版本，但查询可以手动加共享锁和独占锁。
共享锁和独占锁是互斥的，但共享锁和共享锁不互斥。查询时手动加共享锁：select * from table lock in share mode。查询时手动加独占锁：select * from table for update。
10.数据库出现不确定的性能抖动的原因
(1)数据库出现周期性抖动的问题
线上数据库时不时莫名其妙的来一次性能抖动，而且造成性能抖动的不是数据库锂电池充放电的问题。
(2)数据库执行更新语句的流程分析
数据库执行更新语句时，都是先从磁盘上加载数据页到内存的缓存页里。然后会更新缓存页，同时写对应的redo log日志到Redo Log Buffer中。
既然更新了Buffer Pool里的缓存页，缓存页就会变成脏页。因为此时缓存页里的数据和磁盘文件里的数据页的数据不一样。对于脏页，需要有一个合适的时机把数据刷入到磁盘里，数据库会维护一个LRU链表和一个flush链表来实现。
如果加载磁盘文件的数据页到Buffer Pool时发现并没有空闲的缓存页，此时就必须把部分脏页刷入到磁盘文件里。于是MySQL会根据LRU链表寻找最近最少被访问的缓存页刷入磁盘，当然MySQL在不那么繁忙时也会从flush链表将一部分脏页刷入磁盘。
(3)性能抖动的可能情况一
要执行的一个查询语句需要查询大量数据页并加载到缓存页里，此时就可能导致内存里大量的脏页需要淘汰出去然后刷入磁盘，这样才能腾出足够的内存空间来执行这条查询语句。
在这种情况下，可能就会出现数据库在执行某个查询语句时性能抖动。平时只有几十毫秒的查询，这次需要几秒。就是因为要等待大量脏页刷入到磁盘，才能加载查询出的大量数据页，然后SQL语句才能执行，所以才会导致耗时突增。
(4)性能抖动可能的情况二
执行大量更新写满所有redo日志文件，且还不能覆盖第一个日志文件。因为往redo日志文件写入redo log太快了，都追上checkpoint检查点了。此时只能让脏页刷盘，让一些redo log失效来腾出redo日志文件的空间。
一.redo log刷盘的时机
Redo Log Buffer里的数据超过容量的一半 + 提交事务。这两种情况都会强制将Redo Log Buffer里的Redo Log Block刷入磁盘上的redo日志文件中。
二.脏页刷盘的时机
所有redo日志文件都被写满时，会触发一次脏页的刷盘。磁盘上会有多个redo日志文件，这些redo日志文件会循环不停地写入。如果所有redo日志文件都写满了，此时会回到第一个redo日志文件写入。
三.所有redo日志文件写满时要判断第一个redo日志文件能否被覆盖
如果第一个redo日志文件里靠前的一些redo日志，所对应Buffer Pool缓存页的数据，还没有被刷新到磁盘文件的数据页中。一旦把这个redo日志文件里的redo日志进行覆盖，此时数据库却崩溃了，那么被覆盖的redo日志和它对应的被更新过的缓存页数据就彻底丢失了。
所以当所有redo日志文件写满，需要从第一个redo日志文件开始写时，就会判断第一个日志文件里靠前的redo log对应的缓存页是否已刷盘。如果是，则要把要被覆盖的redo log对应的缓存页马上刷入磁盘。
四.写满redo log的所有日志文件时发现不能覆盖第一个redo日志文件
此时就需要把第一个redo日志文件里靠前的一些redo log，所对应Buffer Pool中没被刷入磁盘的缓存页(脏页)，都刷入到磁盘。从而导致数据库无法处理任何更新请求，因为更新请求需要写redo log。而此时还在等待脏页被刷新到磁盘，才能有可以覆盖的redo日志文件。之后才能执行更新语句，才能把新的redo log写入第一个redo日志文件。
五.如果某时刻MySQL在执行大量的更新语句
那么可能会发现数据库的很多更新语句突然短时间内性能抖动了，可能很多几毫秒就执行完的更新语句，这时却要等1s才能执行完毕。
其中的原因大概率就是，所有redo日志文件写满了。必须要等第一个redo日志文件里部分redo log对应的脏页都刷入磁盘，才能继续执行更新语句，让其redo日志能覆盖到第一个redo日志文件中，从而导致此时执行的更新语句性能很差。
(5)线上数据库性能抖动原因总结
导致数据库的更新语句突然出现性能抖动，很可能是以下两种情况。
情况一：要执行的一个查询语句需要查询大量数据页并加载到缓存页里，由于没有足够的空闲缓存页，需要等大量脏页刷盘才能继续加载数据页。
情况二：执行大量更新语句导致所有redo日志文件写满且还不能覆盖第一个文件，要等第一个文件里部分redo log对应的脏页刷盘才能继续执行更新语句。
11.如何优化数据库不确定性的性能抖动
(1)查询和更新时出现性能抖动的可能原因
上面分析了有时在数据库执行查询或者更新语句时，可能SQL语句性能会出现不正常的莫名奇妙的抖动，可能平时只需要几十毫秒执行完成的却居然需要几秒钟才能完成。这种莫名奇妙的性能抖动，在分析过底层原理后，根本原因就两个。
原因一：执行查询时Buffer Pool的缓存页满了
当执行一个需要查询很多数据的SQL时，需要把很多缓存页刷入磁盘。由于脏页刷磁盘太慢了，于是就会导致查询语句执行得很慢。因为要等很多缓存页都刷盘，才能把查询需要的数据页加载到缓存页中。
原因二：执行更新时磁盘上的所有redo日志满了
此时需要回到第一个redo log日志文件尝试进行覆盖写，这又涉及第一个redo log日志文件里很多redo log对应的缓存页还没刷盘。所以此时就必须把那些缓存页刷入到磁盘，才能执行后续的更新语句，于是就会导致执行的更新语句很慢了。
(2)如何优化参数减少脏页刷盘带来的性能抖动
优化一：尽量减少脏页刷盘的频率
给数据库采用大内存机器，给Buffer Pool分配更大的内存空间。那么也只能让缓存页被填满的速度低一些，降低出现这种情况的频率。
优化二：尽量提升脏页刷盘的速度
假设现在要执行一个SQL查询语句，此时要等待刷入一批缓存页到磁盘，接着才能加载数据到缓存页。
如果把那批缓存页刷入磁盘需要1s，然后查询语句执行的时间是200ms，此时这条SQL执行完毕的总时间就需要1.2s。如果把那批缓存页刷入到磁盘的时间优化到100ms，然后再加上200ms，这条SQL执行完毕的总时间只要300ms，性能提升了。
所以关键点在于，尽可能将缓存页刷入到磁盘的时间开销减到最小。
优化三：避免从一次性从MySQL中查询大量的数据或者产生大量的读请求，导致缓存页不够。同时控制MySQL的写请求并发数，不要产生那么多脏页，尤其是不要让redo日志都满了而必须刷脏页。而对应的运维措施就是增大Buffer Pool或者增大redo日志文件的大小和数量。
(3)如何减少脏页刷盘的时间
一.采用SSD固态硬盘而不要使用机械硬盘
因为SSD固态硬盘最强大的地方，就是它的随机IO性能非常高。而把缓存页刷入到磁盘，就是典型的随机IO，需要在磁盘上找到各个缓存页所在的随机位置，把数据写入到磁盘里去。所以如果采用SSD固态硬盘，那么缓存页刷盘的性能就会提高不少。
二.设置以最大随机IO速率刷盘
除了SSD外，还得设置一个关键的参数，就是innodb_io_capacity。这个参数告诉数据库采用多大的IO速率把缓存页刷入到磁盘。
如果SSD能承载每秒600次随机IO，但innodb_io_capacity只设置300。也就是把缓存页刷入到磁盘时，每秒最多执行300次随机IO。那么这样就根本无法把SSD固态硬盘的随机IO性能发挥出来。
所以通常建议对机器的SSD固态硬盘承载的最大随机IO速率进行测试。可以使用fio工具来测试，测出磁盘最大的随机IO速率。测出SSD固态硬盘的最大随机IO速率后，就设置给innodb_io_capacity。这样就可以尽可能让数据库用最大的速率把缓存页刷到磁盘。
三.设置禁止刷入邻近的缓存页
还有一个关键参数，就是innodb_flush_neighbors。这个参数可以控制缓存页刷盘时，临近的其他缓存页是否也刷入到磁盘。如果该参数设置为1，那么就会导致每次刷入磁盘的缓存页太多了。
所以如果使用了SSD固态硬盘，并没必要让数据库同时刷邻近的缓存页。可将该参数设置为0，禁止刷邻近缓存页，减少每次刷新缓存页的数量。
(4)总结
针对MySQl性能随机抖动的问题：最核心的就是把innodb_io_capacity设置为SSD固态硬盘的IOPS。同时设置innodb_flush_neighbors为0，禁止让数据库刷邻近的缓存页。从而让数据库能尽快将缓存页刷进磁盘，及减少每次要刷缓存页的数量，最终将缓存页刷入磁盘的性能提到最高。
四.索引原理和索引优化
1.没有索引数据库如何搜索数据
2.如何设计主键索引及根据主键索引查询
3.索引的物理存储结构
4.聚簇索引是什么
5.针对主键之外的字段建立的二级索引
6.插入数据时如何维护不同索引的B+树
7.MySQL的B+树索引原理总结
8.联合索引使用规则
9.进行排序和分组时如何能使用索引
10.索引覆盖与回表
11.设计索引的原则
1.没有索引数据库如何搜索数据
(1)数据页在磁盘文件中的物理存储结构
数据页之间组成双向链表，数据页内部的数据行组成单向链表。数据行会根据主键从小到大排序进行存储，每个数据页都会有一个页目录，页目录中存放的是每一个数据行的主键和所在槽位的映射关系。数据行会被分散存储到不同的槽位里，一个槽位会有多条数据行。
LRU链表、flush链表和free链表都是双向链表，数据页之间组成双向链表，数据行之间组成单向链表。
(2)查询数据的情形分析
情形一：假设要根据主键查找某个表的一条数据，而且此时该表并没有几条数据，该表总共就一个数据页。那么查找时就会首先到数据页的页目录根据主键进行二分查找，然后通过二分查找在目录里迅速定位到主键对应的数据是在哪个槽位。接着到对应的槽位里，遍历槽位里的每一行数据，这样就能快速找到那个主键对应的数据。
情形二：假设要根据非主键的其他字段查找某个表的一条数据：这时就没有办法通过页目录的主键来进行二分查找了，只能进入到数据页里，依次遍历单向链表里的每一个数据行来查找，这样性能就很差。
情形三：上述只是假设只有一个数据页的情况，如果有很多数据页又该如何查询？当有大量数据页的情况下，而且又没有建立任何索引，那么无论是根据主键查询还是根据其他字段进查询，都只能全表扫描。
所谓全表扫描就是：首先将第一个数据页从磁盘上读取到内存Buffer Pool的缓存页里。然后在该缓存页里，如果是根据主键查询，就在其页目录进行二分查找。如果是根据其他字段查询，则只能遍历缓存页中数据行的单向链表来查找。如果第一个数据页没找到，只能根据数据页的双向链表去找下一个数据页。然后读取到Buffer Pool的缓存页里，按照同样方法继续。依此类推，直到找到为止。
在没有任何索引时，不管如何查找数据，都是一个全表扫描的过程。首先根据双向链表依次把磁盘上的数据页加载到Buffer Pool的缓存页中，然后在Buffer Pool的缓存页内部来查找数据，或是二分查找页目录，或是遍历数据页中数据行的单向链表，来找出需要的那条数据。
2.如何设计主键索引及根据主键索引查询
(1)数据页分裂的过程
在不停往表里插入数据时，会创建一个一个的数据页。如果数据主键不是自增，就可能会出现数据行跨页移动。以此保证下一个数据页的主键值都大于上一个数据页的主键值。
(2)基于主键的索引是如何设计的
针对主键的索引实际上就是主键目录，主键目录会把每个数据页的页号，还有数据页里最小的主键值放在一起，从而形成一个主键的目录。
(3)如何根据主键索引查询
有了主键目录，查询时就可以直接到主键目录去查找了。通过和最小主键值进行对比，就可以快速定位数据在哪个数据页中，之后便可以到对应的数据页中，根据主键值找出要找的数据。类似这种主键目录，就可以认为是索引目录。
3.索引的物理存储结构
既然索引目录里会包含每个数据页和它的最小索引值。那么查询主键值时，通过二分查找索引目录就能快速定位对应的数据页。接着进入对应的数据页的页目录，继续二分查找就能快速定位所查数据。
MySQL将索引目录存储在专门的数据页上，这些数据页称为索引页。如果有很多数据页，此时就需要很多索引页。为了快速查找索引页的数据，可从索引页的基础上多加一个层级出来。在更高的索引层级里，保存每个索引页和索引页里的最小主键值。
其实这就是一棵B+树，所以才说MySQL的索引是用B+树来组成的。当数据库为一个表的主键建立索引后，这个主键的索引就是一棵B+树。当数据库需要根据主键来查询数据时，会从B+树的顶层开始二分查找。一层一层往下定位，最终定位到一个数据页里，然后在数据页内部的目录里继续二分查找，最后找到那条数据。
以上就是索引的物理存储结构：
一.采用和数据页一样的索引页来存储索引数据
二.一个索引就是很多索引页组成的一棵B+树
4.聚簇索引是什么
最下层的索引页，都会有指针引用数据页。在索引页内部，同一个层级的索引页互相之间会基于指针组成双向链表，与数据页之间可以组成双向链表一样。
假设把索引页和数据页综合起来看，它们都是连接在一起的，看起来就如同一棵完整的大的B+树一样。在这棵树里最底层的一层就是数据页，而数据页就是B+树里的叶子节点。所以如果一棵大的B+树索引数据结构里，叶子节点就是数据页自己本身，那么就可以称这棵B+树索引为聚簇索引。
一般来说，即便是亿级的大表，所建的索引的层级也就三四层而已。这个聚簇索引默认是按主键来组织的，所以数据库在增删改数据的时候，会更新数据页，同时自动维护B+树结构的聚簇索引，新增和更新索引页。
5.针对主键之外的字段建立的二级索引
(1)聚簇索引和主键搜索
聚簇索引就是Innodb默认创建的一个基于主键的索引结构，而且表里的数据默认就是直接放在聚簇索引里的，聚簇索引的叶子节点就是数据页自己本身。
对主键数据的搜索，其实就是从聚簇索引的根节点开始进行二分查找。一直找到对应的数据页里，最后基于页目录来二分查找定位到数据行。
(2)对主键外的其他字段建立索引的原理
一.该索引也维护B+树来存放索引和主键字段
假设需要对其他字段建立索引，比如name字段。那么数据库就需要维护一个聚簇索引和一个name字段的索引，既要维护聚簇索引的B+树，也要维护name字段索引的B+树。
所以插入数据时，会先把完整数据插入到聚簇索引的叶子节点的数据页。再把部分数据(主键 + name)插入到name字段索引的叶子节点的数据页。
二.该索引也按索引值排序及构建多层级索引页
name字段索引是独立于聚簇索引之外的一棵索引B+树，name字段的索引B+树里的叶子节点的数据页只放主键和name字段的值。name的排序规则和主键的排序规则一样，也按name值的大小进行排序，也就是下一个数据页的name字段值要大于上一个数据页的name字段值。然后name字段的索引B+树也会构建多层级的索引页，这个索引页里存放的就是下一层的页号和最小name字段值。
三.按该索引查询完整数据要回表查询主键索引
如果要根据name字段来搜索数据，也会从name字段的索引B+树里的根节点开始查找，一层一层往下找，一直找到叶子节点的数据页为止。但找到叶子节点的数据页，也仅仅是找到对应的主键值，还不能找到这行数据完整的所有字段，所以这时候还需要进行回表查询。这个回表，指的是还需要根据主键值，到聚簇索引里从根节点开始查找。一直找到叶子节点的数据页，才能定位到主键对应的完整数据行。
四.联合索引的排序规则和查询
此外也可以把多个字段联合起来，建立联合索引，比如name + age。此时联合索引的运行原理也是一样的，只不过是新建一棵独立的B+树。这棵新的B+树的叶子节点的数据页里会存放id + name + age。联合索引name + age的排序规则是默认按name排序，name一样就按age排序。
在这个name + age的联合索引的B+树的索引页里，放的就是下层节点的页号和最小的name + age的值。所以根据name + age查询时，就会使用name + age联合索引这棵B+树。搜索到主键之后再根据主键到聚簇索引里搜索。
以上1-5就是Innodb存储引擎的索引完整实现原理。
6.插入数据时如何维护不同索引的B+树
(1)MySQL插入数据时聚簇索引的维护过程
一.创建表时只有一个数据页
首先一个表完成创建后，它就是一个数据页。这个数据页属于聚簇索引的一部分，而且当前还是空的。此时MySQL插入数据，会直接往这个数据页里插入，不用新增索引页。
二.只有一个数据页时的查询
这时这个初始的数据页其实就是一个根页。由于每个数据页内默认有一个基于主键的页目录，所以可根据主键来查。查询时直接在这唯一的数据页中，通过页目录来查询即可。
三.根页分裂 -> 根页成为索引页
随后MySQL会往表插入越来越多的数据，此时数据页满了，就会新增两个数据页，把根页里的数据都拷贝到新的两个数据页上，并且会按照主键值大小进行拷贝，让两个新的数据页能根据主键值排序，确保第二个数据页的主键值都大于第一个数据页的主键值。拷贝完成后根页会升级为索引页，这个索引页里放的是两个新增数据页的页号和它们里面最小的主键值。
四.数据页分裂 -> 索引页分裂
之后MySQL继续往表里插入数据，然后数据页会不停的进行页分裂，分裂出越来越多的数据页。此时唯一的一个索引页(根页)里面的索引条目也越来越多，当这个索引页也放不下时，这个索引页也会分裂成两个索引页。原来唯一的根页会往上提一个层级，然后引用这个两个索引页。
五.以此类推
当插入的数据继续增多，数据页越来越多，根页指向的索引页也会不停的分裂，索引页也越来越多。当根页下面的索引页数量太多时，一个根页已经放不下所有的索引页了。这时会导致根页也分裂成多个索引页，根页再次往上提一个层级。
(2)MySQL插入数据时二级索引的维护过程
假设name字段有索引，那么刚开始插入数据时：首先会在聚簇索引的唯一数据页里插入数据，然后会在name字段索引B+树的唯一数据页里插入数据。随后插入的数据越来越多，name字段索引B+树的唯一数据页也会分裂，分裂过程和聚簇索引一样。
所以MySQL插入数据时，本身就会自动维护各个索引的B+树。在name字段索引B+树的索引页中，除存放页号和最小name字段值外，每个索引页还会存放最小name字段值对应的主键值。
7.MySQL的B+树索引原理总结
B+树索引的数据结构、排序规则、插入时索引页的形成过程、基于B+树的查询原理、不同字段的索引有独立B+树、回表的过程。
(1)聚簇索引的叶子节点才是数据页
默认情况下MySQL建立的聚簇索引都是基于主键值来组织的。聚簇索引的叶子节点都是数据页，里面放的就是插入的完整数据。
(2)索引页或数据页之间组成双向链表 + 页内记录组成单向链表
数据页或索引页里的记录会组成一个单向链表，按数据大小有序排列。数据页或索引页之间会组成一个双向链表，按数据大小有序排列。
(3)高效查找原因
正是因为这个有序的B+树索引结构，才能让MySQL查找数据时，从B+树的根节点开始，按数据值大小通过二分查找一层一层往下高效查。
(4)二级索引的维护
如果针对主键外的字段建立索引，那么本质上就是为那个字段的值重新建立另外一棵B+树索引。这棵索引B+树的叶子节点也是数据页，存放的只有字段的值和主键值。而每层索引页存放的都是下层索引页或数据页的引用。
(5)基于二级索引的查找与回表
假设要根据非主键字段的索引来查找一行完整的数据。首先会基于非主键字段的索引B+树，快速查找到那个值所对应的主键值。然后再根据这个主键值，去主键的聚簇索引B+树里，进行回表查询，即重新从根节点开始查找那个主键值。最后找到主键值对应的完整数据。
(6)建立索引的好处和坏处
好处是不需要全表搜索，可以将查询性能提升得很高。因为可以直接根据某个字段的索引B+树来进行高效查找数据。
坏处有两点，分空间上和时间上两个方面。空间上要给很多字段创建索引，创建很多棵索引B+树，很耗费磁盘空间。时间上在增删改时，每次都需要维护各个索引的数据有序性。因为每个索引B+树都要求页内按照索引值大小排序，索引页之间有序。而不停的增删改会导致数据页不停分裂、不停增加索引页，很耗费时间。所以一般不建议一个表里设置太多索引。
8.联合索引使用规则
(1)规则一：等值匹配规则
(2)规则二：最左侧列匹配规则
(3)规则三：最左前缀匹配规则
(4)规则四：范围查找规则
(5)规则五：等值匹配 + 范围匹配的规则
(6)如何判断联合索引的字段是否还在生效
联合索引中的数据页或索引页是按照联合索引的字段顺序进行排序的。先根据第一个字段排序，再根据第二个字段排序，依此来建立顺序关系。
(1)规则一：等值匹配规则
在where语句中的字段名称和联合索引的字段完全一样，而且都是基于等号的等值匹配，那么肯定会用上联合索引。即便where语句里的字段顺序和联合索引里的字段顺序不一致，MySQL也会自动优化按联合索引的字段顺序去找。
(2)规则二：最左侧列匹配规则
假设联合索引是key(class_name, student_name, subject_name)，那么不一定要在where语句里根据这三个字段来查。其实只要根据最左侧的部分字段，也是可以利用索引进行查询的。在联合索引的B+树里，必须先按class_name查，再按student_name查，不能跳过前面两个字段，直接按最后一个subject_name来查。由如下例子可知，联合索引最好是区分度大的排前面。
//可以利用上联合索引来查
mysql> select * from student_score where class_name = '' and student_name = '';
//不能利用联合索引来查
mysql> select * from student_score where subject_name = '';
//只有class_name的值可以在联合索引里搜索，剩下的subject_name没法利用联合索引搜索
mysql> select * from student_score where class_name = '' and subject_name = '';
(3)规则三：最左前缀匹配规则
一.假如使用的是 like '1%' 查询，那么这也是可以用到联合索引的。因为在联合索引的B+树里，都是按class_name排序的。所以要是给出class_name的确定的最左前缀就是1，然后后面给的一个模糊匹配符号，那也可以基于索引来查找。
二.假如使用的是 like '%1' 查询，在左侧用一个模糊匹配符，那么就没法用联合索引。
//查找所有1开头的班级的分数，那么也是可以用到联合索引的
mysql> select * from student_score where class_name like '1%';
//查找所有1结尾的班级的分数，那么也是可以用到联合索引的
mysql> select * from student_score where class_name like '%1';
(4)规则四：范围查找规则
使用如下范围查找，也可以利用上联合索引。因为索引的最下层的数据页都是按顺序组成双成双向链表的，所以完全可以先找到'1班'对应的数据页，再找到'5班'对应的数据页。两个数据页中间的那些数据页，就全都是在所查范围内的数据。
//可以用上联合索引
mysql> select * from student_score where class_name > '1班' and class_name < '5班';
但是如下的语句只有class_name是可以基于联合索引来查，student_name的范围是没法使用到联合索引的。所以如果使用的where语句里有范围查询，那么只有对联合索引最左侧的列进行范围查询才能用到索引。
//只有class_name是可以使用联合索引，student_name的范围是没法使用到联合索引
mysql> select * from student_score where class_name > '1班' and class_name < '5班' and student_name > '';
(5)规则五：等值匹配 + 范围匹配的规则
mysql> select * from student_score where class_name = '1班' and student_name > '' and subject_name < '';
该语句可以使用class_name在联合索引里精准定位到一堆数据。然后这堆数据里的student_name都是按照顺序排列的，所以student_name > ''会基于索引来查找，但接下来student_name < ''则不能用索引。
(6)如何判断联合索引的字段是否还在生效
可以根据，前面的字段按照索引筛选后，得出的结果是否还能按后面的字段顺序排列，来判断后面的字段的索引是否有效。
9.进行排序和分组时如何能使用索引
当SQL语句里使用where语句进行数据过滤和筛选时，从联合索引最左侧的字段开始去使用，保证前一个字段的筛选结果能按当前字段顺序排列，这样当前字段就能用上索引树。
(1)进行排序时如何能使用索引
当SQL语句里使用order by语句进行排序时：尽量按照联合索引的字段顺序去进行order by排序，从而利用上联合索引B+树里的数据有序性。
但要注意，按多个字段排序时，这多个字段要么都是升序要么都是降序。不能出现一个字段升序一个字段降序，否则只能先基于where语句筛选出数据，然后放到内存或临时磁盘文件，接着再通过排序算法按照某个字段来进行排序。
基于临时磁盘文件来排序，MySQL里叫做filesort。
(2)进行分组时如何能使用索引
对于group by后的字段，最好也是按联合索引里的最左侧的字段开始，按顺序排列。其实group by和order by用上索引的原理和条件都是一样的，如果在group by后的字段顺序和联合索引中最左侧开始的字段顺序一致，那么就可以充分利用索引树里已经完成排序的特性。
所以进行表设计时，通常设计两三个常用的索引，覆盖常见的where筛选、order by排序和group by分组的需求，保证常见SQL都能用上索引，系统跑起来不会有太大的查询性能问题。
10.索引覆盖与回表
(1)按索引字段查询非索引字段的值需要回表
一般自己建的索引不管是单列索引还是联合索引：一个索引就对应着一棵独立的索引B+树，其节点仅包含索引里的字段的值和主键值。所以即使根据索引树按照条件找到了需要的数据，这些数据也仅仅是索引里的几个字段的值和主键值。万一查询的是其他非索引里的字段，那还需要进行回表操作，也就是根据主键到聚簇索引里把所需字段提取出来。
(2)回表操作可能不使用联合索引而用全表扫描
//比如语句
mysql> select * from table order by xx1,xx2,xx3;
如果从联合索引的索引树里按照顺序取出的数据特别多，然后还需要根据这些数据的主键去聚簇索引查找，那么性能也不会高。所以有时MySQL执行引擎可能会认为，如果都已经把联合索引和聚簇索引都扫描一遍了，还不如不使用联合索引而直接全表扫描。
(3)有限制条数的回表操作还是会使用联合索引
//比如语句
mysql> select * from table order by xx1,xx2,xx3 limit 10;
此时MySQL的执行引擎就会知道，需要扫描联合索引的索引树拿到10条数据。然后对10条数据在聚簇索引里查找10次，那么还是会使用联合索引的。
(4)索引覆盖会在索引树上获取数据无需回表
索引覆盖不是一种索引，而是基于索引查询的一种方式。需要的字段直接在索引树里就能提取出来，不需要回表到聚簇索引，这样的查询方式就是索引覆盖。
(5)总结
尽量在SQL里指定只需要的几个字段，不要将非索引里的字段也查出来。这样就能利用起索引覆盖的方式，不用回表查聚簇索引。即便需要回表查聚簇索引，尽量使用limit来限定回表聚簇索引的次数。
11.设计索引的原则
(1)设计的索引尽量包含上where、order by、group by里的字段
(2)根据离散度来选择索引字段，尽量使用基数较的、值多的字段
(3)一个表的索引不要太多，最好两三个联合索引能覆盖掉全部查询
(4)查询枚举字段时比如性别可通过in的方式改造成使用联合索引
(5)经常使用范围查询的字段最好放在联合索引最后面
(6)针对低基数字段的查询可用id设计辅助索引来处理
(1)设计的索引尽量包含上where、order by、group by里的字段
设计两三个联合索引，要尽量包含where、order by、group by里的字段。尽量让where、order by、group by后的字段顺序，都能命中联合索引。
(2)根据离散度来选择索引字段，尽量使用基数较的、值多的字段
这样才能发挥出B+树快速二分查找的优势。同时，尽量对字段类型较小的字段建立索引，如tinyint、varchar(255)。如果要建立索引的字段的值太大，可以取字段值的前20个字符建立索引。
比如：字段name是varchar(255)类型，但需要基于字段name建立索引。那么可以建立这样的索引key my_index(name(20),age,course)，不过这种索引只能用在where条件下，不能用在order by和group by条件。
但即便字段基数较低，却频繁查询的字段，也可放到联合索引的最左侧。比如性别字段、省市字段等。
(3)一个表的索引不要太多，最好两三个联合索引能覆盖掉全部查询
索引太多必然导致对数据进行增删改时性能很差。此外主键也最好自增，不要使用UUID之类的主键。
自增的主键可以保证聚簇索引不会频繁的产生页分裂。UUID的主键会导致聚簇索引频繁的页分裂，增删性能查。
(4)查询枚举字段时比如性别可通过in的方式改造成使用联合索引
某些查询语句即便只需要联合索引的部分字段作为条件去查询，也可以通过in语句的方式，把查询语句改造成可以使用联合索引。
比如key(city,sex,age)，虽然只需查where city = 'xxx' and age > xxx，也可以改造成where city = 'xxx' and sex in (0,1) and age > xxx。
也就是说，对于那些枚举字段，查询的时候也可以加进来作为条件。从而利用上联合索引，不然原来的语句是利用不上索引的。
(5)经常使用范围查询的字段最好放在联合索引最后面
从而保证范围查询字段前面的条件字段也可以用到索引，比如上面的age就是范围查询使用得多。
(6)针对低基数字段的查询可用id设计辅助索引来处理
//比如
mysql> select * from users where sex = 'man' limit x,y;
//可以增加辅助字段score，然后建立索引key(sex, score)，查询时使用：
mysql> select * from users where sex = 'man' order by score limit x,y;
//也可以使用id建立辅助索引：key_sex_id(sex,id)，查询时使用：
mysql> select * from users where sex = 'man' order by id limit x,y;
总之，尽量利用一两个复杂的多字段联合索引，抗下80%以上的查询。然后用一两个辅助索引抗下剩余20%的非典型查询。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
