分表后如何快速查询数据？
我有一个大约10亿条记录的MySQL表。当我查询时，速度非常慢。 所以我把这张表按照ID分成了10个结构相同的子表。
table_1(1-1000000)
table_2(10000001-2000000)
table_3(20000001-3000000)
......
但是分表后如何快速查询数据呢？
当我像这样查询用户时：select name from table where name='mark'，我不知道去哪个表查询，因为我可以获得ID范围。
I have a MySQL table about 1000 million records. It is very slow when I make a query.
So I split this table by ID into 10 sub-tables with the same structrue.
table_1(1-1000000)
table_2(10000001-2000000)
table_3(20000001-3000000)
......
But how can i query data in a fast way after table splitting?
when I query a user like this: select name from table where name='mark', I don't know go to which table for querying beacuse I can get the ID range.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
当您显示示例查询时，以这种方式拆分表完全不是正确的方式。实际上，你制造的问题比解决任何问题都多。
让我们回到大表：
第1步是看看为什么它很慢，所以发布解释sql命令来获得概述。
第 2 步是查看是否可以改进该查询。说索引之类的东西不是一个好的解决方案可能是真的。如果是这样，请提供显示这一点的测量结果。
第三步是跳出框框思考。您正在一个非常大的表中运行查询，该表不断插入。考虑使用专门为搜索设计的索引。例如，考虑使用 Solr 为搜索命令建立索引。
最终你甚至可能会到达硬件点，在这个硬件上它只是无法变得更快。但首先请按照步骤操作，添加正确的信息、具体的测量值和规格，以便您可以获得针对您的案例的更完整的支持。
Splitting tables this way is totally not the right way when you show your example query. You created more issues actually than solving anything.
Let's get back to the big table:
Step 1 is to see why it is slow, so post explain sql command to get an overview.
Step 2 is to see whether you can improve that query. Stating things like indexes are not a good solution can be true. If so please provide measurements showing this.
Step 3 is to think outside the box. You are running queries in a very big table which gets constantly inserts. Consider using a specifically for search designed index. For example consider indexing with Solr for the search commands.
Eventually you might even get to the hardware point, it just can't get faster on this hardware. But first follow through steps, add the right information, concrete measurements and specifications so you can get even more complete support on your case.