mysql8 分区裁剪和锁

参考文章 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条记录锁

 

说明 以分区键范围更新是可以分区裁剪的，可以大大减少冲突的概率。

切记 分区后所有查询 修改 删除 一定要带着分区键