SQL优化终于干掉了“distinct”

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: SQL优化终于干掉了“distinct”

一、优化目的

在我提交了代码的时候,架构师给我指出我这个sql这样写会有问题。因为在分库分表的时候,是不支持子查询的。

所以需要把多表的子查询的sql结构进行优化。

二、优化之前的sql长这样

是不是挺恐怖的;(此处为了脱敏,我把相关的sql关键词都给打码掉了)

这个sql的执行步骤如下:

1、查询出来d表中的某个id字段包含多个id值的所有的数据(因为此表是1-n的关系,所以需要去重,仅需要拿到不重复的id才可以继续下一个步骤);可以看到此步骤我把查询出来的多个值的结果给生成的了一个子表名为sss;

2、下一个步骤就是需要进行排序(以时间进行倒序排序,因为要在前台进行按时间进行展示);

3、第3步就是把这些结果与a表进行合并,查询出来排序后的每个id的信息;然后进行分页处理;

其他的可以不必关心,最终要的是去重关键字(DISTINCT),拿小本本记号,一会要考哦。

三、DISTINCT关键字的用法

实践是验证真理的唯一标准

例如有下表:

可以看到nameproduct_unit列有可能是重复的

mysql> SELECT t1.id,t1.name,t1.product_unit  FROM dd_product_category t1;
+----+----------+--------------+
| id | name     | product_unit |
+----+----------+--------------+
| 55 | 饮料     | 瓶           |
| 56 | 饮料     | 箱           |
| 57 | 零食     | 包           |
| 59 | 膨化食品 | 袋           |
| 60 | 方便食品 | 箱           |
| 61 | 自热火锅 | 碗           |
| 62 | 方便面   | 箱           |
| 63 | 矿泉水   | 箱           |
| 64 | 糖果     |              |
| 65 | 酒类     | 箱           |
| 66 | 烈酒     | 箱           |
| 67 | 啤酒     | 箱           |
| 68 | 预调酒   | 箱           |
+----+----------+--------------+
13 rows in set (0.13 sec)
mysql> 
mysql>

如何我们想只拿到name或者product_unit列的值并且不想要重复的值该怎么办?

1、拿到单个值是好拿的,但是是存在重复的数据的,这些重复的数据我们只保留一个就可以了,那么该怎么做呢?

mysql> SELECT t1.product_unit  FROM dd_product_category t1;
+--------------+
| product_unit |
+--------------+
| 瓶           |
| 箱           |
| 包           |
| 袋           |
| 箱           |
| 碗           |
| 箱           |
| 箱           |
|              |
| 箱           |
| 箱           |
| 箱           |
| 箱           |
+--------------+
13 rows in set (19.31 sec)
mysql>

2、去除重复列

mysql> 
mysql> SELECT DISTINCT t1.product_unit  FROM dd_product_category t1;
+--------------+
| product_unit |
+--------------+
| 瓶           |
| 箱           |
| 包           |
| 袋           |
| 碗           |
|              |
+--------------+
6 rows in set (0.11 sec)
mysql>

是不是很简单,虽然看着简单,但是如果多表子查询的时候,就会出现问题,例如你想要查询表a,b,c三个表的数据,这三个表必然都是有关系的。


a和b是1-n的关系。但是你只有b表中id,你需要先查询出来b表的数据,然后利用b表的数据去查询a表的数据,然后再去查询c表的数据。


想必肯定是很绕的。


整个过程中你肯定是需要去重的


当整个sql写完,基本上跟我写的优化前的sql也就差不多了。(多表嵌套,多sql嵌套sql,啦啦啦一大堆)。

