GreatSQL 函数索引失效分析：排序规则匹配机制
作者：GreatSQL
- 2025-07-23 福建
本文字数：10863 字
阅读完需：约 36 分钟
GreatSQL 函数索引失效分析：排序规则匹配机制
某项目中，客户使用 SQL 查询时，索引未生效，经排查发现查询使用的排序规则与函数索引的排序规则不一致，导致无法使用该函数索引。
一、排序规则不匹配的测试案例
'测试表结构如下'greatsql> SHOW CREATE TABLE test_findex;+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| test_findex | CREATE TABLE `test_findex` ( `id` int NOT NULL AUTO_INCREMENT, `c` char(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', `pad` char(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `bbb` ((substr(`pad`,1,10))), KEY `ccc` ((concat(`c`,`pad`)))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
'按照排序规则 utf8mb4_bin 进行查询，排序规则匹配可以使用函数索引'greatsql> EXPLAIN SELECT * FROM test_findex WHERE concat(`c`,`pad`)='aaa' COLLATE utf8mb4_bin;+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+| 1 | SIMPLE | test_findex | NULL | ref | ccc | ccc | 723 | const | 1 | 100.00 | NULL |+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.01 sec)
greatsql> SHOW WARNINGS;+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1003 | /* select#1 */ select `test`.`test_findex`.`id` AS `id`,`test`.`test_findex`.`c` AS `c`,`test`.`test_findex`.`pad` AS `pad` from `test`.`test_findex` where (concat(`c`,`pad`) = ('aaa' collate utf8mb4_bin)) |+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
'按照排序规则 utf8mb4_0900_ai_ci 进行查询，排序规则与索引不一致，同时warnings中给出了不能使用函数索引的原因'greatsql> EXPLAIN SELECT * FROM test_findex WHERE concat(`c`,`pad`)='aaa' COLLATE utf8mb4_0900_ai_ci;+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | test_findex | NULL | ALL | ccc | NULL | NULL | NULL | 1 | 100.00 | Using where |+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 3 warnings (0.00 sec)
greatsql> SHOW WARNINGS;+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Warning | 3909 | Cannot use functional index 'ccc' due to type or collation conversion. || Warning | 1739 | Cannot use range access on index 'ccc' due to type or collation conversion on field '!hidden!ccc!0!0' || Note | 1003 | /* select#1 */ select `test`.`test_findex`.`id` AS `id`,`test`.`test_findex`.`c` AS `c`,`test`.`test_findex`.`pad` AS `pad` from `test`.`test_findex` where (concat(`c`,`pad`) = <cache>(('aaa' collate utf8mb4_0900_ai_ci))) |+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
复制代码
测试现象：在使用索引时，如果查询条件的排序规则和索引的排序规则不匹配（不相同或不兼容），则无法使用这个函数索引。
二、函数索引的底层存储机制
通过提取 SDI 信息分析发现，每个函数索引对应一个隐藏列，其collation_id决定排序规则：
测试表结构如下：greatsql> SHOW CREATE TABLE test_findex2;+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| test_findex2 | CREATE TABLE `test_findex2` ( `id` int NOT NULL AUTO_INCREMENT, `pad` char(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `ddd` (((substr(`pad`,1,10) collate utf8mb4_0900_ai_ci))), KEY `fff` (((substr(`pad`,1,10) collate utf8mb4_bin)))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+$ ./bin/ibd2sdi /usr/local/db/dbdata/test/test_findex2.ibd ...{ "name": "!hidden!ddd!0!0", .... "generation_expression": "(substr(`pad`,1,10) collate utf8mb4_0900_ai_ci)", "generation_expression_utf8": "(substr(`pad`,1,10) collate utf8mb4_0900_ai_ci)", .... "collation_id": 255, // utf8mb4_0900_ai_ci 通过information_schema.COLLATIONS表查询 "is_explicit_collation": false},{ "name": "!hidden!fff!0!0", ... "generation_expression": "(substr(`pad`,1,10) collate utf8mb4_bin)", "generation_expression_utf8": "(substr(`pad`,1,10) collate utf8mb4_bin)", ... "collation_id": 46, // utf8mb4_bin "is_explicit_collation": false},...
复制代码
排序规则 ID 映射关系
greatsql> SELECT * FROM information_schema.COLLATIONS WHERE ID IN(46,255);+--------------------+--------------------+-----+------------+-------------+---------+---------------+| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |+--------------------+--------------------+-----+------------+-------------+---------+---------------+| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE || utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD |+--------------------+--------------------+-----+------------+-------------+---------+---------------+
复制代码
三、排序规则匹配场景测试
1. 查询时指定了排序规则
查询时指定了排序规则,按照指定的排序规则选择索引。
'索引 fff 和 ddd 都指定了排序规则，执行计划均选择了对应排序规则的索引'greatsql> EXPLAIN SELECT * FROM test_findex2 WHERE substr(`pad`,1,10) COLLATE utf8mb4_bin ='1111111111';+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+| 1 | SIMPLE | test_findex2 | NULL | ref | fff | fff | 43 | const | 1 | 100.00 | NULL |+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
greatsql> EXPLAIN SELECT * FROM test_findex2 WHERE substr(`pad`,1,10) COLLATE utf8mb4_0900_ai_ci ='1111111111';+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+| 1 | SIMPLE | test_findex2 | NULL | ref | ddd | ddd | 43 | const | 1 | 100.00 | NULL |+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
复制代码
2. 查询时未指定排序规则
查询时未指定排序规则,使用索引列排序规则对应的索引。
'查询时未指定排序规则，选择了索引 fff（其排序规则是 utf8mb4_bin ）与 pad 列的排序规则相同'greatsql> EXPLAIN SELECT * FROM test_findex2 WHERE substr(`pad`,1,10)='1111111111';+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+| 1 | SIMPLE | test_findex2 | NULL | ref | fff | fff | 43 | const | 1 | 100.00 | NULL |+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.01 sec)
greatsql> SHOW WARNINGS;+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1003 | /* select#1 */ select `test`.`test_findex2`.`id` AS `id`,`test`.`test_findex2`.`pad` AS `pad` from `test`.`test_findex2` where ((substr(`pad`,1,10) collate utf8mb4_bin) = '1111111111') |+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
'将表列 pad 的排序规则改为 utf8mb4_0900_ai_ci ，查询时未指定排序规则，选择了索引 ddd 其排序规则是 utf8mb4_0900_ai_ci ）与 pad 列的排序规则相同'greatsql> ALTER TABLE test_findex2 MODIFY pad char(60) COLLATE utf8mb4_0900_ai_ci;Query OK, 0 rows affected (0.12 sec)Records: 0 Duplicates: 0 Warnings: 0
greatsql> EXPLAIN SELECT * FROM test_findex2 WHERE substr(`pad`,1,10)='1111111111';+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+| 1 | SIMPLE | test_findex2 | NULL | ref | ddd | ddd | 43 | const | 1 | 100.00 | NULL |+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)
greatsql> SHOW WARNINGS;+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1003 | /* select#1 */ select `test`.`test_findex2`.`id` AS `id`,`test`.`test_findex2`.`pad` AS `pad` from `test`.`test_findex2` where ((substr(`pad`,1,10) collate utf8mb4_0900_ai_ci) = '1111111111') |+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码
当 SQL 中 没有显式写出排序规则时，优化器会自动继承查询条件中字段的排序规则，并补上排序规则。
3. 建表时未指定排序规则
系统按照 database 的字符集和排序规则创建表。数据列按照表的排序规则创建，遵循第 2 条：使用索引列排序规则对应的索引。
'创建表时不指定排序规则（继承数据库默认）'CREATE TABLE `test_findex3` ( `id` int NOT NULL AUTO_INCREMENT, `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `ddd` ((substr(`pad`,1,10) COLLATE utf8mb4_0900_ai_ci)), KEY `fff` ((substr(`pad`,1,10) COLLATE utf8mb4_bin))) ENGINE=InnoDB;
'数据库默认字符集 utf8mb4 ，默认排序规则 utf8mb4_0900_ai_ci 'greatsql> SHOW CREATE TABLE test_findex3;+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| test_findex3 | CREATE TABLE `test_findex3` ( `id` int NOT NULL AUTO_INCREMENT, `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `ddd` (((substr(`pad`,1,10) collate utf8mb4_0900_ai_ci))), KEY `fff` (((substr(`pad`,1,10) collate utf8mb4_bin)))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
'查询时未指定排序规则且列未指定排序规则，继承建表的排序规则，同第二条'greatsql> EXPLAIN SELECT * FROM test_findex3 WHERE substr(`pad`,1,10)='1111111111';+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+| 1 | SIMPLE | test_findex3 | NULL | ref | ddd | ddd | 43 | const | 1 | 100.00 | NULL |+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)
greatsql> SHOW WARNINGS;+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1003 | /* select#1 */ select `test`.`test_findex3`.`id` AS `id`,`test`.`test_findex3`.`pad` AS `pad` from `test`.`test_findex3` where ((substr(`pad`,1,10) collate utf8mb4_0900_ai_ci) = '1111111111') |+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码
4. 测试结果
命中索引：
查询时明确指定与函数索引相同的排序规则（排序规则匹配，优化器可用该索引）
查询未指定排序规则，但列的排序规则与索引匹配（优化器自动继承列排序规则并选择匹配索引）
无法命中索引：
查询时指定不同排序规则（排序规则不兼容，触发 Collation Conversion）
查询未指定排序规则，列和索引排序规则不一致（优化器使用列排序规则，但与索引不匹配）
四、避坑建议
显式指定排序规则 在创建函数索引和编写查询时，显式声明排序规则，避免隐式转换：
greatsql> CREATE INDEX idx ON test_table ((substr(col,1,10) COLLATE utf8mb4_bin));greatsql> SELECT * FROM test_table WHERE substr(col,1,10) COLLATE utf8mb4_bin = 'value';
复制代码
保持排序规则一致性
表列、函数索引、查询条件三者的排序规则尽量一致，避免排序规则混用；
修改列排序规则后，需重建索引（因索引依赖列定义），否则索引可能失效。
监控索引失效警告 定期检查慢查询日志，关注
SHOW WARNINGS中 collation 转换提示。根据业务模型选择兼容性强的排序规则 例如
utf8mb4_0900_ai_ci，支持更广泛的 Unicode 字符。
划线
评论
复制
发布于: 刚刚阅读数: 3
版权声明: 本文为 InfoQ 作者【GreatSQL】的原创文章。
原文链接:【http://xie.infoq.cn/article/a719974fa7a558e43459964c0】。文章转载请联系作者。
GreatSQL
关注
GreatSQL社区 2023-01-31 加入
GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。 社区：https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL
评论