MySQL表字段字符集不同导致的索引失效问题

2022-12-19 16:06:48

一、问题描述

昨天晚上，测试小妹妹突然发现，保存企业信息的时候突然报错，我在查看日志之后，发现在添加企业管理员的时候保存企业和用户关系时执行sql报错，原因是user_name字段保存不了特殊字符，因为使用的是utf-8的编码格式，因此决定设置为utf8mb4编码格式，来存储用户名称。ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;执行了这条sql语句设置了表的字符集为utf8mb4，两分钟后，有人提出，日常环境查询巨慢，才发现问题。通过企业信息表和企业用户关系表查询时速度巨慢无比。sql如下：

select  e.* from pub_yht_enterprise e  , pub_yht_enterprise_user eu where  e.id=eu.enterid and eu.userid= '1b65884d-49e9-4a42-b192-bd9cba095f67'

查看执行计划：惊讶的发现 pub_yht_enterprise竟然没有走主键索引，进行了全表扫描，怀疑是设置编码格式导致的。

通过show full columns from pub_yht_enterprise_user;查看表字段的编码格式，如下图：

两张表的企业id字段编码不同，更改列的编码格式ALTER TABLE pub_yht_enterprise_user CHANGE enterid enterid VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;再次查看执行计划，走了索引：如图：

问题解决。

二、问题总结

（1）表字符集不同时，可能导致join的SQL使用不到索引，引起严重的性能问题；

（2）SQL上线前要做好SQL Review工作，尽量在和生产环境一样的环境下Review；

（3）改字符集的alter table操作会阻塞写，尽量在业务低峰操作，建议用pt-online-schema-change;

（4）表结构字符集要保持一致，发布时要做好审核工作；

（5）如果要大批量修改表的字符集，同样做好SQL的Review工作，关联的表的字符集一起做修改。

三、Mysql修改数据库、表、字段字符集说明

修改表字符集：

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE ...]; 把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：

ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...] 如：ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

只是修改表的默认字符集：

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];

如：ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

修改字段的字符集：

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];

如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

查看数据库编码：

SHOW CREATE DATABASE db_name;

查看表编码：

SHOW CREATE TABLE tbl_name;

查看字段编码：

SHOW FULL COLUMNS FROM tbl_name;

查看系统的编码字符

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

四、升级utf8到utf8mb4

1、备份

安全第一，备份所有需要升级字符编码的数据库

可以将库dump出来。
如果是虚拟机，可以给整个主机做快照。

2、升级数据库

utf8mb4是MySQL5.5.3版本之后支持的字符集，so，如果你需要使用这个字符集，前提条件是你的MySQL版本必须 >= 5.5.3

3、修改编码集

在MySQL中，可以为一个database设置字符编码，可以为一张表设置字符编码，也以为某一个字段设置字符编码。

查看当前系统默认的字符集设置

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

查看database的字符编码

show create database polarsnow;

查看table的字符编码

show create table ps;

查看column的字符编码

show full columns from ps;

修改database默认的字符集

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci

虽然修改了database的字符集为utf8mb4，但是实际只是修改了database新创建的表，默认使用utf8mb4，原来已经存在的表，字符集并没有跟着改变，需要手动为每张表设置字符集。

修改table的字符集

只修改表默认的字符集 ALTER TABLE table_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
修改表默认的字符集和所有字符列的字符集 ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

单独修改column默认的字符集

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

注：VARCHAR(191) 根据字段实例的类型填写

4、检查字段的最大长度和索引列

字段长度

由于从utf8升级到了utf8mb4，一个字符所占用的空间也由3个字节增长到4个字节，但是我们当初创建表时，设置的字段类型以及最大的长度没有改变。例如，你在utf8下设置某一字段的类型为TINYTEXT, 这中字段类型最大可以容纳255字节，三个字节一个字符的情况下可以容纳85个字符，四个字节一个字符的情况下只能容纳63个字符，如果原表中的这个字段的值有一个或多个超过了63个字符，那么转换成utf8mb4字符编码时将转换失败，你必须先将TINYTEXT更改为TEXT等更高容量的类型之后才能继续转换字符编码

索引

在InnoDB引擎中，最大的索引长度为767字节，三个字节一个字符的情况下，索引列的字符长度最大可以达到255，四个字节一个字符的情况下，索引的字符长度最大只能到191。如果你已经存在的表中的索引列的类型为VARCHAR(255)那么转换utf8mb4时同样会转换失败。你需要先将VARCHAR(255)更改为VARCHAR(191)才能继续转换字符编码

5、修改配置文件

SET NAMES utf8 COLLATE utf8_unicode_ci becomes SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci

> vim /etc/my.cnf
# 对本地的mysql客户端的配置
[client]
default-character-set = utf8mb4

# 对其他远程连接的mysql客户端的配置
[mysql]
default-character-set = utf8mb4

# 本地mysql服务的配置
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
> service mysqld restart

检查修改

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| 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     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

注：character_set_system 一直都会是 utf8，不能被更改

6. 修复&优化所有数据表

mysqlcheck -u root -p --auto-repair --optimize --all-databases

五、总结

就一句话，上生产环境一定要评估sql，大家一起看，要执行的sql带来的影响，慎重再慎重，防止生产环境事故的发生

码农公寓