zabbix数据库优化，2024年最新GitHub标星1w的软件测试架构师必备技能

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新软件测试全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024b （备注软件测试）

正文

ALTER TABLE trends ADD INDEX clock (clock);
ALTER TABLE trends_uint ADD INDEX clock (clock);
ALTER TABLE EVENTS ADD INDEX objectid (objectid,VALUE,clock);

如果存在这五个存储过程则删除

DROP PROCEDURE IF EXISTS partition_create;
DROP PROCEDURE IF EXISTS partition_drop;
DROP PROCEDURE IF EXISTS partition_maintenance;
DROP PROCEDURE IF EXISTS partition_maintenance_all;
DROP PROCEDURE IF EXISTS partition_verify;

第一次执行存储过程

CALL partition_maintenance_all(‘zabbix’);

inser to crontab 每天执行一次存储过程，生成新的分区和删除老的分区
#!/bin/sh
/xxxxx/mysql/bin/mysql -uzabbix -pzabbixmonitor --socket=/xxxx/mysql/tmp/mysql.sock --database=zabbix -e “CALL partition_maintenance_all(‘zabbix’);”

存储过程详情如下：

/ partition_create /

DELIMITER $$
CREATE PROCEDURE partition_create(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), PARTITIONNAME VARCHAR(64), CLOCK INT)
BEGIN
/
SCHEMANAME =The DB schema in which to make changes
TABLENAME =The table with partitions to potentially delete
PARTITIONNAME =The name of the partition to create
/
/
Verify that the partition does not already exist
/

DECLARE RETROWS INT;
SELECT COUNT(1) INTO RETROWS
FROM information_schema.partitions
WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND partition_description >= CLOCK;

IF RETROWS =0 THEN
/*
1.Print a message indicating that a partition was created.
2.Create the SQL to create the partition.
3.Execute the SQL from#2.
*/
SELECT CONCAT(“partition_create(”, SCHEMANAME,“,”, TABLENAME,“,”, PARTITIONNAME,“,”, CLOCK,“)”) AS msg;
SET @SQL= CONCAT(‘ALTER TABLE ‘, SCHEMANAME,’.’, TABLENAME,’ ADD PARTITION (PARTITION ‘, PARTITIONNAME,’ VALUES LESS THAN (‘, CLOCK,’));');
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;

END$$
DELIMITER ;

/ partition_drop /

DELIMITER $$
CREATE PROCEDURE partition_drop(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE BIGINT)
BEGIN
/
SCHEMANAME =The DB schema in which to make changes
TABLENAME =The table with partitions to potentially delete
DELETE_BELOW_PARTITION_DATE =Delete any partitions with names that are dates older than this one (yyyy-mm-dd)
/
DECLARE done INT DEFAULT FALSE;
DECLARE drop_part_name VARCHAR(16);

/*
Get a list of all the partitions that are older than the date
in DELETE_BELOW_PARTITION_DATE.All partitions are prefixed with
a “p”, so use SUBSTRING TO get rid of that character.
*/
DECLARE myCursor CURSOR FOR
SELECT partition_name
FROM information_schema.partitions
WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND CAST(SUBSTRING(partition_name FROM 2) AS UNSIGNED)< DELETE_BELOW_PARTITION_DATE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done= TRUE;

/*
Create the basics forwhen we need to drop the partition.Also, create
@drop_partitions to hold a comma-delimited list of all partitions that
should be deleted.
*/
SET @alter_header= CONCAT(“ALTER TABLE “, SCHEMANAME,”.”, TABLENAME," DROP PARTITION “);
SET @drop_partitions=”";

/*
Start looping through all the partitions that are too old.
*/
OPEN myCursor;
read_loop: LOOP
FETCH myCursor INTO drop_part_name;
IF done THEN
LEAVE read_loop;
END IF;
SET @drop_partitions= IF(@drop_partitions=“”, drop_part_name, CONCAT(@drop_partitions,“,”, drop_part_name));
END LOOP;
IF @drop_partitions!=“” THEN
/*
1.Build the SQL to drop all the necessary partitions.
2.Run the SQL to drop the partitions.
3.Printout the table partitions that were deleted.
*/
SET @full_sql= CONCAT(@alter_header,@drop_partitions,“;”);
PREPARE STMT FROM @full_sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;

SELECT CONCAT(SCHEMANAME,“.”, TABLENAME) AS table,@drop_partitions AS partitions_deleted;
ELSE
/*
No partitions are being deleted, so printout"N/A"(Not applicable) to indicate
that no changes were made.
*/
SELECT CONCAT(SCHEMANAME,“.”, TABLENAME) AS table,“N/A” AS partitions_deleted;
END IF;

END$$
DELIMITER ;

/ partition_maintenance /
DELIMITER $$
CREATE PROCEDURE partition_maintenance(SCHEMA_NAME VARCHAR(32), TABLE_NAME VARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT)
BEGIN
DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(16);
DECLARE PARTITION_NAME VARCHAR(16);
DECLARE LESS_THAN_TIMESTAMP INT;
DECLARE CUR_TIME INT;

CALL partition_verify(SCHEMA_NAME, TABLE_NAME, HOURLY_INTERVAL);
SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(NOW(),‘%Y-%m-%d 00:00:00’));

SET @__interval=1;
create_loop: LOOP
IF @__interval> CREATE_NEXT_INTERVALS THEN
LEAVE create_loop;
END IF;

SET LESS_THAN_TIMESTAMP = CUR_TIME +(HOURLY_INTERVAL *@__interval*3600);
SET PARTITION_NAME = FROM_UNIXTIME(CUR_TIME + HOURLY_INTERVAL *(@__interval-1)*3600,‘p%Y%m%d%H00’);
CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);
SET @__interval=@__interval+1;
END LOOP;

