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字符集那么查询就比较慢。其实如果朋友是dba 的话，他一看执行计划就能看出来，是因为两种情况下执行计划不一样所以导致执行效率不同

下面建两张测试表，来重现朋友当时的现象
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     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 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     | <derived2> | 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不会为派生表创建 <auto_key0>，这也告诉我们，不要随意设置列的长度，在满足业务的情况下，列的长度是越小越好。

 

2.2 问题原因

    通过上面的例子，我们知道问题的原因是，在不同的字符集下，mysql 对同一个列创建创建索引的长度是不一样的，当mysql计算在该列上创建索引的长度超过1000的话，就不会对派生表创建索引。

关于索引长度具体怎么计算可以参考：

https://blog.csdn.net/shaochenshuo/article/details/105210931

 

2.3 问题处理

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

MySQL 应该使用更小的数据类型(在满足业务的情况下)，理由如下：
1)更小的数据类型可能占用的磁盘空间更小，相同的内存空间可以缓存更多的记录，减少IO操作，减少磁盘空间占用
2)更小的数据类型意味着CPU可以更快的进行计算。
3)最主要的是，在数据库执行过程中MySQL有时会建立一些临时表进行连接排序或去重操作，在这些临时表中列的长度跟源表中列的长度一致，列的长度越大，性能也就越不好。所以列的类型尽可能的小。