问
为什么我使用分区表, 有时候是几个锁, 有时候是几百把锁, 阴晴不定
实验
我们先宽油起一个数据库:
建一个分区表:
我们希望根据 timestamp 的日期进行分区, id 作为主键. 由于分区键必须是主键, 所以我们将 timestamp 加入主键中.
下面我们来研究一下使用分区表时, 分区表到底会用多少个锁.
先插入两条数据:
场景1:
我们用 RC 隔离级别, 锁定 id = 1 的记录
此时, 查看锁信息:
可以看到:
由于我们在 where 条件里没有用到分区键 timestamp, 那么 MySQL 要访问每张表, 就需要给每张表上IX锁.
场景2:
这次我们换成 RR 隔离级别:
查看锁信息:
这次锁数量变成了 64 个, 每个分区表上锁住了 supremum 的 gap 区间. 这很好理解: 我们让 MySQL 锁住了所有 id=1 可能出现的地方, 这就包括了所有分区中相关间隙.
场景3:
这次我们在 where 条件里用到分区键:
查看锁信息:
由于我们直接使用了分区键, 这次只有相关的分区会有锁.
看上去 where 条件中使用分区键, 能大量减少锁的范围.
场景4:
我们在 where 中只使用分区键, 但是条件复杂一点, 换成比较符(大于/小于):
查看锁信息:
可以看到各分区上的 IX 锁又出现了, 跟场景3的结论又出现了偏差.
我们先不着急, 再做一个实验
场景5:
这次我们做一张类似的表, 只是将分区键函数换成了 YEAR
使用场景4类似的 SQL:
查看锁信息:
这里的锁只涉及 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下同样的 SQL:
查看锁信息:
发现与场景1不同, 除了所有分区上都有 IX 锁, 所有行都上有行锁
大家有兴趣的时候, 可以尝试解释这个现象
相关推荐
关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！
