1. Python技术站首页
  2. 数据库
  3. MySQL

重现一条简单SQL的优化过程

• MySQL
  • 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

GreatSQL社区：

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

image-20230105161905827

技术交流群：

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

image-20221030163217640

原文链接：https://www.cnblogs.com/greatsql/p/17275418.html

本站文章如无特殊说明，均为本站原创，如若转载，请注明出处：重现一条简单SQL的优化过程 - Python技术站

赞 (0)
0 0 打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023年4月18日
下一篇 2023年4月18日

相关文章

  • Godaddy虚拟主机新建mysql数据库 2019最新

    第一次用狗爹，完全摸不着路子。 网站本地已搭建，不知道数据库是在哪里上传。 百度搜索结果都是四五年前的旧内容，耽误时间。 还是问客服，Godaddy的客服确实不赖   godaddy虚拟主机如何新建数据库： 1. 进入会员管理中心 https://account.godaddy.com/products 2. 进入虚拟主机管理 3. 进入cPanel管理员 …

    MySQL 2023年4月13日
    00

  • MySQL生僻字插入失败的处理方法(Incorrect string value)

    MySQL生僻字插入失败的错误信息为“Incorrect string value”，这是由于MySQL的字符集不支持某些生僻字导致的。解决方法有以下几种。 1. 修改MySQL字符集 MySQL的字符集决定了它所支持的字符范围，如果MySQL的字符集不支持某些生僻字，则会出现“Incorrect string value”错误。因此，我们可以尝试修改MyS…

    MySQL 2023年5月18日
    00

  • MySQL启动失败之MySQL服务无法启动的原因及解决

    MySQL启动失败之MySQL服务无法启动的原因及解决 问题描述 在启动MySQL服务时，可能会遇到MySQL无法启动的情况，表现为服务启动失败、MySQL进程启动失败等。这个问题可能会影响用户的正常使用，因此需要进行解决。 可能的原因 MySQL服务无法启动的原因有很多，根据实际情况，可以从以下几个方面进行解决： 1. MySQL配置文件错误 MySQL配…

    MySQL 2023年5月18日
    00

  • MySQL8.0.27安装过程中卡在Initializing Database中并报错的解决

    下面是针对MySQL8.0.27安装过程中卡在Initializing Database中并报错的解决攻略。 问题描述 在MySQL8.0.27的安装中，有些用户可能会遇到Initializing Database过程卡住，或者在某些步骤出现错误的问题。这些问题可能是由于各种原因引起，比如系统环境不兼容、文件权限问题等。下面详细介绍解决这些问题的方法。 解决…

    MySQL 2023年5月18日
    00
  • 转 Swoole】用swoole简单实现MySQL连接池 MySQL

    转 Swoole】用swoole简单实现MySQL连接池

    在传统的网站开发中，比如LNMP模式，由Nginx的master进程接收请求然后分给多个worker进程，每个worker进程再链接php-fpm的master进程，php-fpm再根据当前情况去调用其worker进程然后处理PHP，如果需要MySQL，在与MySQL建立连接，这个时候，如果有1000个请求打过来，就需要与MySQL建立1000个连接。如果请…

    2023年4月13日
    00

  • MySQL如何获取binlog的开始时间和结束时间

    MySQL数据库恢复到指定时间点时，我们必须通过MySQL全备+MySQL增量备份（可选）+MySQL的二进制日志（binlog）进行重放来恢复到指定时间点，实际的生产环境中，可能一段时间内生成了多个二进制日志文件（binlog）, MySQL本身不会存储二进制日志文件（binlog）的开始时间和结束时间，如果要还原到某个时间点，我们需要知道还原后重放哪些二…

    MySQL 2023年5月6日
    00

  • MySQL查看事件状态信息

    MySQL中的事件是一种与时间相关的对象，包括存储程序、存储函数和一些特殊事件，用于执行预定义的任务或策略。 在MySQL中查看事件状态信息，可以通过以下步骤实现： 1. 登录MySQL数据库： $ mysql -u root -p 2. 进入要查看状态信息的数据库： mysql> use yourdatabase; 3. 查看事件的状态信息： mys…

    MySQL 2023年3月10日
    00

  • linux – mysql – 卸载：使用rpm方式安装的mysql

    一、查看系统中是否以rpm包安装的mysql rpm -qa | grep -i mysql 结果： 备注：如果有内容则证明是使用rpm方式安装的mysql   二、卸载 使用rpm -e 命令将上个命令中包列表进行卸载 如果报错：error: Failed dependencies 只要加入–nodeps就ok了 命令格式：rpm -e 包名称 –no…

    MySQL 2023年4月13日
    00
合作推广
合作推广
分享本页
返回顶部