MySQL索引失效


VERSION = 5.7.37


1.组合索引不满足最左匹配,索引失效

组合索引 idx_enterprise_id_owner_id_warehouse_id(enterprise_id, owner_id, warehouse_id)

          
EXPLAIN SELECT * FROM store_customer_order_20230922 WHERE warehouse_id = 33495628344758272

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: store_customer_order_20230922
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 373630
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)
          
        

以下SQL满足最左匹配,索引生效

          
EXPLAIN SELECT * FROM store_customer_order_20230922 WHERE enterprise_id = 33494065698107392 AND warehouse_id = 33495628344758272

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: store_customer_order_20230922
   partitions: NULL
         type: ref
possible_keys: idx_enterprise_id_owner_id_warehouse_id
          key: idx_enterprise_id_owner_id_warehouse_id
      key_len: 8
          ref: const
         rows: 15244
     filtered: 10.00
        Extra: Using index condition
1 row in set, 1 warning (0.01 sec)
          
        

2.SELECT * 导致索引失效

回表范围超过一定比例值会导致索引失效
组合索引 idx_enterprise_id_owner_id_warehouse_id(enterprise_id, owner_id, warehouse_id)

          
EXPLAIN SELECT * FROM store_customer_order_20230922 WHERE warehouse_id = 33495628344758272

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: store_customer_order_20230922
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 373630
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)
          
        

以下SQL索引覆盖,索引生效

          
EXPLAIN SELECT warehouse_id,enterprise_id FROM store_customer_order_20230922 WHERE warehouse_id = 33495628344758272

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: store_customer_order_20230922
   partitions: NULL
         type: index
possible_keys: idx_enterprise_id_owner_id_warehouse_id
          key: idx_enterprise_id_owner_id_warehouse_id
      key_len: 24
          ref: NULL
         rows: 373630
     filtered: 10.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
          
        

3.索引列上有计算,索引失效

          
EXPLAIN SELECT * FROM store_customer_order_20230922 WHERE customer_order_id + 1=33944879566856193

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: store_customer_order_20230922
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 381663
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)
          
        

4.索引列上使用函数,索引失效

          
EXPLAIN SELECT * FROM store_customer_order_20230922 WHERE SUBSTR(customer_order_id,1,17)=33944879566856193

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: store_customer_order_20230922
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 381663
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
          
        

5.字段类型不同,索引失效
varchar  <- int   字段类型是varchar, 查询条件是int, 索引失效

          
CREATE TABLE t_1 (
   ...
   trace_id varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '链路ID'
   KEY idx_trace_id (trace_id)
   ...
) ENGINE=InnoDB

表记录条数 = 2138670

mysql> EXPLAIN SELECT * FROM t_1 WHERE trace_id = '527455599584432128';
+----+-------------+----------------------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table                      | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------------------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_1                        | NULL       | ref  | idx_trace_id  | idx_trace_id | 258     | const |    2 |   100.00 | NULL  |
+----+-------------+----------------------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

mysql> EXPLAIN SELECT * FROM t_1 WHERE trace_id = 527455599584432128;
+----+-------------+----------------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table                      | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t_1                        | NULL       | ALL  | idx_trace_id  | NULL | NULL    | NULL | 1773276 |    10.00 | Using where |
+----+-------------+----------------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
          
        

int   <- varchar   字段类型是int, 查询条件是varchar, MySQL会将varchar类型隐式转换成int,索引生效
以下2条SQL都可以查询 customer_order_id = 33931868273811456 数据.

          
SELECT * FROM store_customer_order WHERE customer_order_id = '33931868273811456a';
SELECT * FROM store_customer_order WHERE customer_order_id = '33931868273811456';
          
        

6.字符集不同,导致关联查询索引失效

          
CREATE TABLE `school` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_code` varchar(255) DEFAULT NULL,
  `school_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_school_code` (`school_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_code` varchar(255) DEFAULT NULL,
  `student_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_school_code` (`school_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
          
        

以下SQL导致索引失效

          
SELECT j.*,t.* FROM school t, student j WHERE t.school_code = j.school_code AND j.school_code = 'S001'

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: j
   partitions: NULL
         type: ref
possible_keys: idx_school_code
          key: idx_school_code
      key_len: 1023
          ref: const
         rows: 197
     filtered: 100.00
        Extra: NULL
          
        

修改 school 表的字符编码为 utf8mb4, 索引生效.

          
SELECT j.*,t.* FROM school t, student j WHERE t.school_code = j.school_code AND j.school_code = 'S001'

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ref
possible_keys: idx_school_code
          key: idx_school_code
      key_len: 1023
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: j
   partitions: NULL
         type: ref
possible_keys: idx_school_code
          key: idx_school_code
      key_len: 1023
          ref: const
         rows: 197
     filtered: 100.00
        Extra: Using index condition
          
        

