多主键快速选择查询

d8tt03nd 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(267)

在测试我的简单邮件mysql db时，我发现了一个奇怪的行为，很高兴知道原因。
我的“mail”表有两个主键：

idx : INT(Auto-Increment)
uid : VARCHAR(50)

使用其他一些伪列，如createdtime、info等。
然后，我用200000个虚拟数据填充了我的表，并用一个简单的搜索查询进行了测试：

SELECT * FROM mail WHERE uid='RANDOMGENERATEDUID';

执行这个查询大约花了0.235秒，从workbench的表格式解释中我发现这个查询执行全表扫描并且不使用任何键。
我很好奇，如果我强制使用'idx'列进行select查询，会发生什么情况，因此，我测试了另一个查询，结果相同：

SELECT * FROM mail
WHERE idx IN (SELECT idx FROM foodwagondb.mail WHERE uid='RANDOMGENERATEDUID');

但令我惊讶的是，这个查询的执行速度更快，只需0.078秒！
我眼睛后面是怎么回事？我很高兴知道为什么会这样！

2条答案

在第一个查询中，根据varchar字段（uid）搜索每一行。在第二个查询中，mysql根据索引字段（idx）预先过滤行，然后在该子集中搜索文本字符串。对于较小的表，您看不出有什么区别，但是对于较大的表，它会影响性能。@scaisedge是对的，内部连接规则！

你应该避开 IN 从句和用法 INNER JOIN 相反

SELECT m1.* FROM mail m1
INNER JOIN foodwagondb.mail m2 ON m2.idx = m1.idx 
WHERE idx m2.uid='RANDOMGENERATEDUID';

in子句相当于or条件，这意味着为每个值重新生成相关查询。。内部连接只执行一个查询并匹配结果值。
无论如何，您可以使用适当的组合索引来改进这两个查询

create index my_index on mail (uid, idx)