如何选择多表结构以获得最快的查询响应
我有一个数据库，其中按地理位置列出事件（目前数据库中约有 100000 个事件）。目前它处于一个很好的规范化架构中：表 tevent 中的事件有一个外键 fkvenue，它指向表 tvenue 中的场地，该外键有一个外键 fkcity，依此类推 tregion 和 tcountry。
因此，查找事件最多的国家/地区的查询相当复杂，至少有三个内部联接：
<代码> 从 tevent e 中选择 r.fkcountry,count(distinct e.id) e.fkvenue=v.id 上的内连接 tvenue v 在 v.fkcity=cy.id 上内连接 tcity cy 在 cy.fkregion=r.id 上内连接 tregion r 按 r.fkcountry 分组 按计数排序（不同的 e.id） desc
我正在尝试寻找加快速度的方法，并且我认为直接按国家/地区绘制事件地图可能会有所帮助。我创建了一个地图表 teventcountry，具有以下更简单的结果语法：
<代码> select ec.fkcountry,count(distinct e.id) from tevent e 内部加入 teventcountry ec on ec.fkevent=e.id group by ec.fkcountry order by count(distinct e.id) desc
令我惊讶的是，这产生了完全相反的效果：新查询花费的时间几乎是旧的、更复杂的查询的五倍。
有人可以解释一下这一点，或者为我指出一个好的资源来帮助我了解多表查询的速度吗？或者甚至告诉我这类事情的首选模式？
（在有人问之前，我已经注意在运行之间执行“重置查询缓存”，以使计时结果有效，是的，我已经正确索引了所有内容！！！）
谢谢
大卫
I have a database in which I am listing events by geography (currently around 100000 events in the database). At the moment it's in a nicely normalised schema: an event in table tevent has a foreign key fkvenue which points at a venue in table tvenue which has a foreign key fkcity, and so on to tregion and tcountry.
The query to find the countries with the most events is therefore fairly complex, with no less than three inner joins:
select r.fkcountry,count(distinct e.id) from tevent e
inner join tvenue v on e.fkvenue=v.id
inner join tcity cy on v.fkcity=cy.id
inner join tregion r on cy.fkregion=r.id
group by r.fkcountry order by count(distinct e.id) desc
I'm trying to find ways of speeding things up, and I thought it might be helpful to map the events directly by country. I've created a map table teventcountry, with the following much simpler resulting syntax:
select ec.fkcountry,count(distinct e.id) from tevent e inner join teventcountry ec on ec.fkevent=e.id group by ec.fkcountry order by count(distinct e.id) desc
To my surprise, this had the exact opposite effect: the new query took nearly five times as long as the older, much more complex query.
Can someone explain this, or point me at a good resource to help me gain an understanding of the speed of multi-table queries? Or even tell me a preferred pattern for this sort of thing?
(before anyone asks, I've taken care to do a "reset query cache" between runs in order to make the timing results valid, and yes, I have indexed everything properly!!!!)
Thanks
David
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您需要查看 MySQL 如何评估查询。为此，请使用内置命令
EXPLAIN，它描述了 MySQL 如何执行给定的 select 语句。以下是 EXPLAIN 的参考： http://dev.mysql.com/ doc/refman/5.5/en/explain.html
您可以使用EXPLAIN的输出来查看MySQL“查询执行计划”，包括它将使用哪些索引和连接，等等。由此您可以解释为什么给定查询快或慢，以及可以采取哪些措施来改进它。通常这意味着添加一个特定的索引，或者调整一个索引以便它可以在您的查询中使用。
您可以通过谷歌搜索“MySQL EXPLAIN”来获取有关使用这个强大工具的大量指南。这是一个这样的演练： http://forums.spry .com/howtos/1345-using-mysqls-explain-command.html
You need to look at how MySQL is evaluating the queries. To do this, use the built-in command
EXPLAIN, which describes how MySQL executes a given select statement.Here is the reference on EXPLAIN: http://dev.mysql.com/doc/refman/5.5/en/explain.html
You can use EXPLAIN's output to see the MySQL "query execution plan", including which indexes and joins it would use, and so on. From this you can interpret why a given query is fast or slow, and what you can do to improve it. Usually this means adding a particular index, or tweaking one so it can be used in your query.
You can Google "MySQL EXPLAIN" for a bunch of guides on using this powerful tool. Here is one such walk-through: http://forums.spry.com/howtos/1345-using-mysqls-explain-command.html