SQL优化讲解

发表于 更新于 分类于

给合作商的数分团队培训基础SQL优化

优化的目的

1
2
注意,优化的目的在于显著提升效果,假如数据量小,没有必要进行优化
在同样的数据量同样的资源情况下,进一步提升运行效果,减少运行时间

优化方面

1
2
3
4
5
6
7
8
9
10
11
减少数据访问
    使用压缩,节省数据读取的量级,加快IO效率,但同时提高CPU解压的负载
    分布式存储,将数据打散在不同的节点上,提高并发处理能力,分布键不均匀则会出现热点问题
    分区存储,根据不同分区,将数据切分成不同段的数据,可以避免全表扫描
    针对常用字段建立索引,提高查询效率,索引虽然提高了查询效率,写入效率会被大大降低
减少数据返回
    通过Client端进行数据查询,只返回需要字段,并将数据进行分页处理
减少交互频率
    尽量进行批量DML操作,像Greenplum中CopyIn效率大于Insert
减少服务器CPU开销
    减少排序操作,对于子查询,并不需要进行排序操作,避免额外的CPU开销

理解SQL

语法顺序

1
2
3
4
5
6
7
8
9
10
SELECT 
DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

执行顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
由上到下,依次执行
选取表,将多个表数据通过笛卡尔积变成一个表
FROM <表名>

对笛卡尔积的虚表进行筛选
ON <筛选条件>

指定join,用于添加数据到on之后的虚表中,例如left join会将左表的剩余数据添加到虚表中
JOIN <join, left join, right join...> <join表> 

对上述虚表进行筛选
WHERE <where条件>

分组
GROUP BY <分组条件> 

用于having子句进行判断,在书写上这类聚合函数是写在having判断里面的
<SUM()等聚合函数> 

对分组后的结果进行聚合筛选
HAVING <分组筛选> 

返回的单列必须在group by子句中,聚合函数除外
SELECT <返回数据列表>

数据除重
DISTINCT

排序
ORDER BY <排序条件> 

限制
LIMIT <行数限制>

优化策略

建表优化

1
2
3
4
5
6
7
8
9
10
11
12
# 分区定义
将数据按照一定范围进行分隔存储,如时间,只查询需要的时间范围即可设定分区

# 分布键选择
将数据进行打散,优先选择常用于join键的字段,可以减少无效的网络开销

# 数字型字段
可以用数字型表示字段优先选择数字型,不要设计为字符型,会降低查询和连接性能,增加存储开销
字符型在处理时会逐个比较每一个字符,数字型只需要比较一次

# 使用变长字段
变长字段存储空间小,节省存储空间,定长字段在创建初始空间就已经固定

操作优化

1
2
3
4
5
6
# 使用truncate替代delete
delete操作记录会被一行行记录,而truncate不会,可以有效避免膨胀

# 表膨胀解决
使用insert into new_t select * from t,再改名的操作
可以有效解决表膨胀

语句优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 避免select *
根据业务实际需要,取指定列名数据

# 避免不一致的函数结果
像now,sysdate等,这类函数可能会因为服务器时间不一致出现不确定性

# join时,小表前大表后
执行from后的表关联查询时从左往右执行,第一张表涉及全表扫描,所以小表在前
这个操作不适用所有数据库,MySQL是这样,Oracle却是从右往左执行

# 表名别名使用
多表查询时,表别名前缀到每个列名上,不这样操作,一般的数据库也会进行报错提醒

# 避免使用having
having只在检索所有记录后才对结果进行过滤

# 调整where子句的顺序
将过滤数据多的条件放在前面,或者是将索引字段条件放在前面

索引优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
# 尽量避免使用开头模糊查询
走全表扫描 select * from t where field like '%中%'
走索引查询 select * from t where field like '中%'
Greenplum存在Bug,常规数据库索引都是B+树,左节点优先
如果无法避免使用开头模糊查询,尽量使用函数INSTR/POSITION来获取角标位置

# 尽量避免使用in/not in查询
全表 select * from t where id in (2,3)
索引 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);

# 尽量避免使用OR查询
全表 select * from t where id = 1 or id = 3
索引 select * from t where id = 1 union select * from t where id =3

# 尽量避免null值判断
全表 select * from t where id is null
索引字段应进行null替换,不允许存在null值

# 尽量避免在where条件左侧进行函数操作
全表 select * from t where score / 10 = 9
索引 select * from t where score = 10 * 9

# 避免查询条件使用<>或!=
如果一定需要使用<>或!=,考虑此字段不建立索引操作

# 联合索引注意最左匹配原则
联合索引a,b,c
全表 select * from t where b = 1 and c = 2
索引 select * from t where a = 1 and b = 2

# 字段类型注意隐式转换
varchar类型索引字段
全表 select * from field = 123
索引 select * from field = '123'

# orderby需要与where条件一致
全表 select * from t order by age
索引 select * from t where age > 0 order by age