如果你对mysql如何生成执行计划，如何选择走哪个索引这类问题比较感兴趣的话，请你继续看下去：
本文将会讲解：
- mysql查询会有哪些成本？
- mysql执行计划生成的步骤？
mysql查询会有哪些成本？
我们知道，mysql的数据和索引是存储在磁盘上的，我们查询数据是，需要将数据页从磁盘上读出，这里就是IO成本。数据或索引读出后，需要检测记录是否满足对应的搜索条件（where条件）、对结果集进行排序等，这里就涉及到CPU成本。
所以，在考虑mysql查询时，我们主要考虑的是IO成本与CPU成本。
一般我们认为读取一个页面花费的成本是1.0，读取并检测一条记录是否符合搜索条件的成本默认为0.2，可见读取一个页面的成本比起读取一个页面内的一条记录的成本大概是5：1
mysql执行计划生成的步骤？
大致可以分为以下步骤：
- 根据搜索条件，找出所有可能使用到的索引
- 计算全表扫描的代价
- 计算使用不同索引执行查询的代价
- 对比各种执行方案的代价，找出成本最低的那一个
在讲解之前，我需要创建一个测试表：
create table test_index
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
KEY idx_key1 (key1),
UNIQUE KEY idx_key2 (key2),
KEY idx_key3 (key3),
KEY idx_key_part (key_part1, key_part2, key_part3)
) Engine = InnoDB CHARSET = utf8mb4;
写一个存储过程往表里插入10w条数据：
DROP PROCEDURE IF EXISTS insert_into_test_index;
DELIMITER //
CREATE PROCEDURE insert_into_test_index()
BEGIN
SET @i := 1;
WHILE @i <= 100000 DO
SET @key1 := concat('key1', @i);
SET @key2 := FLOOR(@i); -- floor函数（如FLOOR(X)）返回小于等于X的最大整数
SET @key3 := concat('key1', @i);
SET @key_part1 := concat('key_part1', @i);
SET @key_part2 := concat('key_part2', @i);
SET @key_part3 := concat('key_part3', @i);
SET @common_field := concat('common_field', @i);
INSERT INTO test_index VALUES (@i, @key1, @key2, @key3, @key_part1, @key_part2, @key_part3, @common_field);
SET @i := @i + 1;
if @i % 100 = 0 then
COMMIT;
end if ;
END WHILE;
END //
CALL insert_into_test_index();
1、找出所有可能使用到的索引可以理解，我们在写sql的时候，都会考虑该mysql可能会走什么索引。
比如explain select * from test_index where key1 > 'key1121' and key1 < 'key1122';，如果key1列有索引，那么就可能走索引。
2、计算全表扫描的代价：计算全表需要扫描整个聚簇索引，将聚簇索引中的所有页面都会陆续加载到内存中，然后读取每条记录进行比较，这就涉及到两个成本 IO成本+CPU成本，在这个过程中，我们需要知道聚簇索引占用的页面数，以及全表的记录数。这几个信息来自于mysql表的统计信息。可通过SHOW TABLE STATUS语句来查看表的统计信息。
如：SHOW TABLE STATUS like '%test_index%'
其中Rows：表示表中的记录条数。在MyIsam存储引擎中，该值是准确的。而在Innodb中，这是一个估计值。（我们看到图中的rows是96957，实际表中有100000条数据，为什么是不准确的呢？文章最后会说）
Data_length: 表示表占用的字节数，对于MyIsam，表示数据文件的大小，对于Innodb来说，表示聚簇索引占用的空间大小。
如果通过全表扫描的方式，我们可以计算一下代价（我们这里认为mysql一页的大小为16K）：
IO成本：12075008/(16*1024) = 737
CPU成本：96957 * 0.2 = 19391.4
总成本 = IO成本 + CPU成本 = 20128.4
3、计算使用不同索引执行查询的代价：比如使用idx_key1（key1上的单索引），我们认为查询一定范围内的二级索引就是访问一个页面（这里为[‘key1121’,‘key1122’]），为1.0，然后要估算在idx_key1上[‘key1121’,‘key1122’]之间有多少条数据，这个过程分为三个步骤：
1、找到’key1121’的位置。（这种查询效率很高，是常数级别的，成本忽略不计）
2、找到’key1122’的位置。（这种查询效率很高，是常数级别的，成本忽略不计）
3、估算[‘key1121’,‘key1122’]之间的数据量。
（这里的估算方式是：假如[‘key1121’,‘key1122’]在一个页面内，那么按照索引，估算速度会很快，如果’key1121’和’key1122’不在一个页面内，就会递归往上找父节点，直到父节点在同一个页面内，然后再估算）
最后估算出数量，在本例中为110.
这里的成本为：1.0 + 110 * 0.2 = 56
然后进行回表，估算回表的成本比较粗暴，认为是回表一条数据为加载一个页面的成本，为1.0。所以回表的成本为，页面加载成本+ 数据访问成本 = 110*1.0 + 110 * 0.2 = 132。
最后，成本 = 访问二级索引成本 + 回表成本 = 56 + 132 = 188
4、对比各种执行方案的代价，找出成本最低的那一个：在这里很明显188 < 20128.4.
所以将会采用走idx_key1的方式。
换一个场景
假如我把sql改成explain select * from test_index where key1 > 'key120' and key1 < 'key129';
再次查看执行计划，发现变为了全表扫描的方式：
这是JSON的输出结果：explain FORMAT = JSON select * from test_index where key1 > 'key120' and key1 < 'key129';
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "20672.80"
},
"table": {
"table_name": "test_index",
"access_type": "ALL",
"possible_keys": [
"idx_key1"
],
"rows_examined_per_scan": 99679,
"rows_produced_per_join": 20751,
"filtered": "20.82",
"cost_info": {
"read_cost": "16522.40",
"eval_cost": "4150.40",
"prefix_cost": "20672.80",
"data_read_per_join": "47M"
},
"used_columns": [
"id",
"key1",
"key2",
"key3",
"key_part1",
"key_part2",
"key_part3",
"common_field"
],
"attached_condition": "((`mysql_study`.`test_index`.`key1` > 'key120') and (`mysql_study`.`test_index`.`key1` < 'key129'))"
}
}
}
我们看到消耗的总成本prefix_cost为20672.80。（这与我们前面算出来的20128.4差不大）。
为什么呢？为什么这里不走索引呢？
我查询了一下，发现该条sql匹配的数据量达到了9998条
我们作为事后诸葛亮，看看mysql当时是怎么选择的。
首先我们认为mysql的统计信息基本是准备的，因此：
查询二级索引的成本 = 1.0 + 9998 * 0.2 = 2000.6
回表成本 = 9998 * 1.0 + 9998 * 0.2 = 11997.6
二级索引的成本 + 回表成本 = 13998.2
我们发现，mysql选择了我们认为成本更高的全表扫描了！
我不相信这个结果，让我们一起来看看是什么原因：
修改sql让其强制走key1列的索引：
explain select * from test_index force index(idx_key1) where key1 > 'key120' and key1 < 'key129';
看看json格式的：
explain FORMAT = JSON select * from test_index force index(idx_key1) where key1 > 'key120' and key1 < 'key129';
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "29053.81"
},
"table": {
"table_name": "test_index",
"access_type": "range",
"possible_keys": [
"idx_key1"
],
"key": "idx_key1",
"used_key_parts": [
"key1"
],
"key_length": "403",
"rows_examined_per_scan": 20752,
"rows_produced_per_join": 20752,
"filtered": "100.00",
"index_condition": "((`mysql_study`.`test_index`.`key1` > 'key120') and (`mysql_study`.`test_index`.`key1` < 'key129'))",
"cost_info": {
"read_cost": "24903.41",
"eval_cost": "4150.40",
"prefix_cost": "29053.81",
"data_read_per_join": "47M"
},
"used_columns": [
"id",
"key1",
"key2",
"key3",
"key_part1",
"key_part2",
"key_part3",
"common_field"
]
}
}
}
我们发现，采用key1索引的成本达到了29053.81，比全表扫描要高，没错，在执行计划看来，采用key1索引的成本的确比全表扫描要高了。
我们看执行计划，采用的是Using index condition，Using index condition表示首先按照key1 > 'key120'扫描key1列索引，此时不直接回表，因为剩余的查询条件是key1 < 'key129'，也是key1列的，此时直接进行索引过滤之后，过滤完之后再进行回表。
值的注意的是，真实查询的结果是9998行，为啥执行计划表明需要回表的rows达到了2万多（20754）？
我们来计算一下此时的成本：
首先，查询二级索引的成本 = 1.0 + 20754* 0.2 = 4151.8
回表成本 = 20754* 1.0 + 20754* 0.2 = 24904.8
总成本为：29056.6。
与Mysql给出的29053.81非常接近，说明我们估算的方法基本是准确的。
那为什么是2万多行呢？估算偏差达到2倍多
这个问题还没有解决！希望小伙伴能够帮忙解答。
考虑是索引两个边界值之间数据个数的估算错误。 可以看下optimizer trace 查看Mysql如何选择执行计划的过程
为什么Innodb的统计信息rows是不准确的？
原因是这个统计信息是mysql选取一定页数的聚簇索引，然后计算这个页中的平均数据量，最后乘上总页数，就是rows的结果。由此可见，如果选取的样本页数越多，估计结果越准确，但效率越低。否则样本页数越少，结果越不准确，但效率越高。选取的页数由innodb_stats_persistent_sample_pages变量控制，默认为20.
show variables like '%innodb_stats_persistent_sample_pages%';
小结
了解mysql基于成本的优化有利于我们对执行计划生成的理解。小伙伴们如果有问题欢迎留言。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
