问
为什么我应用分区表, 有时候是几个锁, 有时候是几百把锁, 阴晴不定
试验
咱们先宽油起一个数据库:
建一个分区表:
咱们心愿依据 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/ref... :
在场景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 锁, 所有行都上有行锁
大家有趣味的时候, 能够尝试解释这个景象