暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / GreatSQL函数索引失效分析：排序规则匹配机制

GreatSQL函数索引失效分析：排序规则匹配机制

GreatSQL社区 2025-07-23
76

* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。


某项目中，客户使用SQL查询时，索引未生效，经排查发现查询使用的排序规则与函数索引的排序规则不一致，导致无法使用该函数索引。

一、排序规则不匹配的测试案例

'测试表结构如下'
greatsql> SHOW CREATE TABLE test_findex;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | CreateTable                                                                                                                                                                                                                                                                                                                                                                                    |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_findex | CREATE TABLE`test_findex` (
`id`intNOT 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 DEFAUL TCHARSET=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, 1warning (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)) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1rowinset (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, 3warnings (0.00 sec)

greatsql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                           |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3909 | Cannot use functional index'ccc' due totypeorcollation conversion.                                                                                                                                                            |
| Warning | 1739 | Cannot userangeaccessonindex'ccc' due totypeorcollation conversion onfield'!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       | CreateTable                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_findex2 | CREATE TABLE`test_findex2` (
`id`intNOT NULL AUTO_INCREMENT,
`pad`char(60) CHARACTERSET 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, 1warning (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') |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1rowinset (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, 1warning (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') |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1rowinset (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        | CreateTable                                                                                                                                                                                                                                                                                                               |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_findex3 | CREATE TABLE`test_findex3` (
`id`intNOTNULL 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 DEFAUL TCHARSET=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 inset (0.00 sec)

4. 测试结果

  • 命中索引：
    • 查询时明确指定与函数索引相同的排序规则（排序规则匹配，优化器可用该索引）
    • 查询未指定排序规则，但列的排序规则与索引匹配（优化器自动继承列排序规则并选择匹配索引）
  • 无法命中索引：
    • 查询时指定不同排序规则（排序规则不兼容，触发 Collation Conversion）
    • 查询未指定排序规则，列和索引排序规则不一致（优化器使用列排序规则，但与索引不匹配）

四、避坑建议

1. 显式指定排序规则 在创建函数索引和编写查询时，显式声明排序规则，避免隐式转换：
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';

2. 保持排序规则一致性
    1. 表列、函数索引、查询条件三者的排序规则尽量一致，避免排序规则混用；
    2. 修改列排序规则后，需重建索引（因索引依赖列定义），否则索引可能失效。
3. 根据业务模型选择兼容性强的排序规则 例如utf8mb4_0900_ai_ci
，支持更广泛的Unicode字符。

4. 监控索引失效警告 定期检查慢查询日志，关注 SHOW WARNINGS 中collation转换提示。


Enjoy GreatSQL :)
<往 期 推 荐>
误操作后快速恢复数据 binlog 解析为反向 SQL
GreatSQL优化技巧：使用 FUNCTION 代替标量子查询
JDBC游标读不生效导致OOM问题排查分析

《用三分钟学会一个MySQL知识》

<关于 GreatSQL>

GreatSQL数据库是一款开源免费数据库，可在普通硬件上满足金融级应用场景，具有高可用、高性能、高兼容、高安全等特性，可作为MySQL或Percona Server for MySQL的理想可选替换。

💻社区官网: https://greatsql.cn/ 
⏩Gitee  : https://gitee.com/GreatSQL/GreatSQL
⏩GitHub  : https://github.com/GreatSQL/

🆙BiliBili  : https://space.bilibili.com/1363850082

（对文章有疑问或见解可去社区官网提出哦~）

加入微信交流群
加入QQ交流群
想看更多技术好文，点个"在看"吧！

索引greatsql失效分析mysql排序select函数
文章转载自GreatSQL社区，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论