数据类型相关问题导致的索引失效 | OceanBase SQL 优化实践

背景

针对在OceanBase 论坛中遇到的一些典型SQL调优问题，进行记录与总结，分享给大家。本文介绍的事3个场景：数据类型不匹配、字符集相关属性不匹配，和过滤/联接条件上包含系统函数。

场景一：数据类型不匹配

类型不匹配包括两方面：

• 数据类型不匹配：比如 int vs varchar 等。
• 数据精度（包含 precision、scale 等数据类型的附属属性）不匹配：比如 char(100) vs char(1)、decimal(5, 2) vs decimal(3, 1) 等。

接下来看一个数据类型不匹配的简单例子：

create table t1(c1 varchar(10) primary key);insert into t1 values('1'), ('01.0'), ('+1.0');select * from t1 where c1 = 1;
+------+
| c1   |
+------+
| +1.0 |
| 01.0 |
| 1    |
+------+-- sql 1
obclient> explain select * from t1 where c1 = 1;
+---------------------------------------------------------------------------------------------------+
| Query Plan                                                                                        |
+---------------------------------------------------------------------------------------------------+
| ===============================================                                                   |
| |ID|OPERATOR       |NAME|EST.ROWS|EST.TIME(us)|                                                   |
| -----------------------------------------------                                                   |
| |0 |TABLE FULL SCAN|t1  |1       |4           |                                                   |
| ===============================================                                                   |
| Outputs & filters:                                                                                |
| -------------------------------------                                                             |
|   0 - output([t1.c1]), filter([cast(t1.c1, DECIMAL(-1, -1)) = cast(1, DECIMAL(1, 0))]), rowset=16 |
|       access([t1.c1]), partitions(p0)                                                             |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                 |
|       range_key([t1.c1]), range(MIN ; MAX)always true                                             |
+---------------------------------------------------------------------------------------------------+
11 rows in set (0.04 sec)

在上面的例子中，sql 1 的计划显示进行了全表扫描，没有使用索引：

• 查询计划中的 range_key 为 t1.c1，由于 c1 是字符串类型，而 1 是整数类型，因此进行了隐性类型转换（Implicit cast）。
• 转执行程中，系统会隐式地把 varchar 类型的 '+1.0' 被转换为 int 类型的 1，无法利用建在 varchar 上的索引进行 int 类型的定位。
• 这个隐式类型转换方向是由 SQL 标准制定的，标准 SQL 定义的转换方向大致是：字符串类型 -> 数字类型 -> 时间类型。

作为对比，下面 sql 2 的计划中，通过显式指定类型转换，从而可以达到利用索引进行TABLE GET的目的。（注意：这样改写 SQL 之后，和上面的 SQL 是不等价的，需要关注是否是业务可以接受的！）

-- sql 2
obclient> explain select * from t1 where c1 = cast(1 as char);
+----------------------------------------------------+
| Query Plan                                         |
+----------------------------------------------------+
| =========================================          |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)|          |
| -----------------------------------------          |
| |0 |TABLE GET|t1  |1       |3           |          |
| =========================================          |
| Outputs & filters:                                 |
| -------------------------------------              |
|   0 - output([t1.c1]), filter(nil), rowset=16      |
|       access([t1.c1]), partitions(p0)              |
|       is_index_back=false, is_global_index=false,  |
|       range_key([t1.c1]), range[1 ; 1],            |
|       range_cond([t1.c1 = cast(1, CHAR(1048576))]) |
+----------------------------------------------------+

为了方便大家理解，我们再反着来一遍，创建一个整数类型的列 c1，并尝试使用字符 '+1.0' 来查询。

