Mysql5.7中有两个表,每个表有100000条记录。每一个都包含这样的数据:
id name
-----------
1 name_1
2 name_2
3 name_3
4 name_4
5 name_5
...
ddl是:
CREATE TABLE `table_a` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `table_b` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
现在我执行以下两个查询,看看后者是否会更好。
select SQL_NO_CACHE *
from table_a a inner
join table_b b on a.name = b.name
where a.id between 50000 and 50100;
select SQL_NO_CACHE *
from (
select *
from table_a
where id between 50000 and 50100
) a
inner join table_b b on a.name = b.name;
我认为在前一个查询中,它会迭代100000×100000次,然后通过where子句过滤结果;在后一个查询中,它将首先过滤tablea以获得100个中间结果,然后迭代100*100000次以获得最终结果。因此前者要比后者快得多
但结果是两个查询都要花费1.5秒。通过使用explain语句,我找不到任何实质性的差异
mysql是否优化了前一个查询,使其像后一个查询一样执行?
对于INNER JOIN
,ON
和WHERE
进行了相同的优化。对于LEFT/RIGHT JOIN
,语义不同,因此优化也不同。(同时,请使用ON
来陈述关系,使用WHERE
来过滤——这有助于人们理解查询。(
由于a.id between 50000 and 50100
,这两个查询都可以从a
获取100行开始,然后100次到达另一个表。但由于缺乏任何有用的索引,它必须如何进行表扫描。因此,100 x 100000次操作。("Nested Loop Join"或"NLJ"(
解决速度慢的方法是添加
INDEX(name)
至少将其添加到b
。或者如果这真的是一个查找表;名称";至";ids";,则为CCD_ 10。使用任何一个索引,工作量都应该降到100 x 100。
另一种分析查询的技术是
FLUSH STATUS;
SELECT ...
SHOW VARIABLES LIKE 'Handler%';
它统计实际触摸的行数(数据或索引(。100000(或其倍数(表示在您的情况下进行了完整的表/索引扫描。
更多:索引食谱
连接总是比子查询快,所以尽可能使用连接而不是子查询来加快进程。而在这种情况下,这两个查询是等价的。
优化查询的另一种方法是使用分区。在使用分区时,mysql将根据您指定的查询直接转到分区,这将减少在其他不相关记录上花费的时间。