优化思路还是有很多的,当时能想到的就是把这个复杂的sql拆分成多个简单的sql执行,然后使用Java后台代码进行处理。(对于不甘于现状的我,想找到一个比这个更友好的解决方案的我,我是不会屈服这个问题的。

四、谈:如何优化distinct的sql

说到这里,先给大家放上一个链接:

推荐大家阅读。

Mysql5.7官方手册中提及到的关于优化distinct的方法,原文如下:

MySQL 5.7 Reference Manual / … / DISTINCT Optimization

8.2.1.16 DISTINCT Optimization

DISTINCT combined with ORDER BY needs a temporary table in many cases.

distinct 与order by 结合的许多情况下需要建一个临时表;

Because DISTINCT may use GROUP BY, learn how MySQL works with columns in ORDER BY or HAVING clauses that are not part of the selected columns. See Section 12.20.3, “MySQL Handling of GROUP BY”.

因为distinct可能使用group by,了解MySQL如何处理按order by 列或者具有不属于所选列的子句。见12.20.3节, “MySQL Handling of GROUP BY”.

In most cases, a DISTINCT clause can be considered as a special case of GROUP BY. For example, the following two queries are equivalent:

在大多数情况下,一个不同的子句可以被认为是group by 的特殊情况。例如下面这两个查询是等价的:

SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > const;
SELECT c1, c2, c3 FROM t1
WHERE c1 > const GROUP BY c1, c2, c3;

Due to this equivalence, the optimizations applicable to GROUP BY queries can be also applied to queries with a DISTINCT clause. Thus, for more details on the optimization possibilities for DISTINCT queries, see Section 8.2.1.15, “GROUP BY Optimization”.

由于这种等价性,适用于group by查询的优化,也可以应用于具有不同子句的查询。因此,关于distinct的查询优化的更多细节可以参考Section 8.2.1.15, “GROUP BY Optimization”.

When combining LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows.

当row_count与distinct一起使用时,MySQL一旦发现row_count是唯一的行,就会停止。

If you do not use columns from all tables named in a query, MySQL stops scanning any unused tables as soon as it finds the first match. In the following case, assuming that t1 is used before t2 (which you can check with EXPLAIN), MySQL stops reading from t2 (for any particular row in t1) when it finds the first row in t2:

如果在查询中不适用来自所有表的列,MySQL一旦找到第一个匹配项就会停止扫描任何未使用的表。


在下面的例子中,假设t1在t2之前使用(你可以使用explanin来检查),MySQL在找到t2的第一行时停止从t2读取(对于t1中的任何特定行)。

SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;

官方的手册中写到的,真是句句扣心呀!!!

总结有以下比较重要的几点:

  • 1、distinct与group by几乎等价;
  • 2、distinct的相关优化与group by的查询优化方法是等价的;

五、distinct真的和group by等价吗?

我们抱着试试看的态度,去做个试验。

就以下列这个效果为最终目的好了:

mysql> 
mysql> SELECT DISTINCT t1.product_unit  FROM dd_product_category t1;
+--------------+
| product_unit |
+--------------+
| 瓶           |
| 箱           |
| 包           |
| 袋           |
| 碗           |
|              |
+--------------+
6 rows in set (0.11 sec)
mysql>

使用group by去重:

mysql> select  t1.product_unit from dd_product_category t1 group by t1.product_unit;
+--------------+
| product_unit |
+--------------+
|              |
| 包           |
| 瓶           |
| 碗           |
| 箱           |
| 袋           |
+--------------+
6 rows in set (19.46 sec)
mysql>

可以看到,最终拿到的数据是一模一样的。


那么我们试验是成功的,distinct的效果和group by的效果是一样的。


那么我们优化distinct就变向的去优化group by了(我优化前的sql并未使用group by所以谈不上优化group by,只能说是把distinct的复杂sql改造成group by 的sql)。

打开我前面提到的这个优化group by的官方手册:

https://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html

由于原文比较长,这里就不在过多赘述。

现在需要做的就是把distinct改造成group by的sql语法的写法。

六、优化后的sql长啥样?

怎么样,改造后的sql,是不是还挺清爽的。


1、我们扔掉了多个嵌套sql


2、也不用去生成一个sss的临时表了

七、总结

对于本人而言学到了:

  • 1、distinct与group by几乎等价;
  • 2、distinct的相关优化与group by的查询优化方法是等价的;
  • 3、如果distinct的不能让sql最优化,那么可以尝试着使用group by的方式去改造一下。

这些我都上传到了百度云。




相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
SQL 存储 数据库
面试题19: 如何优化SQL查询?
面试题19: 如何优化SQL查询?
面试题19: 如何优化SQL查询?
|
2天前
|
SQL 关系型数据库 MySQL
|
11天前
|
SQL 数据库 索引
sql深度优化
sql深度优化
21 1
|
12天前
|
SQL 存储 关系型数据库
MySQL索引原理以及SQL优化
MySQL索引原理以及SQL优化
49 0
|
24天前
|
SQL 存储 关系型数据库
【MySQL】七种SQL优化方式 你知道几条
【MySQL】七种SQL优化方式 你知道几条
30 0
|
28天前
|
SQL 存储 关系型数据库
MySQL(终结篇二)- SQL 语句分析与优化
MySQL(终结篇二)- SQL 语句分析与优化
75 0
|
2月前
|
SQL 存储 缓存
如何通过优化SQL查询提升数据库性能
SQL查询是数据库的核心功能之一,对于大型数据量的应用程序来说,优化SQL查询可以显著提升数据库的性能。本文将介绍如何通过优化SQL查询语句来提升数据库的性能,包括索引优化、查询语句优化以及其他一些技巧。
|
2月前
|
SQL 存储 关系型数据库
MySQL SQL优化 【建议熟读并背诵】
MySQL SQL优化 【建议熟读并背诵】
38 0
|
2月前
|
SQL 关系型数据库 MySQL
MySQL索引设计原则 索引使用、索引失效、索引SQL优化
MySQL索引设计原则 索引使用、索引失效、索引SQL优化
31 0
|
2月前
|
SQL 关系型数据库 MySQL
MySQL数据库进阶-SQL优化
MySQL性能分析、SQL优化方案
56 1