参考:
关于基数、选择性、直方图和回表 https://www.jianshu.com/p/44ebb7646b34
关于SQL优化的误区和小技巧 https://www.jianshu.com/p/8a4d8f1ccc56
关于逻辑算子 http://www.imooc.com/article/278660
一.SQL优化
1.目的
- 减少IO
- 减少CPU占用(少使用order by/group by/distinct)
2.技巧
- 少用join
- 少排序(无索引时)
- 少select *，查询指定字段，不要返回不用的字段
- 少用or，或用in替换
- 用join替代子查询
- 避免类型转换
- 查询结果条数过多时，使用分页
3.SQL优化场景
- 只有大表才会产生性能问题
二.几个概念
1.基数(Cardinality)
- 某个列的唯一键的数量称为基数
- 主键列的基数等于表的总行数
- 基数的高低影响列的数据分布
2.选择性(Selectivity)
- 选择性 = 基数/总行数 * 100%
- 索引适用于选择性高的表
- 优化点:确定必须创建索引的列有利于SQL优化
3.直方图(Histogram)
- 基数较低的列数据分布可能会不均衡，查询这种列时可能走全表扫描也可能走索引，此时容易走错执行计划
- 如果没有对基数低的列收集直方图统计信息，基于成本的优化器(CBO)会认为该列数据分布式均衡的
- 直方图可帮助CBO对基数低、数据分布不均衡的列精确估算rows值
- 优化点：确定必须创建直方图的列有利于SQL优化
4.回表(Table Access By Index RowId)
- 对某个列创建索引之后，索引会包含该列的键值以及键值对应行所在的RowId
- 回表就是通过索引中记录的RowId访问表中的数据
- 回表一般是单块读，回表次数过多严重影响SQL性能，此时应该放弃索引扫描而采取全表扫描
- 优化点:返回表中5%以下的数据时走索引，5%以上的数据时走全表扫描(避免过多回表)
5.逻辑算子
- DataSource：数据源，也就是我们SQL语句中的表。select name from table1中的table1
- Selection：选择，如select name from table1 where id = 1中的where后的过滤条件
- Projection：投影，指搜索的列，如select name from table1 where id = 1中的列name
- Join：连接，如select * from table1 table2 where table1.name=table2.name就是把两个表做Join。连接条件是最简单的等值连接，当然还有其他我们熟知的inner join,left join,right join等等
- Sort：排序，如select * from table1 order by id里面的order by。无序的数据通过这个算子处理后，输出有序的数据
- Aggregation：分组，如select sum(score) from table1 group by name中的group by。按照某些列进行分组，分组后可以进行一些聚合操作，比如Max、Min、Sum、Count、Average等等
- Apply：子查询，如select * from (select id，name from table1) as t中的(select id,name from table1) as t。可以进行嵌套查询
- 选择、投影、连接就是最基本的算子
6.逻辑查询计划
- 逻辑查询计划就是SQL语句通过SQL解析之后由各个逻辑算子组成的树状结构。
select user.name from user,score where user.id = score.id and score.num > 60
变成查询计划之后如图所示：
1）DataSource，user,score表，负责读取数据。
2）Join，user.id=score.id
3）Selection过滤，score.num > 60
4）Projection投影，user.name
三.常见误区
1.count(1)和count(primary_key)优于count(*)
- 统计记录条数时，很多人认为count(1)或者count(primary_key)优于count()，其实对于某些场景，count()表现可能更好，因为数据库对count(*)计数操作进行了特别的优化
2.count(column)和count(*)是一样的
- 实际上count(column)统计结果集中有多少个column字段不为空的记录，count()则是表示整个结果集有多少条记录
3. select a,b from ... 比 select a,b,c from ...可以让数据库返回更少的数据量
- 实际上大多数关系型数据库是按照行(row)的方式存储，但数据读取操作都是以一个固定大小的IO单元(block/page)为单位
- 大多数情况，一个IO单元存储了多行，而每行均存储了该行的所有字段，所以在查询中，取一个字段还是多个字段并不影响在数据表中需要访问的数据量
4. order by 一定需要排序操作
- 索引数据实际上是有序的
- 当需要的数据和某个索引顺序一致，且该查询通过这个索引来执行时，数据库将省略排序操作而直接返回数据(数据库知道直接返回的数据已经满足我们的排序需求)
四.关于索引的SQL语句优化技巧
1.尽量避免在where子句中使用 > 、<、!= 等范围操作符，否则数据库引擎将放弃索引进行全表扫描
2.尽量避免在where子句中使用 or 条件，或使用union或者in代替or，否则数据库引擎将放弃索引进行全表扫描
select id from t where num=10 or num=20
select * from t where LOC_ID = 10 or LOC_ID = 20 or LOC_ID = 30;
- 优化为
select id from t where num=10 union all select id from t where num=20
select * from t where LOC_IN in (10,20,30);
3.慎用 in 和 not in
- 对于连续数据，使用between代替
- 使用 exists 代替 in
SELECT * FROM ACCOUNT
WHERE AC_CODE
NOT IN (
SELECT CODE
FROM GOODS
WHERE NUM='001') //低效
SELECT * FROM ACCOUNT
WHERE NOT EXISTS
（SELECT CODE
FROM GOODS
WHERE CODE=ACCOUNT.AC_CODE
AND NUM='001') //更高效
4. like %aaa% 语句将导致全表扫描
5.避免在 where 子句进行表达式操作
select id form t where num/2 = 100
- 优化为
select id from t where num = 200
6.避免在 where 子句对字段进行函数操作
select id from t where substring(name,1,3) = 'abc'
- 优化为
select id from t where name like 'abc%'
7.where字句中的连接顺序
- oracle采用自下而上的顺序解析where字句
表之间的链接必须写在其他where条件之前，那些可以滤过大量纪录的条件必须写在where字句的末尾，例如：
select * from table e
where h>500
and d='001'
and 25<(select count(*)
from table
where count=e.count); //低效
select * from table e
where 25<(select count(*)
from table
where count=e.count);
and h>500
and d='001'; //更高效
8.
五.关于SQL语句执行顺序的优化
1.语法顺序和执行顺序
- SQL的语法顺序：
select [distinct]...from...[xxx join][on]...where...group by...having...[union]...order by...
- SQL的执行顺序：
from...[xxx join][on]...where...group by...sum()...having...select [distinct]...order by....
2.from 子句
- 执行顺序为从后往前、从右到左
- 最后面的那个表名为驱动表，因为执行顺序为从后往前, 所以数据量较少的表尽量放后
3.where子句
- 执行顺序为自下而上、从右到左
- 尽量将可以过滤掉大量数据的条件写在where的子句放在最后
4.group by 和order by 子句
- 执行顺序都为从左到右
5.select子句
- 少用*号，使用列名可减少耗时
六.查询优化器
1.查询优化器分类
- RBO
基于规则的优化器(Rule-Based Optimizer)
RBO严格按照既定优化规则优化Sql语句，同一条Sql语句在不同数据环境下有相同优化结果
在RBO中Sql写法不同很可能影响最终的执行计划，从而影响脚本性能 - CBO
基于成本的优化器(Cost-Based Optimizer)
Sql语句通过优化生成多个执行计划，CBO根据统计信息和成本模型(Cost Model)计算每个执行计划的成本，选取成本最小者执行
2.查询优化器执行过程
- RBO
1）Transformation：遍历关系表达式，按既定优化规则转换Sql语句
2）Build Physical Plan：根据转换的Sql语句形成执行计划 - CBO
1）Exploration：根据优化规则进行语句等价转换，同时保留原关系表达式
2）Build Physical Plan：产生各语句的执行计划
3）Find Best Plan：执行cost最小的执行计划
3.查询优化器常见优化规则(自动执行)
- 谓词下推
优化器自动将外层查询块的 WHERE 子句中的谓词移入所包含的较低层查询块，从而能够提早进行数据过滤以及有可能更好地利用索引
//优化前
EXPLAIN SELECT *
FROM t_student,t_score
WHERE t_score.student_id=t_student.student_id
AND t_score.score_id=2;
//优化后
EXPLAIN SELECT * FROM t_student t1 right JOIN (
SELECT * from t_score WHERE score_id=2
) t2
ON t1.student_id=t2.student_id;
- 列裁剪
SQL查询优化器通过只读取需要的数据，省略过没有用到的列，以减少IO提高执行效率 - 常量折叠
- 投影消除
- 最大最小消除
查询计划会自动优化(最大消除)
//优化前
select max(id) from table1
//优化后
select max(id) from (select id from table1 order by id desc limit 1 where id is not null) t
一个具体Sql优化实例 https://www.jianshu.com/p/0b1571730d3f
待续
