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