基于代价的慢查询优化建议
总第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吧
相关文章