SQL优化之联合索引与优化原理 | 小册免费学

1,871 阅读9分钟

Explain分析

使用方式: 借助Navicat工具

编写好sql后,点解释 直接使用 EXPLAIN + SQL

Explain是用来分析SQL执行计划的一个命令,应用场景 当发现某条SQL执行很慢时,可以借助Explain分析一下是否走索引 当解决了修改某条SQL后,借助Explain查看是否达到预期

Explain指标

指标列名 解释 id 查询编号

select_type 查询类型:显示本行是简单还是复杂查询

table 查询涉及的表

partitions 匹配的分区:查询匹配记录所在的分区仅当使用partition关键字时才显示该列对于非分区表,该值为NULL

type 本次查询的表连接类型

possible_keys 可能选择的索引

key 实际选择的索引

key_len 被选择的索引长度:一般用于判断联合索引有多少列被选择了

rows 预计需要扫描的行数,对InnoDB来说,这个值是估值

filtered 按条件筛选行的百分比

ref 与索引比较的列

Extra 附加信息

type的值

type的值 解释

system 查询对象表只有一行数据,且只能用于MyISAM和Memory引擎的表,这是最好的情况

const 基于主键或唯一索引查询,最多返回一条结果

eq_ref 表连接时基于主键或非NULL的唯一索引完成扫描

ref 基于普通索引的等值查询,或者表间等值连接

fulltext 全文检索

ref_or_null 表连接类型是ref,但进行扫描的索引列中可能包含NULL值

index_merge 利用多个索引

unique_subquery 子查询中使用唯一索引

index_subquery 子查询中使用普通索引

range 利用索引进行范围查询

index 全索引扫描

ALL 全表扫描

possible_keys与key

主要看key,这个是实践查询选择的索引,而possible_keys主要用来映衬key,来推测联合索引的“使用率”。如果possible_keys长度是3,而key长度是2,那么可以推测联合索引只用了前两个。

Extra 与索引覆盖、排序都有关

Extra常见的值 解释 例子

Using filesort 未使用索引排序,将用外部排序,数据较小时从内存排序,否则在磁盘完成排序 explain select * from t1 order by create_time;

Using temporary 需要创建一个临时表来存储结构,通过对没有索引的列进行GROUP BY时 explain select * from t1 group by create_time;

Using index 使用覆盖索引 explain select a from t1 where a=111; Using where 使用where语句来处理结果 explain select * from t1 where create_time=‘2019-06-18 14:38:24’;

impossible WHERE 对where子句判断的结果总是false 而不能选择任何数据 explain select * from t1 where 1<0;

Using join buffer(Block Nested Loop) 关联查询中被驱动表的关联字段没索引 explain select * from t1 straight_join t2 on (t1.create_time=t2.create_time);

Using index condition 使用索引进行条件过滤后,再查数据 explain select * from t1 where a >900 and a like “%9”;

Select tables optimized away 使用某些聚合函数(max、min)来访问存在索引的某个字段 explain select max(a) from t1;

联合索引

联合索引容易实现----最左匹配原则

联合索引再提高索引利用率、加快分组、排序等方面有独特的优势,实际开发中联合索引一般来说由于单列索引

疑问:

联合索引长什么样? 查询时是怎么检索的呢? 为什么联合索引index(a,b,c),当查询条件为 b = 2 ,c = 3时无法利用索引呢?

联合索引长什么样?

还是一棵B+树,不会因为时联合索引,就变成多颗树 索引节点会存储多列,比如单列索引节点存储为【name,name...】,而多列索引的节点内会存储【[name,age],[name,age]...】

建了联合索引后插入一条数据,会发生什么?

插入数据的过程其实伴随着索引的维护(即维护主键索引,又维护联合索引) 在插入一条数据时,会进行索引比较,比较name再比较age,换而言之,这样沿着索引比较后插入的数据,本身就是有序的,是符合联合索引的顺序。

索引加快排序速度

本身插入或更新时,都是按照索引排序规则维护的,当order by的规则与维护索引的规则一致时,此时索引就能加快排序,因为这个时候根本不用排序,也就是利用索引排序

把对顺序的维护分散到每一次增删改的过程中,查询时直接使用维护好的索引排序

最左匹配查询:WHERE条件查询

对于联合索引index(name,age),条件使用age过滤时,索引会失效,为什么? 我们对联合索引进行维护时,即插入一条数据时,是按照索引顺序进行排序的,排序时为先排第一个name条件,而并未单独排过age,age是基于name排序后拍的,所以单独使用age过滤时,无法使用索引。 180cm 98分 182cm 96分 182cm 97分 183cm 99分 184cm 93分 184cm 95分 Mysql在解析SQL时,发现where不符合联合索引的“最左匹配原则”时,直接放弃使用索引查询,选择全表扫描。

当WHERE条件的字段满足最左匹配原则时,可以使用联合索引

