从业三年的DBA总结的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条件会先执行
