åºäºæ¶é´ç±»ååºæä¹ååè¿å®ç°ç¯ãç»èç¯ãä»å¤©æ¥ç»§ç»å享ä¸ä¸æ¶é´ç±»ååºççå®æ¡ä¾ï¼ æ家äºèç½å ¬å¸æ°æ®åºç³»ç»å çååºè¡¨è°ä¼è¿ç¨ã
é®é¢ä¸èæ¯ï¼
- åå¼ è¡¨æ°æ®é太大ï¼æ¯å¤©ä¼äº§ç 10W æ¡è®°å½ï¼ä¸å¹´å°±æ¯ 3650W æ¡è®°å½ï¼
- 对è¿å¼ 表çæ¥è¯¢ 95% é½æ¯å¨æä¸å¤©æè å 天å ï¼è¿æ»¤åºé´æ大ä¸è¶ è¿ä¸ä¸ªæãæ¯å¦å¨2019å¹´3æ1æ¥ã2019å¹´4æ20æ¥æè æ¯2019å¹´5æ1æ¥å2019å¹´5æ5æ¥è¿ä¸ªæ¶é´æ®µå ãå¶å°ä¼æ¶åå°è·¨æã跨年æ¥è¯¢ï¼ä½æ¯é¢çå¾ä½ã
- è®°å½ä¿ç10å¹´ãä¹å°±æ¯å表3.6亿æ¡è®°å½ï¼å表太大ï¼ä¸ä¾¿äºç®¡çï¼åæå¦æå表æåï¼ä¿®å¤ä¹é¾ã
- å表æ¥è¯¢æ§è½å¾å·®ï¼å¯¹åå²æ°æ®å é¤æ§è½ä¹å¾å·®ã
åºäºä»¥ä¸éæ±åæåå¾åºç»è®ºï¼
- æ¥è¯¢è¿æ»¤çæ°æ®èå´ç¸å¯¹æ¯è¾éä¸ï¼ä¸æ¯é£ä¹æ©æ£ï¼è¦åæ¶èèè¿ææ°æ®æ¸ çæ§è½é®é¢ã
- èèæ表æå为10å¼ æ°è¡¨ï¼ä¸å¼ æ¯å½å表ï¼å©ä½9å¼ æ¯åå²å½æ¡£è¡¨ï¼å½å表åæ¾æè¿ä¸¤å¹´çæ°æ®ï¼æ¯å°å¹´åºè¿ç§»èæ§æ°æ®å°åå²è¡¨è¿è¡å½æ¡£ï¼å¹¶ä¸å¯¹è¿æåå²æ°æ®è¿è¡æ¸ çã
- èè对é¨åè¿æ»¤åºæ¯ä½¿ç¨ MySQL ååºè¡¨ï¼é常éå 95% çæ¥è¯¢ï¼å¯ä»¥ä½¿ç¨ååºç½®æ¢åè½ææ°æ®ç§»å°åå²è¡¨ã
- ååºè¡¨å¸¦æ¥å 个好å¤ï¼ ä¸æ¯æ¥è¯¢æ§è½æåï¼äºæ¯ç®¡çæ¹ä¾¿ï¼è¿ææ°æ®ç´æ¥å¿«éæ¸ çï¼ä¸æ¯å¯¹åºç¨éæï¼ææ¶ä¸éè¦åºç¨æ¹ä»£ç ã
æ¥ä¸æ¥çç表çä¼åè¿ç¨ï¼
ç±äºéç§èèï¼ä¸æ¹ä¾¿è´´åå§è¡¨ç»æï¼è¿éç¨ç»æç®åç示ä¾è¡¨æ¥çä¸ä¼åè¿ç¨ãåå§è¡¨ä¸º pt_old ï¼ç¼©åå段个æ°å°3ï¼è®°å½æ°ç¼©å10å为 3650W ï¼æ¯å¹´365Wï¼å®¢æ·åæ¥å段æ30个ï¼è®°å½æ°3.6亿ï¼ï¼è®°å½èå´ä»2011å¹´å°2020å¹´ï¼å好åå¹´çæ°æ®ã
(localhost:ytt)<mysql>show create table pt_old\G
*************************** 1. row ***************************
Table: pt_old
Create Table: CREATE TABLE `pt_old` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`r1` int DEFAULT NULL,
`log_date` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_log_date` (`log_date`)
) ENGINE=InnoDB AUTO_INCREMENT=64306811 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
(localhost:ytt)<mysql>select min(log_date),max(log_date),count(*) from pt_old;
+---------------+---------------+----------+
| min(log_date) | max(log_date) | count(*) |
+---------------+---------------+----------+
| 2011-01-01 | 2020-12-31 | 36500000 |
+---------------+---------------+----------+
1 row in set (21.14 sec)
å 导åºåå§è¡¨æ°æ®ï¼æç §å¹´å¯¼åº10份æ°æ®ï¼ï¼åæç´æ¥å¯¼å ¥å°æ°ååºè¡¨ï¼æ§è¡ä»¥ä¸èæ¬ï¼
root@ytt-unbuntu:/home/ytt/scripts# cat pt_export
#!/bin/sh
for i in `seq 2011 2020`
do
{
mysql -D ytt -e "select * from pt_old where log_date between '$i-01-01' and '$i-12-31' into outfile '/var/lib/mysql-files/pt_$i.csv' fields terminated by ',' "
} &
done
wait
root@ytt-unbuntu:/home/ytt/scripts# ./pt_export
root@ytt-unbuntu:/var/lib/mysql-files# ls -sihl
æ»ç¨é 788M
5767677 79M -rw-r----- 1 mysql mysql 79M 2æ 4 15:39 pt_2011.csv
5775332 79M -rw-r----- 1 mysql mysql 79M 2æ 4 15:42 pt_2012.csv
5775334 79M -rw-r----- 1 mysql mysql 79M 2æ 4 15:42 pt_2013.csv
5774596 79M -rw-r----- 1 mysql mysql 79M 2æ 4 15:42 pt_2014.csv
5775335 79M -rw-r----- 1 mysql mysql 79M 2æ 4 15:42 pt_2015.csv
5775333 79M -rw-r----- 1 mysql mysql 79M 2æ 4 15:42 pt_2016.csv
5775329 79M -rw-r----- 1 mysql mysql 79M 2æ 4 15:42 pt_2017.csv
5775330 79M -rw-r----- 1 mysql mysql 79M 2æ 4 15:42 pt_2018.csv
5775336 79M -rw-r----- 1 mysql mysql 79M 2æ 4 15:42 pt_2019.csv
5775331 79M -rw-r----- 1 mysql mysql 79M 2æ 4 15:42 pt_2020.csv
åå«ä»¥å¹´ä¸ºç²åº¦ï¼å»ºç«10å¼ è¡¨ï¼å ¶ä¸è¡¨ pt_2020 为ååºè¡¨ï¼
root@ytt-unbuntu:/home/ytt/scripts# for i in `seq 2011 2020`;do mysql -e"use ytt;create table pt_$i like pt_old;";done;
ç±äº MySQL ååºè¡¨ç¡¬æ§è§å®ï¼ååºé®å¿ 须为主é®æè 主é®çä¸é¨åï¼ææ¶é´å段å å°ä¸»é®éã
(localhost:ytt)<mysql>alter table pt_2020 drop primary key, add primary key (id,log_date);
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
ç»è¡¨ pt_2020 æ·»å ååºï¼æå¯è½åæ¾å½å¹´ä»¥åå»å¹´çæ°æ®ï¼å æ¤è¦æç §å¤©æ¥ååºï¼å¹¶ä¸åæ两年ï¼è¿æ ·å°äºæ°çä¸å¹´ï¼å°±ç´æ¥æèæ§æ°æ®è¿ç§»åºå»ï¼ï¼ä¿®æ¹ä¸ä¹åçåå¨è¿ç¨å¦ä¸ï¼
DELIMITER $
USE `ytt`$
DROP PROCEDURE IF EXISTS `sp_add_partition_pt_current`$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_add_partition_pt_current`(
IN f_year_start YEAR,
IN f_year_end YEAR,
IN f_tbname VARCHAR(64)
)
BEGIN
DECLARE v_days INT UNSIGNED DEFAULT 365;
DECLARE v_year DATE DEFAULT '2011-01-01';
DECLARE v_partition_name VARCHAR(64) DEFAULT '';
DECLARE v_log_date DATE;
DECLARE i,j INT UNSIGNED DEFAULT 1;
SET @stmt = '';
SET @stmt_begin = CONCAT('ALTER TABLE ',f_tbname,' PARTITION BY RANGE COLUMNS(log_date)(');
SET i = f_year_start;
WHILE i <= f_year_end DO
SET v_year = CONCAT(i,'-01-01');
SET v_days = DATEDIFF(DATE_ADD(v_year,INTERVAL 1 YEAR),v_year);
SET j = 1;
WHILE j <= v_days DO
SET v_log_date = DATE_ADD(v_year,INTERVAL j DAY);
SET v_partition_name = CONCAT('p',i,'_',LPAD(j,3,'0'));
SET @stmt = CONCAT(@stmt,'PARTITION ',v_partition_name,' VALUES LESS THAN(''',v_log_date,'''),');
SET j = j + 1;
END WHILE;
SET i = i + 1;
END WHILE;
SET @stmt_end = 'PARTITION p_max VALUES LESS THAN (maxvalue))';
SET @stmt = CONCAT(@stmt_begin,@stmt,@stmt_end);
PREPARE s1 FROM @stmt;
EXECUTE s1;
DROP PREPARE s1;
SELECT NULL,NULL,NULL INTO @stmt,@stmt_begin,@stmt_end;
END$
DELIMITER ;
(localhost:ytt)<mysql>call sp_add_partition_pt_current(2020,2021,'pt_2020');
Query OK, 1 row affected (42.11 sec)
åå«å¯¼å ¥åå§æ°æ®ï¼2020å¹´çæ°æ®å¯¼å ¥è¡¨ pt_2020 ï¼å ¶ä»æ°æ®å¯¼å ¥å°åå²è¡¨ pt_2011 å° pt_2019 ã
root@ytt-unbuntu:/home/ytt/scripts# cat pt_import
#!/bin/sh
for i in `seq 2011 2020`
do
{
mysql -D ytt -e "load data infile '/var/lib/mysql-files/pt_$i.csv' into table pt_$i fields terminated by ',' "
} &
done
wait
root@ytt-unbuntu:/home/ytt/scripts# ./pt_import
æ´æ¹è¡¨ p_2020 为å½å表ï¼
(localhost:ytt)<mysql>alter table pt_2020 rename to pt_current;
Query OK, 0 rows affected (0.12 sec)
æ¥ä¸æ¥æ们è¦éªè¯ä¸æ¹é åçé¢æææï¼
第ä¸ï¼æ¥è¯¢æ§è½ååºè¡¨è¦å¤§å¹ æåã
第äºï¼ååºè¡¨ç管çãè¿ç»´æçä¹è¦å¤§å¹ æåã
å¦æè¿ä¸¤ç¹é½è¾¾å°è¦æ±ï¼å°±å¯ä»¥ç´æ¥æååºè¡¨æ¹å为åå§è¡¨ï¼åå§è¡¨å é¤ã
å æ¥éªè¯æ¥è¯¢æ§è½æ¯å¦æå¤§å¹ æåï¼
第ä¸æ¡æ¥è¯¢ï¼æ¥è¯¢å½å¤©çè®°å½
以ä¸åºäºè¡¨ pt_old æ§è¡æ¶é´ä¸º 9.99 ç§ï¼èåºäºè¡¨ pt_current æ§è¡æ¶é´ä¸º 0.02 ç§ï¼ååºè¡¨æ¯æ®é表æ¶é´ä¸å¿«500åã
(localhost:ytt)<mysql>select * from pt_old where log_date = '2020-03-01';
...
1 row in set (9.99 sec)
(localhost:ytt)<mysql>select count(*) from pt_current where log_date = '2020-03-01';
...
9593 rows in set (0.02 sec)
第äºæ¡æ¥è¯¢ï¼æ¥è¯¢æªè³2020å¹´å¹´åºæè¿5天çè®°å½
æç»æ§è¡æ¶é´ pt_old 为 0.53 ç§ï¼pt_current 为 0.02 ç§ï¼ååºè¡¨æ¯åå§è¡¨å¿«20å¤åã
(localhost:ytt)<mysql>select count(*) from pt_old where log_date = '2020-03-01';
...
40408 rows in set (0.63 sec)
(localhost:ytt)<mysql>select count(*) from pt_current where log_date = '2020-03-01';
...
40408 rows in set (0.02 sec)
å¾ææ¾æ¥è¯¢æ§è½è¾¾å°é¢æææã
ç°å¨æ¥çä¸ç®¡çä¸è¿ç»´æ§è½æ¯å¦ææåï¼
æ¢ç¶ç¨ååºè¡¨ï¼å°±ä¼æ¶åå°ä¸ä¸ªå¾æ£æçé®é¢ï¼æ¯å°å¹´åºï¼å¦ä½è°æ´ååºè¡¨æ¥éåºæ°å¢è®°å½ï¼MySQL 并没æç´æ¥çæ¹æ³ï¼ ä¸è¿æ们å¯ä»¥å©ç¨é»è®¤ååº p_max æ¥æå·¥æ©å æªæ¥çååºã
æ¥çä¸è¡¨ p_current çååºæ°æ®ï¼
(localhost:ytt)<mysql>select left(partition_name,5) p,sum(table_rows) cnt from information_schema.partitions where table_name = 'pt_current' group by leftt(partition_name,5);
+-------+---------+
| p | cnt |
+-------+---------+
| p2020 | 3641722 |
| p2021 | 0 |
| p_max | 0 |
+-------+---------+
3 rows in set (0.02 sec)
ç®ååªæ2020å¹´ææ°æ®ï¼2021年没ææ°æ®ï¼é£å°äº2021å¹´æ«è®°å½ä¼èªå¨å å ¥å°ååº p_max éã æ以å°äº2022å¹´1æ1æ¥åæ¨åå¾æ2020æ´å¹´çæ°æ®æªåºå»å为 pt_2020 ï¼å¹¶æ2022å¹´çååºå®ä¹å è¿å»ã
é£ä¾ç §æ们çåæï¼æåæ¥åä¸ä¸ªèªå¨æ©å ååºçåå¨è¿ç¨ï¼å¯ä»¥é å OS ç JOB æè MySQL ç EVENT æ¥èªå¨è¿è¡ï¼ä»£ç å¦ä¸ï¼
DELIMITER $
USE `ytt`$
DROP PROCEDURE IF EXISTS `sp_autoextend_partition_pt_current`$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_autoextend_partition_pt_current`(
IN f_year YEAR
)
BEGIN
DECLARE v_days INT UNSIGNED DEFAULT 365;
DECLARE v_days_interval DATE DEFAULT '2018-12-31';
DECLARE i INT UNSIGNED DEFAULT 1;
SET @stmt = '';
SET v_days = DATEDIFF(CONCAT(f_year+1,'-01-01'),CONCAT(f_year,'-01-01'));
SET @stmt_begin = 'ALTER TABLE pt_current REORGANIZE PARTITION p_max into(';
WHILE i <= v_days DO
SET v_days_interval = DATE_ADD(CONCAT(f_year,'-01-01'),INTERVAL i DAY);
SET @stmt = CONCAT(@stmt,'PARTITION p',f_year,'_',LPAD(i,3,"0"),' VALUES LESS THAN (''',v_days_interval,'''),');
SET i = i + 1;
END WHILE;
SET @stmt_end = 'PARTITION p_max VALUES LESS THAN (maxvalue))';
SET @stmt = CONCAT(@stmt_begin,@stmt,@stmt_end);
PREPARE s1 FROM @stmt;
EXECUTE s1;
DROP PREPARE s1;
SET @stmt = NULL;
SET @stmt_begin = NULL;
SET @stmt_end = NULL;
END$
DELIMITER ;
ç°å¨æ¥æ©å 2022å¹´çååºæ°æ®ï¼
(localhost:ytt)<mysql>call sp_autoextend_partition_pt_current(2022);
Query OK, 0 rows affected (14.55 sec)
æ¥ä¸æ¥æ¯å¹´åºéè¦åçäºæ å°±æ¯æå»å¹´çæ°æ®æªèµ°ï¼å¹¶ä¸å é¤æ§ååºå®ä¹ï¼æ·»å æ°çä¸å¹´ååºå®ä¹ã
ç°å¨æ¶é´å°äº2022å¹´ï¼é£å ç» pt_current æå ¥2021å¹´çæ°æ®ï¼çå®ç¯å¢éï¼è¿é¨åæ°æ®æ¯å·²ç»åå¨çï¼ï¼
(localhost:ytt)<mysql>insert into pt_current (r1,log_date) select r1,date_add(log_date,interval 1 year) from pt_current;
Query OK, 3641722 rows affected (2 min 28.75 sec)
Records: 3641722 Duplicates: 0 Warnings: 0
(localhost:ytt)<mysql>select left(partition_name,5) p,sum(table_rows) cnt from information_schema.partitions where table_name = 'pt_current' group by left(partition_name,5);
+-------+---------+
| p | cnt |
+-------+---------+
| p2020 | 3641722 |
| p2021 | 3641726 |
| p2022 | 0 |
| p_max | 0 |
+-------+---------+
4 rows in set (0.02 sec)
åæ2020å¹´çæ°æ®æªå°åå²è¡¨ï¼
(localhost:ytt)<mysql>create table pt_2020 like pt_old;
Query OK, 0 rows affected (0.05 sec)
(localhost:ytt)<mysql>insert into pt_2020 select * from pt_current where log_date between '2020-01-01' and '2020-12-31';
Query OK, 3641722 rows affected (1 min 12.54 sec)
Records: 3641722 Duplicates: 0 Warnings: 0
å é¤è¿ææ°æ®ï¼
(localhost:ytt)<mysql>SELECT CONCAT('alter table ytt.pt_current drop partition ',partition_name,';') FROM information_schema.`PARTITIONS` WHERE table_schema = 'ytt' AND table_name = 'pt_current' AND partition_name like 'p2020%' into outfile '/var/lib/mysql-files/drop_expire_partition_2020.sql';
Query OK, 366 rows affected (0.00 sec)
mysql> \. /var/lib/mysql-files/drop_expire_partition_2020.sql
Query OK, 0 rows affected (0.83 sec)
Records: 0 Duplicates: 0 Warnings: 0
...
Query OK, 0 rows affected (0.82 sec)
Records: 0 Duplicates: 0 Warnings: 0
...
éè¦æ³¨æçç¹ï¼ ååºå®ä¹ä¸å®è¦æè§åï¼è¿æ ·æå©äºåææ¸ çè¿ææ°æ®ã
å ³äº MySQL çææ¯å 容ï¼ä½ 们è¿æä»ä¹æ³ç¥éçåï¼èµ¶ç´§çè¨åè¯å°ç¼å§ï¼