数据库索引选择的探索（一）

数据库索引选择的探索（一）

背景

当我们编写或者调优SQL时，经常遇到SQL没有根据我们的预期去选择索引，数据库是如何选择最佳索引的呢？

原理

数据库是一个复杂的系统，一条SQL由客户端发出到服务器返回结果，要经过N多个步骤，典型的流程图（摘自《数据库系统实现》）如下：

为了理解数据库如何选择最优执行计划及最佳索引，我们需要了解以下知识：

**【查询编译器】**负责编译SQL产生执行计划，对整个SQL的执行效率有着决定性的影响。因为SQL是一个规定了"what to do",而没有规定“how to do”的语言，比如同样一条SQL数据的扫描方式有表扫描或着顺序扫描、索引选择、索引扫描、扫描又分正序和倒序，表连接方式又分内部循环连接、哈希连接、排序合并连接等，排序又分内存排序和多路并归排序，每一个节点都有多种实现，每一种实现都有不同的应用场景和代价。查询编译器就要选择最优化的实现和组合，来实现最高的SQL效率和最低的系统消耗代价。

【优化模式】 基于规则的优化方式（RBO），根据数据库的内部规则去选择执行计划，一些简单的DBMS实现还使用这种方式，如HSQLDB2.3.3。基于代价的优化方式（CBO）,根据表和索引的统计信息去估算各个执行计划的代价，选择代价最小的执行计划，大部分现代数据库都是这种方式如SYBASE 15.7和ABASE 3.5.3

【代价公式】 每种数据库其执行计划的代价计算公式都是不一样的，但基本都是 总代价=IO代价+CPU代价，其中IO代价占比最大。

SYBASE计算公式 总代价=物理IO25+逻辑IO2+CPU*0.1

**【直方图】**基于代价的优化方式除了需要统计表和索引的数据量、物理页大小、索引高度、数据聚集比之外，还需要知道列(索引列)的数据分布，来确定索引的可选择性，列的数据分布存储在直方图中，直方图分等宽直方图和等高直方图：

• 等宽直方图适用于每列的不同值很少情况下，可以在直方图中列出每种不同值出现的次数。

• 等高直方图适用于大多数情况，即列有许多的不同值，比如有1000个JBFY，数据库只有有限个列去存储列的分布，数据库把列分布平均存储在N个列中，每个列的值为改列值比重的累加。

应用

一、构造数据

1.建立T_MS_AJ表
2.构造数据10000条，N_JBFY 1700到1710各1条数据,1711为9989条。
3.建立索引

create  unique index I_MS_AJ_PK on T_MS_AJ (C_BH)
go
create index I_MS_AJ_JBFY on T_MS_AJ(N_JBFY)
go
create index I_MS_ZH01 on T_MS_AJ(N_JBFY,C_AH)
go

二、 查看统计信息

统计值信息存储在数据库的系统表中，SYBASE存储在systabstats和sysstatistics中，可以通过以下SQL查看

SELECT 
   i.indid,
   i.name AS objname,
   t.indexheight,
   t.leafcnt,
   t.pagecnt,
   t.rowcnt,
   t.datarowsize,
   t.statmoddate 
   FROM YYFZ..systabstats t,YYFZ..sysindexes i  
WHERE i.id = t.id 
AND i.indid = t.indid 
AND t.id = object_id('T_MS_AJ')

结果为:

三、查看直方图

SYBASE的直方图输出结果为xml字符串，需要将字符串格式化后查看 histogram节点信息

 --返回结果为xml字符串
 sp_showoptstats 'T_MS_AJ','N_JBFY'
 go

结果为：

<histogram>
      <colName>N_JBFY</colName>
      <dataType>integer</dataType>
      <requestedStepCnt>20</requestedStepCnt>
      <actualStepCnt>3</actualStepCnt>
      <samplingPct>0</samplingPct>
      <TuningFact>20</TuningFact>
      <steps>
           <step>1</step>
           <weight>0.00000000</weight>
           <equation>&lt;=</equation>
           <value>1699</value>
      </steps>
      <steps>
           <step>2</step>
           <weight>0.00110000</weight>
           <equation>&lt;=</equation>
           <value>1710</value>
      </steps>
      <steps>
           <step>3</step>
           <weight>0.99890000</weight>
           <equation>&lt;=</equation>
           <value>1711</value>
           </steps>
 </histogram>

