全民码农都要会的SQL优化，再不掌握就落后啦~

在上一篇文章中&＃xff0c;我们一起了解了关系模型与关系运算相关的知识&＃xff0c;接下来我们一起谈谈&＃xff0c;面对复杂的关系数据&＃xff0c;我们如何来优化&＃xff0c;SQL如何玩转更优呢&＃xff1f;

在上一篇中抛出了4个关于优化方面的问题&＃xff1a;

1.返回表中0.015%的数据应不应该走索引&＃xff1f;

2.什么样的列必须建立索引呢&＃xff1f;

3.嵌套查询、HASH连接、排序合并连接、笛卡尔连接等怎样玩能达到最优&＃xff1f;

4. IN 与EXISTS 谁快谁慢&＃xff1f;

整个优化内容篇幅过长&＃xff0c;查询优化内容并非一篇文章就能讲明白&＃xff0c;更多是需要自己的消化并通过实践运用来得出自己的一套优化体系。

文本也是分篇幅进行讲诉&＃xff0c;还需慢慢消化其中的内容&＃xff0c;目前只针对上诉的两个问题进行了解释&＃xff0c;其余两个问题是关于连表查询的优化内容&＃xff0c;将在下篇的sql连接查询优化进行细谈。

相关的优化知识和经验&＃xff0c;也是本人通过自身的学习并通过实践运用&＃xff0c;不免存在一定的偏差和错误&＃xff0c;如有大佬指正以及其他方面的分享&＃xff0c;不甚感激。

SQL简单查询

简单查询格式如下&＃xff1a;

SELECT * FROM 表名 WHERE 条件

SELECT 字段原名 AS 字段别名

选择条件的构成&＃xff1a;

&＃61;(等于)、<>(不等于)、<(小于)、>(大于)、<&＃61;(不大于)和>&＃61;(不小于)

优先级&＃xff1a;

NOT优先级高于AND&＃xff0c;AND的优先级高于OR。注意使用圆括号的使用。

涉及空值的查询&＃xff1a;

空值用NULL表示&＃xff0c;表明空值或者不为空值可以用“IS NULL”或 “IS NOT NULL”

字符串的比较&＃xff1a;

SQL的习惯用法是在 % 和 _ 之前加上转义符号“\”表示这里的 % 和 _  是一个普通的字符&＃xff0c;而不是匹配使用。

如果不把 \ 作为转义符号&＃xff0c;只把它当作一个普通字符&＃xff0c;可以连续两个 \ 表示一个真正的反斜线 \。

输出的排序&＃xff1a;

ORDER BY <字段名> &＃xff0c;提示&＃xff1a;默认排序是升序&＃xff08;ASC&＃xff09;&＃xff0c;可用倒叙&＃xff08;DESC&＃xff09;

聚合运算符&＃xff1a;

SUM&＃xff1a;求某列中所有值的和&＃xff1b;

AVG&＃xff1a;求某列中所有值的平均值&＃xff1b;

MIN&＃xff1a;求某列中的最小值&＃xff1b;

MAX&＃xff1a;求某列中的最大值&＃xff1b;

COUNT&＃xff1a;求某列中值的个数。

分组&＃xff1a;

在WHERE子句加上GROUP BY &＃xff0c;关键字GROUP BY 后面给出分组属性列表。

注&＃xff1a;如果只希望查询满足一定条件的分组情况&＃xff0c;可以使用关键字HAVING来选择具有给定条件的分组。

SQL优化

在谈及sql查询优化&＃xff0c;优化性能是每个优秀的工程师必备的思想之一&＃xff0c;无论是面对复杂还是简单的工程不仅仅满足于表现层的结果&＃xff0c;哪怕你优化的速度提高了百分之一&＃xff0c;都是经历过自己思考的过程&＃xff0c;以及走向成熟的过程。

再谈优化之前&＃xff0c;先掌握几个重要的概念&＃xff1a;基数&＃xff0c;选择性&＃xff0c;直方图&＃xff0c;回表&＃xff0c;集群因子&＃xff0c;表关系。

基数&＃xff1a;某个列唯一键&＃xff08;Distinct_Keys&＃xff09;的数量叫作基数。比如性别列&＃xff0c;该列只有男女之分&＃xff0c;所以这一列基数是2。主键列的基数等于表的总行数。

例如&＃xff1a;select count(distinct name), count(distinct age), count(*) from student 语句查询如下&＃xff1a;

学生当中不重复的名字有76&＃xff0c;年龄34&＃xff0c;学生人数为86。

基数的高低影响列的数据分布&＃xff0c;往往基数高的列通常用作索引&＃xff0c;获取数据的命中率高。

那么&＃xff0c;回到上面的第一个问题&＃xff1a;返回表中0.014%的数据应不应该走索引&＃xff1f;

