mysql使索引失效语句_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)

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