MySQL查询操作索引优化实践

最新推荐文章于 2023-03-06 20:28:02 发布

adrninistrat0r

最新推荐文章于 2023-03-06 20:28:02 发布

阅读量223

点赞数

分类专栏： MySQL 文章标签：数据库 mysql 索引

本文链接：https://blog.csdn.net/a82514921/article/details/108070191

版权

MySQL 专栏收录该内容

25 篇文章 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缓冲池中索引页与记录数对比

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

| TABLE_NAME                | INDEX_NAME   | page_num | record_num |
+---------------------------+--------------+----------+------------+
| `testdb`.`test_table_log` | PRIMARY      | 4798     | 955437     |
| `testdb`.`test_table_log` | idx_ttl_cust |   15     |   5785     |

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

| TABLE_NAME                | INDEX_NAME     | page_num | record_num |
+---------------------------+----------------+----------+------------+
| `testdb`.`test_table_log` | PRIMARY        | 5158     | 1012502    |
| `testdb`.`test_table_log` | idx_ttl_status |  516     |  230126    |

根据以上对比可知，强制使用数据区分度低的索引查询，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

查询条件需要回表

  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

使用覆盖索引时，执行计划的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缓冲池中索引页与记录数对比

使用覆盖索引查询

| TABLE_NAME                | INDEX_NAME   | page_num | record_num |
+---------------------------+--------------+----------+------------+
| `testdb`.`test_table_log` | idx_ttl_cust |  15      | 5785       |

查询结果需要回表

| TABLE_NAME                | INDEX_NAME   | page_num | record_num |
+---------------------------+--------------+----------+------------+
| `testdb`.`test_table_log` | PRIMARY      | 4798     | 955437     |
| `testdb`.`test_table_log` | idx_ttl_cust |   15     |   5785     |

查询条件需要回表

| TABLE_NAME                | INDEX_NAME   | page_num | record_num |
+---------------------------+--------------+----------+------------+
| `testdb`.`test_table_log` | PRIMARY      | 4798     | 955437     |
| `testdb`.`test_table_log` | idx_ttl_cust |   15     |   5785     |

根据以上对比可知，查询需要回表，与使用覆盖索引相比，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. 执行计划对比

开启索引条件下推

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	test_table_log2	ref	idx_ttl2_all	idx_ttl2_all	22	const	10110	Using index condition

关闭索引条件下推

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	test_table_log2	ref	idx_ttl2_all	idx_ttl2_all	22	const	10110	Using where

开启索引条件下推时，执行计划的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

关闭索引条件下推

Handler_read_key         | 1
Handler_read_next        | 5222

根据以上对比可知，开启索引条件下推时，查询语句读取索引次数少。

6.2.5. 索引条件下推次数

执行以下语句，可以查看当前会话状态中，索引条件下推相关的次数，Handler_icp_attempts代表尝试执行索引条件下推的次数，Handler_icp_match代表执行索引条件下推且匹配的次数。

show status like 'Handler_icp_%';

INFORMATION_SCHEMA.INNODB_METRICS表中也包含NAME含“icp_”的记录，说明略。

开启索引条件下推

Handler_icp_attempts | 5222
Handler_icp_match    | 529

关闭索引条件下推

Handler_icp_attempts | 0
Handler_icp_match    | 0

根据以上对比可知，开启索引条件下推时，上述索引条件下推次数增加。

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

开启索引条件下推

| TABLE_NAME                 | INDEX_NAME   | page_num | record_num |
+----------------------------+--------------+----------+------------+
| `testdb`.`test_table_log2` | PRIMARY      | 585      | 128260     |
| `testdb`.`test_table_log2` | idx_ttl2_all |  20      |   6156     |

关闭索引条件下推

| TABLE_NAME                 | INDEX_NAME   | page_num | record_num |
+----------------------------+--------------+----------+------------+
| `testdb`.`test_table_log2` | PRIMARY      | 4798     | 955437     |
| `testdb`.`test_table_log2` | idx_ttl2_all |   20     |   6156     |

根据以上对比可知，开启索引条件下推时，InnoDB缓冲池中索引页与记录数少。