cost量化分析

浙江好梦来投资

• GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
  
• GreatSQL是MySQL的国产分支版本，使用上与MySQL一致。
  
• 作者： xryz
  
• 文章来源：GreatSQL社区原创
  

前言：

我们在日常维护数据库的时候，经常会遇到查询慢的语句，这时候一般会通过执行EXPLAIN去查看它的执行计划，但是执行计划往往只给我们带来了最基础的分析信息，比如是否有使用索引，还有一些其他供我们分析的信息，比如使用了临时表、排序等等，却无法展示为什么一些其他的执行计划未被选择，比如说明明有索引，或者好几个索引，但是为什么查询时未使用到期望的索引等

1. explain select * from basic_person_info t1 join basic_person_info2 t2 on t1.id_num=t2.id_num where t1.age >10 and t2.age<20;
2. +----+-------------+-------+------------+--------+--------------------------------------+---------------+---------+----------------+------+----------+-----------------------+
3. | id | select_type | table | partitions | type   | possible_keys                        | key           | key_len | ref            | rows | filtered | Extra                 |
4. +----+-------------+-------+------------+--------+--------------------------------------+---------------+---------+----------------+------+----------+-----------------------+
5. |  1 | SIMPLE      | t2    | NULL       | range  | id_num_unique,idx_age,idx_age_id_num | idx_age       | 1       | NULL           | 9594 |   100.00 | Using index condition |
6. |  1 | SIMPLE      | t1    | NULL       | eq_ref | id_num_unique,idx_age                | id_num_unique | 60      | test.t2.id_num |    1 |    50.00 | Using where           |
7. +----+-------------+-------+------------+--------+--------------------------------------+---------------+---------+----------------+------+----------+-----------------------+
8. 2 rows in set, 1 warning (0.01 sec)

复制代码

• optimizer_trace： enabled 开启/关闭optimizer_trace，one_line 是否单行显示，关闭为json模式，一般不开启
  
• optimizer_trace_features：跟踪信息中可打印的项，一般不调整默认打印所有项
  
• optimizer_trace_limit：存储的跟踪sql条数
  
• optimizer_trace_offset：开始记录的sql语句的偏移量，负数表示从最近执行倒数第几条开始记录
  
• optimizer_trace_max_mem_size：optimizer_trace内存的大小，如果跟踪信息超过这个大小，信息将会被截断
  

optimizer_trace表信息：

该表总共有4个字段

• QUERY 表示我们的查询语句。
  
• TRACE 表示优化过程的JSON格式文本。(重点关注)
  
• MISSING_BYTES_BEYOND_MAX_MEM_SIZE 由于优化过程可能会输出很多，如果超过某个限制时，多余的文本将不会被显示，这个字段展示了被忽略的文本字节数。
  
• INSUFFICIENT_PRIVILEGES 表示是否没有权限查看优化过程，默认值是0，只有某些特殊情况下才会是 1，我们暂时不关心这个字段的值。
  

信息解读：

通过 optimizer_trace表的query字段可以看到，一条语句的执行过程主要分为三个步骤：

1. # 打开optimizer trace功能 (默认情况下它是关闭的):
2. set optimizer_trace="enabled=on";
3. select ...; # 这里输入你自己的查询语句
4. SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
5. # 当你停止查看语句的优化过程时，把optimizer trace功能关闭
6. set optimizer_trace="enabled=off";

复制代码

各个步骤的详细内容解读：

• preparation：
  

1. mysql>  show variables like '%optimizer_trace%';
2. +------------------------------+----------------------------------------------------------------------------+
3. | Variable_name                | Value                                                                      |
4. +------------------------------+----------------------------------------------------------------------------+
5. | optimizer_trace              | enabled=on,one_line=off                                                    |
6. | optimizer_trace_features     | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
7. | optimizer_trace_limit        | 1                                                                          |
8. | optimizer_trace_max_mem_size | 1048576                                                                    |
9. | optimizer_trace_offset       | -1                                                                         |
10. +------------------------------+----------------------------------------------------------------------------+

复制代码

• optimization：
  

1. "join_preparation": {},（准备阶段）
2. "join_optimization": {},（优化阶段）
3. "join_execution": {},（执行阶段）

复制代码

trace信息中的json信息很长，因为我们关心的是不同执行计划的cost区别，所以只需要重点关注两个部分rows_estimation 和considered_execution_plans
代价模型计算：

统计信息和cost计算参数：

计算cost会涉及到表的主键索引数据页(聚簇索引)数量和表中的记录数，两个信息都可以通过innodb的表统计信息mysql.innodb_table_stats查到，n_rows是记录数，clustered_index_size是聚簇索引页数。

1. expanded_query ：将语句进行格式化，补充隐藏的列名和表名等
2. transformations_to_nested_joins ：查询重写，比如join的on改为where语句

复制代码

