「分布式技术专题」SQL优化的前置条件和优化技巧分享

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团队精心整编，专题会持续更新，欢迎大家保持关注。