這樣優化MySQL的IP位址存儲方式，存儲查詢效率提升100倍

在 MySQL 中，IP 地址是一个非常重要的数据类型，我们经常需要在数据库中存储和查询大量的 IP 地址数据。

同时，存储 IP 地址数据又是一个比较复杂的问题，如果选错了存储方式，查询速度就会很慢。

我去年曾经做过一次优化，仅仅只是改变了数据类型，效率就提高了至少 100 倍，原本要 4、5 天完成的计算任务，结果两小时不到就全部搞定了......

本篇重点介绍 MySQL 的几种常见 IP 地址存储方式的使用和优化，详解 IP 地址转换为整数存储的方法等，为 IP 地址存储方式的正确选型提供参考。

01

IP地址

先来回顾下 IP 地址的一些基础知识。

IP 地址是由 32 位或 128 位二进制数字组成的，主要用来标识设备的地址。

每个IP 地址都由网络地址和主机地址两部分组成。

• 网络地址用于标识设备所在的网络
• 主机地址则用于标识设备在该网络中的具体位置

IPv4 和 IPv6 都是 IP 地址的标准，它们的区别在于：

• IPv4 使用 32 位二进制数字表示 IP 地址，地址空间非常有限。
• IPv6 使用 128 位二进制数字表示 IP 地址，主要解决 IPv4 地址短缺的问题。

02

为什么要优化 IP 地址的存储方式

在 MySQL 中，IP 地址存储方式的选择，将直接影响到 MySQL 的存储空间和查询速度。

存储空间

在 MySQL 中，存储 IP 地址的数据类型有多种选择，不同的数据类型占用的存储空间不同。

例如：使用 CHAR 或 VARCHAR 类型存储 IP 地址，导致了存储空间占用。这时我们就需要对其进行优化，通过使用 INT UNSIGNED 或 BINARY 类型来节省存储空间。

查询速度

使用不同的数据类型，查询速度、转换速度也会有差异。

PS. 在 MySQL 中，查询时需要将 IP 地址转换成十进制数或二进制数。

使用 INT UNSIGNED 或 BINARY 类型存储 IP 地址的查询速度快，而使用 CHAR 或 VARCHAR 类型存储 IP 地址的查询速度就要相对慢一些。

选择适合的 MySQL IP 地址数据类型可以节省存储空间，并且可以提高查询速度。

03

常见的 IP 地址数据类型

在 MySQL 中，有三种常见的 IP 地址数据类型：

• CHAR(n)
• VARCHAR(n)
• INT UNSIGNED

3.1 CHAR(n) / VARCHAR(n)

CHAR(n) / VARCHAR(n) 类型是什么

是最常见的存储IP地址的方法之一，通常使用定长或变长字符串来存储 IP 地址，例如：VARCHAR(15) 可以存储一个 IPv4 地址。

CHAR(n) / VARCHAR(n) 类型的优点

• 数据可读性强，易于理解和维护。
• 存储较小的IPv4地址时，存储空间占用不大。

CHAR(n) / VARCHAR(n) 类型的缺点

• 存储 IPv6地址时，存储空间占用较大。
• 查询时需要使用函数转换为数字或二进制格式，会降低查询速度。

CHAR(n) / VARCHAR(n) 类型的应用场景

CHAR(n) / VARCHAR(n) 适用于存储 IPv4 地址，例如：存储用户登录的 IP 地址、网站访问日志中的 IP 地址等。

示例：

CREATE TABLE user (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
  ip_address VARCHAR(15) NOT NULL,
  PRIMARY KEY (id)
);

           

3.2 INT UNSIGNED 类型

INT UNSIGNED 类型是另一种存储 IP 地址的方法，它使用 32 位无符号整数来存储 IP 地址，将每个 IP 地址转换为一个数字。

INT UNSIGNED 类型的优点

• 存储空间占用较小。
• 查询速度较快。

INT UNSIGNED 类型的缺点

• 存储 IPv6 地址时，需要占用 64 位空间。
• 数据可读性较差，不易于理解和维护。

INT UNSIGNED 类型的应用场景

INT UNSIGNED 适用于存储 IPv4 和 IPv6 地址，例如：存储网络设备配置的 IP 地址、地理位置信息等。

示例：

CREATE TABLE device (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
  ip_address INT UNSIGNED NOT NULL,
  PRIMARY KEY (id)
);           

我们在选择存储 IP 地址的方法时，依据实际情况来选择最合适的数据类型。

• 如果只需要存储少量 IPv4 地址，可以使用 CHAR(n) / VARCHAR(n) 类型。
• 如果需要存储大量 IPv4 或 IPv6 地址，应该选择 INT UNSIGNED 类型。

3.3 BINARY(4) / VARBINARY(4)

BINARY(4) / VARBINARY(4) 的优缺点

BINARY(4) / VARBINARY(4) 数据类型是一种比较新颖的 IP 地址存储方式。

与前面介绍的 CHAR 和 INT 数据类型相比，BINARY(4) / VARBINARY(4) 数据类型占用存储空间小，查询速度也很快。

与 INT 数据类型相比，BINARY(4) / VARBINARY(4) 数据类型也支持 IPv6 地址。

BINARY(4) / VARBINARY(4) 的应用场景

基于 BINARY(4) / VARBINARY(4) 数据类型存储空间占用小、查询速度快的特性，它更适用于存储大量 IP 地址的场景。

