的utf8mb4字符集是MySQL的新的默认为8.0,和这种变化影响到现有数据和部队任何升级。
迁移到utf8mb4有许多优点,包括:
- 它可以储存更多的符号,包括emojis
- 新的排序对亚洲语言
- 它是快比utf8mb3
尽管如此,你可能想知道移民如何影响你的现有数据。这个博客涵盖多个方面。
存储需求
顾名思义,一个字符可以采取的最大字节数与字符集utf8mb4是四个字节。这是比的要求utf8mb3这需要三个字节和许多其他MySQL字符集。
幸运的是,utf8mb3是的一个子集utf8mb4,和迁移现有的数据并不会增加数据存储在磁盘的大小:每个字符占用多少字节。例如,拉丁字母数字或字母需要一个字节。从其他字母字符可以占用4个字节。这可以用一个简单的测试验证。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20.
|
mysql吗?>
集
的名字
utf8mb4;
查询好吧,0
行
影响(0,00sec)
mysql吗?>
创建
表
charset_len (的名字
VARCHAR(255),瓦尔
字符(1))
字符集
=utf8mb4;
查询好吧,0
行
影响(0,03sec)
mysql吗?>
插入
成
charset_len
值(“拉丁”,
“一个”),(“西里尔А”,
“А”),(“韩国㉿”,
“㉿”),(“海豚?”,
“?”);
查询好吧,4
行
影响(0,02年sec)
记录:4副本:0
警告:0
mysql吗?>
选择
的名字,瓦尔,
十六进制(val),
BIT_LENGTH(val) / 8
从
charset_len;
+ - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
|的名字
|瓦尔|
十六进制(val)|
BIT_LENGTH(val) / 8|
+ - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
|拉丁一个
|一个
|41
|
1.0000|
|西里尔字母А
|А
|D090
|
2.0000|
|韩国㉿
|㉿
|E389BF
|
3.0000|
|海豚吗?
|吗?
|F09F90AC|
4.0000|
+ - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
4
行
在
集
(0,00sec)
|
因此,所有数据使用最多三个字节不会改变,你将能够存储需要4个字节的字符编码。
最大长度的列
而不改变数据存储,当MySQL计算最大列可以存储的数据量,它可能会失败对于一些列大小定义,正常工作utf8mb3。例如,您可以有一个与这个定义表:
|
1
2
3
4
|
mysql
吗?
>
创建
表
len_test
(
- >
喷火
VARCHAR
(
16384年
)
- >
)
引擎
=
InnoDB
字符
集
utf8mb3
;
查询
好吧
,
0
行
影响
,
1
警告
(
0
,
06
证券交易委员会
)
|
如果你决定把这个表使用utf8mb4字符集,操作将会失败:
|
1
2
|
mysql
吗?
>
改变
表
len_test
转换
来
字符
集
utf8mb4
;
错误
1074年
(
42000年
)
:
列
长度
太
大
为
列
“foo”
(
马克斯
=
16383年
)
;
使用
团
或
文本
而不是
|
这样做的原因是,的最大字节数,MySQL可以存储在一个VARCHAR列是65535,这是21845个字符utf8mb3字符集和16383个字符utf8mb4字符集。
因此,如果你有列可能包含超过16383个字符,您将需要将其转换成的文本或量变数据类型。
你可以找到所有这些列如果运行查询:
|
1
2
3
4
5
6
7
|
选择
TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,
CHARACTER_MAXIMUM_LENGTH,DATA_TYPE
从
information_schema。列
在哪里
CHARACTER_MAXIMUM_LENGTH
>
16383年
和
DATA_TYPE
不
就像
“%文本%”
和
DATA_TYPE
不
就像
“% blob %”
和
TABLE_SCHEMA
不
在
(“mysql”,
“information_schema”,
“performance_schema”);
|
例如,在我的测试环境,它返回:
|
1
2
3
4
5
6
7
|
* * * * * * * * * * * * * * * * * * * * * * * * * * *1。
行
* * * * * * * * * * * * * * * * * * * * * * * * * * *
TABLE_SCHEMA:测试
TABLE_NAME:设置
COLUMN_NAME:
价值
CHARACTER_MAXIMUM_LENGTH:20000年
DATA_TYPE:
varchar
1
行
在
集
(0,02年证券交易委员会
|
索引存储要求
MySQL事先并不知道哪些字符存储在列创建索引时。因此,当它计算所需的存储索引,需要选择的字符集的最大值。作为一个结果,你可能会影响索引存储限制从另一个字符集转换的时候出现utf8mb4。InnoDB,索引的最大大小是767字节冗余和紧凑的行格式,3072字节动态和压缩行格式。看到用户参考手册获取详细信息。
这意味着你需要检查如果你有索引,可以长到超过这些值在执行更新。你可以用下面的查询:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
与
索引
作为
(
与
表
作为
(
选择
SUBSTRING_INDEX(t.NAME
' / ',1)
作为
”数据库”,
SUBSTRING_INDEX(t.NAME
' / ',1)
作为
”表”,i.NAME
作为
”指数”,
ROW_FORMAT
从
information_schema.INNODB_INDEXES我
加入
information_schema.INNODB_TABLESt
使用(TABLE_ID)
)
选择
”数据库”,”表”,”指数”,
ROW_FORMAT,
GROUP_CONCAT(kcu.COLUMN_NAME)
作为
列,
总和(c.CHARACTER_MAXIMUM_LENGTH)*4
作为
index_len_bytes
从
表
加入
information_schema.KEY_COLUMN_USAGEkcu
在
('数据库”=TABLE_SCHEMA
和
”表”=kcu.TABLE_NAME
和
”指数”=kcu.CONSTRAINT_NAME)
加入
information_schema。列
c
在
(kcu.COLUMN_NAME=c.COLUMN_NAME
和
”数据库”=c.TABLE_SCHEMA
和
”表”=c.TABLE_NAME)
在哪里
c.CHARACTER_MAXIMUM_LENGTH
是
非空
集团
通过
”数据库”,”表”,”指数”,
ROW_FORMAT
命令
index_len_bytes
)
选择
*
从
索引
在哪里
index_len_bytes
>=768;
|
下面是我的测试环境中运行查询的结果:
|
1
2
3
4
5
6
7
8
|
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
|
数据库
|
表
|
指数
|
ROW_FORMAT
|
列
|index_len_bytes|
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
|食谱|hitcount
|
主
|
动态
|路径
|
1020年|
|食谱|短语
|
主
|
动态
|phrase_val|
1020年|
|食谱|ruby_session|
主
|
动态
|session_id|
1020年|
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
3
行
在
集
(0,04sec)
|
一旦你已经确定了这样的索引,检查列和调整相应的表定义。
注意:查询使用CTEMySQL 8.0的,可用。如果你仍在5.7或更早版本,您将需要重写查询。
临时表
一个问题你可以转换后utf8mb4字符集是一个隐含的临时表的大小增加,MySQL创建解决查询。自utf8mb4可能比其他字符集存储更多的数据,这种隐式表的列的大小也将更大。找出如果你受到这个问题的影响,看全局状态变量Created_tmp_disk_tables。如果这开始显著增加在迁移之后,您可能会考虑更新RAM在您的机器上,增加临时表的最大大小。注意,这个问题可能是一个症状,你的一些查询优化。
结论
转换到utf8mb4字符集带给你的优势更好的性能,更大范围的字符,您可以使用,包括emojis和新排序(排序规则)。这种转换发生在几乎没有价格,可以顺利完成。
确保:
- 你转换VARCHAR列存储超过16383个字符文本或量变数据类型
- 你调整索引定义可能需要超过767字节冗余和紧凑的行格式,3072字节动态和压缩行格式后迁移。
- 优化你的查询,以便他们不应该开始使用内部基于磁盘的临时表
值得一提的另一个问题:从use utf8 (mb3)迁移到utf8mb4可能打破独特的约束自排序是不相同的。看到https://dev.mysql.com/blog-archive/mysql-8-0-collations-migrating-from-older-collations/和https://dev.mysql.com/blog-archive/mysql-8-0-collations-migrating-from-older-collations-part-2/
>这是比utf8mb3快
性能utf8mb3 vs utf8mb4 MariaDB呢?
这取决于如果他们utf8mb4实现移植从MySQL或不是。如果移植:性能应该相同。如果不是:我还没有看到在MariaDB utf8mb4和utf8mb3性能基准测试。
这是一个错误的查询:
从information_schema。在NODB_INDEXES i JOIN information_schema.INNODB_TABLES t USING(TABLE_ID)
它应该是
从information_schema。在NODB_SYS_INDEXES i JOIN information_schema.INNODB_SYS_TABLES t USING(TABLE_ID)
不。INNODB_SYS_INDEXES和INNODB_SYS_TABLES更名为INNODB_INDEXES和INNODB_TABLES MySQL 8.0