MySQL索引失效与解决方法实践

比眉伴天荒 2023-10-07 14:46 29阅读 0赞

## 1. 前言 ##

以下测试使用MariaDB，版本为10.0.36，InnoDb版本为5.6.39-83.1。

## 2. 测试用数据库表说明 ##

以下用于测试的数据库表为test\_table\_log、test\_table\_log2，两个表的字段相同，索引不同，如下所示：

CREATE TABLE IF NOT EXISTS test_table_log
    (
       id             varchar(32) NOT NULL COMMENT 'id',
       type           varchar(20) NOT NULL COMMENT 'type',
       cust_info      varchar(20) NOT NULL COMMENT 'cust_info',
       status         varchar(2) NOT NULL COMMENT 'status',
       other1         varchar(20) NOT NULL COMMENT 'other1',
       other2         varchar(20) NOT NULL COMMENT 'other2',
       create_time    DATETIME(3) NOT NULL COMMENT 'create_time',
       update_time    DATETIME(3) NOT NULL COMMENT 'update_time',
       PRIMARY KEY(id)
    )
    COMMENT = '测试流水表';
    
    CREATE TABLE IF NOT EXISTS test_table_log2 like test_table_log;
    
    alter table test_table_log add INDEX idx_ttl_type(type);
    alter table test_table_log add INDEX idx_ttl_cust(cust_info);
    alter table test_table_log add INDEX idx_ttl_status(status);
    alter table test_table_log add INDEX idx_ttl_time(create_time);
    
    alter table test_table_log2 add INDEX idx_ttl2_all(cust_info,create_time,type,status);
    alter table test_table_log2 add INDEX idx_ttl2_time(create_time);

以上两个表行数为500万行，数据相同，type范围为\[‘1’,‘10’\]，cust\_info范围为\[‘1000’,‘2000’\]，status范围为\[‘1’,‘3’\]，other1固定为’1’，other2范围为\[‘1000’,‘2000’\]，create\_time、update\_time可看作插入时的时间。

## 3. 索引生效的执行情况 ##

在以下情况下，查询语句中指定索引时，索引可以生效。

### 3.1. 单列索引 ###

以下查询条件范围应较小，索引才能生效。

#### 3.1.1. 查询条件使用 = ####

select * from test_table_log 
    where cust_info='1881';

执行计划如下：

select_type: SIMPLE
            table: test_table_log
             type: ref
    possible_keys: idx_ttl_cust
              key: idx_ttl_cust
          key_len: 22
              ref: const
             rows: 10556
            Extra: Using index condition

查询操作耗时如下：

Query_time: 2.507578  Lock_time: 0.001224  Rows_sent: 5222  Rows_examined: 5222

查询语句读取索引次数如下：

Handler_read_key         | 1
    Handler_read_next        | 5222

InnoDB缓冲池中索引页与记录数如下：

#### 3.1.2. 范围查询 ####

select * from test_table_log 
    where cust_info>='1881' and cust_info<='1882';
    
    select * from test_table_log 
    where cust_info between '1881' and '1882';

执行计划如下：

select_type: SIMPLE
            table: test_table_log
             type: range
    possible_keys: idx_ttl_cust
              key: idx_ttl_cust
          key_len: 22
              ref: NULL
             rows: 21096
            Extra: Using index condition

#### 3.1.3. 查询条件使用 or ####

针对同一个索引字段

select * from test_table_log 
    where cust_info='1881' or cust_info='1882';

执行计划如下：

select_type: SIMPLE
            table: test_table_log
             type: range
    possible_keys: idx_ttl_cust
              key: idx_ttl_cust
          key_len: 22
              ref: NULL
             rows: 20480
            Extra: Using index condition; Using where

select * from test_table_log 
    where create_time <='2020-03-10 12:20:39.000' and (cust_info='1881' or type='1');

#### 3.1.4. 查询条件使用 in ####

