B树索引设计

索引第二星

场景4-回表的代价

  我们在上一文的讨论中提出了满足索引第一星的3个原则，满足后的索引似乎已经很优秀了，但是这些都是基于我们在谓词的过滤因子比较好的情况下，而实际业务中可能并没有这么好的过滤因子哪怕是组合谓词。是的，我们再来看一个分页真实场景，在场景3的基础上，扩大create_time检索范围，同时加上常见的排序、分页部分，完整sql如下：

select *
  from ( select tmp_page.*,
               ROWNUM ROW_ID
          from ( select id,
                        age,
                        username,
                        create_time,
                        phone
                   from usert
                  where city = '南宁市'
                    and valid = 1
                    and create_time > '2024-01-01'
               order by id desc ) tmp_page )
 where ROW_ID < 1000
   and ROW_ID > 0 ;

  该语句的原始执行计划如下：

  各谓词的过滤因子如下：

谓词	过滤因子
city=’南宁市’	18%
valid=1	99%
create_time > '2024-01-01'	40%
city=’南宁市’ and valid=1	18%
city=’南宁市’ and create_time > '2024-01-01'	5%
valid=1 and create_time > '2024-01-01'	40%

  我们根据索引第一星的基本原则，选定建立(city,create_time)的索引后，执行结果如下：

  可以看到执行计划中已经选择了我们建立的组合索引，但是执行耗时几乎没有优化，甚至耗时更多了，这是为什么呢？
  我们可以通过ET工具来分析一下（ET是达梦中监控执行计划的各操作符的执行明细情况的工具，合理利用它可以帮助我们快速定位执行计划中的瓶颈所在）：

  从ET返回的监控信息中可以看出，#BLKUP2操作符，即回表操作占据了98%以上的耗时，是的，回表50W数据的代价太大了，导致整体耗时超过了全表扫描。那么是否意味着这个场景下，语句不适合走索引了？
  是的，是不适合走这个一星的组合索引了，既然回表代价太大，我们就要考虑消灭回表操作符。
  在索引的B+树原理学习过程我们知道，回表操作是因为在B+树的叶子节点处只有索引键值+聚集索引指针信息，如果需要获取非索引键值的时候，需要根据聚集索引指针返回聚集索引处获取，就是我们如果不希望回表操作，那么叶子节点处的索引键值就应该包含我们需要的所有列信息。
  根据我们sql语句可以看到我们总共需要的列信息有city、valid、create_time、id、age、username、phone。于是我们建立这么一个包含所有列的组合索引：

CREATE INDEX IDX_USERT_C_V_C_T_I_A_U_P ON USERT(CITY,VALID,CREATE_TIME,ID,AGE,USERNAME,PHONE);

  再来看执行结果，如下图，效率立刻提升了，耗时来到了40毫秒左右，非常理想了：

小结

  从以上的场景4中我们可以看出来回表的代价有时候随着数据量的上升变得不可忽略，甚至远远超过全表扫描的代价，这时候如果语句是高频的主要业务语句，就要考虑建立包含该语句的所有列的组合索引来消灭回表，避免引起性能问题。
  我们可以把这种符合第一星的前提上，又消灭了回表的索引评价上第二星，称之为二星索引。而这种索引也是我们通常说的“黑话”：索引覆盖。

索引第三星

场景5-排序的代价

  在场景4中，我们通过放大结果集来看到回表的代价是不可忽略的，于是通过索引覆盖来消灭回表，达到了非常理想的效率，那么二星索引是否就是最优解了呢？场景4下的语句是否还有优化空间呢？
  我们观察在场景4下建立了覆盖索引后的执行计划，通过ET再分析如下图5.2.5，可以看到耗时中的80%左右在SSEK2（二级索引定位），19%左右在SORT3（数据排序）：

  显然SSEK2已经没有优化空间的可能了，因为索引已经包含了索引列，那么SORT呢？你可能会想，已经40毫秒了，排序才占19%即是6毫秒左右，这个优化有什么必要呢。
  6毫秒看起来很少，但是要知道现在的执行结果是在数据库负载空闲的状态下，如果是高并发的压力下呢，我们通过jemeter模拟一下50并发10秒的一个持续查询，配置如下图：

  测试3次结果如下：

  可以看到在50线程并发下，所有响应的平均耗时来到了386毫秒左右，为了做对比组，我们也做了1线程并发的结果参考，它平均耗时依然是在45毫秒左右：

  显然并发下这个语句的效率也会成倍增长，这里面固然有资源部分的影响，但是语句本身是否还可以进一步的优化，如果还有那就必须要考虑做，而从上面我们的分析中，仅剩下排序的操作符可以考虑，如果把排序操作消灭掉那么语句效率应该还能进一步提升。
  那么如何消灭排序呢。我们检查sql语句可以看到排序是以id列字段为主，根据我们索引的B+树检索原理，我们知道，在前导列确定的前提下，是根据后续的列进行局部排序的。
  而我们创建的索引已经包含了id列，为什么还要排序呢，我们检查一下索引的定义，可以看到在DM8中，如果建立的索引没有指定排序方式时，默认都是以ASC升序进行排序的（如下图5.2.9），而我们场景5的sql语句中却是希望以DESC降序输出结果集的：

  好的，根据sql语句中需要的排序方式，我们重新创建一下这个覆盖索引，显示指定id列为降序，再来验证一下：

