弄了一天了,还是请教大家。
要求:获得proxy每个IP对应chunzhenip的IP段
说明:chunzhenip.end是从小到大排列,主键,比较proxy.ip<chunzhenip.end可以得到确定该IP对应的IP段
下面是表结构:
CREATE TABLE `proxy` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ip` int(10) unsigned NOT NULL DEFAULT '0',
`port` int(11) NOT NULL,
`type` varchar(6) DEFAULT NULL,
`level` int(11) DEFAULT NULL,
`code` char(2) DEFAULT NULL,
`date` char(19) NOT NULL,
`ping` int(11) DEFAULT '60000',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=3057 DEFAULT CHARSET=gbkCREATE TABLE `chunzhenip` (
`start` int(10) unsigned NOT NULL DEFAULT '0',
`end` int(10) unsigned NOT NULL DEFAULT '0',
`location` varchar(100) NOT NULL,
PRIMARY KEY (`end`),
UNIQUE KEY `end` (`end`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=gbk希望大家帮忙写出高效的SQL语句。谢谢!
要求:获得proxy每个IP对应chunzhenip的IP段
说明:chunzhenip.end是从小到大排列,主键,比较proxy.ip<chunzhenip.end可以得到确定该IP对应的IP段
下面是表结构:
CREATE TABLE `proxy` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ip` int(10) unsigned NOT NULL DEFAULT '0',
`port` int(11) NOT NULL,
`type` varchar(6) DEFAULT NULL,
`level` int(11) DEFAULT NULL,
`code` char(2) DEFAULT NULL,
`date` char(19) NOT NULL,
`ping` int(11) DEFAULT '60000',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=3057 DEFAULT CHARSET=gbkCREATE TABLE `chunzhenip` (
`start` int(10) unsigned NOT NULL DEFAULT '0',
`end` int(10) unsigned NOT NULL DEFAULT '0',
`location` varchar(100) NOT NULL,
PRIMARY KEY (`end`),
UNIQUE KEY `end` (`end`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=gbk希望大家帮忙写出高效的SQL语句。谢谢!
select t1.*,t2.* from proxy as t1 left join chunzhenip as t2 on(t1.ip>t2.start && t1.ip<t2.end);
//ip 建索引
EXPLAIN select t1.*,t2.* from proxy as t1 left join chunzhenip as t2 on(t1.ip>t2.start && t1.ip
=====================( 0 )====================
id : 1
select_type : SIMPLE
table : t1
type : ALL
possible_keys :
key :
key_len :
ref :
rows : 3056
Extra :
=====================( 1 )====================
id : 1
select_type : SIMPLE
table : t2
type : ALL
possible_keys : PRIMARY,end
key :
key_len :
ref :
rows : 370000
Extra :
`start` int(10) unsigned NOT NULL DEFAULT '0',
`end` int(10) unsigned NOT NULL DEFAULT '0',
`location` varchar(100) NOT NULL,
PRIMARY KEY (`end`),
UNIQUE KEY `end` (`end`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=gbk
这张表的 start end 都建索引.
UNIQUE KEY `id` (`id`) USING BTREE
这一句不是吗?
EXPLAIN select t1.*,t2.* from proxy as t1 left join chunzhenip as t2 on(t1.ip>t2.start && t1.ip
=====================( 0 )====================
id : 1
select_type : SIMPLE
table : t1
type : ALL
possible_keys :
key :
key_len :
ref :
rows : 3054
Extra :
=====================( 1 )====================
id : 1
select_type : SIMPLE
table : t2
type : ALL
possible_keys : PRIMARY,end,start
key :
key_len :
ref :
rows : 370000
Extra : 两表已建索引CREATE TABLE `chunzhenip` (
`start` int(10) unsigned NOT NULL DEFAULT '0',
`end` int(10) unsigned NOT NULL DEFAULT '0',
`location` varchar(100) NOT NULL,
PRIMARY KEY (`start`,`end`),
UNIQUE KEY `end` (`end`) USING BTREE,
UNIQUE KEY `start` (`start`)
) ENGINE=MyISAM DEFAULT CHARSET=gbkCREATE TABLE `proxy` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ip` int(10) unsigned NOT NULL DEFAULT '0',
`port` int(11) NOT NULL,
`type` varchar(6) DEFAULT NULL,
`level` int(11) DEFAULT NULL,
`code` char(2) DEFAULT NULL,
`date` char(19) NOT NULL,
`ping` int(11) DEFAULT '60000',
PRIMARY KEY (`id`),
UNIQUE KEY `ip` (`ip`) USING BTREE,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3055 DEFAULT CHARSET=gbk
=====================( 0 )====================
id : 1
select_type : SIMPLE
table : t2
type : index
possible_keys : PRIMARY,end,start
key : PRIMARY
key_len : 8
ref :
rows : 370000
Extra : Using index
=====================( 1 )====================
id : 1
select_type : SIMPLE
table : t1
type : ALL
possible_keys : PRIMARY,ip
key :
key_len :
ref :
rows : 3054
Extra : Range checked for each record (index map: 0x5)
用时:12秒
现在需要进行速度优化了
每个IP段首位相连,例如1-3,4-8。
start end
1 3
4 8
9 18
当ip是6,将chunzhenip表按end由小到大排序(本身就已排序),可以直接查询第一个end>ip。
请问如何实现?