MySql存储引擎、锁、sql执行顺序和优化（笔记）

MySQL架构总览

整个架构分为两层，上层是MySQLD的被称为的‘SQL Layer’，下层是各种各样对上提供接口的存储引擎，被称为‘Storage Engine Layer’

 

查询执行流程

 

SQL解析顺序

• 1、FROM

对FROM子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表VT1。

• 2、ON

对VT1应用ON筛选器，只有那些使为真才被插入到TV2。

• 3、OUTER(JOIN)

如果指定了OUTERJOIN(相对于CROSSJOIN或INNERJOIN)，保留表中未找到匹配的行将作为外部行添加到VT2，生成TV3。如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表位置。

• 4、WHERE

对TV3应用WHERE筛选器，只有使为true的行才插入TV4。此时数据还没有分组，所以不能在WHERE中出现对统计的过滤。

• 5、GROUP BY

按GROUPBY子句中的列列表对TV4中的行进行分组，生成TV5。在GROUPBY阶段，数据库认为两个NULL值是相等的，因此会将NULL值分到同一个分组中

• 6、CUTE|ROLLUP

把超组插入VT5，生成VT6。

• 7、HAVING

对VT6应用HAVING筛选器，只有使为true的组插入到VT7。COUNT(expr)会返回expr不为NULL的行数，count(1)、count(*)会返回包括NULL值在内的所有数量。

• 8、SELECT

处理SELECT列表，产生VT8。

• 9、DISTINCT

将重复的行从VT8中删除，产生VT9。

• 10、ORDER BY

将VT9中的行按ORDERBY子句中的列列表顺序，生成一个游标(VC10)。如果不指定排序，数据并非总是按照主键顺序进行排序的。NULL被视为最小值。

• 11、LIMIT

从VC10的开始处选择指定数量或比例的行，生成表TV11，并返回给调用者。LIMITn,m的效率是十分低的，一般可以通过在WHERE条件中指定范围来优化WHEREid>?limit10。

 

InnoDB存储引擎

表空间

InnoDB表由共享表空间、日志文件组(redo文件组)、表结构定义文件组成。

表空间由段、区、页(块)组成。准确的说表空间是由分散的页和段组成。

段主要有数据段、索引段、回滚段等。InnoDB表是由索引组织的，因此数据即索引，索引即数据。所以数据段即为B+树的叶子节点，索引段为B+树非叶子节点。

区是由64个连续的页组成，每个页为16KB，即每个区1MB。

InnoDB默认只有一个共享表空间ibdata1，如果启用参数innodb_file_per_table则每张表的数据可以单独一个表空间。每张表空间只存放数据、索引和插入缓冲，其他类型数据还是存放共享表空间。

主键

InnoDB存储引擎中，每张表都有一个主键，如果创建表时没有显式定义主键，则InnoDB引擎会首先在表中查找是否有非空的唯一索引，如果有则改列即为主键，否则自动创建一个6字节大小的指针。

锁

InnoDB存储引擎锁提供一致的非锁定读、行级锁支持，行级锁没有相关的开销？，可以同时得到并发性和一致性。(《mysql技术内幕·InnoDB存储引擎》第六章第一节最后一段说“行级锁没有相关的开销”，但网上查到的都是“由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。”)

锁类型：共享锁(允许事务读一行数据)、排他锁(允许事务删除或更新一行数据)。

InnoDB还支持一种额外的锁方式—意向锁，意向锁是表级别锁。目的是为在一个事务中揭示下一行被请求的锁类型。意向共享锁(事务想要获取一个表中某几行的共享锁)、意向排他锁(事务想获取一个表中的某几行排他锁)

插入意向锁在行插入之前由INSERT设置一种间隙锁，是意向排它锁的一种。在多事务同时写入不同数据至同一索引间隙的时，不会发生锁等待，事务之间互相不影响其他事务的完成，这和间隙锁的定义是一致的。假设一个记录索引包含4和7，其他不同的事务分别插入5和6，此时只要行不冲突，插入意向锁不会互相等待，可以直接获取。参照锁兼容/冲突矩阵。

