【MySQL】使用不到索引的情况

最新推荐文章于 2024-03-05 13:06:44 发布

原创最新推荐文章于 2024-03-05 13:06:44 发布 · 905 阅读

0 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

5 篇文章

订阅专栏

本文探讨了MySQL数据库在某些操作中可能无法利用索引的现象，通过对实例的分析，总结了创建关联索引和主键索引后仍然可能出现的索引未使用问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL使用不到索引的情况有很多，今天具体操作了一番，总结了些常见的情况

创建以下表，有四个字段，其中name和password做了关联索引，id为主键索引

mysql> show create table users \G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `loginIndex` (`name`,`password`)
) ENGINE=InnoDB AUTO_INCREMENT=6428544 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

1. 使用like关键字。like关键字使用有两种情况

a) 有通配符且在关键字的最左边，会使用不到索引，如下示例

b) 无通配符或者通配符不在关键字最左边，能够使用到索引，如'a%bc'，'abc%'

mysql>  explain select * from users where name like '%abc' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6257258
        Extra: Using where
1 row in set (0.00 sec)

2. 联合索引中，只有声明在最左边的字段被索引上了，在同时使用两个列或使用最左边的列作为查询条件的时候会使用到索引

使用password字段作为查询条件未使用到索引

mysql> explain select * from users where password = '123' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6257258
        Extra: Using where
1 row in set (0.00 sec)

3. 使用补集的情况（!=，not in）

mysql> explain select * from users where name != 'aaa' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
         type: ALL
possible_keys: loginIndex
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6257258
        Extra: Using where
1 row in set (0.04 sec)

mysql> explain select * from users where name not in ('aaa', 'bbb', 'ccc') \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
         type: ALL
possible_keys: loginIndex
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6257258
        Extra: Using where
1 row in set (0.04 sec)

4. 使用了错误的数据类型

范例如下，name字段使用了varchar类型存储，虽然不加引号当做int类型时候可以解析，但是不会用到索引

mysql> explain select * from users where name = 123456 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
         type: ALL
possible_keys: loginIndex
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6257258
        Extra: Using where
1 row in set (0.00 sec)

5. 在未添加索引字段使用or关键字

mysql> explain select * from users where name = 'a' or password = 'b' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
         type: ALL
possible_keys: loginIndex
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6257258
        Extra: Using where
1 row in set (0.00 sec)