有人可以推荐一个关于 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 id 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):
- 尝试使用
USE INDEX 运行查询(<你的索引名称在这里>)看看你的索引使用实际上会提供您期望的性能. - 确保列的顺序在任何综合指数中都反映了您期望使用该索引的方式(对不起，如果这有点含糊，但是MySql 索引使用可能有点古怪有时).
- Try running the query with
USE INDEX (<your index name here>)to see if your index usage would actually provide the performance you expect. - Make sure the order of your columns in any composite index reflects the way you expect that index to be used (sorry if that's a bit vague, but MySql index usage can be a bit quirky at times).
这篇关于有人可以推荐一个关于 MySQL 索引的好教程，特别是在连接期间在 order by 子句中使用时?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
