有人可以推荐一个关于MySQL索引的好教程，特别是在连接期间在order by子句中使用时吗？ [英] Can someone recommend a good tutorial on MySQL indexes, specifically when used in an order by clause during a join?
问题描述
我可以尝试发布并解释我正在尝试运行的确切查询，但我会按照古老的格言说，给一个人一条鱼，他会吃一天，教一个人去鱼和他将在他的余生吃。 SQL优化似乎非常特定于查询，即使您可以为我解决这个特定查询，我将来还要编写更多查询，并且我希望了解索引的工作原理一般而言。
I could try to post and explain the exact query I'm trying to run, but I'm going by the old adage of, "give a man a fish and he'll eat for a day, teach a man to fish and he'll eat for the rest of his life." SQL optimization seems to be very query-specific, and even if you could solve this one particular query for me, I'm going to have to write many more queries in the future, and I'd like to be educated on how indexes work in general.
不过，这里是我当前问题的快速描述。我有一个连接三个表并在0.2秒内运行的查询。真棒。我添加了一个order by子句，它在4分30秒内运行。苏茨基。我对一个表进行了非规范化，因此只有少一个连接，在任何地方添加索引，现在查询运行... 20分钟。我勒个去？最后，我根本不使用连接，而是使用where in（...）order by的子查询，现在它在1.5秒内运行。相当体面。上帝的名字是怎么回事？我觉得如果我真的理解了索引在做什么我可以写一些非常好的SQL。
Still, here's a quick description of my current problem. I have a query that joins three tables and runs in 0.2 seconds flat. Awesome. I add an "order by" clause and it runs in 4 minutes and 30 seconds. Sucky. I denormalize one table so there is one fewer join, add indexes everywhere, and now the query runs in... 20 minutes. What the hell? Finally, I don't use a join at all, but rather a subquery with "where id in (...) order by" and now it runs in 1.5 seconds. Pretty decent. What in God's name is going on? I feel like if I actually understood what indexes were doing I could write some really good SQL.
有人知道一些很好的教程吗？谢谢！
Anybody know some good tutorials? Thanks!
推荐答案
这不是一个非常有说服力的答案，但我发现官方文档可以提供良好的背景：
Not a very riveting answer, but I've found the official docs to give good background:
http：/ /dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
你可以尝试一些事情（假设你已经已经使用 EXPLAIN 分析了优化器路径：
A few things you could try (assuming you've already analysed the optimizer path with EXPLAIN):
- 尝试使用<运行查询code> USE INDEX
（<你的索引名称在这里>）看看
你的索引用法实际上是
提供你期望的性能。 / li>
- 确保任何复合索引中列
的顺序反映了您希望该索引使用的
方式
（对不起，如果这有点模糊，但
MySql索引的使用量有点古怪
）。
这篇关于有人可以推荐一个关于MySQL索引的好教程，特别是在连接期间在order by子句中使用时吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