select * from test_table_log 
    where cust_info in ('1881','1882');

执行计划如下：

select_type: SIMPLE
            table: test_table_log
             type: range
    possible_keys: idx_ttl_cust
              key: idx_ttl_cust
          key_len: 22
              ref: NULL
             rows: 20480
            Extra: Using index condition

#### 3.1.5. 查询条件使用 is null/is not null ####

当满足is null/is not null条件的索引字段数据量不大时，可以使用索引。

例如创建总行数为1000的表。

某允许为空的索引字段约50%的记录为非空，约50%的记录为空，is null能使用索引，type为ref；当查询条件为等于某常量or is null时，能使用索引，type为ref\_or\_null。

某允许为空的索引字段约10%的记录为非空，约90%的记录为空，is null能使用索引，type为ref；is not null也能使用索引，type为range。

### 3.2. 联合索引 ###

对于使用联合索引且生效的情况，需要满足查询条件中指定了前面的字段，且查询范围应较小。具体情况略。

## 4. 忽略索引的执行情况 ##

以下为忽略索引时的执行情况，会使用全表扫描。

select * from test_table_log ignore index (idx_ttl_cust) 
    where cust_info='1881';

执行计划如下：

select_type: SIMPLE
            table: test_table_log
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 4760085
            Extra: Using where

查询操作耗时如下：

Query_time: 3.470909  Lock_time: 0.001395  Rows_sent: 5222  Rows_examined: 5000000

查询语句读取索引次数如下：

Handler_read_rnd_next    | 5000001

InnoDB缓冲池中索引页与记录数如下：

## 5. 索引失效及解决方法 ##

### 5.1. 编码不同 ###

参考 https://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html ，在对非二进制字符串列进行比较时，相应的列应当使用相同的字符集。 例如对使用utf8编码的列和使用latin1编码的列进行比较时不会使用索引。

### 5.2. 隐式转换 ###

select * from test_table_log 
    where cust_info=1881;

以上查询使用的字段cust\_info类型为字符串，但查询条件为int，查询时会进行隐式转换，无法使用索引。

执行计划显示为全表扫描，possible\_keys为idx\_ttl\_cust。

查询操作耗时与以上忽略索引的执行情况接近。

查询语句读取索引次数与以上忽略索引的执行情况相同。

InnoDB缓冲池中索引页与以上忽略索引的执行情况相同。

为了防止隐式转换，查询参数需要使用正确的类型。

### 5.3. 函数操作 ###

select * from test_table_log 
    where concat(cust_info,'0')='18810';
    
    select * from test_table_log where time_to_sec(timediff(create_time,'2020-03-10 12:20:40.000'))<3;

以上查询对单列索引对应的字段进行了函数操作，无法使用索引。

执行计划显示为全表扫描，possible\_keys为NULL。

若需要使用函数操作，应当添加在查询条件上，可以使用索引，如下所示：

select * from test_table_log 
    where cust_info=left('18810',4);
    
    select * from test_table_log where create_time<date_add('2020-03-10 12:20:40.000', interval 3 second);

### 5.4. like以通配符开头 ###

不满足最左匹配原则

select * from test_table_log 
    where cust_info like '%1881';

执行计划显示为全表扫描，possible\_keys为NULL。

为了在使用like时能够使用索引，like对应的查询条件不能以’%'开头。

### 5.5. 查询不同的字段使用or ###

当使用or查询两个不同的字段时，索引会失效（关闭index\_merge）。

#### 5.5.1. 单列索引与非索引字段or ####

select * from test_table_log 
    where cust_info='1881' or other1='1';

当查询时使用某个单列索引对应的字段与非索引字段进行or操作时，不会使用索引。

执行计划显示为全表扫描，possible\_keys为idx\_ttl\_cust。

#### 5.5.2. 单列索引字段之间or ####

