数据库系列4 SQL优化

参考:
关于基数、选择性、直方图和回表 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
待续