mysql 查询和性能
我想知道如果在以下条件下运行此查询对性能的影响。
查询：
select `players`.*, count(`clicks`.`id`) as `clicks_count`
from `players` left join `clicks` on `clicks`.`player_id` = `players`.`id`
group by `players`.`id`
order by `clicks_count` desc
limit 1
条件：
- 在点击表中我期望得到 1 分钟内插入 1000 次
- clicks 表将包含更多 那么 1,000,000 行
- 玩家表将包含 10,000 行
- 玩家表每 5 行插入一次 分钟
我想知道如果我在 1 分钟内运行查询 1000 次，性能方面会发生什么。
谢谢
I would like to know the impact on performance if I run this query in the following conditions.
Query:
select `players`.*, count(`clicks`.`id`) as `clicks_count`
from `players` left join `clicks` on `clicks`.`player_id` = `players`.`id`
group by `players`.`id`
order by `clicks_count` desc
limit 1
Conditions:
- In the clicks table I expect to get
insert 1000 times in a 1 minute - The clicks table will contain more
then 1,000,000 rows - The players table will contain
10,000 rows - The players table get inserted into every 5
minutes
I would like to know what to expect performance-wise if I run the query 1000 times in 1 minute.
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
对于表中任何有意义的数据量，该查询将永远在几毫秒内运行。它将运行两次全表扫描，将两者连接在一起，聚合混乱的数据，然后从中获取顶行。
使用触发器将总计存储在播放器中，并为该字段建立索引。然后您将能够完全避免连接：
That query will never run in milliseconds with any meaningful amounts of data in your tables. It'll run two full table scans, join the two together, aggregate the mess, and fetch the top row from that.
Use a trigger to store the total in the players, and index that field. You'll then be able to avoid the join altogether:
第一&最重要的是，如果您希望在这么多记录和频繁写入的情况下获得良好的性能，那么您应该担心您的架构；即，如果尚未到位，则必须创建适当的索引和约束。
接下来，查询本身，选择所需的最小字段数（因此，如果不需要所有玩家字段，请避免使用“players.*”）。
个人偏好，我会重组表（例如用playerID代替id）并像这样查询：
再次，看看你是否真的需要所有玩家表字段；如果不是，则省略“p.*”并替换为 p.foo、p.bar 等。
First & foremost, you should worry about your schema if you want decent performance with that number of records and frequent writes; i.e. proper indexes and constraints must be created if not already in place.
Next, the query itself, select the minimum number of fields needed (so if you do not need ALL players field, avoid using "players.*").
Personal pref, I'd restructure tables (e.g. playerID in place of id) and query like so:
Again, see if you really need ALL player table fields; if not, omit "p.*" and replace with p.foo, p.bar, etc.