1.mysql 大表优化
--(1).案例。
(1)创建分区表
CREATE TABLE tsdp_server_log_info (
id varchar(64) NOT NULL COMMENT '主键',
execute_starttime datetime NOT NULL,
waterfall bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id,execute_starttime),
KEY waterfall (waterfall)
)
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(execute_starttime)) (
PARTITION p20240112 VALUES LESS THAN (TO_DAYS('2024-01-12')),
PARTITION p20240119 VALUES LESS THAN (TO_DAYS('2024-01-13')),
PARTITION p20240126 VALUES LESS THAN (TO_DAYS('2024-01-14'))
);
Query OK, 0 rows affected (0.02 sec)
root@itpux 16:34: [mydb]>
(2)创建事件,定时删除30天前的分区，并创建新的分区。
--按日分区。
--日志表记录分区的删除和增加。
drop table mydb.add_partition_log;
create table mydb.add_partition_log(add_time datetime ,part_name varchar(30),flag varchar(20));
--切换到Mydb数据库
use mydb;
--drop procedure P_ADD_TAB_PARTITION;
DELIMITER //
CREATE PROCEDURE P_ADD_TAB_PARTITION()
BEGIN
SELECT COUNT(1) INTO @PART_NUM1 FROM INFORMATION_SCHEMA.PARTITIONS
WHERE UPPER(TABLE_NAME)='TSDP_SERVER_LOG_INFO'
AND PARTITION_NAME=CONCAT('p',DATE_FORMAT((SELECT DATE_SUB(NOW(), INTERVAL -2 DAY)),'%Y%m%d'));
IF @PART_NUM1=0 THEN
SET @ADD_PARTITION = CONCAT("alter table mydb.tsdp_server_log_info add partition(partition ", concat('p',date_format((SELECT DATE_SUB(now(), INTERVAL -2 DAY)),'%Y%m%d')) , " VALUES LESS THAN (TO_DAYS('",date_format((SELECT DATE_SUB(now(), INTERVAL -2 DAY)),'%Y-%m-%d'),"')));");
PREPARE STMT FROM @ADD_PARTITION;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
INSERT INTO mydb.add_partition_log VALUES(NOW(),CONCAT('p',DATE_FORMAT((SELECT DATE_SUB(NOW(), INTERVAL -2 DAY)),'%Y%m%d')),'add');
ELSE
INSERT INTO mydb.add_partition_log VALUES(NOW(),concat('p',DATE_FORMAT((SELECT DATE_SUB(NOW(), INTERVAL -2 DAY)),'%Y%m%d')),'already exist');
END IF;
SELECT COUNT(1) INTO @PART_NUM2 FROM INFORMATION_SCHEMA.PARTITIONS
WHERE UPPER(TABLE_NAME)='TSDP_SERVER_LOG_INFO'
AND PARTITION_NAME=CONCAT('p',DATE_FORMAT((SELECT DATE_SUB(NOW(), INTERVAL 30 DAY)),'%Y%m%d'));
IF @PART_NUM2=0 THEN
INSERT INTO mydb.add_partition_log VALUES(NOW(),concat('p',DATE_FORMAT((SELECT DATE_SUB(NOW(), INTERVAL 30 DAY)),'%Y%m%d')),'does not exist');
ELSE
SET @DROP_PARTITION= CONCAT("alter table mydb.tsdp_server_log_info drop partition ", CONCAT('p',DATE_FORMAT((SELECT DATE_SUB(NOW(), INTERVAL 30 DAY)),'%Y%m%d')),';');
PREPARE STMT FROM @DROP_PARTITION;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
INSERT INTO mydb.add_partition_log VALUES(NOW(),CONCAT('p',DATE_FORMAT((SELECT DATE_SUB(NOW(), INTERVAL 30 DAY)),'%Y%m%d')),'drop');
END IF;
END//
DELIMITER ;
--默认每次执行时删除30天前的分区,同时添加下一个自然日的分区。
--如果想要每次执行时添加sysdate+7 日的分区,则可以修改如下这段:
concat('p',DATE_FORMAT((SELECT DATE_SUB(NOW(), INTERVAL -2 DAY)),'%Y%m%d')) --1天后的日分区。
--修改为:
concat('p',DATE_FORMAT((SELECT DATE_SUB(NOW(), INTERVAL -8 DAY)),'%Y%m%d')) --7天后的日分区。
--(3)验证分区功能
root@itpux 18:41: [mydb]> insert into tsdp_server_log_info values(19,'2024-01-19 23:00:00',null); Query OK, 1 row affected (0.00 sec) root@itpux 18:41: [mydb]> select * from tsdp_server_log_info where execute_starttime between '2024-01-19 00:00:00' and '2024-01-19 23:59:00'; +----+---------------------+-----------+ | id | execute_starttime | waterfall | +----+---------------------+-----------+ | 19 | 2024-01-19 23:00:00 | 1 | +----+---------------------+-----------+ 1 row in set (0.00 sec) root@itpux 18:42: [mydb]> explain select * from tsdp_server_log_info where execute_starttime between '2024-01-19 00:00:00' and '2024-01-19 23:59:00'; +----+-------------+----------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | tsdp_server_log_info | p20240120 | index | NULL | waterfall | 8 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+----------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
(4)
--创建定时任务事件。
--从1月12日开始执行,以后每天11:00定时运行一次。 DROP EVENT E_ADD_PARTION; CREATE EVENT E_ADD_PARTION ON SCHEDULE EVERY 1 DAY STARTS '2024-01-12 11:00:00' DO CALL P_ADD_TAB_PARTITION; --主库开启定时任务。 root@itpux 10:29: [mydb]> set global event_scheduler=1; Query OK, 0 rows affected (0.00 sec) vi /etc/my.cnf [mysqld] event_scheduler=1 --主库查看事件。 root@itpux 10:48: [mydb]> show events; +------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ | mydb | E_ADD_PARTION | root@localhost | SYSTEM | RECURRING | NULL | 1 | DAY | 2024-01-12 10:50:00 | NULL | ENABLED | 513306 | utf8 | utf8_general_ci | utf8_general_ci | +------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ --从库查看事件。 --可以看到从库禁用事件。 root@itpux 10:47: [mydb]> show events; +------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+--------------------+------------+----------------------+----------------------+--------------------+ | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+--------------------+------------+----------------------+----------------------+--------------------+ | mydb | E_ADD_PARTION | root@localhost | SYSTEM | RECURRING | NULL | 1 | DAY | 2024-01-12 10:50:00 | NULL | SLAVESIDE_DISABLED | 513306 | utf8 | utf8_general_ci | utf8_general_ci | +------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+--------------------+------------+----------------------+----------------------+--------------------+
(5)定时任务开始前检查
--定时任务开始前检查。 root@itpux 10:55: [mydb]> select * from add_partition_log; Empty set (0.00 sec) root@itpux 10:55: [mydb]> show create table tsdp_server_log_info; +----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tsdp_server_log_info | CREATE TABLE `tsdp_server_log_info` ( `id` varchar(64) NOT NULL COMMENT '主键', `execute_starttime` datetime NOT NULL, `waterfall` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`,`execute_starttime`), KEY `waterfall` (`waterfall`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (TO_DAYS(execute_starttime)) (PARTITION p20240112 VALUES LESS THAN (739262) ENGINE = InnoDB, PARTITION p20240113 VALUES LESS THAN (739263) ENGINE = InnoDB, PARTITION p20240120 VALUES LESS THAN (739270) ENGINE = InnoDB) */ | +----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
(6)检查定时任务执行后的表分区
--每天11点执行一次,现在11:01检查一次,发现已经执行成功。 --我的系统时间比正常时间快一天，时间可以忽略。 root@itpux 10:55: [mydb]> select * from add_partition_log; +---------------------+-----------+----------------+ | add_time | part_name | flag | +---------------------+-----------+----------------+ | 2024-01-20 11:00:00 | p20240122 | add | | 2024-01-20 11:00:00 | p20231221 | does not exist | +---------------------+-----------+----------------+ --默认按日分区,每次添加下一个自然日的分区。 --同时删除30天前的分区,如果分区不存在,就写入日志文件,分区不存在"does not exist "。 --检查新增的分区: CREATE TABLE `tsdp_server_log_info` ( `id` varchar(64) NOT NULL COMMENT '主键', `execute_starttime` datetime NOT NULL, `waterfall` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`,`execute_starttime`), KEY `waterfall` (`waterfall`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (TO_DAYS(execute_starttime)) (PARTITION p20240112 VALUES LESS THAN (739262) ENGINE = InnoDB, PARTITION p20240113 VALUES LESS THAN (739263) ENGINE = InnoDB, PARTITION p20240120 VALUES LESS THAN (739270) ENGINE = InnoDB, PARTITION p20240122 VALUES LESS THAN (739272) ENGINE = InnoDB) */ ; --可以看到新的分区已经添加。 --说明定时任务生效。
2.总结
mysql的分区功能相对较弱，目前尚无法实现自动分区，不过我们可以通过创建事件的方式，实现自动增加分区的功能。