四、查看索引选择

常用的查看执行计划的命令为"set showplan on",该命令仅将优化器选择的最优的执行计划打印出来，要查看详细的索引选择过程需要执行"dbcc traceon(302,310,3604)"命令。

310：代表打印现实索引的选择信息。
302：代表打印估算io统计信息。
3604: 代表向终端输出。

set showplan on
go
dbcc traceon(302,310,3604)
go
select C_AH FROM T_MS_AJ WHERE N_JBFY = 1700
go

结果为：

第一部分：可供选择的优化项和优化资源限制
OptCriteria:
distinct_sorte d
distinct_sorting
distinct_hashing
group_sorted
...
...
cr497066: infer the nullability of isnull() by looking at its parameters
cr421607: support NULL=NULL merge and hash join keys
cr552795: eliminate duplicate rows during reformatting when they're not needed
imdb_costing: 0 PIO costing for scans for in-memory database
allow_minmax: allow local session to consider MINMAX optimization
cr646220: enable better store index key generation with correlated predicate

Optimizer Level: ase_default
Optimization Goal: allrows_oltp
Optimization Learning mode disabled.

Optimization Timeout limit: 10% (Default)
Server Level Optimization Timeout has been set.
Optimization query tuning time limit enabled.
The compiler will use the current best plan when 10%(Optimization Timeout limit) of average execution time is exceeded.

Optimization query tuning mem limit enabled.
The compiler will use the current best plan when 10%(Max Resource Granularity) of procedure cache is exceeded.
第二部分：可选择的路径及估算代价

******************************************************************************
          BEGIN: Search Space Traversal for OptBlock0 
******************************************************************************

Scan plans selected for this optblock:



Beginning selection of qualifying indexes for table 'T_MS_AJ',

Estimating selectivity of index 'T_MS_AJ.I_MS_AJ_JBFY', indid 3
    N_JBFY = 1700
    Estimated selectivity for N_JBFY,
        selectivity = 0.0001,
    scan selectivity 0.0001, filter selectivity 0.0001
    1 rows, 1 pages
    Data Row Cluster Ratio 0.9988107
    Index Page Cluster Ratio 1
    Data Page Cluster Ratio 0.03443114
    using no index prefetch (size 16K I/O)
    in index cache 'default data cache' (cacheid 0) with LRU replacement

    using no table prefetch (size 16K I/O)
    in data cache 'default data cache' (cacheid 0) with LRU replacement
    Data Page LIO for 'I_MS_AJ_JBFY' on table 'T_MS_AJ' = 1

Estimating selectivity of index 'T_MS_AJ.I_MS_ZH01', indid 4
    N_JBFY = 1700
    Estimated selectivity for N_JBFY,
        selectivity = 0.0001,
    scan selectivity 0.0001, filter selectivity 0.0001
    1 rows, 1 pages
    Index covers query.
    Data Row Cluster Ratio 0.2706239
    Index Page Cluster Ratio 1
    Data Page Cluster Ratio 0.2585438
    using no index prefetch (size 16K I/O)
    in index cache 'default data cache' (cacheid 0) with LRU replacement

Estimating selectivity for table 'T_MS_AJ'
    Table scan cost is 10000 rows, 751 pages,

The table (Datarows) has 10000 rows, 751 pages,
Data Page Cluster Ratio 0.9893455
    N_JBFY = 1700
    Estimated selectivity for N_JBFY,
        selectivity = 0.0001,
    Search argument selectivity is 0.0001.
    using no table prefetch (size 16K I/O)
    in data cache 'default data cache' (cacheid 0) with LRU replacement
OptBlock0 Eqc{0} -> Pops added: 

	( PopIndScan I_MS_ZH01 T_MS_AJ ) cost:54.29999 T(L2,P2,C3) O(L2,P2,C3) order: none 



******************************************************************************
          DONE: Search Space Traversal for OptBlock0 
******************************************************************************
第三部分：选择的最优执行计划
The best global plan (Pop tree) :

FINAL PLAN ( total cost = 54.29999 ):
	lio=2 pio=2 cpu=3
