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

一、问题描述

昨天晚上,测试小妹妹突然发现,保存企业信息的时候突然报错,我在查看日志之后,发现在添加企业管理员的时候保存企业和用户关系时执行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竟然没有走主键索引,进行了全表扫描,怀疑是设置编码格式导致的。

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

 

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

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

 

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

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

 

 

 问题解决。

 二、问题总结

(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带来的影响,慎重再慎重,防止生产环境事故的发生

 

上一篇:Linux centos7 mysql 配置支持emoji表情包,笔记


下一篇:java 插入数据时报错Incorrect string value: '\xF0\x9F\x88\x9A' for column 'personal_histo