MySQL优化原理是什么

共计 6029 个字符，预计需要花费 16 分钟才能阅读完成。

这篇文章主要介绍“MySQL 优化原理是什么”，在日常操作中，相信很多人在 MySQL 优化原理是什么问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL 优化原理是什么”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

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

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

实验环境

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

MySQL 数据库版本：5.7.10

创建新表 tb_article，创建了两个索引：index_title、index_author_id，表结构如下：

我们尝试插入一些数据：

现执行 SQL 语句，select * from tb_article where author_id=20 and title= b 分析该 SQL 语句的执行过程和优化策略。

MySQL 执行 SQL 语句过程

一、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

可以发现，执行这条 SQL 语句实际上没有走 index_title 索引，而是选择走 index_author_id 索引。

打开 optimizer trace 功能：

SET optimizer_trace= enabled=on  select * from information_schema.optimizer_trace\G;

执行计划最终选择了 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 会优先选择叶子块数量较少的索引。

对于 SQL 语句：select title, author_id from tb_article where author_id=20 and title= b

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

到此，关于“MySQL 优化原理是什么”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！