如何提高频繁插入率的MySQL查询性能？
我有大约。 表 tb_post 中有 20 万行，每 5 分钟大约有 20 万行。 10 个新插入。
我正在使用以下查询来获取行 -
SELECT tb_post.ID, tb_post.USER_ID, tb_post.TEXT, tb_post.RATING, tb_post.CREATED_AT,
tb_user.ID, tb_user.NAME
FROM tb_post, tb_user
WHERE tb_post.USER_ID=tb_user.ID
ORDER BY tb_post.RATING DESC
LIMIT 30
以排序方式获取所有行需要超过 10 秒的时间。
以下是 EXPLAIN 查询的报告：
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tb_user ALL PRIMARY NULL NULL NULL 20950 Using temporary; Using filesort 1 SIMPLE tb_post ref tb_post_FI_1 tb_post_FI_1 4 tb_user.id 4
很少输入：
tb_post.RATING是 Float 类型tb_post.USER_ID上有索引
任何人都可以建议我一些关于如何优化它的建议查询并提高其读取性能？
PS：我是数据库扩展问题的新手。 因此，任何类型的建议对于该查询都是有用的。
I'm having approx. 200K rows in a table tb_post, and every 5 minutes it has approx. 10 new inserts.
I'm using following query to fetch the rows -
SELECT tb_post.ID, tb_post.USER_ID, tb_post.TEXT, tb_post.RATING, tb_post.CREATED_AT,
tb_user.ID, tb_user.NAME
FROM tb_post, tb_user
WHERE tb_post.USER_ID=tb_user.ID
ORDER BY tb_post.RATING DESC
LIMIT 30
It's taking more than 10sec to fetch all the rows in sorted fashion.
Following is the report of EXPLAIN query:
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tb_user ALL PRIMARY NULL NULL NULL 20950 Using temporary; Using filesort 1 SIMPLE tb_post ref tb_post_FI_1 tb_post_FI_1 4 tb_user.id 4
Few inputs:
tb_post.RATINGis Float type- There is index on
tb_post.USER_ID
Can anyone suggest me few pointers about how should I optimize this query and improve its read performance?
PS: I'm newbie in database scaling issues. So any kinds of suggestions will be useful specific to this query.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您需要一个涵盖 ORDER BY 和 WHERE 子句的 tb_post 索引。
=> EXPLAIN SELECT ...ORDER BY tb_post.RATING DESC LIMIT 30 的输出
You need an index for tb_post that covers both the ORDER BY and WHERE clause.
=> output of EXPLAIN SELECT ...ORDER BY tb_post.RATING DESC LIMIT 30
您可以尝试索引
tb_post.RATING：MySQL有时可以使用索引来优化ORDER BY子句：http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html如果您尝试聚合来自不同表的数据，您还可以检查哪种类型的联接（ http://en.wikipedia.org/wiki/Join_(SQL)）您想要的。 有些比其他更好，具体取决于您想要什么。
You could try to index
tb_post.RATING: MySQL can sometimes use indexes to optimizeORDER BYclauses : http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.htmlIf you're trying to aggregate data from different tables, you could also check which type of join ( http://en.wikipedia.org/wiki/Join_(SQL) ) you want. Some are better than others, depending on what you want.
如果取消 ORDER BY 会发生什么，这会对性能产生影响吗？ 如果这有很大的影响，那么也许可以考虑索引 tb_post.RATING。
卡尔
What happens if you take the ORDER BY off, does that have a performance impact? If that has a large effect then maybe consider indexing tb_post.RATING.
Karl