SELECT SQL_CALC_FOUND_ROWS FStatDate, FMerchantId, FVersion, FBatch, FTradeAmount, FTradeCount FROM T_Mch******Stat_1020 WHERE FStatDate = 20201020 AND FVersion = 0 AND FMerchantId > 0 ORDER BY FMerchantId ASC LIMIT 0, 8000
对该 SQL 进行 explain 得到如下结果,Extra 字段的值为 using where,说明并没有使用到索引。
优化后的 SQL 语句(做了部分裁剪)B:
SELECT SQL_CALC_FOUND_ROWS a1.FStatDate, a1.FMerchantId, a1.FVersion, FBatch, FTradeAmount, FTradeCount FROM T_Mch******Stat_1020 a1, ( SELECT FStatDate, FMerchantId, FVersion FROM T_Mch******Stat_1020 WHERE FStatDate = 20201020 AND FVersion = 0 AND FMerchantId > 0 ORDER BY FMerchantId ASC LIMIT 0, 8000 ) a2 where a1.FStatDate = a2.FStatDate and a1.FVersion = a2.FVersion and a1.FMerchantId = a2.FMerchantId;
哈希表是一种以键-值(K-V)存储数据的结构,我们只需要输入键 K,就可以找到对应的值 V。哈希的思路是用特定的哈希函数将 K 换算到数组中的位置,然后将值 V 放到数组的这个位置。如果遇到不同的 K 计算出相同的位置,则在这个位置拉出一个链表依次存放。哈希表适用于等值查询的场景,对应范围查询则无能为力。
CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` enum('M','F') NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`), KEY `i_first_name` (`first_name`), KEY `i_hire_date` (`hire_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL 语句 A
explain select * from employees where hire_date > '1990-01-14';
explain 结果:
SQL 语句 B
explain select emp_no from employees where hire_date > '1990-01-14';
explain 结果:
分析
从前后两次 explain 的结果可以看到 SQL 语句 A 的 extra 为 using where,SQL 语句 B 的 extra 为 using where;using index。这说明 A 没有使用索引,而 B 使用了索引。
索引 K 中包含了查询语句所需要的字段 ID 的值,无需再次回到主键索引树查找,也就是“覆盖”了我们的查询需求,我们称之为覆盖索引。覆盖索引可以减少树的搜索次数,显著提升查询性能。
最左匹配
SQL 语句 A
explain select * from employees where hire_date > '1990-01-14' and first_name like '%Hi%';
SQL 语句 B
explain select * from employees where hire_date > '1990-01-14' and first_name like 'Hi%';
分析
在上述测试的 SQL 语句 A 使用了极端方式: first_name like '%Hi%',前后都增加模糊匹配使得 SQL 语句无法使用到索引;当去掉最左边的‘%’后,SQL 语句 B 就使用了索引。最左匹配可以是字符串索引的最左 N 个字符,也可以是联合索引的最左 M 的字段。合理规划、使用最左匹配可以减少索引,从而节约磁盘空间。
select * from employees where hire_date > '1990-01-14' and first_name like 'Hi%';
在 MySQL 5.5 执行 explain,extra 字段的值显示没有使用索引
执行查询花费时间为 0.12s
在 MySQL 5.7 执行 explain,extra 字段的值显示使用了索引下推
执行查询花费时间为 0.02s
索引下推
explain 结果中的 extra 字段值包含 using index condition,则说明使用了索引下推。索引下推功能是从 5.6 版本开始支持的。在 5.6 版本之前,i_first_name 索引是没有使用上的,需要每次去主键索引表取完整的记录值进行比较。从 5.6 版本开始,由于索引 i_first_name 的存在,可以直接取索引的 first_name 值进行过滤,这样不符合"first_name like 'Hi%'"条件的记录就不再需要回表操作。
MRR 优化
MySQL 5.6 版本开始支持 Multi-Range Read(MRR)优化,MRR 优化的目的是为减少磁盘的随机访问,并且将随机访问转化为较为顺序的数据访问,对于 IO-bound 类型的 SQL 查询语句可带来性能极大提升。我们先看下对比测试,以下测试语句在同一个 MySQL 实例下执行,执行前均进行 mysql 服务重启,以保证缓存此没被预热。
关闭 MRR
SET @@optimizer_switch='mrr=off'; select * from employees where hire_date > '1990-01-14' and first_name like 'Hi%';
执行耗时未 0.90s
开启 MRR
SET @@optimizer_switch='mrr=on,mrr_cost_based=off'; select * from employees where hire_date > '1990-01-14' and first_name like 'Hi%';
在不使用覆盖索引的情况下,优化器只有在数据量小的时候才会选择使用非聚集索引。受制于传统的机械磁盘特性,通过聚集索引顺序读数据行的性能会比通过非聚集索引离散读数据行要好。所以,优化器在即使有非聚集索引、但是访问数据量可能达到送记录数的 20%时会选择聚集索引。当然也可以用 Force index 强制使用索引。
explain select * from employees where hire_date > '1999-06-02';
使用函数会使得 MySQL 无法使用索引进行快速查询,因为对索引字段做函数操作会破坏索引值的有序性,所以优化器选择不使用索引。而查询条件类型不一致其实也是同样的情况,因为其使用了隐式类型转换*。
模糊匹配和不使用组合索引的首字段作为查询条件均是无法快速定位索引位置从而导致无法使用索引。模糊匹配当查询条件是 lwhere A ike 'a%',a 是 A 的最左前缀时是可能用上索引的(最左匹配),是否用上最终还是依赖优化器对查询数据量的评估。
回到初始的案例
让我们回到文章初的案例,尝试回答下当时提出的 3 个问题。
-- A语句 SELECT FStatDate, FMerchantId, FVersion, FBatch, FTradeAmount, FTradeCount FROM T_Mch******Stat_1020 WHERE FStatDate = 20201020 AND FVersion = 0 AND FMerchantId > 0 ORDER BY FMerchantId ASC LIMIT 0, 8000;
-- B语句 SELECT SQL_CALC_FOUND_ROWS a1.FStatDate, a1.FMerchantId, a1.FVersion, FBatch, FTradeAmount, FTradeCount FROM T_Mch******Stat_1020 a1, ( SELECT FStatDate, FMerchantId, FVersion FROM T_Mch******Stat_1020 WHERE FStatDate = 20201020 AND FVersion = 0 AND FMerchantId > 0 ORDER BY FMerchantId ASC LIMIT 0, 8000 ) a2 where a1.FStatDate = a2.FStatDate and a1.FVersion = a2.FVersion and a1.FMerchantId = a2.FMerchantId;
SQL 语句 A 的查询条件字段都在主键中,主键索引用到了没?
主键索引其实是有被使用的:索引的范围查询,只是其需要逐条读取和解析所有记录才导致慢查询。
SQL 语句 B 的子查询为什么能够用到索引?
前文中我们介绍了聚集索引,其索引键值就是主键。
两条 SQL 语句的不同之处在于 B 语句的子查询语句的 Select 字段都包含在主键字段中,而 A 语句还有其它字段(例如 FBatch 和 FTradeAmount 等)。这种情况下只凭主键索引的键值就能满足 B 语句的字段要求;A 语句则需要逐条取整行记录进行解析。
前后两条语句执行流程的差异是什么?
SQL 语句 A 的执行过程:
逐条扫描索引表并比较查询条件
遇到符合查询条件的则读取整行数据返回
回到 a 步骤,直至完成所有索引记录的比较
对返回的所有符合条件的记录(完整的记录)进行排序
选取前 8000 条数据返回
SQL 语句 B 的执行过程:
逐条扫描索引表并比较查询条件
遇到符合查询条件的则从索引键中取相关字段值返回
回到 a 步骤,直至完成所有索引记录的比较
对返回的所有符合条件的记录(每条记录只有 3 个主键)进行排序
选取前 8000 条数据返回形成临时表
关联临时表与主表,使用主键相等比较查询 8000 条数据
对比两个 SQL 语句的执行过程,可以发现差异点集中在步骤 2 和步骤 4。在步骤 2 中 SQL 语句 A 需要随机读取整行数据并解析非常耗资源;步骤 4 涉及 MySQL 的排序算法,这里也会对执行效率有影响,排序效果上看 SQL 语句 B 比 SQL 语句 A 好。