问
为什么我使用分区表, 有时候是几个锁, 有时候是几百把锁, 阴晴不定
实验
我们先宽油起一个数据库:
建一个分区表:
我们希望根据 timestamp 的日期进行分区, id 作为主键. 由于分区键必须是主键, 所以我们将 timestamp 加入主键中.
下面我们来研究一下使用分区表时, 分区表到底会用多少个锁.
场景1:
此时, 查看锁信息:
可以看到:
场景2:
这次我们换成 RR 隔离级别:
查看锁信息:
这次锁数量变成了 64 个, 每个分区表上锁住了 supremum 的 gap 区间. 这很好理解: 我们让 MySQL 锁住了所有 id=1 可能出现的地方, 这就包括了所有分区中相关间隙.
场景3:
这次我们在 where 条件里用到分区键:
场景4:
可以看到各分区上的 IX 锁又出现了, 跟场景3的结论又出现了偏差.
我们先不着急, 再做一个实验
场景5:
这里的锁只涉及 p0 和 p1 (也就是包含匹配where条件的数据的分片), 不包括不相关的分片 p2这次测试又和场景3的结论一致了
场景3/4/5的结论不一致, 我们怎么理解呢? 还得回到官网:MySQL 可以根据 where 条件中的分片键信息, 过滤出相关的分片, 仅在相关分片上用锁, 这技术成为 partition pruning.但这项优化有限制条件, 这里我们节选一段官网文档, 详细信息大家参考：https://dev.mysql.com/doc/refman/8.0/en/partitioning-pruning.html
在场景4和场景5中, 在 where 中使用了比较符的情况下, 场景4中的 DAYOFYEAR 函数是不支持 partition pruning 的, 而场景5中的 YEAR 函数则能支持, 所以两个场景存在差异.
通过今天的实验, 我们可以看到:对分区表的使用中, where 中带有分区键, 并且形式简单 ("等于" 比 "比较符" 简单), 并且分区键中的函数符合条件 ( YEAR/TO_DAYS/TO_SECONDS/...), 那么 partition pruning 机制会优化上锁的数量.否则, 分区表的使用会带来锁数量的上升.
小贴士
也可以通过 select ... from table partition(p1) where ... , 将SQL的作用范围限定在某个分片的范围内。
但这样对业务的侵入就比较严重, 可作为非常手段使用
思考题
大家有空可以思考以下现象的原因:
我们造一张与之前实验类似的表, 造一些数据:
发现与场景1不同, 除了所有分区上都有IX锁, 所有行都上有行锁
相关推荐
