创建索引有哪些规律？

  1. 字段的数值有唯一性的限制，比如用户名
  2. 频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下
  3. 需要经常 GROUP BY 和 ORDER BY 的列
  4. UPDATE、DELETE 的 WHERE 条件列，一般也需要创建索引
  5. DISTINCT 字段需要创建索引
  6. 做多表 JOIN 连接操作时，创建索引需要注意以下的原则
    1. 连接表的数量尽量不要超过 3 张
    2. 对 WHERE 条件创建索引
    3. 对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致

什么时候不需要创建索引

  1. WHERE 条件（包括 GROUP BY、ORDER BY）里用不到的字段不需要创建索引
  2. 表记录太少，比如少于 1000 个，那么是不需要创建索引的
  3. 字段中如果有大量重复数据，也不用创建索引
  4. 频繁更新的字段不一定要创建索引

什么情况下索引失效

  1. 如果索引进行了表达式计算，则会失效
  1. EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id+1 = 900001

好的写法:

  1. SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900000
  1. 如果对索引使用函数，也会造成失效
  1. EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE SUBSTRING(comment_text, 1,3)='abc'

好的写法:

  1. SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_text LIKE 'abc%'
  1. 在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。

比如下面的 SQL 语句，comment_id 是主键，而 comment_text 没有进行索引，因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效

  1. EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900001 OR comment_text = '462eed7ac6e791292a79'
  1. 当我们使用 LIKE 进行模糊查询的时候，前面不能是 %
  1. EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_text LIKE '%abc'

  1. 索引列尽量设置为 NOT NULL 约束

  2. 我们在使用联合索引的时候要注意最左原则

总结

实际工作中，查询的需求多种多样，创建的索引也会越来越多。这时还需要注意，我们要尽可能扩展索引，而不是新建索引，因为索引数量过多需要维护的成本也会变大，导致写效率变低。同时，我们还需要定期查询使用率低的索引，对于从未使用过的索引可以进行删除，这样才能让索引在 SQL 查询中发挥最大价值。

image.png