mysql回表致索引失效

落日映苍穹つ 2022-01-14 09:13 309阅读 0赞

## 简介 ##

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='毕业生薪水数据统计';

delimiter  //
    CREATE PROCEDURE init_salary_static() 
    BEGIN 
    	DECLARE year INT;
    	DECLARE schid INT;
    	DECLARE stuid INT;
    	SET year = 2000;
    	WHILE year < 2020 DO
    		START TRANSACTION; 
    		SET schid = 1;
    		WHILE schid < 100 DO
    			SET stuid = 1;
    			WHILE stuid < 1000 DO
    				insert into salary_static(school_id,student_id,salary,year) values (schid,stuid,floor(rand()*10000),year);
    				SET stuid = stuid + 1;
    			END WHILE;
    			SET schid = schid + 1;
    		END WHILE;
    		SET year = year + 1;
    		COMMIT; 
    	END WHILE;
    END //
    delimiter ;
    call init_salary_static();

测试数据创建完成后，执行以下sql语句进行统计查询。

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

预计该sql应该使用`year_school_key`索引进行查询，但实际上通过`explain`命令可以发现，该sql使用的是`school_id_key`索引，并且由于使用了错误的索引，该sql进行了全表扫描导致查询时间花费了7秒。

![在这里插入图片描述][20190616154727954.png]

![在这里插入图片描述][20190616154742952.png]

强制使用`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;

![在这里插入图片描述][20190616154758960.png]

![在这里插入图片描述][2019061615481146.png]

## 分析 ##

使用mysql的`optimizer tracing`（mysql5.6版本开始支持）功能来分析sql的执行计划：

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，展示了该sql在mysql内部的sql优化过程、索引选择过程的执行计划。

重点关注执行计划的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"
    	}
      }
    }

这里的查询成本`cost`值完全可以手算出来，`cost`=`I/O`成本（每一次读取记录页一次成本，每次成本为`1.0`）+`CPU`成本（每一条记录一次成本，每次成本为`0.2`）。

#### 全表扫描查询成本 ####

`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`成本

1973546 * 0.2 = 394709.2

*  合计查询成本

5030 + 394709.2 = 399739.2

#### 索引查询成本 ####

`year_school_key`索引时预估需要扫描`812174`条记录，且使用该索引需要先通过索引查询到rowId，然后通过rowId回表。mysql认为每次回表均需要一次单独的`I/O`成本

*  `CPU`成本

812174 * 0.2 = 162434.8

*  `I/O`成本

812174 * 1.0 = 812174

*  合计查询成本

162434.8 + 812174 = 974608.8

接着再关注`reconsidering_access_paths_for_index_ordering`，表示最终对排序再进行一次索引选择优化。这里选择了`school_id_key`索引并且一票否决了上面`where`条件选择的全表扫描：`"plan_changed": true`，详见[group-by-optimization][]。

{ 
        "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"
          }
        }
    }

事实上排序索引优化也存在bug，详见[Bug＃93845][Bug_93845]。

## 优化 ##

通过分析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语句后效果如下，仅花费了0.2秒完成查询，比起`school_id_key`索引的时间减少了35倍。

![在这里插入图片描述][20190616154828182.png]

![在这里插入图片描述][20190616154838451.png]

#### 回表率计算 ####

上述问题为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.2
    
    
    b*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

即当一条sql查询超过表中超过大概17%的记录且不能使用覆盖索引时，会出现索引的回表代价太大而选择全表扫描的现象。且这个比例随着单行记录的字节大小的增加而略微增大。

[20190616154727954.png]: /images/20220114/33e87c5f8ecc443596ea76c7462d595e.png
[20190616154742952.png]: /images/20220114/fd334773f1df461ca6ce62fc33f834f4.png
[20190616154758960.png]: /images/20220114/dc924bc4966f4e35a701ac6de83471f6.png
[2019061615481146.png]: /images/20220114/42facee6547f4780b36d4f9010e77e77.png
[group-by-optimization]: https://dev.mysql.com/doc/refman/5.6/en/group-by-optimization.html
[Bug_93845]: https://bugs.mysql.com/bug.php?id=93845
[20190616154828182.png]: /images/20220114/ac55d17296294a5e9ad0f94731ec803b.png
[20190616154838451.png]: /images/20220114/fb9e88a76a5d4fc699acd339b6fb4482.png