QianBase MPP数据库中的索引问题

为了提高数据库中SQL的执行效率，我们最常用到的手段之一就是创建合适的索引。但是在生产环境中，经常会遇到索引失效的问题，索引失效也是一个老生常谈的话题，下面我们梳理一下QianBase MPP中的索引失效，从而避免索引失效引起的SQL执行效率问题。

按照大的分类，索引失效可以分为两类：

1.建立的索引无法使用；

2.优化器不选择走索引。

一

索引无法使用

索引无法使用的情况主要包括：索引类型不匹配、collate不一致、数据类型不一致、索引不支持等；

索引类型不匹配

1、过滤条件带有函数

过滤条件带有函数会导致索引类型不匹配，如下：

test表上id、info上都包含索引，如果过滤条件带有函数，则索引无法使用，如下：

两个查询中，在第二个查询带有函数，因此索引无法使用。当然，如果建立一个函数索引，则可以使用索引，但函数必须是immutable的。下面简单介绍一下函数的三态，QianBase MPP函数有三个状态：IMMUTABLE、STABLE、VOLATILE。

IMMUTABLE：函数不能修改数据库并且确保用相同的参数永远返回相同的结果；

STABLE：函数不能修改数据库并且确保对一个语句中的所有行用给定的相同参数返回相同的结果；

VOLATILE：函数可以做任何事情，包括修改数据库。在使用相同的参数连续调用时，它能返回不同的结果。

创建函数索引后，查询可以使用函数索引，如下：

2、过滤条件位于右侧

过滤条件必须位于比较符右侧，否则也会导致索引失效，如下：

虽然这三条SQL的逻辑和结果集一模一样，但是第三条SQL就无法进行索引，而第二条SQL优化器则可以自动转化。

collate不一致

在QianBase MPP数据库中有一系列本地化的参数locale，locale与encoding的默认配置取决于操作系统的配置，本地化设置对以下SQL特性有影响:

1）排序和比较操作

2）内置函数

3）模式匹配

4）to_char相关函数

5）LIKE能否使用索引

collate是比较容易被忽略的一点，不同的collate会影响到order by语句的顺序，会影响到where条件中大于小于号筛选出来的结果等，如下，encoding是UTF8，collate是zh_CN.UTF-8：

不同的collate会导致同样的字符比较发生改变。

再看一个模糊查询的例子，如下：

因为locale关于字符串的等价规则有一套自己的定义，因此只有数据库自身提供的C LOCALE，才能够正常地进行模式匹配。C LOCALE的比较规则非常简单，就是挨个比较字符码位。

数据类型不一致

数据类型不一致指的是column列类型和过滤条件不一致，如下：

在第一个查询中由于条件两边字段类型不一致，导致无法使用索引，在第二个查询中字段类型一致，可以使用索引。

索引不支持

每种索引有自己特定的场景，也有不支持的操作，QianBase MPP 6.0版本包含gist、hash、gin、btree、spgist、bitmap 6种类型索引，我们可以通过系统表来查询判断不同索引所支持的操作，如下：

通过以上查询，可以知道不同索引所能够支持的操作。

二优化器不选择走索引

当优化器认为走索引还不如走顺序扫描快时，会导致索引不被使用。索引扫描对应的是离散IO，当然，我们可以通过调整random_page_cost参数以告诉优化器，随机IO和顺序IO的比值。优化器不选择走索引的情况主要包括：表行数太少、关联度、结果集过大、最左原则、不等操作符、limit使用、统计信息失真等。

表行数太少

当表行数过少时，优化器选择不走索引，如下：

查询2中走索引的总成本20.18要比顺序扫描1.42高14倍左右，因此优化器选择顺序扫描。

关联度

在一个SQL查询中，高效的索引扫描，只需扫描几次就能获取到所需数据，而糟糕的索引扫描则需要返回大量数据，需要多次扫描，每次都要经历树根 → 树干 → 树枝 → 树叶，从而导致大量的离散IO，因此优化器也会将这个关联度考虑进去。

