本文转载自微信公众号「码上Java」，作者msJava。转载本文请联系码上Java公众号。
1. 如果索引进行了表达式计算，会失效
我们可以使用EXPLAIN关键字来查看 MySQL 中一条 SQL 语句的执行计划，比如：
- EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_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 | Using where |
- +----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
你能看到如果对索引进行了表达式计算，索引就失效了。这是因为我们需要把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式，运行时间也会慢很多，最终运行时间为 2.538 秒。
为了避免索引失效，我们对 SQL 进行重写：
- SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900000
运行时间为 0.039 秒。
2. 如果对索引使用函数，会失效
比如我们想要对 comment_text 的前三位为 abc 的内容进行条件筛选，这里我们来查看下执行计划：
- EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE SUBSTRING(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 | Using where |
- +----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
你能看到对索引字段进行函数操作，造成了索引失效，这时可以进行查询重写：
- SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_text LIKE '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 | Using index condition |
- +----+-------------+-----------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
你能看到经过查询重写后，可以使用索引进行范围检索，从而提升查询效率。
3. 在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，会失效。
比如下面的 SQL 语句，comment_id 是主键，而 comment_text 没有进行索引，因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效：
- EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900001 OR comment_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 | Using where |
- +----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
如果我们把 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 | Using union(PRIMARY,comment_text); Using where |
- +----+-------------+-----------------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
你能看到这里使用到了 index merge，简单来说 index merge 就是对 comment_id 和 comment_text 分别进行了扫描，然后将这两个结果集进行了合并。这样做的好处就是避免了全表扫描。
4. 当我们使用 LIKE 进行模糊查询的时候，后面不能是 %，会失效。
- EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_text LIKE '%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 | Using where |
- +----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
这个很好理解，如果一本字典按照字母顺序进行排序，我们会从首位开始进行匹配，而不会对中间位置进行匹配，否则索引就失效了。
5. 索引列与 NULL 或者 NOT NULL 进行判断的时候会失效。
这是因为索引并不存储空值，所以最好在设计数据表的时候就将字段设置为 NOT NULL 约束，比如你可以将 INT 类型的字段，默认值设置为 0。将字符类型的默认值设置为空字符串 (’’)。
总结
除了以上情况索引会失效，我们在使用联合索引的时候要注意最左原则。
最左原则也就是需要从左到右的使用索引中的字段，一条 SQL 语句可以只使用联合索引的一部分，但是需要从最左侧开始，否则也会失效。
