SQL优化

最新推荐文章于 2021-08-05 10:22:25 发布

dearKundy

最新推荐文章于 2021-08-05 10:22:25 发布

阅读量354

点赞数 1

分类专栏： SQL 文章标签： SQL

本文链接：https://blog.csdn.net/dearKundy/article/details/80546735

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

在项目开发中，当数据表越来越大的时候，我们必须要解决的问题之一就是SQL语句的性能问题。下面就用一个自己亲手挖的坑来给大家讲讲SQL优化相关的一些知识。

背景：AAA表大概有两百万条记录，BBB表一百万。我需要把这两个表联合起来，然后根据条件去查询出相关的记录。

刚开始写的SQL如下：

SELECT adsId, phoneNum, COUNT(*) AS num
FROM (
	SELECT phoneNum, channel, type, adsId, createDate
	FROM AAA
	UNION
	SELECT phoneNum, adsType AS channel, actionType AS type, adsId, createDate
	FROM BBB
) g
WHERE createDate >= '2018-01-01'
	AND channel = 4
	AND type = 1
GROUP BY adsId, phoneNum

这个SQL主要有两个大的问题：

1.查询条件放在了最外面，这就相当于全量扫描了AAA与BBB这两张表，然后union起来，再根据条件去筛选这两张表的全量数据。这就完全违反了尽量缩小搜索范围的原则了。

2.使用了union去联合两个表。

被导师一顿喷之后，优化出来的SQL:

SELECT adsId, phoneNum, COUNT(*) AS num
FROM (
	SELECT phoneNum, channel, type, adsId, createDate
	FROM htt_ads_his
	WHERE createDate >= '2018-01-01'
		AND channel = 4
		AND type = 1
	UNION all
	SELECT phoneNum, adsType AS channel, actionType AS type, adsId, createDate
	FROM htt_burry_point_ads_his
	WHERE createDate >= '2018-01-01'
		AND adsType = 4
		AND actionType = 1
) g
GROUP BY adsId, phoneNum

1.我把查询条件分别都放进了里面，从根源处缩小了查询的范围。

2.用union all替代union，因为union会对两个表进行去重操作，会进行排序，性能会很低。

借着这个机会，看了很多相关的博客总结了一些关于SQL的具体执行过程与性能优化的相关知识：

一条SQL的执行过程

待分析的SQL如下：

SELECT name, COUNT(name) AS num
FROM student
WHERE grade < 60
GROUP BY name
HAVING num >= 2

1.执行from student，这一步会把数据库中的表文件加载到内存中去。

2.where grade < 60，对内存中的表进行过滤，筛选出符合条件的记录，生成一张临时表。

3.group by name，把临时表按照name切分成若干临时表。

4.select的执行分为SQL中有无group by两种情况.

（1）没有group by：根据select 后面的字段名称对内存中的临时表整列的读取。

（2）有group by：会对内存中的若干临时表分别执行select，而且只取临时表的第一条数据，然后再形成新的临时表。这也是使用了group by场景下的sql，select后面一般是分组的字段与聚合函数。

5.having num > = 2：对group by生成的临时表再次过滤，与where不同的是，having作用在group by之后，where是对from student从数据库表文件加载到内存中的原生数据过滤，而having是对select语句执行之后的临时表中的数据过滤，所以column AS otherName，otherName这样的字段在WHERE后不能使用，但在HAVING后可以使用。但HAVING后使用的字段只能是SELECT后的字段。最后生成一张临时表。

这就是一条SQL语句的执行过程。

JOIN连接查询

下面我们再来看看连接查询

首先我们得知道笛卡尔积，简单来说就是两个集合相乘，假如存在AB两个集合，AxB就是A中的每一个元素与B中的每一个元素分别相乘形成一个新的集合。笛卡尔积不满足乘法交换律。

数据库数据连接时所遵循的算法就是笛卡尔积，表与表之间的连接可以看成是在做乘法运算。

下面来分析一条SQL连接语句

select * from A join B

1.FROM语句把AB两个表从数据库文件加载到内存中。

2.join语句相当于对两张表做了乘法，把A表中的每一行记录按照顺序和B表中记录一次匹配。

3.匹配完成后，会得到一张有（A表记录数 x B表记录数）条的临时表。

这里会出现一个问题，难道表连接的时候都要先形成一张笛卡尔积表吗，如果这两个表的数据量都比较大的话，那样就会占用很大的内存空间，这显然是不合理的。所以我们在进行表连接查询的时候一般都会使用JOIN xxx ON xxx的语法，ON语句的执行是在JOIN语句之前的，也就是说两张表数据行之间进行匹配的时候，会先判断数据行是否符合ON语句后面的条件，再决定是否JOIN。

因此，有一个显而易见的SQL优化的方案是，当两张表的数据量比较大，又需要连接查询是，应该使用FROM table1 JOIN table2 ON xxx 的语法，避免使用FROM table1，table2 WHERE xxx的语法，因为后者会在内存中先生成一张数据量比较大的笛卡尔积表，增加了内存的开销。