例如：

在某些数据统计场景下，需要对大量的用户 IP 地址进行统计分析，这时候我们使用 BINARY(4) / VARBINARY(4) 数据类型可以有效减少存储空间，同时提高查询效率。

示例：使用 BINARY(4) 数据类型存储 IPv4 地址

CREATE TABLE `user_login` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(32) NOT NULL,
`ip_address` BINARY(4) NOT NULL,
`login_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `user_login` (`username`, `ip_address`) 
VALUES ('user1', INET_ATON('192.168.1.1'));


SELECT `username`, INET_NTOA(`ip_address`) AS `ip_address`, `login_time`
FROM `user_login`
WHERE `ip_address` = INET_ATON('192.168.1.1');

           

上面的示例中，ip_address 字段使用了 BINARY(4) 数据类型来存储 IPv4 地址，同时使用了 INET_ATON() 函数和 INET_NTOA() 函数来进行 IP 地址和二进制数据的转换。

3.4 如何选择最适合 IP 地址存储方式？

在选择 IP 地址存储方式时，我们需要综合考虑以下两个因素。

存储空间大小

存储空间是需要考虑的重要因素之一。

IP 地址通常存储为四个整数，存储空间可以通过减少每个整数的存储位数来进行优化。

上文我们了解到 BINARY(4) / VARBINARY(4) 是存储 IP 地址最小的数据类型，CHAR(n) / VARCHAR(n) 是存储空间最大的数据类型。

如果环境需要存储大量的 IP 地址，就应该选择占用更少存储空间的数据类型。

在存储空间受限的环境中，例如移动设备或嵌入式系统，BINARY(4) / VARBINARY(4) 是首选的数据类型。

查询速度如何

一般情况下，查询速度与数据类型的存储方式有关。

前面我们已经介绍了，INT UNSIGNED和BINARY(4) / VARBINARY(4) 比 CHAR(n) / VARCHAR(n) 查询速度更快。

如果场景中需要频繁地查询 IP 地址，就要选择查询速度更快的数据类型。在需要进行复杂查询或聚合操作时，BINARY(4) / VARBINARY(4) 的性能表现会更好。

支持 IPv6

IPv6 是下一代互联网协议，与 IPv4 相比，IPv6 提供了更多的 IP 地址。

IPv6 地址由 8 个 16 位的整数表示，可以使用 BINARY(16) / VARBINARY(16) 数据类型进行存储。

如果需要支持 IPv6 地址，建议选择 BINARY(16) / VARBINARY(16) 数据类型。如果需要支持 IPv4 地址，则建议选择 BINARY(4) / VARBINARY(4) 或 INT UNSIGNED 数据类型。

04

如何将 IP 地址转换为整数存储？

在 MySQL 中，将 IP 地址转换为整数存储，可以进一步优化存储和查询速度。

4.1 将IP 地址转换为整数存储

下面这个示例展示了将 IP 地址转换为整数存储的方法。

将每个 IP 地址分段转换为 2 进制数，并将这些二进制数合并为一个 32 位的二进制数。

例如：IP 地址“192.168.1.1”可以转换成二进制数“11000000 10101000 00000001 00000001”，它所对应的十进制整数是“3232235777”。

示例：IP 地址转换为整数的 SQL

SELECT INET_ATON('192.168.1.1') as ip_integer;           

这个 SQL 语句将返回整数“3232235777”。

4.2 将整数转换为 IP 地址

将 32 位二进制数分为 4 个 8 位二进制数，并将每个 8 位二进制数转换为十进制数，最终合并为 IP 地址。

例如，整数“3232235777”可以转换为IP地址“192.168.1.1”。

示例：将整数转换为 IP 地址的 SQL

SELECT INET_NTOA(3232235777) as ip_address;           

这个 SQL 语句将返回 IP 地址“192.168.1.1”。

整数存储 IP 地址，表结构如下：

CREATE TABLE users (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
  ip_address INT UNSIGNED
);           

在这个示例中，IP 地址被存储为整数，使用 INT UNSIGNED 数据类型。

将 IP 地址转换为整数并将其插入到表中的 SQL：

INSERT INTO users (name, ip_address)
VALUES ('John', INET_ATON('192.168.1.1')),
       ('Mary', INET_ATON('10.0.0.1'));           

这个 SQL语句将返回 IP 地址为“192.168.1.1”的用户的姓名。

可见，将 IP 地址转换为整数存储，是可以提高 MySQL 中 IP 地址的存储和查询速度的。

另外，我们还可以使用 MySQL 提供的函数，将 IP 地址和整数相互转换。

05

总结

通过本文，我们了解到了 IP 地址的定义和作用、IPv4 和 IPv6 的区别、IP 地址存储方式，常见的 三种 IP 地址数据类型CHAR(n) / VARCHAR(n)、INT UNSIGNED 及 BINARY(4) / VARBINARY(4) ，对如何选择数据类型的有了更清晰的理解。

在实际应用中，需要结合数据类型的特性以及应用场景综合考虑。

可能有同学会说，大部分情况下，节省这点空间和效率是微不足道的。但是，如果我们的运算或存储执行是几十亿、几百亿次呢，这个时候的一丁半点的优化改进，就能带来明显的优化效果。

我是爱分享的程序员宝妹儿，谢谢关注 Java面试题宝。

如果觉得不错，请一键三连支持下。

--- end---