自增锁(AUTO-INC Locks)是事务插入时自增列上特殊的表级别的锁。最简单的一种情况：如果一个事务正在向表中插入值，则任何其他事务必须等待，以便第一个事务插入的行接收连续的主键值。我们一般把主键设置为AUTO_INCREMENT的列，默认情况下这个字段的值为0，InnoDB会在AUTO_INCREMENT修饰下的数据列所关联的索引末尾设置独占锁。在访问自增计数器时，InnoDB使用自增锁，但是锁定仅仅持续到当前SQL语句的末尾，而不是整个事务的结束，毕竟自增锁是表级别的锁，如果长期锁定会大大降低数据库的性能。由于是表锁，在使用期间，其他会话无法插入表中

InnoDB行锁是通过给索引上的索引项加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

InnoDB对不同语句执行时的加锁状况

如果一个SQL语句要对二级索引(非主键索引)设置X模式的Record锁，InnoDB还会检索出相应的聚簇索引(主键索引)并对它们设置锁定。

• SELECT语句

SELECT ... FROM是快照读取，除了SERIALIZABLE的事务隔离级别，该SQL语句执行时不会加任何锁。insert into target_tab select … from source_tab和create  table new_tab select … from source_tab 中source_tab加了共享锁。

SERIALIZABLE级别下，SELECT语句的执行会在遇到的索引记录上设置S模式的next-key锁。但是对于唯一索引，只锁定索引记录，而不会锁定gap。

• UPDATE系列

S锁读取(SELECT ... LOCK IN SHARE MODE)，X锁读取(SELECT ... FOR UPDATE)、更新UPDATE和删除DELETE这四类语句，采用的锁取决于搜索条件中使用的索引类型。

如果使用唯一索引，InnoDB仅锁定索引记录本身，不锁定间隙；

如果使用非唯一索引，或者未命中索引，InnoDB使用间隙锁或者next-key锁来锁定索引范围，这样就可以阻止其他事务插入锁定范围。

• UPDATE语句

UPDATE ... WHERE ... 在搜索遇到的每条记录上设置一个独占的next-key锁，如果是唯一索引只锁定记录。

当UPDATE修改聚簇索引时，将对受影响的二级索引采用隐式锁，隐式锁是在索引中对二级索引的记录逻辑加锁，实际上不产生锁对象，不占用内存空间。

例如update test set code=100 where id=10;执行的时候code=10的索引(code是二级索引，见文中给出的建表语句)会被加隐式锁，只有隐式锁产生冲突时才会变成显式锁(如S锁、X锁)。即此时另一个事务也去更新id=10这条记录，隐式锁就会升级为显示锁。这样做的好处是降低了锁的开销。

UPDATE可能会导致新的普通索引的插入。当新的索引插入之前，会首先执行一次重复索引检查。在重复检查和插入时，更新操作会对受影响的二级索引记录采用共享锁定(S锁)。

 

• DELETE语句

DELETE FROM ... WHERE ... 在搜索遇到的每条记录上设置一个独占的next-key锁，如果是唯一索引只锁定记录。

 

• INSERT语句

INSERT区别于UPDATE系列单独列出，是因为它的处理方式较为特别。

插入行之前，会设置一种插入意向锁，插入意向锁表示插入的意图。如果其它事务在要插入的位置上设置了X锁，则无法获取插入意向锁，插入操作也因此阻塞。

INSERT在插入的行上设置X锁。该锁是一个Record锁，并不是next-key锁，即只锁定记录本身，不锁定间隙，因此不会阻止其他会话在这行记录前的间隙中插入新的记录。

 

InnoDB锁算法

RecordLock(单个记录上的锁)、GapLock(间隙锁，锁定一个范围，但不包含记录本身)、Next-KeyLock(GapLock+RecordLock,锁定一个范围，且锁定记录本身)。InnoDB行锁实现特点意味着：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果根表锁一样。

1. 在不通过索引条件查询时，InnoDB会锁定表中所有记录
2. 由于Mysql行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的
3. 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁
4. 有时候虽然在条件中使用了索引字段，但是是否使用索引来检查数据是由Mysq通过判断不同的执行计划来决定的，如果Mysql认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引。这个时候Mysql将会使用表锁，不会使用行锁。所以在分析锁冲突的时候不要遗漏了用EXPLAIN观察Mysql执行计划，是否走了索引
5. 当我们用范围条件而不是相等条件检索数据，并请求共享锁或排他锁时候，InnoDB会给符合条件的已有数据的索引项加锁；对于键值在条件范围内但是并不存在的记录，叫做间隙（GAP），InnoDB也会对间隙加锁，这种机制就是Next-Key。除了对范围条件加锁时候加锁使用Next-key锁，对不存在的记录加锁，也会使用Next-key锁。InnoDB对间隙加锁的目的，是为了防止幻读。
6. 事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下考虑使用表锁来提高该事务的执行速度
7. 事务设计多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况可以考虑一次性锁定事务设计的表，从而避免死锁，减少数据库因事务回滚带来的开销

 

