从数据库检索 ZIP - 如何使用索引优化它
我得到以下数据库：
http://sourceforge.net/projects/zips/
我想做的是优化它。基本上，当用户输入邮政编码时，我想检索经度和纬度。
我用Spring>休眠> MySQL 设置。
问题是如何配置 MySQL 和 Hibernate 以加快检索速度。我对索引不太熟悉，所以稍微解释一下也会很有帮助。
理想情况下，如果我可以创建优化（压缩）数据库结构而不是 MySQL 表，那将是理想的，因为这将仅用于读取。
I got following database:
http://sourceforge.net/projects/zips/
What I would like to do is optimize it. Basically when user enters ZIP code I want to retrieve Long and Lat.
I use Spring > Hibernate > MySQL setup.
The question is how do I configure the MySQL and Hibernate to speedup the retrieval. I'm not that familiar with indexes so little explanation would be also very helpful.
Ideally if I could create optimized (compressed) database construct instead of MySQL table, that would be ideal, since this will be used only for reading.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我首先对查询运行 SQL 解释并查看 SQL 的成本。之后，您想查看数据库如何匹配行。它是否进行了全表扫描或进行了索引匹配（并且索引匹配存在变化）？您希望始终避免全表扫描，除非表中只有几百行。在这种情况下，数据库可能决定进行全表扫描。
一旦获得该信息，您就可以确定表上需要什么索引。然后，在一段时间内，随着表中数据的增长，您希望再次运行表扫描以查看索引是否仍然良好。
这是一个很好的教程来帮助您入门。
希望这有帮助！
I would first run a SQL explain on the query and see the cost of the SQL. After that you want to see how the database matched on the rows. Did it do a full table scan or did it to a index match (and there are variations on index matches)? You want to avoid full table scans always unless you have like only few hundreds rows in the table. In that case the database might decide to do a full table scan.
Once you have that information you can determine what index you need on your table. Then over a period of time as the table grows more in data you want to run the table scans again to see if the indices are still good.
Here is a good tutorial to get you started.
Hope this helps!