了解一些 MySQL 优化技巧
嘿，我正在寻找一些方法来使我的 MySQL 查询更加优化，我买了一些用于实践，但对于一些我希望了解为什么推荐它们。
例如，不使用“LIMIT 10000,10”，因为我了解到MySQL读取10010行，丢弃10000行并返回10。
现在我想知道的是：
参考这篇文章：http://forge.mysql.com/wiki/Top10SQLPerformanceTips
“使用巧妙的键并 ORDER BY 而不是 MAX”
什么是“聪明钥匙”？
MySQL 采用什么执行路径来计算建议避免的 MAX。
谢谢。
Hey, I was looking up some ways to make my MySQL queries more optimized, I bought some into practice but for some I wish to understand why they are recommended.
For example not using "LIMIT 10000,10" as I learned that MySQL reads 10010 rows, throws away the 10000 and returns the 10.
Now what I want to know is:
Referring to this article: http://forge.mysql.com/wiki/Top10SQLPerformanceTips
"Use a clever key and ORDER BY instead of MAX"
What is a "clever key" ?
What execution path does MySQL take to calculate MAX that it has been recommended to avoid it.
Thank you.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我将尝试回答问题 1，并说“聪明的键”是表上的主键，当以某种方式订购时它具有价值。 （例如，一个酒店数据库，其中有一个表，用于跟踪客人何时登记入住。将表的主键设置为登记入住的时间戳。然后，当您需要知道最近的登记入住时，可以按主键进行排序键按降序排列）。我认为这比使用自动递增整数作为主键和时间戳字段的表要快。
您可能会在此 stackoverflow 讨论中找到问题 2 的答案。它应该让您了解执行路径，但不一定将其与链接中提到的聪明的密钥进行比较。
I'm going to take a stab at question 1 and say a "clever key" is a primary key on a table which has value when ordered a certain way. (e.g. A hotel database with a table that tracks when guests check in. Make the primary key of the table the TimeStamp of the check-in. Then, when you need to know the most recent check-ins, you can order by the primary key in descending order). I suppose that is faster than having a table that uses an auto-incrementing integer as the primary key and a Timestamp field.
You might find your answer to Question 2 in this stackoverflow discussion. It should give you an idea of the execution path, but doesn't necessarily compare it the clever key mentioned in your link.
聪明的键是允许 mysql 根据想要的顺序读取行的键，从而不使用文件排序或临时表进行排序。
它不一定必须是主键。要了解何时可以使用这样的键进行排序，请阅读这篇非常有用的文章：http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
A clever key is a key that will allow the mysql to read the rows according to the wanted order, thus not using filesort or temporary tables for the sorting.
It does not have to be a primary key. To learn when you can use such a key for sorting read this very useful article: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html