数据库 索引失效
数据库 索引
MySQL优化(一)
MySQL优化(二)
MySQL优化(三)
MySQL优化(四)
索引失效常见情况
- sql语句中有or关键字;
- 复合索引未用左列字段;
- like以%开头;
- 需要类型转换;
- where中索引列有运算;
- where中索引列使用了函数;
- 如果mysql觉得全表扫描更快时(数据少);
EXPLAIN
id(重要):标识SQL语句的执行顺序
id相同的情况 - 执行顺序从上到下
id不同的情况 - id值越大优先级越高
id相同、不同都存在的情况 - id值越大优先级越高,相同从上往下
id为null的情况 - 永远是最后执行
select_type: 表示当前查询部分的查询类型
SQL语句的分类:简单查询、复杂查询
简单查询 - 不包含子查询、union关键字的SQL语句
复杂查询 - 包含子查询(select/where后面包含子查询、from后面包含子查询)、union关键字的SQL语句
可选值:
SIMPLE:查询中不包含任何子查询或者union PRIMARY:查询中包含了任何复杂的子部分,最外层的就会变成PRIMARY (最后被执行的查询) SUBQUERY:在SELECT或者WHERE列表中的子查询标记为SUBQUERY
DERIVED:在FROM中包含的子查询被标记为 DERIVED(衍生表)
UNION:如果第二个SELECT出现在UNION之后,则被标记为UNION,如果UNION包含在FROM子句的子查询中,第一个SELECT会被标记为:DERIVED
UNION RESULT:从UNION表获取结果的select
table:操作的表名
type(重要):表示MySQL会用什么方式去表中获取数据行
可选值:
all - 全表扫描,效率最低
index - 全索引扫描,效率比全表略好
range - 索引范围扫描,只扫描了部分索引
ref - 出现在非唯一性索引,表示只需要扫描精准值的局部范围
eq_ref - 出现在唯一性索引的连接查询中
const - 直接操作主键查询时出现,表示系统将当前的查询变成了一个常量
system - 表示系统明确的知道表中一定只有一条记录
null - 表示SQL语句直接在编译时获得结果
(type的优劣:system > const > eq_ref > ref > range > index > All)
possible_keys:可以用上的索引,不一定用上
keys(重要):当前执行计划用上的索引
注意:有可能出现possible_keys没有的索引,但是keys中出现了,也有可能出现possible_keys有索引,但是keys为null
key_len:表示使用的索引长度,这个值越大,表示索引的使用效率越高
rows(重要):表示查询结果可能需要扫描的记录行数,这个地方的值越小越好
ref:显示使用哪个列或常数与key一起从表中选择行。
Extra(重要):表示执行过程中的一些额外信息
Using index - 表示使用了覆盖索引
const row not found - 表示找不到
Using where - 表示Mysql将对storage engine提取的结果进行过滤,过滤条件字段无索引
Using index condition - 表示会先条件过滤索引,过滤完索引后找到所有符合索引条件的数据行,随后用 WHERE 子句中的其他条件去过滤这些数据行
Distinct - MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists - MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #) - MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
Using filesort - MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
索引失效举例
CREATE INDEX bcd ON AAA(BB,CC,DD);
SHOW INDEX FROM AAA;
1.全值匹配
EXPLAIN SELECT * FROM AAA WHERE BB = 111 AND CC = 'aaa' AND dd = 'a1';
type是ref,key是bcd,key_len上有值,使用上bcd索引
2.最佳左前缀法则
缺少头
EXPLAIN SELECT * FROM AAA WHERE CC = 'aaa' AND DD = 'a1';
type是ALL,key是为空,没有使用上bcd索引
缺少中间
EXPLAIN SELECT * FROM AAA WHERE BB = 111 AND DD = 'a1';
type是ref,possible_key为bcd,key是为bcd,key_len有值,还是能使用上bcd索引
缺少最后
EXPLAIN SELECT * FROM AAA WHERE BB = 111 AND CC = 'aaa';
type是ref,possible_key为bcd,key是为bcd,key_len有值,还是能使用上bcd索引
3.在索引列上做任何操作
EXPLAIN SELECT * FROM AAA WHERE BB = 111 AND LEFT(CC,1) = 'a' AND dd = 'a1';
type是ref,possible_key为bcd,key是为bcd,key_len有值但是值比较小,还是能使用上bcd索引但效率低
4.范围条件后列上索引失效
EXPLAIN SELECT * FROM AAA WHERE BB > 111 AND CC = 'bbb' AND dd = 'a1';
查找级别是范围,name上的索引失效。
type是ALL,possible_key为bcd,key是为null,key_len没有值,没有使用索引
5.尽量使用覆盖索引减少使用select全字段
EXPLAIN SELECT * FROM AAA WHERE BB = 111;
type是ref,possible_key为bcd,key是为bcd,key_len有值但是值比较小,还是能使用上bcd索引但效率低
EXPLAIN SELECT BB FROM AAA WHERE BB = 111;
type是ref,possible_key为bcd,key是为bcd,key_len有值但是值比较小,Extra为Using index,还是能使用上bcd索引和覆盖索引
6.使用不等于(!= 或者<>)不能使用索引
EXPLAIN SELECT * FROM AAA WHERE BB != 111;
7.使用 is null 或者 is not null 也不能使用索引
EXPLAIN SELECT * FROM AAA WHERE BB IS NOT NULL;
8.like 已通配符开头(%XX)导致索引失效 (解决方法:使用覆盖索引)
EXPLAIN SELECT * FROM AAA WHERE BB LIKE '%1%';
想用的话解决方法,使用覆盖索引
EXPLAIN SELECT BB FROM AAA WHERE BB LIKE '%1%';
type是index,possible_key为null,key是为bcd,key_len有值Extra为Using index和Using Where,还是能使用上bcd索引并且使用上覆盖索引
9.少用or,用它来连接索引会失效
EXPLAIN SELECT * FROM AAA WHERE BB = 111 OR CC = 'bbb' OR dd = 'a1';
type是ALL,possible_key为bcd,key是为null,没有使用上bcd索引
覆盖索引
InnoDB存储引擎支持覆盖索引(coveringindex,或称索引覆盖),即从辅助索引中就可以得到查询的记录,而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息,故其大小要远小于聚集索引,因此可以减少大量的IO操作
非聚集索引上直接可以拿到所需数据,不需要再回表查,比如 select id from table where name =xxx;(id为主键、name为索引列) 在统计操作中也会使用覆盖索引。比如(a,b)联合索引,select * from table where b = xxx语句按最左前缀原则是不会走索引的,但如果是统计语句select count(*) from table where b = xxx;就会使用覆盖索引。