我正在使用MySQL 5.6,而我的存储引擎是InnoDB.
我有一个包含一百万行的表,其中包含各列:
> ID(主键)
>名字
>姓氏
> foreign_key_id(外键,非空)
> Foreign_key_id2(另一个外键,默认为NULL)
这些行在下面分开:
> 25%,foreign_key_id值为1,foreign_key_id2为NULL
> 25%,foreign_key_id值为1,foreign_key_id2不为空
> 25%,foreign_key_id值为2,foreign_key_id2为NULL
> 25%,foreign_key_id值为2,foreign_key_id2不为空
具有以下索引:
>在foreign_key_id上索引foreign_key_idx
>索引foreign_key_2_idx和foreign_key_id2
>上的综合索引foreign_key_comp_idx(foreign_key_idx,foreign_key_2_idx)
我执行以下查询:
Query 1 – without indexes:
SELECT *
FROM table tbl
IGNORE INDEX(foreign_key_idx, foreign_key_2_idx, foreign_key_comp_idx)
WHERE tbl.foreign_key_id = 1 AND tbl.foreign_key_id2 IS NOT NULL
Query 2 – with indexes (no composite index):
SELECT *
FROM table tbl
IGNORE INDEX(foreign_key_comp_idx)
WHERE tbl.foreign_key_id = 1 AND tbl.foreign_key_id2 IS NOT NULL
Query 3 – with composite index (no other indexes):
SELECT *
FROM table tbl
IGNORE INDEX(foreign_key_idx, foreign_key_2_idx)
WHERE tbl.foreign_key_id = 1 AND tbl.foreign_key_id2 IS NOT NULL
结果:
Query 1 (no indexes) performs a full table scan and uses 1 million records with a
total duration of 0.37 seconds.Query 2 (indexes, no composite index) performs a non-unique key lookup on foreign_key_idx index and
uses 500K records with a total duration of 0.6 seconds.Query 3 (composite index only) performs an index range scan on composite index and uses 480K
records with a total duration of 0.13 seconds.
我真正不明白的是:为什么查询2(带有索引)总是比查询1(没有索引)慢?我真的很困,需要一些帮助…
我已经用不同的行数(例如1k,10k,20k,50k,100k,200k,250k,500k,1M等)测试了上面的查询,始终使用相同的比率(25%),并且结果相同(查询2总是执行缓慢)
在此先感谢您,非常感谢您的任何投入!
编辑(2016年5月2日)
显示创建表命令:
CREATE TABLE `table` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`FirstName` varchar(255) NOT NULL,
`LastName` varchar(255) NOT NULL,
`foreign_key_id` int(11) NOT NULL,
`foreign_key_id2` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `foreign_key_idx` (`foreign_key_id`),
KEY `foreign_key_2_idx` (`foreign_key_id2`),
KEY `foreign_key_comp_idx ` (`foreign_key_id`,`foreign_key_id2`),
CONSTRAINT `foreign_key_idx` FOREIGN KEY (`foreign_key_id`) REFERENCES `table2` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `foreign_key_2_idx` FOREIGN KEY (`foreign_key_id2`) REFERENCES `table3` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
) ENGINE=InnoDB AUTO_INCREMENT=1515998 DEFAULT CHARSET=latin1
不确定是否重要,但是table2有20条记录,table3也有100万条记录.
解决方法:
让我吃惊的是查询3比查询1快:-)
您需要表记录的25%.因此,简单地顺序读取表应该是最快的方法. (至少这是我会做的,在这种情况下大多数DBMS会做.)
可以使用复合索引,因为它足以知道选择哪些记录.但是,经过一棵树最终只能得到必须逐个访问的所有记录的25%,这似乎是一项艰巨的任务.如前所述,令人惊讶的是它的运行速度比全表扫描更快.也许物理记录恰好按需要排序,所以您不必从一个部分到另一个部分来回移动,这通常是从索引中获取时发生的情况. (说明:假设您在磁盘上表节A的索引中找到了匹配的记录引用,下一个匹配项恰好在扇区B中,第三个匹配项又在扇区A中,…可能需要很长时间.如果幸运的是,您首先在一个扇区中找到所有记录,然后又在另一个扇区中发现.通过全表扫描,您可以逐个扇区地读取扇区,而不必从一个切换到另一个,然后又返回,因此,保证了全表扫描相当快,而通过索引的访问速度可能快或慢.)
现在查询2:索引仅指向可能匹配的记录(表的记录的50%,其中只有一半是匹配的).这意味着您仅需阅读所描述的树,即可读取表记录的一半.这是太多的工作.