mysql 数值 字符 优化,教你如何进行Mysql数据类型优化

1. 版本

1)操作系统版本

cat /proc/version

Linux version 3.10.0-957.5.1.el7.x86_64 (mockbuild@kbuilder.bsys.centos.org) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-36) (GCC) ) #1

2)数据库版本

mysql --version

mysql  Ver 14.14 Distrib 5.7.22, for linux-glibc2.12 (x86_64) using  EditLine wrapper

2. 问题描述

2.1 问题发现

这是一个朋友跟我咨询的问题，帮他分析解决这个问题中，我发现这个问题也正好有效的印证了我们常说的mysql 数据类型优化原则，既选择更小的数据类型(在满足业务使用的情况下)。在此拿出来跟大家分享一下。他的问题如下：

他在两张表上进行关联查询，如果两张表都是utf8 字符集那么查询会很快。如果两张表是utf8mb4字符集那么查询就比较慢。

下面建两张测试表，来重现朋友当时的现象

1. 创建两张字符集为 utf8 的表

create table test_join_1(id int,name varchar(250)) default character set utf8;

create table test_join_2(id int,name varchar(250)) default character set utf8;

2. 查看sql 的执行计划

#这里该sql写的是否有优化空间，不在我们本次讨论范围

explain select a.* ,b.id from `test_join_1` a left join(SELECT name, min(id) id from `test_join_2` b  group by name) b  on a.name=b.name;

explain select a.* ,b.id from `test_join_1` a left join(SELECT name, min(id) id from `test_join_2` b  group by name) b  on a.name=b.name;

| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref              | rows | filtered | Extra                           |

|  1 | PRIMARY     | a          | NULL       | ALL  | NULL          | NULL        | NULL    | NULL             |    1 |   100.00 | NULL                            |

|  1 | PRIMARY     | | NULL       | ref  |    | | 753     | test_chen.a.name |    2 |   100.00 | NULL                            |

|  2 | DERIVED     | b          | NULL       | ALL  | NULL          | NULL        | NULL    | NULL             |    1 |   100.00 | Using temporary; Using filesort |

3 rows in set, 1 warning (0.00 sec)

3. 转换表的字符集为 utf8mb4

#注意 utf8 字符集转换成 utf8mb4不会有问题，当时 utf8mb4 向 utf8 转换不能保证没有问题

ALTER TABLE test_join_1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ALTER TABLE test_join_2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

4. 转换字符集后再次查看sql 的执行计划

explain select a.* ,b.id from `test_join_1` a left join (SELECT name, min(id) id from `test_join_2` b  group by name) b  on a.name=b.name;

| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |

|  1 | PRIMARY     | a          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |

|  1 | PRIMARY     | | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where; Using join buffer (Block Nested Loop) |

|  2 | DERIVED     | b          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using temporary; Using filesort                    |

3 rows in set, 1 warning (0.00 sec)

##在使用 utf8 字符集时，mysql 为派生表创建了一个索引(auto_key0)，这样a表和派生表之间使用 index Nested-Loop Join(NLJ) 方式关联。在使用 utf8mb4 字符集时 Mysql 使用 Block Nested-Loop Join(BNL) 方式进行关联。NLJ 比 BNL 访问方式更高效。

通过分析执行计划，相同数据的情况下，第一次执行会比第二次执行更高效，这也符合朋友生产中产生的现象。但是问题是为什么使用 utf8字符集时，会给派生表创建索引，使用utf8mb4时就不会呢？

其实原因很简单，代价，mysql 会计算给派生表name列索引的长度(派生表name列长度使用b表中name列长度)，b表name列 为 varchar(250),在utf8和utf8mb4 字符集下计算出的索引长度分别为 753(utf8 字符集最多使用3个字节表示一个字符，同时 name 列允许为 null，并且是可变长度类型，所以key_len=250*3+1+2=753) 和 1003(utf8mb4 字符集最多使用4个字节表示一个字符，同时name列允许为null，并且是可变长度类型，所以key_len=250*4+1+2=1003)

对于 key_len 超过 1000 的列mysql不会为派生表创建 ，这也告诉我们，不要随意设置列的长度，在满足业务的情况下，列的长度是越小越好。

2.2 问题原因

在不同的字符集下，mysql 对同一个列创建创建索引的长度是不一样的，当mysql计算在该列上创建索引的长度超过1000的话，就不会对派生表创建索引。

关于索引长度具体怎么计算下次会分享。

2.3 问题处理

按照规范设计和使用数据库。

MySQL 应该使用更小的数据类型(在满足业务的情况下)，理由如下：

1)更小的数据类型可能占用的磁盘空间更小，相同的内存空间可以缓存更多的记录，减少IO操作，减少磁盘空间占用

2)更小的数据类型意味着CPU可以更快的进行计算。

3)最主要的是，在数据库执行过程中MySQL有时会建立一些临时表进行连接排序或去重操作，在这些临时表中列的长度跟源表中列的长度一致，列的长度越大，性能也就越不好。所以列的类型尽可能的小。