MySQL 加索引后 SQL 执行变慢

引言

第一次遇到加索引后 SQL 执行变慢的现象,尽管这个索引是用于优化另一条 SQL 所创建。

经确认根本原因是关联算法发生变化,从 Block Nested-Loop Join 变为 Index Nested-Loop Join。执行计划显示驱动表全表扫描,被驱动表索引性能低,导致扫描行数成倍增加。因此使用 join buffer 尽管不慢但并不合理,而索引有效的前提是索引性能足够高。

本文复现了该问题,详细计算了加索引后的实际扫描行数与估算扫描行数,并进行了成本分析。

现象

时间:20231118

数据库版本:5.7.24

现象:为优化一条 update,加索引后另一条 select 执行变慢,扫描行数增加。

分析

SQL

SQL

select 
  sum(reputd.EXPECTED_QTY) 
from 
  CO_PUTAWAY_REPUT_D reputd 
  join CO_PUTAWAY_REPUT_M reputm on reputd.container_no = reputm.container_no 
where 
  reputd.ex_no = 'PE570' 
  and reputd.goods_no = '100030504460' 
  and reputm.reput_status = 1 
  and reputd.yn = 1 
  and reputm.org_no = '760' 
  and reputm.distribute_no = '608' 
  and reputm.warehouse_no = '16'

执行计划

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: reputm
   partitions: NULL
         type: ALL
possible_keys: CONTAINER_NO_IDX
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 858
     filtered: 0.12
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: reputd
   partitions: NULL
         type: ref
possible_keys: IDX_CONTAINER_NO
          key: IDX_CONTAINER_NO
      key_len: 98
          ref: ib_receiving.reputm.CONTAINER_NO
         rows: 48
     filtered: 0.10
        Extra: Using where
2 rows in set1 warning (0.02 sec)

其中:

  • 驱动表索引失效全表扫描
  • 被驱动表索引性能一般

执行用时

1 row in set (0.21 sec)

表结构

mysql> show create table CO_PUTAWAY_REPUT_m G
*************************** 1. row ***************************
       Table: CO_PUTAWAY_REPUT_m
