查询优化

Question

SELECT nar.name, nar.reg, stat.lvl
FROM members AS nar
JOIN stats AS stat 
ON stat.id = nar.id
WHERE nar.ref = 9

我在两个表中都有id的索引，我也有索引referavo。但是，它仍然会检查stats表中的所有行(我使用Explain来获取此信息)，但在members表中，它只检查一行。统计表有什么问题？非常感谢。

CREATE TABLE `members` (
 `id` int(11) NOT NULL
 `ref` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT


CREATE TABLE `stats` (
 `id` int(11) NOT NULL AUTO_INCREMENT
 PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC



id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE stat ALL PRIMARY NULL NULL NULL 22  
1 SIMPLE nar eq_ref PRIMARY PRIMARY 4 table_nme.stat.id 1 Using where

Answer 1

您的表小得离谱--只有23行是tiny。

MySQL根据表中的行数以及它估计将选择的行数(从统计数据中)选择不同的查询计划。您应该使用真实的数据来测试您的查询性能--数据量和数据中的值分布都应该尽可能真实。否则，MySQL在测试中选择的查询计划可能与活动系统的实际查询计划不同。

您的表非常小，因此使用索引可能比直接检查表的慢。请记住，检查已在内存中的数据速度很快，但读取速度很慢。访问索引可能需要额外的读操作--首先必须获取和读取索引以查找要选择的行，然后如果您的索引不是覆盖索引，则必须获取和读取表中的相关行以获取索引中不存在的值。MySQL完全有权不使用索引，即使有可用的索引，如果它认为这样做会导致较慢的计划。

在您的表中添加更多行(数千行)，然后再次尝试运行EXPLAIN。您可能会发现，当您有更多的行时，主键索引将用于连接。

Answer 2

对于每个表，MySQL一次只能使用一个索引，因此它使用索引查看member行，然后对ID执行顺序搜索。

您必须为members表创建一个多列索引

 CREATE INDEX idref ON members(id,ref);

如果没有变得更好，请尝试相反的方法(首先:在成员上删除index idref )

 CREATE INDEX idref ON members(ref,id);

(我现在不能亲自尝试)