MySQL:在本例中，使用例程查找匹配项的速度不应该快得多

Question

此查询需要0.0002秒才能执行：

SELECT country,city
FROM location
WHERE locID = 30296
LIMIT 1

locID显然是一个索引

另一个查询使用一个例程，执行需要0.0005秒（返回30296）：

SELECT IPTOLOCID(
'190.131.60.58'
)

那么，为什么这个组合查询需要1.7912秒才能执行呢？似乎远远超出了应有的水平：

SELECT country, city
FROM location
WHERE locID = IPTOLOCID('190.131.60.58')
LIMIT 1

如果你觉得这很有用，下面是表格和例程：

CREATE TABLE  `blocks` (
  `startIPNum` int(10) unsigned NOT NULL,
  `endIPNum` int(10) unsigned NOT NULL,
  `locID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`startIPNum`,`endIPNum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 DELAY_KEY_WRITE=1;
CREATE TABLE  `location` (
  `locID` int(10) unsigned NOT NULL,
  `country` char(2) default NULL,
  `region` char(2) default NULL,
  `city` varchar(45) default NULL,
  `postalCode` char(7) default NULL,
  `latitude` double default NULL,
  `longitude` double default NULL,
  `dmaCode` char(3) default NULL,
  `areaCode` char(3) default NULL,
  PRIMARY KEY  (`locID`),
  KEY `Index_Country` (`country`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;
DELIMITER $$
DROP FUNCTION IF EXISTS `IPTOLOCID` $$
CREATE FUNCTION `IPTOLOCID`( ip VARCHAR(15)) RETURNS int(10) unsigned
BEGIN
  DECLARE ipn INTEGER UNSIGNED;
  DECLARE locID_var INTEGER;
  IF ip LIKE '192.168.%' OR ip LIKE '10.%' THEN
    RETURN 0;
  END IF;
  SET ipn = INET_ATON(ip);
  SELECT locID INTO locID_var
    FROM `blocks`
    INNER JOIN
      (SELECT MAX(startIPNum) AS start
       FROM `blocks`
       WHERE startIPNum <= ipn) AS s
    ON (startIPNum = s.start)
    WHERE endIPNum >= ipn;
  RETURN locID_var;
END $$
DELIMITER ;

Answer 1

我不知道为什么上一个答案被否决了，但他说得对。该函数针对location表中的每一行执行，因为：

• 函数定义中没有提供[NOT] DETERMINISTIC子句，因此假定为NOT DETERMINISTIC
• LIMIT子句在过程的最后应用，此时所有行都已扫描完毕，并且每个行都已检查了WHERE条件

如果优化器决定不使用索引，我也不会感到惊讶，因为所有行最终都会被扫描。您可以使用EXPLAIN 进行检查

如果您将函数重新定义为DETERMINISTIC，也可以添加READS SQL DATA子句以避免任何意外。

顺便说一句，就其结果而言，这个函数是无意义的。这应该作为一个视图来实现（这样问题就不适用了）。

Answer 2

运行查询时，首先执行SELECT，然后WHERE进入并开始筛选SELECT。因此，在查询中，每一行SELECT都会运行IPTOLOCID函数，然后输出数据。