读《SQL优化核心思想》：你不知道的优化技巧
SQL性能问题已经逐步发展成为数据库性能的首要问题，80%的数据库性能问题都是因SQL而导致。
1.1 基数（CARDINALITY）
某个列唯一键（Distinct_Keys）的数量叫作基数。比如性别列，该列只有男女之分，所以这一列基数是2。主键列的基数等于表的总行数。基数的高低影响列的数据分布。
以测试表test为例，owner列和object_id列的基数分别如下所示。
1 SQL> select count(distinct owner),count(distinct object_id),count(*) from test; 2 COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_ID) COUNT(*) 3 -------------------- ------------------------ ---------- 4 29 72462 72462
TEST表的总行数为72 462，owner列的基数为29，说明owner列里面有大量重复值，object_id列的基数等于总行数，说明object_id列没有重复值，相当于主键。owner列的数据分布如下。
SQL> select owner,count(*) from test group by owner order by 2 desc; OWNER COUNT(*) -------------------- ---------- SYS 30808 PUBLIC 27699 SYSMAN 3491 ORDSYS 2532 APEX_030200 2406 MDSYS 1509 XDB 844 OLAPSYS 719 SYSTEM 529 CTXSYS 366 WMSYS 316 EXFSYS 310 SH 306 ORDDATA 248 OE 127 DBSNMP 57 IX 55 HR 34 PM 27 FLOWS_FILES 12 OWBSYS_AUDIT 12 ORDPLUGINS 10 OUTLN 9 BI 8 SI_INFORMTN_SCHEMA 8 ORACLE_OCM 8 SCOTT 7 APPQOSSYS 3 OWBSYS 2
owner列的数据分布极不均衡，我们运行如下SQL。
select * from test where owner='SYS';
SYS有30 808条数据，从72 462条数据里面查询30 808条数据，也就是说要返回表中42.5%的数据。
SQL> select 30808/72462*100 "Percent" from dual; Percent ---------- 42.5160774
那么请思考，你认为以上查询应该使用索引吗？现在我们换一种查询语句。
select * from test where owner='SCOTT';
SCOTT有7条数据，从72 462条数据里面查询7条数据，也就是说要返回表中0.009%的数据。
select 7/72462*100 "Percent" from dual; Percent ---------- .009660236
请思考，返回表中0.009%的数据应不应该走索引？
如果你还不懂索引，没关系，后面的章节我们会详细介绍。如果你回答不了上面的问题，我们先提醒一下。当查询结果是返回表中5%以内的数据时，应该走索引；当查询结果返回的是超过表中5%的数据时，应该走全表扫描。
当然了，返回表中5%以内的数据走索引，返回超过5%的数据就使用全表扫描，这个结论太绝对了，因为你还没掌握后面章节的知识，这里暂且记住5%这个界限就行。我们之所以在这里讲5%，是怕一些初学者不知道上面问题的答案而纠结。
现在有如下查询语句。
select * from test where owner=:B1;
语句中，“:B1”是绑定变量，可以传入任意值，该查询可能走索引也可能走全表扫描。
现在得到一个结论：如果某个列基数很低，该列数据分布就会非常不均衡，由于该列数据分布不均衡，会导致SQL查询可能走索引，也可能走全表扫描。在做SQL优化的时候，如果怀疑列数据分布不均衡，我们可以使用select列，count(*) from表group by列order by 2 desc来查看列的数据分布。
如果SQL语句是单表访问，那么可能走索引，可能走全表扫描，也可能走物化视图扫描。在不考虑有物化视图的情况下，单表访问要么走索引，要么走全表扫描。现在，回忆一下走索引的条件：返回表中5%以内的数据走索引，超过5%的时候走全表扫描。相信大家读到这里，已经搞懂了单表访问的优化方法。
我们来看如下查询。
select * from test where object_id=:B1;
不管object_id传入任何值，都应该走索引。
我们再思考如下查询语句。
select * from test where object_name=:B1;
不管给object_name传入任何值，请问该查询应该走索引吗？
请你去查看object_name的数据分布。写到这里，其实有点想把本节名称改为“数据分布”。大家在以后的工作中一定要注意列的数据分布！
1.2 选择性（SELECTIVITY）
基数与总行数的比值再乘以100%就是某个列的选择性。
在进行SQL优化的时候，单独看列的基数是没有意义的，基数必须对比总行数才有实际意义，正是因为这个原因，我们才引出了选择性这个概念。
下面我们查看test表各个列的基数与选择性，为了查看选择性，必须先收集统计信息。关于统计信息，我们在第2章会详细介绍。下面的脚本用于收集test表的统计信息。
SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', 3 tabname => 'TEST', 4 estimate_percent => 100, 5 method_opt => 'for all columns size 1', 6 no_invalidate => FALSE, 7 degree => 1, 8 cascade => TRUE); 9 END; 10 / PL/SQL procedure successfully completed.
下面的脚本用于查看test表中每个列的基数与选择性。
SQL> select a.column_name, 2 2 b.num_rows, 3 3 a.num_distinct Cardinality, 4 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity, 5 5 a.histogram, 6 6 a.num_buckets 7 7 from dba_tab_col_statistics a, dba_tables b 8 8 where a.owner = b.owner 9 9 and a.table_name = b.table_name 10 10 and a.owner = 'SCOTT' 11 11 and a.table_name = 'TEST'; 12COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS 13--------------- ---------- ----------- ----------- --------- ----------- 14OWNER 72462 29 .04 NONE 1 15OBJECT_NAME 72462 44236 61.05 NONE 1 16SUBOBJECT_NAME 72462 106 .15 NONE 1 17OBJECT_ID 72462 72462 100 NONE 1 18DATA_OBJECT_ID 72462 7608 10.5 NONE 1 19OBJECT_TYPE 72462 44 .06 NONE 1 20CREATED 72462 1366 1.89 NONE 1 21LAST_DDL_TIME 72462 1412 1.95 NONE 1 22TIMESTAMP 72462 1480 2.04 NONE 1 23STATUS 72462 1 0 NONE 1 24TEMPORARY 72462 2 0 NONE 1 25GENERATED 72462 2 0 NONE 1 26SECONDARY 72462 2 0 NONE 1 27NAMESPACE 72462 21 .03 NONE 1 28EDITION_NAME 72462 0 0 NONE 0 2915 rows selected.
请思考：什么样的列必须建立索引呢？
有人说基数高的列，有人说在where条件中的列。这些答案并不完美。基数高究竟是多高？没有和总行数对比，始终不知道有多高。比如某个列的基数有几万行，但是总行数有几十亿行，那么这个列的基数还高吗？这就是要引出选择性的根本原因。
当一个列选择性大于20%，说明该列的数据分布就比较均衡了。测试表test中object_name、object_id的选择性均大于20%，其中object_name列的选择性为61.05%。现在我们查看该列数据分布（为了方便展示，只输出前10行数据的分布情况）。
SQL> select * 2 2 from (select object_name, count(*) 3 3 from test 4 4 group by object_name 5 5 order by 2 desc) 6 6 where rownum <= 10; 7OBJECT_NAME COUNT(*) 8------------------ ---------- 9COSTS 30 10SALES 30 11SALES_CHANNEL_BIX 29 12COSTS_TIME_BIX 29 13COSTS_PROD_BIX 29 14SALES_TIME_BIX 29 15SALES_PROMO_BIX 29 16SALES_PROD_BIX 29 17SALES_CUST_BIX 29 18DBMS_REPCAT_AUTH 5 1910 rows selected.
由上面的查询结果我们可知，object_name列的数据分布非常均衡。我们查询以下SQL。
select * from test where object_name=:B1;
不管object_name传入任何值，最多返回30行数据。
什么样的列必须要创建索引呢？当一个列出现在where条件中，该列没有创建索引并且选择性大于20%，那么该列就必须创建索引，从而提升SQL查询性能。当然了，如果表只有几百条数据，那我们就不用创建索引了。
下面抛出SQL优化核心思想第一个观点：只有大表才会产生性能问题。
也许有人会说：“我有个表很小，只有几百条，但是该表经常进行DML，会产生热点块，也会出性能问题。”对此我们并不想过多地讨论此问题，这属于应用程序设计问题，不属于SQL优化的范畴。
下面我们将通过实验为大家分享本文第一个全自动优化脚本。
抓出必须创建索引的列（请读者对该脚本适当修改，以便用于生产环境）。
首先，该列必须出现在where条件中，怎么抓出表的哪个列出现在where条件中呢？有两种方法，一种是可以通过V$SQL_PLAN抓取，另一种是通过下面的脚本抓取。
先执行下面的存储过程，刷新数据库监控信息。
begin
dbms_stats.flush_database_monitoring_info;
end;
运行完上面的命令之后，再运行下面的查询语句就可以查询出哪个表的哪个列出现在where条件中。
1select r.name owner, 2 o.name table_name, 3 c.name column_name, 4 equality_preds, ---等值过滤 5 equijoin_preds, ---等值JOIN 比如where a.id=b.id 6 nonequijoin_preds, ----不等JOIN 7 range_preds, ----范围过滤次数 > >= < <= between and 8 like_preds, ----LIKE过滤 9 null_preds, ----NULL 过滤 10 timestamp 11 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r 12 where o.obj# = u.obj# 13 and c.obj# = u.obj# 14 and c.col# = u.intcol# 15 and r.name = 'SCOTT' 16 and o.name = 'TEST';
下面是实验步骤。
我们首先运行一个查询语句，让owner与object_id列出现在where条件中。
1SQL> select object_id, owner, object_type 2 2 from test 3 3 where owner = 'SYS' 4 4 and object_id < 100 5 5 and rownum <= 10; 6 OBJECT_ID OWNER OBJECT_TYPE 7---------- -------------------- ----------- 8 20 SYS TABLE 9 46 SYS INDEX 10 28 SYS TABLE 11 15 SYS TABLE 12 29 SYS CLUSTER 13 3 SYS INDEX 14 25 SYS TABLE 15 41 SYS INDEX 16 54 SYS INDEX 17 40 SYS INDEX 1810 rows selected.
其次刷新数据库监控信息。
1SQL> begin 2 2 dbms_stats.flush_database_monitoring_info; 3 3 end; 4 4 / 5PL/SQL procedure successfully completed.
然后我们查看test表有哪些列出现在where条件中。
1SQL> select r.name owner, o.name table_name, c.name column_name 2 2 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r 3 3 where o.obj# = u.obj# 4 4 and c.obj# = u.obj# 5 5 and c.col# = u.intcol# 6 6 and r.name = 'SCOTT' 7 7 and o.name = 'TEST'; 8OWNER TABLE_NAME COLUMN_NAME 9---------- ---------- ------------------------------ 10SCOTT TEST OWNER 11SCOTT TEST OBJECT_ID
接下来我们查询出选择性大于等于20%的列。
1SQL> select a.owner, 2 2 a.table_name, 3 3 a.column_name, 4 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity 5 5 from dba_tab_col_statistics a, dba_tables b 6 6 where a.owner = b.owner 7 7 and a.table_name = b.table_name 8 8 and a.owner = 'SCOTT' 9 9 and a.table_name = 'TEST' 10 10 and a.num_distinct / b.num_rows >= 0.2; 11OWNER TABLE_NAME COLUMN_NAME SELECTIVITY 12---------- ---------- ------------- ----------- 13SCOTT TEST OBJECT_NAME 61.05 14SCOTT TEST OBJECT_ID 100
最后，确保这些列没有创建索引。
1SQL> select table_owner, table_name, column_name, index_name 2 2 from dba_ind_columns 3 3 where table_owner = 'SCOTT' 4 4 and table_name = 'TEST'; 5未选定行
把上面的脚本组合起来，我们就可以得到全自动的优化脚本了。
1SQL> select owner, 2 2 column_name, 3 3 num_rows, 4 4 Cardinality, 5 5 selectivity, 6 6 'Need index' as notice 7 7 from (select b.owner, 8 8 a.column_name, 9 9 b.num_rows, 10 10 a.num_distinct Cardinality, 11 11 round(a.num_distinct / b.num_rows * 100, 2) selectivity 12 12 from dba_tab_col_statistics a, dba_tables b 13 13 where a.owner = b.owner 14 14 and a.table_name = b.table_name 15 15 and a.owner = 'SCOTT' 16 16 and a.table_name = 'TEST') 17 17 where selectivity >= 20 18 18 and column_name not in (select column_name 19 19 from dba_ind_columns 20 20 where table_owner = 'SCOTT' 21 21 and table_name = 'TEST') 22 22 and column_name in 23 23 (select c.name 24 24 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r 25 25 where o.obj# = u.obj# 26 26 and c.obj# = u.obj# 27 27 and c.col# = u.intcol# 28 28 and r.name = 'SCOTT' 29 29 and o.name = 'TEST'); 30OWNER COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY NOTICE 31---------- ------------- ---------- ----------- ----------- ---------- 32SCOTT OBJECT_ID 72462 72462 100 Need index
