投稿
  1. 极客之音首页
  2. 数据库

MySQL 慢 SQL 优化之关联字段无索引导致全表扫描

小半 • • 数据库 • 阅读 360

引言

之前介绍的慢 SQL 优化的案例都是基于单表，本文开始介绍关联查询，通过优化一个简单案例介绍 MySQL 中关联查询的概念与关联算法。

该案例中被驱动表关联字段无索引导致全表扫描，给关联字段创建索引后依然如此，最终通过给关联字段 + 筛选字段创建联合索引后索引生效提高了查询效率。

现象

时间：20230426 10:30

现象：慢 SQL

SQL

select
  r.sys_no as sysNo,
  r.item_code as itemCode,
  i.item_desc as itemDesc,
  i.evaluate_type as evaluateType,
  r.status as status,
  r.create_time as createTime,
  i.sort as sort
from
  evaluate_result r,
  evaluate_item i
where
  r.yn = 1
  and i.item_code = r.item_code
  and r.business_type = 'in-evaluate'
  and r.business_no = '23042311329'
order by
  i.sort;

特点：

  • 两张表的关联查询
  • 关联条件不在 on 中，而是在 where 中

执行用时 1.29s。

5 rows in set (1.29 sec)

分析

执行计划 + 表结构

查看执行计划，分析性能瓶颈。

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2570427
     filtered: 0.01
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)

其中：

  • type: ALL，表明两张表都是全表扫描；
  • Extra: Using where; Using join buffer (Block Nested Loop)，表明没有合适的索引。

这里提出一个问题，驱动表一定是全表扫描吗？下文中将回答该问题。


查看表结构，显示关联字段无索引。

mysql> show create table evaluate_result G
*************************** 1. row ***************************
       Table: evaluate_result
Create Table: CREATE TABLE `evaluate_result` (
  `sys_no` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '内部主键',
  `area_no` varchar(32) NOT NULL DEFAULT '' COMMENT '区域编码',
  `area_name` varchar(32) NOT NULL DEFAULT '' COMMENT '区域名称',
  `dc_no` varchar(32) NOT NULL DEFAULT '' COMMENT '配送中心的唯一编码（与ERP一致）',
  `dc_name` varchar(32) NOT NULL DEFAULT '' COMMENT '配送中心名称',
  `wh_no` varchar(32) NOT NULL DEFAULT '' COMMENT '仓库编码',
  `wh_name` varchar(32) NOT NULL DEFAULT '' COMMENT '仓库名称',
  `business_type` varchar(20) NOT NULL DEFAULT '' COMMENT '调研类别-1：司机满意度调研；',
  `business_no` varchar(128) DEFAULT NULL COMMENT '排队queueNo',
  `item_code` varchar(20) NOT NULL DEFAULT '' COMMENT '调研项简码',
  `item_grade` int(5) NOT NULL DEFAULT '10' COMMENT '调研项分数',
  `item_result` varchar(500) DEFAULT '' COMMENT '调研项结果',
  `item_remark` varchar(100) DEFAULT '' COMMENT '调研项结果说明',
  `status` tinyint(1) DEFAULT '1' COMMENT '1:初始待评价;2:已评价;3:默认到期评价',
  `yn` int(1) NOT NULL DEFAULT '1' COMMENT '是否有效： 0否 1是',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间戳',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_pin` varchar(100) DEFAULT '' COMMENT '创建人',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_pin` varchar(100) DEFAULT '' COMMENT '创建人',
  PRIMARY KEY (`sys_no`),
  KEY `idx_wh_bus_no` (`wh_no`,`business_no`)
) ENGINE=InnoDB AUTO_INCREMENT=2658402 DEFAULT CHARSET=utf8 COMMENT='评价结果表'
1 row in set (0.00 sec)

mysql> show create table evaluate_item G
*************************** 1. row ***************************
       Table: evaluate_item
