GreatSQL函數索引失效分析：排序規則匹配機制
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字符。
Enjoy GreatSQL ??
關于 GreatSQL
GreatSQL是適用于金融級應用的國內自主開源數據庫，具備高性能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用于線上生產環境，且完全免費并兼容MySQL或Percona Server。
相關鏈接： GreatSQL社區 Gitee GitHub Bilibili
GreatSQL社區：
社區博客有獎征稿詳情：https://greatsql.cn/thread-100-1-1.html
技術交流群：
微信：掃碼添加
GreatSQL社區助手微信好友，發送驗證信息加群。
浙公網安備 33010602011771號