MySQL.

使用MySQL索引注意事项及优化要点

使用MySQL索引注意事项及优化要点

  • 索引的作用是快速的筛选结果,所以,筛选度太小的列不建议建立索引,如性别(一般只能筛选50%左右)
  • 大多数情况下索引能大幅度提高查询效率,但索引不是越多越好,更多的索引意味着更多的存储空间以及更多的维护成本(增删改时需要更新索引)
  • 查询条件中尽量使用常量,不要使用函数,也不要进行运算,否则无法使用索引,如
    • 以下不能使用索引
      • SELECT * FROM user WHERE DATE(created_at) = ‘2014-01-01’;
      • SELECT * FROM user WHERE (age-5) = 20;
  • 用于查询的值尽量和索引列的数据类型相同,否则,可能会无法使用索引
  • 使用LIKE时需要小心,左前缀才能用到索引(LIKE ‘aiddroid%’ 能用到索引,而LIKE ‘%aiddroid%’或者 LIKE ‘%aiddroid’则无法用到索引)
  • <,<=,=,>,>=,BETWEEN AND,IN等肯定比较查询 , 能用到索引,但<>,NOT IN ,!=等否定比较则无法使用索引
  • 联合索引时,遵循最左前缀原则(如INDEX(nickname,city),则
    • 以下能使用索引
      • SELECT * FROM user WHERE nickname=’aiddroid’;
      • SELECT * FROM user WHERE nickname=’aiddroid’ AND city = ‘广州’;
    • 以下不能使用索引
      • SELECT * FROM user WHERE city = ‘广州’;
  • 索引列存在NULL值时,将无法用到索引(这是一个误区,存在NULL值时,MySQL不会对SQL进行优化,但并不是无法用到索引)
  • MySQL查询只使用一个索引,因此如果where子句中已经使用了索引,那么order by中的列不再会使用索引
  • ORDER BY 使用索引优化时
    • SELECT到的列需要有索引,直接SELECT * 可能导致用不到索引优化,必要时可以FORCE INDEX
    • 如果ORDER BY多个字段,排序方向一致才能用到索引优化(ASC,DESC混用,无法使用索引优化)
  • GROUP BY时,条件需要有索引,多个条件时同样遵循最左前缀原则,必要时可以使用LIMIT
  • 当存在多个索引时,MySQL会选择一个它认为能最快筛选出结果的索引
  • MySQL会使用最好的索引,除非MySQL优化器认为表扫描更有效。在以前, 是否使用扫描取决于最好的索引跨越过30%的表。但现在已经不用固定比例来判断是否使用索引还是表扫描,MySQL优化器更加复杂,它的估计基于其它因素,例如表大小、行数和I/O块大小等等
  • 多多EXPLAIN不是坏事