MySQL · 调优案例 · MES

发表于 | 更新于 | 分类于 | 阅读次数：

数据归档

需求

工厂号 工厂名 月结结束时间 数据库地址 端口
1026 合肥波轮 2019/10/31 11:00 192.168.175.11 3306
1022 合肥空调 2019/10/31 12:00 10.142.201.254 3306

策略

订单日期是 3 个月以前，且订单状态是已完成

表 说明 关联字段 总数据量 归档数据量
base_production_order_t 订单表 Code 17627 5174
bns_pm_operation 条码表 WorkUser_MOrderCode 2656496 1530802
bns_pm_scanhistory_month 扫描记录表 Code 7392827 4370623
bns_qm_processtest 过程检验表 Code 7562577 4508488
bns_pm_prodprocess 工位过站记录表 Order_Code …. ….
bns_pm_barcodecode 物料绑定表 …. 6614117 3967682 
1
2
select count(*) from base_production_order_t where EST < now()-interval 3 month and OrderStatus = '2';
select count(*) from bns_pm_barcodecode where scan_time < now()-interval 3 month;

实践

准备

1
2
3
4
5
6
# 归档表的表结构与原表一致，命名统一采用原表名_archive的格式
bns_pm_operation_archive
bns_pm_scanhistory_month_archive
bns_qm_processtest_archive
bns_pm_prodprocess_archive
bns_pm_barcodecode_archive
  • 开始归档
1
2
# 归档命令
nohup pt-archiver --source h=xx,P=3306,u=sre,p='xx',D='xx',t=bns_pm_barcodecode --charset=utf8 --dest  h=xx,P=3306,u=sre,p='xx',D='xx',t=bns_pm_barcodecode_archive --where "scan_time < now()-interval 3 month" --limit 1000 --commit-each --bulk-insert --no-version-check > myout.file 2>&1 &
  • 实时查看进度
1
2
use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'xx' and table_name='xx';

bns_pm_barcodecode

1
2
3
4
5
6
# 开始归档
nohup pt-archiver --source h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_pm_barcodecode --charset=utf8 --dest  h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_pm_barcodecode_archive --where "scan_time < now()-interval 3 month" --limit 1000 --commit-each --bulk-insert --no-version-check > myout.file 2>&1 &

# 查看归档进度
use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'cosmo_im_1026' and table_name='bns_pm_barcodecode_archive';

bns_pm_operation

1
2
3
4
5
6
# 开始归档
nohup pt-archiver --source h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_pm_operation --charset=utf8 --dest  h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_pm_operation_archive --where "WorkUser_MOrderCode IN (select Code from base_production_order_t where EST < now()-interval 3 month and OrderStatus = '2')" --limit 1000 --commit-each --bulk-insert --no-version-check > myout1.file 2>&1 &

# 查看归档进度
use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'cosmo_im_1026' and table_name='bns_pm_operation_archive';

bns_pm_scanhistory_month

1
2
3
4
5
6
# 开始归档
nohup pt-archiver --source h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_pm_scanhistory_month --charset=utf8 --dest  h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_pm_scanhistory_month_archive --where "Code IN (select Code from base_production_order_t where EST < now()-interval 3 month and OrderStatus = '2')" --limit 1000 --commit-each --bulk-insert --no-version-check > myout2.file 2>&1 &

# 查看归档进度
use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'cosmo_im_1026' and table_name='bns_pm_scanhistory_month_archive';

bns_qm_processtest

1
2
3
4
5
6
# 开始归档
nohup pt-archiver --source h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_qm_processtest --charset=utf8 --dest  h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_qm_processtest_archive --where "Code IN (select Code from base_production_order_t where EST < now()-interval 3 month and OrderStatus = '2')" --limit 1000 --commit-each --bulk-insert --no-version-check > myout3.file 2>&1 &

# 查看归档进度
use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'cosmo_im_1026' and table_name='bns_qm_processtest_archive';

bns_pm_prodprocess

1
2
3
4
5
6
# 开始归档
nohup pt-archiver --source h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_pm_prodprocess --charset=utf8 --dest  h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_pm_prodprocess_archive --where "Order_Code IN (select Code from base_production_order_t where EST < now()-interval 3 month and OrderStatus = '2')" --limit 1000 --commit-each --bulk-insert --no-version-check > myout4.file 2>&1 &

# 查看归档进度
use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'cosmo_im_1026' and table_name='bns_pm_prodprocess_archive';

设置自动归档

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 存储过程
DELIMITER $$ 

DROP PROCEDURE IF EXISTS sp_archive$$ 
CREATE PROCEDURE sp_archive()
BEGIN 	
	INSERT INTO bns_pm_barcodecode_archive SELECT * FROM bns_pm_barcodecode WHERE 
	scan_time < now() - interval 3 month;
	DELETE FROM bns_pm_barcodecode WHERE scan_time < now() - interval 3 month;
END $$

DELIMITER ;

# 事件
CREATE EVENT Event_Archive_Daily
ON SCHEDULE EVERY 1 DAY STARTS '2019-11-01 02:00:00'
ON COMPLETION PRESERVE
ENABLE
DO call sp_archive();

慢 SQL 优化

工厂：黄岛滚筒

数据库：10.180.129.24:3306

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# 慢SQL1
UPDATE bns_pm_operation a
SET a.T_WMS_1 = '1'
WHERE a.Create_Date < '2019-04-01'
AND a.T_WMS_1 = '0'

# 慢SQL2
SELECT ZYH
FROM t_mes_scan
WHERE PPLACE = '一线总装箱体上线'
ORDER BY REALDATE DESC
LIMIT 0, 1

# 视图
SELECT
	t.ID AS N_ID,
	ifnull( t.WorkUser_RandomBarCode, t.WorkUser_BarCode ) AS SERN,
	t.WorkUser_MOrderCode AS ORDER_ID,
	t.Work_OperationOutMainItemCode AS ZYH,
	t.WorkUser_RightMostItemName AS XH_ID,
	t.WorkUser_LineName AS LINENAME,
	t3.Work_Cell_Desc AS PPLACE,
	t2.ScanTime AS REALDATE,
	t2.User_Code AS YG_ID,
	t2.Create_Date AS CREATEDATE,
	t4.Team_Code AS Team_Code,
	t4.Team_Desc AS Team_Desc,
	t5.Shift_Code AS Shift_Code,
	t5.Shift_Desc AS Shift_Desc 
FROM bns_pm_operation t 
LEFT JOIN bns_pm_scanhistory_month t2 ON t.WorkUser_BarCode = t2.WorkUser_BarCode
LEFT JOIN pm_work_cells_t t3 ON t2.Work_Cell_Code = t3.Work_Cell_Code 
LEFT JOIN base_team_t t4 ON t2.Team_Code = t4.Team_Code
LEFT JOIN base_shifts_t t5 ON t2.Shift_Code = t5.Shift_Code 
WHERE t2.Type = 0
ORDER BY t2.ScanTime