Mysql中索引失效常见原因和解决方案

索引失效

跳过复合索引中间列，或首列

CREATE TABLE IF NOT EXISTS article(
	id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	author_id INT(10) UNSIGNED NOT NULL,
	category_id INT(10) UNSIGNED NOT NULL,
	views INT(10) UNSIGNED NOT NULL,
	comments INT(10) UNSIGNED NOT NULL,
	title VARCHAR(255) NOT NULL,
	content TEXT NOT NULL
);

INSERT INTO article(author_id,category_id,views,comments,title,content)
VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(2,2,2,2,'aa','2'),
(2,2,2,2,'bba啊','2'),
(1,1,3,3,'cca','3');

需求

查询category_id为1 而且 comments =1 的情况下，views最多的article_id。

对应sql

select id,author_id 
from article 
where  category_id = 1 and comments = 1
ORDER BY views 
desc LIMIT 1;

创建复合索引

跳过复合索引中间列，或首列，导致索引失效

1、跳过首列，全部失效

mysql> explain select id,author_id from article where comments = 1 ORDER BY views desc LIMIT 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

2、跳过首列，全部失效

mysql> explain select id,author_id from article ORDER BY views desc LIMIT 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

3、跳过中间列，views索引失效

mysql> explain select id,author_id from article where  category_id = 1  ORDER BY views desc LIMIT 1;
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+----------------+
| id | select_type | table   | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra          |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | article | NULL       | ref  | idx_article_ccv | idx_article_ccv | 4       | const |    2 |   100.00 | Using filesort |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

索引列上做额外操作

mysql>  explain select id,author_id from article where  left(category_id,11) = 1 and comments = 1  ORDER BY views desc LIMIT 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

限定复合索引某列的范围

mysql> explain select id,author_id from article where  category_id = 1 and comments >1   ORDER BY views desc LIMIT 1;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | article | NULL       | range | idx_article_ccv | idx_article_ccv | 8       | NULL |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

示例中：views索引失效

select * 可能导致回表查询

Mysql中的回表查询

mysql> explain select * from article where  category_id = 1 and comments>1   ORDER BY views desc LIMIT 1;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | article | NULL       | range | idx_article_ccv | idx_article_ccv | 8       | NULL |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

like以通配符%开头字符串

like以通配符%开头字符串 会导致索引失效

mysql> create index idx_article_title on article(title);

mysql> explain select * from article where  title like '%a';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

注意：通配符在最后面，不会导致索引失效

mysql> explain select * from article where  title like 'a%';
+----+-------------+---------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | article | NULL       | range | idx_article_title | idx_article_title | 767     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.03 sec)

用关键字OR

mysql> explain select * from article where  category_id = 1 or comments = 2;
+----+-------------+---------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | article | NULL       | ALL  | idx_article_ccv | NULL | NULL    | NULL |    6 |    58.33 | Using where |
+----+-------------+---------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

索引失效总结

索引失效常见解决方案

索引失效（应该避免）

1、最佳左前缀法则 - 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过复合索引中间列。

2、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。

3、存储引擎不能使用索引中范围条件右边的列。

4、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *。

5、mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。

6、is null, is not null 也无法使用索引。

7、like以通配符开头（’%abc…’），mysql索引失效会变成全表扫描的操作。

8、字符串不加单引号索引失效。

9、少用or，用它来连接时会索引失效。

优化总结口诀

全值匹配我最爱， 最左前缀要遵守；

带头大哥不能死， 中间兄弟不能断；

索引列上少计算， 范围之后全失效；

LIKE 百分写最右， 覆盖索引不写 *；

不等空值还有 OR， 索引影响要注意；

VAR 引号不可丢， SQL 优化有诀窍。