MySQL查询操作索引优化实践

爱被打了一巴掌 2023-10-07 14:44 144阅读 0赞

## 1. 前言 ##

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

以下针对索引的优化方法可参考“MySQL索引优化相关原理”。

以下对索引的分析方法可参考“MySQL索引分析除了EXPLAIN还有什么方法”。

## 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. 全表扫描与二级索引回表耗时对比 ##

进行全表扫描时，查询总行数为10万行，每行约33字节的表的全部行的全部字段，耗时约0.16秒。查询总行数为100万行的表，limit限制查询10万行，耗时基本一致。

使用二级索引查询且需要回表时，强制使用二级索引，通过查询条件控制查询出1千行记录，每行约33字节，耗时约0.49秒。通过limit限制查询1千行时，耗时基本一致。

通过以上数据可以看出，全表扫描可能比通过二级索引查询时回表更快。

## 4. 使用区分度不同的索引查询对比 ##

以下针对同一个表，强制使用数据区分度高与数据区分度低的索引分别进行查询。

### 4.1. SQL语句 ###

*  强制使用数据区分度高的索引

select * from test_table_log 
    force index (idx_ttl_cust) 
    where cust_info='1881' and create_time>'2020/3/8 11:57:27' and type='1' and status='1';

*  强制使用数据区分度低的索引

select * from test_table_log 
    force index (idx_ttl_status) 
    where cust_info='1881' and create_time>'2020/3/8 11:57:27' and type='1' and status='1';

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

*  强制使用数据区分度高的索引

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; Using where

*  强制使用数据区分度低的索引

select_type: SIMPLE
            table: test_table_log
             type: ref
    possible_keys: idx_ttl_status
              key: idx_ttl_status
          key_len: 4
              ref: const
             rows: 2380042
            Extra: Using index condition; Using where

根据以上对比可知，强制使用数据区分度低的索引查询，需要检查的行估计值rows大很多。

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

*  强制使用数据区分度高的索引

Query_time: 2.367849  Lock_time: 0.001504  Rows_sent: 391  Rows_examined: 5222

*  强制使用数据区分度低的索引

Query_time: 11.326475  Lock_time: 0.001196  Rows_sent: 391  Rows_examined: 1668791

根据以上对比可知，强制使用数据区分度低的索引查询，耗时及检查行数大很多。

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

SHOW SESSION STATUS LIKE 'Handler_read%';

*  强制使用数据区分度高的索引

Handler_read_key         | 1
    Handler_read_next        | 5222

*  强制使用数据区分度低的索引

Handler_read_key         | 1
    Handler_read_next        | 1668791

根据以上对比可知，强制使用数据区分度低的索引查询，读取索引次数大很多。

### 4.5. InnoDB缓冲池中索引页与记录数对比 ###

*  强制使用数据区分度高的索引

*  强制使用数据区分度低的索引

根据以上对比可知，强制使用数据区分度低的索引查询，InnoDB缓冲池中索引页与记录数大很多。

### 4.6. 读取索引页次数对比 ###

*  强制使用数据区分度高的索引

在执行SQL语句后，读取索引页的次数增加了4811。

*  强制使用数据区分度低的索引

在执行SQL语句后，读取索引页的次数增加了29294。

根据以上对比可知，强制使用数据区分度低的索引查询，读取索引页次数大很多。

## 5. 使用覆盖索引查询/需要回表对比 ##

以下针对查询时使用覆盖索引，及查询需要回表的情况进行对比。

当查询结果及查询条件中不包含除主键、查询结果与查询条件中的索引字段外的其他字段时，查询时会进行使用覆盖索引。

在其他情况下，即查询结果或查询条件中包含除主键、查询结果与条件中的索引字段外的其他字段时，查询时需要回表。

### 5.1. SQL语句 ###

例如对test\_table\_log表进行查询，当查询结果及查询条件中仅包含的字段不超过索引idx\_ttl\_cust对应的cust\_info，及主键id字段时，可以使用覆盖索引。超过以上字段后，查询时需要回表。

*  使用覆盖索引查询

select id,cust_info from test_table_log 
    where cust_info='1881' and id is not null;