事务隔离级别

SQL标准中的事务四种隔离级别

隔离级别	脏读 （DirtyRead）	不可重复读 （NonRepeatableRead）	幻读 （PhantomRead）
未提交读 (Readuncommitted)	可能	可能	可能
已提交读 （Readcommitted）	不可能	可能	可能
可重复读 （Repeatableread）	不可能	不可能	可能
可串行化 （Serializable）	不可能	不可能	不可能

1. 未提交读(ReadUncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
2. 提交读(ReadCommitted)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别(不重复读)
3. 可重复读(RepeatedRead)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读
4. 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

 

索引

Mysql主要支持B树索引、散列索引、空间索引、全文索引，索引是由存储引擎实现，不同引擎存在差异。

逻辑上分为：单列索引、复合索引、唯一索引、非唯一索引。

聚集索引（聚簇索引、簇索引）：索引值的逻辑顺序与索引服务的表中相应行的物理顺序相同（数据和索引（B+树）在一起，记录被真实的保存在索引的叶子中），InnoDB表使用聚簇索引。

聚簇索引优点：

1. 将相关的的数据保持在一起， 叶子节点内可保存相邻近的记录。
2. 因为索引和数据存储在一起， 所以查找数据通常比非簇索引更快。 由于主键是有序的， 很显然， 对于InnoDB表， 最高效的存取方式是按主键存取唯一记录或进行小范围的主键扫描。

聚簇索引缺点：

1. 簇索引对I/O密集型的负荷性能提升最佳， 但如果数据是在内存中（访问次序不怎么重要） ， 那么簇索引并没有明显益处。
2. 插入操作很依赖于插入的顺序， 按primary key的顺序插入是最快的。
3. 更新簇索引列的成本比较高， 因为InnoDB不得不将更新的行移动到新的位置。
4. 全表扫描的性能不佳， 尤其是数据存储得不那么紧密时， 或者因为页分裂（page split） 而导致物理存储不连续。
5. 二级索引的叶节点中存储了主键索引的值， 如果主键采用的是较长的字符， 那么索引可能会很大， 且通过二级索引查找数据也需要进行两次索引查找

索引的使用

1. 筛选记录的条件应能组成复合索引最左边的部分， 即按最左前缀的原则进行筛选。
2. 索引列上的范围查找，应该避免大范围的索引范围查找， 如果索引范围查找的成本太高， 那么数据库可能会选择全表扫描的方式。IN(...)并不属于范围查找的范畴。
3. Join。在联合查询两个表时， 比如查询语句为“SELECT a.col1,b.col2 FROM a JOIN b ON a.id=b.id”， 其中id为主键， 若a表是驱动表， 那么数据库可能全表扫描a表， 并用a表的每个id去探测b表的索引查找匹配的记录
4. WHERE子句的条件列是复合索引前面的索引列再加上紧跟的另一个列的范围查找。MySQL索引仅支持最近一个范围的查询。也就是说，MySQL使用最左边的前缀，一直到碰到第一个范围的查找条件为止
5. MySQL优化器会做一些特殊优化， 比如对于索引查找MAX（索引列） ， 那么可以进行直接定位，可以直接利用索引信息来解决， 而不需要去检索物理记录。 优化器确定只需要返回一行结果即可。

注意事项和建议

1. WHERE条件中的索引列不能是表达式的一部分， MySQL也不支持函数索引。
2. InnoDB的非主键索引存储的不是实际记录的指针， 而是主键的值， 所以主键最好是整型值， 如自增ID， 基于主键存取数据是最高效的， 使用二级索引存取数据则需要进行两次索引查找。
3. 最好是按主键的顺序导入数据， 如果导入大量随机id的数据， 那么可能需要运行OPTIMIZE TABLE命令来优化表。
4. 索引应尽量是高选择性的， 而且需要留意“基数（cardinality） ”值， 基数指的是一个列中不同值的个数， 显然， 最大基数意味着该列中的每个值都是唯一的， 最小基数意味着该列中的所有值都是相同的。 索引列的基数相对于表的行数较高时（也就是说重复值更少），索引的工作效果更好。
5. 一些基数很小的列， 如性别可能就不适合建立索引。 也存在这样一种特殊的情况， 有些列虽然基数很小， 但由于数据分布很不均匀因此也会导致某些值的记录数很少， 那么这种情况也适合创建索引加速查找这部分数据。
6. 使用更短的索引。 可以考虑前缀索引， 前缀索引仅索引前面一部分字符（值） ， 但应确保所选择的前缀的长度可以保证大部分值是唯一的
7. 索引太多时可能会浪费空间， 且降低修改数据的速度。 所以， 不要创建过多的索引， 也不要创建重复的索引。
8. 如果是唯一值的列， 创建唯一索引会更佳， 也可以确保不会出现重复数据。
9. 使用覆盖索引（ covering index） 也可以大大提高性能。
10. 利用索引来排序。 MySQL有两种方式可以产生有序的结果。一种是使用文件排序（ filesort） 来对记录集进行排序， 另一种是扫描有序的索引。 我们应尽量利用索引来排序。尽量保证索引列和ORDER BY的列相同， 且各列均按相同的方向排序。如果要连接多张表， 那么ORDER BY引用的列需要在表连接的顺序的首张表内。
11. 添加冗余索引， 需要权衡。
12. 对于复合索引， 如果不考虑ORDER BY、 GROUP BY这样的一些操作， 那么把最具选择性的列放在前面是合适的， 复合索引主要用于优化WHERE查找。 但如果是排序之类的操作， 把最具选择性的列放在前面则不一定最有效， 因为避免随机I/O和排序可能才是我们更值得考虑的。

 

Sql优化

分页limit

大偏距（high offset） 值的查询效率奇差， 会花费大部分时间来扫描大量数据， 而这些数据最终都会被丢弃； 这种情况下， 更好的办法是限制用户所看到的页， 比如只提供最新的几页、 上一页、 下一页， 因为没有什么用户会去关注第10000页的内容。另一个办法是使用覆盖索引（covering index） 。在一定的数据量下， 性能尚可。以下示例中的表已经在（sex， rating） 上创建了索引， id是主键。

SELECT col_1,col_2 FROM profiles INNER JOIN (SELECT id FROM profiles WHERE x.sex='M' ORDER BY rating) AS x USING id;

NULL值

使用DISTINCT、 GROUP BY或ORDER BY时， 所有NULL值将被视为是等同的。

对于聚合（累计） 函数， 如COUNT（） 、 MIN（） 和SUM（） ， 将忽略NULL值。 对此的例外是COUNT（*） ， 它将计数行而不是单独的列值。

NULL值可能会导致MySQL的优化变得复杂， 所以， 一般建议字段应尽量避免使用NULL值。

连接的优化

1. ON、 USING子句中的列确认有索引。 如果优化器选择了连接的顺序为B、 A， 那么我们只需要在A表的列上创建索引即可。 例如， 对于查询“SELECT B.*,A.*FROM B JOIN A ON B.col1=A.col2;”语句MySQL会全表扫描B表， 对B表的每一行记录探测A表的记录（利用A表col2列上的索引） 。
2. 最好是能转化为INNER JOIN， LEFT JOIN的成本比INNER JOIN高很多。
3. 使用EXPLAIN检查连接,留意EXPLAIN输出的rows列， 如果rows列太高， 比如几千， 上万， 那么就需要考虑是否索引不佳或连接表的顺序不当。
4. 反范式设计， 这样可以减少连接表的个数， 加快存取数据的速度。
5. 考虑在应用层实现连接。

GROUP BY、 DISTINCT、 ORDER BY语句优化

1. 尽量对较少的行进行排序。
2. 如果连接了多张表， ORDER BY的列应该属于连接顺序的第一张表。
3. 利用索引排序， 如果不能利用索引排序， 那么EXPLAIN查询语句将会看到有filesort。
4. GROUP BY、 ORDER BY语句参考的列应该尽量在一个表中， 如果不在同一个表中， 那么可以考虑冗余一些列， 或者合并表。
5. 需要保证索引列和ORDER BY的列相同， 且各列均按相同的方向进行排序。
6. 增加sort_buffer_size。
7. 增加read_rnd_buffer_size。
8. 改变tmpdir变量指向基于内存的文件系统或其他更快的磁盘。
9. 优化GROUP BY WITH ROLLUP。GROUP BY WITH ROLLUP可以方便地获得整体分组的聚合信息（super aggregation） ， 但如果存在性能问题， 可以考虑在应用层实现这个功能， 这样往往会更高效， 伸缩性也更佳
10. 使用非GROUP BY的列来代替GROUP BY的列

优化子查询

但MySQL对于子查询的优化一直不佳， 就目前的研发实践来说， 子查询应尽量改写成JOIN的写法。

优化IN列表

于IN列表， MySQL会排序IN列表里的值， 并使用二分查找（ Binary Search） 的方式去定位数据。把IN子句改写成OR的形式并不能提高性能。如果能够将其转化为多个等于的查询， 那么这种方式会更优。

优化UNION

UNION语句默认是移除重复记录的， 需要用到排序操作， 如果结果集很大， 成本将会很高， 所以， 建议尽量使用UNIONALL语句。

优化带有BLOB、 TEXT类型字段的查询

由于MySQL的内存临时表不支持BLOB、 TEXT类型， 如果包含BLOB或TEXT类型列的查询需要用到临时表， 就会使用基于

磁盘的临时表， 性能将会急剧降低。 所以， 编写查询语句时， 如果没有必要包含BLOB、 TEXT列， 就不要写入查询条件。

优化的办法有如下3种。

1. 如果必须使用， 可以考虑拆分表， 把BLOB、 TEXT字段分离到单独的表。
2. 如果有许多大字段， 可以考虑合并这些字段到一个字段， 存储一个大的200KB比存储20个10KB更高效。
3. 考虑使用COMPRESS()， 或者在应用层进行压缩， 再存储到BLOB字段中。

filesort的优化

filesort往往意味着你没有利用到索引进行排序。

排序一个带JOIN（连接） 的查询， 如果ORDER BY子句参考的是JOIN顺序里的第一张表的列且不能利用索引进行排序， 那么MySQL会对这个表进行文件排序（filesort） ， EXPLAIN输出中的Extra列就有filesort。 如果排序的列来自于其他的表， 且需要临时文件来帮助排序， 那么EXPLAIN输出的Extra列就有“Using temporary;Using filesort”字样。 对于MySQL 5.1， 如果有LIMIT子句， 那么是在filesort之后执行LIMIT的， 这样做效率可能会很差， 因为需要排序过多的记录。

MySQL有两种filesort算法： two-pass和single-pass。MySQL一般使用single-pass这种算法。

优化临时表

通过EXPLAIN的Extra列可以查看是否用到了临时表： “Using temporary”表示使用了临时表。

• 触发以下条件， 会创建临时表。

1. ORDER BY子句和GROUP BY子句引用的列不一样。
2. 在连接查询中， ORDER BY或GROUP BY使用的列不是连接顺序中的第一个表。
3. ORDER BY中使用了DISTINCT关键字。

• 可能会导致使用到磁盘临时表

1. 表中有BLOB或TEXT字段。
2. 使用UNION或UNION ALL时， SELECT子句中包含了大于512字节的列。

• 避免临时表的方法

1. 创建索引： 在ORDER BY或GROUP BY的列上创建索引。
2. 分拆长的列： 一般情况下， TEXT、 BLOB， 大于512字节的字符串， 基本上都是为了显示信息， 而不会用于查询条件， 因此设计表的时候， 可以考虑将这些列分离到另外一张表中。
3. 不需要用DISTINCT时就没必要用DISTINCT， 能用UNION ALL就不要用UNION。

 

参考资料

 https://www.2cto.com/database/201512/453280.html

 https://blog.csdn.net/weixin_39703170/article/details/79056533

https://www.cnblogs.com/warehouse/p/9410599.html

https://www.cnblogs.com/prayers/p/9007392.html

https://dbaplus.cn/news-11-2518-1.html

https://blog.csdn.net/keda8997110/article/details/45080453

《mysql技术内幕·InnoDB存储引擎》姜承尧

《高性能mysql》(第三版) 宁海元等译

《MYSQL DBA修炼之道》陈晓勇