数据库优化思路 - 旧游无处不堪寻

基数、选择性等指标影响数据库优化的思路，下面我们会依次结束一些指标来指导我们进行数据库的优化。

基数

数据表中某一列不重复(distinct)的值的个数称为基数。例如性别列，基数为 2。基数越小，记录分布的就越不均匀。
我们以 test 表为例，说明基数对数据库优化的影响。

select count(distinct owner), count(distinct object_id), count(1) from test;

count(distinct owner)	count(distinct object_id)	count(1)
20	75560	75560

我们看到 owner 列的基数很小，object_id 列的基数等于表的总行数。说明 owner 列里面有大量重复值，我们再看一下 owner 分布情况:

select owner, count(owner) from test group by owner order by 2 desc;

owner	count(owner)
SYS	20808
PUBLIC	19870
ADMIN	12768
…	…
SCOTT	3

owner 列的数据分布极不均衡，这就导致如果我们运行如下 SQL:

select * from test where owner='SYS';

需要返回 20808 条数据，占总数据的 27.5%。如果我们换一个条件:

select * from test where owner='SCOTT';

只返回 3 条数据，占比为 0.00397%。那么我们思考一下，如果 owner 列为索引列，以上两个语句走索引的情况。
一般情况以 5% 为限，超过则全表扫描，低于则走索引。

选择性

基数与总行数的比值再乘以 100% 就是某个列的选择性。在进行 SQL 优化的时候，单独看列的基数是没有意义的，基数必须对比总行数才有实际意义。什么样的列必须建立索引呢，我们使用选择性这一指标来衡量，当一个列选择性大于 20%，说明该列的数据分布就比较均衡了。该列出现在 where 子句中时，就应该创建索引。

select a.column_name, 
	   b.num_rows, 
	   a.num_distinct, 
	   round(a.num_distinct/b.num_rows * 100, 2) selectivity
from dba_tab_col_statistics a, dba_tables b
where a.owner=b.owner
	and a.table_name=b.table_name
	and a.owner='SCOTT'
	and a.table_name='TEST';

column_name	num_rows	num_distinct	selectivity
OWNER	75560	20	.03
OBJECT_NAME	75560	44236	57.71
…	…	..	..

回表

当对一个列创建索引之后，索引会包含该列的键值以及键值对应行所在的 rowid。通过索引中记录的 rowid 访问表中的数据就叫回表。回表次数太多会严重影响 SQL 性能，如果回表次数太多，就不应该走索引扫描了，应该直接走全表扫描。
什么样的SQL必须要回表，例如下面的语句:

select * from table where ...

这样的 SQL 就必须回表，所以我们必须严禁使用Select *。那什么样的SQL不需要回表，例如下面的语句:

select count(*) from table

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

select name, age from user where name='SCOTT' and age=20;

总结

根据上面的一些事例，我们知道:

应该选择选择性高的，出现在 where 子句中的列作为索引列
应该尽量避免回表，建立组合索引消除回表操作