mysql语句中索引可能失效的情况

一、创建两张表 表a 表b  选择不同的存储引擎以InnoDB和MyISAM为例

表a

CREATE TABLE `a` (
`ID`  int NOT NULL AUTO_INCREMENT ,
`name`  varchar(32) NOT NULL ,
`score`  int UNSIGNED NOT NULL ,
PRIMARY KEY (`ID`),
UNIQUE INDEX `name` (`name`) 
)
ENGINE=InnoDB
;

表b

CREATE TABLE `b` (
`ID`  int NOT NULL AUTO_INCREMENT ,
`name`  varchar(32) NOT NULL ,
`score`  int UNSIGNED NOT NULL ,
PRIMARY KEY (`ID`),
UNIQUE INDEX `name` (`name`) 
)
ENGINE=MyISAM
;

新增数据

mysql> select * from a;
+----+--------------+-------+
| ID | name         | score |
+----+--------------+-------+
|  1 | 德玛西亚     |    88 |
|  2 | 艾欧尼亚     |    78 |
|  3 | 无畏先锋     |    66 |
|  4 | 暗影岛       |    99 |
|  5 | 黑色玫瑰     |    43 |
|  6 | 诺克萨斯     |    56 |
|  7 | 班德尔城     |    72 |
|  8 | 雷瑟守备     |    30 |
+----+--------------+-------+
8 rows in set (0.00 sec)

mysql> select * from b;
+----+--------------+-------+
| ID | name         | score |
+----+--------------+-------+
|  1 | 德玛西亚     |    88 |
|  2 | 艾欧尼亚     |    78 |
|  3 | 无畏先锋     |    66 |
|  4 | 暗影岛       |    99 |
|  5 | 黑色玫瑰     |    43 |
|  6 | 诺克萨斯     |    56 |
|  7 | 班德尔城     |    72 |
|  8 | 雷瑟守备     |    30 |
+----+--------------+-------+
8 rows in set (0.00 sec)

二、开始测试

1.where后面使用了or 索引可能会失效

innodb和myisam：字段都建立索引时候有效，否则索引失效

mysql> explain select name from a where id = 1 or score =66;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    8 |    23.44 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select score from a where id = 1 or name ='暗影岛';
+----+-------------+-------+------------+-------------+---------------+--------------+---------+------+------+----------+-----------------------------
-----------+
| id | select_type | table | partitions | type        | possible_keys | key          | key_len | ref  | rows | filtered | Extra
           |
+----+-------------+-------+------------+-------------+---------------+--------------+---------+------+------+----------+-----------------------------
-----------+
|  1 | SIMPLE      | a     | NULL       | index_merge | PRIMARY,name  | PRIMARY,name | 4,98    | NULL |    2 |   100.00 | Using union(PRIMARY,name); U
sing where |
+----+-------------+-------+------------+-------------+---------------+--------------+---------+------+------+----------+-----------------------------
-----------+
1 row in set, 1 warning (0.00 sec)

2.where后面使用了<>  != 索引可能会失效
innodb:id字段索引可用

myisam:id字索引失效

mysql> explain select * from a where id<>2;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    7 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from b where id<>2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | b     | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    8 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

3.使用了like '%....'索引会失效，查询时候最左原则
innodb和myisam:name字段索引失效

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

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

mysql> explain select * from b where name like '斯%';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | b     | NULL       | range | name          | name | 98      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

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

暂时写到这里- -写的不全，第一次写博客，写的比较简单，欢迎指正！