MySQL - 库表设计之IP和TIMESTAMP的处理

叁歲伎倆 2022-11-28 13:34 134阅读 0赞

### 文章目录 ###

*  学习更多干货
 *  Pre
 *  TIMESTAMP
 *  IP
 *  总结

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70_pic_center]

--------------------

# 学习更多干货 #

[带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试][MySQL]

--------------------

# Pre #

[MySQL - 高效的设计MySQL库表][MySQL - _MySQL]

设计指导思想和注意事项都梳理了一下，那来个小练习把

--------------------

# TIMESTAMP #

我们使用 MySQL 内置的函数(FROM\_UNIXTIME()，UNIX\_TIMESTAMP())，可以将日期转化为数字，用 INT UNSIGNED 存储日期和时间

示例

时间 2020-08-17 22:22:22 与整数之间的转换，**转化后数字是连续的，占用空间更小，并且可以使用索引提升查询性能。**

mysql> select UNIX_TIMESTAMP('2020-08-17 22:22:22');
    +---------------------------------------+
    | UNIX_TIMESTAMP('2020-08-17 22:22:22') |
    +---------------------------------------+
    |                            1597674142 |
    +---------------------------------------+
    1 row in set
    
    mysql> select FROM_UNIXTIME(1597674142);
    +---------------------------+
    | FROM_UNIXTIME(1597674142) |
    +---------------------------+
    | 2020-08-17 22:22:22       |
    +---------------------------+
    1 row in set
    
    mysql>

mysql> select UNIX_TIMESTAMP(now());
    +-----------------------+
    | UNIX_TIMESTAMP(now()) |
    +-----------------------+
    |            1597674191 |
    +-----------------------+
    1 row in set
    
    mysql> select FROM_UNIXTIME(1597674191);
    +---------------------------+
    | FROM_UNIXTIME(1597674191) |
    +---------------------------+
    | 2020-08-17 22:23:11       |
    +---------------------------+
    1 row in set
    
    mysql>

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70_pic_center 1]

--------------------

# IP #

*  一般使用 Char(15) 进行存储，但是当进行查找和统计时，字符类型不是很高效。
 *  MySQL 数据库内置了两个 IP 相关的函数 INET\_ATON()、INET\_NTOA()，可以实现 IP 地址和整数的项目转换。

因此，我们使用 INT UNSIGNED（占用 4 个字节）存储 IP，非 Char(15)。占 15 个字节。

mysql> select INET_ATON('127.0.0.1');
    +------------------------+
    | INET_ATON('127.0.0.1') |
    +------------------------+
    |             2130706433 |
    +------------------------+
    1 row in set
    
    mysql> select  INET_NTOA(2130706433);
    +-----------------------+
    | INET_NTOA(2130706433) |
    +-----------------------+
    | 127.0.0.1             |
    +-----------------------+
    1 row in set
    
    mysql>

**将 IP 的存储从字符型转换成整形，转化后数字是连续的，提高了查询性能，使查询更快，占用空间更小。**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70_pic_center 2]

--------------------

# 总结 #

1.  以高性能为目标，库表设计以范式为主，根据特殊业务场景使用反范式，允许必要的空间换时间。
2.  规范数据库的使用原则，统一规范命名，减少性能隐患，减少隐式转换。
3.  高性能表设计的原则：合适的字段、合适的长度、NOT NULL。
4.  从不同角度思考 IP、timestamp 的转换，拓宽设计思路。
5.  规范的命名可提高可读性，反范式设计可提高查询性能。

--------------------

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221124/6e4d315da9f14d6091fa924684e4f3fb.png
[MySQL]: https://marketing.csdn.net/poster/3766
[MySQL - _MySQL]: https://artisan.blog.csdn.net/article/details/108044300
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221124/0a0488d88b2846c6bdff2f37b513d696.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221124/460608a004a94a80a8591f86b96ad026.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70]: /images/20221124/1a3fab984d604bf1a6267a4d78a1107e.png