MySQL优化原理剖析

谁践踏了优雅 2022-12-30 15:53 112阅读 0赞

**前言：MySQL架构体系**

首先分享实验前的基础知识，**MySQL主要分为Server层与存储引擎层。**

Server层主要包含连接器、检索内存、分析器、优化器、执行器等，所有跨存储引擎的功能均于这一层构建，例如存储过程、触发器、视图，函数等，有一个标准化的binglog日志模块。

存储引擎负责数据的存储与存取，使用可更换的插件式架构，拥有InnoDB、MyISAM、Memory等多个存储引擎，其中InnoDB引擎有redo log日志模块。如下图所示

![24571c93e5ffbfcebdeb9b62bee82355.png][]

### **（图片来自网络，侵权联系删除）** ###

**实验环境**

***操作系统内核版本：Tencent tlinux release 2.2***

***MySQL数据库版本：5.7.10***

创建新表tb\_article，创建了两个索引：index\_title、index\_author\_id，表结构如下：

![fa6e787961a3409365ec047c4a55a663.png][]

我们尝试插入一些数据：

![d8d3d2f2fdf7284272128c03b7b69742.png][]

现执行SQL语句，select \* from tb\_article where author\_id=20 and title='b'; 分析该SQL语句的执行过程和优化策略。

**MySQL执行SQL语句过程**

![919f3734f07e8afb6b09ea16c4d62df5.png][]

**一、MySQL客户端和服务器通讯**

客户端按照MySQL通信协议将SQL发送到服务端，SQL到达服务端后，服务端会单起一个线程执行SQL。MySQL客户端和服务器之间的通讯协议是“半双工”的。

**二、查询状态**

对于MySQL连接，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。使用show full processlist命令查看当前状态。在一个查询生命周期中，状态会变化很多次，下面是这些状态的解释：

1. sleep：线程正在等待客户端发送新的请求；

2. query：线程正在执行查询或者正在将结果发送给客户端；

3. locked：在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态；

4. analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划；

5. copying to tmp table：线程在执行查询，并且将其结果集复制到一个临时表中，这种状态一般要么是做group by操作，要么是文件排序操作，或者union操作。如果这个状态后面还有on disk标记，那表示MySQL正在将一个内存临时表放到磁盘上；

6. sorting result：线程正在对结果集进行排序；

7. sending data：线程可能在多个状态间传送数据，或者在生成结果集，或者在想客户端返回数据。

**三、查询缓存**

MySQL的缓存主要的作用是为了提升查询的效率，缓存以key和value的哈希表形式存储，key是具体的sql语句，value是结果的集合。如果无法命中缓存,就继续走到分析器的的一步,如果命中缓存就直接返回给客户端 。

如果使用查询缓存，在进行读写操作时会带来额外的资源消耗，如果在一个写多读少的环境中，缓存会频繁的新增和失效。MySQL8.0版本开始取消查询缓存。

**四、查询优化处理**

查询的生命周期的下一步是将一个SQL转换成一个执行计划，MySQL在依照这个执行计划和存储引擎进行交互。这包含多个子阶段：解析SQL、预处理、优化SQL执行计划。这个过程中任何错误都可能终止查询。

1. 语法解析器和预处理：首先MySQL通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。MySQL解析器将使用mysql语法规则验证和解析查询；预处理器则根据一些MySQL规则进一步检查解析数是否合法。

2. 查询优化器：当语法树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

3. 执行计划：MySQL不会生成查询字节码来执行查询，MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。

**五、查询执行引擎**

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。

**六、返回结果给客户端**

了解select \* from tb\_article where author\_id=20 and title='b';性能和优化策略，一般采用explain命令进行分析。

***MySQL explain***

MySQL Query Optimizer通过执行explain命令来获取一个Query在当前状态的数据库中的执行计划。expain出来的信息有10列，分别是id、select\_type、table、type、possible\_keys、key、key\_len、ref、rows、Extra

下面对这些字段出现的可能进行解释：

**1. id**

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。MySQL将 select 查询分为简单查询和复杂查询。复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、union 查询。

**2.select\_type**

(1) SIMPLE(简单SELECT,不使用UNION或子查询等)；

(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)；

(3) UNION(UNION中的第二个或后面的SELECT语句)；

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)；

(5) UNION RESULT(UNION的结果)；

(6) SUBQUERY(子查询中的第一个SELECT)；

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)；

(8) DERIVED(派生表的SELECT, FROM子句的子查询)；

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)。

**3. table**

这一列表示 explain 的一行正在访问哪个表。当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有union时，UNION RESULT的table列的值为 <union1,2>，1和2表示参与 union 的select行id。

**4. type**

表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有：ALL, index,  range, ref, eq\_ref, const, system, NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。相比 eq\_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

eq\_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

**5. possible\_keys**

