关于数据库:重现一条简单SQL的优化过程

GreatSQL 社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。
GreatSQL 是 MySQL 的国产分支版本，应用上与 MySQL 统一。
作者：JennyYu
文章起源：GreatSQL 社区投稿

接到客户诉求说一条 SQL 长时间运行不出后果，让给看看怎么回事，SQL 不简单，优化措施也不简单，然而要想 SQL 达到最优状态，也是须要通过一番考量并做出抉择的。上面借试验还原一下此 SQL 优化过程。

数据库环境：MySQL5.7.39

测试表构造如下：

mysql> show create table t_1\G
*************************** 1. row ***************************
       Table: t_1
Create Table: CREATE TABLE `t_1` (`w_id` int(11) DEFAULT NULL,
  `w_name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)


mysql> show create table t_2\G
*************************** 1. row ***************************
       Table: t_2
Create Table: CREATE TABLE `t_2` (`i_id` int(11) NOT NULL,
  `i_name` varchar(24) DEFAULT NULL,
  `i_price` decimal(5,2) DEFAULT NULL,
  `i_data` varchar(50) DEFAULT NULL,
  `i_im_id` int(11) NOT NULL,
  PRIMARY KEY (`i_im_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> show create table t_3\G
*************************** 1. row ***************************
       Table: t_3
Create Table: CREATE TABLE `t_3` (`s_w_id` int(11) NOT NULL,
  `s_i_id` int(11) NOT NULL,
  `s_quantity` int(11) DEFAULT NULL,
  `s_ytd` int(11) DEFAULT NULL,
  `s_order_cnt` int(11) DEFAULT NULL,
  `s_remote_cnt` int(11) DEFAULT NULL,
  `s_data` varchar(50) DEFAULT NULL,
  `s_dist_01` char(24) DEFAULT NULL,
  `s_dist_02` char(24) DEFAULT NULL,
  `s_dist_03` char(24) DEFAULT NULL,
  `s_dist_04` char(24) DEFAULT NULL,
  `s_dist_05` char(24) DEFAULT NULL,
  `s_dist_06` char(24) DEFAULT NULL,
  `s_dist_07` char(24) DEFAULT NULL,
  `s_dist_08` char(24) DEFAULT NULL,
  `s_dist_09` char(24) DEFAULT NULL,
  `s_dist_10` char(24) DEFAULT NULL,
  `t_2_id` int(11) DEFAULT NULL,
  `t_1_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`s_w_id`,`s_i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

Create Table: CREATE TABLE `t_4` (`w_name` varchar(10) DEFAULT NULL,
  `s_i_id` int(11) NOT NULL,
  `s_quantity` int(11) DEFAULT NULL,
  `s_ytd` int(11) DEFAULT NULL,
  `s_order_cnt` int(11) DEFAULT NULL,
  `s_remote_cnt` int(11) DEFAULT NULL,
  `s_data` varchar(50) DEFAULT NULL,
  `t_2_id` int(11) DEFAULT NULL,
  `i_name` varchar(24) DEFAULT NULL,
  `i_price` decimal(5,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

其中 t_1 表 25 条记录，t_2 表 100 条记录，t_3 表 500 万条数据。我这里试验数据量少些，客户理论业务表数据量别离是（30，150，2700 万）。t_4 表为一个历史数据归档表，用于插入数据。

SQL 文本展现如下：

insert into t_4
SELECT
  c.w_name,
  a.s_i_id,
  a.s_quantity,
  a.s_ytd,
  a.s_order_cnt,
  a.s_remote_cnt,
  a.s_data,
  a.t_2_id,
  b.i_name,
  b.i_price
FROM
 t_3 a,
 t_2 b,
 t_1 c
WHERE
 a.t_2_id = b.i_id
and a.t_1_id = c.w_id
and a.s_ytd = 0;

查看语句中 select 局部的执行打算如下图所示：

看到这个打算，就想对数据库说一句：” 您辛苦了!”。

优化器抉择先对两个小表 c，b 进行关联，而后失去的后果集再与大表 a 进行关联，因为语句中 c，b 两个表没有字段进行间接关联，所以这两个表连贯后的后果集是一个笛卡尔积 25 *100=2500，因为大表的关联字段上没有索引，所以须要对最内层的大表全表扫描 2500 次。

这是不是一个大工程呢？数据库不辞辛苦，你让它干，它就干，只有你等得起就能够。事实上咱们是没有急躁等的。我原本还想看看数据库到底用多久能力给出后果，等了 10 分钟，切实没有急躁持续等上来了。

这条 SQL 不简单吧，就是三张表进行关联，然而关联字段上都没有索引，都进行了全表扫描。那么解决措施就是加索引，然而索引怎么加就须要做出抉择了。

有共事就提出这个 SQL 在大表上全表扫描 2500 次，在大表的关联字段上加上索引就能够了，看到这里，你有没有认同这个见解呢？我想应该有很多小伙伴是认同的。

不错，给大表加上索引就不必全表扫描了，首先大表加索引，会锁表很长时间，这个索引在客户的生产环境须等到变更窗口能力加，客户等不及，其次你有思考过这真的是最好的方法吗？

因为我这是试验环境，能够随时给大表加索引，那接下来咱们就给大表加上索引试试成果。

mysql> alter table t_3 add key(t_1_id,t_2_id);
Query OK, 0 rows affected (28.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

索引加好之后，执行打算如下：

能够看出优化器并没有抉择走索引，仍然是应用 BNL 优化策略，进行全表扫描，为什么不走索引呢？应该是优化器认为索引扫描的老本高于全表扫描的老本，因为这条语句最终后果要返回大表的 90% 以上的数据，走索引后回表代价是很高的。这一点咱们是不认同优化器的，怎么着 2500 次全表扫描也比每次通过索引范畴扫描的代价要高呀，好吧，既然不认同，那么应用 force index 来干预优化器决策，让它应用索引。

执行打算如下图所示：

执行打算中显示索引用上了，那理论执行成果如何呢？

mysql> insert into t_4
    -> SELECT
    ->   c.w_name,
    ->   a.s_i_id,
    ->   a.s_quantity,
    ->   a.s_ytd,
    ->   a.s_order_cnt,
    ->   a.s_remote_cnt,
    ->   a.s_data,
    ->   a.t_2_id,
    ->   b.i_name,
    ->   b.i_price
    -> FROM
    ->  t_3 a force index(t_1_id),
    ->  t_2 b,
    ->  t_1 c
    -> WHERE
    ->  a.t_2_id = b.i_id
    -> and a.t_1_id = c.w_id
    -> and a.s_ytd = 0;
Query OK, 4800000 rows affected (4 min 43.57 sec)
Records: 4800000  Duplicates: 0  Warnings: 0

的确效率不错，500 万数据须要4 min 43.57 sec，生产环境的 2700 万数据大略须要半个小时左右。

但这是不是效率最高的方法呢，因为最终后果集会返回大表的 90% 以上的数据，所以须要对大量的索引数据回表，因为回表是会产生随机 IO 的，这个回表代价的确比拟高，优化器默认也没有抉择这种执行打算。如果咱们给小表的关联字段上加索引会是什么成果呢？

接下来我给两个小表的关联字段上加了索引。

mysql> alter table t_2 add key(i_id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_1 add key(w_id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

咱们去掉大表的 force index，不干预优化器，让优化器本人做决策。执行打算如下：

上图的执行打算显示，优化器抉择了对大表全表扫描，大表做驱动表，驱动两个小表。那这样的实际效果如何呢？

mysql> insert into t_4
    -> SELECT
    ->   c.w_name,
    ->   a.s_i_id,
    ->   a.s_quantity,
    ->   a.s_ytd,
    ->   a.s_order_cnt,
    ->   a.s_remote_cnt,
    ->   a.s_data,
    ->   a.t_2_id,
    ->   b.i_name,
    ->   b.i_price
    -> FROM
    ->  t_3 a,
    ->  t_2 b,
    ->  t_1 c
    -> WHERE
    ->  a.t_2_id = b.i_id
    -> and a.t_1_id = c.w_id
    -> and a.s_ytd = 0;
Query OK, 4800000 rows affected (1 min 59.06 sec)
Records: 4800000  Duplicates: 0  Warnings: 0

这种形式耗时1min 59.06sec，效率进步 1 倍多，生产环境的大数据量，效率晋升应该更显著。果然采纳大表驱动小表这种形式效率进步了，优化器的抉择是对的。

抉择这种形式的益处：

1.SQL 的执行效率高一倍

2. 节俭空间，因为大表的索引会占用很大的磁盘空间。

3. 响应及时，防止了必须等到变更窗口能力加索引的麻烦。

4. 不必批改 SQL 语句

该如何抉择是不是很分明了呢？

到这里仿佛优化就完结了，然而如果想要精益求精，谋求极致的话，小表上的索引能够建成笼罩索引，避免小表回表取数据。

mysql> alter table t_1 drop key w_id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_2 drop key i_id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_2 add key(i_id,i_name,i_price);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_1 add key(w_id,w_name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行成果如下：

mysql> insert into t_4
    -> SELECT
    ->   c.w_name,
    ->   a.s_i_id,
    ->   a.s_quantity,
    ->   a.s_ytd,
    ->   a.s_order_cnt,
    ->   a.s_remote_cnt,
    ->   a.s_data,
    ->   a.t_2_id,
    ->   b.i_name,
    ->   b.i_price
    -> FROM
    ->  t_3 a,
    ->  t_2 b,
    ->  t_1 c
    -> WHERE
    ->  a.t_2_id = b.i_id
    -> and a.t_1_id = c.w_id
    -> and a.s_ytd = 0;
Query OK, 4800000 rows affected (1 min 38.99 sec)
Records: 4800000  Duplicates: 0  Warnings: 0

能够看出，小表上的索引建成笼罩索引，耗时 又缩短了 20 秒，执行效率更高了。

至此该条 SQL 的优化完结。

1. 本条 SQL 的最终执行打算是大表驱动小表，这也算是给上篇文章《NL 连贯肯定是小表驱动大表效率高吗》提供了一个案例。

2. 优化措施可能有很多不同的抉择，要依据理论状况抉择最优的，不要粗率做出决定。

3. 精益求精是优化的极致，然而有时候也是须要做出折中抉择的，达到业务运行的要求是目标，这点当前遇到案例再说。

Enjoy GreatSQL :)

## 对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

相干链接：GreatSQL 社区 Gitee GitHub Bilibili

社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html

微信：扫码增加 GreatSQL 社区助手 微信好友，发送验证信息 加群。

关于数据库:重现一条简单SQL的优化过程

背景

试验：

总结

GreatSQL 社区：

技术交换群：