mysql回表致索引失效案例讲解

简介

mysql的innodb引擎查询记录时在无法使用索引覆盖的场景下，需要做回表操作获取记录的所需字段。

mysql执行sql前会执行sql优化、索引选择等操作，mysql会预估各个索引所需要的查询代价以及不走索引所需要的查询代价，从中选择一个mysql认为代价最小的方式进行sql查询操作。而在回表数据量比较大时，经常会出现mysql对回表操作查询代价预估代价过大而导致索引使用错误的情况。

案例

示例如下，在5.6版本的mysql、1CPU2G内存的Linux环境下，新建一个测试表，并创建将近200万的记录用于测试。

CREATE TABLE `salary_static` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',  `school_id` int(11) NOT NULL COMMENT '学校id',  `student_id` int(11) NOT NULL COMMENT '毕业生id',  `salary` int(11) NOT NULL DEFAULT '0' COMMENT '毕业薪水',  `year` int(11) NOT NULL COMMENT '毕业年份',  PRIMARY KEY (`id`),  KEY `school_id_key` (`school_id`) USING BTREE,  KEY `year_school_key` (`year`,`school_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='毕业生薪水数据统计';

select school_id,avg(salary) from salary_static where year between 2016 and 2019 group by school_id;

在这里插入图片描述

强制使用year_school_key索引进行查询后发现，该sql的查询时间花费锐减到了0.6秒，比起school_id_key索引的时间减少了10倍。

select school_id,avg(salary) from salary_static force index(year_school_key) where year between 2015 and 2019 group by school_id;

SET optimizer_trace="enabled=on";select school_id,avg(salary) from salary_static where year between 2016 and 2019 group by school_id;SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

重点关注执行计划的json中range_analysis下的内容，这里展示了where范围查询过程中索引选择。table_scan表示全表扫描，预估需要扫描1973546条记录，但是由于全表扫描走聚集索引是顺序IO读，因此每条记录的查询成本很小，最终计算出来的查询成本为399741。range_scan_alternatives表示使用索引的范围查询，year_school_key索引预估需要扫描812174条记录，但是由于需要回表操作导致随机IO读，最终计算出来的查询成本为974610。所以对于where查询过程最终选择全表扫描不走索引。

"range_analysis": {  "table_scan": {"rows": 1973546,"cost": 399741  },  "potential_range_indices": [{  "index": "PRIMARY",  "usable": false,  "cause": "not_applicable"},{  "index": "school_id_key",  "usable": true,  "key_parts": ["school_id","id"  ]},{  "index": "year_school_key",  "usable": true,  "key_parts": ["year","school_id","id"  ]}  ],  "setup_range_conditions": [  ],  "group_index_range": {"chosen": false,"cause": "not_applicable_aggregate_function"  },  "analyzing_range_alternatives": {"range_scan_alternatives": [  {"index": "year_school_key","ranges": [  "2016 <= year <= 2019"],"index_dives_for_eq_ranges": true,"rowid_ordered": false,"using_mrr": false,"index_only": false,"rows": 812174,"cost": 974610,"chosen": false,"cause": "cost"  }],"analyzing_roworder_intersect": {  "usable": false,  "cause": "too_few_roworder_scans"}  }}

全表扫描查询成本

table_scan全表扫描时预估需要扫描1973546条记录，通过show table status like "salary_static"命令可得全表记录为82411520字节（Data_length），innodb每个记录页为16KB即全表扫描需要读取82411520/1024/16 = 5030个记录页。

I/O成本

5030 * 1.0 = 5030

CPU成本

812174 * 0.2 = 162434.8

{    "reconsidering_access_paths_for_index_ordering": {      "clause": "GROUP BY",      "index_order_summary": {        "table": "`salary_static`",        "index_provides_order": true,        "order_direction": "asc",        "index": "school_id_key",        "plan_changed": true,        "access_type": "index_scan"      }    }}

优化

通过分析sql执行过程，可以发现选择索引错误的是因为year_school_key索引回表记录太多导致预估查询成本大于全表扫描最终选择了错误的索引。

因此减少该sql的执行时间，下一步的优化方案是减少该sql的回表操作，即让该sql进行索引覆盖。该sql涉及到的字段只有school_id、salary和year这3个字段，因此创建这3个索引的联合索引，并注意这3个字段在联合索引中的顺序：where过滤语句最先执行，所以year字段在联合索引第一位；group by语句本质上和order by一样，因此排在where后面即联合索引第二位；salary仅仅为了减少回表因此放在联合索引末位。

CREATE INDEX year_school_salary_key ON salary_static (year, school_id, salary);

在这里插入图片描述

回表率计算

上述问题为sql一次性查询数量太多，导致回表代价太大。事实上，上述现象的临界值完全可以计算出来：

假设一行记录的大小为a字节，表的记录数量为b，临界记录数量为c，则该表的记录页数量为b*a/1024/16

全表扫描的查询成本 = I/O成本 + CPU成本= b*a/1024/16 * 1.0 + b * 0.2索引扫描的查询成本 = I/O成本 + CPU成本= c * 1.0 + c * 0.2 = c * 1.2b*a/1024/16 * 1.0 + b * 0.2 = c * 1.2临界比例 = c/b = (a/1024/16 + 0.2)/1.2= a * 5E-5 + 0.1667

简介

案例

全表扫描查询成本

优化

回表率计算

更多相关文章

随机推荐