7.LIKE '%...'


8.OR

          
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `t_status` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`t_status`),
  KEY `idx_year` (`year`)
) ENGINE=InnoDB
          
        

8.1 以下SQL索引失效

          
SELECT * FROM t_order WHERE t_status=1 OR year=2023 OR create_time='2023-09-24 12:19:00'
          
        

使用关键字OR,那么在它的前面和后面的字段都要有索引, 否则索引失效

8.2
回表范围超过一定比例值会导致索引失效

验证如下
表中数据9999条,t_status=1数据3374条,year=2023数据2531条
以下SQL索引生效

          
SELECT * FROM t2 WHERE t_status=1 OR year=2023

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: index_merge
possible_keys: idx_status,idx_year
          key: idx_status,idx_year
      key_len: 4,4
          ref: NULL
         rows: 5905
     filtered: 100.00
        Extra: Using union(idx_status,idx_year); Using where
          
        

表中数据19998条,t_status=1数据13373条,year=2023数据12530条
以下相同SQL索引失效

          
SELECT * FROM t2 WHERE t_status=1 OR year=2023

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ALL
possible_keys: idx_status,idx_year
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 19840
     filtered: 27.55
        Extra: Using where
          
        

9.范围查询(>=和IN等)

1.回表范围超过一定比例值会导致索引失效
2.range_optimizer_max_mem_size 设置过小

IN子查询


10.NOT IN

回表范围超过一定比例值会导致索引失效

          
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `t_status` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `create_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `idx_status` (`t_status`),
  KEY `idx_year` (`year`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB
          
        

表中数据19999条,t_status=1数据3条,,t_status=2数据10条,t_status=3数据19986条

以下SQL索引生效

          
SELECT * FROM t2 WHERE t_status NOT IN (2,3)

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: range
possible_keys: idx_status
          key: idx_status
      key_len: 4
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using index condition
          
        

以下SQL索引失效

          
SELECT * FROM t2 WHERE t_status NOT IN (1,2)

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ALL
possible_keys: idx_status
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 19838
     filtered: 50.01
        Extra: Using where
          
        

11.NOT EXISTS


12.IS NULL

回表范围超过一定比例值会导致索引失效

验证如下

          
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `order_status` int(11) NOT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_phone` (`phone`)
) ENGINE=InnoDB
          
        

说明: 表中总条数15136, 其中 phone IS NULL 条数14022

以下SQL,索引失效

          
SELECT * FROM t2 WHERE phone IS NULL

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ALL
possible_keys: idx_phone
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14959
     filtered: 93.74
        Extra: Using where
          
        

以下SQL,索引生效

          
SELECT phone FROM t2 WHERE phone IS NULL

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ref
possible_keys: idx_phone
          key: idx_phone
      key_len: 14
          ref: const
         rows: 14022
     filtered: 100.00
        Extra: Using where; Using index
          
        

13.IS NOT NULL

回表范围超过一定比例值会导致索引失效

验证如下

          
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `order_status` int(11) NOT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_phone` (`phone`)
) ENGINE=InnoDB
          
        

说明: 表中总条数39999, 其中 phone IS NOT NULL 条数31113

以下SQL,索引失效

          
SELECT * FROM t2 WHERE phone IS NOT NULL

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ALL
possible_keys: idx_phone
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 40296
     filtered: 50.00
        Extra: Using where
          
        

以下SQL,索引生效

          
SELECT phone FROM t2 WHERE phone IS NOT NULL

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: range
possible_keys: idx_phone
          key: idx_phone
      key_len: 14
          ref: NULL
         rows: 20148
     filtered: 100.00
        Extra: Using where; Using index
          
        

14.<> 和 !=

回表范围超过一定比例值会导致索引失效

验证如下

          
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `t_status` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`t_status`),
  KEY `idx_year` (`year`)
) ENGINE=InnoDB
          
        

表中数据9999条,t_status=1数据11条,t_status=3数据9988条

以下SQL,索引失效

          
SELECT * FROM t2 WHERE t_status != 1

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ALL
possible_keys: idx_status
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10107
     filtered: 98.83
        Extra: Using where
          
        

以下SQL,索引生效

          
SELECT * FROM t2 WHERE t_status != 3

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: range
possible_keys: idx_status
          key: idx_status
      key_len: 4
          ref: NULL
         rows: 12
     filtered: 100.00
        Extra: Using index condition
          
        

15.ORDER BY
当查询条件涉及ORDER BY, LIMIT等条件时, 索引是否生效情况比较复杂,而且与MySQL版本有关.
通常普通索引,未使用LIMIT, 则索引失效
ORDER BY多个索引字段时, 可能索引失效
建议在使用时进行EXPLAIN验证