数据库 索引失效

数据库 索引失效

数据库 索引
MySQL优化（一）
MySQL优化（二）
MySQL优化（三）
MySQL优化（四）

---

索引失效常见情况

1. sql语句中有or关键字；
2. 复合索引未用左列字段;
3. like以%开头;
4. 需要类型转换;
5. where中索引列有运算;
6. where中索引列使用了函数;
7. 如果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;就会使用覆盖索引。