登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 安全 ›GreatSQL函数索引失效分析：排序规则匹配机制 ...

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

甘子萱 4 天前
GreatSQL函数索引失效分析：排序规则匹配机制

某项目中，客户使用SQL查询时，索引未生效，经排查发现查询使用的排序规则与函数索引的排序规则不一致，导致无法使用该函数索引。
一、排序规则不匹配的测试案例
  1. '测试表结构如下'
  2. greatsql> SHOW CREATE TABLE test_findex;
  3. +--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  4. | Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                    |
  5. +--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  6. | test_findex | CREATE TABLE `test_findex` (
  7.   `id` int NOT NULL AUTO_INCREMENT,
  8.   `c` char(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  9.   `pad` char(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  10.   PRIMARY KEY (`id`),
  11.   KEY `bbb` ((substr(`pad`,1,10))),
  12.   KEY `ccc` ((concat(`c`,`pad`)))
  13. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
  14. +--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  16. '按照排序规则 utf8mb4_bin 进行查询，排序规则匹配可以使用函数索引'
  17. greatsql> EXPLAIN SELECT * FROM test_findex WHERE concat(`c`,`pad`)='aaa' COLLATE utf8mb4_bin;
  18. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  19. | id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
  20. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  21. |  1 | SIMPLE      | test_findex | NULL       | ref  | ccc           | ccc  | 723     | const |    1 |   100.00 | NULL  |
  22. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  23. 1 row in set, 1 warning (0.01 sec)
  25. greatsql> SHOW WARNINGS;
  26. +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  27. | Level | Code | Message                                                                                                                                                                                                           |
  28. +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  29. | 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)) |
  30. +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  31. 1 row in set (0.00 sec)
  34. '按照排序规则 utf8mb4_0900_ai_ci 进行查询，排序规则与索引不一致，同时warnings中给出了不能使用函数索引的原因'
  35. greatsql> EXPLAIN SELECT * FROM test_findex WHERE concat(`c`,`pad`)='aaa' COLLATE utf8mb4_0900_ai_ci;
  36. +----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
  37. | id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  38. +----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
  39. |  1 | SIMPLE      | test_findex | NULL       | ALL  | ccc           | NULL | NULL    | NULL |    1 |   100.00 | Using where |
  40. +----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
  41. 1 row in set, 3 warnings (0.00 sec)
  43. greatsql> SHOW WARNINGS;
  44. +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  45. | Level   | Code | Message                                                                                                                                                                                                                           |
  46. +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  47. | Warning | 3909 | Cannot use functional index 'ccc' due to type or collation conversion.                                                                                                                                                            |
  48. | Warning | 1739 | Cannot use range access on index 'ccc' due to type or collation conversion on field '!hidden!ccc!0!0'                                                                                                                             |
  49. | 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))) |
  50. +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
复制代码
测试现象：在使用索引时，如果查询条件的排序规则和索引的排序规则不匹配（不相同或不兼容），则无法使用这个函数索引。
二、函数索引的底层存储机制

