雪落夜的博客

sql优化核心思想

2025-01-07

一、SQL优化必懂概念

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

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

选择性高代表该列数据多样化、重复值少，更适合使用索引。而选择性低代表该列数据有大量重复，不适合走索引。到底选择性超过多少算高，没有特别绝对大值，一般要在20%以上。

为什么索引适合执行重复值少的数据？

一种说法是B树更适合查找唯一性的值，这样可以尽量少遍历节点，而大量重复值势必要遍历很多重复节点。那顺序索引呢？

第二种说法是优化器会将选择性低于5%（这应该只有Oracle是5%）的列强制走全表扫描，就算加了索引也不生效。

什么样的列要给它建索引？

  1. 该列作为where查询条件或order by 字段。

  2. 选择性大于20%。

  3. 数据库表行数不能太少。例如超过1万行。

直方图：直方图的作用是对列的选择性进行统计，供成本优化器COB使用。执行计划里的rows是计算值，未必准确。直方图保证成本优化器得到精准的rows数据。

基数低的列如果不收集直方图信息，有可能会被优化器认为是高基数而误走索引。

下面这个例子没有开启直方图，从72 462条数据里面返回30 808条数据，但执行计划计算的Rows是2499行。因为owner列的基数是29，它是平均计算72462/29得到非常不准确的行数。而又因为2499条只占总数的3.4%，所以优化器选择走索引，但如果按实际3万多条数据，肯定不应该走索引，而应该全表扫描。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> select * from test where owner='SYS';

30808 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3932013684

-------------------------------------------------------------------------------------
| Id |Operation                   | Name     | Rows  | Bytes | Cost(%CPU)| Time     |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |          |  2499 |   236K|   73   (0)| 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID| TEST     |  2499 |   236K|   73   (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN           | IDX_OWNER|  2499 |       |    6   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SYS')
← 《个人品牌打造》总结
欢迎关注公众号

扫描二维码，分享此文章