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

2025-07-23 201

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';

保持排序规则一致性
1. 表列、函数索引、查询条件三者的排序规则尽量一致，避免排序规则混用；
2. 修改列排序规则后，需重建索引（因索引依赖列定义），否则索引可能失效。
监控索引失效警告定期检查慢查询日志，关注 SHOW WARNINGS 中collation转换提示。
根据业务模型选择兼容性强的排序规则例如 utf8mb4_0900_ai_ci，支持更广泛的Unicode字符。

Enjoy GreatSQL :)

关于 GreatSQL

GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。

相关链接： GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区：

社区有奖建议反馈： https://greatsql.cn/thread-54-1-1.html

社区博客有奖征稿详情： https://greatsql.cn/thread-100-1-1.html

（对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~）

技术交流群：

微信&QQ群：

QQ群：533341697

微信群：添加GreatSQL社区助手（微信号：wanlidbc ）好友，待社区助手拉您进群。

微信关注我们

原文链接：https://my.oschina.net/GreatSQL/blog/18685559

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

AiPy，能踏实当我的 AI 打工牛马吗？

最近是不是感觉AI智能体（Agent）层出不穷？个个都说能帮你干活，解放双手。但试了一圈，有的像“玩具”，中看不中用；有的像个“黑盒子”，搞不清它在干嘛，数据安全更是让人心里打鼓；还有的门槛高得吓人，仿佛得先考个程序员证才能用... 所以，我也一直在寻找那个真正能“扛活”、让人放心的AI伙伴。最近发现了 AiPy，它会不一样吗，能踏实当我的 AI 打工牛马吗？ 8月1日（周五）晚8点，开源中国《技术领航》直播栏目，邀请知道创宇 AI 业务部总经理王利伟，来一场关于开源智能体 AiPy 的深度直播。为什么说 AiPy 值得特别关注？因为它戳中了我对靠谱AI助手的很多期待：无需懂复杂代码，用自然语言告诉AiPy你的需求。比如分析合同、处理数据、生成图表？说出来就好。AiPy能理解你的意图，并自动写出可运行的Python程序。技术小白也能轻松上手。 AiPy的核心优势就是本地化部署。你的文件、你的数据，就在你自己的机器上处理。很多人担心的商业机密、敏感数据上传云端的问题都不存在了。而且，AiPy不只是给你一个最终结果。它会自动交付完整的、可运行的Python代码和成果。过程透明可见，...

2025-07-22

139

大家好，我是陈哥。昨天刚看完朱少民老师的《软件工程3.0》，书中提到：现在已经是大模型驱动的人机协同范式，我们已经进入了软件工程3.0时代。这不仅意味着技术的跃迁，更是观念与方法的深刻变革。即便是在软件质量保证（SQA）领域，AI也以锐不可当之势渗透：测试脚本实现自我修复；机器学习模型精准预测易出Bug的代码区块；自动化机器人一夜之间便可吞吐数千个测试用例； …… 一个核心问题浮出水面：AI会取代测试人员吗？不会。因为AI没有让测试人员冗余，只是重新定义了他们的角色。如果您也想在测试团队落地“AI+测试”，可备注【AI测试】了解咨询服务。一、人工智能在软件测试中的崛起过去数年，AI工具已显著提升了测试的速度、覆盖范围与整体效率。举个例子，AI 算法可通过分析历史Bug模式，精准锁定应用中最可能失效的区域，将测试资源集中于这些高风险“热点”，实现潜在问题的提前拦截，在开发周期早期便筑牢软件质量的防线。此外，AI工具还攻克了测试维护的难题：与手动脚本不同，部分AI工具能自动适配微小的UI变化。借助AI在应用界面变动时动态更新测试步骤，大幅减少了代码微调后重写测试脚本...

2025-07-23

103

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。