MySQL查询操作索引优化实践

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

1. 前言

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

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

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

2. 测试用数据库表说明

以下用于测试的数据库表为test_table_log、test_table_log2，两个表的字段相同，索引不同，如下所示：

  1. CREATE TABLE IF NOT EXISTS test_table_log
  2. (
  3.    id             varchar(32) NOT NULL COMMENT 'id',
  4.    type           varchar(20) NOT NULL COMMENT 'type',
  5.    cust_info      varchar(20) NOT NULL COMMENT 'cust_info',
  6.    status         varchar(2) NOT NULL COMMENT 'status',
  7.    other1         varchar(20) NOT NULL COMMENT 'other1',
  8.    other2         varchar(20) NOT NULL COMMENT 'other2',
  9.    create_time    DATETIME(3) NOT NULL COMMENT 'create_time',
  10.    update_time    DATETIME(3) NOT NULL COMMENT 'update_time',
  11.    PRIMARY KEY(id)
  12. )
  13. COMMENT = '测试流水表';
  15. CREATE TABLE IF NOT EXISTS test_table_log2 like test_table_log;
  17. alter table test_table_log add INDEX idx_ttl_type(type);
  18. alter table test_table_log add INDEX idx_ttl_cust(cust_info);
  19. alter table test_table_log add INDEX idx_ttl_status(status);
  20. alter table test_table_log add INDEX idx_ttl_time(create_time);
  22. alter table test_table_log2 add INDEX idx_ttl2_all(cust_info,create_time,type,status);
  23. 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

    1.     table: test_table_log
    2.      type: ref

    possible_keys: idx_ttl_cust

    1.       key: idx_ttl_cust
    2.   key_len: 22
    3.       ref: const
    4.      rows: 10556
    5.     Extra: Using index condition; Using where

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

    select_type: SIMPLE

    1.     table: test_table_log
    2.      type: ref

    possible_keys: idx_ttl_status

    1.       key: idx_ttl_status
    2.   key_len: 4
    3.       ref: const
    4.      rows: 2380042
    5.     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. 查询语句读取索引次数对比

  1. 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

    1.     table: test_table_log
    2.      type: ref

    possible_keys: idx_ttl_cust

    1.       key: idx_ttl_cust
    2.   key_len: 22
    3.       ref: const
    4.      rows: 10556
    5.     Extra: Using where; Using index

  • 查询结果需要回表

    select_type: SIMPLE

    1.     table: test_table_log
    2.      type: ref

    possible_keys: idx_ttl_cust

    1.       key: idx_ttl_cust
    2.   key_len: 22
    3.       ref: const
    4.      rows: 10556
    5.     Extra: Using index condition

  • 查询条件需要回表

    select_type: SIMPLE

    1.     table: test_table_log
    2.      type: ref

    possible_keys: idx_ttl_cust

    1.       key: idx_ttl_cust
    2.   key_len: 22
    3.       ref: const
    4.      rows: 10556
    5.     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均相同，说明索引条件下推优化未起作用。

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

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

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

6.2.1. SQL语句

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

  1. select * from test_table_log2
  2. where cust_info = '1881' and type = '10';
  4. select * from test_table_log2
  5. 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代表执行索引条件下推且匹配的次数。

  1. 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缓冲池中索引页与记录数少。

发表评论

表情：
评论列表 （有 0 条评论，111人围观）

还没有评论，来说两句吧...

相关阅读

    相关 mysql索引优化实践

    背景 最近在写一些数据统计的面板，里面有sql对表数据的聚合统计，我的主表现在有100来万的数据，其间看了很多资料。记录一下sql索引的优化过程. sql 如下,只有一个

    心已赠人心已赠人/ 0 赞/ 268 阅读

    相关 mysql查询索引优化

    索引一般用于在数据规模大时对查询进行优化的一种机制，对于一般的查询来说，[MySQL][]会去遍历整个表，来查询符合要求的结果；如果借助于索引，[mysql][MySQL]会将

    Myth丶恋晨Myth丶恋晨/ 0 赞/ 455 阅读