参考文章 https://opensource.actionsky.com/20210606-mysql/
按照官方文档说法 好像只有year(),to_days(),TO_SECONDS() 进行了分区裁剪
官方文档 https://dev.mysql.com/doc/refman/8.0/en/partitioning-pruning.html
由于最近项目使用了 PARTITION BY RANGE COLUMNS 分区,所以需要验证这种情况下mysql是否进行了分区裁剪,实验环境8.0.25
建表语句如下
CREATE TABLE IF NOT EXISTS `exam_core_result` (
`RID` bigint NOT NULL AUTO_INCREMENT COMMENT '数据主键',
`ID` varchar(32) COLLATE NOT NULL COMMENT '主键',
`TEST_DATE` date NOT NULL COMMENT '考试日期',
#中间无关字段省略
PRIMARY KEY (`RID`,`TEST_DATE`),
UNIQUE KEY `UI_RESULT_ID_TEST_TIME` (`ID`,`TEST_DATE`),
KEY `I_RESULT_CORE_STUDENT_ID` (`CORE_STUDENT_ID`),
KEY `I_RESULT_TEST_DATE` (`TEST_DATE`)
)
PARTITION BY RANGE COLUMNS(TEST_DATE)
(PARTITION p2021_2 VALUES LESS THAN ('2021-08-01'),
PARTITION p2021_3 VALUES LESS THAN ('2021-10-01'),
PARTITION p2021_4 VALUES LESS THAN ('2022-01-01'),
PARTITION p2022_1 VALUES LESS THAN ('2022-04-01'),
PARTITION p2022_2 VALUES LESS THAN ('2022-08-01'),
PARTITION p2022_3 VALUES LESS THAN ('2022-10-01'),
PARTITION p2022_4 VALUES LESS THAN ('2023-01-01'),
PARTITION p2023_1 VALUES LESS THAN ('2023-04-01'),
PARTITION p2023_2 VALUES LESS THAN ('2023-08-01'),
PARTITION p2023_3 VALUES LESS THAN ('2023-10-01'),
PARTITION p2023_4 VALUES LESS THAN ('2024-01-01'),
PARTITION p2024_1 VALUES LESS THAN ('2024-04-01'),
PARTITION p2024_2 VALUES LESS THAN ('2024-08-01'),
PARTITION p2024_3 VALUES LESS THAN ('2024-10-01'),
PARTITION p2024_4 VALUES LESS THAN ('2025-01-01'),
PARTITION p2025_1 VALUES LESS THAN ('2025-04-01'),
PARTITION p2025_2 VALUES LESS THAN ('2025-08-01'),
PARTITION p2025_3 VALUES LESS THAN ('2025-10-01'),
PARTITION p2025_4 VALUES LESS THAN ('2026-01-01')) ;
情况说明:
1.该表一共19 个分区
2. 由于线上数据库使用了 READ COMMITTED 隔离级别,我只在这个隔离级别下测试
表中 满足id='1' 的数据只有1条, 且test_date='2021-06-18' ,数据 在 p2021_2 这个分区中
select id,test_date from exam_core_result where id= '1'
测试1: 以id 执行更新
#以下是测试sql
set global innodb_status_output_locks=ON;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
start transaction ;
select id,test_date from exam_core_result where id= '1' for update;
#查询当前事物id
SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
show engine innodb status;
#rollback;
#set global innodb_status_output_locks=OFF;
当前事物id=1893582,
因为id 查询使用了 UNIQUE INDEX `UI_RESULT_ID_TEST_TIME` (`ID`, `TEST_DATE`) 这个复合索引
所以需要在 UI_RESULT_ID_TEST_TIME 和 PRIMARY 主键上 分别添加记录锁
当 where 条件使用二级索引筛选数据时,会对二级索引命中的条目和对应的聚簇索引都加锁;所以其他事务变更命中加锁的聚簇索引时,都会等待锁。
详细信息见下图
包含21个lock,其中 19个分区 IX lock和 2个 RECORD LOCKS
其中有 2个 Record lock,锁定索引记录
所以光使用唯一索引且不带分区键的话是不能分区裁剪的。
在 lock说明信息中 RECORD LOCKS space id 2723 page no 5
space id=2723
可以用以下语句查询
select * from information_schema.INNODB_TABLESPACES t where t.SPACE=2723
说明id=1 的数据确实是在 p2021_2 这个分区中
测试2 以id和分区键更新
set global innodb_status_output_locks=ON;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
start transaction ;
select * from exam_core_result where TEST_DATE= '2021-06-18' and id= '1' for update;
#查询当前事物id
SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
show engine innodb status;
这时候会分区裁剪只会锁定一个分区,3个lock 一个分区lock ,2个索引记录lock
测试3 以分区键范围更新
set global innodb_status_output_locks=ON;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
start transaction ;
#该范围内27条记录
select * from exam_core_result where TEST_DATE>= '2021-06-18' and TEST_DATE<= '2021-10-18' for update;
#查询当前事物id
SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
show engine innodb status;
包含3个分区IX lock和一个 主键RECORD LOCKS, 以及27条记录锁
说明 以分区键范围更新是可以分区裁剪的,可以大大减少冲突的概率。
切记 分区后所有查询 修改 删除 一定要带着分区键