Create TableCREATE TABLE `co_putaway_reput_m` (
  `ID` bigint(20NOT NULL AUTO_INCREMENT,
  `CONTAINER_NO` varchar(32NOT NULL,
  `DAMAGE_FLAG` int(11NOT NULL DEFAULT '0',
  `HANDLE_TIME` datetime DEFAULT NULL,
  `HANDLE_USER` varchar(50DEFAULT NULL,
  `REPUT_STATUS` int(11NOT NULL DEFAULT '0',
  `ORG_NO` varchar(20DEFAULT NULL,
  `DISTRIBUTE_NO` varchar(20DEFAULT NULL,
  `WAREHOUSE_NO` varchar(20DEFAULT NULL,
  `CREATE_USER` varchar(50DEFAULT NULL,
  `CREATE_TIME` datetime NOT NULL DEFAULT '1900-01-01 00:00:00',
  `UPDATE_USER` varchar(50DEFAULT NULL,
  `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `YN` int(11NOT NULL DEFAULT '0',
  `TASK_NO` varchar(32DEFAULT NULL,
  `IS_AR` int(11DEFAULT '0',
  `PUTAWAY_TYPE` int(11NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `idx_update_time` (`UPDATE_TIME`),
  KEY `CONTAINER_NO_IDX` (`CONTAINER_NO`,`ORG_NO`,`DISTRIBUTE_NO`,`WAREHOUSE_NO`USING BTREE
ENGINE=InnoDB AUTO_INCREMENT=69007 DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

mysql> show create table CO_PUTAWAY_REPUT_D G
*************************** 1. row ***************************
       Table: CO_PUTAWAY_REPUT_D
Create TableCREATE TABLE `co_putaway_reput_d` (
  `ID` bigint(20NOT NULL AUTO_INCREMENT,
  `CONTAINER_NO` varchar(32NOT NULL,
  `EX_NO` varchar(32NOT NULL,
  `BIZ_CODE` varchar(128DEFAULT NULL,
  `BIZ_TYPE` varchar(32NOT NULL,
  `OWNER_NO` varchar(20NOT NULL,
  `GOODS_NO` varchar(30NOT NULL,
  `LOT_NO` varchar(100NOT NULL,
  `PACKING_UNIT` varchar(20NOT NULL,
  `GOODS_NAME` varchar(300NOT NULL,
  `STOCK_LEVEL` varchar(32DEFAULT NULL,
  `EXPECTED_QTY` decimal(15,4NOT NULL,
  `COMM_AREA_NO` varchar(30DEFAULT NULL,
  `COMM_CELL_NO` varchar(30DEFAULT NULL,
  `COMM_CONTAINER_NO` varchar(30DEFAULT NULL,
  `COMM_CONS_TYPE` int(11DEFAULT NULL,
  `ORG_NO` varchar(20DEFAULT NULL,
  `DISTRIBUTE_NO` varchar(20DEFAULT NULL,
  `WAREHOUSE_NO` varchar(20DEFAULT NULL,
  `CREATE_TIME` datetime NOT NULL DEFAULT '1900-01-01 00:00:00',
  `create_user` varchar(50DEFAULT NULL,
  `update_user` varchar(50DEFAULT NULL,
  `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `YN` int(11NOT NULL DEFAULT '0',
  `TASK_TYPE` int(11DEFAULT NULL,
  `IS_BIG` int(11DEFAULT NULL,
  `IS_SMALL` int(11DEFAULT NULL,
  `IS_PROMOTION` int(11DEFAULT NULL,
  `TOTAL_VOLUME` decimal(18,4DEFAULT NULL,
  `TASK_NO` varchar(32DEFAULT NULL,
  `REPUT_M_ID` bigint(20DEFAULT NULL,
  `PRODUCT_LEVEL` varchar(50DEFAULT NULL,
  `MD5_VALUE` varchar(32DEFAULT NULL,
  `BOX_REGULATIONS` varchar(50DEFAULT NULL,
  `EXPECTED_BOX_QTY` decimal(15,4NOT NULL DEFAULT '0.0000',
  PRIMARY KEY (`ID`),
  KEY `idx_update_time` (`UPDATE_TIME`),
  KEY `REPUT_M_ID_IDX` (`REPUT_M_ID`USING BTREE,
  KEY `IDX_CONTAINER_NO` (`CONTAINER_NO`,`ORG_NO`,`DISTRIBUTE_NO`,`WAREHOUSE_NO`USING BTREE
ENGINE=InnoDB AUTO_INCREMENT=611357 DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

表大小

mysql> show table status like 'CO_PUTAWAY_REPUT_m' G
*************************** 1. row ***************************
           Name: co_putaway_reput_m
         EngineInnoDB
        Version10
     Row_format: Dynamic
           Rows729
 Avg_row_length: 2899
    Data_length: 2113536
Max_data_length: 0
   Index_length: 81920
      Data_free: 13631488
 Auto_increment: 69007
    Create_time: 2023-11-18 16:07:17
    Update_time: 2023-11-20 10:11:45
     Check_time: NULL
      Collation: utf8_general_ci
       ChecksumNULL
 Create_options: 
        Comment
1 row in set (0.02 sec)

mysql> show table status like 'CO_PUTAWAY_REPUT_D' G
*************************** 1. row ***************************
           Name: co_putaway_reput_d
         EngineInnoDB
        Version10
     Row_format: Dynamic
           Rows6692
 Avg_row_length: 629
    Data_length: 4210688
Max_data_length: 0
   Index_length: 13008896
      Data_free: 309329920
 Auto_increment: 611357
    Create_time: 2023-11-19 02:00:58
    Update_time: 2023-11-20 10:11:45
     Check_time: NULL
      Collation: utf8_general_ci
       ChecksumNULL
 Create_options: 
        Comment
1 row in set (0.02 sec)

其中:

  • CO_PUTAWAY_REPUT_m 是小表,CO_PUTAWAY_REPUT_D 是大表,其中 d 表是明细表,两张表通过 CONTAINER_NO 字段关联,关系是一对多;
  • 因此执行计划是小表驱动大表,正常。注意所谓的小表指的是结果集小的表。

小表加索引

查看驱动表 SQL 的执行计划。

mysql> explain select count(*) from CO_PUTAWAY_REPUT_M reputm where reputm.reput_status=1 and reputm.org_no='760' and reputm.distribute_no='608' and reputm.warehouse_no='16' G
*************************** 1. row ***************************
           id1
  select_type: SIMPLE
        table: reputm
   partitionsNULL
         typeALL
possible_keys: NULL
          keyNULL
      key_len: NULL
          refNULL
         rows848
     filtered: 0.12
        Extra: Using where
1 row in set1 warning (0.02 sec)

由于执行计划显示驱动表全表扫描,因此首先给 m 表加索引。

mysql> alter table CO_PUTAWAY_REPUT_M 
add index idx_test(`ORG_NO`,`DISTRIBUTE_NO`,`WAREHOUSE_NO`, CONTAINER_NO);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行计划

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: reputd
   partitions: NULL
         type: ALL
possible_keys: IDX_CONTAINER_NO
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7380
     filtered: 0.10
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: reputm
   partitions: NULL
         type: ref
possible_keys: CONTAINER_NO_IDX,idx_test
          key: CONTAINER_NO_IDX
      key_len: 287
          ref: ib_receiving.reputd.CONTAINER_NO,const,const,const
         rows: 5
     filtered: 10.00
        Extra: Using where
2 rows in set1 warning (0.02 sec)

其中:

  • 驱动表发生变化,大表驱动小表
  • 驱动表依然是全表扫描,表明驱动表索引性能一般

执行用时

1 row in set (0.04 sec)

显示执行用时从 0.21s 降低到 0.04s。

大表加索引

由于新的驱动表依然是全表扫描,因此接下来又给 d 表加索引。

mysql> alter table CO_PUTAWAY_REPUT_D 
add index idx_test(ex_no,goods_no,yn,container_no);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行计划

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: reputd
   partitions: NULL
         type: ref
possible_keys: IDX_CONTAINER_NO,idx_test
          key: idx_test
      key_len: 194
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: reputm
   partitions: NULL
         type: ref
possible_keys: CONTAINER_NO_IDX,idx_test
          key: CONTAINER_NO_IDX
      key_len: 287
          ref: ib_receiving.reputd.CONTAINER_NO,const,const,const
         rows: 5
     filtered: 10.00
        Extra: Using where
2 rows in set1 warning (0.03 sec)

执行用时

1 row in set (0.02 sec)

显示执行用时从 0.04s 降低到 0.02s,理论上性能达到最优。

问题分析

2023-11-18 02:12:54,给 d 表加索引。

CREATE INDEX IDX_CONTAINER_NO USING BTREE ON ib_receiving.co_putaway_reput_d 
(CONTAINER_NO,ORG_NO,DISTRIBUTE_NO,WAREHOUSE_NO);

执行变慢,扫描行数从 678,471 增大到 204,583,347,执行用时从 0.425 增大到 327.936

MySQL 加索引后 SQL 执行变慢

2023-11-18 15:44:29,分别给 m、d 表加索引。

CREATE INDEX CONTAINER_NO_IDX USING BTREE ON ib_receiving.co_putaway_reput_m 
(CONTAINER_NO,ORG_NO,DISTRIBUTE_NO,WAREHOUSE_NO);
CREATE INDEX CONTAINER_NO_IDX USING BTREE ON ib_receiving.co_putaway_reput_d 
(ex_no,ORG_NO,DISTRIBUTE_NO,WAREHOUSE_NO);

执行变快,数据结转,扫描行数从 1,949,940 减少到 103,160,执行用时从 2.643 下降到 0.313

MySQL 加索引后 SQL 执行变慢

2023-11-19 02:02:48,给 d 表加索引。

alter table CO_PUTAWAY_REPUT_D 
add index idx_ex_goods_no(ex_no,goods_no,yn,container_no);

加索引后没有出现慢 SQL。


主要存在以下两个问题:

  • 为什么创建第二个索引后驱动表发生变化
  • 为什么创建第一个索引后 SQL 执行变慢

其中第一个问题暂不讨论,本文主要分析第二个问题。


已知第一个索引的原因是用于优化以下 update。

update 
  co_putaway_reput_d 
set 
  yn = 1 
where 
  container_no = '000010016001322' 
  and yn = 0 
  and org_no = '760' 
  and distribute_no = '608' 
  and warehouse_no = '16';

加索引后的执行计划

*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: co_putaway_reput_d
   partitionsNULL
         typerange
possible_keys: IDX_CONTAINER_NO
          key: IDX_CONTAINER_NO
      key_len: 287
          ref: const,const,const,const
         rows18
     filtered: 100.00
        Extra: Using where
1 row in set (0.02 sec)

因此最奇怪的现象是创建索引后 update 执行变快,但是另一条 select 执行变慢

测试

从线上导出表结构与数据到测试环境,并将后加的索引全部删除,恢复到原始的表结构,用于复现并分析加索引后 SQL 变慢的现象。

原始表结构

mysql> show create table CO_PUTAWAY_REPUT_D G
*************************** 1. row ***************************
       Table: CO_PUTAWAY_REPUT_D
Create TableCREATE TABLE `co_putaway_reput_d` (
  ...
  PRIMARY KEY (`ID`),
  KEY `idx_update_time` (`UPDATE_TIME`),
  KEY `REPUT_M_ID_IDX` (`REPUT_M_ID`USING BTREE
ENGINE=InnoDB AUTO_INCREMENT=611488 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table CO_PUTAWAY_REPUT_m G
*************************** 1. row ***************************
       Table: CO_PUTAWAY_REPUT_m
Create TableCREATE TABLE `co_putaway_reput_m` (
  ...
  PRIMARY KEY (`ID`),
  KEY `idx_update_time` (`UPDATE_TIME`)
ENGINE=InnoDB AUTO_INCREMENT=69016 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

表大小

mysql> select count(*) from CO_PUTAWAY_REPUT_D;
+----------+
| count(*) |
+----------+
|     6966 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from CO_PUTAWAY_REPUT_m;
+----------+
| count(*) |
+----------+
|      738 |
+----------+
1 row in set (0.00 sec)

注意 m 表的全部数据都满足条件。

mysql> select count(*) from CO_PUTAWAY_REPUT_M reputm 
where reputm.reput_status=1 and reputm.org_no='760' and reputm.distribute_no='608' and reputm.warehouse_no='16';
+----------+
| count(*) |
+----------+
|      738 |
+----------+
1 row in set (0.00 sec)

统计信息

mysql> show table status like 'co_putaway_reput_d' G
*************************** 1. row ***************************
           Name: co_putaway_reput_d
         EngineInnoDB
        Version10
     Row_format: Dynamic
           Rows6811
 Avg_row_length: 541
    Data_length: 3686400
Max_data_length: 0
   Index_length: 3407872
      Data_free: 5242880
 Auto_increment: 611488
    Create_time: 2023-11-20 15:24:16
    Update_time: 2023-11-20 11:15:51
     Check_time: NULL
      Collation: utf8_general_ci
       ChecksumNULL
 Create_options: 
        Comment
1 row in set (0.00 sec)

mysql> show table status like 'co_putaway_reput_m' G
*************************** 1. row ***************************
           Name: co_putaway_reput_m
         EngineInnoDB
        Version10
     Row_format: Dynamic
           Rows738
 Avg_row_length: 177
    Data_length: 131072
Max_data_length: 0
   Index_length: 65536
      Data_free: 0
 Auto_increment: 69016
    Create_time: 2023-11-20 11:25:31
    Update_time: 2023-11-20 11:15:57
     Check_time: NULL
      Collation: utf8_general_ci
       ChecksumNULL
 Create_options: 
        Comment
1 row in set (0.01 sec)

其中:

  • m 表准确行数 738,预估行数 738,两者相等;
  • d 表准确行数 6966,预估行数 6811,两者不等。

原始执行计划

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: reputm
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 738
     filtered: 0.14
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: reputd
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6811
     filtered: 0.01
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set1 warning (0.01 sec)

执行用时

1 row in set (0.00 sec)

日志中显示扫描行数 7,704

mysql> select 6966+738;
+----------+
| 6966+738 |
+----------+
|     7704 |
+----------+
1 row in set (0.00 sec)

其中:

  • 慢日志中显示扫描行数等于两张表的数据行数之和,原因是关联算法使用 Block Nested Loop。

BNL 算法的执行流程是将驱动表的结果集加载到内存后保存在 join buffer 中,然后批量扫描被驱动表。

显然该算法的执行依赖 join buffer,默认大小 256 KB。

mysql> select @@join_buffer_size/1024;
+-------------------------+
| @@join_buffer_size/1024 |
+-------------------------+
|                256.0000 |
+-------------------------+
1 row in set (0.00 sec)

计算 join buffer 是否足够保存 m 表的全部数据。

mysql> select TABLE_NAME,TABLE_ROWS,AVG_ROW_LENGTH 
from information_schema.tables where table_name='co_putaway_reput_m';
+--------------------+------------+----------------+
| TABLE_NAME         | TABLE_ROWS | AVG_ROW_LENGTH |
+--------------------+------------+----------------+
| co_putaway_reput_m |        738 |            177 |
+--------------------+------------+----------------+
1 row in set (0.01 sec)

mysql> select 177*738;
+---------+
| 177*738 |
+---------+
|  130626 |
+---------+
1 row in set (0.00 sec)

mysql> select @@join_buffer_size;
+--------------------+
| @@join_buffer_size |
+--------------------+
|             262144 |
+--------------------+
1 row in set (0.01 sec)

其中:

  • 假设 join buffer 可以全部用于保存 m 表的数据,因此理论上只需要访问一次被驱动表。

d 表加索引后执行变慢

加索引

mysql> alter table co_putaway_reput_d add index IDX_CONTAINER_NO(CONTAINER_NO,ORG_NO,DISTRIBUTE_NO,WAREHOUSE_NO);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行计划

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: reputm
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 738
     filtered: 0.14
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: reputd
   partitions: NULL
         type: ref
possible_keys: IDX_CONTAINER_NO
          key: IDX_CONTAINER_NO
      key_len: 98
          ref: cctest.reputm.CONTAINER_NO
         rows: 44
     filtered: 0.11
        Extra: Using where
2 rows in set1 warning (0.00 sec)

执行用时从 0.00s 增大到 0.11s,显示执行变慢,因此复现问题。

1 row in set (0.11 sec)

慢日志显示扫描行数从 7704 增大到 77492,显示扫描行数增加。

MySQL 加索引后 SQL 执行变慢

下面分别计算实际扫描行数与估算扫描行数,用于分析执行变慢的原因。

实际扫描行数

由于被驱动表的联合索引仅使用到了第一个字段,并且 type: ref,表明查询索引中指定值时,将顺序扫描等于该值的数据行,因此等于该值的数据有多少行就扫描多少行。

如下所示,查询 d 表中每个值的重复次数。

mysql> select 
  reputd.CONTAINER_NO, 
  count(*) 
from 
  CO_PUTAWAY_REPUT_D reputd 
where 
  reputd.container_no in (
    select 
      reputm.container_no 
    from 
      CO_PUTAWAY_REPUT_M reputm 
    where 
      reputm.reput_status = 1 
      and reputm.org_no = '760' 
      and reputm.distribute_no = '608' 
      and reputm.warehouse_no = '16'
  ) 
group by 
  reputd.CONTAINER_NO limit 3;
+-----------------+----------+
| CONTAINER_NO    | count(*) |
+-----------------+----------+
| 000010016000050 |        3 |
| 000010016000089 |        6 |
| 000010016000100 |        5 |
+-----------------+----------+
3 rows in set (0.02 sec)

然后查询 m 表查询结果中每个值的查询次数。

mysql> select 
  reputm.container_no, 
  count(*) 
from 
  CO_PUTAWAY_REPUT_M reputm 
where 
  reputm.reput_status = 1 
  and reputm.org_no = '760' 
  and reputm.distribute_no = '608' 
  and reputm.warehouse_no = '16' 
group by 
  reputm.container_no limit 3;
+-----------------+----------+
| container_no    | count(*) |
+-----------------+----------+
| 000010016000050 |        3 |
| 000010016000089 |        1 |
| 000010016000100 |        1 |
+-----------------+----------+
3 rows in set (0.00 sec)

每个值的重复次数✖️查询次数并相加就可以计算出被驱动表的扫描行数,因此将以上两个查询结果进行关联查询。

mysql> select 
  sum(t1.cn * t2.cn) as scan_rows
from 
  (
    select 
      reputm.container_no as no
      count(*) as cn 
    from 
      CO_PUTAWAY_REPUT_M reputm 
    where 
      reputm.reput_status = 1 
      and reputm.org_no = '760' 
      and reputm.distribute_no = '608' 
      and reputm.warehouse_no = '16' 
    group by 
      reputm.container_no
  ) as t1 
  inner join (
    select 
      reputd.CONTAINER_NO as no
      count(*) as cn 
    from 
      CO_PUTAWAY_REPUT_D reputd 
    where 
      reputd.container_no in (
        select 
          reputm.container_no 
        from 
          CO_PUTAWAY_REPUT_M reputm 
        where 
          reputm.reput_status = 1 
          and reputm.org_no = '760' 
          and reputm.distribute_no = '608' 
          and reputm.warehouse_no = '16'
      ) 
    group by 
      reputd.CONTAINER_NO
  ) as t2 on t1.no = t2.no;
+-----------+
| scan_rows |
+-----------+
|     76754 |
+-----------+
1 row in set (0.02 sec)

加上驱动表的扫描行数就是最终的扫描行数。

mysql> select 76754+738;
+-----------+
| 76754+738 |
+-----------+
|     77492 |
+-----------+
1 row in set (0.00 sec)

显示计算出的扫描行数等于慢日志中显示的扫描行数

估算扫描行数

执行计划中显示被驱动表通过 type: ref 方式访问索引 key: IDX_CONTAINER_NO,也就是针对驱动表的每条记录分别执行 CONTAINER_NO = 常数 的等值查询,可是实际执行前无法像前一节中计算索引列每个值的准确重复次数,这种场景下就需要通过索引统计数据计算索引列所有值的平均重复次数。

查看 mysql.innodb_index_stats 表中的统计项【索引列不重复的值有多少】。

mysql> select * from mysql.innodb_index_stats t 
where t.database_name='cctest' and t.table_name='co_putaway_reput_d' and index_name='IDX_CONTAINER_NO';
+---------------+--------------------+------------------+---------------------+--------------+------------+-------------+---------------------------------------------------+
| database_name | table_name         | index_name       | last_update         | stat_name    | stat_value | sample_size | stat_description                                  |
+---------------+--------------------+------------------+---------------------+--------------+------------+-------------+---------------------------------------------------+
| cctest        | co_putaway_reput_d | IDX_CONTAINER_NO | 2023-11-20 15:24:16 | n_diff_pfx01 |        152 |          18 | CONTAINER_NO                                      |
| cctest        | co_putaway_reput_d | IDX_CONTAINER_NO | 2023-11-20 15:24:16 | n_diff_pfx02 |        152 |          18 | CONTAINER_NO,ORG_NO                               |
| cctest        | co_putaway_reput_d | IDX_CONTAINER_NO | 2023-11-20 15:24:16 | n_diff_pfx03 |        152 |          18 | CONTAINER_NO,ORG_NO,DISTRIBUTE_NO                 |
| cctest        | co_putaway_reput_d | IDX_CONTAINER_NO | 2023-11-20 15:24:16 | n_diff_pfx04 |        152 |          18 | CONTAINER_NO,ORG_NO,DISTRIBUTE_NO,WAREHOUSE_NO    |
| cctest        | co_putaway_reput_d | IDX_CONTAINER_NO | 2023-11-20 15:24:16 | n_diff_pfx05 |       6966 |          18 | CONTAINER_NO,ORG_NO,DISTRIBUTE_NO,WAREHOUSE_NO,ID |
| cctest        | co_putaway_reput_d | IDX_CONTAINER_NO | 2023-11-20 15:24:16 | n_leaf_pages |         18 |        NULL | Number of leaf pages in the index                 |
| cctest        | co_putaway_reput_d | IDX_CONTAINER_NO | 2023-11-20 15:24:16 | size         |         19 |        NULL | Number of pages in the index                      |
+---------------+--------------------+------------------+---------------------+--------------+------------+-------------+---------------------------------------------------+
7 rows in set (0.00 sec)

其中:

  • n_diff_pfx01 = n_diff_pfx02 = n_diff_pfx03 = n_diff_pfx04,表明所有列的联合索引的后三个字段完全相同,因此可以将联合索引看作单列索引;
mysql> select count(distinct ORG_NO,DISTRIBUTE_NO,WAREHOUSE_NO) from co_putaway_reput_d 
group by ORG_NO,DISTRIBUTE_NO,WAREHOUSE_NO;
+---------------------------------------------------+
| count(distinct ORG_NO,DISTRIBUTE_NO,WAREHOUSE_NO) |
+---------------------------------------------------+
|                                                 1 |
+---------------------------------------------------+
1 row in set (0.01 sec)
  • n_diff_pfx01 = 152,表明索引列 CONTAINER_NO 字段不重复的值有 152 个;
  • 估算的平均重复次数等于准确的平均重复次数,原因是 sample_size = n_leaf_pages = 18,表明为生成统计数据而采样的页面数量等于索引的叶子节点数,相当于全表扫描而不是采样。
mysql> select count(distinct CONTAINER_NO) from co_putaway_reput_d;
+------------------------------+
| count(distinct CONTAINER_NO) |
+------------------------------+
|                          152 |
+------------------------------+
1 row in set (0.00 sec)

对于有多个列的联合索引来说,采样的页面数量是:innodb_stats_persistent_sample_pages × 索引列的个数。当需要采样的页面数量大于该索引的叶子节点数量的话,就直接采用全表扫描来统计索引列的不重复值数量了。所以大家可以在查询结果中看到不同索引对应的size列的值可能是不同的。

根据统计项【索引列不重复的值有多少】计算所有值的平均重复次数。

统计信息显示表中共有 6811 条数据,其中有 152 条不同的值,因此平均每个值对应 44.8092 条数据。

mysql> select 6811/152;
+----------+
| 6811/152 |
+----------+
|  44.8092 |
+----------+
1 row in set (0.00 sec)

根据连接查询的成本计算公式:

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

计算连接查询的估算扫描行数。

mysql> select 6811/152*738+738;
+------------------+
| 6811/152*738+738 |
+------------------+
|       33807.1974 |
+------------------+
1 row in set (0.01 sec)

对比实际扫描行数,显示实际是估算的 2 倍以上,表明计算误差较大。

mysql> select 77492/33807.1974;
+------------------+
| 77492/33807.1974 |
+------------------+
|           2.2922 |
+------------------+
1 row in set (0.00 sec)

怀疑是由于数据分布不均匀导致统计误差,因此查看数据分布。

mysql> select 
      reputd.CONTAINER_NO as no
      count(*) as cn 
    from 
      CO_PUTAWAY_REPUT_D reputd 
    where 
      reputd.container_no in (
        select 
          reputm.container_no 
        from 
          CO_PUTAWAY_REPUT_M reputm 
        where 
          reputm.reput_status = 1 
          and reputm.org_no = '760' 
          and reputm.distribute_no = '608' 
          and reputm.warehouse_no = '16'
      ) 
    group by 
      reputd.CONTAINER_NO order by cn desc limit 3;
+-----------------+-----+
| no              | cn  |
+-----------------+-----+
| 000010016001335 | 428 |
| 000010016002588 | 366 |
| 000010016001666 | 264 |
+-----------------+-----+
3 rows in set (0.02 sec)

mysql> select 
      reputm.container_no as no
      count(*) as cn 
    from 
      CO_PUTAWAY_REPUT_M reputm 
    where 
      reputm.reput_status = 1 
      and reputm.org_no = '760' 
      and reputm.distribute_no = '608' 
      and reputm.warehouse_no = '16' 
    group by 
      reputm.container_no order by cn desc limit 3;
+-----------------+----+
| no              | cn |
+-----------------+----+
| 000010016001335 | 36 |
| 000010016002588 | 27 |
| 000010016002589 | 21 |
+-----------------+----+
3 rows in set (0.01 sec)

其中:

  • 估算扫描行数误差较大的原因是数据分布不均匀,重复最多的数据被查询的次数最多。


查看 trace 中显示的所有值的平均重复次数。

{
  "plan_prefix": [
  ],
  "table""`co_putaway_reput_m` `reputm`",
  "best_access_path": {
    "considered_access_paths": [
      {
        "rows_to_scan"738,
        "access_type""scan",
        "resulting_rows"1,
        "cost"155.6,
        "chosen"true
      }
    ]
  },
  "condition_filtering_pct"100,
  "rows_for_plan"1,
  "cost_for_plan"155.6,
  "rest_of_plan": [
    {
      "plan_prefix": [
        "`co_putaway_reput_m` `reputm`"
      ],
      "table""`co_putaway_reput_d` `reputd`",
      "best_access_path": {
        "considered_access_paths": [
          {
            "access_type""ref",
            "index""IDX_CONTAINER_NO",
            "rows"44.809,
            "cost"53.771,
            "chosen"true
          },
          {
            "access_type""scan",
            "cost"1587.2,
            "rows"6811,
            "chosen"false,
            "cause""cost"
          }
        ]
      },
      "condition_filtering_pct"100,
      "rows_for_plan"44.809,
      "cost_for_plan"209.37,
      "chosen"true
    }
  ]
}

其中:

  • d 表,“rows”: 44.809,与前文中的计算结果相等。注意显示的单次访问被驱动表的扫描行数;
  • d 表,”cost”: 53.771;
  • m 表,”rows_to_scan”: 738
  • m 表,”cost”: 155.6

下一节中介绍成本的计算方式。

成本分析

MySQL 中一条查询语句的执行成本由以下两方面组成:

  • I/O 成本,将页面从磁盘加载到内存所损耗的时间称为 I/O 成本,默认 1.0;
  • CPU 成本,读取并检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称为 CPU 成本,默认 0.2。

下面分别依次分析并计算驱动表与被驱动表的成本,正好分别是全表扫描与使用索引。


全表扫描的步骤可以简化为:

  • 先将聚簇索引的全部页面加载到内存。注意对于InnoDB存储引擎来说,页是磁盘和内存之间交互的基本单位;
  • 然后依次检测每条记录是否满足搜索条件。

因此,计算全表扫描的成本需要以下两个信息:

  • 聚簇索引占用的页面数
  • 该表中的记录数

而这两个信息都可以从统计信息中获取到,如下所示。

mysql> show table status like 'CO_PUTAWAY_REPUT_M' G
*************************** 1. row ***************************
           Name: co_putaway_reput_m
         EngineInnoDB
        Version10
     Row_format: Dynamic
           Rows738
 Avg_row_length: 177
    Data_length: 131072
Max_data_length: 0
   Index_length: 163840
      Data_free: 0
 Auto_increment: 69016
    Create_time: 2023-11-21 14:24:19
    Update_time: 2023-11-20 11:15:57
     Check_time: NULL
      Collation: utf8_general_ci
       ChecksumNULL
 Create_options: 
        Comment
1 row in set (0.00 sec)

其中:

  • 统计项Rows表示表中记录的条数,因此该表的记录数 = 738;
  • 统计项Data_length表示聚簇索引占用的空间,由于存储空间 = 页面✖️页面大小,因此可以通过 Data_length 计算出聚簇索引占用的页面数 = 8。
mysql> select 131072/16/1024;
+----------------+
| 131072/16/1024 |
+----------------+
|     8.00000000 |
+----------------+
1 row in set (0.01 sec)

计算成本

mysql> select (8*1.0+1.1)+(738*0.2+1.0);
+---------------------------+
| (8*1.0+1.1)+(738*0.2+1.0) |
+---------------------------+
|                     157.7 |
+---------------------------+
1 row in set (0.00 sec)

其中:

  • I/O 成本中 1.0 指加载一个页面的成本常数,后面的 1.1 是微调值;
  • CPU 成本中 0.2 指访问一条记录的成本常数,后面的 1.0 是微调值;
  • 计算出的成本等于 157.7,减去微调值后的成本等于 trace 中显示的成本
mysql> select 157.7-2.1;
+-----------+
| 157.7-2.1 |
+-----------+
|     155.6 |
+-----------+
1 row in set (0.00 sec)


使用二级索引的步骤可以简化为:

  • 先将二级索引的扫描区间中的全部页面加载到内存。注意 MySQL 优化器认为读取索引的一个扫描区间的 I/O 成本和读取一个页面相同;
  • 然后依次检测每条记录是否满足搜索条件;
  • 如果需要回表,使用二级索引中保存的主键值反查聚簇索引,用于将完整的行记录加载到内存。注意 MySQL 优化器认为每次回表操作都相当于访问一个页面,也就是说二级索引范围区间有多少记录,就需要进行多少次回表操作;
  • 如果其他非二级索引字段有搜索条件,依次检测每条记录是否满足其他搜索条件。

因此,计算使用索引的成本需要以下两个信息:

  • 扫描区间的数量,对于被驱动表的每次查询 = 1;
  • 需要回表的记录数,也就是扫描区间有多少条记录,同样使用所有值的平均重复次数 = 44.809。

计算成本

mysql> select 1*1.0+(44.809*0.2+0.01)+44.809*1.0+44.809*0.2;
+-----------------------------------------------+
| 1*1.0+(44.809*0.2+0.01)+44.809*1.0+44.809*0.2 |
+-----------------------------------------------+
|                                       63.7426 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select 44.809*0.2+44.809*1.0;
+-----------------------+
| 44.809*0.2+44.809*1.0 |
+-----------------------+
|               53.7708 |
+-----------------------+
1 row in set (0.00 sec)

其中:

  • 计算出的成本等于 63.7426,并不等于 trace 中显示的成本 53.771;
  • 由于53.771 = 44.809*1.2,怀疑优化器针对关联查询的被驱动表,成本计算有特殊处理,以后有能力了再分析。

到这里本案例分析完成,下面简单介绍下其中相关的知识点。

知识点

关联算法

Simple Nested-Loop Join

最简单的关联算法是嵌套循环连接(Nested-Loop Join),取驱动表符合条件的每一行数据分别遍历被驱动表,也就是常说的笛卡尔积。

MySQL 加索引后 SQL 执行变慢

伪代码如下所示。

# 此处表示遍历满足对t1单表查询结果集中的每一条记录
for each row in t1 {
    
    # 此处表示对于某条t1表的记录来说,遍历满足对t2单表查询结果集中的每一条记录
    for each row in t2 {
     
        # 此处表示对于某条t1和t2表的记录组合来说,对t3表进行单表查询
        for each row in t3 {
            if row satisfies join conditions, send to client
        }
    }
}

该算法的主要缺点有两条:

  • 被驱动表的每次访问都是全表扫描;
  • 驱动表的访问次数取决于对驱动表结果集中的记录条数。

因此嵌套循环连接中关联查询的扫描行数 = 驱动表行数 + 驱动表符合条件的行数 * 被驱动表行数

假设被驱动表符合条件的记录有 1w 条,被驱动表也有 1w 条数据,关联查询的扫描行数 = 1w + 1w * 1w。

下面介绍的两种关联算法分别用于优化这两点。

Index Nested-Loop Join

索引嵌套循环连接(Index Nested-Loop Join)是基于索引进行连接的算法,下面举例说明。

前文查询 t2 表时的过滤条件是 t1.m1 = t2.m2 AND t2.n2 < ‘d’。

针对驱动表 t1 符合条件的两条记录,查询 t2 表的过程对应以下两条单表查询语句。

select * from t2 where t2.m2 = 2 and t2.n2 < 'd';

select * from t2 where t2.m2 = 3 and t2.n2 < 'd';

假设给 m2 字段创建索引,t2.m2 = 2 的等值查询可以使用索引,具体使用哪种扫描方式与索引类型有关。

  • 普通二级索引,执行计划中 type=ref,表示可能查询多行;
  • 唯一索引,执行计划中 type=eq_ref,表示仅查询单行,注意单表中唯一索引对应 type=const。

索引结构 B+ 树的查询效率稳定,任何关键字的查找必须走一条从根结点到叶子结点的路,I/O 次数等于树的高度。

因此在忽略回表的前提下,索引嵌套循环连接中关联查询的扫描行数 = 驱动表行数 + 驱动表符合条件的行数 * 被驱动表树的高度

通过使用被驱动表的索引减少被驱动表的扫描行数,从而提高查询效率,这也是 MySQL 中关联查询最理想的关联算法。

MySQL 加索引后 SQL 执行变慢

该算法的主要优点是:

  • 被驱动表的每次访问从全表扫描变成索引的等值查询。

Block Nested Loop

实际上,在无法使用索引嵌套循环连接时,MySQL 并不会使用嵌套循环连接,而是使用缓存块嵌套循环连接(Block Nested Loop)。

该算法通过将驱动表的结果集加载到内存后保存在 join buffer 中,然后扫描被驱动表,每一条被驱动表的记录一次性和 join buffer 中的多条驱动表记录做匹配,因为匹配的过程都是在内存中完成的,所以这样可以显著减少被驱动表的 I/O 代价。

MySQL 加索引后 SQL 执行变慢

因此不需要针对驱动表结果集的每条记录,分别加载被驱动表的全部记录,匹配完成后清除内存,再重新加载。

该算法的主要优点是:

  • 驱动表的访问次数小于驱动表结果集中的记录条数。

主要缺点是:

  • join buffer 大小有限,默认 256KB。如果 join buffer 可以容纳驱动表结果集的全部记录,就只需要访问一次被驱动表,否则就需要访问多次。因此如果被驱动表无法使用索引,可以通过调大 join buffer 进行优化;
  • join buffer 中为了尽量保存更多记录,不会保存驱动表的所有列,仅保存查询列表中的列和过滤条件中的列。因此不建议使用 select *,否则将导致 join buffer 效率降低。

因此在假设 join buffer 足够大的情况下,缓存块嵌套循环连接中关联查询的扫描行数 = 驱动表行数 + 被驱动表行数


需要注意的是 BNL 适用于 non-indexed joins,因此如果使用索引,就无法使用该算法。

The block nested loop algorithm can be used for full table scans, full index scans, and range scans.

因此当 join buffer 很小或查询的行数多、字段多时依然会慢,显然该算法的性能不稳定。

由于关联查询建议使用索引,因此生产环境可以考虑关闭 BNL,语法如下所示。

SET optimizer_switch='block_nested_loop=off';

对比关闭前后执行计划与执行用时。

# 开启 block_nested_loop
         type: ALL
         rows: 6854
        Extra: Using where; Using join buffer (Block Nested Loop)
        0.00 s
        
# 关闭 block_nested_loop
         type: ALL
         rows: 6854
        Extra: Using where
        2.37 s

其中:

  • 关联算法从 BNL 变为 Nested-Loop Join;
  • 执行用时从 0.00 s 变为 2.37 s。

成本计算

MySQL 中一条查询语句的执行成本由以下两方面组成:

  • I/O 成本,将页面从磁盘加载到内存所损耗的时间称为 I/O 成本,默认 1.0;
  • CPU 成本,读取并检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称为 CPU 成本,默认 0.2。


简单整理不同场景下的 I/O 成本与 CPU 成本计算方法。

I/O 成本:

  • 全表扫描,需要加载的页数等于聚簇索引占用的页数;
  • 使用二级索引,需要加载的页数等于扫描区间的个数;
  • 使用二级索引回表时,需要加载的页数等于扫描区间内的记录数。

CPU 成本:

  • 全表扫描,需要读取并检测的记录数等于表中的记录数;
  • 使用二级索引或回表时,需要读取并检测的记录数等于扫描区间内的记录数。


综上,计算成本时【扫描区间/范围内的记录数】是一个非常重要的指标,下面介绍该指标的计算方法。

范围内记录数

前文中提到,计算使用索引的成本时的一个重要指标是【范围内记录数】。

感谢董神与操盛春大佬,一位提供高质量群聊,一位热心指教。

该指标的计算方法与扫描区间的类型和大小有关,其中:

  • 数据量小时精确计算,数据量大时估算;
  • 单点区间与范围区间的判断数据量大小的计算方式不同;
    • 单点区间,IN 的参数大于系统参数eq_range_index_dive_limit时认为不可以精确计算;
    • 范围区间,扫描区间左端点、右端点记录所在的索引页,中间隔着大于 9 个索引页时认为不可以精确计算。
  • 单点区间与范围区间的估算的计算方式不同;
    • 单点区间,根据统计信息中统计项【索引列不重复的值有多少】计算所有值的平均重复次数,范围内记录数 = 每个值的平均数量✖️参数数量;
    • 范围区间,从区间最左记录向右读 10 个页面,计算平均每个页面中包含多少记录,范围内记录数 = 每页的平均数量✖️页数。
  • 精确计算的范围内记录数 = 左右端点记录数 + 左右端点之间记录数,如下图所示,以相隔小于等于 9 个叶结点举例。
MySQL 加索引后 SQL 执行变慢

其中每页具体有多少条记录具体是读取 Page Header 中 PAGE_N_RECS 字段。

MySQL 加索引后 SQL 执行变慢

Page Header 用于记录数据页的状态信息,其中部分字段见下表。

名称 字节 说明
PAGE_N_DIR_SLOTS 2 数据页的数据目录中slot的个数。对于新建的空数据页,有两个目录,分别指向最大记录与最小记录。在一个非空的数据页中,第一个目录永远指向最小记录,最后一个目录永远指向最大记录
PAGE_N_HEAP 2 目前已经被使用空间中的记录数量。对于空数据页,默认为2,即最大与最小记录。最高位被用来标记这个数据页是否存了新格式的记录(compact和redundant)。由此可以计算出当前页实际记录的数量
PAGE_N_RECS 2 当前数据页中用户的记录,不包括最大和最小记录
PAGE_MAX_TRX_ID 8 修改当前数据页的当前最大事务ID,注意该值仅在 Secondary Index 中定义,用于辅助判断二级索引记录的可见性
PAGE_LEVEL 2 当前页在索引树的位置,0x00表示叶子节点,根节点的leve最大
PAGE_INDEX_ID 8 索引ID,表示当前页属于哪个索引
PAGE_BTR_SEG_LEAF 10 B+树数据页叶子节点所在段的 segment header
PAGE_BTR_SEG_TOP 10 B+树数据页非叶子节点所在段的 segment header

而案例中计算单次访问被驱动使用索引的成本时,由于索引是关联字段,且访问方式为 ref 即等值查询单点区间,因此基于统计信息计算范围内记录数。

结论

创建索引后 SQL 执行变慢的根本原因是关联算法发生变化,从 Block Nested-Loop Join 变为 Index Nested-Loop Join,其中驱动表全表扫描,被驱动表索引性能低,导致扫描行数成倍增加。两种算法的扫描行数分别是多张表的行数相加与相乘。

建议:

  • 优化器自动选择关联算法,本身不可控。这两种关联算法中推荐使用 Index Nested-Loop Join,原因是索引性能足够好的前提下可以通过减少嵌套循环的次数减少扫描行数,而 Block Nested-Loop Join 依赖 join buffer 与数据量,性能不稳定;
  • 创建索引时建议创建过滤性强的索引,当天凌晨创建的联合索引仅第一个字段生效,而该字段是被驱动表的关联条件,通常建议将关联条件作为联合索引的最后一个字段,将筛选条件放在联合索引的前面。


由于多条 SQL 相互影响无法判断,因此当前仅可以做到针对单条 SQL 进行优化。

针对该问题,尽管加索引之前 SQL 执行并不慢,但是两张表都是全表扫描,依赖 join buffer,连接较少且系统较为空闲时可能问题不大,一旦系统压力较大或连接较多时,用于缓存结果的 join buffer 就可以将实例打爆,因此原执行计划本身风险就较高,建议保证多表关联查询时每张表都走索引,并保证索引的性能足够好,后续:

  • 评估是否有必要关闭 BNL 特性;
  • 慢 SQL 治理系统要重点关注 全表扫描访问数据的慢 SQL。

待办

  • 驱动表变化原因分析
  • index dive
  • MySQL 8.0 hash join

参考教程

  • 《MySQL 是怎样运行的》
  • mysql5.7的SQL执行成本计算
https://www.cnblogs.com/zhuwenjoyce/p/14968183.html
https://blog.csdn.net/u010841296/article/details/89790399

原文始发于微信公众号(丹柿小院):MySQL 加索引后 SQL 执行变慢

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

文章由极客之音整理,本文链接:https://www.bmabk.com/index.php/post/178440.html

(0)
小半的头像小半

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站,欢迎收藏到浏览器,订阅我们!