( PopEmit 
	proj: {{ T_MS_AJ.C_AH } }
	pred: [Tc{} Pe{}]
	subs: {}
	cost: 54.29999 
	I/O estimate : [
		rowcount=1
		averagewidth=41.60223
		pages=1
		prefetchpages=1
		lio=0 pio=0 cpu=0
		total lio=2 total pio=2 total cpu=3
	]
	Cache Strategy: [
		prefetch=YES
		iosize=16384 Bytes
		bufreplace=LRU
	]
	order: none 
	( PopIndScan index: Gti2( I_MS_ZH01 ) 
			table: Gtt0( T_MS_AJ ) 
		
		proj: {{ T_MS_AJ.C_AH } }
		pred: [Tc{} Pe{{ T_MS_AJ.N_JBFY }  = 1700}]
		subs: {T_MS_AJ.N_JBFY ,T_MS_AJ.C_AH }
		cost: 54.29999 
		I/O estimate : [
			rowcount=1
			averagewidth=41.60223
			pages=1
			prefetchpages=1
			lio=2 pio=2 cpu=3
			scanlio=2 scanpio=2 scancpu=3
		]
		Cache Strategy: [
			prefetch=NO
			iosize=16384 Bytes
			bufreplace=LRU
		]
		order: none )
)


QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Serial Mode
    STEP 1
        The type of query is SELECT.

	1 operator(s) under root

       |ROOT:EMIT Operator (VA = 1)
       |
       |   |SCAN Operator (VA = 0)
       |   |  FROM TABLE
       |   |  T_MS_AJ
       |   |  Index : I_MS_ZH01
       |   |  Forward Scan.
       |   |  Positioning by key.
       |   |  Index contains all needed columns. Base table will not be read.
       |   |  Keys are:
       |   |    N_JBFY ASC
       |   |  Using I/O Size 16 Kbytes for index leaf pages.
       |   |  With LRU Buffer Replacement Strategy for index leaf pages.


Total estimated I/O cost for statement 1 (at line 1): 54.

输出结果包括三大部分：

• 第一部分：可选择的优化项和优化资源限制。

优化项例如内部循环连接(nested loop join)、哈希连接(hash join)、排序合并连(sorted merge join)、散列消除重复(distinct_hashing)、排序消除重复(distinct_sorted)等等。

优化的限制主要包括优化等级、目标、时间和占用内存限制等，可以通过全局设置来配置，防止因为执行计划优化消耗过多资源。比如对于join表比较多的情况，可优化选择的表连接顺序随着表个数成指数级增加，全部估算每个表连接计划会消耗大量时间和资源。

• 第二部分：可选择的扫描路径和估算代价。每条路径的可选择性、可过滤性、IO代价都是不同的，可选择性值最小、IO最小的路径最优。

• 第三部分：选择的最优执行计划。包括执行计划的每一个节点代价的详细输出。

数据库如何选择最佳索引的？

实现"SELECT C_AH FROM T_MS_AJ WHERE N_JBFY = 1700" 共有三条路径：

路径1：走I_MS_AJ_JBFY索引及查找一次T_MS_AJ基表，IO=1个索引页(index page)+1个数据页(data page)

路径2：走I_MS_ZH01索引,覆盖索引 IO=1个索引页(index page)

路径3：走全表扫描,IO=751个数据页(data page)

每条路径中N_JBFY的选择性(selectivity)是由直方图桶间比重计算得来(默认桶间比重是线性增长)
selectivity = (0.00110000 - 0.00000000)/(1710 - 1699)

扫描索引页节点页数 = 索引页数 * selectivity

扫描索引总页数 = （索引高度+页节点页数）

由公式“总代价=物理IO25+逻辑IO2+CPU*0.1”可知最优索引为I_MS_ZH01。

总结

SQL执行过程中有多条执行计划多个索引可供选择，数据库查询编译器会采用代价模式根据表的统计信息和直方图计算出代价最小效率最高的执行计划。同时执行计划和索引选择还有很多其他内容需要研究探索，比如数据倾斜和索引的顺序是如何影响索引选择的？ABASE如何查看详细的执行计划索引选择信息？根据执行计划选择的原理，是否可以用统计信息和直方图来模拟数据加压？