背景说明
mysql将ip由varchar或char转为int型,在《高性能mysql》中介绍能用int尽量使用int型。某些类型的数据并不直接与内置类型一致。ipv4地址,人们经常使用varchar(15)列来存储IP地址。然而,它们实际上是32位无符号整数。不是字符串。用小数点将地址分成四段的表示方法只是为了让人们阅读容易。所以应该用无符号号数存储IP地址。mysql提供inet_aton()和inet_ntoa()函数在这两种表示方法之间转换。
在mysql能用int型表示尽量使用int型,不使用varchar。下面从存储空间、存取效率对ip为varchar(15)及转为int后的测试。
其他说明
独立表空间默认打开,如图1
图1
相关代码
建表语句
说明:ip_str表主要存储字符串IP,ip_num存储将IP转化为数字后的IP。
/*建表语句*/
DROP TABLE IF EXISTS ip_str;
CREATE TABLE ip_str
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ip_addr VARCHAR(15) NOT NULL ,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS ip_num;
CREATE TABLE ip_num
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ip_addr INT UNSIGNED NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
存储过程代码
填充ip_num表数据存储过程代码:
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `insert_ip_num`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_ip_num`(num INT)
BEGIN
DECLARE i INT;
SET i=0;
SELECT NOW();
WHILE i<num DO
SET @str=INET_ATON(CONCAT(FLOOR(1 + (RAND() * 255)),'.',FLOOR(1 + (RAND() * 255)),'.',FLOOR(1 + (RAND() * 255)),'.',FLOOR(1 + (RAND() * 255))));
INSERT INTO ip_num(ip_addr)VALUE(@str);
SET i=i+1;
END WHILE;
SELECT NOW();
END$$
填充ip_str表数据存储过程代码:
DELIMITER ;
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `insert_ip_str`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_ip_str`(num INT)
BEGIN
DECLARE i INT;
SET i=0;
SELECT NOW();
WHILE i<num DO
SET @str=CONCAT(FLOOR(1 + (RAND() * 255)),'.',FLOOR(1 + (RAND() * 255)),'.',FLOOR(1 + (RAND() * 255)),'.',FLOOR(1 + (RAND() * 255)));
INSERT INTO ip_str(ip_addr)VALUE(@str);
SET i=i+1;
END WHILE;
SELECT NOW();
END$$
DELIMITER ;
测试对比
insert对比
分别写入300万数据量,写入花费时间如图1、图2。
ip设置为字符型,写入300万花费时间为53分34.92秒,如图2:
图2
ip设置为字符型,写入300万花费时间为48分29.76秒,如图3:
图3
select对比
两表数据分别追加到3千万,分别
对表中某一IP进行查询。
对ip_str表中ip_addr为‘47.175.221.69’的记录进行查询,所花费的时间为11.91秒,如图4:
图4
对ip_str表中ip_addr为‘47.175.221.69’的记录进行查询,所花费的时间为7.54秒,如图5
图5
存储空间对比
ip_str表与ip_num表同为3千万数据量的情况下,ip_str占用1.2G的磁盘空间,ip_num占用840M的磁盘空间,如图6
图6
结论
将ip转化为int型存储,存取的效率要相对于varchar(15)要高,本测试中数据库实例为纯净的测试环境。若在生产环境中,其复杂程度会高多,存取效率间差距会比这简单的测试结果要大得多。
注:
在测试时先在mysql中执行“set global
innodb_flush_log_at_trx_commit =2
”可以加快写的速度。