MySQL知识总结
《MySQL是怎样运行的》知识总结
12 基于查询成本的优化
表结构
CREATE TABLE single_table (
id int not null auto_increment,
key1 VARCHAR(100),
key2 int,
key3 varchar(100),
key_part1 varchar(100),
key_part2 varchar(100),
key_part3 varchar(100),
common_field varchar(100),
primary key (id),
key idx_key1 (key1),
unique key uk_key2 (key2),
key idx_key3 (key3),
key idx_part (key_part1, key_part2, key_part3)
) ENGINE = INNODB
CHARSET = utf8;
成本
MySQL在执行查询时可以有不同的执行方案，它会选择成本最低的那个执行方案去执行查询，成本是由两个方面组成：IO成本、CPU成本
IO成本：从磁盘到内存的加载过程损耗的时间
在使用MyISAM、InnoDB存储引擎都是将表的数据、索引存储到磁盘上，查询表中的记录时，需要将磁盘的数据或索引加载到内存，然后进行操作
CPU成本：读取记录、检测记录是否满足对应的搜索条件、对结果集进行排序等损耗的时间
对于InnoDB存储引擎来说，页是磁盘和内存进行交互的基本单位，MySQL规定：
- 读取一个页面花费的成本默认（成本常数）是1.0
- 读取及检测一条记录是否符合查询条件的成本默认（成本常数）是0.2
单表查询成本
例子
select *
from single_table
where key1 in ('a', 'b', 'c')
and key2 10
and key2 < 1000
and key3 key2
and key_part1 like '%hello%'
and common_field = '111'
可以使用的索引列为：
- key1，多个单点扫描区间
- key2，单个范围扫描区间
key3中与key比较，不是与常数进行比较
key_part1是使用通配符开头
基于成本的优化步骤
在执行一条单表查询语句前，MySQL的优化器会找出所有可以用来执行这个查询的方案，并对比这些方案，找出成本最低的方案，成本最低的方案就是执行计划，之后再调用存储引擎的接口，真正地执行查询。这个过程是：
- 根据查询条件，找出可能用到的索引
- 计算全表扫描的成本
- 计算使用不同索引进行查询的成本
- 对比各种执行方案，找出成本最低的那个方案
找出可能用到的索引
对于B+树索引来说，只要索引列和常数使用了=、!=、比较运算符、IN、NOT IN、IS NULL 、IS NOT NULL、BETWEEN、LIKE连接，就会产生一个扫描区间，以上的搜索条件都可能用到索引，MySQL把查询中可能用到的索引成为possible keys
计算全表扫描的成本
对于InnoDB存储引擎来说，全表扫描就是把聚簇索引的记录依次与给定的条件进行比较，并把符合条件的记录加入到结果集中，所以需要把聚簇索引记录加载到内存，再检测记录是否符合条件，计算全表扫描时需要两个信息：
- 聚簇索引占用的页面数
- 表中的记录数
- MySQL会为每个表维护一个
统计信息
show table status like 'single_table';
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| single_table | InnoDB | 10 | Dynamic | 9937 | 54 | 540672 | 0 | 1032192 | 4194304 | 10001 | 2022-01-08 20:37:22 | 2022-01-13 22:24:47 | NULL | utf8_general_ci | NULL |
- Row：表中记录数
- 对于MyISAM存储引擎来说，这个数值是精确的
- 对于InnoDB来说，这个值是估算的
- Data_length：表占存储空间的大小
- 对于MyISM存储引擎来说，这个数值是数据文件的大小
- 对于InnoDB存储引擎来说，这个数值是
聚簇索引占用的存储空间大小
聚簇索引页面数量×页面大小
- 全表扫描的总成本计算
- 根据上面的信息可以知道，聚簇索引的页面为33个（540672÷16÷1024）
- IO成本：33×1.0+1.1=34.1
- CPU成本：10108*0.2+1.0=2022.6
- 全表扫描时，是使用了聚簇索引，在查询数据时是获得最左边的叶子节点后，直接
通过叶子节点组成的双向的链表将所有记录遍历，但在计算全表扫描的成本时，是直接使用聚簇索引占用的页面数量来作为IO成本的依据
计算使用索引执行查询的成本
在前面的例子中，已经得知可能用到索引的列为key1、key2，MySQL需要分析单独使用索引进行查询的查询成本，最后还要分析是否出现索引合并。MySQL会先分析使用唯一索引的查询成本，再分析使用普通索引的成本。对于使用索引和回表的查询，MySQL在计算成本时，两方面的数据：扫描区间数量、需要回表的记录数。
- 查询优化器认为读取索引的
一个扫描区间的IO成本和读取一个页面的成本是相同的 - 计算回表的记录数时，会查找区间的
最左记录和最右记录，只要这2个记录相隔不大于10个页面，就可以精确统计出记录数量，否则只沿着最左记录向右移动10个页面，计算每个页面记录数的平均值，然后再乘最左、右相隔的页面数量（相隔的页面数量可以根据目录项中确定）
例子中，对于key2的查询条件的扫描区间是(10, 1000)，记录数为398条
- 使用索引查询数据成本，IO成本为1×1.0=1.0，CPU成本为：398×0.2+0.1=79.7，
- 回表的成本，IO成本为389×1.0=389.0，CPU成本为389×0.2=79.6
key1的查询条件的扫描区间是(‘a’, ‘b’, ‘c’)，记录数为416条
- 索引查询成本，IO成本为3×1.0=3.0，CPU成本为416×0.2+0.01=83.21
- 回表成本，IO成本为416×1.0=416.0，CPU成本为416×0.2=83.2
对比执行方案的查询成本
对比3中执行方案的成本，选择成本最低的执行方案，这里选择key列的索引进行查询。
基于索引统计数据进行成本计算
使用IN语句 容易产生多个单点扫描区间，如果使用的索引不是唯一索引，那么我们不能确定单点扫描区间内的记录有多少条，我们需要获取最左、右的记录之间的记录数量才能进行计算，这种直接访问索引对应的B+树来计算某个扫描区间上的记录数量的方式是index dive。
MySQL提供了系统变量对使用index dive方法进行了限制，默认情况下，当单点扫描的个数大于200时，就不会使用index dive方法记录扫描区间内的记录数量，而是使用索引统计数据进行计算。
查看使用index dive条件限制
# 查看使用index dive条件限制
show variables like '%index_dive%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200 |
+---------------------------+-------+
索引统计数据
# 查看索引统计数据
show index from single_table;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| single_table | 0 | PRIMARY | 1 | id | A | 9937 | NULL | NULL | BTREE | |||
| single_table | 0 | uk_key2 | 1 | key2 | A | 9937 | NULL | NULL | YES | BTREE | ||
| single_table | 1 | idx_key1 | 1 | key1 | A | 4183 | NULL | NULL | YES | BTREE | ||
| single_table | 1 | idx_key3 | 1 | key3 | A | 4209 | NULL | NULL | YES | BTREE | ||
| single_table | 1 | idx_part | 1 | key_part1 | A | 4188 | NULL | NULL | YES | BTREE | ||
| single_table | 1 | idx_part | 2 | key_part2 | A | 8492 | NULL | NULL | YES | BTREE | ||
| single_table | 1 | idx_part | 3 | key_part3 | A | 9905 | NULL | NULL | YES | BTREE |
Cardinality：基数，某列不重复的值，对于InnDB来说，它是一个估算值。
# 表统计信息
show table status like 'single_table';
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| single_table | InnoDB | 10 | Dynamic | 9937 | 54 | 540672 | 0 | 1032192 | 4194304 | 10001 | 2022-01-08 20:37:22 | 2022-01-13 22:24:47 | NULL | utf8_general_ci | NULL |
上述说到，当单点扫描区间的个数大于限制时，就不使用index dive的方式来计算扫描区间的记录数量，而是使用索引的统计数据，使用统计数据包括：
- 表统计数据的
Rows - 索引统计数据的
Cardinality
Rows为9937，key1列的Cardinality为4183，9937÷4189≈2，则key1的单点扫描区间中大约为2条记录，如果IN语句中有10000个单点扫描区间，那就要进行回表操作20000次。
使用统计数据进行计算成本可能与时间成本相差很大。
连接查询的成本
条件过滤
MySQL中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，而被驱动表会被访问多次，显然，查询驱动表得到的记录数（扇出值）越少，对被驱动表的查询也就越少，查询驱动表时，可能用到全表扫描、索引。
使用全表扫描执行单表查询时，计算驱动表扇出值时需要猜测满足全部查询条件的记录数，这个猜测的过程叫做条件过滤，猜测的过程可能用到统计数据或索引。
两表连接成本
连接查询的总成本：单次访问驱动表成本+驱动表扇出值×访问被驱动表的成本。
对于内连接来说，驱动表、被驱动表的位置可以互换。连接查询的成本可能因为驱动表、被驱动表的位置而不同，所以需要考虑表的连接顺序。
例子
select *
from single_table s1
join single_table s2 on s1.key1 = s2.common_field
where s1.key2 10
and s1.key2 < 1000
and s2.key2 1000
and s2.key2 < 2000;
使用s1作为驱动表时，很明显将使用key2对应的索引执行单表查询，然后对被驱动表进行单表查询，查询条件是s2.common_field = 常数、s2.key2 1000 and s2.key2 < 2000，对于s2的查询，将使用到key2的索引。
此时的查询成本为：key2索引访问s1的成本+s1的扇出值×使用key2索引访问s2的成本。
如果使用s2作为驱动表，将使用key2的索引进行单表查询，在对s1表进行查询时，搜索条件是
s1.key1 = 常数、s1.key2 10 and s1.key2 < 1000，可以用到的索引有key1使用ref的访问方法、key2使用range访问方法。ref方法方法的成本衡量方式是使用统计数据进行计算的,一般情况下ref方法方法的成本比range方法方法的成本低。
优化器会从两种连接顺序中选出成本最低的那个，真正去执行查询，可以看出连接查询成本占比较大的部分是扇出值×访问被驱动表的成本，所有优化重点是：
- 减少驱动表的扇出
- 访问被驱动表的成本要低
- 尽量在被驱动表
连接列上建立索引，这样可以使用到ref的访问方法
调节成本常数
除了上述的两个成本常数外，还有其他成本常数
# 存储成本常数的表
show tables from mysql like '%cost%';
+--------------------------+
| Tables_in_mysql (%cost%) |
+--------------------------+
| engine_cost |
| server_cost |
+--------------------------+
server_cost表
select *from mysql.server_cost;
| cost_name | cost_value | last_update | comment | （描述） |
|---|---|---|---|---|
| disk_temptable_create_cost | NULL (40.0) | 2021-09-07 18:49:49 | NULL | |
| disk_temptable_row_cost | NULL (1.0) | 2021-09-07 18:49:49 | NULL | 向磁盘临时表写入或读取一条记录的成本 |
| key_compare_cost | NULL (0.1) | 2021-09-07 18:49:49 | NULL | 两条记录进行比较操作的成本，多用在排序操作中，filesort |
| memory_temptable_create_cost | NULL (2.0) | 2021-09-07 18:49:49 | NULL | |
| memory_temptable_row_cost | NULL (0.2) | 2021-09-07 18:49:49 | NULL | |
| row_evaluate_cost | NULL (0.2) | 2021-09-07 18:49:49 | NULL | 读取并检测一条记录是否符合查询条件的成本，增大这个值，可能会让优化器倾向于使用索引进行而不是全表扫描 |
成本常数为NULL，则为默认值
执行包含distinct、group by、union的子句及某些特殊条件的排序，MySQL可能会创建临时表来辅助完成查询，将相应结果记录插入到临时表，如使用distinct子句进行去重操作。
创建临时表、对临时表进行操作的成本还是很高的。
engine_cost表
select *from mysql.engine_cost;
| engine_name | device_type | cost_name | cost_value | last_update | comment |
|---|---|---|---|---|---|
| default | 0 | io_block_read_cost | NULL (1.0) | 2021-09-07 18:49:50 | NULL |
| default | 0 | memory_block_read_cost | NULL (1.0) | 2021-09-07 18:49:50 | NULL |
- default：这个成本常数适用于所有存储引擎
- io_block_read_cost：从磁盘读取一个块的成本，对于InnoDB存储引擎来说，一个页就是一个块，对于MyISAM存储引擎来说，默认4096字节为一个块
- memory_block_read_cost ：从内存读取一个块的成本
549
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
