函数索引触发的一个有趣的问题

2025-02-26 189

函数索引触发的一个有趣的问题

导引

听同事提到一个有意思的事情，说在使用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: t1
Create 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_bin
1 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/IP
2024-12-10T09:08:29.463713+08:00           10 Query     SET NAMES utf8mb4
2024-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，这样才具备通用性，虽然数据库提供了建函数索引的功能，但其实通过建函数索引来解决问题实在是下下策，因为函数索引的使用具备局限性，换一种查法，可能就用不上了。

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/17771989

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

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

DistilQwen2.5发布：通义千问蒸馏小模型再升级

引言因高计算成本和复杂性，在例如移动设备和边缘计算场景等资源有限的环境中，限制了大语言模型的普及。如何在保留模型性能的同时提高计算效率并降低部署成本，已成为研究和工业界必须面对的关键挑战。在此背景下，我们正式推出基于Qwen2.5的轻量化大模型系列DistilQwen2.5。该模型通过创新的双层蒸馏框架实现突破，基于数据优化策略重构指令数据集强化模型理解能力，并且采用参数融合技术实现细粒度知识迁移。实验表明，DistilQwen2.5 在多项基准测试中性能超越原模型，同时显著降低计算资源消耗。 DistilQwen2.5 蒸馏模型的 Checkpoint 已在Hugging Face及Model Scope全面开源。本文将介绍 DistilQwen2.5 的蒸馏算法、效果评测，以及 DistilQwen2.5 模型在阿里云人工智能平台 PAI 上的使用方法，并提供在各开源社区的下载使用教程。 DistilQwen2.5中的知识蒸馏技术本节中，主要描述DistilQwen2.5模型训练中使用的黑盒化和白盒化的知识蒸馏技术。其中，DistilQwen2.5模型蒸馏的算法框架如下...

2025-02-26

449

2025年春节假期已过，大家都带着慢慢的活力回到了工作岗位。为了让大家在新的一年继续感受到 Tiny Engine 的成长与变化，我们很高兴地宣布：TinyEngine v2.2版本正式发布！本次更新带来了重要的功能增强------页面支持嵌套路由，让开发者在构建复杂应用时更加得心应手。接下来，我们将详细介绍该版本的几大亮点与改进 v2.2.0变更特性概览页面支持嵌套路由，出码支持多层级路由嵌套结构物料协议npm字段整改，物料协议新增字段：materials.packages 状态管理默认值支持表达式默认开启区块插件中的区块分类与物料插件中区块分组合并的选项修复了多个问题，包括Element Plus出码样式引入、I18n格式化、国际化配置弹窗异常等默认分支变更，refactor/develop 分支更改为 develop 详情可见：https://github.com/opentiny/tiny-engine/releases/tag/v2.2.0 TinyEngine v2.2.0新特性解读 1. 页面支持嵌套路由 Vue 的路由管理器 vue-router 在前端项目中...

2025-02-26

182

资源下载

更多资源

优质分享App

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

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

Spring

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

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。