SQL 优化必懂知识点

阳光穿透心脏的1/2处 2022-05-17 03:48 164阅读 0赞

### **1. 基数** ###

单个列唯一键（distict\_keys）的数量叫做基数。比如性别列，该列只有男女之分，抛开中性，所以这一列基数就是主键列的基数等于表的总行数。基数的高低影响列的数据分布。

MariaDB [test]> select count(distinct gender),count(distinct phone) from test;
    +------------------------+-----------------------+
    | count(distinct gender) | count(distinct phone) |
    +------------------------+-----------------------+
    |                      2 |                     7 |
    +------------------------+-----------------------+
    1 row in set (0.00 sec)

test 表的总函数是 7，gender 列的基数是 2，说明 gender 列里面有大量重复值，phone 列的基数等于总行数，说明 phone 列没有重复值，相当于主键。gender 列的数据分布如下：

MariaDB [test]> select gender,count(*) from test group by gender order by 2 desc;
    +--------+----------+
    | gender | count(*) |
    +--------+----------+
    |      1 |        4 |
    |      2 |        3 |
    +--------+----------+
    2 rows in set (0.00 sec)

gender 列的数据分布极其不均衡，运行如下 SQL。

MariaDB [test]> select * from test where gender=1;
    +----+--------+-------------+
    | id | gender | phone       |
    +----+--------+-------------+
    |  1 |      1 | 13054480665 |
    |  2 |      1 | 13167007801 |
    |  4 |      1 | 13167007803 |
    |  6 |      1 | 13167007805 |
    +----+--------+-------------+
    4 rows in set (0.00 sec)

gender 为 1 有 4 条数据，从 7 条数据里查询 4 条数据，也就是说要返回表中超过 50% 的数据。

MariaDB [test]> select 4/7*100 "percent from dual";
    +-------------------+
    | percent from dual |
    +-------------------+
    |           57.1429 |
    +-------------------+
    1 row in set (0.00 sec)

那么请思考，你认为如上查询应该使用索引？现在我们换一种查询语句。

MariaDB [test]> select * from test where phone='13054480665';
    +----+--------+-------------+
    | id | gender | phone       |
    +----+--------+-------------+
    |  1 |      1 | 13054480665 |
    +----+--------+-------------+
    1 row in set (0.00 sec)

phone 等值条件有 1 条数据，从 7 条数据里查询 1 条数据，也就是说要返回表中 14% 的数据。

MariaDB [test]> select 1/7*100 "percent from dual";
    +-------------------+
    | percent from dual |
    +-------------------+
    |           14.2857 |
    +-------------------+
    1 row in set (0.00 sec)

请思考，返回表中 14% 的数据是否走索引？

如果你还不懂索引，没关系，可以看下笔者其他相关的 chat。如果你回答不了上述问题，我们先提醒一下。当查询结果返回表中 30% 内的数据时，应该走索引（表中数据量小，其实 phone 的等值查询也是）；当查询结果返回的是超过表中 30% 数据时，基本会走全表扫描。

当然了，返回表中 30% 内的数据会走索引，返回超过 30% 数据就使用全表扫描，这个结论太绝对了，但其实大多场景下，你先记住这个 30% 这个界限吧。这里之所以让记住 30% 这个界限，是不想让初学者为了答案纠结，其实工作中真返回超过 30% 的数据量，本身业务角度就有问题，尤其在 oltp 业务下。

现在有如下查询语句：

select * from test where gender=:b1;

语句中 :b1 是绑定变量，可以传入任何值，该查询可能走索引也可能走全表扫描。

现在得到一个结论：如果一个列基数很低，该列数据分布不均衡，由于该列数据分布极度不均衡，会导致 SQL 查询可能走索引，也可能走全表扫描。

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

如果 SQL 语句是单表访问，可能走索引扫描，也可能走全表扫描，也可能走物理物化视图扫描。在不考虑物理物化视图的情况下，单表访问要么走索引扫描，要么走全表扫描。

现在，回忆一下，走索引的条件：返回表中 30% 内的数据要么走索引，要么走全表扫描。相信大家看到这里，已经懂得单表访问的优化方法。

我们来看如下查询：

select * from test where phone=:b1;

不管 phone 传入任何值，都应该走索引。

### **2. 选择性（SELECTIVITY）** ###

基数与总行数的比值再乘 100% 就是一个列的选择性。

在进行 SQL 优化的时候，单独看列的基数是没有任何意义的，基数相对于总行数才有实际意义，正是这个原因，我们才引出选择性这个概念。

请思考，什么样的列必须建立索引？

有人说基数高的列，有人说在 where 条件中的列。这些答案并不完美。基数高究竟多高？没有和总行数对比，始终不知道有多高。

比如一个列的基数是几万行，但是总数是十几亿行，那么这个列的基数还高？这就是引出选择性的根本原因。

对于如下 SQL

