什么时间需要建立索引？

  • 经常用于 where 查询条件的字段
  • 字段有唯一性限制的，比如商品编码
  • 经常用于 group by 和 order by的字段，这样查询时不需要在此排序。

索引优化方法？

  • 前缀索引优化
    针对某个字段中的前几个字符建立索引
  • 覆盖索引优化
    覆盖SQL query的所有字段，从二级索引中直接查询到记录，避免回表。

索引失效的情况？

  • 使用模糊匹配的时候，也就是 like %xx 或者 like %xx%
  • 在查询条件中对索引列做了计算、函数、类型转换等操作
  • 联合索引要能正确遵循最左匹配原则
  • 在WHERE子句中，如果OR前的条件是索引列，而在OR后的条件列不是索引列

联合索引相关问题？

创建了一个 (a, b, c) 联合索引，哪些语句可以匹配？

  • where a=1；

  • where a=1 and b=2 and c=3；

  • where a=1 and b=2；
    需要注意的是，因为有查询优化器，所以a字段在where子句的顺序并不重要。

  • where b=2；

  • where c=3；

  • where b=2 and c=3；
    不符合最左匹配。

  • where a = 1 and c = 3
    索引下推，可以在存储引擎层进行索引遍历过程中，对索引中包含的字段先做判断，直接过滤到不满足条件的记录，再返回给Server层，减少回表次数。

  • where b > 1 and a = 3
    首先，优化器会对where子句进行优化，将a提前，所以其可以使用联合索引。

like “%x”一定会失效吗？

  1. 一个表有多个字段，其中name是索引字段，其他非索引，id拥有自增主键索引。
  2. 一个表有2个字段，其中name是索引字段，id拥有自增主键索引。

以上两张表、分别执行以下查询语句：

  • select * from s where name like “xxx”
  • select * from s where name like “xxx%”
  • select * from s where name like “%xxx”
  • select * from s where name like “%xxx%”
  1. 1,2条会走，3,4条索引失效
  2. 1,2走，3,4也可以用到，因为覆盖索引