前言

  我们通常所说的“加索引”在90%场景下都是说的B树索引（后续统称B树索引，不再单独区分普通B树和B+树），如果把olap系统中数据库表当作一道大餐，那么B树索引毫无疑问是那道不可或缺的调料品，没有索引的大多数业务场景将食之无味。
  从前面的原理知识学习，我们可以了解索引将极大的加速了定位查询和范围查询的检索过程，那么是否是只要存在利用这个条件的业务场景，我们把这个条件的索引建立起来就可以了呢？
  那显然是不合适的，我们知道很多时候二级索引是需要返回聚集索引来获取完整数据的，这个回表的过程有时候也是影响性能的一部分，并且不同列的索引适合不同的场景，索引占据的存储空间，对表DML的性能考虑也是不可缺少的。
  所以作为一个合格的数据库管理员或者开发从业人员，设计合理的索引是系统设计初期非常重要的一环，他能避免后期很多不必要的性能问题。当然索引优化也是伴随数据库生命周期的长期工作。

B树索引设计

  那么如何设计一个合理的B树索引呢，接下来让我们逐步展开，在开始学习之前我们先认识几个通用名词（以下名词可能在不同文献中存在差异叫法，原理大同小异，仅供学习参考使用）。

基础名词

• 1、基数
    一般指表中某列（或索引列）的唯一值的数量，反映的是数据的分布特征。例如user表中id列为主键唯一，那么id的基数就是表的总行数，而sex列只有男和女，那么sex列的基数就是2。
• 2、谓词
    一般是指 SQL 语句中用于过滤数据的条件表达式，通常出现在 WHERE、JOIN ON 或 HAVING 等子句中，搭配=、in、like、not in、>、<等使用。
    例如以下的sql语句中，谓词有单列谓词”id=10010”，范围谓词”create_time>’2025-01-01’ ”，或者统称组合谓词”id =10010 and create_time>’2025-01-01’”。

select * from user where id =10010 and create_time>’2025-01-01’;

• 3、过滤因子
    一般是指谓词对数据集的过滤比例，即满足条件的行数占表总行数的百分比。即过滤因子=满足条件的行数*100/总行数，单位%。例如存在user表总行数为1000行，以下sql查询结果分别为：

select * from user where sex =’男’;  ---结果集为550行
select * from user where create_time>’2025-01-01’;  ---结果集为200行

  于是单列谓词”sex =’男’ ”的过滤因子是：550 * 100/1000=55%，范围谓词”create_time>’2025-01-01’ ”的过滤因子是200 * 100/1000=20%。
  在不知道两者组合过滤下的实际结果集时，可以推测两者组合一起的组合谓词过滤因子是：55%*20%<=过滤因子<=20%。
  过滤因子是谓词的一个属性。

• 4、回表
    二级索引通过索引键定位数据后，如果还需要获取其他不包含在二级索引中的列信息，就需要返回聚集索引处来获取完整数据，这个过程就是回表。
    二级索引定位后的数据页是集中顺序的，但是顺序返回聚集索引处访问的顺序就不一样了，如果二级索引定位的数据比较多，就仍然有可能造成大量随机存储I/O访问，引发性能问题。
    在达梦中通过查看sql的执行计划来确认有无回表，执行计划中二级索引定位操作符SSEK2的上层操作符出现BLKUP2，就表示存在回表操作。如下图中检索路径利用了二级索引IDX_TEST20250515_ID进行索引定位，同时因为需要查询该索引以外的其他列数据，所以产生了回表操作。

索引第一星

  在同一个场景下，不同的索引有好有劣，为此我们可以根据一些优先条件来设计一些思路，当索引满足这些条件则评价为一定星级，以此判断此索引是否是最优秀的选择。

场景1-最低过滤因子

  索引是为了加速数据定位检索过程的，那么索引设计的选择显然应该从谓词的过滤性分析中开始，例如以下场景1的SQL语句：

select id,age,username,create_time,phone from usert
 where 
sex='男'
and id='105010'
and create_time > '2023-01-01'
order by create_time desc;

  谓词存在3个，根据排列组合我们的索引选择可以有7个，那么该如何选择呢？我们可以先来确认3个单列谓词各自的过滤因子，假设经过计算后得到过滤因子分别如下表：

谓词	过滤因子
sex='男'	48%
id='105010'	<0.01%
create_time > '2023-01-01'	85%

  在有过滤因子的前提下，我们就可以很容易选择了，显然第二个谓词”id='105010'”的过滤性非常好，那么基于这个场景下的索引设计，包含id列毋庸置疑。

场景2-最左原则

  在场景1中，我们得出结论，索引必须包含id列，那么事实是否只要包含了id列的索引都会生效呢，假设因为在另一个业务场景的查询需要，已经创建有了(same,sex,id)顺序的索引，他也包含了id列，是否也适合场景1，我们验证一下，创建以下索引后查看执行计划：

