mysql 分区
只是想验证数据库分区仅在数据库级别实现，当我们查询分区表时，我们仍然执行正常查询，查询没有什么特别的，解析查询时会自动执行优化，对吗？
例如，我们有一个名为“地址”的表，其中有一列名为“国家/地区代码”和“城市”。因此，如果我想获取美国纽约的所有地址，通常我会这样做：
select * from address where country_code = 'US' and city = 'New York'
如果现在表由“country_code”分区，并且我知道现在查询将仅在包含的分区上执行国家/地区代码 = 美国。我的问题是我需要在sql语句中显式指定要查询的分区吗？或者我仍然使用之前的语句并且数据库服务器会自动优化它？
提前致谢！
just want to verify that database partition is implemented only at the database level, when we query a partitioned table, we still do our normal query, nothing special with our queries, the optimization is performed automatically when parsing the query, is that correct?
e.g. we have a table called 'address' with a column called 'country_code' and 'city'. so if i want to get all the addresses in New York, US, normally i wound do something like this:
select * from address where country_code = 'US' and city = 'New York'
if now the table is partitioned by 'country_code', and i know that now the query will only be executed on the partition which contains country_code = US. My question is do I need to explicitly specify the partition to query in my sql statement? or i still use the previous statement and the db server will optimize it automatically?
Thanks in advance!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
Jarod 的答案是正确的，完全回答了问题，但我认为缺少一个重要的部分。
如果您的查询不包含分区列上的筛选条件，则您可能会看到比相同大小的未分区表更差的性能。
如果您将查询更改为仅筛选城市，则它不会仅使用美国分区，并且性能会比针对相同但未分区的表的查询更差。
分区可以帮助查询的唯一其他地方是连接到具有相同分区键的分区表时。通常，该连接可以轻松并行化。假设这不是你正在做的事情。
Jarod's answer is correct and fully answers the question but I think there's an important piece missing.
If your query doesn't include a filter condition on the partition column, you're likely to see worse performance than you would on an unpartitioned table of the same size.
If you changed your query to just filter on City, it won't use only the US partition and will perform worse than a query against the same yet unpartitioned table.
The only other place where partitioning can help queries is when joining to a partitioned table with the same partition key. Often that join can be parallelize easily. Assuming that's not what you're doing.
分区对于您编写的任何查询都是透明的。无需指定要查询哪个分区。
数据库仅使用下面的分区进行相应优化，理想情况下减少需要根据您的 where 子句搜索的数据或索引量。
The partitioning is transparent to any queries you write. There is no need to specify what partition to query.
The database just uses the partitions underneath to optimize accordingly and ideally reducing the amount of data or indexes it needs to search through based on you where clause.