Create Table: CREATE TABLE `evaluate_item` (
  `sys_no` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `business_type` varchar(20) NOT NULL DEFAULT '' COMMENT '调研类别-1：司机满意度调研；',
  `item_code` varchar(20) NOT NULL DEFAULT '' COMMENT '调研项简码',
  `item_code_name` varchar(20) NOT NULL DEFAULT '' COMMENT '调研项简码名称',
  `item_desc` varchar(100) DEFAULT '' COMMENT '调研项描述--用于页面展示调研问题题目和说明',
  `evaluate_type` int(2) DEFAULT '1' COMMENT '调研类型1:打分项 2:意见项-文本输入',
  `remark` varchar(100) DEFAULT '' COMMENT '调研项描述--用于页面展示调研问题题目和说明',
  `sort` int(3) DEFAULT '999' COMMENT '调研项排序',
  `is_count` int(3) DEFAULT '1' COMMENT '是否统计该调研项',
  `is_use` tinyint(1) DEFAULT '1' COMMENT '是否启用调研项',
  `yn` int(1) NOT NULL DEFAULT '1' COMMENT '是否有效： 0否 1是',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间戳',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_pin` varchar(100) DEFAULT '' COMMENT '创建人',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_pin` varchar(100) DEFAULT '' COMMENT '创建人',
  PRIMARY KEY (`sys_no`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='评价项设置表'
1 row in set (0.01 sec)

其中：

  • 两张表的自增主键字段为 sys_no。

表大小

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

mysql> select count(*) from evaluate_result;
+----------+
| count(*) |
+----------+
|  2658400 |
+----------+
1 row in set (0.40 sec)

其中：

  • 参考执行计划，驱动表 evaluate_item 是小表，被驱动表 evaluate_result 200 多 w 行，因此是小表驱动大表。

关联字段创建单列索引

创建索引

关联条件

from
  evaluate_result r,
  evaluate_item i
where
  i.item_code = r.item_code

分别给两张表的关联字段创建单列索引

mysql> alter table evaluate_item add index idx_item_code(item_code);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table evaluate_result add index idx_test(item_code);
Query OK, 0 rows affected (5.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看执行计划

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
   partitions: NULL
         type: ALL
possible_keys: idx_test
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2570427
     filtered: 0.10
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
   partitions: NULL
         type: ALL
possible_keys: idx_item_code
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.01 sec)

其中：

  • 两张表都索引失效，与加索引前一样；
  • 大表驱动小表，与加索引前不一样；
  • order by i.sort，但是显示 r 表 Using filesort，【原因待定】，不确定是先查询后排序还是先排序后查询。


通过指定强制索引，也可以发现性能不如全表扫描。

查看执行计划，显示强制索引生效。

mysql> explain select count(*) from evaluate_result r force index(idx_test),evaluate_item i where r.yn=1 and i.item_code = r.item_code and r.business_type = 'in-evaluate' and r.business_no = '23042311329' order by i.sort G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
   partitions: NULL
         type: ALL
possible_keys: idx_item_code
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
   partitions: NULL
         type: ref
possible_keys: idx_test
          key: idx_test
      key_len: 62
          ref: test_zk.i.item_code
         rows: 642606
     filtered: 0.10
        Extra: Using where
2 rows in set, 1 warning (0.01 sec)

其中：

  • 使用 select count(*) 替代 select 字段；
  • 强制索引指定 r 表的索引 force index(idx_test)；
  • 执行计划显示 rows: 642606。

查看执行用时，显示强制索引性能更差。

mysql> select count(*) from evaluate_result r force index(idx_test),evaluate_item i where r.yn=1 and i.item_code = r.item_code and r.business_type = 'in-evaluate' and r.business_no = '23042311329' order by i.sort;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (2.96 sec)

mysql> select count(*) from evaluate_result r,evaluate_item i where r.yn=1 and i.item_code = r.item_code and r.business_type = 'in-evaluate' and r.business_no = '23042311329' order by i.sort;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.98 sec)

因此，删除 evaluate_result 表上的测试索引。

mysql> alter table evaluate_result drop index idx_test;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

下面根据 trace 分析在给关联字段创建单列索引后索引失效的原因。

索引失效

ref_optimizer_key_uses 中显示两张表关联的等值字段为 item_code，索引字段也是 item_code。

{
    "ref_optimizer_key_uses": [
      {
        "table": "`evaluate_result` `r`",
        "field": "item_code",
        "equals": "`i`.`item_code`",
        "null_rejecting": false
      },
      {
        "table": "`evaluate_item` `i`",
        "field": "item_code",
        "equals": "`r`.`item_code`",
        "null_rejecting": false
      }
    ]
  }

i 表选择全表扫描。

"table": "`evaluate_item` `i`",
"best_access_path": {
  "considered_access_paths": [
    {
      "access_type": "ref",
      "index": "idx_item_code",
      "usable": false,
      "chosen": false
    },
    {
      "rows_to_scan": 5,
      "access_type": "scan",
      "resulting_rows": 5,
      "cost": 2,
      "chosen": true,
      "use_tmp_table": true
    }
  ]
}

原因是全表扫描 2576.6 的成本低于使用索引 3084.5。

"table": "`evaluate_item` `i`",
"best_access_path": {
  "considered_access_paths": [
    {
      "access_type": "ref",
      "index": "idx_item_code",
      "rows": 1,
      "cost": 3084.5,
      "chosen": true
    },
    {
      "rows_to_scan": 5,
      "access_type": "scan",
      "using_join_cache": true,
      "buffers_needed": 6,
      "resulting_rows": 5,
      "cost": 2576.6,
      "chosen": true
    }
  ]
}

r 表也选择了全表扫描。

"table": "`evaluate_result` `r`",
"best_access_path": {
  "considered_access_paths": [
    {
      "access_type": "ref",
      "index": "idx_test",
      "usable": false,
      "chosen": false
    },
    {
      "rows_to_scan": 2570427,
      "access_type": "scan",
      "resulting_rows": 2570.4,
      "cost": 541861,
      "chosen": true
    }
  ]
}

原因是全表扫描 547769 的成本低于使用索引 1.06e6。

"table": "`evaluate_result` `r`",
"best_access_path": {
  "considered_access_paths": [
    {
      "access_type": "ref",
      "index": "idx_test",
      "rows": 642607,
      "cost": 1.06e6,
      "chosen": true
    },
    {
      "rows_to_scan": 2570427,
      "access_type": "scan",
      "using_join_cache": true,
      "buffers_needed": 1,
      "resulting_rows": 2570.4,
      "cost": 547769,
      "chosen": true
    }
  ]
}

其中：

  • “using_join_cache”: true，表明关联查询使用到了 join cache 优化策略。【具体该策略的原因待确认】。


attached_conditions_computation 中显示查询优化器如何确定每个表的附加筛选条件（attached conditions）。所谓“附加条件”，是指在搜索表行的过程中，除了索引条件之外，还需要满足的其他条件。

具体来说，查询优化器会将所有的查询条件分类，分成两类：

  • 一类是能够利用索引直接过滤的条件，这类条件会被用于索引查找；
  • 另一类是不能直接利用索引过滤的条件，这类条件被称为“附加条件”（attached conditions），在索引查找后，结果集会再进行一次这个条件的过滤。
"attached_conditions_computation": [
    {
      "table": "`evaluate_item` `i`",
      "rechecking_index_usage": {
        "recheck_reason": "not_first_table",
        "range_analysis": {
          "table_scan": {
            "rows": 5,
            "cost": 4.1
          },
          "potential_range_indexes": [
            {
              "index": "PRIMARY",
              "usable": false,
              "cause": "not_applicable"
            },
            {
              "index": "idx_item_code",
              "usable": true,
              "key_parts": [
                "item_code",
                "sys_no"
              ]
            }
          ],
          "setup_range_conditions": [
          ],
          "group_index_range": {
            "chosen": false,
            "cause": "not_single_table"
          },
          "analyzing_range_alternatives": {
            "range_scan_alternatives": [
              {
                "index": "idx_item_code",
                "chosen": false,
                "cause": "depends_on_unread_values"
              }
            ],
            "analyzing_roworder_intersect": {
              "usable": false,
              "cause": "too_few_roworder_scans"
            }
          }
        }
      }
    }
  ]

其中：

  • “recheck_reason”: “not_first_table”，表示某个表不是查询计划中的第一个表，导致需要对该表进行重新检查。判断可能是由于查询的结果集需要先经过排序后再进行关联操作，因此导致执行前再次检查，而该操作会导致额外的性能开销；
  • range_scan_alternatives 未使用索引的原因是 depends_on_unread_values，表明由于需要回表因此无法使用索引 idx_item_code 的范围扫描；
  • analyzing_roworder_intersect 没有使用 row order intersect 的原因是 too_few_roworder_scans，表明由于顺序扫描次数过少导致不使用联合排序索引（row order intersect）。

参考 chatgpt，联合排序索引（row order intersect）与索引合并（index merge）的相同点是都是优化多个索引查询的策略。

主要区别在于 row order intersect 针对每条符合条件的记录依次访问每个索引，而 index merge 针对每个索引单独返回符合条件的记录最后将结果取交集（OR）或并集（AND）。

MySQL 慢 SQL 优化之关联字段无索引导致全表扫描
image-20230724103031876

关联字段 + 筛选字段创建联合索引

驱动表通过关联字段访问被驱动表，并结合筛选字段定位到数据行。

查看关联条件与查询条件。

from
  evaluate_result r,
  evaluate_item i
where
  i.item_code = r.item_code
  and r.business_type = 'in-evaluate'
  and r.business_no = '23042311329'

给关联字段 + 筛选字段创建联合索引。

mysql> alter table evaluate_result add index idx_test(item_code, business_type, business_no);
Query OK, 0 rows affected (14.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看执行计划

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
   partitions: NULL
         type: ALL
possible_keys: idx_item_code
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
   partitions: NULL
         type: ref
possible_keys: idx_test
          key: idx_test
      key_len: 511
          ref: test_zk.i.item_code,const,const
         rows: 1
     filtered: 10.00
        Extra: Using where
2 rows in set, 1 warning (0.01 sec)

其中：

  • i 表 possible_keys: idx_item_code，key: NULL，表明索引失效；
  • key: idx_test，表明联合索引生效；
  • key_len: 511，表明联合索引全部生效，511 = (20+20+128) * 3+2 * 3+1，其中 2 表示变长字段，1 表示可为空；
  • ref: wmsun.i.item_code,const,const，表明常量；
  • type: ref，表明非唯一索引可能定位到多行；
  • Extra: Using filesort、Extra: Using where，表明驱动表与被驱动表分别需要排序与回表。

查看两张表关联查询结果的主键值。

mysql> select   r.sys_no, i.sys_no from   evaluate_result r,   evaluate_item i where   r.yn = 1   and i.item_code = r.item_code   and r.business_type = 'in-evaluate'   and r.business_no = '23042311329' order by   i.sort ;
+---------+--------+
| sys_no  | sys_no |
+---------+--------+
| 2123337 |      1 |
| 2123338 |      2 |
| 2123339 |      3 |
| 2123340 |      4 |
| 2123341 |      5 |
+---------+--------+
5 rows in set (0.00 sec)

其中：

  • 两张表关联查询的结果显示主键一对一，而且主键连续。对应执行计划中被驱动表扫描行数 rows: 1，因此被驱动表扫描行数等于 rows: 5 * rows: 1 = 5。在创建联合索引之前等于 rows: 5 * rows: 642606 = 3213030。

查看执行用时，显示执行用时从 1.29 s 优化到 0.00s。

5 rows in set (0.00 sec)

测试完成，删除测试索引。

mysql> alter table evaluate_result add index idx_test(item_code);
Query OK, 0 rows affected (3.88 sec)
Records: 0  Duplicates: 0  Warnings: 0

原理

连接

连接的概念

根据数据库设计范式，非主属性必须完全依赖于主属性，且非主属性不能与非主属性之间有依赖关系。因此需要将分别依赖于不同主属性的非主属性拆分到不同数据表。

连接查询是通过关联条件将两个或多个表中的数据进行连接，从多个表中检索与组合需要的数据。

连接的本质是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。

参考《MySQL 是怎样运行的》，准备两张表，用于讲解连接的原理。

mysql> CREATE TABLE t1 (m1 int, n1 char(1));

mysql> CREATE TABLE t2 (m2 int, n2 char(1));

mysql> INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');

mysql> INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');

如下图所示，将表 t1 的每条记录分别与 t2 的每条记录匹配组合，这个查询过程称为连接查询，这样的查询结果集称为笛卡尔积。其中 t1 与 t2 分别有 3 条记录，因此连接查询的笛卡尔积有 3 *3 = 9 条记录。

MySQL 慢 SQL 优化之关联字段无索引导致全表扫描
微信图片_20230723111453

其中将第一个查询的表称为驱动表。

连接的条件

由于笛卡尔积会导致查询结果集呈指数增长，因此实际使用时，需要在连接过程中过滤掉特定记录组合，在连接过程中的过滤条件可以分为两种：

  • 涉及单表的条件
  • 涉及两表的条件

以如下查询语句举例。

SELECT * FROM t1, t2 
WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

其中指定了 3 个过滤条件：

  • t1.m1 > 1，单表的条件
  • t1.m1 = t2.m2，两表的条件
  • .n2 < ‘d’，单表的条件

不过通常会将两表的关联条件写在 on 子句中，而不是 where 子句中。

实际上该查询语句等价于：

SELECT * FROM t1
JOIN t2 ON t1.m1 = t2.m2
WHERE t1.m1 > 1 AND t2.n2 < 'd';

原因是过滤条件可以分为两种：

  • where 子句中的过滤条件
  • on 子句中的过滤条件

具体过滤条件的使用与连接的类型有关。

连接的分类

连接可以分为以下两类：

  • 内连接，驱动表不确定，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，默认内连接；
  • 外连接，驱动表确定，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集，并赋值为 NULL。

其中外连接又可以分为以下两类：

  • 左外连接，左表为驱动表
  • 右外连接，右表为驱动表

不同类型连接的对比见下图。

MySQL 慢 SQL 优化之关联字段无索引导致全表扫描

where 与 on 子句中过滤条件的区别：

  • where 子句中的过滤条件，不论是内连接还是外连接，凡是不符合 where 子句中的过滤条件的记录都不会被加入最后的结果集。内连接中的 where 子句等价于 on 子句；
  • on 子句中的过滤条件，如果要求当被驱动表中没有匹配的记录时也将该记录加入到结果集，使用外连接。数据更多的表作为驱动表，如果该表在左边，使用左外连接，否则使用右外连接。

因此：

  • 通常将单表过滤条件写在 where 子句，将多表过滤条件写在 on 子句，也称为关联条件；
  • 内连接中的 where 子句等价于 on 子句，外连接中通常 join 与 where 配合选择结果集。

连接的过程

前文提到的查询语句的执行过程可以简化为下图。

SELECT * FROM t1, t2 
WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';
MySQL 慢 SQL 优化之关联字段无索引导致全表扫描
微信图片_20230723203955

其中：

  • 首先确定第一个需要查询的表，称为驱动表。假设使用 t1 表作为驱动表，执行第一个过滤条件 t1.m1 > 1，由于该字段没有二级索引，因此使用全表扫描的方式访问每一行数据判断是否满足过滤条件；
  • t1 表符合条件的有两条记录；
  • 然后针对从驱动表产生的结果集中的每一条记录，分别到被驱动表中查找符合条件的记录。因此 t1 表的两条记录分别查询 t2 表：
    • t1.m1 = 2 时，过滤条件 t1.m1 = t2.m2 相当于 t2.m2 = 2，因此 t2 表执行过滤条件 t2.m2 = 2、t2.n2 < ‘d’。由于两个字段都没有索引，因此同样使用全表扫描的方式访问数据进行判断；
    • t1.m1 = 3 时，过滤条件 t1.m1 = t2.m2 相当于 t2.m2 = 3，因此 t2 表执行过滤条件 t2.m2 = 3、t2.n2 < d’。由于两个字段都没有索引，因此同样使用全表扫描的方式访问数据进行判断。
  • 将两张表的查询结果集合并，t1 表符合条件的两条记录分别对应 t2 表的一条记录，因此连接查询的结果有两行数据，select * 对应四个字段。结果如下所示。
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+

该两表连接查询共需要查询 1 次 t1 表，2 次 t2 表。

实际上，在 MySQL 中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次。

具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数，因此提出关联算法用于减少被驱动表的扫描行数，从而提高查询效率。

关联算法

Simple Nested-Loop Join

最简单的关联算法是嵌套循环连接（Nested-Loop Join），取驱动表符合条件的每一行数据分别遍历被驱动表。

伪代码如下所示。

# 此处表示遍历满足对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 足够大的情况下，缓存块嵌套循环连接中关联查询的扫描行数 = 驱动表行数 + 被驱动表行数。

几个问题

驱动表都是全表扫描吗？

前文中以下 SQL 对应执行计划显示 evaluate_item 表 idx_item_code 索引失效。

trace 中显示由于需要回表因此无法使用索引 idx_item_code 的范围扫描。

select
  r.sys_no as sysNo,
  r.item_code as itemCode,
  i.item_desc as itemDesc,
  i.evaluate_type as evaluateType,
  r.status as status,
  r.create_time as createTime,
  i.sort as sort
from
  evaluate_result r,
  evaluate_item i
where
  r.yn = 1
  and i.item_code = r.item_code
  and r.business_type = 'in-evaluate'
  and r.business_no = '23042311329'
order by
  i.sort;

如果将 SQL 查询字段与除关联条件外的过滤条件中 evaluate_item 表全部字段删除，改下后 SQL 如下所示。

select
  r.sys_no as sysNo,
  r.item_code as itemCode,
  r.status as status,
  r.create_time as createTime
from
  evaluate_result r,
  evaluate_item i
where
  r.yn = 1
  and i.item_code = r.item_code
  and r.business_type = 'in-evaluate'
  and r.business_no = '23042311329

查看执行计划，显示 evaluate_item 表 idx_item_code 索引生效。

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
   partitions: NULL
         type: index
possible_keys: idx_item_code
          key: idx_item_code
      key_len: 62
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
   partitions: NULL
         type: ref
possible_keys: idx_test
          key: idx_test
      key_len: 511
          ref: test_zk.i.item_code,const,const
         rows: 1
     filtered: 10.00
        Extra: Using where
2 rows in set, 1 warning (0.01 sec)

因此关联查询中驱动表并非一定是全表扫描，具体与成本有关系。

执行计划未显示关联算法表示使用了索引吗？

被驱动表全表扫描时执行计划中被驱动表显示 Extra: Using join buffer (Block Nested Loop)，表明使用缓存块嵌套循环连接（Block Nested Loop）。

*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
   partitions: NULL
         type: ALL
possible_keys: idx_item_code
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.01 sec)

创建索引后，执行计划中被驱动表显示 key: idx_test 表明使用了索引，但是 Extra: Using where，所以使用的是索引嵌套循环连接（Index Nested-Loop Join）吗？

*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
   partitions: NULL
         type: ref
possible_keys: idx_test
          key: idx_test
      key_len: 511
          ref: test_zk.i.item_code,const,const
         rows: 1
     filtered: 10.00
        Extra: Using where
2 rows in set, 1 warning (0.01 sec)

判断如果 Extra 中不显示 Block Nested Loop，表示关联算法是 Index Nested-Loop Join【待确认】。

为什么建议小表驱动大表？

首先需要明确的是“小表”不是指表中数据行数，而是在索引和查询条件对数据过滤后剩余的数据行数。

本文使用关联查询的扫描行数作为评价指标，对比使用不同关联算法时小表驱动大表与大表驱动小表的匹配次数。

假设有两张表 t1 与 t2，符合条件的数据行数分别为 100 行与 10000 行，对应 B+ 树的高度分别为 2 与 3。

1）Simple Nested-Loop Join

计算公式：关联查询的扫描行数 = 驱动表行数 + 驱动表符合条件的行数 * 被驱动表行数

对比：

  • 小表驱动大表，扫描行数 = 100 + 100 * 10000；
  • 大表驱动小表，扫描行数 = 10000 + 100 * 10000。

2）Index Nested-Loop Join

计算公式：关联查询的扫描行数 = 驱动表行数 + 驱动表符合条件的行数 * 被驱动表树的高度

对比：

  • 小表驱动大表，扫描行数 = 100 + 100 * 3 = 400；
  • 大表驱动小表，扫描行数 = 10000 + 10000 * 2 = 30000。

3）Block Nested Loop

假设 join buffer 一次恰好可以容纳 100 条记录。

计算公式：关联查询的扫描行数 = 驱动表行数 + join buffer 次数 * 被驱动表行数

对比：

  • 小表驱动大表，扫描行数 = 100 + 1 * 10000 = 10100；
  • 大表驱动小表，扫描行数 = 10000 + (10000 / 100) * 100 = 20000。

综上，无论使用哪种关联算法，MySQL 中统一建议小表驱动大表。

优化器怎样自动选择驱动表？

前文中显示内连接不同条件下选择的驱动表可能不同：

  • 无索引，驱动表为 i；
  • 关联字段创建索引，驱动表为 r；
  • 关联字段 + 筛选字段创建联合索引，驱动表为 i。

可是，优化器是怎么选择驱动表的呢？

这里简单介绍下两表连接的成本分析方法。


首先是连接查询的成本计算公式：

连接查询总成本 = 单次访问驱动表的成本 + 驱动表符合条件的行数 * 单次访问被驱动表的成本。

对于外连接，驱动表固定，因此只需要为驱动表与被驱动表分别选择成本最低的访问方法就可以得到最优的查询方案。

对于内连接，驱动表不固定，因此需要考虑两方面问题：

  • 选择哪个表作为驱动表？
  • 每个表选择哪种访问方法？

因此需要对比不同连接顺序下不同访问方法的查询成本，进而选择出最优的查询方案。


由于连接查询的成本中驱动表符合条件的行数 * 单次访问被驱动表的成本是主要影响因素，因此优化重点是以下两方面：

  • 尽量减少驱动表符合条件的行数，也就是减少查询驱动表后得到的记录条数；
  • 尽量降低被驱动表的访问成本，建议被驱动表的连接列是该表的主键或唯一二级索引。

结论

两张表关联查询的执行计划显示Extra: Using where; Using join buffer (Block Nested Loop)，表明没有合适的索引。

在给关联字段创建单列索引后依然索引失效，原因是被驱动表二级索引 + 回表的成本高于全表扫描。

在给关联字段 + 筛选字段创建联合索引后索引生效，原因是被驱动表二级索引 + 回表的成本低于全表扫描，执行计划显示 Extra: Using where。


连接查询是通过关联条件将两个或多个表中的数据进行连接，从多个表中检索与组合需要的数据。

连接过程可以简化为：

  • 首先确定第一个需要查询的表，称为驱动表，然后在驱动表执行过滤条件，即执行第一个单表查询；
  • 针对从驱动表产生的结果集中的每一条记录，分别到被驱动表中查找符合条件的记录，即执行第二个单表查询。

因此 MySQL 中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次。

具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数，因此提出关联算法用于减少被驱动表的扫描行数：

  • 嵌套循环连接（Nested-Loop Join），取驱动表符合条件的每一行数据分别全表遍历被驱动表；
  • 索引嵌套循环连接（Index Nested-Loop Join），使用使用被驱动表的索引减少被驱动表的扫描行数；
  • 缓存块嵌套循环连接（Block Nested Loop），无法使用索引时，将驱动表的结果集保存在 join buffer 中批量访问被驱动表。


连接查询的成本计算公式：

连接查询总成本 = 单次访问驱动表的成本 + 驱动表符合条件的行数 * 单次访问被驱动表的成本。

对于外连接，驱动表固定，因此只需要考虑一个问题，每个表选择哪种访问方法？

对于内连接，驱动表不固定，因此还需要考虑另一个问题，选择哪个表作为驱动表？

由于连接查询的成本中驱动表符合条件的行数 * 单次访问被驱动表的成本是主要影响因素，因此优化重点是以下两方面：

  • 尽量减少驱动表符合条件的行数，也就是减少查询驱动表后得到的记录条数；
  • 尽量降低被驱动表的访问成本，建议被驱动表的连接列是该表的主键或唯一二级索引。

待办

  • hash join

参考教程

  • 《MySQL 是怎样运行的》
  • 学习Mysql的join算法：Index Nested-Loop Join和Block Nested-Loop Join
  • 一文详细讲清楚 MySQL为什么是小表驱动大表？


原文始发于微信公众号（丹柿小院）：MySQL 慢 SQL 优化之关联字段无索引导致全表扫描

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

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

赞 (0)
小半的头像小半
0 0

相关推荐

发表回复

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