通过提取SDI信息分析发现，每个函数索引对应一个隐藏列，其collation_id决定排序规则：
  1. 测试表结构如下：
  2. greatsql> SHOW CREATE TABLE test_findex2;
  3. +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  4. | Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
  5. +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  6. | test_findex2 | CREATE TABLE `test_findex2` (
  7.   `id` int NOT NULL AUTO_INCREMENT,
  8.   `pad` char(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  9.   PRIMARY KEY (`id`),
  10.   KEY `ddd` (((substr(`pad`,1,10) collate utf8mb4_0900_ai_ci))),
  11.   KEY `fff` (((substr(`pad`,1,10) collate utf8mb4_bin)))
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
  13. +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  14. $ ./bin/ibd2sdi  /usr/local/db/dbdata/test/test_findex2.ibd
  15. ...
  16. {
  17.     "name": "!hidden!ddd!0!0",
  18.      ....
  19.     "generation_expression": "(substr(`pad`,1,10) collate utf8mb4_0900_ai_ci)",
  20.     "generation_expression_utf8": "(substr(`pad`,1,10) collate utf8mb4_0900_ai_ci)",
  21.     ....
  22.     "collation_id": 255, // utf8mb4_0900_ai_ci 通过information_schema.COLLATIONS表查询
  23.     "is_explicit_collation": false
  24. },
  25. {
  26.     "name": "!hidden!fff!0!0",
  27.     ...
  28.     "generation_expression": "(substr(`pad`,1,10) collate utf8mb4_bin)",
  29.     "generation_expression_utf8": "(substr(`pad`,1,10) collate utf8mb4_bin)",
  30.     ...
  31.     "collation_id": 46, // utf8mb4_bin
  32.     "is_explicit_collation": false
  33. },
  34. ...
复制代码
排序规则ID映射关系
  1. greatsql> SELECT * FROM information_schema.COLLATIONS WHERE ID IN(46,255);
  2. +--------------------+--------------------+-----+------------+-------------+---------+---------------+
  3. | COLLATION_NAME     | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
  4. +--------------------+--------------------+-----+------------+-------------+---------+---------------+
  5. | utf8mb4_bin        | utf8mb4            |  46 |            | Yes         |       1 | PAD SPACE     |
  6. | utf8mb4_0900_ai_ci | utf8mb4            | 255 | Yes        | Yes         |       0 | NO PAD        |
  7. +--------------------+--------------------+-----+------------+-------------+---------+---------------+
复制代码
三、排序规则匹配场景测试

1. 查询时指定了排序规则

查询时指定了排序规则,按照指定的排序规则选择索引。
  1. '索引 fff 和 ddd 都指定了排序规则，执行计划均选择了对应排序规则的索引'
  2. greatsql> EXPLAIN SELECT * FROM test_findex2 WHERE substr(`pad`,1,10) COLLATE utf8mb4_bin ='1111111111';
  3. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  4. | id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
  5. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  6. |  1 | SIMPLE      | test_findex2 | NULL       | ref  | fff           | fff  | 43      | const |    1 |   100.00 | NULL  |
  7. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  9. greatsql> EXPLAIN SELECT * FROM test_findex2 WHERE substr(`pad`,1,10) COLLATE utf8mb4_0900_ai_ci ='1111111111';
  10. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  11. | id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
  12. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  13. |  1 | SIMPLE      | test_findex2 | NULL       | ref  | ddd           | ddd  | 43      | const |    1 |   100.00 | NULL  |
  14. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
复制代码
2. 查询时未指定排序规则

查询时未指定排序规则,使用索引列排序规则对应的索引。
  1. '查询时未指定排序规则，选择了索引 fff（其排序规则是 utf8mb4_bin ）与 pad 列的排序规则相同'
  2. greatsql> EXPLAIN SELECT * FROM test_findex2 WHERE substr(`pad`,1,10)='1111111111';
  3. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  4. | id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
  5. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  6. |  1 | SIMPLE      | test_findex2 | NULL       | ref  | fff           | fff  | 43      | const |    1 |   100.00 | NULL  |
  7. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  8. 1 row in set, 1 warning (0.01 sec)
  10. greatsql> SHOW WARNINGS;
  11. +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  12. | Level | Code | Message                                                                                                                                                                                  |
  13. +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  14. | 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') |
  15. +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  16. 1 row in set (0.00 sec)
  19. '将表列 pad 的排序规则改为 utf8mb4_0900_ai_ci ，
  20. 查询时未指定排序规则，选择了索引 ddd 其排序规则是 utf8mb4_0900_ai_ci ）与 pad 列的排序规则相同'
  21. greatsql>  ALTER TABLE test_findex2 MODIFY pad char(60) COLLATE utf8mb4_0900_ai_ci;
  22. Query OK, 0 rows affected (0.12 sec)
  23. Records: 0  Duplicates: 0  Warnings: 0
  25. greatsql> EXPLAIN SELECT * FROM test_findex2 WHERE substr(`pad`,1,10)='1111111111';
  26. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  27. | id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
  28. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  29. |  1 | SIMPLE      | test_findex2 | NULL       | ref  | ddd           | ddd  | 43      | const |    1 |   100.00 | NULL  |
  30. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  31. 1 row in set, 1 warning (0.00 sec)
  33. greatsql> SHOW WARNINGS;
  34. +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  35. | Level | Code | Message                                                                                                                                                                                         |
  36. +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  37. | 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') |
  38. +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  39. 1 row in set (0.00 sec)
复制代码
当SQL 中 没有显式写出排序规则时，优化器会自动继承查询条件中字段的排序规则，并补上排序规则。
3. 建表时未指定排序规则

系统按照database的字符集和排序规则创建表。数据列按照表的排序规则创建，遵循第2条：使用索引列排序规则对应的索引。
  1. '创建表时不指定排序规则（继承数据库默认）'
  2. CREATE TABLE `test_findex3` (
  3.   `id` int NOT NULL AUTO_INCREMENT,
  4.   `pad` char(60) NOT NULL DEFAULT '',
  5.   PRIMARY KEY (`id`),
  6.   KEY `ddd` ((substr(`pad`,1,10) COLLATE utf8mb4_0900_ai_ci)),
  7.   KEY `fff` ((substr(`pad`,1,10) COLLATE utf8mb4_bin))
  8. ) ENGINE=InnoDB;
  10. '数据库默认字符集 utf8mb4 ，默认排序规则 utf8mb4_0900_ai_ci '
  11. greatsql> SHOW CREATE TABLE test_findex3;
  12. +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  13. | Table        | Create Table                                                                                                                                                                                                                                                                                                               |
  14. +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  15. | test_findex3 | CREATE TABLE `test_findex3` (
  16.   `id` int NOT NULL AUTO_INCREMENT,
  17.   `pad` char(60) NOT NULL DEFAULT '',
  18.   PRIMARY KEY (`id`),
  19.   KEY `ddd` (((substr(`pad`,1,10) collate utf8mb4_0900_ai_ci))),
  20.   KEY `fff` (((substr(`pad`,1,10) collate utf8mb4_bin)))
  21. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
  22. +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  24. '查询时未指定排序规则且列未指定排序规则，继承建表的排序规则，同第二条'
  25. greatsql> EXPLAIN SELECT * FROM test_findex3 WHERE substr(`pad`,1,10)='1111111111';
  26. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  27. | id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
  28. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  29. |  1 | SIMPLE      | test_findex3 | NULL       | ref  | ddd           | ddd  | 43      | const |    1 |   100.00 | NULL  |
  30. +----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
  31. 1 row in set, 1 warning (0.00 sec)
  33. greatsql> SHOW WARNINGS;
  34. +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  35. | Level | Code | Message                                                                                                                                                                                         |
  36. +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  37. | 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') |
  38. +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  39. 1 row in set (0.00 sec)
复制代码
4. 测试结果


  • 命中索引：

    • 查询时明确指定与函数索引相同的排序规则（排序规则匹配，优化器可用该索引）
    • 查询未指定排序规则，但列的排序规则与索引匹配（优化器自动继承列排序规则并选择匹配索引）

  • 无法命中索引：

    • 查询时指定不同排序规则（排序规则不兼容，触发 Collation Conversion）
    • 查询未指定排序规则，列和索引排序规则不一致（优化器使用列排序规则，但与索引不匹配）

四、避坑建议


  • 显式指定排序规则 在创建函数索引和编写查询时，显式声明排序规则，避免隐式转换：
  1. greatsql> CREATE INDEX idx ON test_table ((substr(col,1,10) COLLATE utf8mb4_bin));
  2. greatsql> SELECT * FROM test_table WHERE substr(col,1,10) COLLATE utf8mb4_bin = 'value';
复制代码

  • 保持排序规则一致性

    • 表列、函数索引、查询条件三者的排序规则尽量一致，避免排序规则混用；
    • 修改列排序规则后，需重建索引（因索引依赖列定义），否则索引可能失效。

  • 监控索引失效警告 定期检查慢查询日志，关注 SHOW WARNINGS 中collation转换提示。
  • 根据业务模型选择兼容性强的排序规则 例如 utf8mb4_0900_ai_ci，支持更广泛的Unicode字符。

Enjoy GreatSQL
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

使用道具 举报
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
甘子萱
4 天前

0

粉丝关注

15

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