個人博客地址： https://note.raokun.top
擁抱ChatGPT，國內訪問網站：https://www.playchat.top

使用場景：
由于表數據量巨大，導致一些統(tǒng)計相關的sql執(zhí)行非常慢，使用戶有非常不好的體驗，并且sql和數據庫已經沒有優(yōu)化空間了。(并且該統(tǒng)計信息數據實時性要求不高的前提下)

解決方案：
整體思路：創(chuàng)建預處理表——通過定時任務將數據插入到結果表——統(tǒng)計信息時直接通過結果表進行查詢——大大提高響應速度

注：1.結果表中需要包含查詢條件里的所有字段

2.定時任務可以根據實際需要設定頻率

3.最好創(chuàng)建一個與結果表表結構一樣的臨時表用于數據暫存,防止在插入數據這個時間段上導致結果表數據空白。（直接將臨時表數據插入到結果表速度很快這段時間可以忽略）

方案示例：
第一步：創(chuàng)建結果表

drop?table?if?exists?`user_study_statistics`;
CREATE?TABLE?`project_statistics`?(
??`project_id`?varchar(128)?CHARACTER?SET?utf8mb4?COLLATE?utf8mb4_bin?DEFAULT?NULL?COMMENT?'項目id',
??`project_unit_id`?int(11)?NOT?NULL?COMMENT?'單位工程id',
??`unit_name`?varchar(255)?DEFAULT?NULL?COMMENT?'單位工程名稱',
??`project_name`?varchar(255)?DEFAULT?NULL?COMMENT?'項目名稱',
??`file_count`?int(8)?NOT?NULL?DEFAULT?'0'?COMMENT?'文件總數',
??`file_count_30`?int(8)?NOT?NULL?DEFAULT?'0'?COMMENT?'文件總數（30天內）',
??`file_count_90`?int(8)?NOT?NULL?DEFAULT?'0'?COMMENT?'文件總數（90天內）',
??`file_count_180`?int(8)?NOT?NULL?DEFAULT?'0'?COMMENT?'文件總數（180天內）',
??`check_count`?int(8)?DEFAULT?NULL?COMMENT?'檢查次數',
??`check_count_30`?int(8)?DEFAULT?NULL?COMMENT?'檢查次數（30天內）',
??`check_count_90`?int(8)?DEFAULT?NULL?COMMENT?'檢查次數（90天內）',
??`check_count_180`?int(8)?DEFAULT?NULL?COMMENT?'檢查次數（180天內）',
??`check_num`?int(8)?NOT?NULL?DEFAULT?'0'?COMMENT?'檢查文件總數',
??`check_num_30`?int(8)?NOT?NULL?DEFAULT?'0'?COMMENT?'檢查文件總數（30天內）',
??`check_num_90`?int(8)?NOT?NULL?DEFAULT?'0'?COMMENT?'檢查文件總數（90天內）',
??`check_num_180`?int(8)?NOT?NULL?DEFAULT?'0'?COMMENT?'檢查文件總數（180天內）',
??`check_scale`?varchar(26)?DEFAULT?NULL?COMMENT?'檢查文件占比',
??`check_scale_30`?varchar(26)?DEFAULT?NULL?COMMENT?'檢查文件占比（30天內）',
??`check_scale_90`?varchar(26)?DEFAULT?NULL?COMMENT?'檢查文件占比（90天內）',
??`check_scale_180`?varchar(26)?DEFAULT?NULL?COMMENT?'檢查文件占比（180天內）',
??`pass_scale`?varchar(26)?DEFAULT?NULL?COMMENT?'通過率',
??`pass_scale_30`?varchar(26)?DEFAULT?NULL?COMMENT?'通過率（30天內）',
??`pass_scale_90`?varchar(26)?DEFAULT?NULL?COMMENT?'通過率（90天內）',
??`pass_scale_180`?varchar(26)?DEFAULT?NULL?COMMENT?'通過率（180天內）',
??`correct_count`?int(8)?DEFAULT?NULL?COMMENT?'整改條數',
??`correct_count_30`?int(8)?DEFAULT?NULL?COMMENT?'整改條數（30天內）',
??`correct_count_90`?int(8)?DEFAULT?NULL?COMMENT?'整改條數（90天內）',
??`correct_count_180`?int(8)?DEFAULT?NULL?COMMENT?'整改條數（180天內）',
??`out_time_count`?int(8)?DEFAULT?NULL?COMMENT?'逾期未完成整改條數',
??`out_time_count_30`?int(8)?DEFAULT?NULL?COMMENT?'逾期未完成整改條數（30天內）',
??`out_time_count_90`?int(8)?DEFAULT?NULL?COMMENT?'逾期未完成整改條數（90天內）',
??`out_time_count_180`?int(8)?DEFAULT?NULL?COMMENT?'逾期未完成整改條數（180天內）'
)?ENGINE=InnoDB?DEFAULT?CHARSCREATE?TABLE?`project_statistics_tmp`?(
??`project_id`?varchar(128)?CHARACTER?SET?utf8mb4?COLLATE?utf8mb4_bin?DEFAULT?NULL?COMMENT?'項目id',
??`project_unit_id`?int(11)?NOT?NULL?COMMENT?'單位工程id',
??`unit_name`?varchar(255)?DEFAULT?NULL?COMMENT?'單位工程名稱',
??`project_name`?varchar(255)?DEFAULT?NULL?COMMENT?'項目名稱',
??`file_count`?int(8)?NOT?NULL?DEFAULT?'0'?COMMENT?'文件總數',
??`file_count_30`?int(8)?NOT?NULL?DEFAULT?'0'?COMMENT?'文件總數（30天內）',
??`file_count_90`?int(8)?NOT?NULL?DEFAULT?'0'?COMMENT?'文件總數（90天內）',
??`file_count_180`?int(8)?NOT?NULL?DEFAULT?'0'?COMMENT?'文件總數（180天內）',
??`check_count`?int(8)?DEFAULT?NULL?COMMENT?'檢查次數',
??`check_count_30`?int(8)?DEFAULT?NULL?COMMENT?'檢查次數（30天內）',
??`check_count_90`?int(8)?DEFAULT?NULL?COMMENT?'檢查次數（90天內）',
??`check_count_180`?int(8)?DEFAULT?NULL?COMMENT?'檢查次數（180天內）',
??`check_num`?int(8)?NOT?NULL?DEFAULT?'0'?COMMENT?'檢查文件總數',
??`check_num_30`?int(8)?NOT?NULL?DEFAULT?'0'?COMMENT?'檢查文件總數（30天內）',
??`check_num_90`?int(8)?NOT?NULL?DEFAULT?'0'?COMMENT?'檢查文件總數（90天內）',
??`check_num_180`?int(8)?NOT?NULL?DEFAULT?'0'?COMMENT?'檢查文件總數（180天內）',
??`check_scale`?varchar(26)?DEFAULT?NULL?COMMENT?'檢查文件占比',
??`check_scale_30`?varchar(26)?DEFAULT?NULL?COMMENT?'檢查文件占比（30天內）',
??`check_scale_90`?varchar(26)?DEFAULT?NULL?COMMENT?'檢查文件占比（90天內）',
??`check_scale_180`?varchar(26)?DEFAULT?NULL?COMMENT?'檢查文件占比（180天內）',
??`pass_scale`?varchar(26)?DEFAULT?NULL?COMMENT?'通過率',
??`pass_scale_30`?varchar(26)?DEFAULT?NULL?COMMENT?'通過率（30天內）',
??`pass_scale_90`?varchar(26)?DEFAULT?NULL?COMMENT?'通過率（90天內）',
??`pass_scale_180`?varchar(26)?DEFAULT?NULL?COMMENT?'通過率（180天內）',
??`correct_count`?int(8)?DEFAULT?NULL?COMMENT?'整改條數',
??`correct_count_30`?int(8)?DEFAULT?NULL?COMMENT?'整改條數（30天內）',
??`correct_count_90`?int(8)?DEFAULT?NULL?COMMENT?'整改條數（90天內）',
??`correct_count_180`?int(8)?DEFAULT?NULL?COMMENT?'整改條數（180天內）',
??`out_time_count`?int(8)?DEFAULT?NULL?COMMENT?'逾期未完成整改條數',
??`out_time_count_30`?int(8)?DEFAULT?NULL?COMMENT?'逾期未完成整改條數（30天內）',
??`out_time_count_90`?int(8)?DEFAULT?NULL?COMMENT?'逾期未完成整改條數（90天內）',
??`out_time_count_180`?int(8)?DEFAULT?NULL?COMMENT?'逾期未完成整改條數（180天內）'
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8?COMMENT='項目檢查狀態(tài)統(tǒng)計表';

ET=utf8?COMMENT='項目檢查狀態(tài)統(tǒng)計表_臨時表';


創(chuàng)建視圖：

CREATE?VIEW?`v_project_statistics_info`?AS
SELECT?project_id,project_unit_id,unit_name,project_name,
COUNT(1)?file_count,--?文件總數
COUNT((datediff(date(now()),date(gmt_create))?<=30?)?OR?null)?file_count_30,
COUNT((datediff(date(now()),date(gmt_create))?<=90?)?OR?null)?file_count_90,
COUNT((datediff(date(now()),date(gmt_create))?<=180?)?OR?null)?file_count_180,
SUM(check_count)?as?check_count,--?檢查次數
SUM(CASE?WHEN?(datediff(date(now()),date(modify_time))?<=30?)?THEN?check_count?ELSE?0?END)?AS?check_count_30,
SUM(CASE?WHEN?(datediff(date(now()),date(modify_time))?<=90?)?THEN?check_count?ELSE?0?END)?AS?check_count_90,
SUM(CASE?WHEN?(datediff(date(now()),date(modify_time))?<=180?)?THEN?check_count?ELSE?0?END)?AS?check_count_180,
COUNT(check_count>0?OR?null)?as?check_num,--?檢查文件總數
COUNT((datediff(date(now()),date(modify_time))?<=30?)?and?check_count>0?OR?null)?as?check_num_30,
COUNT((datediff(date(now()),date(modify_time))?<=90?)?and?check_count>0?OR?null)?as?check_num_90,
COUNT((datediff(date(now()),date(modify_time))?<=180?)?and?check_count>0?OR?null)?as?check_num_180,?
CONCAT(ROUND(COUNT(check_count>0?OR?null)/COUNT(1)*100,0),'%')?as?check_tage,?--?檢查文件占比
CONCAT(ROUND(COUNT((datediff(date(now()),date(modify_time))?<=30?)?and?check_count>0?OR?null)/COUNT((datediff(date(now()),date(gmt_create))?<=30?)?OR?null)*100,0),'%')?as?check_scale_30,
CONCAT(ROUND(COUNT((datediff(date(now()),date(modify_time))?<=90?)?and?check_count>0?OR?null)/COUNT((datediff(date(now()),date(gmt_create))?<=90?)?OR?null)*100,0),'%')?as?check_scale_90,
CONCAT(ROUND(COUNT((datediff(date(now()),date(modify_time))?<=180?)?and?check_count>0?OR?null)/COUNT((datediff(date(now()),date(gmt_create))?<=180?)?OR?null)*100,0),'%')?as?check_scale_180,
CONCAT(ROUND(COUNT(check_status=2?OR?null)/COUNT(check_count>0?OR?null)*100,0),'%')?as?pass_tage,?--?通過率
CONCAT(ROUND(COUNT((datediff(date(now()),date(modify_time))?<=30?)?and?check_status=2?OR?null)/COUNT((datediff(date(now()),date(modify_time))?<=30?)?and?check_count>0?OR?null)*100,0),'%')?as?pass_scale_30,
CONCAT(ROUND(COUNT((datediff(date(now()),date(modify_time))?<=90?)?and?check_status=2?OR?null)/COUNT((datediff(date(now()),date(modify_time))?<=90?)?and?check_count>0?OR?null)*100,0),'%')?as?pass_scale_90,
CONCAT(ROUND(COUNT((datediff(date(now()),date(modify_time))?<=180?)?and?check_status=2?OR?null)/COUNT((datediff(date(now()),date(modify_time))?<=180?)?and?check_count>0?OR?null)*100,0),'%')?as?pass_scale_180,
SUM(correct_count)?as?correct_count,--?整改條數
SUM(correct_count_30)?as?correct_count_30,
SUM(correct_count_90)?as?correct_count_90,
SUM(correct_count_180)?as?correct_count_180,
SUM(out_time_count)?as?out_time_count,?--?逾期未完成整改條數
SUM(out_time_count_30)?as?out_time_count_30,
SUM(out_time_count_90)?as?out_time_count_90,
SUM(out_time_count_180)?as?out_time_count_180
from?(

SELECT?SUBSTRING_INDEX(d.bus_id,'_',1)?as?project_id,d.project_unit_id,d.node_id,ifnull(d.check_count,0)?as?check_count,d.check_status,ifnull(a.correct_count,0)?as?correct_count,ifnull(a.correct_count_30,0)?as?correct_count_30,ifnull(a.correct_count_90,0)?as?correct_count_90,ifnull(a.correct_count_180,0)?as?correct_count_180,ifnull(a.out_time_count,0)?as?out_time_count,ifnull(a.out_time_count_30,0)?as?out_time_count_30,ifnull(a.out_time_count_90,0)?as?out_time_count_90,ifnull(a.out_time_count_180,0)?as?out_time_count_180,u.unit_name,p.project_name,d.modify_time,d.gmt_create?from?v_doc_info?d
INNER?JOIN?project_detail?p?on?p.local_project_id=d.project_id
LEFT?JOIN?project_unit?u?on?u.id=d.project_unit_id
left?join(?
SELECT?local_project_id,IFNULL(correct_soure_id,0)as?node_id,COUNT(1)?as?correct_count
,COUNT(datediff(date(now()),date(create_time))?<=30)?as?correct_count_30
,COUNT(datediff(date(now()),date(create_time))?<=90)?as?correct_count_90
,COUNT(datediff(date(now()),date(create_time))?<=180)?as?correct_count_180
,count((have_deleted=0?and?correct_end_time<NOW())?or?null)?as?out_time_count
,count((have_deleted=0?and?correct_end_time<NOW()?and?(datediff(date(now()),date(create_time))?<=30))?or?null)?as?out_time_count_30
,count((have_deleted=0?and?correct_end_time<NOW()?and?(datediff(date(now()),date(create_time))?<=90))?or?null)?as?out_time_count_90
,count((have_deleted=0?and?correct_end_time<NOW()?and?(datediff(date(now()),date(create_time))?<=180))?or?null)?as?out_time_count_180??from?doc_correct?GROUP?BY?local_project_id,correct_soure_id
)?a?on?a.node_id=d.node_id
UNION?ALL
SELECT?d.project_id,d.remote_project_id?as?project_unit_id,d.id?as?node_id,ifnull(d.check_count,0)?as?check_count,d.check_status,ifnull(a.correct_count,0)?as?correct_count,ifnull(a.correct_count_30,0)?as?correct_count_30,ifnull(a.correct_count_90,0)?as?correct_count_90,ifnull(a.correct_count_180,0)?as?correct_count_180,ifnull(a.out_time_count,0)?as?out_time_count,ifnull(a.out_time_count_30,0)?as?out_time_count_30,ifnull(a.out_time_count_90,0)?as?out_time_count_90,ifnull(a.out_time_count_180,0)?as?out_time_count_180,u.unit_name,p.project_name,d.modify_time,d.gmt_create?from?v_datumdata_info?d
INNER?JOIN?project_detail?p?on?p.local_project_id=d.project_id
LEFT?JOIN?project_unit?u?on?u.id=d.remote_project_id
left?join(?
SELECT?local_project_id,IFNULL(correct_soure_id,0)as?node_id,COUNT(1)?as?correct_count
,COUNT(datediff(date(now()),date(create_time))?<=30)?as?correct_count_30
,COUNT(datediff(date(now()),date(create_time))?<=90)?as?correct_count_90
,COUNT(datediff(date(now()),date(create_time))?<=180)?as?correct_count_180
,count((have_deleted=0?and?correct_end_time<NOW())?or?null)?as?out_time_count
,count((have_deleted=0?and?correct_end_time<NOW()?and?(datediff(date(now()),date(create_time))?<=30))?or?null)?as?out_time_count_30
,count((have_deleted=0?and?correct_end_time<NOW()?and?(datediff(date(now()),date(create_time))?<=90))?or?null)?as?out_time_count_90
,count((have_deleted=0?and?correct_end_time<NOW()?and?(datediff(date(now()),date(create_time))?<=180))?or?null)?as?out_time_count_180??from?doc_correct?GROUP?BY?local_project_id,correct_soure_id
)?a?on?a.node_id=d.id
)t?GROUP?BY?project_id,project_unit_id


第二步：創(chuàng)建預處理定時任務

????/**
?????*?資料檢查統(tǒng)計預加載定時任務
?????*/
????@PostConstruct?//程序啟動時就執(zhí)行一次
????@Scheduled(cron?=?"${schedul.statistics.checkStatistics:0?0?*?*?*??}")?//默認定時頻率每小時執(zhí)行一次
????public?void?preProjectStatistics(){
????????//0.刪除臨時表里的數據
????????statisticsDao.deleteStatisticsTmpData();
????????//1.將數據插入臨時表
????????statisticsDao.insertStatisticsTmpData();
????????//2.刪除正式表里的數據
????????statisticsDao.deleteStatisticsData();
????????//3.將臨時表里的數據插入正式表
????????statisticsDao.insertStatisticsData();
????????//3.刪除臨時表里的數據
????????statisticsDao.deleteStatisticsTmpData();
????}


對應的sql語句：

<insert?id="insertStatisticsTmpData">
????????insert?into?project_statistics_tmp?SELECT?*?from?v_project_statistics_info
????</insert>
????<delete?id="deleteStatisticsData"?parameterType="java.lang.Long">
????????delete?from?project_statistics;
????</delete>
????<insert?id="insertStatisticsData">
????????insert?into?project_statistics
????select?*?from?project_statistics_tmp;
????</insert>
????<delete?id="deleteStatisticsTmpData"?parameterType="java.lang.Long">
????????delete?from?project_statistics_tmp;
????</delete>


第三步： 修改之前的統(tǒng)計sql從多表關聯(lián)到查詢單表速度有了質的提升