MySQL中级优化教程（五）——数据库建表时数据类型的选择

• 您可以到这里下载本篇博文所使用的数据库以及优化工具mysqldumpslow和pt-query-digest：

• https://blog.csdn.net/IT_TIfarmer/article/details/92224646

• 关于explain语句的结果参数详解也可以参考如上博文。

数据类型的选择，重点在于合适二字，如何确定选择的数据类型是否合适？

请记住以下几个原则：

 

 

1. 使用可以存下我们的数据的最小的数据类型。
2. 使用简单的数据类型。Int要比varchar类型在mysql处理上简单。
3. 尽可能使用not null定义字段。
4. 尽量少用text类型，非用不可时考虑好分表。

举个栗子，当我们要存取一个日期时，我们可以用varchar进行存储，也可用date、datetime、timestamp、甚至是int来存储。int无疑是上述几种数据类型中最小的，也比较简单，而在mysql数据库中，timestamp所占字节与int一样，但int在出具库引擎中的处理要比timestamp简单的多，所以我们首选int，其次是timestamp。

为什么要尽可能使用not null来定义字段呢？

原因就是innodb对允许为null的字段，会额外的消耗一些内存对其进行存储，而且，就算我们将一个数据的该字段设为空值，它还是会占用跟其它非空字段一样的存储空间，更何况，空值对索引是不友好的，因此我们尽量用一个默认值来取代空值。

那么对于第四条如何解释呢？

众所周知，text和blob这些大的数据类型是非常占用空间的，它会对我们的查询操作造成不小的影响，因为查询操作对这些字段内容的遍历会白白浪费很多资源。如果我们非用不可的话，就需要把这些字段单独开一张或几张表来存储，并与原来的主表关联，以此来提高主表查询的效率。

有些同鞋可能对此有些疑惑，用int如何存储时间呢？

我们可以借助FROM_UNIXTIME()和UNIX_TIMESTAMP()这两个函数对我们的int进行转换，FROM_UNIXTIME()可以把我们的int类型的时间戳（时间戳就是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数）转换为标准的日期时间格式，而UNIX_TIMESTAMP()则可以把时间转换为int类型，因此如果我们想用int来对时间进行存储，就需要经常用到这两个函数，用法如下：

create table test(
id int auto_increment not null,
timeint int,
primary key(id)
);
insert into test(timeint) values (UNIX_TIMESTAMP('2014-06-01 13:12:00'));
select FROM_UNIXTIME(timeint) from test;

执行结果如下：

+------------------------+
| FROM_UNIXTIME(timeint) |
+------------------------+
| 2014-06-01 13:12:00    |
+------------------------+

如此一来，我们就达到了int来存取时间的目的。

 

我们再举一个存储ip的栗子：

在我们的一些web应用中，不可避免的会遇到对ip地址进存储，如用户注册IP、用户下订单的IP、访问的IP等。
这时我们首先想到的可能是varchar类型，因为192.168.48.189这种地址看起来就很像字符串，用字符串自然是比较方便。可我们应该知道的是，一个varchar在mysql中是15个字节，而一个bigint只占8个字节，如果我们用bigint来进行存储的话，那么这就近乎节省了一半的存储空间！

怎么做呢？
我们需要利用INET_ATON()、 INET_NTOA()这两个函数进行装换。

create table sessions(
id int AUTO_INCREMENT NOT NULL,
ipaddress BIGINT, 
PRIMARY KEY(id)
);

insert into sessions(ipaddress) values(INET_ATON('192.168.0.1'));

select INET_NTOA(ipaddress) from sessions;

查询结果如下：

+----------------------+
| INET_NTOA(ipaddress) |
+----------------------+
| 192.168.0.1          |
+----------------------+

千万不要小看这节省下来的7个字节，当数据两非常庞大的时候，它会为我们节省很大的存储空间，而且还会对我们查询操作的IO效率和内存带来可观的优化。

在今后的开发中，大家也可以经常去尝试这些mysql函数的使用，使用int或bigint来代替varchar的存储，字段的对比操作也会更加简单和高效。