结合案例说明MySQL的数据类型如何优化

正文必须有字！！！

MySQL数据类型优化

下面很多优化的点总结于《高性能MySQL》这本书，加上一些自己操作过的案例说明。

不超过范围的情况下，数据类型越小越好

应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少，但是要确保没有低估需要存储的值的范围，如果无法确认哪个数据类型，就选择你认为不会超过范围的最小类型。

看一个案例，下面是两张字段相同，字段类型相同，只是id字段emp1是smallint类型，emp2的id是bigint类型，分别向两个表插入5000条记录，观察一下表容量大小。

CREATE TABLE `mytest`.`emp1`  (
  `id` smallint(5) NULL,
  `name` varchar(255) NULL
);

CREATE TABLE `mytest`.`emp2`  (
  `id` bigint(5) NULL,
  `name` varchar(255) NULL
);

两个表的初始大小是一致的：

都是96K

找不到数据文件在哪的，可以用如下命令查看：

mysql> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

写个shell脚本分别向两个表插入5000条记录：

#!/bin/bash
i=1
while [ $i -le 5000 ]
do
    mysql -uroot -p123456 mytest -e "insert into emp2 (id,name) values ($i,'n$i');"
    i=$(($i+1))
done

先后执行insert into emp1和insert into emp2

执行完毕，确认两个表都是5000条记录：

mysql> select count(*) from emp1;
+----------+
| count(*) |
+----------+
|    5000 |
+----------+
1 row in set (0.03 sec)

mysql> select count(*) from emp2;
+----------+
| count(*) |
+----------+
|    5000 |
+----------+
1 row in set (0.01 sec)

来，见证一下奇迹先：

[root@node1 mytest]# ll -h | grep emp1.ibd && ll -h | grep emp2.ibd
-rw-r-----. 1 mysql mysql 272K 8月   9 09:33 emp1.ibd
-rw-r-----. 1 mysql mysql 304K 8月   9 09:37 emp2.ibd

我们发现，两个表占用的空间竟然不一样，表emp1id字段类型smallint(5)插入5000条记录后占用空间为272K，而emp2id字段类型bigint(5)插入同样的数据后占用空间大小为304K。

这就是所谓不超过范围的情况下，数据类型越小越好

简单就好

简单数据类型的操作通常需要更少的CPU周期 1、整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂 2、使用mysql自建类型而不是字符串来存储日期和时间 3、用整型存储IP地址

我们拿日期数据类型来举个例子，同样建两张表：

CREATE TABLE `tab1` (
  `id` smallint(5) NULL,
  `name` varchar(255) NULL,
  `ctime` date NULL
);

CREATE TABLE `tab2` (
  `id` smallint(5) NULL,
  `name` varchar(255) NULL,
  `ctime` datetime NULL
);

tab1的ctime字段类型为date，tab2的ctime字段类型为datetime，同样，执行shell脚本，插入2万条记录：

#!/bin/bash
i=1
while [ $i -le 20000 ]
do
    mysql -uroot -p123456 mytest -e "insert into tab1 (id,name,ctime) values ($i,'n$i',now());"
    i=$(($i+1))
done

数据准备完毕后，我们来分别查询一下这两个表

look，看到了，查询两个表的sql语句执行速度明显不一样！

尽量避免null

如果查询中包含可为NULL的列，对mysql来说很难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂。 通常情况下null的列改为not null带来的性能提升比较小，所有没有必要将所有的表的schema进行修改，但是应该尽量避免设计成可为null的列。 一切以实际情况为准。

一些细则

整数类型

可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。尽量使用满足需求的最小数据类型。前文有述。

字符和字符串类型

varchar根据实际内容长度保存数据

使用最小的符合需求的长度。 varchar(n) n小于等于255使用额外一个字节保存长度，n>255使用额外两个字节保存长度。 varchar(5)与varchar(255)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小 。 varchar在mysql5.6之前变更长度，或者从255一下变更到255以上时时，都会导致锁表。

varchar应用场景：

存储长度波动较大的数据，如：文章，有的会很短有的会很长 字符串很少更新的场景，每次更新后都会重算并使用额外存储空间保存长度 适合保存多字节字符，如：汉字，特殊字符等

char：固定长度的字符串

最大长度：255 会自动删除末尾的空格 检索效率、写效率 会比varchar高，以空间换时间

char应用场景：

存储长度波动不大的数据，如：md5摘要 存储短字符串、经常更新的字符串

BLOB和TEXT类型

MySQL 把每个 BLOB 和 TEXT值当作一个独立的对象处理。两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式存储。

日期时间

datetime

占用8个字节 与时区无关，数据库底层时区配置，对datetime无效 可保存到毫秒 可保存时间范围大 不要使用字符串存储日期类型，占用空间大，损失日期类型函数的便捷性

timestamp

占用4个字节 时间范围：1970-01-01到2038-01-19 精确到秒 采用整形存储 依赖数据库设置的时区 自动更新timestamp列的值

date

占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节 使用date类型还可以利用日期时间函数进行日期之间的计算 date类型用于保存1000-01-01到9999-12-31之间的日期

使用枚举代替字符串类型

有时可以使用枚举类代替常用的字符串类型，mysql存储枚举类型会非常紧凑，会根据列表值的数据压缩到一个或两个字节中，mysql在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的查找表

特殊类型数据

曾经我使用varchar(15)来存储ip地址，然而，ip地址的本质是32位无符号整数不是字符串，可以使用INET_ATON和INET_NTOA函数在这两种表示方法之间转换。

比如：

mysql> select inet_aton('192.168.134.119');
+------------------------------+
| inet_aton('192.168.134.119') |
+------------------------------+
|                   3232269943 |
+------------------------------+
1 row in set (0.03 sec)

mysql> select inet_ntoa('3232269943');
+-------------------------+
| inet_ntoa('3232269943') |
+-------------------------+
| 192.168.134.119         |
+-------------------------+
1 row in set (0.03 sec)