SQL进阶使用教程 Part Ⅱ（join，sql优化，窗口函数）

最新推荐文章于 2024-05-13 17:05:51 发布

数据求学家

最新推荐文章于 2024-05-13 17:05:51 发布

阅读量328

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_36040866/article/details/131873146

版权

本文介绍了SQL中的join类型，强调了索引和谓词下推在提升查询效率中的作用，并提供了查询优化的实例，如避免全表扫描、使用合适的JOIN类型和优化GROUPBY语句。此外，还探讨了窗口函数在数据分析中的应用，如计算平均值和方差。

摘要由CSDN通过智能技术生成

最近看到Datawhale出了套sql进阶教程，链接地址：

https://www.heywhale.com/home/competition/648d5f4a0f80cbce101aa718/content

随即试着进行练手，以下内容记录学习的内容和答题闯关过程（文中内容都是基于sqlite，在python环境中运行，部分函数，标识符与其他数据库如mysql等函数、用法可能会有一定冲突）本文包含其中关卡三和四，并对其中内容进行一定程度的延申学习。本文承接SQL进阶使用教程 Part Ⅰ_数据求学家的博客-CSDN博客

关卡三：Join 小技巧：提升数据的处理速度

本关卡主要介绍的就是在多张表时用到的join方法，重点学习如何提升关联表的效率。

1.学习内容

1）常用的几种join类型（日常工作经常用到的还是前两个）

inner join
left join
full outer join:又称全量 JOIN，基础但是比较少见的 JOIN 方式，最直观的理解方式，就是对于两张表互相各做一次 LEFT JOIN 后取并集（即把 A LEFT JOIN B 的结果和 B LEFT JOIN A 的结果拼接后去重）
cross join:笛卡尔积

2）提升查询效率（本关卡重点）

①索引

索引的重要不必多说了，索引列就像一本书的目录，能够快速找到目标页，而不用全文遍历。

# 快速创建一个索引
CREATE INDEX                  -- CREATE INDEX： 关键字创建一个索引
IF NOT EXISTS                 -- IF NOT EXISTS: 如果该索引已存在，则不执行
passengerIndex                -- 索引名称,
ON example_table(PassengerId)        -- 索引对象,一般是某张表的某一列，形式为 table_name(column_name)



# 创建完成后，我们在查询的时候可以直接利用索引，无需额外的声明
SELECT Name
FROM example_table
WHERE PassengerId = 2

②谓词下推：先过滤，后计算（仅供参考）

其含义是“将查询语句中具有过滤性质的表达式尽可能下推到距离数据源最近的地方，方便查询在执行时尽早完成数据的过滤，进而显著地减少数据传输或计算的开销”。比如能在on上过滤的，就不要在where上做。但是SQLite 语句已经在会在执行语句的时候帮助你做下推的动作，后来我查阅mysql，也早已经在代码内部实现了。所以该项内容仅供参考。

SELECT t1.Pclass
        ,t1.Survived
        ,t2.Survived
FROM example_table AS t1
        JOIN example_table AS t2
            ON t1.Pclass < t2.Pclass           
                AND t1.Pclass = 1               -- 下推
'''

# ⭕ 不推荐
query_no_ppd = '''
SELECT t1.Pclass
        ,t1.Survived
        ,t2.Survived
FROM example_table AS t1
        JOIN example_table AS t2
            ON  t1.Pclass = 1   
WHERE t1.Pclass < t2.Pclass                           -- 没有下推

③其他优化方式

该关卡其实并未传授一些有用的提升查询效率的经验，因此我查阅了其他大佬的一些资料，做一些摘录和整理。（文中使用的是mysql）

SQL优化2020最全干货总结---MySQL-阿里云开发者社区

Ⅰ. 避免不走索引的场景

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE username LIKE '%陈%'

优化方式：尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE '陈%'

2. 尽量避免使用in 和not in，会导致引擎走全表扫描。如下：