SET OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), INTERVAL KEEP_DATA_DAYS DAY),‘%Y%m%d0000’);
CALL partition_drop(SCHEMA_NAME, TABLE_NAME, OLDER_THAN_PARTITION_DATE);

END$$
DELIMITER ;

/ partition_verify /
DELIMITER $$
CREATE PROCEDURE partition_verify(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11))
BEGIN
DECLARE PARTITION_NAME VARCHAR(16);
DECLARE RETROWS INT(11);
DECLARE FUTURE_TIMESTAMP TIMESTAMP;

/*
*Checkif any partitions exist for the given SCHEMANAME.TABLENAME.
*/
SELECT COUNT(1) INTO RETROWS
FROM information_schema.partitions
WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND partition_name IS NULL;

/*
*If partitions donot exist, go ahead and partition the table
*/
IF RETROWS =1 THEN
/*
*Take the current date at 00:00:00and add HOURLYINTERVAL to it.Thisis the timestamp below which we will store values.
*Webegin partitioning based on the beginning of a day.Thisis because we don’t want to generate a random partition
* that won’t necessarily fall in line with the desired partition naming (ie: if the hour interval is 24 hours, we could
*end up creating a partition now named “p201403270600"when all other partitions will be like “p201403280000”).
*/
SET FUTURE_TIMESTAMP = TIMESTAMPADD(HOUR, HOURLYINTERVAL, CONCAT(CURDATE(),” ",‘00:00:00’));
SET PARTITION_NAME = DATE_FORMAT(CURDATE(),‘p%Y%m%d%H00’);

–Create the partitioning query
SET @__PARTITION_SQL= CONCAT(“ALTER TABLE “, SCHEMANAME,”.”, TABLENAME," PARTITION BY RANGE(clock)“);
SET @__PARTITION_SQL= CONCAT(@__PARTITION_SQL,”(PARTITION “, PARTITION_NAME,” VALUES LESS THAN (“, UNIX_TIMESTAMP(FUTURE_TIMESTAMP),”));");

–Run the partitioning query
PREPARE STMT FROM @__PARTITION_SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;

END$$

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注软件测试）

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注软件测试）
[外链图片转存中…(img-dfDMifSW-1713170998812)]

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！