MySQL在查询中忽略子分区
我有一个表，它按 id 上的范围和代码上的哈希进行分区（两者都是整数）。 30 个分区，每个分区 4 个哈希子分区，总共 120 个。
如果我单独对 id 进行选择，解释计划表明它正确地修剪为仅分区和分区。与其相关的子分区 (4)
如果我对 id + code 进行选择，解释计划显示它正确地修剪到与其相关的一个特定子分区 (1)
但是...
如果我单独对代码进行选择，请解释计划似乎表明 MySQL 正在进行全表扫描（120 个分区），而不是像 Oracle 那样只扫描每个相关分区的一个子分区（总共 30 个）。
当 MySQL 无法修剪整个分区时，我是否需要做一些特殊的事情才能让 MySQL 充分利用子分区修剪功能？或者 MySQL（至少 5.1）不支持利用子分区本身？
I have a table that's partitioned by range on id, and by hash on code (both are integers). 30 partitions, 4 hashed subpartitions apiece, 120 total.
If I do a select on id alone, explain plan shows that it's correctly pruning down to only the partition & subpartitions related to it (4)
If I do a select on id + code, explain plan shows that it's correctly pruning down to the one specific subpartition related to it (1)
HOWEVER...
If I do a select on code alone, explain plan seems to be showing that MySQL is doing a full table scan (120 partitions), instead of acting like Oracle and scanning only the one subpartition of each partition that would be relevant (30 total).
Do I have to do something special to get MySQL to take advantage of subpartition-pruning when it can't prune away entire partitions? Or does MySQL (5.1, at least) just not support taking advantage of subpartitions by themselves?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我找到了答案。似乎只有 MySQL 5.5 及更高版本才能独立于主分区值利用子分区。总有一天，我会记得开始关注我正在阅读的 MySQL 文档的版本。
最后一个示例：
假设“表”在 A 上按范围进行分区，并在 B 上按哈希进行子分区。
查询 1：“SELECT * from table where A=? and B=? and C=?”：
查询 2：“SELECT A from table where B=? and C=?”：
在 MySQL 5.5 及更高版本下可能按预期工作。如果Oracle正常工作，在最坏的情况下，它会在每个分区范围中搜索单个子分区。如果您有 20 个分区范围和 4 个子分区，最坏的情况是它会通过 20 个子分区进行强力搜索。
在 MySQL 5.1 下无法按预期工作。它将一一迭代所有 80 个子分区，并且基本上执行全表扫描。
I found the answer. It appears that only MySQL 5.5 and newer is able to take advantage of subpartitions independently of the main partitioning value. One of these days, I'll remember to start paying attention to which version of the MySQL docs I'm reading.
Final example:
Suppose 'table' is partitioned by range on A and subpartitioned by hash on B.
Query 1: "SELECT * from table where A=? and B=? and C=?":
Query 2: "SELECT A from table where B=? and C=?":
Might work as expected under MySQL 5.5 and newer. If it were Oracle working properly, in the worst case, it would search a single subpartition out of each partition range. If you had 20 partitioned ranges and 4 subpartitions apiece, at worst it would do brute-force searches through 20 subpartitions.
Won't work as expected under MySQL 5.1. It will iterate through all 80 subpartitions one by one, and basically do a full table scan.