当查询时使用两个单列索引各自对应的字段进行or操作时，是否使用索引与index\_merge优化器开关有关。查询语句如下。

select * from test_table_log 
    where cust_info='1881' or create_time<='2020-03-10 12:20:39.000';

##### 5.5.2.1. 开启index\_merge #####

index\_merge优化器默认是开启的。

当开启index\_merge时，以上执行计划显示，连接类型为索引合并，使用了idx\_ttl\_cust与idx\_ttl\_time索引。

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>test_table_log</td> 
   <td>index_merge</td> 
   <td>idx_ttl_cust,idx_ttl_time</td> 
   <td>idx_ttl_cust,idx_ttl_time</td> 
   <td>22,7</td> 
   <td>NULL</td> 
   <td>12488</td> 
   <td>Using sort_union(idx_ttl_cust,idx_ttl_time); Using where</td> 
  </tr> 
 </tbody> 
</table>

执行查询操作耗时如下：

Query_time: 2.520033  Lock_time: 0.003090  Rows_sent: 7152  Rows_examined: 7152

##### 5.5.2.2. 关闭index\_merge #####

通过以下命令可以关闭index\_merge，或index\_merge\_sort\_union优化器开关。

set optimizer_switch = 'index_merge=off';
    set optimizer_switch = 'index_merge_sort_union=off';

当关闭index\_merge时，以上执行计划显示为全表扫描，possible\_keys为idx\_ttl\_cust,idx\_ttl\_time。

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>test_table_log</td> 
   <td>ALL</td> 
   <td>idx_ttl_cust,idx_ttl_time</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>4760085</td> 
   <td>Using where</td> 
  </tr> 
 </tbody> 
</table>

执行查询操作耗时如下：

Query_time: 3.925060  Lock_time: 0.001728  Rows_sent: 7152  Rows_examined: 5000000

#### 5.5.3. 使用or导致索引失效优化 ####

在查询中使用两个包含单列索引的字段进行or时，使用union all，可以解决索引失效的问题，可替代开启索引合并的方法。

##### 5.5.3.1. 查询数据 #####

通过union all查询数据时，示例语句如下：

select * from test_table_log 
    where cust_info='1881'
    union all
    select * from test_table_log 
    where create_time<='2020-03-10 12:20:39.000';

##### 5.5.3.2. 查询行数 #####

通过union all查询数据时，示例语句如下：

select count(*) from test_table_log 
    where cust_info='1881'
    union all
    select count(*) from test_table_log 
    where create_time<='2020-03-10 12:20:39.000';

以上查询结果为多行，可以使用sum获取总和，如下所示：

select sum(a.c) from 
    (
    select count(*) as c from test_table_log 
    where cust_info='1881'
    union all
    select count(*) as c from test_table_log 
    where create_time<='2020-03-10 12:20:39.000'
    )as a;

#### 5.5.4. 联合索引内部字段or ####

当联合索引内部的不同字段之间使用or时，无论是否开启index\_merge，索引均会失效。例如对(a,b)列添加联合索引，查询条件为“a = xxx or b = xxx”时，索引会失效。

### 5.6. is null/is not null 范围过大 ###

当满足is null/is not null的索引字段数据太多时，不会使用索引。

select * from test_table_log 
    where cust_info is not null;

执行计划显示为全表扫描，possible\_keys为idx\_ttl\_cust。

### 5.7. 使用!=或<> ###

使用!=或<>时，索引会失效。示例语句如下：

select * from test_table_log 
    where cust_info != '1881';
    
    select * from test_table_log 
    where cust_info <> '1881';

执行计划显示为全表扫描，possible\_keys为idx\_ttl\_cust。

### 5.8. 查询条件使用 not in ###

查询条件使用not in时，索引会失效。示例语句如下：

select * from test_table_log 
    where cust_info not in ('1881');

执行计划显示为全表扫描，possible\_keys为idx\_ttl\_cust。

