SQL优化核心思想--笔记1:SQL优化必懂概念

本文是对《SQL优化核心思想》第一章的笔记
该书第一章电子版链接：《SQL优化核心思想》第一章

SQL优化必懂概念

1.基数（CARDINALITY）

基数：某个列唯一键的数量。比如：性别列，该列只有男女之分，所以这一列的基数是2。
主键列的基数 等于 表的总行数
基数的高低影响列的数据分布

查询是否走索引，要看列的数据分布
（1.1） 当查询结果返回表中5%以内数据时，应该走索引，当查询结果返回超过5%的数据时，应该走全表扫描。
（1.2） 如果某个列的基数很低，该列数据分布就会非常不均衡，会导致SQL查询可能走索引，也可能走全表扫描。
（1.3） 在做SQL优化的时候，怀疑数据分布不均衡，可以使用SELECT 列, COUNT(*) FROM 表 GROUP BY 列 ORDER BY 2 DESC 来查看列的数据分布。

2.选择性（SELECTIVITY）

选择性：基数与总行数的比值再乘以100%就是某个列的选择性。
在进行SQL优化的时候，单独看列的基数是没有意义的，基数必须对比总行数才有实际意义。

什么样的列需要必须要创建索引呢？
当一个列出现在where条件中，该列没有创建索引，并且选择性大于20%，那么该列就必须创建索引。因为当一个列的选择性大于20%，说明该列的数据分布就比较均衡了。

3.直方图（HISTOGRAM）

如果没有对基数低的列收集直方图统计信息，基于成本的优化器（CBO）会认为该列数据分布是均衡的。会导致数据库优化器走错执行计划。
如果CBO每次都能计算得到精确的ROWS，那么我们就不用担心SQL走错执行计划了。

4.回表（TABLE ACCESS BY INDEX ROWID）

通过索引记录的rowid访问表中的数据就叫回表。
回表一般是单块读，回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描了，应该直接走全表扫描。

在进行SQL优化的时候，一定要注意回表次数！特别要注意回表的物理I/O次数

Q: 为什么返回5%以内的数据走索引，而超过5%的数据走全表扫描呢？
A: 根本原因在于回表
所以，我们往往通过建立组合索引来消除回表，从而提升查询性能。

5.集群因子（CLUSTERING FACTOR）

集群因子用于判断 索引回表 需要消耗的 物理I/O次数。
索引的叶子块中有序存储了索引的键值以及键值对应行所在的ROWID。

集群因子算法
（5.1） 对比2，3 对应的ROWID是否在同一个数据块，如果在同一个数据块，集群因子的值 + 0，如果不在同一个数据块，那么集群因子的值 + 1。
（5.2） 对比3，4 对应的ROWID是否在同一个数据库，如果如果在同一个数据块，集群因子的值 + 0，如果不在同一个数据块，那么集群因子的值 + 1。
按上面步骤一直这样有序的比较下去，直到比较完索引中最后的一个键值。

集群因子介于表的块数和表的行数之间

• 如果集群因子与块数接近，说明表的数据基本是有序的，而且其顺序基本与索引的顺序一致。这样在进行索引范围或者索引全表扫描的时候，回表只需要读取少量的数据块就能完成。
• 如果集群因子与表记录数接近，说明表的数据和索引顺序差异很大，在进行索引范围扫描或者索引全扫描的时候，回表读取更多的数据块。

集群因子只会影响索引范围扫描（INDEX RANGE SCAN）以及索引全扫描（INDEX FULL SCAN），因为只有这两种索引扫描方式会有大量数据回表。

集群因子不会影响唯一索引扫描（INDEX UNIQUE SCAN），因为索引唯一扫描只返回一条数据。更不会影响索引快速全扫描（INDEX FAST FULL SCAN），因为索引快速全扫描不回表。

6.表与表之间的关系

表与表之间存在3种关系。

• 1:1关系
• 1:N关系
• N:N关系
  搞懂表与表之间的关系，对于SQL优化，SQL等价改写，表设计优化以及分库分表都有巨大帮助。