mySQL查询优化(RANGE)
问题描述:
好吧,这样查询就简单了。这是它被存储在表中:mySQL查询优化(RANGE)
CREATE TABLE `ips` (
`ip` int(11) unsigned NOT NULL DEFAULT '0',
`ip_start` int(11) unsigned NOT NULL DEFAULT '0',
`ip_end` int(11) unsigned NOT NULL DEFAULT '0',
KEY `IP` (`ip`),
KEY `IP_RANGE_END` (`ip_end`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
此表中填充有任一单数的IP(在IP列)或IP范围(ip_start到ip_end)。
下面是我使用的功能:
CREATE DEFINER=`root`@`%` FUNCTION `in_adometry`(in_ip VARCHAR(30)) RETURNS int(11)
DETERMINISTIC
BEGIN
SET @ip = 0;
SELECT COUNT(*) INTO @ip FROM ips WHERE (INET_ATON(in_ip) BETWEEN ip_start AND ip_end) OR ip = INET_ATON(in_ip) LIMIT 1;
IF @ip > 0 THEN
RETURN 1;
END IF;
RETURN 0;
END
我目前主办这次在亚马逊的RDS(中等大小),我不能得到任何更多的则800查询第二个CPU的点击量超过之前100%。在这个RDS中没有其他的东西,但是这个表格。有一百一十万个单一的IP,其次是约三千个IP范围(超过二千万个IP地址)。我能做些什么来加快速度?
答
一个可能的原因慢查询是(从MySQL文档):
与使用MEMORY存储引擎,如果运行检查值的范围(使用运算符,例如>,< =查询表或索引列上的BETWEEN),使用USING BTREE子句创建索引。使用等号运算符(=或< =>)检索单个行的默认(USING HASH)很快,但速度要慢得多(需要全表扫描)才能检查一系列列值。使用USING BTREE子句创建的MEMORY表对于相等比较仍然很快,所以对于处理各种查询的MEMORY表使用该子句。
更改索引类型可以显着提高您的查询速度。请阅读Avoid Full Table Scan了解更多信息。
答
做count(*)
可能是不必要的。试试这个:
BEGIN
IF exists (SELECT 1
FROM ips
WHERE INET_ATON(in_ip) BETWEEN ip_start AND ip_end OR ip = INET_ATON(in_ip)
)
THEN
RETURN 1;
END IF;
RETURN 0;
这是否与我使用INNODB表的事实相关吗? –
在您的电脑上运行EXPLAIN。这将澄清您的查询是否正在进行全表扫描。 – RaviH
没有全表扫描,尽管在我的两个索引上做了union_join。 –