select * from test where phone=:b1;

不管 phone 传入任何值，最多返回1条。

什么样的列必须要创建索引呢？当一个列出现在 where 条件中，该列没有创建索引并且选择性大于 20% 时，那么该列必须创建索引，从而提升 SQL 查询性能。当然了，如果表只有几百条数据，那我们就不用创建索引了。

下面抛出 SQL 优化核心的第一个观点：只有大表才会产生性能问题。

也许有人会说：“我有个表很小，只有几百条，但是经常进行 DML，会产生热点块，也会出性能问题。”对此我们并不想过多的讨论，这属于应用程序设计问题，不属于 SQL 优化的范畴。

### **3. 回表（TABLE ACCESS BY INDEX ROWID）** ###

当对一个列创建索引之后，索引会包含该列的键值及键值对应行所在的 rowid。通过索引中记录的 rowid 访问表中的数据就叫回表。

回表一般是单块读，回表次数太多会严重影响 SQL 性能，如果回表次数太多，就不应该走索引扫描，应该直接走全表扫描。

在进行 SQL 优化时，一定要注意回表次数！特别是注意回表的物理 IO 次数。

此 SQL 执行计划的 ALL，也就是说全表扫描，但是 select \* 需要查询表中所有的列，也就是回表。

在此处，引出 SQL 优化有一核心点：减少回表，也就是网络传输消耗。笔者公司前段时间刚出现大量回表导致小部分用户，使用功能超时。

什么样的 SQL 必须要回表？

select * from test where .......;

这样的 SQL 就必须要回表，所以我们一般禁止使用 select \*。那么什么样的 SQL 不需要回表？

select count (*) from test;

这样的 SQL 就无需回表。

当要查询的列也包含在索引中，这个时候就不需要回表，所以我们往往会建立组合索引来消除回表，从而提升性能。

当一个 SQL 有多个过滤条件但是只有一个列或者部分列建立了索引，这个时候回出现回表再过滤，也需要创建组合索引，进而消除回表再过滤，从而提升查询性能。

关于回表有些专业名词，笔者是借用 Oracle 数据中的，其实思想是想通的。

### **4. 集群因子** ###

集群因子用于判断索引回表需要消耗的物理 IO 次数。

我们先对测试表 test 的 object\_id 累创建一个索引 idx\_id。

create index idx_id on test(object_id);

然后我们查看该索引的集群因子。（基于 Oracle 的）

select onwer,index_name,clustering_factor from dba_indexes where owner="scott" and index_name="IDX_ID';

索引 idx\_id 的叶子块中有序存储了索引的键值及键值对应行所在的 rowid。

sql>select * from (select object_id,rowid from test where object_id is not null order by object_id) where rownum<=5;
    object_id     rowid
    2             AAASNJAAEAAAAAITAAw
    3             AAASNJAAEAAAAAITAAf
    4             AAASNJAAEAAAAAITAAx
    5             AAASNJAAEAAAAAITAAa
    6             AAASNJAAEAAAAAITAAV

集群因子的算法如下：

*  首先我们比较 2、3 对应的 rowid 是否在同一个块，如果在同一个块 clustering\_factor+0，如果不在同一个数据块，clustering\_factor+1。
 *  然后我们比较 3、4 对应的 rowid 是否在同一个数据块，如果在同一个块 clustering\_factor+0，如果不在同一个数据块，clustering\_factor+1。

如上面步骤一样，一直这样有序的比较下去，直到比较完索引中最后一个键值。

根据算法，我们直到集群因子介于表的块数和表的行数之间。

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

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

集群因子只会影响索引范围扫描及索引全扫描，因为只有这两种索引扫描数据会有大量数据回表。

集群因子不会影响索引唯一扫描，因为索引唯一扫描只返回一条数据。集群因子更不会影响索引快速扫描，因为索引快速扫描不回表。

集群因子到底影响的是什么性能？集群因子影响的是索引回表的物理 IO 次数。

我们假设索引范围扫描返回了 1000 行数据，如果 buffer  cache 中没有缓存表的数据块，假设这 1000 行数据都在同一数据块中，那么回表就需要耗费 1 个物理 IO；

假设这 1000 行数据都在不同的数据块中，那么回表就需要消耗 1000 个物理 IO。因此集群因子影响索引回表的物理 IO 次数。

请注意，不要尝试重建索引来降低集群因子，这根本没用，因为表中的数据顺序始终没变。

唯一能降低集群因子的办法就是根据索引列排序对表进行重建（creeate table new\_table as select \* from old\_table order by 索引列），但是这在实际操作中是不可取的，因为我们无法照顾到每一个索引。

怎么才能避免集群因子对 SQL 查询性能产生影响？集群因子只影响索引范围扫描和索引全扫描。当索引范围扫描，索引全扫描不回表或者返回数据量很少的时候，不管集群因子多大，对SQL查询性能几乎不受影响。