本文转载自微信公众号《CodeonJava》，作者msJava。转载本文请联系码上Java公众号。1.如果索引是通过表达式计算的，则无效。我们可以使用EXPLAIN关键字查看MySQL中一条SQL语句的执行计划，如：EXPLAINSELECTcomment_id,user_id,comment_textFROMproduct_commentWHEREcomment_id+1=900001运行结果：+----+------------+----------------+------------+------+--------------+--------+--------+------+--------+--------+------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+------------+----------------+------------+------+----------------+------+--------+------+--------+------------+------------+|1|SIMPLE|product_comment|NULL|ALL|NULL|NULL|NULL|NULL|996663|100.00|Usingwhere|+----+------------+----------------+-----------+--------+----------------+------+--------+------+--------+----------+------------+可以看到，如果表达式Calculated,index变得无效。这是因为我们需要提取索引字段的所有值，然后计算表达式以便判断条件。所以采用全表扫描的方式，运行时间会慢很多。最终运行时间为2.538秒。为了避免索引失效，我们重写SQL：SELECTcomment_id,user_id,comment_textFROMproduct_commentWHEREcomment_id=900000运行时间为0.039秒。2.如果你在索引上使用一个函数，它会失败。比如我们要条件过滤comment_text前三位为abc的内容，这里看执行计划：EXPLAINSELECTcomment_id,user_id,comment_textFROMproduct_commentWHERESUBSTRING(comment_text,1,3)='abc'运行结果：+----+------------+----------------+-------------+------+----------------+------+--------+------+-------+------------+------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+------------+----------------+----------+------+--------------+------+--------+------+--------+----------+------------+|1|SIMPLE|product_comment|NULL|ALL|NULL|NULL|NULL|NULL|996663|100.00|Usingwhere|+----+------------+------------------+------------+------+----------------+------+----------+------+--------+----------+------------+可以看到对索引字段的函数操作导致索引失败。这时可以改写查询：SELECTcomment_id,user_id,comment_textFROMproduct_commentWHEREcomment_textLIKE'abc%'使用EXPLAIN分析查询语句：+----+-----------+-------------+------------+--------+------------+------------+--------+-----+-----+--------+-------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-----------+----------------+------------+-------+----------------+------------+--------+------+------+------------+------------------------+|1|SIMPLE|product_comment|NULL|range|comment_text|comment_text|767|NULL|213|100.00|Usingindexcondition|+----+------------+----------------+------------+--------+----------------+--------------+--------+------+------+--------+-------------------+可以看到改写查询后，可以使用索引进行范围检索，从而提高查询效率3.在WHERE子句中，如果OR之前的条件column被索引，但是OR之后的条件列没有被索引并且会无效。比如下面的SQL语句中，comment_id为主键，但是comment_text没有建立索引，因为OR的意思是两个只需要满足一个，所以只对一个条件列建立索引是没有意义的，只要条件列没有索引，会进行全表扫描，所以索引的条件列也会失效：EXPLAINSELECTcomment_id,user_id,comment_textFROMproduct_commentWHEREcomment_id=900001ORcomment_text='462eed7ac6e791292a79'运行结果：+----+-------------+----------------+------------+------+----------+-----+--------+-----+--------+----------+------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+--------------+----------------+------------+------+---------------+------+--------+------+--------+-----------+------------+|1|SIMPLE|product_comment|NULL|ALL|PRIMARY|NULL|NULL|NULL|996663|10.00|Usingwhere|+----+--------------+---------------+------------+------+----------------+------+--------+--------+------+---------+------------+如果会发生什么我们索引了comment_text?+----+-----------+----------------+-----------+---------------+------------------------+---------------------+--------+------+------+--------+--------------------------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+------------+----------------+------------+------------+--------------------+--------------------+--------+------+------+--------+-----------------------------------------------+|1|SIMPLE|product_comment|NULL|index_merge|PRIMARY,comment_text|PRIMARY,comment_text|4,767|NULL|2|100.00|Usingunion(PRIMARY,comment_text);Usingwhere|+----+-----------+----------------+------------+-------------+--------------------+--------------------+--------+------+------+----------+----------------------------------------------+可以看到这里使用了indexmerge，简单的对于indexmerge，它分别扫描comment_id和comment_text，然后合并两个结果集。这样做的好处是避免了全表扫描。4、我们在使用LIKE进行模糊查询时，后面不能跟%，会失效。EXPLAINSELECTcomment_id,user_id,comment_textFROMproduct_commentWHEREcomment_textLIKE'%abc'运行结果：+----+------------+----------------+----------+------+--------------+------+--------+------+--------+----------+------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+------------+------------------+------------+------+----------------+------+----------+------+--------+----------+------------+|1|SIMPLE|product_comment|NULL|ALL|NULL|NULL|NULL|NULL|996663|11.11|Usingwhere|+----+------------+----------------+------------+-----+------------+------+--------+------+--------+--------+-----------+这个很容易理解。如果一个字典是按字母排序的，我们将从第一个位置开始匹配，而不是从中间位置开始匹配，否则索引将无效。5.当索引列判断为NULL或NOTNULL时，会失效。这是因为索引不存储空值，所以在设计数据表时最好将该字段设置为NOTNULL约束。例如，您可以将INT类型字段的默认值设置为0。将字符类型的默认值设置为空字符串('')。总结除了以上几种情况，索引也会失效。在使用联合索引时要注意最左原则。最左原则是从左到右使用索引中的字段。一条SQL语句只能使用联合索引的一部分，但需要从最左边开始，否则会失败。
