不超过范围的情况下，数据类型越小越好
应当尽可能使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，由于它们占用更少的磁盘、内存和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 ：
PS：可以用以下命令查看数据文件的寄存位置：
>
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
注意表名，emp1 和 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 脚本，插入 20000 条记录：
#!/bin/bash
i=1
while [ $i -le 20000 ]
do
mysql -uroot -p123456 test -e “insert into tab1 (id,name,ctime) values ($i,’n$i’,now());”
i=$(($i+1))
done
改下脚本，再向表 tab2 插入 20000 条记录。
数据准备终了后，我们来分别查询一下这两个表：
look，看到了，查询两个表的 SQL 语句履行速度不一样（样本量可能还有点小）！
尽可能避免 null
如果查询中包括可为 NULL 的列，对 MySQL 来讲很难优化，由于可为 null 的列使得索引、索引统计和值比较都更加复杂。
通常情况下 null 的列改成 not null 带来的性能提升比较小，所有无必要将所有的表的 schema 进行修改，但是应当尽可能避免设计成可为 null 的列。
一切以实际情况为准。
一些细则
整数类型
可使用的几种整数类型：
- TINYINT 8 bit，
- SMALLINT 16 bit，
- MEDIUMINT 24 bit，
- INT 32 bit，
- BIGINT 64 bit
尽可能使用满足需求的最小数据类型。前文有述。
字符和字符串类型
varchar：根据实际内容长度保存数据。
使用最小的符合需求的长度：
varchar(n) ：n小于等于255使用额外一个字节保存长度，n>255使用额外两个字节保存长度。
varchar(5) 与 varchar(255) 保存一样的内容，硬盘存储空间相同，但内存空间占用区别，是指定的大小 。
varchar在 MySQL 5.6 之前变更长度，或从255一下变更到255以上时，都会致使锁表。
varchar利用场景：
存储长度波动较大的数据，如：文章，有的会很短有的会很长；
字符串很少更新的场景，每次更新后都会重算并使用额外存储空间保存长度；
合适保存多字节字符，如：汉字，特殊字符等。
char：固定长度的字符串
最大长度：255；
会自动删除末尾的空格；
检索效力、写效力 会比varchar高，以空间换时间。
char使用处景：
存储长度波动不大的数据，如：md5摘要；
存储短字符串、常常更新的字符串。
BLOB 和 TEXT 类型
MySQL 把每一个 BLOB 和 TEXT值当作一个独立的对象处理。
二者都是为了存储很大数据而设计的字符串类型，分别采取二进制和字符方式存储。
日期时间
datetime
- 占用8个字节；
- 与时区无关，数据库底层时区配置，对 datetime 无效；
- 可保存到毫秒；
- 可保存时间范围大；
- 不要使用字符串存储日期类型，占用空间大，损失日期类型函数的便捷性。
timestamp
- 占用4个字节；
- 时间范围：1970-01-01到2038-01⑴9；
- 精确到秒；
- 采取整形存储；
- 依赖数据库设置的时区；
- 自动更新timestamp列的值。
date
- 占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节；
- 使用date类型还可以利用日期时间函数进行日期之间的计算；
- date类型用于保存1000-01-01到9999⑴2⑶1之间的日期。
使用枚举代替字符串类型
有时可使用枚举类型代替经常使用的字符串类型，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)
到此这篇关于原来MySQL 数据类型也能够优化的文章就介绍到这了,更多相关MySQL 数据类型 内容请搜索之前的文章或继续浏览下面的相关文章希望大家以后多多支持！
本文来源：https://www.yuntue.com/post/150803.html | 云服务器网，转载请注明出处！
微信扫一扫打赏
支付宝扫一扫打赏