CREATE OR REPLACE INDEX IDX_USERT_C_V_C_T_I_A_U_P ON USERT(CITY,VALID,CREATE_TIME,ID DESC,AGE,USERNAME,PHONE);

  然后我们再来看一下执行计划：

  看起来是没有预期效果，执行计划中仍然存在SORT3的操作符，这是为什么呢？我们再次回想一下B+树检索的有序原理，是前导列确定等值的前提下，而我们条件列中是存在范围谓词的，根据索引第一星中场景3的分析，我们如果希望id有序，那么create_time就不能在id前面。
  于是，我们重新调整索引列的顺序后，重建该覆盖索引：

CREATE OR REPLACE INDEX IDX_USERT_C_V_C_T_I_A_U_P ON USERT(CITY,VALID,ID DESC,CREATE_TIME,AGE,USERNAME,PHONE);

  接着我们再来看一下执行计划：

  可以看到SORT3操作符已经去掉，语句效率来到了16毫秒左右了。我们最后再通过jemeter来模拟一下50并发10秒查询的结果来和未优化排序前进行对比：

  3次测试结果可以看出，对比未消除排序操作符的结果，性能已经又得到了极大的提升，压力测试下平均耗时已经在20毫秒内了，并发下的业务场景基本不会有性能损耗的感知体现了。
  至此，这个语句在索引利用上已经没有优化的空间了。

小结

  通过场景5的并发查询业务场景，我们更进一步的看到了排序的代价以及优化后得到的巨大性能提升，所以排序也是我们日常优化中需要重点关注的点。
  我们在基于第二星的基础上，把消灭了排序的索引，评价为第三颗星，称为三星索引。

B树索引设计总结

  从上面5个场景的测试例子看到满足了三星索引后，sql语句效率就可以非常的优秀了。三星索引评价的规则也不难理解记忆，在此我们回顾总结一下：

• 索引第一星

1. 最低过滤因子：索引设计包含最低过滤因子的谓词列。
2. 最左原则：索引的最左列必须在sql语句的谓词当中，否则该sql语句无法利用此索引。
3. 等值优先范围：组合索引中如果同时包含等值和范围谓词的话，等值谓词列必须要在范围谓词列前。
4. 补充：当两个或多个字段都是等值查询时，组合索引中各个列的前后关系是无关紧要的；同时存在多个范围谓词时，索引只能利用一个范围谓词。

• 索引第二星

1. 消灭回表：回表的代价是随着二级索引定位后返回的结果集数量决定，如果回表代价太大就需要考虑消灭回表。
2. 补充：回表消除除了利用二级B树索引外，也可以通过聚集索引来实现，但是聚集索引的限制存在（如每个表只能存在一个聚集索引、不适合修改频繁的列等）导致其并没有二级索引这么灵活使用。

• 索引第三星

1. 消灭排序：排序也是有代价的，如果语句中存在排序就需要关注他的代价，排序代价过大，也追求最高效率就可以考虑尝试消灭排序。
2. 补充：排序和排序数量、排序区内存使用相关，并且并不是所有排序都可以优化的，如嵌套查询中的外层排序，多表关联中的不同表的列联合排序，都是不能直接利用索引优化排序的，这时候如果排序的代价不能接受，就要考虑排序的必要性了，评估能否从逻辑上去掉排序。

参考文档：
[1]《DM8系统管理员手册》
[2]《DM8_SQL语言使用手册》
[3]《数据库索引设计与优化》