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 1509XDB 844OLAPSYS 719SYSTEM 529CTXSYS 366WMSYS 316EXFSYS 310SH 306ORDDATA 248OE 127DBSNMP 57IX 55HR 34PM 27FLOWS_FILES 12OWBSYS_AUDIT 12ORDPLUGINS 10OUTLN 9BI 8SI_INFORMTN_SCHEMA 8ORACLE_OCM 8SCOTT 7APPQOSSYS 3OWBSYS 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_name10 10 and a.owner = 'SCOTT'11 11 and a.table_name = 'TEST';12COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS13--------------- ---------- ----------- ----------- --------- -----------14OWNER 72462 29 .04 NONE 115OBJECT_NAME 72462 44236 61.05 NONE 116SUBOBJECT_NAME 72462 106 .15 NONE 117OBJECT_ID 72462 72462 100 NONE 118DATA_OBJECT_ID 72462 7608 10.5 NONE 119OBJECT_TYPE 72462 44 .06 NONE 120CREATED 72462 1366 1.89 NONE 121LAST_DDL_TIME 72462 1412 1.95 NONE 122TIMESTAMP 72462 1480 2.04 NONE 123STATUS 72462 1 0 NONE 124TEMPORARY 72462 2 0 NONE 125GENERATED 72462 2 0 NONE 126SECONDARY 72462 2 0 NONE 127NAMESPACE 72462 21 .03 NONE 128EDITION_NAME 72462 0 0 NONE 02915 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 3010SALES 3011SALES_CHANNEL_BIX 2912COSTS_TIME_BIX 2913COSTS_PROD_BIX 2914SALES_TIME_BIX 2915SALES_PROMO_BIX 2916SALES_PROD_BIX 2917SALES_CUST_BIX 2918DBMS_REPCAT_AUTH 51910 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 timestamp11 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r12 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 INDEX10 28 SYS TABLE11 15 SYS TABLE12 29 SYS CLUSTER13 3 SYS INDEX14 25 SYS TABLE15 41 SYS INDEX16 54 SYS INDEX17 40 SYS INDEX1810 rows selected.
其次刷新数据库监控信息。
1SQL> begin2 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 OWNER11SCOTT 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 SELECTIVITY12---------- ---------- ------------- -----------13SCOTT TEST OBJECT_NAME 61.0514SCOTT TEST OBJECT_ID 100
最后，确保这些列没有创建索引。
1SQL> select table_owner, table_name, column_name, index_name2 2 from dba_ind_columns3 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) selectivity12 12 from dba_tab_col_statistics a, dba_tables b13 13 where a.owner = b.owner14 14 and a.table_name = b.table_name15 15 and a.owner = 'SCOTT'16 16 and a.table_name = 'TEST')17 17 where selectivity >= 2018 18 and column_name not in (select column_name19 19 from dba_ind_columns20 20 where table_owner = 'SCOTT'21 21 and table_name = 'TEST')22 22 and column_name in23 23 (select c.name24 24 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r25 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 NOTICE31---------- ------------- ---------- ----------- ----------- ----------32SCOTT OBJECT_ID 72462 72462 100 Need index