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

末蓝、 2022-01-14 03:03 210阅读 0赞

*  您可以到这里下载本篇博文所使用的数据库以及优化工具mysqldumpslow和pt-query-digest：
 *  [https://blog.csdn.net/IT\_TIfarmer/article/details/92224646][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的存储，字段的对比操作也会更加简单和高效。

[https_blog.csdn.net_IT_TIfarmer_article_details_92224646]: https://blog.csdn.net/IT_TIfarmer/article/details/92224646