ip地址使用int类型存储，用INET_NTOA()和INET_ATON()转换
mysql> select inet_ntoa('2130706433'),inet_aton('127.0.0.1'); +-------------------------+------------------------+ | inet_ntoa('2130706433') | 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。