在数据足够多&＃xff0c;达到百万千万级的数据时&＃xff0c;当查询结果是返回表中5%以内的数据时&＃xff0c;应该走索引&＃xff1b;当查询结果返回的是超过表中5%的数据时&＃xff0c;应该走全表扫描。 

也就是说&＃xff0c;如果某个列基数很低&＃xff0c;该列数据分布就会非常不均衡&＃xff0c;由于该列数据分布不均衡&＃xff0c;会导致SQL查询可能走索引&＃xff0c;也可能走全表扫描。

在做 SQL优化的时候&＃xff0c;如果怀疑列数据分布不均衡&＃xff0c;我们可以使用select列&＃xff0c;count(*) from 表 group by 列 order by  desc来查看列的数据分布。 

选择性&＃xff1a;一个操作过滤的记录的比例&＃xff0c; 是一个介于 0 和  1 之间的数值。通常工程师在使用查表时&＃xff0c;往往会筛选掉不必要的数据列来提高查询速率&＃xff0c;这也是一种很直观的优化方式。那么在数据重量级的情况下&＃xff0c;我们要怎么来直观的查看选择性呢&＃xff1f;通常&＃xff0c;基数与总行数的比值再乘以100%就是某个列的选择性。 

那么&＃xff0c;我们可以回到上诉的第二个问题&＃xff1a;什么样的列必须建立索引呢&＃xff1f;

在我的之前建立索引的经验中&＃xff0c;往往基数高的列&＃xff0c;where条件中常用的列来建立索引&＃xff0c;但是这样往往还不够&＃xff0c;我们无法单一的看基数高的列&＃xff0c;where常用条件就来建立索引&＃xff0c;此时我们无法确定命中率的高低&＃xff0c;所以&＃xff0c;通过基数与总行数的比值再乘以100%&＃xff0c;就能直观的看出哪些列占比就可以建立索引。当一个列选择性大于20%&＃xff0c;说明该列的数据分布就比较均衡了。

直方图&＃xff1a;如果没有对基数低的列收集直方图统计信息&＃xff0c;基于成本的优化器&＃xff08;CBO&＃xff09;会认为该列数据分布是均衡的。

1.如果没有对基数低的列收集直方图统计信息&＃xff0c;基于成本的优化器&＃xff08;CBO&＃xff09;会认 为该列数据分布是均衡的。

2.直方图信息就是以上SQL的查询结果&＃xff0c;这些查询结果会保存在数据字典中。这样 当我们查询owner为任意值的时候&＃xff0c;CBO总会算出正确的Rows&＃xff0c;因为直方图已经知道 每个值有多少行数据。

也就是说&＃xff0c;你所执行的sql查询之后&＃xff0c;你所查询的结果会保存在数据字典中&＃xff0c;这里也涉及到了数更新之后所统计的数据没有更新的话会存在一定的误差&＃xff0c;这也是后话了。

如果SQL使用了绑定变量&＃xff0c;绑定变量的列收集了直方图&＃xff0c;那么该SQL就会引起绑定变量窥探。关于绑定变量窥探的问题&＃xff0c;这里也不过多讲诉。

那&＃xff0c;什么样的列需要收集直方图呢&＃xff1f;

当列出现在where条件中&＃xff0c;列的选择性小于1%并 且该列没有收集过直方图&＃xff0c;这样的列就应该收集直方图。注意&＃xff1a;千万不能对没有出现 在where条件中的列收集直方图。对没有出现在where条件中的列收集直方图完全是做无用功&＃xff0c;浪费数据库资源。

回表&＃xff1a;当对一个列创建索引之后&＃xff0c;索引会包含该列的键值以及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表一般是单块读&＃xff0c;回表次数太多 会严重影响SQL性能&＃xff0c;如果回表次数太多&＃xff0c;就不应该走索引扫描了&＃xff0c;应该直接走全表 扫描。

为什么返回表中5%以内的数据走索引、超过表中5% 的数据走全表扫描&＃xff1f;

根本原因就在于回表。在无法避免回表的情况下&＃xff0c;走索引如果返回数据量太多&＃xff0c;必然会导致回表次数太 多&＃xff0c;从而导致性能严重下降。

什么样的SQL必须回表&＃xff1f;什么样的不需要回表呢&＃xff1f;

select *from tablename where ...... 

这样的sql是必须回表的&＃xff0c;所以工程师们在编写sql的时候通常严禁 select * 的出现。

select count(*) from tablename .

这样的sql就不走回表&＃xff0c;当查询的列也包含在索引中时&＃xff0c;也不需要走回表&＃xff0c;所以通常建立索引时&＃xff0c;往往会建立组合索引来消除回表&＃xff0c;来提升性能。

集群因子&＃xff1a;集群因子用于判断索引回表需要消耗的物理I/O次数。