### 5.9. 查询条件使用 not like ###

查询条件使用not like时，索引会失效。示例语句如下：

select * from test_table_log 
    where cust_info not like '1881%';

执行计划显示为全表扫描，possible\_keys为NULL。

### 5.10. 查询条件范围过大 ###

查询条件对应的数据范围过大时，索引会失效。示例语句如下：

select max(other2) from test_table_log 
    where cust_info<'1919';
    
    select max(other2) from test_table_log 
    where cust_info>'1919';

以上SQL语句均不会使用索引idx\_ttl\_cust。

select_type: SIMPLE
            table: test_table_log
             type: ALL
    possible_keys: idx_ttl_cust
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 4760085
            Extra: Using where

以下针对忽略索引（与上述查询条件范围过大导致索引失效的SQL语句效果相同），及强制使用索引的情况进行对比。

可以看到当查询条件对应的数据范围过大时，有时使用全表扫描比通过二级索引进行查询耗时更短。

*  忽略索引

select max(other2) from test_table_log ignore index (idx_ttl_cust)
    where cust_info>'1919';

*  强制使用索引

select max(other2) from test_table_log force index (idx_ttl_cust)
    where cust_info>'1919';

#### 5.10.1. 执行计划对比 ####

*  忽略索引

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>test_table_log</td> 
   <td>ALL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>4760085</td> 
   <td>Using where</td> 
  </tr> 
 </tbody> 
</table>

*  强制使用索引

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>test_table_log</td> 
   <td>range</td> 
   <td>idx_ttl_cust</td> 
   <td>idx_ttl_cust</td> 
   <td>22</td> 
   <td>NULL</td> 
   <td>831036</td> 
   <td>Using index condition</td> 
  </tr> 
 </tbody> 
</table>

#### 5.10.2. 查询操作耗时对比 ####

*  忽略索引

Query_time: 2.683089  Lock_time: 0.003528  Rows_sent: 1  Rows_examined: 5000000

*  强制使用索引

Query_time: 86.099621  Lock_time: 0.001233  Rows_sent: 1  Rows_examined: 404372

#### 5.10.3. 查询语句读取索引次数对比 ####

*  忽略索引

Handler_read_rnd_next    | 5000001

*  强制使用索引

Handler_read_key         | 1
    Handler_read_next        | 404372

### 5.11. 联合索引未使用前面的字段 ###

在使用联合索引对应字段进行查询时，若未使用前面的字段进行查询，则索引会失效。例如存在索引(a,b,c)时，使用a、a,b、a,b,c查询时能够使用索引，使用b、b,c、c查询时索引会失效。

以下的SQL语句查询时使用了create\_time,type,status字段，存在联合索引(cust\_info,create\_time,type,status)，由于查询条件未使用cust\_info字段，因此联合索引会失效。

select * from test_table_log2 
    where create_time>'2020/3/8 11:57:27' and type='1' and status='1';

执行计划如下，（create\_time字段存在单列索引，但查询条件对应的数据范围太大，因此也不会使用索引）：

select_type: SIMPLE
            table: test_table_log2
             type: ALL
    possible_keys: idx_ttl2_time
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 4727385
            Extra: Using where

### 5.12. in或or查询时数据量太小 ###

数据库表的数据量太小（例如小于等于6行）时，针对同一个索引字段使用or或in操作进行查询时，不会使用索引。

参考 https://dev.mysql.com/doc/refman/5.6/en/table-scan-avoidance.html， 对于数据量小的表，进行全表扫描通常是合适的，并且对性能的影响可以忽略。

如以下SQL语句在执行时不会使用索引。

select * from test_table_log where cust_info in ('1881','1882');
    
    select * from test_table_log where cust_info='1881' or cust_info='1882';

执行计划如下：

select_type: SIMPLE
            table: test_table_log
             type: ALL
    possible_keys: idx_ttl_cust
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 6
            Extra: Using where