好的顺序扫描数据更加紧凑，都集中在一块，这样磁头扫过去，只需扫描有限个数据块，就可以获取到大部分所需数据。而糟糕的顺序扫描数据相对离散，需要扫描大量数据并过滤才能获取到想要的数据，这个也就是统计信息里面的correlation字段的作用，表示列的物理顺序和逻辑顺序的相关性，相关性越高，走索引扫描的离散块扫描更少，走索引扫描的离散块扫描代价越低。例子如下：

t3表上有3个索引，其中：

1）col_asc索引是顺序递增的

2）col_desc索引是倒序递减的

3）col_rand索引则是随机分布的

因此假如SQL查询如下：

select * from t3 where c2_asc between 2 and 4;

则对于col_asc，只需要读第一个页面即可

而假如是对随机列进行查询的话：

select * from t3 where c2_asc between 2 and 4;

则需要读取所有的页面

因此在索引扫描的时候也会将correlation考虑进去。

返回的数据集过大

当返回集过大时，索引会对应大量的离散IO，也会导致索引失效，如下：

最左原则

当创建复合索引的时候，遵循最左优先，以最左边的为起点任何连续的索引都能匹配上，否则匹配不上，如下：

当我们禁用顺序扫描的时候，也会走索引扫描，但是成本要高得多，如下：

由上图可以看出，不遵循最左原则的时候强制性的走索引扫描，成本要多消耗5倍多。

不等操作符

在查询条件中存在不等于操作符如：<>、!=等不走索引，如下：

limit使用

当使用limit关键字时，也会导致索引失效，如下：

当优化器认为所查询列在表中有足够多的随机值时，就会发生这种情况，因为优化器认为从数据库中获取所需数量的连续块的总成本将小于先获取索引块然后再获取相应的数据块的成本，优化器认为数据分布均匀，只需扫描有限数据即可获得所需数据，中止继续扫描，但是一旦数据分布不均，就需要扫描大量的数据。

统计信息

统计信息更新不及时也会导致优化器产生误判，甚至没有统计信息，比如修改字段长度、类型后，没有做analyze手动收集统计信息，就会导致优化器错误的选择。

三总结

对于一条SQL执行，影响因素很多，优化器判断能否走索引是需要经过多方面权衡和比较的，当出现索引失效的情况还需要具体问题具体分析。

党的二十大报告指出，教育、科技、人才是全面建设社会主义现代化国家的基础性、战略性支撑。必须坚持科技是第一生产力、人才是第一资源、创新是第一动力。加快建设网络强国和数字中国。贵州易鲸捷信息技术有限公司连日来深入学习党的二十大精神，将其贯彻至具体生产工作中，凝心聚力攻克科技技术难关，为我党实现第二个百年奋斗目标奋勇前进。

END

▼

往期精彩回顾

▼

易鲸捷简介

易鲸捷公司成立于2015年，专注于新一代融合型分布式数据库核心技术研发。公司核心团队源自天腾公司，曾创造过NonStopSQL等全球领先的数据库产品，核心技术完全自主可控。经过多年技术沉淀，易鲸捷已形成自主可控、国产可信、安全高效的三条完整分布式数据库产品线：QianBase xTP/QianBase TP/QianBase MPP，可面向不同行业应用提供完整的一站式解决方案，在金融、运营商、智能制造、5G等重点行业获得广泛应用。

网址：www.esgyn.cn

贵州易鲸捷信息技术有限公司 地址：贵阳市高新区长岭南路160号高科1号C座24楼
北京易鲸捷信息技术有限公司 地址：北京市朝阳区大屯街道北苑路万科时代中心奥林A座10层
上海易鲸捷信息技术有限公司 地址：上海市浦东新区金科路2889弄1号长泰广场A座6层03单元
北京：010-84983409	上海：021-50822117
邮箱：[e-mail]	网址：www.esgyn.cn