create table t1(c1 int primary key);obclient> explain select * from t1 where c1 = '+1.0';
+--------------------------------------------------------------------------------+
| Query Plan                                                                     |
+--------------------------------------------------------------------------------+
| =========================================                                      |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)|                                      |
| -----------------------------------------                                      |
| |0 |TABLE GET|t1  |1       |5           |                                      |
| =========================================                                      |
| Outputs & filters:                                                             |
| -------------------------------------                                          |
|   0 - output([t1.c1]), filter(nil), rowset=16                                  |
|       access([t1.c1]), partitions(p0)                                          |
|       is_index_back=false, is_global_index=false,                              |
|       range_key([t1.c1]), range[1 ; 1],                                        |
|       range_cond([cast(t1.c1, DECIMAL(11, 0)) = cast('+1.0', DECIMAL(1, -1))]) |
+--------------------------------------------------------------------------------+
12 rows in set (0.04 sec)

在上面这个例子中，尽管我们使用了字符 '+1.0' 来查询整数类型的列 c1，查询计划仍然使用了索引扫描。这是因为索引建在整型列上，隐式类型转换会将字符 '+1.0' 转换为整数 1，转换之后正好可以利用到建在整数类型上的索引。

场景二：字符集相关属性不匹配

charset 或者 collation 不同，都会导致无法利用索引。

请大家直接参考上一篇博客《collation 导致的索引失效》，内容十分详实，所以这里不再赘述了。

场景三：过滤/联接条件上包含系统函数

创建如下的表和索引，索引建在 date 类型列上。

CREATE TABLE employees (employee_id INT PRIMARY KEY,hire_date DATE
);CREATE INDEX idx_hire_date ON employees(hire_date);

执行 SQL 时，如果在过滤条件中的 hire_dater 列的外层加一个 year 函数，就无法走上索引了。这个很好理解，索引建在 date 类型列上，但是过了条件两边，一个是 year，一个是 int，都不是 date 类型，走不上索引也是理所应当。

explain SELECT * FROM employees WHERE YEAR(hire_date) = 2023;
+---------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                          |
+---------------------------------------------------------------------------------------------------------------------+
| ====================================================                                                                |
| |ID|OPERATOR       |NAME     |EST.ROWS|EST.TIME(us)|                                                                |
| ----------------------------------------------------                                                                |
| |0 |TABLE FULL SCAN|employees|1       |4           |                                                                |
| ====================================================                                                                |
| Outputs & filters:                                                                                                  |
| -------------------------------------                                                                               |
|   0 - output([employees.employee_id], [employees.hire_date]), filter([year(employees.hire_date) = 2023]), rowset=16 |
|       access([employees.employee_id], [employees.hire_date]), partitions(p0)                                        |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                   |
|       range_key([employees.employee_id]), range(MIN ; MAX)always true                                               |
+---------------------------------------------------------------------------------------------------------------------+
11 rows in set (0.005 sec)

这个时候，有一些用户会尝试用 hint 强制让 SQL 走索引，不过类型不匹配，索引无能为力，最终依然走不上索引。

explain basic SELECT /* index(employees idx_hire_date) */ * FROM employees WHERE year(hire_date) = 2023;
+---------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                          |
+---------------------------------------------------------------------------------------------------------------------+
| ==============================                                                                                      |
| |ID|OPERATOR       |NAME     |                                                                                      |
| ------------------------------                                                                                      |
| |0 |TABLE FULL SCAN|employees|                                                                                      |
| ==============================                                                                                      |
| Outputs & filters:                                                                                                  |
| -------------------------------------                                                                               |
|   0 - output([employees.employee_id], [employees.hire_date]), filter([year(employees.hire_date) = 2023]), rowset=16 |
|       access([employees.employee_id], [employees.hire_date]), partitions(p0)                                        |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                   |
|       range_key([employees.employee_id]), range(MIN ; MAX)always true                                               |
+---------------------------------------------------------------------------------------------------------------------+
11 rows in set (0.05 sec)

这种场景，最简单的等价 SQL 改写方法，就是让过滤条件中出现索引列的 date 类型，例如：

