SQL优化是数据库优化的一部分,数据库优化又是系统优化的一部分。本篇着重讲解SQL优化的一些技巧,另外,硬件基础、业务类型、存储结构也是影响SQL执行效率的重要因素,是SQL优化的前置条件。
一、SQL优化的前置条件
1、硬件基础
数据库是操作系统之上的一种数据管理软件,其SQL最终的执行还是需要在硬件层面执行,所以硬件条件如CPU核数、内存大小、磁盘转速、网络带宽等是保障数据库的SQL脚本能快速运行的基础。
2、业务类型判断
- 了解业务是读多写少还是读写均衡,判断是偏OLAP还是OLTP业务
- 根据表的使用方式判断其数据分布和形态
事实表
含有大量的事实数据,包含描述业务的特定事件的数据,如商品交易情况表。
维度表
用户来分析数据,会在某一维度汇总事实数据表数据。
流水表
存放的是一个用户的变更记录,用于记录业务轨迹。
比如在一张流水表中,一天的数据中,会存放一个用户的每条修改记录。
宽表
汇总多个维度或指标,例如计算用户画像指标的结果表。
全量表
只记录所有数据的最新状态。
增量表
按天分区,每一天会存放当天所产生的增量数据。
快照表
按天分区,每一天的数据都是截止到那一天的全量数据。
切片表
根据基础表,往往只反映某一个维度的相应数据。
其表结构与基础表结构相同,但数据往往只有某一维度,或者某一个事实条件的数据。
拉链表
数据仓库中经常用到,所谓拉链,就是记录历史。
使用区间段记录数据,表中有一个数据的起始时间和结束时间,记录数据的有效区间。
相比每天分区,既能查看历史,又很节省空间。
字典表
在系统中充当基础参数的角色。比如机构代码表、商品种类表。
码表
类似于数据字典。
临时表
在业务计算中,保留中间的计算结果,使用完成后会删除或清空。
在SQL优化中,了解业务类型和数据的分布情况是一个重要的前提,如果只基于SQL规则去优化无异于盲人摸象。
3、存储结构设计
数据的存储结构和数据查询的效率是密切相关的。
分区分桶
在传统的关系型数据库中,分库分表是数据体量较大时一种常用的解决方式。
在分布式数据库中,集群节点可以线性扩展,分区分桶即可实现数据按某一维度分散存储。
行存 vs 列存
对于OLAP的统计分析型的业务,列存是优先的存储格式; 对于OLTP的事务及高并发业务,行存则占据优势。
压缩
无论列存还是行存,如果数据存储时能够压缩,则可节省大量的存储空间,进行SQL查询时,也可大大减少IO,加快查询速度。
4、索引设计
本质上来说,索引是一种空间换时间的策略,通过索引减少全表扫描,能大大降低IO,提高查询速度。但同时也会牺牲一定的更新速度。
索引分类
逻辑上:
• 单列索引
• 多列索引
• 唯一索引
• 非唯一索引
• 函数索引
物理上:
• 分区索引
• 非分区索引
• B树
• 位图索引
• 哈希索引
索引的代价
-
空间上的代价
每建立一个索引都要为它建立一个数据页
-
时间上的代价
增删改查都要对索引同步操作
索引设计原则
-
为查询频率高的字段创建索引
-
为经常需要排序、分组和联合操作的字段建立索引
-
选择区分度高的列作为索引
例如电话、身份证号码基本唯一,适合做索引,但性别不适合做索引 -
使用数据类型占用空间小的字段做索引
一般来说,数字类型、日期类型、char类型的字段适合做索引;字符串和BLOG等类型的字段不适合做索引。
二、SQL优化技巧
孙子兵法有云,“不战而屈人之兵,善之善者也。故上兵伐谋,其次伐交,其次伐兵,其下攻城。”
当业务模型和数据模型等都已设计完成,数据库即将入数或已经开始对外提供服务时,SQL优化能力就可以大显身手,为数据库高效运行保驾护航了。
优化法则 :
- 通过索引减少磁盘IO
- 通过谓词下推减少网络传输
- 去除非必要排序操作减少CPU和内存开销
- 增加资源加速查询
数据存储类型
- 选择最精简的数据类型和存储格式,例如:
长度固定的字符串字段使用char类型;日期类型优先使用timestamp - 尽量把字段设置为 not null
索引优化
- 利用索引,可以避免大表全表扫描;但数据量不大时,全表扫描也可能更快
- 避免在索引列上使用函数
- 避免出现索引列隐式转换(数据类型转换)
- 支持索引的 where 操作: =、>、>=、< 、<= 、between、in
- 不支持索引的 where 操作: <> 、not in、表达式或函数计算、is null
- like 语句前 % 不支持索引
联合主键优化
- 多列索引选择合适的索引列顺序(最左原则)
- 联合主键中如果不是从最左列查询,则无法使用索引。
- 范围查询时,也只能用最左列进行范围查找。
- order by后字段如果和联合主键的顺序不同,则无法使用索引
- asc desc 混用,无法使用索引
select优化
- 只查询需要用到的列,而不是全字段
- 如果表多个列且没有主键,则 count(1) 执行效率优于 count(*)
- 如果有主键,则 select count(主键)的执行效率是最优的
order by 优化
可能会发生排序操作的SQL语法:
• Order by
• Group by
• Distinct
• Exists子查询
• Not Exists子查询
• In子查询
• Not In子查询
• Union(并集),Union All不会发生排序
• Minus(差集)
• Intersect(交集)
• Create Index
• Merge Join
优化思路:
• 减少使用不必要的ORDER BY排序
• 排序时,返回的列要尽量少
• 因为索引天然有序,所以尽量利用索引排序
• 避免使用耗费资源的操作,包括:DISTINCT,UNION,MINUS,INTERSECT,ORDER BY
• 使用group by代替distinct
• 对于范围查询,如果字段恰好是排序的,则查找和IO都会更快
join 优化
• 表聚合的本质是做笛卡尔积
• 有小表时使用广播
• 驱动表选择小表,驱动表量越大越会引起性能问题(大小表前后问题)
• 使用 join 代替子查询。子查询需要在内存中创建临时表。
• 使用 join 代替子连接。子连接会先查外表再匹配内表。
• 需要join的字段最好创建索引,且是相同的数据类型
• 尽量使用union all而不是union(有去重操作)
• join 尽量不要超过 2 张表
• 用UNION替换OR ,用UNION-ALL 替换UNION
子查询和子连接
- 出现在FROM关键字后的子句是子查询语句
SELECT * FROM STUDENT, (SELECT * FROM SCORE) as sc;
• 出现在WHERE/ON等约束条件中或投影中的子句是子连接语句
SELECT * FROM STUDENT WHERE EXISTS (
SELECT A FROM SCORE WHERE SCORE.sno = STUDENT.sno);
其中STUDENT叫外表,SCORE称内表。
where 条件优化
• 谓词下推,提前执行where谓词逻辑
• 用 in 替换 or
• 使用 between或exists 代替 in
• 用NOT EXISTS替代NOT IN
• 用Where子句替换HAVING子句
• 减少比较操作
on 与 where 的执行顺序以及效率
-
on :与取得结果集同步进行数据刷选及过滤。
-
where : 获得结果集之后,才进行数据刷选及过滤。
执行顺序:on在上游,where在中游,having在下游。
-
left join 中on条件对左表无效,对右表有效;
-
right join中on条件对右表无效,对左表有效;
-
inner join时,结果与放在where条件中等价,但是on条件会先执行
以上为SQL优化的前置条件和优化技巧分享,「分布式技术专题」是国产数据库hubble团队精心整编,专题会持续更新,欢迎大家保持关注。