IP地址的存储和使用
2024-09-01 11:46:59
ip地址使用int类型存储，用INET_NTOA()和INET_ATON()转换
mysql> select inet_ntoa(''),inet_aton('127.0.0.1');
+-------------------------+------------------------+
| inet_ntoa('') | inet_aton('127.0.0.1') |
+-------------------------+------------------------+
| 127.0.0.1 | 2130706433 |
+-------------------------+------------------------+
1 row in set (0.00 sec)
1.环境
mysql ----5.6.13 mysql> show create table test \G;
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ip_from` int(10) unsigned DEFAULT NULL,
`ip_to` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_ip` (`ip_from`,`ip_to`),
KEY `idx_ip_from` (`ip_from`)
) ENGINE=InnoDB AUTO_INCREMENT=9568111 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.01 sec)
ERROR:
No query specified ------------------------------------------------------
mysql> show index from test;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 0 | PRIMARY | 1 | id | A | 9289578 | NULL | NULL | | BTREE | | |
| test | 1 | idx_ip | 1 | ip_from | A | 9289578 | NULL | NULL | YES | BTREE | | |
| test | 1 | idx_ip | 2 | ip_to | A | 9289578 | NULL | NULL | YES | BTREE | | |
| test | 1 | idx_ip_from | 1 | ip_from | A | 9289578 | NULL | NULL | YES | BTREE | | |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec) mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 9541210 |
+----------+
1 row in set (2.84 sec)
2.使用
查询某个值属于哪个ip段。
- SELECT * FROM test WHERE ip_from<=2352356 AND ip_to>=2352356;
mysql> explain SELECT * FROM test WHERE ip_from<=2352356 AND ip_to>=2352356;
+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+
| 1 | SIMPLE | test | range | idx_ip,idx_ip_from | idx_ip | 5 | NULL | 1 | Using index condition |
+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+
1 row in set (0.08 sec)
- 这个方式对索引进行了范围全扫描，耗时较长。
- SELECT * FROM test WHERE id IN ( SELECT id FROM test WHERE ip_from<=2352356 AND ip_to>=2352356 );
mysql> EXPLAIN SELECT * FROM test WHERE id IN (
-> SELECT id FROM test WHERE ip_from<=2352356 AND ip_to>=2352356 );
+----+-------------+-------+--------+----------------------------+---------+---------+---------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------------+---------+---------+---------------------+------+--------------------------+
| 1 | SIMPLE | test | range | PRIMARY,idx_ip,idx_ip_from | idx_ip | 5 | NULL | 1 | Using where; Using index |
| 1 | SIMPLE | test | eq_ref | PRIMARY | PRIMARY | 8 | ip2location.test.id | 1 | NULL |
+----+-------------+-------+--------+----------------------------+---------+---------+---------------------+------+--------------------------+
2 rows in set (0.01 sec)
mysql> status;
--------------
mysql Ver 14.14 Distrib 5.5.35, for debian-linux-gnu (x86_64) using readline 6.2
Connection id: 4305567
Current database: ip2location
Current user: ip2location@10.1.255.10
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.13-log MySQL Community Server (GPL)
Protocol version: 10
Connection: ip2location.cgs2bjzqxcxl.us-east-1.rds.amazonaws.com via TCP/IP
Insert id: 1
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 30 days 18 hours 51 min 44 sec
Threads: 4 Questions: 21017670 Slow queries: 4 Opens: 188007 Flush tables: 1 Open tables: 147 Queries per second avg: 7.901 --------------------------------------------- mysql> EXPLAIN SELECT * FROM test WHERE id IN (
-> SELECT id FROM test WHERE ip_from<=2352356 AND ip_to>=2352356 );
+----+--------------------+-------+-----------------+----------------------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-----------------+----------------------------+---------+---------+------+--------+-------------+
| 1 | PRIMARY | test | ALL | NULL | NULL | NULL | NULL | 206509 | Using where |
| 2 | DEPENDENT SUBQUERY | test | unique_subquery | PRIMARY,idx_ip,idx_ip_from | PRIMARY | 8 | func | 1 | Using where |
+----+--------------------+-------+-----------------+----------------------------+---------+---------+------+--------+-------------+
2 rows in set (0.00 sec)
mysql> status;
--------------
mysql Ver 14.14 Distrib 5.5.37, for Linux (x86_64) using EditLine wrapper
Connection id: 5
Current database: howe
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.37-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /home/mysql/mysql5/tmp/mysql.sock
Uptime: 6 min 52 sec
Threads: 3 Questions: 208 Slow queries: 0 Opens: 112 Flush tables: 1 Open tables: 105 Queries per second avg: 0.504
--------------
不同版本对IN的处理方式不同，5.6优于以前的版本
- SELECT * FROM test WHERE ip_from<=2352356 ORDER BY ip_from DESC LIMIT 1;
mysql> explain SELECT * FROM test WHERE ip_from<=2352356 ORDER BY ip_from DESC LIMIT 1;
+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+
| 1 | SIMPLE | test | range | idx_ip,idx_ip_from | idx_ip | 5 | NULL | 1 | Using index condition |
+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
删除idx_ip索引。
mysql> explain SELECT * FROM test WHERE ip_from<=2352356 ORDER BY ip_from DESC LIMIT 1;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| 1 | SIMPLE | test | range | idx_ip_from | idx_ip_from | 5 | NULL | 1 | Using index condition |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
这个方式是最优。利用了ip段的特性、order by、limit。
最新文章
- asp.net Word Document Open return null
- Ajax参数详解
- 体验最火的敏捷——SCRUM（厦门，2014.1.4）
- [转]asp.net webform 与mvc 共享session
- HackRF实现无线门铃信号分析重放
- 初识 Asp.Net内置对象之Session对象
- discuz x3插件开发傻瓜图文教程，用demo说话
- 【无聊放个模板系列】HDU 1269 （SCC）
- 【USACO 2.3.5】控制公司
- Java的按位操作符
- 2015 多校联赛 ——HDU5299（树删边）
- Qt贴图实现地图标记效果
- soapUI-property Transfer
- iOS菊花加载圈
- CORS 跨域请求
- 【vue】——CDN或全局引入CSS、JS。
- Pandas DataFrame构造简析
- ibatis 批量插入
- Gartner提出的7种多租户模型
- （3.9）常用知识-标识值（identity）的不连续与强行插入、计算列