作者： sustyle 原文来源：https://tidb.net/blog/2a72bc13
1 背景
近期，我们线上的 tidb 集群就遇到一个变更忽略大小写的需求，本来以为一个改表工单就解决了，但是业务反馈工单完成后，大小写仍旧敏感，本文就来总结一下这类需求可能遇到的问题以及如何避坑，仅供参考。
本文演示的实验环境是 tidb 4.0.13 版本。
tidb 集群默认是大小写敏感，如果初始化集群的时候没有启用相关参数【new_collations_enabled_on_first_bootstrap】，后期想变更为忽略大小写就十分麻烦，可能还存在一定的风险。
2、问题复现
（1）准备测试环境
mysql> use tidbtestReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A
Database changedmysql> create table t(id int not null primary key,name varchar(50) not null default '',age int not null default 0,unique key uk_name(name),key idx_age(age));Query OK, 0 rows affected (0.07 sec)
mysql> show create table t\G*************************** 1. row *************************** Table: tCreate Table: CREATE TABLE `t` ( `id` int(11) NOT NULL, `name` varchar(50) NOT NULL DEFAULT '', `age` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `uk_name` (`name`), KEY `idx_age` (`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin1 row in set (0.01 sec)
mysql>
mysql> insert into t values(1,'a',10),(2,'A',11);Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t;+----+------+-----+| id | name | age |+----+------+-----+| 1 | a | 10 || 2 | A | 11 |+----+------+-----+2 rows in set (0.00 sec)
mysql>
复制代码
测试表【t】id 是主键字段，name 是唯一索引字段，因为是大小写敏感，所以 ’a’ 和 ‘A’ 是可以写进去的，唯一索引也是为了验证大小写敏感这一性质。
（2）修改排序集
mysql> alter table t modify `name` varchar(50) not null COLLATE utf8mb4_general_ci DEFAULT '';Query OK, 0 rows affected (0.07 sec)
mysql> show create table t\G*************************** 1. row *************************** Table: tCreate Table: CREATE TABLE `t` ( `id` int(11) NOT NULL, `name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '', `age` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `uk_name` (`name`), KEY `idx_age` (`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin1 row in set (0.00 sec)
mysql> select * from t where name = 'a';+----+------+-----+| id | name | age |+----+------+-----+| 1 | a | 10 |+----+------+-----+1 row in set (0.00 sec)
mysql> select * from t where name = 'A';+----+------+-----+| id | name | age |+----+------+-----+| 2 | A | 11 |+----+------+-----+1 row in set (0.01 sec)
mysql>
复制代码
可以看到，修改排序集操作成功了，但是从实际测试看没有生效。
3、测试
经过查阅官方文档后发现是因为 tidb 从 4.0 开始支持 collation 规则但是默认是关闭的，对应的参数是 new_collation_enabled。该参数在 6.0 版本开始默认开启。下面将该参数调整为 True 试试。
该参数对应的配置文件参数是 new_collations_enabled_on_first_bootstrap，要求是新集群才生效，即旧集群启用这个参数是无法启用 collation 规则，需要直接更改 tidb 系统的参数才生效。
（1）验证
mysql> select * from mysql.tidb where VARIABLE_NAME = 'new_collation_enabled';+-----------------------+----------------+----------------------------------------------------+| VARIABLE_NAME | VARIABLE_VALUE | COMMENT |+-----------------------+----------------+----------------------------------------------------+| new_collation_enabled | False | If the new collations are enabled. Do not edit it. |+-----------------------+----------------+----------------------------------------------------+1 row in set (0.00 sec)
mysql> update mysql.tidb set VARIABLE_VALUE = 'True' where VARIABLE_NAME = 'new_collation_enabled' limit 1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from mysql.tidb where VARIABLE_NAME = 'new_collation_enabled';+-----------------------+----------------+----------------------------------------------------+| VARIABLE_NAME | VARIABLE_VALUE | COMMENT |+-----------------------+----------------+----------------------------------------------------+| new_collation_enabled | True | If the new collations are enabled. Do not edit it. |+-----------------------+----------------+----------------------------------------------------+1 row in set (0.01 sec)
mysql> select * from t where name = 'a';+----+------+-----+| id | name | age |+----+------+-----+| 1 | a | 10 |+----+------+-----+1 row in set (0.00 sec)
mysql> select * from t where name = 'A';+----+------+-----+| id | name | age |+----+------+-----+| 2 | A | 11 |+----+------+-----+1 row in set (0.01 sec)
mysql>
复制代码
可以看到调整为 True 后还是不生效，原因是这个参数需要重启 tidb 组件才能生效。
tiup cluster reload cluster_name -R tidb
复制代码
注意这里用的是 reload。需要注意，这种情况下在线上一定不能 reload 或者重启，要不然容易引发故障，如下测试，查询会失效。
重启以后再次查看发现一个诡异的事情，之前的查询条件不管是使用小写还是大写现在都没法查询到数据，但是数据真实的存在表里，于是乎就想着走强制索引试试。
mysql> select * from mysql.tidb where VARIABLE_NAME = 'new_collation_enabled';+-----------------------+----------------+----------------------------------------------------+| VARIABLE_NAME | VARIABLE_VALUE | COMMENT |+-----------------------+----------------+----------------------------------------------------+| new_collation_enabled | True | If the new collations are enabled. Do not edit it. |+-----------------------+----------------+----------------------------------------------------+1 row in set (0.00 sec)
mysql> use tidbtestReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A
Database changedmysql> select * from t where name = 'a';Empty set (0.00 sec)
mysql> select * from t where name = 'A';Empty set (0.00 sec)
mysql> select * from t;+----+------+-----+| id | name | age |+----+------+-----+| 1 | a | 10 || 2 | A | 11 |+----+------+-----+2 rows in set (0.00 sec)
mysql>
复制代码
如果 name 字段不带索引，这里的查询是可以查到数据的，而且是符合预期，即大小写不再敏感。
下面使用强制索引试试
mysql> select * from t where name = 'a';Empty set (0.00 sec)
mysql> select * from t force index(idx_age) where name = 'a';Empty set (0.00 sec)
mysql> desc select * from t force index(idx_age) where name = 'a';+-------------+---------+------+------------------------------+---------------+| id | estRows | task | access object | operator info |+-------------+---------+------+------------------------------+---------------+| Point_Get_1 | 1.00 | root | table:t, index:uk_name(name) | |+-------------+---------+------+------------------------------+---------------+1 row in set (0.00 sec)
mysql> desc select * from t where name = 'a';+-------------+---------+------+------------------------------+---------------+| id | estRows | task | access object | operator info |+-------------+---------+------+------------------------------+---------------+| Point_Get_1 | 1.00 | root | table:t, index:uk_name(name) | |+-------------+---------+------+------------------------------+---------------+1 row in set (0.00 sec)
mysql>
复制代码
可以看到强制索引并不能解决问题，通过执行计划发现还是走了 uk_name 这个索引。
起初以为跟这个表的数据量少有关系，后来追加了 10000 行测试数据发现结果还是一样。
后来尝试通过模糊查询发现只有将 ’%a’ 或者 ’%A’ 才能查到数据，且大小写已经不敏感。
mysql> select * from t where name like '%a';+----+------+-----+| id | name | age |+----+------+-----+| 1 | a | 10 || 2 | A | 11 |+----+------+-----+2 rows in set (0.01 sec)
mysql> select * from t where name like '%a%';+----+------+-----+| id | name | age |+----+------+-----+| 1 | a | 10 || 2 | A | 11 |+----+------+-----+2 rows in set (0.02 sec)
mysql> select * from t where name like '%A';+----+------+-----+| id | name | age |+----+------+-----+| 1 | a | 10 || 2 | A | 11 |+----+------+-----+2 rows in set (0.02 sec)
mysql> select * from t where name like '%A%';+----+------+-----+| id | name | age |+----+------+-----+| 1 | a | 10 || 2 | A | 11 |+----+------+-----+2 rows in set (0.02 sec)
mysql> select id,concat('-',name,'-') name, age from t where name like '%a%';+----+------+-----+| id | name | age |+----+------+-----+| 1 | -a- | 10 || 2 | -A- | 11 |+----+------+-----+1 row in set (0.00 sec)
mysql>
复制代码
从测试结果猜测可能是 uk_name 这个索引的问题，忽略大小写且在唯一约束条件下，’a’ 和 ’A’ 能同时存在，这显然不合理，于是乎就想着重建这个索引。
mysql> alter table t drop index uk_name;Query OK, 0 rows affected (0.26 sec)
mysql> select * from t where name = 'A';+----+------+-----+| id | name | age |+----+------+-----+| 1 | a | 10 || 2 | A | 11 |+----+------+-----+2 rows in set (0.01 sec)
mysql> select * from t where name = 'a';+----+------+-----+| id | name | age |+----+------+-----+| 1 | a | 10 || 2 | A | 11 |+----+------+-----+2 rows in set (0.02 sec)
mysql> desc select * from t where name = 'A';+-------------------------+---------+-----------+---------------+--------------------------------+| id | estRows | task | access object | operator info |+-------------------------+---------+-----------+---------------+--------------------------------+| TableReader_7 | 1.00 | root | | data:Selection_6 || └─Selection_6 | 1.00 | cop[tikv] | | eq(tidbtest.t.name, "A") || └─TableFullScan_5 | 2.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |+-------------------------+---------+-----------+---------------+--------------------------------+3 rows in set (0.00 sec)
mysql>
复制代码
可以看到删除 uk_name 这个索引后，查询就正常了。
mysql> alter table t add unique index uk_name(name);ERROR 1062 (23000): Duplicate entry 'a' for key 'uk_name'mysql> alter table t add index idx_name(name);Query OK, 0 rows affected (0.26 sec)
mysql> select * from t where name = 'a';+----+------+| id | name |+----+------+| 1 | a || 2 | A |+----+------+2 rows in set (0.00 sec)
mysql> select * from t where name = 'A';+----+------+| id | name |+----+------+| 1 | a || 2 | A |+----+------+2 rows in set (0.01 sec)
mysql>
复制代码
因 ’a’,‘A’ 是重复数据，所以没法加唯一索引，加一个普通二级索引。
4、解决方案
通过测试发现，这类需求比较棘手，那能不能解决呢？答案当然是肯定的，只是成本问题，下面提供两个方案，仅供参考。
（1）直接在原集群进行操作
这个方案适合小表的场景，主要考虑点是操作过程中对目标字段需要重建索引（如果有索引的话），所以要能容忍全表扫描带来的性能问题。
mysql> use tidbtestReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A
Database changedmysql> select * from t where name = 'A';+----+------+-----+| id | name | age |+----+------+-----+| 2 | A | 11 |+----+------+-----+1 row in set (0.00 sec)
mysql> select * from t where name = 'a';+----+------+-----+| id | name | age |+----+------+-----+| 1 | a | 10 |+----+------+-----+1 row in set (0.00 sec)
mysql> alter table t modify `name` varchar(50) not null COLLATE utf8mb4_general_ci DEFAULT '';ERROR 8200 (HY000): Unsupported modifying collation of column 'name' from 'utf8mb4_bin' to 'utf8mb4_general_ci' when index is defined on it.mysql> /*目标字段没有索引直接就能修改成功*/
复制代码
如果目标字段是索引字段，则无法直接修改排序集，需要删除索引后再修改。
mysql> alter table t drop index uk_name;Query OK, 0 rows affected (0.25 sec)
mysql> alter table t modify `name` varchar(50) not null COLLATE utf8mb4_general_ci DEFAULT '';Query OK, 0 rows affected (0.07 sec)
mysql> select * from t where name = 'a';+----+------+-----+| id | name | age |+----+------+-----+| 1 | a | 10 || 2 | A | 11 |+----+------+-----+2 rows in set (0.02 sec)
mysql> alter table t add index idx_name(name); Query OK, 0 rows affected (0.85 sec)/*如果目标字段没有索引就忽略即可*//*如果目标字段是唯一索引则需要考虑重复值问题，有可能加不上*/
mysql> select * from t where name = 'a';+----+------+-----+| id | name | age |+----+------+-----+| 1 | a | 10 || 2 | A | 11 |+----+------+-----+2 rows in set (0.00 sec)
mysql>
复制代码
可以看到，大小写已经不敏感了。
（2）集群迁移（物理备份）
这个方案适合目标表是大表的场景。具体操作略，下面简单提供一个思路：
部署好一套新集群
利用 br 备份原集群
利用 br 将备份恢复到新集群
使用 ticdc 进行增量同步
待数据同步后，对新集群的目标表配置大小写不敏感
协同业务进行验证
切换流量，将业务流量切到新集群
另外，待数据同步后，是在新集群变更大小写敏感参数，因为新集群这时候未接受业务流量，所以重启，重建索引等可以放心操作，具体操作流程请参考第一个方案。
（3）集群迁移（逻辑备份）
这个方案适合目标表是大表的场景，这也是官方建议的方案。具体操作略，下面简单提供一个思路：
这种架构可能存在一个问题，上游是大小写敏感，但是下游大小写不敏感，对于唯一索引字段可能会导致同步失败。
5、总结
官方建议有需要忽略大小写需求的话，在初始化集群的时候就启用该参数，已经初始化的集群，无法通过更改该配置项打开或关闭新的 collation 框架，但是我实际测试过程中发现直接变更【mysql.tidb】表的 new_collation_enabled 参数是能关闭或者开启的，而且也能符合预期。
（1）如果目标字段是非索引字段，直接变更字段排序集以及系统参数即可，待合适时间重新加载 tidb 组件即可生效。
（2）如果目标表是小表，全表扫描的查询成本与使用目标字段的索引的查询成本相差不大，也可直接参考第一点进行操作。
（3）如果目标表是大表，则需要慎重一些，需要考虑通过集群迁移的方式进行变更此类需求。
（4）如果集群已经启用 collation 框架，则不能对索引字段进行变更排序集操作，否则会报错。
new_collation_enabled=True
mysql> alter table t2 modify `name` varchar(50) not null COLLATE utf8mb4_general_ci DEFAULT '';ERROR 8200 (HY000): Unsupported modifying collation of column 'name' from 'utf8mb4_bin' to 'utf8mb4_general_ci' when index is defined on it.mysql>
复制代码
综上所述，tidb 集群想要变更大小写敏感问题需要考虑的风险点较多，需要用户根据自身环境进行充分评估测试，本文涉及的测试及方案仅供参考，非线上环境的操作建议。
如果存在上下游关系的架构中，一定要评估好风险，避免因调整了 tidb 的大小写敏感问题导致上下游同步异常。
6、写在最后
本文对 tidb 集群变更大小写敏感问题做了一下分析及总结，总体来说此类需求是存在一定的风险的，各公司的业务场景也不一样，需求也不同，还可能碰上其他未知的问题，本文所有内容仅供参考。
评论