*  查询结果需要回表

select id,cust_info,create_time from test_table_log 
    where cust_info='1881';

*  查询条件需要回表

select id,cust_info from test_table_log 
    where cust_info='1881' and create_time >'2000-01-01';

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

*  使用覆盖索引查询

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 where; Using index

*  查询结果需要回表

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

*  查询条件需要回表

使用覆盖索引时，执行计划的Extra列中会显示“Using index”。

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

*  使用覆盖索引查询

Query_time: 0.016890  Lock_time: 0.001350  Rows_sent: 5222  Rows_examined: 5222

*  查询结果需要回表

Query_time: 2.431267  Lock_time: 0.001262  Rows_sent: 5222  Rows_examined: 5222

*  查询条件需要回表

Query_time: 2.463971  Lock_time: 0.001259  Rows_sent: 5222  Rows_examined: 5222

根据以上对比可知，查询需要回表，与使用覆盖索引相比，耗时长很多，检查行数不变。

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

*  使用覆盖索引查询

Handler_read_key         | 1
    Handler_read_next        | 5222

*  查询结果需要回表

Handler_read_key         | 1    
    Handler_read_next        | 5222

*  查询条件需要回表

Handler_read_key         | 1
    Handler_read_next        | 5222

查询语句读取索引次数相同。

### 5.5. InnoDB缓冲池中索引页与记录数对比 ###

*  使用覆盖索引查询

*  查询结果需要回表

*  查询条件需要回表

根据以上对比可知，查询需要回表，与使用覆盖索引相比，InnoDB缓冲池中索引页与记录数多。

对于二级索引的读取次数相同；但回表时需要读取主键索引，使用覆盖索引时不需要读取主键索引。

## 6. 索引条件下推（ICP） ##

### 6.1. 索引条件下推优化生效的场景 ###

索引条件下推优化生效的场景，包括但不限于以下：

联合索引包含（a, b, c）字段，当查询条件使用a，c字段等于常量，不使用b字段时，索引条件下推优化生效；

联合索引包含（a, b）字段，当查询条件使用a字段的范围查询，b字段等于常量时，索引条件下推优化生效。

需要注意，执行SQL语句后，若慢SQL日志中显示的开启/关闭索引条件下推时的Rows\_examined相同，说明查询时检查的行数相同，是否使用索引条件下推执行效率不会有差别。

例如联合索引包含（a, b）字段，当查询条件使用a字段等于常量，b字段的范围查询时，是否启用索引条件下推优化对查询无影响。

示例如下，在开启/关闭索引条件下推时，Rows\_sent与Rows\_examined均相同，说明索引条件下推优化未起作用。

select * from test_table_log2
    where cust_info = '1881' and create_time >'2020-03-10 12:29:29.000';

### 6.2. 开启/关闭索引条件下推对比 ###

以下对开启/关闭索引条件下推时的查询进行对比。

#### 6.2.1. SQL语句 ####

以下语句在开启索引条件下推时，优化能够生效。

select * from test_table_log2
    where cust_info = '1881' and type = '10';

select * from test_table_log2
    where cust_info like '188%' and create_time >'2020-03-10 12:29:29.000';

以下为针对上述第一条SQL的执行情况对比。

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

*  开启索引条件下推

<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_log2</td> 
   <td>ref</td> 
   <td>idx_ttl2_all</td> 
   <td>idx_ttl2_all</td> 
   <td>22</td> 
   <td>const</td> 
   <td>10110</td> 
   <td>Using index condition</td> 
  </tr> 
 </tbody> 
</table>

*  关闭索引条件下推

开启索引条件下推时，执行计划的Extra列显示“Using index condition”。

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

*  开启索引条件下推

Query_time: 0.338027  Lock_time: 0.001234  Rows_sent: 529  Rows_examined: 529

*  关闭索引条件下推

Query_time: 2.545925  Lock_time: 0.000097  Rows_sent: 529  Rows_examined: 5222

根据以上对比可知，开启索引条件下推时，执行耗时短，检查行数少。

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

*  开启索引条件下推

Handler_read_key         | 1
    Handler_read_next        | 529

*  关闭索引条件下推