SELECT * FROM t WHERE id IN (2,3)`

优化方式：如果是连续数值，可以用between代替。如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查询，可以用exists代替。如下：

-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);

3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3

优化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

4. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。（但一般还是得判断NULL）如下：

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0`

5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。

可以将表达式、函数操作移动到等号右侧。如下：

-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9`

6. 当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：

SELECT username, age, sex FROM T WHERE 1=1

优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。

Ⅱ. SELECT语句其他优化

1. 避免出现select *

首先，select * 操作在任何类型数据库中都不是一个好的SQL编写习惯。

使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。

建议提出业务实际需要的列数，将指定列名以取代select *。

2. 避免出现不确定结果的函数

特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。

3.多表关联查询时，小表在前，大表在后。

在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。

4. 使用表的别名

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。

5. 用where字句替换HAVING字句

避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。

where和having的区别：where后面不能使用组函数

6.调整Where字句中的连接顺序

MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

Ⅲ. 查询条件优化

1. 对于复杂的查询，可以使用中间临时表暂存数据

2. 优化group by语句

默认情况下，MySQL 会对GROUP BY分组的所有值进行排序，如 “GROUP BY col1，col2，....;” 查询的方法如同在查询中指定 “ORDER BY col1，col2，...;” 如果显式包括一个包含相同的列的 ORDER BY子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。

因此，如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL禁止排序。例如：

SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;

3. 优化join语句

MySQL中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN)..替代。

例子：假设要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

如果使用连接(JOIN).. 来完成这个查询工作，速度将会有所提升。尤其是当 salesinfo表中对 CustomerID 建有索引的话，性能将会更好，查询如下：

SELECT col1 FROM customerinfo 
   LEFT JOIN salesinfo
   ON customerinfo.CustomerID=salesinfo.CustomerID 
      WHERE salesinfo.CustomerID IS NULL

连接(JOIN).. 之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

4. 优化union查询

MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。

高效：

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 

UNION ALL 

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

低效：

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 

UNION 

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

5.拆分复杂SQL为多个小SQL，避免大事务

简单的SQL容易使用到MySQL的QUERY CACHE；
减少锁表时间特别是使用MyISAM存储引擎的表；
可以使用多核CPU。

Ⅳ. 建表优化

1. 在表中建立索引，优先考虑where、order by使用到的字段。

2. 尽量使用数字型字段（如性别，男：1 女：2），若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

3. 查询数据量大的表会造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段分页进行查询，循环遍历，将结果合并处理进行展示。要查询100000到100050的数据，如下：

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* 
   FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050

4. 用varchar/nvarchar 代替 char/nchar

2.答题

Q1：假设 A 表有 5条数据，B 表有 4条数据，两张表有一共享的 id 列。下列哪种 JOIN 可能产生的数据结果最多

A . A INNER JOIN B

B . A LEFT JOIN B

C . A FULL OUTER JOIN B

D . A RIGHT JOIN B

Q2：假设 A 表有 5条数据，B 表有 4条数据，两张表有一共享的 id 列。下列哪种 JOIN 可能产生的数据结果最少

A . A RIGHT JOIN B

B . A LEFT JOIN B

C . A FULL OUTER JOIN B

D . A INNER JOIN B

Q3：下面哪个可能是 SQL JOIN 查询慢的原因

A . JOIN 的时候使用 OR

B . JOIN 的时候对索引列使用了函数

C . 在 JOIN 的时候没有使用谓词下推

D . 以上都是

Q4：计算乘客生存率方差的语句，我们希望按照 CabinType 来统计乘客生存率方差，默认 Cabin 和 CabinType 字段都添加了索引 的情况下，下面哪段代码替换 /* MISSING CODE * / 可以实现 JOIN 的效率的最优：

SELECT t1.CabinType
        ,avg((t2.Survived-t1.Survived)*(t2.Survived-t1.Survived)) AS std -- 方差计算
FROM example_table_gb AS t1
/*
MISSING CODE
*/
GROUP BY t1.CabinType

#missing_code
# A.
MISSING_CODE = '''
JOIN example_table AS t2
    ON t1.CabinType = t2.Cabin
'''

# B.
MISSING_CODE = '''
JOIN example_table AS t2
    ON t1.CabinType = substr(t2.Cabin,0,1) -- substr 函数可以获取第一个字母
'''

# C.
MISSING_CODE = '''
JOIN example_table AS t2
    ON t1.CabinType LIKE t2.Cabin          -- LIKE 函数可以进行模糊匹配
'''

# D.
MISSING_CODE = '''
JOIN 
    (
        SELECT substr(Cabin,0,1) AS ctype
               ,Survived
        FROM example_table
        ) AS t2
    ON t1.CabinType = t2.ctype          
'''

解答：

1.C

2.D

3.D

4.D(这里很有迷惑性，A答案其实是关联逻辑本身存在问题，可以说它没进行谓词下推）

关卡四：Window 函数技巧

本章主要介绍了一些常用的窗口函数，这些函数其实工作中也常会用到，这边简单回顾一下。

1.学习内容

用一张表格展示这个对比：

分组方式	分组条件	组内数据满足	分组数
GROUP BY	GROUP BY 后的条件的具体取值	GROUP BY 后的条件	<= 数据的条目数
WINDOWS 函数	无条件，每行数据都是一组	处于“轴”的一定范围内	= 数据的条目数

关卡四中没有列举太多的窗口函数，如果大家有兴趣可以翻看我之前的文章。

【阿里云天池龙珠计划SQL训练营】SQL复习Task5_数据求学家的博客-CSDN博客

例句使用示例如下：

-- 每个城市的 3日平均消费
SELECT t1.order_date
       ,t1.address
       ,avg(total_payment) OVER(PARTITION BY address ORDER BY order_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as three_day_avg
FROM    (SELECT "订单创建日期" AS order_date
        ,"收货地址 " AS address
        ,SUM("总金额") AS total_payment
    FROM example_table
    GROUP BY  "订单创建日期","收货地址 ") AS t1

2.答题

Q1：在订单数据集中，从订单创建 {时间} 上首次出现“单笔交易总金额>5000”最晚的城市/省份是？

Q2：在订单数据集中，哪个城市/省份的订单金额（“总金额”字段）的方差最大？

Q3. 在订单数据集中，计算北京和上海的累计平均订单金额这一指标，两地这一指标差距最大的日期是几号？(累计平均订单金额 = 累计订单总金额 / 累计订单数)

Q4：在 Q3 的数据中，北京有多少天的数据是高于上海的？

解答：

1. '内蒙古自治区'

SELECT
    "订单创建日期" AS order_date
    ,"收货地址 " as address
    from example_table
    where "总金额">5000
    group by "收货地址","订单创建日期"
    
    order by order_date desc

2. '上海'

SELECT t1."收货地址 "
      ,avg((t1.err)*(t1.err)) AS std 
FROM 
    (SELECT
        "收货地址 "
        ,("总金额" -                  -- window 函数的结果可以直接被引用
            avg("总金额")             -- avg() 聚合函数
            OVER(
                PARTITION BY "收货地址 "    
                )
                ) err
    FROM  example_table) AS t1
GROUP BY "收货地址 "
order by std desc
limit 10

3. '24'

4. '4'

select count(*)
from 
(select 
order_date,
result,
address,
rank() over(partition by order_date order by result desc) rk
from 
(select 
address,
order_date,
sum(money) over(partition by address order by address,order_date),
sum(num) over(partition by address order by address,order_date),
sum(money) over(partition by address order by address,order_date)/
sum(num) over(partition by address order by address,order_date) result
from (
SELECT
    "订单创建日期" AS order_date
    ,"收货地址 " AS address
    ,sum("总金额") as money
    ,count(*) as num
    from example_table
    where "收货地址 " = "上海" or "收货地址 " = "北京"
    group by  "订单创建日期","收货地址 "
    ) t
)t1
)t2
where rk = 1 and address = '北京'