如果涉及的表之一没有定义索引，mysql查询运行速度会变慢吗？
这个已经填充的数据库来自另一个开发人员。
我不确定该开发人员创建表时脑子里在想什么，但在我们的一个脚本中，有一个涉及 4 个表的查询，而且运行速度非常慢。
SELECT
a.col_1, a.col_2, a.col_3, a.col_4, a.col_5, a.col_6, a.col_7
FROM
a, b, c, d
WHERE
a.id = b.id
AND b.c_id = c.id
AND c.id = d.c_id
AND a.col_8 = '$col_8'
AND d.g_id = '$g_id'
AND c.private = '1'
注意： $col_8 和 $g_id 是表单中的变量
这只是我的理论，这是由于表 b 和 c 没有索引，尽管我猜测开发人员认为没有必要，因为这些表只告诉a和d之间的关系，其中b表示a中的数据属于某个用户，c表示该用户属于d中的某个组。
正如您所看到的，甚至没有使用连接或其他广泛的查询函数，但这个只返回大约 100 行的查询需要 2 分钟来执行。
无论如何，我的问题只是这篇文章的标题。如果涉及的表之一没有定义索引，mysql 查询运行速度是否会变慢？
There's this already populated database which came from another dev.
I'm not sure what went on in that dev's mind when he created the tables, but on one of our scripts there is this query involving 4 tables and it runs super slow.
SELECT
a.col_1, a.col_2, a.col_3, a.col_4, a.col_5, a.col_6, a.col_7
FROM
a, b, c, d
WHERE
a.id = b.id
AND b.c_id = c.id
AND c.id = d.c_id
AND a.col_8 = '$col_8'
AND d.g_id = '$g_id'
AND c.private = '1'
NOTE: $col_8 and $g_id are variables from a form
It's only my theory that it's due to tables b and c not having an index, although I'm guessing that the dev didn't think that it was necessary since those tables only tell relations between a and d, where b tells that the data in a belongs to a certain user, and c tells that the user belongs to a group in d.
As you can see, there's not even a join or other extensive query functions used but this query which returns only around 100 rows takes 2 minutes to execute.
Anyway, my question is simply this post's title. Will a mysql query run slower if one of the tables involved has no index defined?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
是的！它会慢得多。
您需要为所有“ID”添加索引。
并且可能应该重写查询以使用联接（将有助于优化器）...
将索引放在那里后，您可以看到性能提高了数百倍。
这样的查询所说的另一件事是，以前的开发人员是“一个彻头彻尾的白痴（tm）”
M。
YES! It will be MUCH slower.
You need to put indices on all the "IDs".
And probably should rewrite the query to use joins (will help the optimizer)...
Just after putting the indices there you can see hundred times performance increase.
The other thing that such a query says is that the previous developer was "A complete idiot (tm)"
M.
答案是“这取决于细节，但很可能会更慢”。
索引允许您从磁盘中仅读取表数据的 X%，而不是 100%（也称为表扫描）。
另外，您需要随时运行 EXPLAIN “为什么我的查询太慢”无法获取必要的详细信息以进行真正的解释
The answer is "it depends on details, but more likely than not it will be slower".
What the index allows you to do is to read only X% of the table's data from the disk instead of 100% (aka table scan).
Also, you need to run EXPLAIN anytime you want to answer "why is my query too slow" to get necessary details for a real explanation
如果不知道表中的数据分布以及查询本身，就无法确定。但是您几乎可以保证它不会更快...
No way to tell for sure without knowing the data distribution in thew table, and the query itself.. But You can pretty much guarantee that it won't br faster...
一般来说，是的...但是您必须为您正在运行的特定查询定义索引。
看起来您的表都应该在
id字段上有索引，c应该在private上有索引，而a应该有一个索引。 code> 应该在a.col_8的可能列值上有索引。然而，真正判断的唯一方法是使用EXPLAIN或DESCRIBE运行查询来查看引擎如何执行它......Generally, yes... but you have to define your indexes to the specific queries that you're running.
It looks like your tables should all have indexes on your
idfields,cshould have an index onprivate, andashould have indexes on the possible column values ofa.col_8. However, the only way to really tell is to run your query withEXPLAINorDESCRIBEto see how the engine is executing it...