记录下MySQL的分区表
分区表的结构
audit_log 表的结构定义如下：
mysql> SHOW CREATE TABLE audit_log\G
*************************** 1. row ***************************Table: audit_log
Create Table: CREATE TABLE `audit_log` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '日志ID',`log_time` datetime NOT NULL COMMENT '日志产生时间',`operator_id` varchar(64) NOT NULL COMMENT '操作人ID (索引)',`operator_name` varchar(128) NOT NULL COMMENT '操作人名称快照',`action_type` varchar(50) NOT NULL COMMENT '操作类型',`target_type` varchar(50) NOT NULL COMMENT '目标类型',`target_id` varchar(64) NOT NULL COMMENT '目标ID',`target_name` varchar(255) NOT NULL COMMENT '目标名称快照',`action_summary` varchar(500) NOT NULL COMMENT '操作摘要描述',`change_count` int(10) unsigned DEFAULT '0' COMMENT '变更项数量',`entity_count` int(10) unsigned DEFAULT '0' COMMENT '涉及实体数量',PRIMARY KEY (`id`),KEY `aal_idx_log_time` (`log_time`),KEY `aal_idx_operator` (`operator_id`),KEY `aal_idx_target` (`target_type`,`target_id`),KEY `aal_idx_summary` (`action_summary`(100))
) ENGINE=MyISAM AUTO_INCREMENT=14650005 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
表中模拟了 1400 万数据（从2021-07-19至2025-07-22每天一万条）：
mysql> SELECT COUNT(*) FROM audit_log;
+----------+
| COUNT(*) |
+----------+
| 14650004 |
+----------+
1 row in set (0.00 sec)mysql>
分区前的数据表文件：
➜ data ll demo_db/audit_log*
-rw-r----- 1 dnsmasq systemd-journal 13K 7月 23 11:32 demo_db/audit_log.frm
-rw-r----- 1 dnsmasq systemd-journal 2.2G 7月 23 11:33 demo_db/audit_log.MYD
-rw-r----- 1 dnsmasq systemd-journal 562M 7月 23 11:35 demo_db/audit_log.MYI
➜ data
这个mysql容器之前调试时设置成了 MyISAM，分区表建议使用 InnoDB 存储引擎：
- MyISAM 不支持事务，没有 Redo log，无法保证服务崩溃后的数据一致性。
- MyISAM 只有表级锁，虽然写入操作只涉及一个分区，但是还是会锁定整张表。
- MyISAM 存储引擎下，每个分区都会生成一个 .MYD 和 .MYI 文件，如果是按月或按日的分区，会导致文件系统 inode 和目录项激增，也会快速消耗服务器的文件描述符资源 (open_files_limit)，可能导致 Can't open file 错误。
转换为按季度的分区表
步骤1: 移除原自增主键
mysql> ALTER TABLE audit_log DROP PRIMARY KEY, MODIFY id BIGINT UNSIGNED NOT NULL;
Query OK, 14650004 rows affected (2 min 49.20 sec)mysql>
步骤2: 添加复合主键 (id + log_time)，分区键必须属于主键（是主键或是主键的一部分）
mysql> ALTER TABLE audit_log ADD PRIMARY KEY (id, log_time);
Query OK, 14650004 rows affected (3 min 5.18 sec)mysql>
步骤3: 定义分区方案（按季度分区）
ALTER TABLE audit_log
PARTITION BY RANGE COLUMNS(log_time) (PARTITION p2021Q3 VALUES LESS THAN ('2021-10-01'),PARTITION p2021Q4 VALUES LESS THAN ('2022-01-01'),PARTITION p2022Q1 VALUES LESS THAN ('2022-04-01'),PARTITION p2022Q2 VALUES LESS THAN ('2022-07-01'),PARTITION p2022Q3 VALUES LESS THAN ('2022-10-01'),PARTITION p2022Q4 VALUES LESS THAN ('2023-01-01'),PARTITION p2023Q1 VALUES LESS THAN ('2023-04-01'),PARTITION p2023Q2 VALUES LESS THAN ('2023-07-01'),PARTITION p2023Q3 VALUES LESS THAN ('2023-10-01'),PARTITION p2023Q4 VALUES LESS THAN ('2024-01-01'),PARTITION p2024Q1 VALUES LESS THAN ('2024-04-01'),PARTITION p2024Q2 VALUES LESS THAN ('2024-07-01'),PARTITION p2024Q3 VALUES LESS THAN ('2024-10-01'),PARTITION p2024Q4 VALUES LESS THAN ('2025-01-01'),PARTITION p2025Q1 VALUES LESS THAN ('2025-04-01'),PARTITION p2025Q2 VALUES LESS THAN ('2025-07-01'),PARTITION p2025Q3 VALUES LESS THAN ('2025-10-01'),PARTITION p_future VALUES LESS THAN (MAXVALUE)
);mysql> ALTER TABLE audit_log -> PARTITION BY RANGE COLUMNS(log_time) (-> PARTITION p2021Q3 VALUES LESS THAN ('2021-10-01'),-> PARTITION p2021Q4 VALUES LESS THAN ('2022-01-01'),-> PARTITION p2022Q1 VALUES LESS THAN ('2022-04-01'),-> PARTITION p2022Q2 VALUES LESS THAN ('2022-07-01'),-> PARTITION p2022Q3 VALUES LESS THAN ('2022-10-01'),-> PARTITION p2022Q4 VALUES LESS THAN ('2023-01-01'),-> PARTITION p2023Q1 VALUES LESS THAN ('2023-04-01'),-> PARTITION p2023Q2 VALUES LESS THAN ('2023-07-01'),-> PARTITION p2023Q3 VALUES LESS THAN ('2023-10-01'),-> PARTITION p2023Q4 VALUES LESS THAN ('2024-01-01'),-> PARTITION p2024Q1 VALUES LESS THAN ('2024-04-01'),-> PARTITION p2024Q2 VALUES LESS THAN ('2024-07-01'),-> PARTITION p2024Q3 VALUES LESS THAN ('2024-10-01'),-> PARTITION p2024Q4 VALUES LESS THAN ('2025-01-01'),-> PARTITION p2025Q1 VALUES LESS THAN ('2025-04-01'),-> PARTITION p2025Q2 VALUES LESS THAN ('2025-07-01'),-> PARTITION p2025Q3 VALUES LESS THAN ('2025-10-01'),-> PARTITION p_future VALUES LESS THAN (MAXVALUE)-> );
Query OK, 14650004 rows affected, 1 warning (2 min 17.65 sec)mysql>
分区之后的数据表文件：
➜ data ll demo_db/audit_log*
-rw-r----- 1 dnsmasq systemd-journal 13K 7月 23 11:38 demo_db/audit_log.frm
-rw-r----- 1 dnsmasq systemd-journal 184 7月 23 11:38 demo_db/audit_log.par
-rw-r----- 1 dnsmasq systemd-journal 112M 7月 23 11:38 demo_db/audit_log#P#p2021Q3.MYD
-rw-r----- 1 dnsmasq systemd-journal 92M 7月 23 11:38 demo_db/audit_log#P#p2021Q3.MYI
-rw-r----- 1 dnsmasq systemd-journal 139M 7月 23 11:38 demo_db/audit_log#P#p2021Q4.MYD
-rw-r----- 1 dnsmasq systemd-journal 114M 7月 23 11:38 demo_db/audit_log#P#p2021Q4.MYI
-rw-r----- 1 dnsmasq systemd-journal 136M 7月 23 11:38 demo_db/audit_log#P#p2022Q1.MYD
-rw-r----- 1 dnsmasq systemd-journal 112M 7月 23 11:39 demo_db/audit_log#P#p2022Q1.MYI
-rw-r----- 1 dnsmasq systemd-journal 138M 7月 23 11:39 demo_db/audit_log#P#p2022Q2.MYD
-rw-r----- 1 dnsmasq systemd-journal 113M 7月 23 11:39 demo_db/audit_log#P#p2022Q2.MYI
-rw-r----- 1 dnsmasq systemd-journal 139M 7月 23 11:39 demo_db/audit_log#P#p2022Q3.MYD
-rw-r----- 1 dnsmasq systemd-journal 114M 7月 23 11:39 demo_db/audit_log#P#p2022Q3.MYI
-rw-r----- 1 dnsmasq systemd-journal 139M 7月 23 11:39 demo_db/audit_log#P#p2022Q4.MYD
-rw-r----- 1 dnsmasq systemd-journal 114M 7月 23 11:39 demo_db/audit_log#P#p2022Q4.MYI
-rw-r----- 1 dnsmasq systemd-journal 136M 7月 23 11:39 demo_db/audit_log#P#p2023Q1.MYD
-rw-r----- 1 dnsmasq systemd-journal 112M 7月 23 11:39 demo_db/audit_log#P#p2023Q1.MYI
-rw-r----- 1 dnsmasq systemd-journal 138M 7月 23 11:39 demo_db/audit_log#P#p2023Q2.MYD
-rw-r----- 1 dnsmasq systemd-journal 113M 7月 23 11:39 demo_db/audit_log#P#p2023Q2.MYI
-rw-r----- 1 dnsmasq systemd-journal 139M 7月 23 11:39 demo_db/audit_log#P#p2023Q3.MYD
-rw-r----- 1 dnsmasq systemd-journal 114M 7月 23 11:39 demo_db/audit_log#P#p2023Q3.MYI
-rw-r----- 1 dnsmasq systemd-journal 139M 7月 23 11:39 demo_db/audit_log#P#p2023Q4.MYD
-rw-r----- 1 dnsmasq systemd-journal 114M 7月 23 11:39 demo_db/audit_log#P#p2023Q4.MYI
-rw-r----- 1 dnsmasq systemd-journal 138M 7月 23 11:39 demo_db/audit_log#P#p2024Q1.MYD
-rw-r----- 1 dnsmasq systemd-journal 113M 7月 23 11:40 demo_db/audit_log#P#p2024Q1.MYI
-rw-r----- 1 dnsmasq systemd-journal 138M 7月 23 11:40 demo_db/audit_log#P#p2024Q2.MYD
-rw-r----- 1 dnsmasq systemd-journal 113M 7月 23 11:40 demo_db/audit_log#P#p2024Q2.MYI
-rw-r----- 1 dnsmasq systemd-journal 139M 7月 23 11:40 demo_db/audit_log#P#p2024Q3.MYD
-rw-r----- 1 dnsmasq systemd-journal 114M 7月 23 11:40 demo_db/audit_log#P#p2024Q3.MYI
-rw-r----- 1 dnsmasq systemd-journal 139M 7月 23 11:40 demo_db/audit_log#P#p2024Q4.MYD
-rw-r----- 1 dnsmasq systemd-journal 114M 7月 23 11:40 demo_db/audit_log#P#p2024Q4.MYI
-rw-r----- 1 dnsmasq systemd-journal 136M 7月 23 11:40 demo_db/audit_log#P#p2025Q1.MYD
-rw-r----- 1 dnsmasq systemd-journal 112M 7月 23 11:40 demo_db/audit_log#P#p2025Q1.MYI
-rw-r----- 1 dnsmasq systemd-journal 138M 7月 23 11:40 demo_db/audit_log#P#p2025Q2.MYD
-rw-r----- 1 dnsmasq systemd-journal 113M 7月 23 11:40 demo_db/audit_log#P#p2025Q2.MYI
-rw-r----- 1 dnsmasq systemd-journal 34M 7月 23 11:40 demo_db/audit_log#P#p2025Q3.MYD
-rw-r----- 1 dnsmasq systemd-journal 28M 7月 23 11:40 demo_db/audit_log#P#p2025Q3.MYI
-rw-r----- 1 dnsmasq systemd-journal 0 7月 23 11:38 demo_db/audit_log#P#p_future.MYD
-rw-r----- 1 dnsmasq systemd-journal 2.0K 7月 23 11:38 demo_db/audit_log#P#p_future.MYI
➜ data
转换后的常规查询对比
按原主键 id 的查询
mysql> use demo_db;Database changed
mysql>
mysql> EXPLAIN SELECT * FROM audit_log WHERE id = 10000001\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: audit_logpartitions: p2021Q3,p2021Q4,p2022Q1,p2022Q2,p2022Q3,p2022Q4,p2023Q1,p2023Q2,p2023Q3,p2023Q4,p2024Q1,p2024Q2,p2024Q3,p2024Q4,p2025Q1,p2025Q2,p2025Q3,p_futuretype: ref
possible_keys: PRIMARYkey: PRIMARYkey_len: 8ref: constrows: 15filtered: 100.00Extra: NULL
1 row in set, 1 warning (0.00 sec)mysql> mysql> use demo_db_bak;Database changed
mysql>
mysql> EXPLAIN SELECT * FROM audit_log WHERE id = 10000001\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: audit_logpartitions: NULLtype: const
possible_keys: PRIMARYkey: PRIMARYkey_len: 8ref: constrows: 1filtered: 100.00Extra: NULL
1 row in set, 1 warning (0.00 sec)mysql>
直接按 id 查询，分区后的 type 是 ref，分区前的是 const
type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下。
访问的类型有很多，效率从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
rows: 15 表示优化器预估需检查15行（约等于分区数量），实际是扫描所有分区的主键索引。
按普通索引字段的查询
mysql> EXPLAIN SELECT * FROM audit_log WHERE operator_id = 'user_000001'\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: audit_logpartitions: p2021Q3,p2021Q4,p2022Q1,p2022Q2,p2022Q3,p2022Q4,p2023Q1,p2023Q2,p2023Q3,p2023Q4,p2024Q1,p2024Q2,p2024Q3,p2024Q4,p2025Q1,p2025Q2,p2025Q3,p_futuretype: ref
possible_keys: aal_idx_operatorkey: aal_idx_operatorkey_len: 258ref: constrows: 15filtered: 100.00Extra: NULL
1 row in set, 1 warning (0.00 sec)mysql>mysql> EXPLAIN SELECT * FROM audit_log WHERE operator_id = 'user_000001'\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: audit_logpartitions: NULLtype: ref
possible_keys: aal_idx_operatorkey: aal_idx_operatorkey_len: 258ref: constrows: 1filtered: 100.00Extra: NULL
1 row in set, 1 warning (0.00 sec)mysql>
按 log_time 范围的查询（跨分区和不跨分区）
mysql> EXPLAIN SELECT * FROM audit_log WHERE log_time between '2025-03-31 00:00:00' and '2025-04-01 23:59:59'\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: audit_logpartitions: p2025Q1,p2025Q2type: range
possible_keys: aal_idx_log_timekey: aal_idx_log_timekey_len: 5ref: NULLrows: 22191filtered: 100.00Extra: Using index condition
1 row in set, 1 warning (0.00 sec)mysql>
mysql> EXPLAIN SELECT * FROM audit_log WHERE log_time between '2025-04-01 00:00:00' and '2025-04-01 23:59:59'\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: audit_logpartitions: p2025Q2type: range
possible_keys: aal_idx_log_timekey: aal_idx_log_timekey_len: 5ref: NULLrows: 9472filtered: 100.00Extra: Using index condition
1 row in set, 1 warning (0.00 sec)mysql>mysql> EXPLAIN SELECT * FROM audit_log WHERE log_time between '2025-03-31 00:00:00' and '2025-04-01 23:59:59'\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: audit_logpartitions: NULLtype: range
possible_keys: aal_idx_log_timekey: aal_idx_log_timekey_len: 5ref: NULLrows: 16468filtered: 100.00Extra: Using index condition
1 row in set, 1 warning (0.00 sec)mysql>
mysql> EXPLAIN SELECT * FROM audit_log WHERE log_time between '2025-04-01 00:00:00' and '2025-04-01 23:59:59'\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: audit_logpartitions: NULLtype: range
possible_keys: aal_idx_log_timekey: aal_idx_log_timekey_len: 5ref: NULLrows: 8234filtered: 100.00Extra: Using index condition
1 row in set, 1 warning (0.01 sec)mysql>
程序中的查询时间对比
没有指定 log_time 范围，分区后会检索所有分区，查询变慢；
// demo_db 库，1.03ms
// [2025-07-23 14:06:04][1.03]select count(*) as aggregate from `audit_log`
$count = DB::connection('mysql')->table('audit_log')->count();
echo 'count = ' . $count . PHP_EOL; // count = 14650004// demo_db_bak 库，0.58ms
[2025-07-23 14:06:04][0.58]select count(*) as aggregate from `audit_log`
$count = DB::connection('mysql_test')->table('audit_log')->count();
echo 'count = ' . $count . PHP_EOL; // count = 14650004
// demo_db 库，12.67ms
// [2025-07-23 14:08:57][12.67]select count(*) as aggregate from `audit_log` where `operator_id` = 'user_000001'
$count = DB::connection('mysql')->table('audit_log')->where('operator_id', '=', 'user_000001')->count();
echo 'count = ' . $count . PHP_EOL; // count = 1// demo_db_bak 库，0.68ms
// [2025-07-23 14:08:57][0.68]select count(*) as aggregate from `audit_log` where `operator_id` = 'user_000001'
$count = DB::connection('mysql_test')->table('audit_log')->where('operator_id', '=', 'user_000001')->count();
echo 'count = ' . $count . PHP_EOL; // count = 1
// demo_db 库，80.81ms
[2025-07-23 14:15:12][80.81]select count(*) as aggregate from `audit_log` where `log_time` between '2025-07-01 00:00:00' and '2025-07-31 23:59:59' and `operator_id` = 'user_000001'
$count = DB::connection('mysql')->table('audit_log')->whereBetween('log_time', ['2025-07-01 00:00:00', '2025-07-31 23:59:59'])->where('operator_id', '=', 'user_000001')->count();
echo 'count = ' . $count . PHP_EOL;// demo_db_bak 库，319.2ms
[2025-07-23 14:15:12][319.2]select count(*) as aggregate from `audit_log` where `log_time` between '2025-07-01 00:00:00' and '2025-07-31 23:59:59' and `operator_id` = 'user_000001'
$count = DB::connection('mysql_test')->table('audit_log')->whereBetween('log_time', ['2025-07-01 00:00:00', '2025-07-31 23:59:59'])->where('operator_id', '=', 'user_000001')->count();
echo 'count = ' . $count . PHP_EOL;
查看分区数据分布
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'audit_log';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2021Q3 | 740000 |
| p2021Q4 | 920000 |
| p2022Q1 | 900000 |
| p2022Q2 | 910000 |
| p2022Q3 | 920000 |
| p2022Q4 | 920000 |
| p2023Q1 | 900000 |
| p2023Q2 | 910000 |
| p2023Q3 | 920000 |
| p2023Q4 | 920000 |
| p2024Q1 | 910000 |
| p2024Q2 | 910000 |
| p2024Q3 | 920000 |
| p2024Q4 | 920000 |
| p2025Q1 | 900000 |
| p2025Q2 | 910000 |
| p2025Q3 | 220004 |
| p_future | 0 |
| NULL | 14650004 |
+----------------+------------+
19 rows in set (0.00 sec)mysql>
在摘要描述字段上的模糊搜索
摘要描述字段上的索引是 aal_idx_summary (action_summary(100))，使用 “%搜索内容%” 时索引不生效，对比下此时的查询：
mysql> SELECT COUNT(*) FROM audit_log WHERE log_time > '2025-07-01 00:00:00'\G
*************************** 1. row ***************************
COUNT(*): 220004
1 row in set (0.02 sec)mysql>
mysql> EXPLAIN SELECT COUNT(*) FROM audit_log WHERE log_time > '2025-07-01 00:00:00' AND action_summary LIKE '%系统配置%'\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: audit_logpartitions: p2025Q3,p_futuretype: ALL
possible_keys: aal_idx_log_timekey: NULLkey_len: NULLref: NULLrows: 220004filtered: 11.11Extra: Using where
1 row in set, 2 warnings (0.00 sec)mysql>
mysql> SELECT COUNT(*) FROM audit_log WHERE log_time > '2025-07-01 00:00:00' AND action_summary LIKE '%系统配置%'\G
*************************** 1. row ***************************
COUNT(*): 55001
1 row in set (0.06 sec)mysql>mysql> SELECT COUNT(*) FROM audit_log WHERE log_time > '2025-07-01 00:00:00'\G
*************************** 1. row ***************************
COUNT(*): 220004
1 row in set (0.03 sec)mysql>
mysql> EXPLAIN SELECT COUNT(*) FROM audit_log WHERE log_time > '2025-07-01 00:00:00' AND action_summary LIKE '%系统配置%'\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: audit_logpartitions: NULLtype: range
possible_keys: aal_idx_log_timekey: aal_idx_log_timekey_len: 5ref: NULLrows: 152015filtered: 11.11Extra: Using index condition; Using where
1 row in set, 1 warning (0.01 sec)mysql>
mysql> SELECT COUNT(*) FROM audit_log WHERE log_time > '2025-07-01 00:00:00' AND action_summary LIKE '%系统配置%'\G
*************************** 1. row ***************************
COUNT(*): 55001
1 row in set (0.27 sec)mysql>
MySQL 5.6+ 并且是InnoDB时，可以增加全文索引
ALTER TABLE audit_log ADD FULLTEXT ft_action_summary (action_summary);查询时使用：（执行计划将从 ALL → fulltext，性能提升10倍以上）：
SELECT COUNT(*) FROM audit_log WHERE log_time > '2025-07-01'AND MATCH(action_summary) AGAINST('系统配置' IN BOOLEAN MODE);
按 log_time 进行分区后，禁止在该字段上做函数转换
-- 错误示范
SELECT * FROM audit_log WHERE DATE(log_time) BETWEEN '2024-03-16' AND '2024-04-15';
SELECT * FROM audit_log WHERE YEAR(log_time) = 2024 AND QUARTER(log_time) = 2;-- 正确示范：
SELECT * FROM audit_log WHERE log_time >= '2024-03-16 00:00:00' AND log_time <= '2024-04-15 23:59:59';
SELECT * FROM audit_log WHERE log_time >= '2024-04-01 00:00:00' AND log_time <= '2024-06-30 23:59:59';
- 破坏索引使用：对字段使用函数后，MySQL 无法使用该字段的索引；
- 破坏分区裁剪：分区优化依赖字段的原始值，函数处理会使优化器无法识别分区边界；
- 强制全表扫描：每次查询都需要对所有分区数据执行函数计算；
查询优化
当查询条件跨越多个分区（如 WHERE log_time BETWEEN '2024-03-16' AND '2024-04-15' 跨越Q1和Q2分区）时，可采取以下优化策略：
分区裁剪优化（核心策略）
确保分区裁剪生效，禁止在分区字段上使用函数
-- 禁止在分区字段（这里是 log_time）上使用函数
SELECT * FROM audit_log WHERE YEAR(log_time) = 2025;
-- 确保使用直接日期比较
SELECT count(*) FROM audit_log WHERE log_time >= '2025-01-01 00:00:00';-- 明确指定分区范围（当已知分区时）
SELECT * FROM audit_log PARTITION (p2024Q1, p2024Q2) WHERE log_time BETWEEN '2024-03-16' AND '2024-04-15';
覆盖索引+分区提示优化
-- 未优化查询 (跨两个季度分区)
SELECT COUNT(*) FROM audit_log WHERE log_time >= '2024-03-16 00:00:00' AND log_time <= '2024-04-15 23:59:59' AND action_type = 'UPDATE';-- 添加覆盖索引
ALTER TABLE audit_log ADD INDEX idx_cross_partition (log_time, action_type, operator_id);-- 优化后 (覆盖索引+分区提示)
SELECT COUNT(*) FROM audit_log PARTITION (p2024Q1, p2024Q2) USE INDEX (idx_cross_partition) WHERE log_time BETWEEN '2024-03-16 00:00:00' AND '2024-04-15 23:59:59' AND action_type = 'UPDATE';
-- 未优化查询 (跨两个季度分区)
mysql> SELECT COUNT(*) FROM audit_log WHERE log_time >= '2024-03-16 00:00:00' AND log_time <= '2024-04-15 23:59:59' AND action_type = 'UPDATE'\G
*************************** 1. row ***************************
COUNT(*): 0
1 row in set (0.38 sec)mysql>
mysql>
mysql> EXPLAIN SELECT COUNT(*) FROM audit_log WHERE log_time >= '2024-03-16 00:00:00' AND log_time <= '2024-04-15 23:59:59'AND action_type = 'UPDATE'\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: audit_logpartitions: p2024Q1,p2024Q2type: range
possible_keys: aal_idx_log_timekey: aal_idx_log_timekey_len: 5ref: NULLrows: 323026filtered: 10.00Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)mysql> mysql> ALTER TABLE audit_log ADD INDEX idx_cross_partition (log_time, action_type, operator_id);
Query OK, 14650004 rows affected, 1 warning (2 min 49.10 sec)mysql> -- 优化后 (覆盖索引+分区提示)
mysql> SELECT COUNT(*) FROM audit_log PARTITION (p2024Q1, p2024Q2) USE INDEX (idx_cross_partition) WHERE log_time BETWEEN '2024-03-16 00:00:00' AND '2024-04-15 23:59:59' AND action_type = 'UPDATE'\G
*************************** 1. row ***************************
COUNT(*): 0
1 row in set (0.09 sec)mysql>
mysql> EXPLAIN SELECT COUNT(*) FROM audit_log PARTITION (p2024Q1, p2024Q2) USE INDEX (idx_cross_partition) WHERE log_time BETWEEN
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: audit_logpartitions: p2024Q1,p2024Q2type: range
possible_keys: idx_cross_partitionkey: idx_cross_partitionkey_len: 207ref: NULLrows: 362118filtered: 10.00Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)mysql>
添加覆盖索引前的执行计划：Using index condition; Using where，先过滤时间，再回表过滤 action_type。
添加覆盖索引后的执行计划：Using where; Using index，直接在覆盖索引中完成过滤；
历史数据的归档
历史数据归档后，系统中如果还需要提供这部分数据的查询服务，需要提供单独的查询入口，或者在查询的业务逻辑中根据查询的日期范围切换所连接的数据表（或数据库）。
步骤1：创建与分区结构完全相同的归档表
CREATE TABLE audit_log_archive_2021Q3 LIKE audit_log;-- 移除归档表的分区定义（使其成为普通表）
ALTER TABLE audit_log_archive_2021Q3 REMOVE PARTITIONING;
操作记录：
mysql> CREATE TABLE audit_log_archive_2021Q3 LIKE audit_log;
Query OK, 0 rows affected, 1 warning (0.02 sec)mysql>
mysql> SHOW CREATE TABLE audit_log_archive_2021Q3\G
*************************** 1. row ***************************Table: audit_log_archive_2021Q3
Create Table: CREATE TABLE `audit_log_archive_2021Q3` (`id` bigint(20) unsigned NOT NULL,`log_time` datetime NOT NULL COMMENT '日志产生时间',`operator_id` varchar(64) NOT NULL COMMENT '操作人ID (索引)',`operator_name` varchar(128) NOT NULL COMMENT '操作人名称快照',`action_type` varchar(50) NOT NULL COMMENT '操作类型',`target_type` varchar(50) NOT NULL COMMENT '目标类型',`target_id` varchar(64) NOT NULL COMMENT '目标ID',`target_name` varchar(255) NOT NULL COMMENT '目标名称快照',`action_summary` varchar(500) NOT NULL COMMENT '操作摘要描述',`change_count` int(10) unsigned DEFAULT '0' COMMENT '变更项数量',`entity_count` int(10) unsigned DEFAULT '0' COMMENT '涉及实体数量',PRIMARY KEY (`id`,`log_time`),KEY `aal_idx_log_time` (`log_time`),KEY `aal_idx_operator` (`operator_id`),KEY `aal_idx_target` (`target_type`,`target_id`),KEY `aal_idx_summary` (`action_summary`(100)),KEY `idx_cross_partition` (`log_time`,`action_type`,`operator_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
/*!50500 PARTITION BY RANGE COLUMNS(log_time)
(PARTITION p2021Q3 VALUES LESS THAN ('2021-10-01') ENGINE = MyISAM,PARTITION p2021Q4 VALUES LESS THAN ('2022-01-01') ENGINE = MyISAM,PARTITION p2022Q1 VALUES LESS THAN ('2022-04-01') ENGINE = MyISAM,PARTITION p2022Q2 VALUES LESS THAN ('2022-07-01') ENGINE = MyISAM,PARTITION p2022Q3 VALUES LESS THAN ('2022-10-01') ENGINE = MyISAM,PARTITION p2022Q4 VALUES LESS THAN ('2023-01-01') ENGINE = MyISAM,PARTITION p2023Q1 VALUES LESS THAN ('2023-04-01') ENGINE = MyISAM,PARTITION p2023Q2 VALUES LESS THAN ('2023-07-01') ENGINE = MyISAM,PARTITION p2023Q3 VALUES LESS THAN ('2023-10-01') ENGINE = MyISAM,PARTITION p2023Q4 VALUES LESS THAN ('2024-01-01') ENGINE = MyISAM,PARTITION p2024Q1 VALUES LESS THAN ('2024-04-01') ENGINE = MyISAM,PARTITION p2024Q2 VALUES LESS THAN ('2024-07-01') ENGINE = MyISAM,PARTITION p2024Q3 VALUES LESS THAN ('2024-10-01') ENGINE = MyISAM,PARTITION p2024Q4 VALUES LESS THAN ('2025-01-01') ENGINE = MyISAM,PARTITION p2025Q1 VALUES LESS THAN ('2025-04-01') ENGINE = MyISAM,PARTITION p2025Q2 VALUES LESS THAN ('2025-07-01') ENGINE = MyISAM,PARTITION p2025Q3 VALUES LESS THAN ('2025-10-01') ENGINE = MyISAM,PARTITION p_future VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */
1 row in set, 1 warning (0.00 sec)mysql>
mysql> ALTER TABLE audit_log_archive_2021Q3 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.02 sec)mysql>
mysql> SHOW CREATE TABLE audit_log_archive_2021Q3\G
*************************** 1. row ***************************Table: audit_log_archive_2021Q3
Create Table: CREATE TABLE `audit_log_archive_2021Q3` (`id` bigint(20) unsigned NOT NULL,`log_time` datetime NOT NULL COMMENT '日志产生时间',`operator_id` varchar(64) NOT NULL COMMENT '操作人ID (索引)',`operator_name` varchar(128) NOT NULL COMMENT '操作人名称快照',`action_type` varchar(50) NOT NULL COMMENT '操作类型',`target_type` varchar(50) NOT NULL COMMENT '目标类型',`target_id` varchar(64) NOT NULL COMMENT '目标ID',`target_name` varchar(255) NOT NULL COMMENT '目标名称快照',`action_summary` varchar(500) NOT NULL COMMENT '操作摘要描述',`change_count` int(10) unsigned DEFAULT '0' COMMENT '变更项数量',`entity_count` int(10) unsigned DEFAULT '0' COMMENT '涉及实体数量',PRIMARY KEY (`id`,`log_time`),KEY `aal_idx_log_time` (`log_time`),KEY `aal_idx_operator` (`operator_id`),KEY `aal_idx_target` (`target_type`,`target_id`),KEY `aal_idx_summary` (`action_summary`(100)),KEY `idx_cross_partition` (`log_time`,`action_type`,`operator_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)mysql>
步骤2：执行分区交换
ALTER TABLE audit_log EXCHANGE PARTITION p2021Q3 WITH TABLE audit_log_archive_2021Q3;
操作记录：
mysql> ALTER TABLE audit_log EXCHANGE PARTITION p2021Q3 WITH TABLE audit_log_archive_2021Q3;
Query OK, 0 rows affected (0.01 sec)mysql>
mysql> SELECT COUNT(*) FROM audit_log_archive_2021Q3;
+----------+
| COUNT(*) |
+----------+
| 740000 |
+----------+
1 row in set (0.00 sec)mysql>
步骤3：验证后删除原分区
-- 检查交换后分区是否为空
SELECT COUNT(*) FROM audit_log PARTITION (p2021Q3);-- 删除空分区
ALTER TABLE audit_log DROP PARTITION p2021Q3;
操作记录：
mysql> SELECT COUNT(*) FROM audit_log PARTITION (p2021Q3);
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set, 1 warning (0.01 sec)mysql>
mysql> ALTER TABLE audit_log DROP PARTITION p2021Q3;
Query OK, 0 rows affected, 1 warning (0.02 sec)mysql>
mysql> SHOW CREATE TABLE audit_log\G
*************************** 1. row ***************************Table: audit_log
Create Table: CREATE TABLE `audit_log` (`id` bigint(20) unsigned NOT NULL,`log_time` datetime NOT NULL COMMENT '日志产生时间',`operator_id` varchar(64) NOT NULL COMMENT '操作人ID (索引)',`operator_name` varchar(128) NOT NULL COMMENT '操作人名称快照',`action_type` varchar(50) NOT NULL COMMENT '操作类型',`target_type` varchar(50) NOT NULL COMMENT '目标类型',`target_id` varchar(64) NOT NULL COMMENT '目标ID',`target_name` varchar(255) NOT NULL COMMENT '目标名称快照',`action_summary` varchar(500) NOT NULL COMMENT '操作摘要描述',`change_count` int(10) unsigned DEFAULT '0' COMMENT '变更项数量',`entity_count` int(10) unsigned DEFAULT '0' COMMENT '涉及实体数量',PRIMARY KEY (`id`,`log_time`),KEY `aal_idx_log_time` (`log_time`),KEY `aal_idx_operator` (`operator_id`),KEY `aal_idx_target` (`target_type`,`target_id`),KEY `aal_idx_summary` (`action_summary`(100)),KEY `idx_cross_partition` (`log_time`,`action_type`,`operator_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
/*!50500 PARTITION BY RANGE COLUMNS(log_time)
(PARTITION p2021Q4 VALUES LESS THAN ('2022-01-01') ENGINE = MyISAM,PARTITION p2022Q1 VALUES LESS THAN ('2022-04-01') ENGINE = MyISAM,PARTITION p2022Q2 VALUES LESS THAN ('2022-07-01') ENGINE = MyISAM,PARTITION p2022Q3 VALUES LESS THAN ('2022-10-01') ENGINE = MyISAM,PARTITION p2022Q4 VALUES LESS THAN ('2023-01-01') ENGINE = MyISAM,PARTITION p2023Q1 VALUES LESS THAN ('2023-04-01') ENGINE = MyISAM,PARTITION p2023Q2 VALUES LESS THAN ('2023-07-01') ENGINE = MyISAM,PARTITION p2023Q3 VALUES LESS THAN ('2023-10-01') ENGINE = MyISAM,PARTITION p2023Q4 VALUES LESS THAN ('2024-01-01') ENGINE = MyISAM,PARTITION p2024Q1 VALUES LESS THAN ('2024-04-01') ENGINE = MyISAM,PARTITION p2024Q2 VALUES LESS THAN ('2024-07-01') ENGINE = MyISAM,PARTITION p2024Q3 VALUES LESS THAN ('2024-10-01') ENGINE = MyISAM,PARTITION p2024Q4 VALUES LESS THAN ('2025-01-01') ENGINE = MyISAM,PARTITION p2025Q1 VALUES LESS THAN ('2025-04-01') ENGINE = MyISAM,PARTITION p2025Q2 VALUES LESS THAN ('2025-07-01') ENGINE = MyISAM,PARTITION p2025Q3 VALUES LESS THAN ('2025-10-01') ENGINE = MyISAM,PARTITION p_future VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */
1 row in set, 1 warning (0.00 sec)mysql>
将归档的数据迁移到同实例的其他数据库
mysql> RENAME TABLE demo_db.audit_log_archive_2021Q3 TO demo_db_bak.audit_log_archive_2021Q3;
Query OK, 0 rows affected (0.01 sec)mysql>
当前操作的用户要有这两个库的权限；
如果存储引擎是 InnoDB 的可以对迁移后的数据进行压缩：ALTER TABLE audit_log_archive_2021Q3 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
迁移到不同的MySQL服务器
# 1. 导出表结构和数据
mysqldump -uroot -p demo_db audit_log_archive_2021Q3 > archive_2021Q3.sql;# 2. 传输文件到目标服务器
scp archive_2021Q3.sql user@remote-server:/tmp/# 3. 在目标服务器导入
mysql -u root -p demo_db_bak < /tmp/archive_2021Q3.sql# 4. 在当前服务器上删除当前归档表
DROP TABLE current_db.audit_log_archive_2021Q3;
若目标服务器使用不同 MySQL 版本，需测试兼容性；
定期添加新分区
如：提前添加下一个季度（2025-09-30 之前添加 2025Q4 分区），或者提前添加下一年的分区（2025-12-31 之前添加 2026Q1，2026Q2，2026Q3，2026Q4）
-- 示例：添加下一季度（2025Q4）的分区
ALTER TABLE audit_log REORGANIZE PARTITION p_future INTO (PARTITION p2025Q4 VALUES LESS THAN ('2026-01-01'),PARTITION p_future VALUES LESS THAN (MAXVALUE)
);
操作记录：
mysql> ALTER TABLE audit_log REORGANIZE PARTITION p_future INTO (-> PARTITION p2025Q4 VALUES LESS THAN ('2026-01-01'),-> PARTITION p_future VALUES LESS THAN (MAXVALUE)-> );
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql>
mysql> SHOW CREATE TABLE audit_log\G
*************************** 1. row ***************************Table: audit_log
Create Table: CREATE TABLE `audit_log` (`id` bigint(20) unsigned NOT NULL,`log_time` datetime NOT NULL COMMENT '日志产生时间',`operator_id` varchar(64) NOT NULL COMMENT '操作人ID (索引)',`operator_name` varchar(128) NOT NULL COMMENT '操作人名称快照',`action_type` varchar(50) NOT NULL COMMENT '操作类型',`target_type` varchar(50) NOT NULL COMMENT '目标类型',`target_id` varchar(64) NOT NULL COMMENT '目标ID',`target_name` varchar(255) NOT NULL COMMENT '目标名称快照',`action_summary` varchar(500) NOT NULL COMMENT '操作摘要描述',`change_count` int(10) unsigned DEFAULT '0' COMMENT '变更项数量',`entity_count` int(10) unsigned DEFAULT '0' COMMENT '涉及实体数量',PRIMARY KEY (`id`,`log_time`),KEY `aal_idx_log_time` (`log_time`),KEY `aal_idx_operator` (`operator_id`),KEY `aal_idx_target` (`target_type`,`target_id`),KEY `aal_idx_summary` (`action_summary`(100)),KEY `idx_cross_partition` (`log_time`,`action_type`,`operator_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
/*!50500 PARTITION BY RANGE COLUMNS(log_time)
(PARTITION p2021Q4 VALUES LESS THAN ('2022-01-01') ENGINE = MyISAM,PARTITION p2022Q1 VALUES LESS THAN ('2022-04-01') ENGINE = MyISAM,PARTITION p2022Q2 VALUES LESS THAN ('2022-07-01') ENGINE = MyISAM,PARTITION p2022Q3 VALUES LESS THAN ('2022-10-01') ENGINE = MyISAM,PARTITION p2022Q4 VALUES LESS THAN ('2023-01-01') ENGINE = MyISAM,PARTITION p2023Q1 VALUES LESS THAN ('2023-04-01') ENGINE = MyISAM,PARTITION p2023Q2 VALUES LESS THAN ('2023-07-01') ENGINE = MyISAM,PARTITION p2023Q3 VALUES LESS THAN ('2023-10-01') ENGINE = MyISAM,PARTITION p2023Q4 VALUES LESS THAN ('2024-01-01') ENGINE = MyISAM,PARTITION p2024Q1 VALUES LESS THAN ('2024-04-01') ENGINE = MyISAM,PARTITION p2024Q2 VALUES LESS THAN ('2024-07-01') ENGINE = MyISAM,PARTITION p2024Q3 VALUES LESS THAN ('2024-10-01') ENGINE = MyISAM,PARTITION p2024Q4 VALUES LESS THAN ('2025-01-01') ENGINE = MyISAM,PARTITION p2025Q1 VALUES LESS THAN ('2025-04-01') ENGINE = MyISAM,PARTITION p2025Q2 VALUES LESS THAN ('2025-07-01') ENGINE = MyISAM,PARTITION p2025Q3 VALUES LESS THAN ('2025-10-01') ENGINE = MyISAM,PARTITION p2025Q4 VALUES LESS THAN ('2026-01-01') ENGINE = MyISAM,PARTITION p_future VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */
1 row in set, 1 warning (0.00 sec)mysql>
建议分区表还是在 InnoDB 下使用，将 audit_log 转为 InnoDB
-- 转换表引擎
ALTER TABLE audit_log ENGINE=InnoDB;
-- 转换后需重建所有分区
ALTER TABLE audit_log REBUILD PARTITION ALL;
mysql> ALTER TABLE audit_log ENGINE=InnoDB;
Query OK, 13910004 rows affected (2 min 34.70 sec)mysql>
mysql> ALTER TABLE audit_log REBUILD PARTITION ALL;
Query OK, 0 rows affected (3 min 50.76 sec)mysql>
mysql> SHOW CREATE TABLE audit_log\G
*************************** 1. row ***************************Table: audit_log
Create Table: CREATE TABLE `audit_log` (`id` bigint(20) unsigned NOT NULL,`log_time` datetime NOT NULL COMMENT '日志产生时间',`operator_id` varchar(64) NOT NULL COMMENT '操作人ID (索引)',`operator_name` varchar(128) NOT NULL COMMENT '操作人名称快照',`action_type` varchar(50) NOT NULL COMMENT '操作类型',`target_type` varchar(50) NOT NULL COMMENT '目标类型',`target_id` varchar(64) NOT NULL COMMENT '目标ID',`target_name` varchar(255) NOT NULL COMMENT '目标名称快照',`action_summary` varchar(500) NOT NULL COMMENT '操作摘要描述',`change_count` int(10) unsigned DEFAULT '0' COMMENT '变更项数量',`entity_count` int(10) unsigned DEFAULT '0' COMMENT '涉及实体数量',PRIMARY KEY (`id`,`log_time`),KEY `aal_idx_log_time` (`log_time`),KEY `aal_idx_operator` (`operator_id`),KEY `aal_idx_target` (`target_type`,`target_id`),KEY `aal_idx_summary` (`action_summary`(100)),KEY `idx_cross_partition` (`log_time`,`action_type`,`operator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50500 PARTITION BY RANGE COLUMNS(log_time)
(PARTITION p2021Q4 VALUES LESS THAN ('2022-01-01') ENGINE = InnoDB,PARTITION p2022Q1 VALUES LESS THAN ('2022-04-01') ENGINE = InnoDB,PARTITION p2022Q2 VALUES LESS THAN ('2022-07-01') ENGINE = InnoDB,PARTITION p2022Q3 VALUES LESS THAN ('2022-10-01') ENGINE = InnoDB,PARTITION p2022Q4 VALUES LESS THAN ('2023-01-01') ENGINE = InnoDB,PARTITION p2023Q1 VALUES LESS THAN ('2023-04-01') ENGINE = InnoDB,PARTITION p2023Q2 VALUES LESS THAN ('2023-07-01') ENGINE = InnoDB,PARTITION p2023Q3 VALUES LESS THAN ('2023-10-01') ENGINE = InnoDB,PARTITION p2023Q4 VALUES LESS THAN ('2024-01-01') ENGINE = InnoDB,PARTITION p2024Q1 VALUES LESS THAN ('2024-04-01') ENGINE = InnoDB,PARTITION p2024Q2 VALUES LESS THAN ('2024-07-01') ENGINE = InnoDB,PARTITION p2024Q3 VALUES LESS THAN ('2024-10-01') ENGINE = InnoDB,PARTITION p2024Q4 VALUES LESS THAN ('2025-01-01') ENGINE = InnoDB,PARTITION p2025Q1 VALUES LESS THAN ('2025-04-01') ENGINE = InnoDB,PARTITION p2025Q2 VALUES LESS THAN ('2025-07-01') ENGINE = InnoDB,PARTITION p2025Q3 VALUES LESS THAN ('2025-10-01') ENGINE = InnoDB,PARTITION p2025Q4 VALUES LESS THAN ('2026-01-01') ENGINE = InnoDB,PARTITION p_future VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
1 row in set (0.00 sec)mysql>