explain SELECT * FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| ====================================================================                                                                                      |
| |ID|OPERATOR        |NAME                    |EST.ROWS|EST.TIME(us)|                                                                                      |
| --------------------------------------------------------------------                                                                                      |
| |0 |TABLE RANGE SCAN|employees(idx_hire_date)|1       |4           |                                                                                      |
| ====================================================================                                                                                      |
| Outputs & filters:                                                                                                                                        |
| -------------------------------------                                                                                                                     |
|   0 - output([employees.employee_id], [employees.hire_date]), filter(nil), rowset=16                                                                      |
|       access([employees.employee_id], [employees.hire_date]), partitions(p0)                                                                              |
|       is_index_back=false, is_global_index=false,                                                                                                         |
|       range_key([employees.hire_date], [employees.employee_id]), range(2023-01-01,MIN ; 2023-12-31,MAX),                                                  |
|       range_cond([cast(employees.hire_date, DATETIME(-1, -1)) >= INTERNAL_FUNCTION('2023-01-01', 114, 17)], [cast(employees.hire_date, DATETIME(-1, -1))  |
|       <= INTERNAL_FUNCTION('2023-12-31', 112, 17)])                                                                                                       |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.003 sec)

最后再多说一句，如果在过滤/联接条件的列上，加了计算结果类型和索引列类型一样的系统函数，也会导致走不上索引。例如：

create table t1(c1 int, index idx(c1));-- 走上索引了
obclient [test]> explain select * from t1 where c1 = 1;
+-----------------------------------------------------------------------+
| Query Plan                                                            |
+-----------------------------------------------------------------------+
| ===================================================                   |
| |ID|OPERATOR        |NAME   |EST.ROWS|EST.TIME(us)|                   |
| ---------------------------------------------------                   |
| |0 |TABLE RANGE SCAN|t1(idx)|1       |4           |                   |
| ===================================================                   |
| Outputs & filters:                                                    |
| -------------------------------------                                 |
|   0 - output([t1.c1]), filter(nil), rowset=16                         |
|       access([t1.c1]), partitions(p0)                                 |
|       is_index_back=false, is_global_index=false,                     |
|       range_key([t1.c1], [t1.__pk_increment]), range(1,MIN ; 1,MAX),  |
|       range_cond([t1.c1 = 1])                                         |
+-----------------------------------------------------------------------+
12 rows in set (0.013 sec)-- 过滤条件在列上加了个 add 函数，就走不上索引
explain select * from t1 where c1 + 1 = 1;
+------------------------------------------------------------------------------------+
| Query Plan                                                                         |
+------------------------------------------------------------------------------------+
| ===============================================                                    |
| |ID|OPERATOR       |NAME|EST.ROWS|EST.TIME(us)|                                    |
| -----------------------------------------------                                    |
| |0 |TABLE FULL SCAN|t1  |1       |4           |                                    |
| ===============================================                                    |
| Outputs & filters:                                                                 |
| -------------------------------------                                              |
|   0 - output([t1.c1]), filter([t1.c1 + 1 = 1]), rowset=16                          |
|       access([t1.c1]), partitions(p0)                                              |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],  |
|       range_key([t1.__pk_increment]), range(MIN ; MAX)always true                  |
+------------------------------------------------------------------------------------+
11 rows in set (0.002 sec)

原因是优化器在抽 query range 的时候，range_key 上不能有函数。不然每一行在函数作用下的结果可能都是不连续的，就构不成 range 了。

总结

1. 当 SQL 走不上索引时，需要注意索引条件上是否存在隐式 cast，并考虑能否通过显式指定 cast 或其他 SQL 改写的方式解决该问题。
2. 尽量保证索引条件上 column 属性和索引列完全一致，包括数据类型、字符集属性（charset 和 collation）、精度（precision 和 scale）等。
3. 尽量不要在过滤条件和联接条件里，对希望能走索引的列上加系统函数。可以考虑通过改写 SQL 解决该问题。

补充

针对性能调优的各种场景，在OceanBas社区中建立了一个《OceanBase 性能调优》博客专题，欢迎大家积极留言评论，提出您的问题和需求。