函数索引触发的一个有趣的问题
导引
听同事提到一个有意思的事情，说在使用 GreatSQL 时，在 navicat 客户端和 GreatSQL 命令行客户端创建的函数索引不能共用，navicat 客户端创建的函数索引，在 navicat 上执行 SQL 时可以使用，在 GreatSQL 命令行执行相同的 SQL 却用不上，反之，在 GreatSQL 命令行创建的函数索引，在 navicat 客户端无法使用，这究竟是怎么回事呢？
问题回放
创建测试表与测试数据
CREATE TABLE t1(id INT PRIMARY KEY AUTO_INCREMENT,c1 INT,c2 VARCHAR(100));
INSERT INTO t1(c1,c2) VALUES(20241209120000,'20241209120000');
复制代码
查询表结构信息
greatsql> SHOW CREATE TABLE t1\G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `id` int NOT NULL AUTO_INCREMENT, `c1` bigint DEFAULT NULL, `c2` varchar(100) COLLATE utf8mb4_0900_bin DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin1 row in set (0.00 sec)
复制代码
navicat 客户端增加 c1 列的函数索引 idx_c1。
greatsql> ALTER TABLE t1 ADD INDEX idx_c1((SUBSTR(c1,1,8)));
复制代码
navicat 客户端执行如下 SQL，发现可以使用 idx_c1 索引。
greatsql> EXPLAIN SELECT * FROM t1 WHERE SUBSTR(c1,1,8) ='20241209';+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+| 1 | SIMPLE | t1 | NULL | ref | idx_c1 | idx_c1 | 35 | const | 1 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+1 row in set (0.03 sec)
复制代码
GreatSQL 命令行执行此 SQL，发现索引 idx_c1 没有用上。
greatsql> EXPLAIN SELECT * FROM t1 WHERE SUBSTR(c1,1,8) ='20241209';+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
复制代码
设置此索引不可见，在命令行创建索引 idx_c1_1。
greatsql> ALTER TABLE t1 ALTER INDEX idx_c1 INVISIBLE;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
greatsql> ALTER TABLE t1 ADD INDEX idx_c1_1 ((SUBSTR(c1,1,8)));Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0
复制代码
navicat 执行 SQL，发现不可以使用索引 idx_c1_1;
greatsql> EXPLAIN SELECT * FROM t1 WHERE SUBSTR(c1,1,8) ='20241209';+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set (0.26 sec)
复制代码
GreatSQL 命令行执行 SQL，发现可以使用索引 idx_c1_1;
greatsql> EXPLAIN SELECT * FROM t1 WHERE SUBSTR(c1,1,8) ='20241209';+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| 1 | SIMPLE | t1 | NULL | ref | idx_c1_1 | idx_c1_1 | 35 | const | 1 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)
复制代码
问题分析
什么情况下会导致索引失效呢？
我们很容易会想到以下几个可能，索引列上施加了函数或表达式计算，索引列上发生隐式转换，索引列跟比较值的 character set 不一致或 collation 不一致。
那么哪一种可能会引发上面的问题呢？
不知小伙伴们是否注意到一个细节，例子中 t1 表的 c1 列原本为 bigint 类型，取前 8 位数字创建函数索引，substr(c1,1,8)，substr 函数的返回值是 varchar 类型，上面例子中创建的函数索引其实是对字符数据 substr(c1,1,8) 进行排序创建的，既然是 varchar 类型，就会涉及 character set 与 collation 的问题。一般来讲，对列施加字符函数，返回值应该与列的字符集和校验规则一致（本例中的 c2 列原本为 varchar 类型，对其增加函数索引，两种客户端都可以使用，读者可自行测试验证），但是问题在于本例中的 c1 列为 bigint 类型，不涉及字符集和校验规则。
那么对整型列上施加字符函数 substr 而创建的函数索引，它的 character set 和 collation 是依据什么呢？
针对上面的问题，很容易联想到很可能是因为两种客户端上 character set 和 collation 设置不同。
为了验证这个问题，首先查询一下两个客户端的 character set 与 collation
navicat：
greatsql> SHOW VARIABLES LIKE 'character%';+--------------------------+-----------------------------------+| Variable_name | Value |+--------------------------+-----------------------------------+| character_set_client | utf8mb4 || character_set_connection | utf8mb4 || character_set_database | utf8mb4 || character_set_filesystem | binary || character_set_results | utf8mb4 || character_set_server | utf8mb4 || character_set_system | utf8mb3 || character_sets_dir | /usr/bin/share/greatsql/charsets/ |+--------------------------+-----------------------------------+8 rows in set (0.03 sec)
greatsql> SHOW VARIABLES LIKE '%collation%';+-------------------------------+--------------------+| Variable_name | Value |+-------------------------------+--------------------+| collation_connection | utf8mb4_0900_ai_ci || collation_database | utf8mb4_0900_bin || collation_server | utf8mb4_0900_bin || default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |+-------------------------------+--------------------+4 rows in set (0.03 sec)
复制代码
GreatSQL 命令行：
greatsql> SHOW VARIABLES LIKE 'character%';+--------------------------+-----------------------------------+| Variable_name | Value |+--------------------------+-----------------------------------+| character_set_client | utf8mb4 || character_set_connection | utf8mb4 || character_set_database | utf8mb4 || character_set_filesystem | binary || character_set_results | utf8mb4 || character_set_server | utf8mb4 || character_set_system | utf8mb3 || character_sets_dir | /usr/bin/share/greatsql/charsets/ |+--------------------------+-----------------------------------+8 rows in set (0.01 sec)
greatsql> SHOW VARIABLES LIKE '%collation%';+-------------------------------+--------------------+| Variable_name | Value |+-------------------------------+--------------------+| collation_connection | utf8mb4_0900_bin || collation_database | utf8mb4_0900_bin || collation_server | utf8mb4_0900_bin || default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |+-------------------------------+--------------------+4 rows in set (0.00 sec)
复制代码
对比发现，字符集设置是相同的，只有collation_connection参数存在不同。
那么是否是因为collation_connection的设置影响了函数索引的使用呢？我们继续前面的实验，设置 navicat 客户端的collation_connection参数 ，保持与命令行相同。
greatsql> SET collation_connection=utf8mb4_0900_bin;Query OK, 0 rows affected (0.00 sec)
greatsql> EXPLAIN SELECT * FROM t1 WHERE substr(c1,1,8) ='20241209';+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| 1 | SIMPLE | t1 | NULL | ref | idx_c1_1 | idx_c1_1 | 35 | const | 1 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set (0.03 sec)
复制代码
发现通过改变 navicat 客户端 collation_connection 的设置，可以使用 GreatSQL 命令行创建的索引 idx_c1_1 了。这说明这个函数索引的 collation 跟 collation_connection 有关。
MySQL 手册中对 collation_connection 参数说明如下:
The collation of the connection character set. collation_connection is important for comparisons of literal strings. For comparisons of strings with column values, collation_connection does not matter because columns have their own collation, which has a higher collation precedence .
此参数对字符串常量的比较影响比较大，而对两个列比较影响不大，因为列有自己的 collation 规则，本例中恰好是函数索引列跟字符常量的比较。
为什么 navicat 客户端的 collation_connection 跟 GreatSQL 命令行设置会不同呢？
我们打开 general log，看一下 navicat 在连接数据库时发生了什么？
2024-12-10T09:08:29.460226+08:00 10 Connect greatsql@172.16.64.235 on using TCP/IP2024-12-10T09:08:29.463713+08:00 10 Query SET NAMES utf8mb42024-12-10T09:08:29.468336+08:00 10 Query SHOW VARIABLES LIKE 'lower_case_%';2024-12-10T09:08:29.472745+08:00 10 Query SHOW VARIABLES LIKE 'sql_mode';2024-12-10T09:08:29.475088+08:00 10 Query SELECT COUNT(*) AS support_ndb FROM information_schema.ENGINES WHERE Engine = 'ndbcluster'2024-12-10T09:08:29.490589+08:00 10 Query SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA
复制代码
使用 navicat 打开数据库的连接，从 general log 可以看到数据库接收到“SET NAMES utf8mb4”。
set names 语句的语法如下：
SET NAMES {'charset_name' [COLLATE 'collation_name'] | DEFAULT}
复制代码
此语句会改变三个 session 级会话参数，character_set_client, character_set_connection, and character_set_results。
未指定 COLLATE 子句时，会将会话级的[collation_connection]参数设置为指定 charset_name 的默认的 collation。从上面查询数据库的 character set 和 collation 相关参数设置时可以看到，参数 default_collation_for_utf8mb4 的值为 utf8mb4_0900_ai_ci，所以字符集 utf8mb4 的默认 collation 是 utf8mb4_0900_ai_ci。
而数据库在 global 级设置collation_connection的值为 utf8mb4_0900_bin。GreatSQL 命令行连接数据库并没有发出 set names 语句，继承了数据库 global 级的collation_connection参数设置，这才造成了 navicat 客户端与 GreatSQL 命令行客户端的 session 级collation_connection参数设置不同。
问题解决
1.在 navicat 客户端上涉及此问题时，使用 set names 语句修改会话级参数设置，与数据库 global 参数设置保持一致。
SET names utf8mb4 collate utf8mb4_0900_bin;
复制代码
然后再创建函数索引，执行相关 SQL。
2.从根本上解决，还是 table 设计要规范，本案例中之所以涉及这个问题，是因为时间数据用整型来存储，查询时又按照字符类型来查询一天的数据，所以要建 substr 函数索引来查询，兜兜转转的转换，还是挺累的。最好时间数据就用时间类型的字段来存储。即便用整型来存储了时间数据，也不一定非要靠建函数索引来解决此问题。直接在列上建索引，语句条件调整为 c1>=20241209000000 and c1<20241210000000， 这样才具备通用性，虽然数据库提供了建函数索引的功能，但其实通过建函数索引来解决问题实在是下下策，因为函数索引的使用具备局限性，换一种查法，可能就用不上了。
评论