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

来源：互联网发布：c语言二级文件系统编辑：程序博客网时间：2024/05/25 01:36

一、创建两张表表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); Using 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)

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

1 0