创建了索引查询还是慢，你可能犯了这些错误

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 语句可以只使用联合索引的一部分，但是需要从最左侧开始，否则也会失效。