这一列显示查询可能使用哪些索引来查找。

explain 时可能出现 possible\_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，MySQL认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

**6. key**

key列显示MySQL实际决定使用的键（索引）

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible\_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

**7. key\_len**

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key\_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key\_len是根据表定义计算而得，不是通过表内检索出的）。

不损失精确性的情况下，长度越短越好。

**8. ref**

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

**9. rows**

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，这个不是结果集里的行数。

**10. Extra**

该列包含MySQL解决查询的详细信息,有以下几种情况：

Using index：这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录，是性能高的表现。

Using where：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤。

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。

Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”，对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行。

***执行explain语句***

explain select \* from tb\_article where author\_id=20 and title='b';

![3fc8b52eb95259f3bec855f351ebe1a1.png][]

可以发现，执行这条SQL语句实际上没有走index\_title索引，而是选择走index\_author\_id索引。

打开optimizer trace功能：SET optimizer\_trace="enabled=on";

select \* from information\_schema.optimizer\_trace\\G;

![b09959b3daf32c9356a0ec1d8e3951b6.png][]

执行计划最终选择了index\_author\_id索引，原因是index\_author\_id的cost小于index\_title。这里需要介绍MySQL的代价计算模型。

**MySQL代价模型**

总代价模型：COST = CPU Cost + IO Cost

MySQL在cost类型上分为IO、CPU和Memory，MySQL5.7的代价模型还在完善中，Memory的代价虽然已经收集了，但还没有计算在最终的代价中。

MySQL5.7在源码上对cost模型进行了大量重构，代价分为server层和engine层。server层主要是CPU代价，而engine层主要是IO代价。MySQL5.7 引入了两个系统表mysql.server\_cost和mysql.engine\_cost来分别配置这两个层的代价。

以下分析均基于MySQL5.7.10

***server\_cost***

1. row\_evaluate\_cost (default 0.2) 计算符合条件的行的代价，行数越多，此项代价越大；

2. memory\_temptable\_create\_cost (default 2.0) 内存临时表的创建代价；

3. memory\_temptable\_row\_cost (default 0.2) 内存临时表的行代价；

4. key\_compare\_cost (default 0.1) 键比较的代价，例如排序；

5. disk\_temptable\_create\_cost (default 40.0) 内部myisam或innodb临时表的创建代价；

6. disk\_temptable\_row\_cost (default 1.0) 内部myisam或innodb临时表的行代价；

可以看出创建临时表的代价是很高的，尤其是内部的myisam或innodb临时表。

***engine\_cost***

1. io\_block\_read\_cost (default 1.0) 从磁盘读数据的cost，对innodb来说，表示从磁盘读一个page的cost；

2. memory\_block\_read\_cost (default 1.0）；

从内存读数据的cost，对innodb来说，表示从buffer pool读一个page的cost。

目前io\_block\_read\_cost和memory\_block\_read\_cost默认值均为1，实际生产中建议酌情调大memory\_block\_read\_cost，特别是对普通硬盘的场景。

对表tb\_article创建复合索引index\_title\_author

ALTER TABLE tb\_article ADD KEY index\_title\_author(\`title\`,\`author\_id\`);

select \* from tb\_article where author\_id=20 and title='b';

index\_author\_id和index\_title\_author的cost相等，MySQL会优先选择叶子块数量较少的索引。

![dc02261c6b1a6d437f28682ab33cb390.png][]

对于SQL语句：select title, author\_id from tb\_article where author\_id=20 and title='b';

MySQL会优先选择走复合索引index\_title\_author，原因是index\_title\_author是索引覆盖扫描，不需要回表，性能较高。

![896c17fc40001a0ed15e6581ad7e9f59.png][]

[24571c93e5ffbfcebdeb9b62bee82355.png]: /images/20221120/a0e0d6df019c402f9eef1699033ce0cb.png
[fa6e787961a3409365ec047c4a55a663.png]: /images/20221120/f960d55790904d08a6e2e0ba119d512c.png
[d8d3d2f2fdf7284272128c03b7b69742.png]: /images/20221120/e4af2ac7dedc4c5e8cd1240c4f73408b.png
[919f3734f07e8afb6b09ea16c4d62df5.png]: /images/20221120/4b6f3f4b98554119952791a941d56fef.png
[3fc8b52eb95259f3bec855f351ebe1a1.png]: /images/20221120/5471378449254ddf9509f489c179a31e.png
[b09959b3daf32c9356a0ec1d8e3951b6.png]: /images/20221120/c165bb5cf1e441a4a798aef99a2bffc8.png
[dc02261c6b1a6d437f28682ab33cb390.png]: /images/20221120/deda2ac2bccb4e1ab49a170529dff31c.png
[896c17fc40001a0ed15e6581ad7e9f59.png]: /images/20221120/b72a7df96aee4b4d8283af9cf634ac92.png