SQL优化之Record上的索引

感谢各位朋友的阅读和转发，没关注的请关注微信公众号EvolveHRMS。 以下是正文部分。

用户总是向我们抱怨“慢、慢、慢”，登录慢菜单慢查询慢算薪慢等等，仿佛系统已经病入膏肓，无药可救。上线后，性能问题如同鬼魅一般跟随着我们的系统。这个系统终其一生，都在饱受性能问题的困扰。假如“人生若只如初见”，都像系统在开发环境那样该有多好。

解决性能问题是一个系统工程，没有一个开关，打开就可以提升性能,换句话说就是没有什么灵丹妙药，给系统喂下就可以提升百倍性能。那是不是性能问题就没治了呢？也不是，提升的关键就在一个一个细节里。本篇不谈系统架构，只认识一下record上的索引。由于PS系统大部分都建在Oracle数据库里，文中提到的知识也是基于Oracle的。

1基础知识

执行计划

一条SQL从提交到返回结果中间经历了许多步骤。Oracle用来执行目标SQL语句的这些步骤的组合就被称为执行计划[1]。

索引

索引是一种能提高数据获取获取速度的结构，代价是需要额外的维护和存储空间[2]。Oracle数据库中最常见的索引类型是b-tree索引，也就是B-树索引

组合索引

组合索引是同时在两个或两个以上列建的索引。在一个列上建的索引叫单列索引。

下面我们看一下AppDesigners是如何创建和管理这些索引的。

2Build Record时的索引-PSOPRDEFN

以常见的存储用户名的表PSOPRDEFN为例，其中看到OPRID是key，USERIDALIAS是Alternate Search Key.

Build的时候可以只生成Script，用于查看执行语句。

Build之后，可以看到自动创建了5个索引。

其中一个唯一性索引，另外4个是B树索引。如果为某个字段勾选了Alternate Search Key，那么创建索引的时候就会创建一个Alternate Search Key+Key(s)的组合索引，此处就是索引PS0PSOPRDEFN，是(USERIDALIAS,OPRID)的组合索引。

如何查看SQL语句的执行计划？

在PLSQLDeveloper中查看执行计划非常简单，选中一个SQL语句，按F5就可以了，也可以从工具栏上的图标。

平时工作的时候我们最常用的查询之一通常是OPRID和EMPLID互查，

语句1：SELECT OPRID FROM PSOPRDEFN WHERE EMPLID=:1

语句2：SELECT EMPLID FROM PSOPRDEFN WHERE OPRID=:2

我们看语句2的执行计划，因为指定了OPRID的数值，毫无疑问走了索引PS_PSOPRDEFN。

我们看语句1的执行计划，因为语句1指定了EMPLID，所以走了索引PSBPSOPRDEFN。

两个语句的cost都非常小，都只有2.

下面我们删除索引后查看执行计划和Cost

使用语句dropindexPSBPSOPRDEFN删除索引PSBPSOPRDEFN之后，再查看语句1的执行计划，使用了全表扫描，Cost达到了147，和之前的成本2相比，是没有走索引时候成本的73.5倍。

所以在使用AppDesigner创建表的时候自动创建了索引PSBPSOPRDEFN。这是标准产品基于对常见业务的判断自动创建的索引。

3用JOB表另举一个例子

在JOB表中LOCATION字段默认是没有勾选AlternateSearch Key的，现在我们选中LOCATION字段的Alternate Search Key，重新build索引。

在AppDesigner的输出窗口中从生成的SQL中我们可以找到创建索引的语句

CREATE INDEX PS5JOB ON PS_JOB(LOCATION,

EMPLID,

EMPL_RCD,

EFFDT,

EFFSEQ) TABLESPACE PSINDEXSTORAGE (INITIAL 40000 NEXT 100000

MAXEXTENTS UNLIMITED PCTINCREASE 0)PCTFREE 10 PARALLEL NOLOGGING

这和我们之前的推断是一致的：如果为某个字段勾选了Alternate Search Key，那么创建索引的时候就会创建一个AlternateSearch Key+Key(s)的组合索引。

现在我们用LOCATION作为条件查，生成执行计划看到会走另一个索引PSDJOB，

这个索引的创建语句是：

CREATE INDEX PSDJOB ON PS_JOB(SETID_LOCATION,

LOCATION)

PCTFREE 10 INITRANS 2 MAXTRANS 255COMPUTE STATISTICS

PCTINCREASE 0 FREELISTS 1 FREELISTGROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHEDEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "PSINDEX"

此处的查询并不符合最左前缀原则，但优化器仍然选择了这个索引，使用了INDEX SKIP SCAN,因为从数据来看SETID_LOCATION只有数个，是选择INDEX SKIP SCAN的合适的条件。

Skip scan会探测出索引前导列的唯一值个数，每个唯一值都会作为常规扫描的入口，在此基础上做一次查找，最后合并这些查询. ORACLE官方说，在前导列唯一值较少的情况下，才会用到index skip can。这个其实好理解，就是入口要少，这也是skip scan的条件[3]。如果SETID_LOCATION的数值很多，数据库可能没有索引能用，只会走全表扫描了。

我们删掉这个索引dropINDEXPSDJOB;再看同样语句的执行计划，见下图。我们看到因为没有找到合适的索引可以用，所以走了全表扫描,cost达到了1270，是走索引PSDJOB的时候成本的两百倍之多。

在勾中LOCATION的Alternate Search Key的情况下重新build一遍索引，再次为相同语句生成执行计划，我们看到这次的查询优化器选择了走PS5JOB这个索引。这个索引正是我们为LOCATION字段选中Alternate Search Key后创建的索引。这次优化器并没有选择走索引PSDJOB。

执行计划的生成随着统计信息的不同会有变化，在有两个索引PSDJOB和PS5JOB的情况下，优化器似乎不太确定选择哪个。

4小结

我们看到勾中字段的Alternate Search Key后不是只在Search Page上多出来了一个字段，而是后台会为之配备索引，以加快查询。如果为某个字段勾选了Alternate Search Key，那么创建索引的时候就会创建一个AlternateSearch Key+Key(s)的组合索引。当然现在打开脑洞，是不是AlternateSearch Key越多越好，也不是，数据库为了维护这些索引也需要额外的代价。系统优化不能头痛医头脚痛医脚，SQL优化也是博大精深，请感兴趣的读者继续深入学习。

[1]《基于Oracle的SQL优化》崔华编著

[2] Database index https://en.wikipedia.org/wiki/Database_index

[3]深入理解Oracle索引(1)：INDEX SKIPSCAN和INDEX RANGE SCAN

https://blog.csdn.net/dba_waterbin/article/details/8550405

------------------------------------------

---------谢谢赞赏，生产内容也需要时间---------