上面我们提到&＃xff0c;回表是走单块读取&＃xff0c;每一条数据的回表它都是一次IO消耗。

1.集群因子的算法&＃xff0c;在这我们暂且先不提&＃xff0c;但是我们知道集群因子介于表的块数和表行数之间。

2.如果集群因子与块数接近&＃xff0c;说明表的数据基本上是有序的&＃xff0c;而且其顺序基本与 索引顺序一样。这样在进行索引范围或者索引全扫描的时候&＃xff0c;回表只需要读取少量 的数据块就能完成。

3.如果集群因子与表记录数接近&＃xff0c;说明表的数据和索引顺序差异很大&＃xff0c;在进行索引范围扫描或者索引全扫描的时候&＃xff0c;回表会读取更多的数据块。

集群因子只会影响索引范围扫描&＃xff08;INDEX RANGE SCAN&＃xff09;以及索引全扫描 &＃xff08;INDEX FULL SCAN&＃xff09;&＃xff0c;因为只有这两种索引扫描方式会有大量数据回表。

集群因子不会影响索引唯一扫描&＃xff08;INDEX UNIQUE SCAN&＃xff09;&＃xff0c;因为索引唯一扫描 只返回一条数据。集群因子更不会影响索引快速全扫描&＃xff08;INDEX FAST FULL SCAN&＃xff09;&＃xff0c;因为索引快速全扫描不回表。

集群因子究竟影响的是什么性能呢&＃xff1f;

集群因子影响的是索引回表的物理I/O次 数。我们假设索引范围扫描返回了1 000行数据&＃xff0c;如果buffer cache(缓存)中没有缓存表的数据块&＃xff0c;假设这1000行数据都在同一个数据块中&＃xff0c;那么回表需要耗费的物理I/O就只需要 一个&＃xff1b;

假设这1000行数据都在不同的数据块中&＃xff0c;那么回表就需要耗费1000个物理 I/O。因此&＃xff0c;集群因子影响索引回表的物理I/O次数。

请注意&＃xff0c;不要尝试重建索引来降低集群因子&＃xff0c;这根本没用&＃xff0c;因为表中的数据顺序 始终没变。唯一能降低集群因子的办法就是根据索引列排序对表进行重建&＃xff08;create table new_table as select * from old_table order by 索引列&＃xff09;&＃xff0c;但是这在实际操作中 是不可取的&＃xff0c;因为我们无法照顾到每一个索引。

怎么才能避免集群因子对SQL查询性能产生影响呢&＃xff1f;

集群因子只影响索引范围扫描和索引全扫描。当索引范围扫描&＃xff0c;索引全扫描不回表或者 返回数据量很少的时候&＃xff0c;不管集群因子多大&＃xff0c;对SQL查询性能几乎没有任何影响。 

再次强调一遍&＃xff0c;在进行SQL优化的时候&＃xff0c;往往会建立合适的组合索引消除回表&＃xff0c; 或者建立组合索引尽量减少回表次数。

如果无法避免回表&＃xff0c;怎么做才能消除回表对SQL查询性能产生影响呢&＃xff1f;

当我们把 表中所有的数据块缓存在buffer cache中&＃xff0c;这个时候不管集群因子多大&＃xff0c;对SQL查询性 能也没有多大影响&＃xff0c;因为这时不需要物理I/O&＃xff0c;数据块全在内存中访问速度是非常快 的。

表与表之间的关系&＃xff1a;要理清楚表与表之间的关系。

表与表之间存在3种关系。一种是1∶1关系&＃xff0c; 一种是1∶N关系&＃xff0c;最后一种是N∶N关系。搞懂表与表之间关系&＃xff0c;对于SQL优化、SQL等价改写、表设计优化以及分表分库都有巨大帮助。

两表在进行关联的时候&＃xff0c;如果两表属于1∶1关系&＃xff0c;关联之后返回的结果也是属于1的关系&＃xff0c;数据不会重复。如果两表属于1∶N关系&＃xff0c;关联之后返回的结果集属于N的关系。如果两表属于N∶N关系&＃xff0c;关联之后返回的结果集会产生局部范围的笛卡儿积&＃xff0c;N∶N关系一般不存在内/外连接中&＃xff0c;只能存在于半 连接或者反连接中。 

好了&＃xff0c;以上咱们细谈了SQL的简单查询&＃xff0c;相关SQL优化的思想&＃xff0c;里面内容其实包括了很多&＃xff0c;也省略了部分细节没有展开来讲解&＃xff0c;有兴趣的伙伴可以多去了解了解&＃xff0c;也可以和我分享。最重要的核心优化思想之一&＃xff0c;关于优化方面最主要的核心就是&＃xff1a;只有大表才会产生性能问题。 

如果喜欢的话&＃xff0c;麻烦点个赞呀~