CREATE INDEX IDX_USERT_SNAME_SEX_ID ON USERT(SNAME,SEX,ID);

  很显然，优化器没有选择已有的组合索引依然是全表扫描CSCN2，哪怕以(same,sex,id)顺序建立的组合索引已经包含了我们期望利用的id列。
  这个可以从我们B+树的构建原理中来解释，我们知道索引是以索引键值的顺序进行构建B+树的，而在以(same,sex,id)顺序建立的时候，索引键值的排序优先级别是sname>sex>id，即先以sname比较大小排顺序，而只有sname相等时才以sex比较大小排顺序，而只有sname和sex都相等时，才以id比较大小排顺序。

  那么这时候的sex和id在以(same,sex,id)键值构造的B+树中是整体散列分布的，只有在sname已确定的局部中sex才有序，在sname,sex已确定的局部中id才有序。这意味着要利用索引(same,sex,id)就必须存在sname列条件，但是谓词中不存在该列，所以无法利用。
  这就是B+树中存在的最左原则：B+ 树的层级排序要求查询条件必须从最左列开始，否则索引无法逐层定位数据路径。基于此原则我们只能创建诸如(id)，(id,age)，(age,id)等以sql语句中的谓词列在最左顺序的索引后，此场景2的sql语句才可以利用。
  即索引的最左列必须在sql语句的谓词当中，否则该sql语句无法利用此索引！索引的最左列我们有时候又称之为前导列。

场景3-等值优先范围

  在场景1中id本身就是高基数列，他出现在谓词中让索引列选择显得容易了很多，但有时候sql语句中并没有高基数列，即单谓词的过滤因子并不好，例如以下场景2的sql语句：

select id,age,username,create_time,phone from usert
 where 
city='南宁市'
and valid=1
and create_time > '2024-10-01';

  原始语句计划，无任何索引情况下，cscn全表扫描后，根据3个谓词进行过滤，然后返回行数据：

  他们的单谓词过滤因子如下：

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

  单谓词显然不足以达到索引的预期目标，因为他们单个的过滤因子都太大了超出了10%，于是我们看组合谓词的过滤因子：

谓词	过滤因子
city=’南宁市’ and valid=1	18%
city=’南宁市’ and create_time > '2024-10-01'	0.2%
valid=1 and create_time > '2024-10-01'	20%

  于是场景2下的候选索引为组合列：city和create_time。那么以city,create_time顺序和create_time,city的顺序建立的索引都符合最低过滤因子、最左原则了，那他们的效果是一样的吗？我们测试验证一下：
  city,create_time顺序的索引下，语句执行计划：

  create_time,city顺序的索引下，语句执行计划：

  以上对比可以看到在获取完整数据的情况下，city,create_time顺序的时候是优于create_time,city顺序的，从实际的执行计划中也可以看到差别，city,create_time顺序下，SSEK2后往上就是BLKUP2了，这意味着一趟二级索引定位就完成了city,create_time两个谓词的过滤和数据定位，直接就可以回表获取完整数据了。
  而create_time,city顺序下，SSEK2后上一层还有一层SLCT2才到BLKUP2，SLCT2里是谓词”city=’南宁市’ ”的过滤操作，即SSEK2二级索引定位的过程只利用了范围谓词”create_time > '2024-10-01'”的过滤性。为什么会如此呢？
  （这一段原理描述可能稍显啰嗦，如果你的时间宝贵请直接看结论）为了更深入理解，我们根据以上语句简化一组20个的表数据如下，其中city列内容用两字母表示，create_time内容用数字表示，索引1是city,create_time顺序，索引2是create_time,city顺序：

  根据前面过滤因子的大小，我们设定业务场景需要检索usert表中city=’NN’ and creaet_time > 67的数据，那么索引1和索引2的检索过程有什么区别呢。
  我们先看索引1构造的B+树的检索过程（下图5.1.8），首先root指针读取第1次io获取根节点信息,通过键值信息”NN18”确认需要a指针的二层节点信息，第2次io获取a指针的二层节点信息，根据键值信息”NN69”确认需要b和c指向的叶子节点，根据键值信息”QZ49”确认d，c的叶子节点不需要获取，于是进行最后2次IO，获取d和*c指向的叶子节点，得到两个叶子节点中的完整键值信息和聚集索引指针，在场景2中我们知道，组合索引在第1列确定的情况下是以第2列排序的，所以我们这里确定”NN37”、”NN69”后，即可顺序得到”NN69”、”NN98”符合检索条件，完成索引键值数据快速定位检索过程，最后只要根据聚集索引指针返回聚集索引处获取完整数据即可：

  我们再看索引2构造的B+树的检索过程（下图5.1.9），首先同样root指针读取第1次io获取根节点信息,通过键值信息”67GG”确认需要a指针的二层节点信息，第2次io获取*a指针的二层节点信息，这时候差异来了，*a的二层节点中的所有键值信息均无法得到准确的判断，即所有叶子节点中键值信息第1列均是>67的，而第2列均可能=’NN’，于是只能是遍历该二层节点处的所有叶子节点，即以此进行第3,4,5,6次IO，读取所有叶子节点的信息，这里所有叶子节点的键值信息的第1列都满足create_time>67的条件了，但是city=’NN’无法直接比较，因为第1列create_time>67的值是不确定的，city的值就是散列的，所以需要重新针对这部分键值信息根据city值排序后，再获取满足city=’NN’的键值信息指针，而这里的操作就是我们看到的SSEK2后需要再进行一次SLCT2的操作符。

  到这里我们得出另一个结论，组合索引中如果同时包含等值和范围谓词的话，等值谓词列必须要在范围谓词列前。

小结

  从以上讨论中我们得到了一些基本索引设计思路，如最低过滤因子的谓词选择，索引最左列必须在谓词中的最左原则，等值谓词列顺序优先范围谓词列。为了给索引设计合理性提供判断依据，我们设定只要满足了以上规则来设计的索引，我们就可以给这个索引评价上第一颗星。

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