CREATE TABLE `salary_static` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
`school_id` int(11) NOT NULL COMMENT '學校id',
`student_id` int(11) NOT NULL COMMENT '畢業生id',
`salary` int(11) NOT NULL DEFAULT '0' COMMENT '畢業薪水',
`year` int(11) NOT NULL COMMENT '畢業年份',
PRIMARY KEY (`id`),
KEY `school_id_key` (`school_id`) USING BTREE,
KEY `year_school_key` (`year`,`school_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='畢業生薪水數據統計';
delimiter //
CREATE PROCEDURE init_salary_static()
BEGIN
DECLARE year INT;
DECLARE schid INT;
DECLARE stuid INT;
SET year = 2000;
WHILE year 2020 DO
START TRANSACTION;
SET schid = 1;
WHILE schid 100 DO
SET stuid = 1;
WHILE stuid 1000 DO
insert into salary_static(school_id,student_id,salary,year) values (schid,stuid,floor(rand()*10000),year);
SET stuid = stuid + 1;
END WHILE;
SET schid = schid + 1;
END WHILE;
SET year = year + 1;
COMMIT;
END WHILE;
END //
delimiter ;
call init_salary_static();
測試數據創建完成后，執行以下sql語句進行統計查詢。
select school_id,avg(salary) from salary_static where year between 2016 and 2019 group by school_id;
SET optimizer_trace="enabled=on";
select school_id,avg(salary) from salary_static where year between 2016 and 2019 group by school_id;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
table_scan全表掃描時預估需要掃描1973546條記錄，通過show table status like "salary_static"命令可得全表記錄為82411520字節（Data_length），innodb每個記錄頁為16KB即全表掃描需要讀取82411520/1024/16 = 5030個記錄頁。
