慢sql优化
当数据量太大，且需要频繁查询时，会严重影响数据库性能，查询速度也会很慢，不利于数据展示效率的提高，此时，建议在数据库中通过存储过程新建一张临时表，将需要查询的结果数据插入到临时表中，然后调度此存储过程，并设置更新时间。实现每天在库中查询一次数据到临时表，然后实时查询临时表的结果数据，来提升性能。
- 新建存储过程，建表，每次都覆盖插入结果数据
DROP PROCEDURE IF EXISTS mysql.wukushitu_liantong;
--新建存储过程
--注1：参数根据实际情况设置，in/out不写的情况下默认为in(存储过程名后必须要加括号，及时参数为空，也要加括号)
--注2：begin与end之间写需要执行的脚本，可根据实际情况设置输出变量并赋值(也经常赋予计算逻辑)
--注3：每块脚本后都要加分号
create PROCEDURE mysql.wukushitu_liantong
(
OUT P_OUT_ERROR INT, -- Return value:
OUT P_OUT_ERRMSG VARCHAR(100), -- Return message, success or failure
OUT P_OUT_ROWCOUNT INT, -- Row number Count
IN P_TASKID VARCHAR(64), -- taskId
IN P_TENANTID VARCHAR(36) -- TENANTID
)
BEGIN
set P_OUT_ERROR = 0;
set P_OUT_ERRMSG= 'Success!';
set P_OUT_ROWCOUNT = 0;
-- 新建临时表
drop table if exists mysql.test_day_report;
create table mysql.test_day_report (
DATA_DAY date DEFAULT NULL,
PROVINCE varchar(50) DEFAULT NULL,
CITY varchar(50) DEFAULT NULL,
CARRIER varchar(50) DEFAULT NULL,
YEAR varchar(20) DEFAULT NULL,
SITE_TYPE varchar(50) DEFAULT NULL,
SUPPLIER varchar(255) DEFAULT NULL,
LIBRARY varchar(50) DEFAULT NULL,
INDICATOR_CODE varchar(50) DEFAULT NULL,
REASONS varchar(50) DEFAULT NULL,
INDICATOR_TARGET decimal(18,0) DEFAULT NULL,
INDICATOR_VALUE decimal(18,0) DEFAULT NULL,
TENANTID VARCHAR(63) DEFAULT NULL,
PROVINCE_1 VARCHAR(63) DEFAULT NULL
);
--添加索引
ALTER TABLE mysql.test_day_report ADD INDEX idx_tenantid ( tenantid );
--将结果数据插入临时表
insert into mysql.test_day_report select a.*,d.tenantid,d.province_1
from
(
select
DATA_DAY,
PROVINCE,
CITY,
CARRIER,
YEAR,
SITE_TYPE,
SUPPLIER,
LIBRARY,
INDICATOR_CODE,
REASONS,
INDICATOR_TARGET,
INDICATOR_VALUE
from mysql.cu_site_unicom_day
) a
left join
(
select
b.tenantid tenantid,
c.province_1,
count(b.tenantid)
from
(
select
TenantId,
City,
sum(case when isvaluesite='Y' then 1 else 0 end)
from mysql.cwr_4libsite
group by tenantid,city
) b
left join
(
select
tenantid,
left(tenantname,char_length(tenantname)-2) province_1
from mysql.cwr_tenant
where left(tenantname,char_length(tenantname)-2) not in('联通','电信') and left(tenantname,char_length(tenantname)-2) not like '%test%'
) c
on b.TenantId=c.tenantid where c.province_1 IS NOT null group by b.tenantid,c.province_1
) d
on a.PROVINCE=d.province_1;
END;
- 调度存储过程
call mysql.wukushitu_liantong(@a,@b,@c,'','');#调度的时候参数必须要加@符号
老化数据保留
数据库中考虑到存储问题，有时候宽表需要设置老化周期，为避免老化后的数据查询不到的问题，可以新建存储过程，将历史数据加以保留，只替换更新新数据，以保证业务正常使用。
- 新建存储过程，建表，首次插入结果数据
--菲律宾pldt_cell_hu存储过程
drop procedure if exists mysql.test_procedure;
create procedure mysql.test_procedure(
OUT P_OUT_ERROR INT, -- Return value:
OUT P_OUT_ERRMSG VARCHAR(100), -- Return message, success or failure
OUT P_OUT_ROWCOUNT INT, -- Row number Count
IN P_TASKID VARCHAR(64), -- taskId
IN P_TENANTID VARCHAR(36) -- TENANTID
)
BEGIN
set P_OUT_ERROR = 0;set P_OUT_ERRMSG= 'Success!';set P_OUT_ROWCOUNT = 0;
--建表
create table if not exists mysql.test_report(
data_date date default null,
site_no varchar(255) default null,
cell_name varchar(255) default null,
band varchar(255) default null,
onair_time date default null,
pre_cell_priority varchar(255) default null,
pre_site_priority varchar(255) default null,
pre_txrx varchar(255) default null,
pre_user decimal(20,4) default null,
pre_traffic decimal(20,4) default null,
pre_throughput decimal(20,4) default null,
post_cell_priority varchar(255) default null,
post_site_priority varchar(255) default null,
post_txrx varchar(255) default null,
post_user decimal(20,4) default null,
post_traffic decimal(20,4) default null,
post_throughput decimal(20,4) default null,
user_gain decimal(20,4) default null,
traffic_gain decimal(20,4) default null,
throughput_gain decimal(20,4) default null,
solution varchar(255) default null
);
--添加联合主键，为替换更新建立参考字段
alter table mysql.test_report add primary key(site_no,cell_name);
--首次插入数据
insert into mysql.test_report
select
m.*,
z.cell_priority pre_cell_priority,
z.site_priority pre_site_priority,
z.txrx pre_txrx,
z.rrc_user pre_user,
z.payload pre_traffic,
z.lte_dl_user_throughput_kbps pre_throughput,
w.cell_priority post_cell_priority,
w.site_priority post_site_priority,
w.txrx post_txrx,
w.rrc_user post_user,
w.payload post_traffic,
w.lte_dl_user_throughput_kbps post_throughput,
(w.rrc_user-z.rrc_user)/z.rrc_user*100 user_gain,
(w.payload-z.payload)/z.payload*100 traffic_gain,
(w.lte_dl_user_throughput_kbps-z.lte_dl_user_throughput_kbps)/z.lte_dl_user_throughput_kbps*100 throughput_gain,
case when w.sector_priority IN("prio1-Critical", "prio2-high util", "prio3-Warning") then "fail" ELSE "passed" END solution
from
(select
y.data_date,
x.site_no site_no,
x.cell_name cell_name,
y.band,
x.onair_time onair_time
from
(select max(date(a.extFiled36)) onair_time,b.site_no,b.sector_name,b.cell_name
from mysql.test_attr a,mysql.test_request b where a.requestId=b.requestId and a.extFiled36<>'' group by b.site_no,b.sector_name,b.cell_name) x
left join
(select
data_date,
site_no,
cell_name,
band from mysql.test) y
on x.site_no=y.site_no and x.cell_name=y.cell_name
where datediff(x.onair_time,y.data_date)>=0 and datediff(x.onair_time,y.data_date)<7) m
left join
(select
data_date,
site_no site_no,
cell_name cell_name,
cell_priority,
site_priority,
band,
txrx,
sum(rrc_user) rrc_user,
sum(payload) payload,
sum(lte_dl_user_throughput_kbps) lte_dl_user_throughput_kbps
from mysql.test group by data_date,site_no,cell_name,cell_priority,site_priority,band,txrx) z
on z.site_no=m.site_no and z.cell_name=m.cell_name and z.data_date=date_add(m.data_date,interval -7 day)
left join
(select
data_date,
site_no site_no,
cell_name cell_name,
cell_priority,
site_priority,
band,
txrx,
sum(rrc_user) rrc_user,
sum(payload) payload,
sum(lte_dl_user_throughput_kbps) lte_dl_user_throughput_kbps
from mysql.test group by data_date,site_no,cell_name,cell_priority,site_priority,band,txrx) w
on w.site_no=m.site_no and w.cell_name=m.cell_name and w.data_date=date_add(m.data_date,interval 14 day);
end
- 调度存储过程，使新建的表生效
call mysql.test_procedure(@a,@b,@c,'','');
- 新建存储过程，替换更新有变化的新数据，保留没变化的历史数据
--数据更新存储过程
drop procedure if exists mysql.test_procedure_update;
create procedure mysql.test_procedure_update(
OUT P_OUT_ERROR INT, -- Return value:
OUT P_OUT_ERRMSG VARCHAR(100), -- Return message, success or failure
OUT P_OUT_ROWCOUNT INT, -- Row number Count
IN P_TASKID VARCHAR(64), -- taskId
IN P_TENANTID VARCHAR(36) -- TENANTID
)
BEGIN
set P_OUT_ERROR = 0;set P_OUT_ERRMSG= 'Success!';set P_OUT_ROWCOUNT = 0;
--更新存在新onair_time的小区数据，保留无新onair_time的小区数据
replace into mysql.test_report
select
m.*,
z.cell_priority pre_cell_priority,
z.site_priority pre_site_priority,
z.txrx pre_txrx,
z.rrc_user pre_user,
z.payload pre_traffic,
z.lte_dl_user_throughput_kbps pre_throughput,
w.cell_priority post_cell_priority,
w.site_priority post_site_priority,
w.txrx post_txrx,
w.rrc_user post_user,
w.payload post_traffic,
w.lte_dl_user_throughput_kbps post_throughput,
(w.rrc_user-z.rrc_user)/z.rrc_user*100 user_gain,
(w.payload-z.payload)/z.payload*100 traffic_gain,
(w.lte_dl_user_throughput_kbps-z.lte_dl_user_throughput_kbps)/z.lte_dl_user_throughput_kbps*100 throughput_gain,
case when w.sector_priority IN("prio1-Critical", "prio2-high util", "prio3-Warning") then "fail" ELSE "passed" END solution
from
(select
y.data_date,
x.site_no site_no,
x.cell_name cell_name,
y.band,
x.onair_time onair_time
from
(select max(date(a.extFiled36)) onair_time,b.site_no,b.sector_name,b.cell_name
from mysql.test_attr a,mysql.test_request b where a.requestId=b.requestId and a.extFiled36<>'' group by b.site_no,b.sector_name,b.cell_name) x
left join
(select
data_date,
site_no,
cell_name,
band from mysql.test) y
on x.site_no=y.site_no and x.cell_name=y.cell_name
where datediff(x.onair_time,y.data_date)>=0 and datediff(x.onair_time,y.data_date)<7) m
left join
(select
data_date,
site_no site_no,
cell_name cell_name,
cell_priority,
site_priority,
band,
txrx,
sum(rrc_user) rrc_user,
sum(payload) payload,
sum(lte_dl_user_throughput_kbps) lte_dl_user_throughput_kbps
from mysql.test group by data_date,site_no,cell_name,cell_priority,site_priority,band,txrx) z
on z.site_no=m.site_no and z.cell_name=m.cell_name and z.data_date=date_add(m.data_date,interval -7 day)
left join
(select
data_date,
site_no site_no,
cell_name cell_name,
cell_priority,
site_priority,
band,
txrx,
sum(rrc_user) rrc_user,
sum(payload) payload,
sum(lte_dl_user_throughput_kbps) lte_dl_user_throughput_kbps
from mysql.test group by data_date,site_no,cell_name,cell_priority,site_priority,band,txrx) w
on w.site_no=m.site_no and w.cell_name=m.cell_name and w.data_date=date_add(m.data_date,interval 14 day)
--若中间表中已存在基础表中最新的cell_name和onair_time，则不操作(保留原始数据，以防宽表老化后查询不到)，否则以site_no与cell_name为主键，替换成最新的数据
where not exists (select n.onair_time,n.cell_name from mysql.test_report n
where concat(m.onair_time,m.cell_name)=concat(n.onair_time,n.cell_name));
END
- 调度存储过程，实现数据更新
call mysql.test_procedure_update(@a,@b,@c,'','');
扫一扫
638
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