代价模型将操作分为Server层和Engine（存储引擎）层两类，Server层主要是CPU代价，Engine层主要是IO代价，比如MySQL从磁盘读取一个数据页的代价io_block_read_cost为1，从buffer pool读取的代价memory_block_read_cost为0.25，计算符合条件的行代价为row_evaluate_cost为0.1，除此之外还有：

• memory_temptable_create_cost (default 1.0) 内存临时表的创建代价。
  
• memory_temptable_row_cost (default 0.1) 内存临时表的行代价。
  
• key_compare_cost (default 0.1) 键比较的代价，例如排序。
  
• disk_temptable_create_cost (default 20.0) 内部myisam或innodb临时表的创建代价。
  
• disk_temptable_row_cost (default 0.5) 内部myisam或innodb临时表的行代价。
  

这些都可以通过mysql.server_cost、mysql.engine_cost查看defalt值和设置值

1. condition_processing{ ：条件句处理。
2.     transformation{：转换类型句。这三次的转换分别是
3.         equality_propagation（等值条件句转换），如：a = b and b = c and c = 5
4.         constant_propagation（常量条件句转换），如：a = 1 AND b > a
5.         trivial_condition_removal（无效条件移除的转换），如：1 = 1
6.     }
7. }
8. substitute_generated_columns ：替换虚拟生成列，测试了很多sql，这一列都没有看到有用的信息
9. table_dependencies ：梳理表之间的依赖关系。
10. ref_optimizer_key_uses ：如果优化器认为查询可以使用ref的话，在这里列出可以使用的索引。
11. rows_estimation{ ：估算表行数和扫描的代价。如果查询中存在range扫描的话，对range扫描进行计划分析及代价估算。
12.   table_scan：全表扫描的行数(rows)以及所需要的代价(cost)。
13.   potential_range_indexes：该阶段会列出表中所有的索引并分析其是否可用，并且还会列出索引中可用的列字段。
14.   analyzing_range_alternatives ：分析可选方案的代价。
15. }
16. considered_execution_plans{ ：对比各可行计划的代价，选择相对最优的执行计划。
17.   plan_prefix：前置的执行计划。
18.   best_access_path：当前最优的执行顺序信息结果集。
19.   access_type表示使用索引的方式，可参照为explain中的type字段。
20.   condition_filtering_pct：类似于explain中的filtered列，这是一个估算值。
21.   rows_for_plan：该执行计划最终的扫描行数，这里的行数其实也是估算值，是由considered_access_paths的resulting_rows相乘之后再乘以condition_filtering_pct获得。
22.   cost_for_plan：该执行计划的执行代价，由considered_access_paths的cost相加而得。
23.   chosen：是否选择了该执行计划。
24. }
25. attaching_conditions_to_tables ：添加附加条件，使得条件尽可能筛选单表数据。
26. refine_plan ：优化后的执行计划。

复制代码

计算公式：

如上面介绍的一样，代价模型将操作分为两类io_cost和cpu_cost，io_cost+cpu_cost就是总的cost，下面是具体的计算方法：
全表扫描：

全表扫描成本 = io_cost + 1.1 + cpu_cost + 1 (io_cost +1.1和cpu_cost +1在代码里是直接硬加上的，不知道为什么，计算的时候直接加上)
io_cost = clustered_index_size (统计信息中的主键页数) * avg_single_page_cost(读取一个页的平均成本)
avg_single_page_cost = pages_in_memory_percent * 0.25(memory_block_read_cost) + pages_on_disk_percent * 1.0(io_block_read_cost)
pages_in_memory_percent 表示已经加载到 Buffer Pool 中的叶结点占所有叶结点的比例 pages_on_disk_percent 表示没有加载到 Buffer Pool 中的叶结点占所有叶结点的比例
所以当数据已经全部读取到buffer pool中的时候：
io_cost=clustered_index_size * 0.25
都没有读取到buffer pool中的时候：
io_cost=clustered_index_size * 1.0
当部分数据在buffer pool中，部分数据需要从磁盘读取时，这时的系数介于0.25到1之间
cpu_cost = n_rows(统计信息中记录数) * 0.1(row_evaluate_cost)
走索引的成本：

和全表扫描的计算方法类似，其中io_cost与搜索的区间数有关，比如扫描三个区间where a between 1 and 10  or  a between 20 and 30 or a between 40 and 50，此时：
io_cost=3 * avg_single_page_cost
cpu_cost=记录数 * 0.1(row_evaluate_cost)+0.01(代码中的微调参数)
针对二级索引还会有回表的操作：
MySQL认为每次回表都相当于是访问一个页面，所以每次回表都会进行一次IO，这部分成本：
io_cost=rows(记录数)*avg_single_page_cost
对回表查询的数据还需要进行一次计算：
cpu_cost=rows(记录数) *  0.1(row_evaluate_cost)(需要注意的是当索引需要回表扫描时，在rows_estimation阶段并不会计算这个值，在considered_execution_plans阶段会重新加上这部分成本)
所以针对需要回表的查询：
io_cost=查询区间 * avg_single_page_cost + rows(记录数) * avg_single_page_cost
cpu_cost=记录数 * 0.1(row_evaluate_cost) + 0.01(代码中的微调参数) + rows(记录数) * 0.1(row_evaluate_cost)
例子：

