MySQL执⾏成本是如何计算的？
1. 前⾔
我们知道，全表扫描适⽤于任何查询，这是最简单也是最笨拙的⼀种查询⽅式，它的缺点是当表中数据量较⼤时性能就会⾮常差，因为需要扫描整棵聚簇索引B+树的叶⼦节点，涉及到⼤量的磁盘IO和CPU计算。为了解决全表扫描的性能问题，我们可以给条件列加上索引，这样就可以形成⼀个较⼩的扫描区间，过滤掉绝⼤部分的记录，从⽽提⾼查询效率。如果过滤条件⼗分复杂，涉及到多个列，我们还可以给多个列都加上索引，MySQL会判断可能会使⽤到哪些索引，以及使⽤这些索引对应的执⾏成本是多少，最终选择成本最低的索引⽅案，这就是所谓的执⾏计划。最后⼀步根据执⾏计划调⽤存储引擎提供的接⼝，读取数据并返回给客户端了。
流程我们是清楚了，但是这⾥还有⼏个问题。
使⽤不同索引的执⾏成本⾥的「成本」到底是什么？
MySQL⼜是如何去计算这些所谓的「成本」的？
计算的「成本」是否准确？开销⼤不⼤？
优化器为何如此智能，可以⾃动选择更优的索引？
优化器会做出错误的决策吗？
明明使⽤索引A效率更⾼，为什么MySQL错误的使⽤了索引B？
我们可以修改优化器的决策吗？
⼤家可以思考⼀下这些问题，本篇⽂章重点分析「成本」的计算规则，相信看完会解决你的⼤部分疑惑，但有的问题还是需要你们⾃⼰去思考。
2. 执⾏成本
在了解成本是如何被计算出来的之前，我们先知晓⼀下，成本到底是什么？
⼀条查询语句在MySQL中的执⾏成本，主要由两部分组成：
IO成本：记录存储在磁盘上，检索记录⾸先需要将记录从磁盘加载到内存，然后才能进⾏操作。
CPU成本：记录加载到内存后，CPU负责读取记录、判断是否符合过滤条件、对结果集排序等等操作。
对于InnoDB引擎，有两个⾮常重要的成本常数，⼤家要牢记在⼼。「页」是磁盘和内存交互的基本单
位，MySQL默认读取⼀个页的成本是1.0，读取以及检测⼀条记录是否符合搜索条件的成本是0.2。
2.1 单表查询成本
我们先看简单的单表查询成本是如何计算的，再看复杂的多表查询。
SELECT*
FROM T
WHERE a BETWEEN1AND100
AND b <100
AND c LIKE'%xx%';
现有如上SQL语句，假设a、b、c列均有索引。MySQL在真正执⾏它之前，⾸先需要经过优化器出所有可以执⾏的⽅案，最终选择成本最低的⽅案，这个最终的⽅案就是执⾏计划，然后根据执⾏计划去调⽤存储引擎层提供的接⼝执⾏真正的查询。过程是这样的：
1. 根据搜索条件，出可能使⽤的索引。
2. 计算全表扫描的成本。
3. 计算使⽤不同索引查询的成本。
4. 选择成本最低的⽅案，执⾏查询。
我们按照这个步骤来分析⼀下，MySQL的⼤体流程。
1、根据搜索条件，出可能使⽤的索引
列a和列b均可以使⽤到索引，但是列c由于使⽤了左侧模糊匹配，所以是不能应⽤到索引的，所以该查询可能使⽤的索引是idx_a和idx_b，执⾏⽅案⼀共有三种：
全表扫描
使⽤idx_a索引到符合条件的记录id，回表查询判断其它条件是否符合。
使⽤idx_b索引到符合条件的记录id，回表查询判断其它条件是否符合。
2、计算全表扫描的成本
全表扫描的本质是扫描整棵聚簇索引B+树，再准确点应该是扫描B+树的所有叶⼦节点，内节点是不需要扫描的，InnoDB只需要从最左侧的叶⼦节点顺序的往后扫描即可。但是MySQL在计算成本时简单粗暴，直接将扫描整棵B+树的成本算进去了，这⾥⼤家要注意⼀下。
要计算全表扫描的成本，⾸先我们必须要知道两件事：1、聚簇索引B+树占⽤了多少页⾯？2、表中有多少条记录？
MySQL如何知道这些数据呢？不还得全表扫描吗？⾮也，执⾏成本只是⼀个预估值，没必要精确计算。MySQL通过表的统计数据就可以计算出来。查看表统计信息的语法如下：
mysql>SHOW TABLE STATUS LIKE'T';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name |Engine| Version | Row_format |Rows| Avg_row_length | Data_length | Max_data_length | Index_length | Data_free |Auto_increment| Create_ti me | Update_time | Check_time | Collation | Checksum | Create_options |Comment|
jsonp使用场景+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+---
-------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| T |InnoDB|10| Dynamic |9981|34|344064|0|376832|14680064|160008|2022-03-0611:47:20|2022-03-13 11:36:55|NULL| utf8_general_ci |NULL|||
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
信息太多了，我们重点关注Rows和Data_length这两列。
Rows：表的记录数量，对于MYISAM引擎它是⼀个准确值，对于InnoDB引擎，很可惜它只是⼀个预估值，实际表T有⼀万多条记录。
怎么写jspData_length：表占⽤的存储空间字节数，对于InnoDB引擎，它代表聚簇索引占⽤的空间字节数。
所以，我们可以推导出聚簇索引占⽤的页⾯数，公式是Data_length/(16*1024)，结果是21个页⾯。因此全表扫描的成本计算是这样的：IO成本：21 * 1.0 + 1.1 = 22.1
CPU成本：9981 * 0.2 + 1.0 = 1997.2
总成本：22.1 + 1997.2 = 2019.3
IO成本⾥最后的的1.1和CPU成本⾥最后的1.0是MySQL规定的微调值，不必在意。
3、计算使⽤不同索引查询的成本
可能使⽤到的索引有idx_a和idx_b，使⽤⼆级索引来帮助查询，涉及到的成本有：
⼆级索引页的IO成本+⼆级索引记录的CPU成本
回表的IO成本+回表记录的CPU成本
我们先来看使⽤idx_a索引的执⾏成本，列a的搜索条件是a BETWEEN 1 AND 100，形成的扫描区间就是[1,100]。**优化器规定，读取⼆级索引的⼀个扫描区间的IO成本，和读取⼀个页⾯的IO成本相同，⽆论它占⽤多少页⾯。**这个是规定，⼤家记住就好了，因此⼆级索引页的IO 成本就是1.0。接下来就是估算⼆级索引过滤后的记录数量了，也就是满⾜a BETWEEN 1 AND 100的记录数量。MySQL是这样预估的：
1. 到idx_a B+树中a=1的第⼀条记录，称为该区间的最左记录，这个过程是极快的。
2. 到idx_a B+树中a=100的最后⼀条记录，称为该区间的最右记录，这个过程也是极快的。
3. 从最左记录向右最多读10个页⾯，如果读到了最右记录，则精确计算区间的记录数。
4. 如果读不到最右记录，说明中间记录⽐较多，则采⽤预估法。对10个页⾯中的记录数取平均值，⽤平均值乘以区间的页⾯数量即可。
索引页的Page Header部分有PAGE_N_RECS属性记录了页中的记录数，因此不⽤遍历每个页⾥的记录。
⼜带来⼀个新的问题，如何计算这个区间的页⾯数量呢？还记得B+树的结构吗？该区间的第0层的叶⼦节点数虽然很多，难以统计，但是我们可以看它们的⽗节点啊，这两个索引页的⽬录项⼤概率是会在同⼀个⽗节点页中的，在⽗节点页中统计区间内有多少页⾯就⾮常容易了，其实就是统计两个⽬录项之间隔了多少个⽬录项记录。
这⾥，我们假设满⾜a BETWEEN 1 AND 100的记录数是50个，则⼆级索引记录的CPU成本是50 * 0.2 + 0.01 = 10.01。
mysql语句的执行顺序最后的0.01是MySQL规定的微调值，不必在意。
接下来就是这50条记录回表的IO成本了，MySQL规定，每次回表的IO成本相当于读取⼀个页⾯的IO成本，⼆级索引过滤出的记录数量就是回表的次数。因此，回表的IO成本是50 * 1.0 = 50.0。
回表后读取到的完整的⽤户记录，还需要判断是否符合其它搜索条件，因此还有⼀个CPU成本是50 *
0.2 = 10.0。
综上所述，使⽤idx_a索引的执⾏成本是：
IO成本：1.0 + 50.0 = 51.0。
CPU成本：10.01 + 10.0 = 20.01。
总成本：51.0 + 20.01 = 71.01。
很显然，使⽤idx_a索引的执⾏成本⽐全表扫描要低的多，接下来就要看idx_b是否会更低了，否则会采⽤idx_a索引⽅案。
使⽤idx_b索引的成本计算和idx_a类似，就不再细说了。b<100会形成⼀个扫描区间[-∞,100)，对应着读取⼀个页⾯的IO成本，然后这预估个区间的记录数量，最后计算回表的IO成本和CPU成本。我们假设最终使⽤idx_b的总成本是80.0，那么，三种⽅案对应的成本分别是：
执⾏⽅案执⾏成本
全表扫描2019.3请简述linux操作系统的特点
idx_a71.01
idx_b80.0
因此，MySQL最终会采⽤idx_a索引来完成查询。
2.2 基于索引统计数据
我们已经知道，对于⼆级索引的⼀个扫描区间，它的IO成本是1.0，在评估区间的记录数量时，最差的情况下，InnoDB需要读取10个页⾯求平均值来预估。MySQL把这种通过访问⼆级索引B+树来预估区间记录数的⽅式叫作「Index dive」。可以看出，这个操作其实开销并不⼩。有时候，我们的查询SQL往往会导致⼤量的扫描区间，例如往IN条件⾥疯狂塞参数，如下：
SELECT*FROM T WHERE a IN(1,2,10000);
假设IN⾥⾯有⼀万个参数，那么MySQL在预估这些区间的记录数量时，极端情况下⾄少需要读取10000 * 10=100000个页⾯才能得到结果，这个开销是⾮常⼤的，甚⾄会超过全表扫描本⾝的成本。这就太糟糕了，优化器光是分析⼀个执⾏⽅案的成本就要花费这么⼤的开销，显然是不能被接受的。这种场景下，Index dive就不能被使⽤，MySQL转⽽使⽤另⼀种⽅式来快速预估区间内的记录数。
MySQL除了会为每张表维护⼀份统计数据外，也会为每个索引维护⼀份统计数据，查看索引统计数据的语法如下：
mysql>SHOW INDEX FROM T;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |Null| Index_type |Comment| Index_comm ent |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t |0|PRIMARY|1| id | A |9981|NULL|NULL||BTREE|||
| t |1| idx_a |1| a | A |6737|NULL|NULL||BTREE|||
| t |1| idx_b |1| b | A |2|NULL|NULL| YES |BTREE|||
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
每个列代表的含义如下：
列名说明
Table索引所属表的名称
flexible18柔术tNon_unique是否是⾮唯⼀索引，主键和唯⼀索引是0，其它是1
Key_name索引的名称
Seq_in_index索引列在索引中的位置，从1开始计数
Column_name索引列的名称
Collation排序⽅式，A是升序，NULL是降序
Cardinality索引列中不重复值的数量，基数
Sub_part字符串列的前缀索引的字符数
Packed索引列如何被压缩
Null是否允许存储 NULL
Index_type⽤索引的类型
Comment索引列注释信息
Index_comment索引注释信息
我们重点关注Cardinality属性，它代表了索引列中不重复的值的个数，通过表的统计信息中的Rows属性，我们就可以计算出该列的⼀个值在表中平均重复了多少次。计算⽅式是Rows/Cardinality，我们假设结果为2，也就是列a的⼀个值平均重复了两次。那么上述SQL语句中的⼀万个扫描区间，也就对应着10000*2=20000条记录，也就意味着回表的次数是2万。
基于索引统计数据的⽅式预估⼆级索引区间内的记录数，⽐Index dive的⽅式简单的多，效率也⾼的多，缺点是不准确。所以，我们必须在效率和准确性上做权衡，MySQL通过参数eq_range_index_dive_limit来控制单点扫描区间数量超过多少时，放弃Index dive转⽽⽤索引统计数据的⽅式来计算，MySQL5.7默认值是200。
mysql>SHOW VARIABLES LIKE'eq_range_index_dive_limit';
+---------------------------+-------+
| Variable_name |Value|
+---------------------------+-------+
| eq_range_index_dive_limit |200|
+---------------------------+-------+
其实理解了单表查询的计算成本，多表连接的查询成本计算就是依葫芦画瓢了，这⾥就先不赘述了。
2.3 修改成本常数
我们前⾯说过，MySQL默认读取⼀个页⾯的IO成本常数是1.0，读取并检测⼀条记录是否符合搜索条件的CPU成本常数是0.2，除此之外，还有很多成本常数，这些成本常数是可以配置的，MySQL把它们存储在mysql数据库⾥，有两张表分别是server_cost和engine_cost。MySQL架构有Server层和存储引擎层，对于在Server层操作的成本常数就保存在server_cost⾥，对于在存储引擎层操作的成本常数就保存在engine_cost表⾥。
我们先看server_cost，如下：
mysql>SELECT*FROM mysql.server_cost;
+------------------------------+------------+---------------------+---------+
| cost_name | cost_value | last_update |comment|
+------------------------------+------------+---------------------+---------+
| disk_temptable_create_cost |NULL|2018-12-1519:52:11|NULL|
| disk_temptable_row_cost |NULL|2018-12-1519:52:11|NULL|
| key_compare_cost |NULL|2018-12-1519:52:11|NULL|
| memory_temptable_create_cost |NULL|2018-12-1519:52:11|NULL|
| memory_temptable_row_cost |NULL|2018-12-1519:52:11|NULL|
| row_evaluate_cost |NULL|2018-12-1519:52:11|NULL|
+------------------------------+------------+---------------------+---------+
cost_name表⽰成本常数的名称，cost_value代表成本常数的值，为NULL的话MySQL会使⽤默认值。这些成本常数对应的含义如下：cost_name默认值说明
disk_temptable_create_cost40.0创建基于磁盘的临时表的成本
disk_temptable_row_cost 1.0向基于磁盘的临时表写⼊或读取⼀条记录的成本key_compare_cost0.1两条记录做⽐较操作的成本，多⽤在排序上memory_temptable_create_cost 2.0创建基于内存的临时表的成本
memory_temptable_row_cost0.2向基于内存的临时表写⼊或读取⼀条记录的成本row_evaluate_cost0.2检测⼀条记录是否符合搜索条件的成本
这些成本常数的修改应当要⼗分慎重，它会影响MySQL优化器的决策，⽐如增⼤memory_temptable_create_cost的值，MySQL将减少创建基于内存的临时表，更多的采⽤创建基于磁盘的临时表，修改不当反⽽会影响MySQL的效率。
表修改完成后，还需要调⽤FLUSH OPTIMIZER_COSTS让MySQL重新加载成本常数，才能⽣效。
evaluate函数无效怎么回事再看engine_cost表，如下：
mysql>SELECT*ine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+
| engine_name | device_type | cost_name | cost_value | last_update |comment|
+-------------+-------------+------------------------+------------+---------------------+---------+
|default|0| io_block_read_cost |NULL|2018-12-1519:52:11|NULL|
|default|0| memory_block_read_cost |NULL|2018-12-1519:52:11|NULL|
+-------------+-------------+------------------------+------------+---------------------+---------+
engine_name：成本常数适⽤的存储引擎，default代表适⽤于任何存储引擎。
device_type：存储引擎使⽤的设备类型，主要是为了区分机械硬盘和固态硬盘。
engine_cost表数据要少的多，只有两列：
cost_name默认值说明
io_block_read_cost 1.0从磁盘上读取⼀个块对应的成本，对于InnoDB，⼀个块就是⼀个页memory_block_read_cost 1.0从内存中读取⼀个块对应的成本
磁盘和内存的速度天差地别，为啥从磁盘和内存读取⼀个块的成本是⼀样的呢？这主要是因为，MySQL会将读取到的页缓存起来，并不是⽤完就释放掉的，只有当内存不⾜时才会释放掉。如此⼀来，MySQL其实就不能判断你要读取的页是在磁盘⾥，还是在内存⾥，所以⽬前它俩的成本常数是⼀样的。
3. 总结
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论