基于代价的SQL慢查询优化建议
总第503篇
2022年 第020篇
1 背景
2 基于代价的优化器介绍
2.1 SQL执行与优化器
2.2 代价模型介绍
2.3 基于代价的索引选择
2.4 基于代价的索引推荐思路
3 索引推荐实现
3.1 前置校验
3.2 提取关键列名
3.3 生成候选索引
3.4 数据采集
3.5 统计数据计算
3.6 候选索引代价评估
4 推荐质量保证
4.1 有效性验证
4.2 效果追踪
4.3 仿真环境
4.4 测试案例库
5 慢查询治理运营
5.1 过去-历史慢查询
5.2 现在-新增慢查询
5.3 未来-潜在慢查询
6 项目运行情况
7 未来规划
1 背景
select * from sync_test1 where name like 'Bobby%'
，直接添加索引IX(
name
) 就可以取得不错的效果；但对于稍微复杂点的SQL，如
select * from sync_test1 where name like 'Bobby%' and dt > '2021-07-06'
，到底选择IX(
name
)、IX(
dt
)、IX(
dt,name
) 还是IX(
name,dt
)，该方法也无法给出准确的回答。更别说像多表Join、子查询这样复杂的场景了。所以采用基于代价的推荐来解决该问题会更加普适，因为基于代价的方法使用了和数据库优化器相同的方式，去量化评估所有的可能性，选出的是执行SQL耗费代价最小的索引。
2 基于代价的优化器介绍
2.1 SQL执行与优化器
2.2 代价模型介绍
-
memory_temptable_create_cost ( default 2.0 ) 内存临时表的创建代价。 -
memory_temptable_row_cost ( default 0.2 ) 内存临时表的行代价。 -
key_compare_cost ( default 0.1 ) 键比较的代价，例如排序。 -
disk_temptable_create_cost ( default 40.0 ) 内部myisam或innodb临时表的创建代价。 -
disk_temptable_row_cost ( default 1.0 ) 内部myisam或innodb临时表的行代价。
2.3 基于代价的索引选择
SQL select * from sync_test1 where name like 'Bobby%' and dt > '2021-07-06'
为例，我们看看MySQL优化器是如何根据代价模型选择索引的。首先，我们直接在建表时加入四个候选索引。
Create Table: CREATE TABLE `sync_test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cid` int(11) NOT NULL,
`phone` int(11) NOT NULL,
`name` varchar(10) NOT NULL,
`address` varchar(255) DEFAULT NULL,
`dt` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `IX_name` (`name`),
KEY `IX_dt` (`dt`),
KEY `IX_dt_name` (`dt`,`name`),
KEY `IX_name_dt` (`name`,`dt`)
) ENGINE=InnoDB
mysql> explain select * from sync_test1 where name like 'Bobby%' and dt > '2021-07-06';
+----+-------------+------------+------------+-------+-------------------------------------+---------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-------------------------------------+---------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | sync_test1 | NULL | range | IX_name,IX_dt,IX_dt_name,IX_name_dt | IX_name | 12 | NULL | 572 | 36.83 | Using index condition; Using where |
+----+-------------+------------+------------+-------+-------------------------------------+---------+---------+------+------+----------+------------------------------------+
mysql> select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE\G;
*************************** 1. row ***************************
TRACE: {
...
"rows_estimation": [
{
"table": "`sync_test1`",
"range_analysis": {
"table_scan": {
"rows": 105084,
"cost": 21628
},
...
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "IX_name",
"ranges": [
"Bobby\u0000\u0000\u0000\u0000\u0000 <= name <= Bobbyÿÿÿÿÿ"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 572,
"cost": 687.41,
"chosen": true
},
{
"index": "IX_dt",
"ranges": [
"0x99aa0c0000 < dt"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 38698,
"cost": 46439,
"chosen": false,
"cause": "cost"
},
{
"index": "IX_dt_name",
"ranges": [
"0x99aa0c0000 < dt"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 38292,
"cost": 45951,
"chosen": false,
"cause": "cost"
},
{
"index": "IX_name_dt",
"ranges": [
"Bobby\u0000\u0000\u0000\u0000\u0000 <= name <= Bobbyÿÿÿÿÿ"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 572,
"cost": 687.41,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "IX_name",
"rows": 572,
"ranges": [
"Bobby\u0000\u0000\u0000\u0000\u0000 <= name <= Bobbyÿÿÿÿÿ"
]
},
"rows_for_plan": 572,
"cost_for_plan": 687.41,
"chosen": true
}
...
}
-
走全表扫描的代价：io_cost + cpu_cost = （数据页个数 * io_block_read_cost）+ (数据行数 * row_evaluate_cost + 1.1) = （data_length / block_size + 1）+ (rows * 0.2 + 1.1) = (9977856 / 16384 + 1) + (105084 * 0.2 + 1.1) = 21627.9。 -
走二级索引IX_name的代价：io_cost + cpu_cost = (预估范围行数 * io_block_read_cost + 1) + (数据行数 * row_evaluate_cost + 0.01) = (572 * 1 + 1) + (572*0.2 + 0.01) = 687.41。 -
走二级索引IX_dt的代价：io_cost + cpu_cost = (预估范围行数 * io_block_read_cost + 1) + (数据行数 * row_evaluate_cost + 0.01) = (38698 * 1 + 1) + (38698*0.2 + 0.01) = 46438.61。 -
走二级索引IX_dt_name的代价: io_cost + cpu_cost = (预估范围行数 * io_block_read_cost + 1) + (数据行数 * row_evaluate_cost + 0.01) = (38292 * 1 + 1) + (38292 * 0.2 + 0.01) = 45951.41。 -
走二级索引IX_name_dt的代价：io_cost + cpu_cost = (预估范围行数 * io_block_read_cost + 1) + (数据行数 * row_evaluate_cost + 0.01) = (572 * 1 + 1) + (572*0.2 + 0.01) = 687.41。
计算结果在小数上有偏差，因为MySQL使用%g打印浮点数，小数会以最短的方式输出。
除“+1.1 +1”这种调节值外，Cost计算还会出现+0.01, 它是为了避免index scan和range scan出现Cost的竞争。
Cost计算是基于MySQL的默认参数配置，如果Cost Model参数改变，optimizer_switch的选项不同，数据分布不同都会导致最终Cost的计算结果不同。
data_length可查询information_schema.tables，block_size默认16K。
2.4 基于代价的索引推荐思路
3 索引推荐实现
3.1 前置校验
3.2 提取关键列名
select * from tb1, tb2 where a = 1，列a归属tb1还是tb2取决于谁唯一包含列a。
-
select * from tb1 natural join tb2 where tb1.a = 1，在自然连接中，tb1和tb2默认使用了相同列名进行连接，但SQL中并没有暴露出这些可用于添加索引的列。
3.3 生成候选索引
-
已经存在的索引，如存在AB，需排除AB、A，因为MySQL支持使用前缀索引。 -
超过最大索引长度3072字节限制的索引。 -
一些暂时不支持的索引，如带地理数据类型列的空间索引。
3.4 数据采集
-
元数据：即表的定义数据，包括列定义、索引定义，可通过show create table获取。 -
统计数据：如表的行数、表数据大小、索引大小，可以通过查询infromation_schema.tables获取；已存在索引的cardinality（关键值：即索引列的不同值个数，值越大，索引优化效果越明显），可以通过查询mysql.innodb_index_stats表获取。 -
样本数据：候选索引为假索引，采集的统计数据并不包含假索引的数据，这里我们通过采集原表的样本数据来计算出假索引的统计数据。
select * from table where rand() < rate
，然而它会给线上数据库造成大量I/O的问题，严重时可引发数据库故障。所以我们采用了基于块的采样方式：它参考了MySQL 8.0的直方图采样算法，如对于一张100万的表，采集10万行数，根据主键的最小值最大值将表数据均分成100个区间，每个区间取一块1000行数据，采集数据的SQL，最后将采集到的数据塞入采样表中。代码如下：
select A,B,C,id from table where id >= 1000 and id <= 10000 limit 1000;
select A,B,C,id from table where id >= 10000 and id <= 20000 limit 1000;
...
3.5 统计数据计算
select * from table1 where A > 100 and B < 1000
，候选索引A、B来说，优化器会调用此函数在索引页A上估算A > 100有多少行数，在索引页B上估计B<1000的行数，例如满足条件的A有200行，B有50行，那么优化器会优先选择使用索引B。对于假索引来说，我们按照该公式：样本满足条件的范围行数 * (原表行数 / 样本表行数)，直接样本数据中查找，然后按照采样比例放大即可估算出原表中满足条件的范围行数。
-
第一趟计算：取样本数据一半来统计A的不同值个数R1，区间[min_id, min_id+(max_id - min_id) / 2]。 -
第二趟计算：取所有样本据统计A的不同值个数R2，区间[min_id, max_id] 计算斜率：R2/R1。 -
判断斜率：如果斜率小于1.1，为固定值100，否则根据采样比例放大，为10,000。
3.6 候选索引代价评估
-
建包含候选索引的表：将候选索引塞入原表定义，并把存储引擎改为Fakeindex，在推荐引擎的mysqld上创建表。 -
通过在推荐引擎mysqld上explain format=json SQL，获取优化器选择的索引。
候选索引代价评估
4 推荐质量保证
-
索引推荐计算出的Cost严重依赖样本数据的质量，在当表数据分布不均或数据倾斜时会导致统计数据出现误差，导致推荐出错误索引。 -
索引推荐系统本身存在缺陷，从而导致推荐出错误索引。 -
MySQL优化器自身存在的缺陷，导致推荐出错误索引。
4.1 有效性验证
4.2 效果追踪
4.3 仿真环境
4.4 测试案例库
-
利用美团线上的丰富数据，以影响MySQL索引选择的因素特征为抓手，直接从全量SQL和慢SQL中抽取最真实的案例，不断更新现有测试案例库。 -
在生产的推荐系统链路上埋点，自动收集异常案例，回流到现有的测试案例库。 -
对于现有数据没有覆盖到的极端场景，采用人为构造的方案，补充测试用例。
5 慢查询治理运营
5.1 过去-历史慢查询
-
核心数据库：该类慢查询通常会被周期性地关注，如慢查询周报、月报，可直接将优化建议提前生成出来，接入它们，一并运营治理。 -
普通数据库：可将优化建议直接接入数据库平台的慢查询模块，让研发自助地选择治理哪些慢查询。
5.2 现在-新增慢查询
-
影响程度一般的慢查询：可通过实时分析慢查询日志，对比历史慢查询，识别出新增慢查询，并生成优化建议，为用户创建数据库风险项，跟进治理。 -
影响程度较大的慢查询：该类通常会引发数据库告警，如慢查询导致数据库Load过高，可通过故障诊断根因系统，识别出具体的慢查询SQL，并生成优化建议，及时推送到故障处理群，降低故障处理时长。
5.3 未来-潜在慢查询
-
未上线的准慢查询：项目准备上线而引入的新的准慢查询，可接入发布前的集成测试流水线，Java项目可通过 agentmain的代理方式拦截被测试用例覆盖到的SQL，再通过经验+explain识别出慢查询，并生成优化建议，给用户在需求管理系统上创建缺陷任务，解决后才能发布上线。 -
已上线的准慢查询：该类属于当前执行时间较快的SQL，随着表数据量的增加，会演变成慢查询，最常见的就是全表扫描，这类可通过增加慢查询配置参数log_queries_not_using_indexes记录到慢日志，并生成优化建议，为用户创建数据库风险项，跟进治理。
6 项目运行情况
7 未来规划
参考资料
-
MySQL Writing a Custom Storage Engine -
MySQL Optimizer Guide -
MySQL 直方图 -
Golang cgo -
阿里云-DAS之基于Workload的全局自动优化实践 -
SQL诊断优化，以后就都交给数据库自治服务DAS吧