MySQL(十八)：索引失效分析与优化

使用索引来提高查询性能效果是显著的，但不正确的 SQL 语句使用也会导致索引失效，索引使用未达预期，甚至执行全表扫描，此情况是严重影响性能的，在编写 SQL 语句时特别注意。

特别强调，在每提交一条 SQL 语句时，必须使用 Explain 分析下索引的的使用。在生产环境或测试环境下，小数据的 SQL 执行感觉不到性能的差异，但一旦发布到线上大数据表，不正确的 SQL 可能会严重影响生产库的性能，并给业务带了损失，此情况仍时有发生。

索引失效

索引失效与优化

1. 复合索引的使用未遵循 最左前缀原则。
   优化：要使用复合索引，必须满足 B-Tree 索引的 最左前缀原则(LeftMost)，(带头索引不能死，中间索引不能断）。
   复合索引实际上是将多个单列索引按顺序存放在一起，最左前缀原则指的从最左前列开始并且不跳过索引中的列。

2. WHERE 条件索引列上做涉及计算、函数、类型转换的操作，会导致索引失效造成全表扫描。
   例如使用 MIN()、MAX() 求最小/最大值，可以优化为使用排序求第一条数据，如下：

   EXPLAIN SELECT MAX(film_id) FROM inventory;
   EXPLAIN SELECT film_id FROM inventory ORDER BY film_id DESC LIMIT 1;
   

   类型转换的，如字符串数值列，使用了数值比较；字符串列不加单引号比较。

3. 尽可能使用覆盖索引，可查询索引列的值，这样就可以直接从索引取出值，而不用查表。
   避免甚至禁止 SELECT 出现，字段按需查询。
   *注意：使用通过 Mapper 接口查询会取出整个对像的数据；尽可能使用覆盖索引按需查，需要手动写每条 SQL。

4. 使用不等于( <>，!= )查询时也会导致索引失效。
   优化：尽可能使用等值查询，即全值匹配查询。

5. IS NOT NULL 条件查询也可能导致索引失效。
   使用 IS NOT NULL 条件查询，若选择的列包含了非索引列，则索引失效；若选择的列全是索引列，则会扫描所有索引，相比扫描全表还是更快。

6. 以通配符 % 开头的条件查询也会导致它引失效。
   深入体会理解，其实要使用索引都必须遵循 最左前缀原则，不管是单列索引还是多列索引，而以 % 开头的查询未遵循该原则。
   优化：先扫描满足条件的主键列表，根据主键回表去检索记录，这样访问避开了全表扫描产生的大量 IO 请求。这种方式实际是由全表扫描改为了扫描全索引，相比全表扫描效率要高。

   SELECT * FROM 
       (SELECT actor_id FROM actor WHERE last_name LIKE '%NI%') a, actor b
       WHERE a.actor_id = b.`actor_id`;
   

   更优的处理方式是将模糊查询的列使用搜索服务来存储，利用搜索引擎来提高查询效率和性能。

7. 当查询认为全表扫描比使用索引效率更好时，就会放弃索引(查询的数据量超过表的30%以上)。
   例如，使用前缀模糊搜索，需要返回记录的比例较大，MySQL预估索引扫描不如全表扫描，则会放弃使用索引。

8. 少用 OR 来连接查询，可能导致索引失效。
   如果不注意的话，OR 连接的是非索引列，则会引起整个 SQL 语句在索引使用上失效。

索引其它优化

1. 如果索引列出现很多重复的值，则索引效果并不会太好，如：状态字段、Y/N 或 0/1。
2. 建立索引能覆盖80%的主查询，不求全，解决主要矛盾。
3. 避免冗余索引，即重复索引，交叉索引，例(idx_abc(a,b,c),idx_ab(a,b),)。