1. mysql> select * from mysql.innodb_table_stats where table_name='basic_person_info';
2. +---------------+-------------------+---------------------+--------+----------------------+--------------------------+
3. | database_name | table_name        | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
4. +---------------+-------------------+---------------------+--------+----------------------+--------------------------+
5. | test          | basic_person_info | 2022-12-23 18:27:24 |  86632 |                  737 |                     1401 |
6. +---------------+-------------------+---------------------+--------+----------------------+--------------------------+
7. 1 row in set (0.01 sec)

复制代码

其中 default_value的值是系统默认的，不能修改，cost_value列的值我们可以修改，如果cost_value列的值不为空系统将用该值覆盖默认值，我们可以通过update语句来修改

1. mysql> select * from mysql.server_cost;
2. +------------------------------+------------+---------------------+---------+---------------+
3. | cost_name                    | cost_value | last_update         | comment | default_value |
4. +------------------------------+------------+---------------------+---------+---------------+
5. | disk_temptable_create_cost   |       NULL | 2022-05-11 16:09:37 | NULL    |            20 |
6. | disk_temptable_row_cost      |       NULL | 2022-05-11 16:09:37 | NULL    |           0.5 |
7. | key_compare_cost             |       NULL | 2022-05-11 16:09:37 | NULL    |          0.05 |
8. | memory_temptable_create_cost |       NULL | 2022-05-11 16:09:37 | NULL    |             1 |
9. | memory_temptable_row_cost    |       NULL | 2022-05-11 16:09:37 | NULL    |           0.1 |
10. | row_evaluate_cost            |       NULL | 2022-05-11 16:09:37 | NULL    |           0.1 |
11. +------------------------------+------------+---------------------+---------+---------------+
12. mysql> select * from mysql.engine_cost;
13. +-------------+-------------+------------------------+------------+---------------------+---------+---------------+
14. | engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |
15. +-------------+-------------+------------------------+------------+---------------------+---------+---------------+
16. | default     |           0 | io_block_read_cost     |       NULL | 2022-05-11 16:09:37 | NULL    |             1 |
17. | default     |           0 | memory_block_read_cost |       NULL | 2023-01-09 11:17:39 | NULL    |          0.25 |
18. +-------------+-------------+------------------------+------------+---------------------+---------+---------------+

复制代码

很多资料都说执行flush optimizer_costs就可以生效，不过我在修改完后并执行flush optimizer_costs并不能马上生效，最后是通过重启数据库实例才生效，这个可能是数据库版本的差异，大家可以自行验证。

1. mysql> set optimizer_trace='enabled=on';
2. Query OK, 0 rows affected (0.00 sec)
4. mysql>explain select * from basic_person_info t1 join basic_person_info2 t2 on t1.id_num=t2.id_num where t1.age >10 and t2.age<20;
5. +----+-------------+-------+------------+--------+--------------------------------------+---------------+---------+----------------+------+----------+-----------------------+
6. | id | select_type | table | partitions | type   | possible_keys                        | key           | key_len | ref            | rows | filtered | Extra                 |
7. +----+-------------+-------+------------+--------+--------------------------------------+---------------+---------+----------------+------+----------+-----------------------+
8. |  1 | SIMPLE      | t2    | NULL       | range  | id_num_unique,idx_age,idx_age_id_num | idx_age       | 1       | NULL           | 9594 |   100.00 | Using index condition |
9. |  1 | SIMPLE      | t1    | NULL       | eq_ref | id_num_unique,idx_age                | id_num_unique | 60      | test.t2.id_num |    1 |    50.00 | Using where           |
10. +----+-------------+-------+------------+--------+--------------------------------------+---------------+---------+----------------+------+----------+-----------------------+
11. 2 rows in set, 1 warning (0.04 sec)

复制代码

总结：

• MySQL的优化器是基于成本来选择最优执行方案的，哪个成本最少就选哪个，所以重点在于计算出各个执行计划的cost
  
• 成本由CPU成本和IO成本组成，每个成本常数值可以自己调整，非必要的情况下不要调整，以免影响整个数据库的执行计划选择
  
• 通过开启optimizer_trace可以跟踪优化器的各个环节的分析步骤，可以判断有时候为什么没有走索引而走了全表扫描
  
• explain加上format=json选项后可以查看成本信息分为read_cost和eval_cost，但只能看到当前已经选择的执行计划，另外通过show warnings可以看到优化器改写后的语句
  

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/17204186.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！