假设联合索引index(a,b,c)来看几个最左匹配的案例: where a,b,c 全匹配 where a,b(只能匹配a,b) where a,c(只能匹配a) where b,c 不能匹配 a,c的查询过程是怎样的?

如果where条件能走索引,就能在上层就判断下层应该走哪个方向,否则只能深入节点内部数据逐个比较,先筛选出满足a条件的所有节点,然后这些节点逐个与c条件比较。

除此之外,除了不能缺少联合索引中的查询字段外,范围查询也会使后面字段失效 where a=1,b>2,c=3(只能匹配a,b) 当使用范围查询后,我们就无法使用联合索引后续字段的排序了

最左匹配原则:ORDER BY

当ORDER BY的条件与联合索引排序规则保持一致时,直接查询即可,无需排序,因为数据在插入时就已经按索引顺序拍好了。

如果没有利用索引排序,或无法利用索引排序时,会发生什么? file sort 使用外部排序(利用内存或磁盘中重新排序)

什么情况下ORDER BY 索引排序会失效呢? 以联合索引index(name,age)为例,无法利用索引排序的情况: order by age,name(字段顺序不一致) order by name desc,age asc(字段排序方式不同步,desc和asc混着来) 字段顺序不一致时:因为索引是按照name,age排序的,没有按age,name排序的规则,所以需要重新排序

字段排序方式不一致时,维护索引的排序规则未name ASC,age ASC来排序的,如果你使用一个不一致的排序方式,与索引排序方式不匹配时,需要重新排序

即使索引的排序方式是顺序排序的,但当查询时都为倒序时也能使用索引的排序,为什么? 因为倒序为顺序的取反,所以只需得到顺序的结果,取反即可

如果像利用索引排序,那么order by的顺序需要符合最左前缀原则。

但当配合where使用时又有例外:如index(a,b,c) where a = 1 order by b,c也是可以利用索引排序的 为什么? where是返回结果前的过滤,当where过滤完后得到的结果其实是按照了3个字段排序后的结果,此时order by 在进行排序时,就是已经拍好序的结果了。

但当where中使用了范围查询,order by 将不能使用索引排序

最左匹配查询:GROUP BY

group by 可以看成两步:先排序,在归并 所以基本上和order by差不多,与联合索引顺序保持一致时,会跳过排序,直接归并

联合索引的使用场景

场景一:多条件查询,提高利用率

如果SQL的where条件经常是多个相同的字段,比如where a,b,c 、where a,b where a,c等,可以考虑建立联合索引index(a,b,c)

需要注意的规则: where条件的书写顺序不影响是否走联合索引,where a,b,c和where b,a,c都可以走index(a,b,c) 但where b,c不行,因为b,c是基于a排序的,而where a,c只能利用a部分索引,c基于a,b 创建索引时,把区分度高的字段排在前面,区分度不高的字段可能包含几个节点,此时就可能读取多个节点了,而区分度高的字段,就可以减少第二次查询的次数

场景二:避免回表

走辅助索引并且回表时,就会多一次对主键索引的扫描 当走辅助索引获取不到查询的所有列时就会回表,所以如果情况允许的话,建立一个可以覆盖查询列的联合索引,并不是只有联合索引才能使用索引覆盖,只要辅助索引上的字段能满足查询列即可。

实际开发中索引覆盖比较少,回表的情况多(走了辅助索引,其实也提高了一部分效率)

场景三:索引排序

当你建立了联合索引index(a,b,c)时,每次增删该都会维护这个顺序,等于同时建立了一个基于a,b,c的顺序索引(最左前缀匹配),这个时候就可以考虑利用索引排序。

where + order by 是个特例,当where的条件+order by的条件满足最左匹配原则也能利用索引排序

场景四:COUNT统计

COUNT也能使用上联合索引,在COUNT时尽量走索引

联合索引失效场景 不满足最左匹配原则 使用了范围查询

查询优化器其实就可以帮我们优化where条件顺序问题,和选择使用执行计划等处理

一句话概括SQL优化:

所谓SQL优化,其实就是让查询优化器根据程序员的医院选择匹配的执行计划,最终减少查询中产生的IO

复习时尽量注意以下知识节点:

MySQL常用数据类型及选择(肯定有人对int(11)和varchar(255)里的数字迷茫吧?) 数据库范式与反范式的取舍 SQL基础:简单查询、关联查询、子查询、GROUP BY、HAVING、ORDER BY、LIMIT,重点关注GROUP BY和ORDER BY,它俩最常用也最难理解 常用函数,比如now()等 什么是索引、索引的数据结构是什么(只考虑InnoDB) 索引加快查询的原理、聚簇索引和非聚簇索引的概念 索引的优缺点 如何利用Explain分析执行计划、慢查询日志 索引失效的几种场景(最左匹缀、!=、LIKE %...、列函数计算等) SQL改写的几种场景与策略

本文正在参与「掘金小册免费学啦!」活动, 点击查看活动详情