MySQL-为复杂的计算提高UPDATE查询性能 [英] MySQL - Improve UPDATE Query Performance For Complex Calculations
本文介绍了MySQL-为复杂的计算提高UPDATE查询性能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试最小化以下UPDATE语句的计算/查询时间，该语句嵌入在服务器上的存储过程中...
I am trying to minimise the calculation / query time for the following UPDATE statement that is embedded in a stored procedure on my server...
SET @EQUIPMENT_VARIABLE = 'B1010-V001';
UPDATE EQUIPMENT e
/*24. MAX COF*/
SET e.MAX_COF = least(COF_SAFETY, COF_HEALTH, COF_ENVIRONMENT, COF_ASSETS)
/*25. RECOMMENDED RBI INTERNAL INTERVAL*/
, e.RECOMMENDED_RBI_INTERNAL_INTERVAL = (SELECT RECOMMENDED_RBI_INTERNAL_INSPECTION_INTERVAL FROM DAMAGE_MECHANISM WHERE EQUIPMENT_ID = e.EQUIPMENT_ID ORDER BY RECOMMENDED_RBI_INTERNAL_INSPECTION_INTERVAL ASC LIMIT 1)
/*26. LAST INSPECTION DATE INTERNAL*/
, e.LAST_INSPECTION_DATE_INTERNAL = (
SELECT a.LAST_INSPECTION_DATE
FROM CML a LEFT JOIN CML_TYPES b ON a.CML_TYPE = b.CML_TYPE
WHERE a.EQUIPMENT_ID = e.EQUIPMENT_ID AND a.STATUS NOT IN('X') AND b.INTERNAL_EXTERNAL = 'INTERNAL'
ORDER BY LAST_INSPECTION_DATE DESC LIMIT 1
)
/*27. NEXT INSPECTION DATE INTERNAL*/
, e.NEXT_INSPECTION_DATE_INTERNAL = DATE_ADD(e.LAST_INSPECTION_DATE_INTERNAL, INTERVAL FLOOR(RECOMMENDED_RBI_INTERNAL_INTERVAL * 365) DAY)
/*28. RECOMMENDED RBI EXTERNAL INTERVAL*/
, e.RECOMMENDED_RBI_EXTERNAL_INTERVAL = (SELECT RECOMMENDED_RBI_EXTERNAL_INSPECTION_INTERVAL FROM DAMAGE_MECHANISM WHERE EQUIPMENT_ID = e.EQUIPMENT_ID ORDER BY RECOMMENDED_RBI_EXTERNAL_INSPECTION_INTERVAL ASC LIMIT 1)
/*29. LAST INSPECTION DATE EXERNAL*/
, e.LAST_INSPECTION_DATE_EXTERNAL = (
SELECT a.LAST_INSPECTION_DATE
FROM CML a LEFT JOIN CML_TYPES b ON a.CML_TYPE = b.CML_TYPE
WHERE a.EQUIPMENT_ID = e.EQUIPMENT_ID AND a.STATUS NOT IN('X') AND b.INTERNAL_EXTERNAL = 'EXTERNAL'
ORDER BY LAST_INSPECTION_DATE DESC LIMIT 1
)
/*30. NEXT INSPECTION DATE EXTERNAL*/
, e.NEXT_INSPECTION_DATE_EXTERNAL = DATE_ADD(e.LAST_INSPECTION_DATE_EXTERNAL, INTERVAL FLOOR(RECOMMENDED_RBI_EXTERNAL_INTERVAL * 365) DAY)
/*31. END OF LIFE*/
, e.END_OF_LIFE = (
SELECT END_OF_LIFE
FROM CML
WHERE EQUIPMENT_ID = e.EQUIPMENT_ID AND STATUS NOT IN('X')
ORDER BY END_OF_LIFE ASC LIMIT 1
)
/*XX. REMEDIATION STATUS*/
, e.REMEDIATION_STATUS = (SELECT GROUP_CONCAT(DISTINCT(REMEDIATION_STATUS)) FROM CML WHERE EQUIPMENT_ID = e.EQUIPMENT_ID AND STATUS NOT IN('X'))
, e.REMEDIATION_STATUS =
CASE WHEN e.REMEDIATION_STATUS IS NOT NULL
THEN e.REMEDIATION_STATUS
ELSE 'F'
END
/*XX. MAX ACR*/
, e.MAX_ACR = (SELECT APPLIED_CORROSION_RATE FROM CML WHERE EQUIPMENT_ID = e.EQUIPMENT_ID AND STATUS NOT IN ('X') ORDER BY APPLIED_CORROSION_RATE DESC LIMIT 1)
/*XX. MIN REMAINING LIFE*/
, e.MIN_REMAINING_LIFE = (SELECT REMAINING_LIFE_BASED_ON_ACR FROM CML WHERE EQUIPMENT_ID = e.EQUIPMENT_ID AND STATUS NOT IN ('X') ORDER BY REMAINING_LIFE_BASED_ON_ACR ASC LIMIT 1)
WHERE e.EQUIPMENT_ID like @EQUIPMENT_VARIABLE;
关于如何改善查询结构以最小化查询时间/最大化UPDATE查询性能的任何想法?
Any ideas on how I can improve the query structure to minimise the query time / maximise the UPDATE query performance?
**更新**
每个主表中的大约记录数如下:
Approximate number of records in each major table is as follows:
- CML_TYPES:10
- REMEDIATION_STATUSES:10
- 检查:218,000
- CML:2180
- DAMAGE_MECHANISM:2180
- 设备:218
下面提供了表数据库结构.
Table database structure is provided below.
/*CREATE TABLES AND SET DATATYPES*/
/*LOOKUP TABLES TO BE ADDED FIRST SO FOREIGN KEY CONSTRAINTS CAN BE ASSIGNED CORRECTLY*/
CREATE TABLE IDMAS_VESSELS.CML_TYPES
( /*FIELD NAME*/ /*DATA TYPE AND RESTRICTIONS*/
`CML_TYPE` VARCHAR(50),
`INTERNAL_EXTERNAL` enum('INTERNAL', 'EXTERNAL') default NULL,
/*PRIMARY AND FOREIGN KEY DEFINITIONS*/
PRIMARY KEY (CML_TYPE),
/*ADD TABLE INDEXES TO IMPROVE PERFORMANCE*/
INDEX CML_TYPES_INDEX (CML_TYPE)
) ENGINE=InnoDB;
CREATE TABLE IDMAS_VESSELS.REMEDIATION_STATUSES
( /*FIELD NAME*/ /*DATA TYPE AND RESTRICTIONS*/
`REMEDIATION_STATUS` VARCHAR(1),
`DESCRIPTION` VARCHAR(100),
/*PRIMARY AND FOREIGN KEY DEFINITIONS*/
PRIMARY KEY (REMEDIATION_STATUS)
/*ADD TABLE INDEXES TO IMPROVE PERFORMANCE*/
-- PRIMARY KEY INDEXED BY DEFAULT. NO OTHER INDEXES REQUIRED.
) ENGINE=InnoDB;
CREATE TABLE IDMAS_VESSELS.EQUIPMENT
( /*FIELD NAME*/ /*DATA TYPE AND RESTRICTIONS*/
`EQUIPMENT_ID` VARCHAR(20) NOT NULL,
`EQUIP_TYPE` VARCHAR(50),
`STATUS_OF_VESSEL` enum('ACTIVE', 'INACTIVE') NOT NULL default 'ACTIVE',
`REMEDIATION_STATUS` VARCHAR(10),
`MIN_REMAINING_LIFE` DOUBLE,
`MAX_ACR` DOUBLE,
`END_OF_LIFE` DATE,
`AREA` VARCHAR(50),
`EQUIP_FROM` VARCHAR(50),
`EQUIP_TO` VARCHAR(20),
`CONTENTS` VARCHAR(50),
`COMMENTS` TEXT,
`MATERIAL` VARCHAR(50),
`MATERIAL_SPECIFICATION` VARCHAR(2),
`CORROSION_ALLOWANCE (MM)` DOUBLE(2,2),
`CONSTRUCTION_CODE` VARCHAR(20),
`DESIGN_PRESSURE_MPA` DOUBLE,
`DESIGN_TEMPERATURE_DEGC` DOUBLE,
`PID` VARCHAR(20),
`DRAWING_REF` VARCHAR(20),
`OPERATING_PRESSURE_KPA` DOUBLE,
`OPERATING_TEMP_DEGC` DOUBLE,
`CAPACITY_L` DOUBLE,
`CAPACITY_MPA_PER_L` DOUBLE,
`MAXIMUM_ALLOWABLE_WORKING_PRESSURE_MPA` DOUBLE,
`MAXIMUM_ALLOWABLE_WORKING_TEMPERATURE_DEGC` DOUBLE,
`DESIGN_STRENGTH_MPA` DOUBLE,
`JOINT_EFFICIENCY` DOUBLE,
`COF_SAFETY` INT(1),
`COF_HEALTH` INT(1),
`COF_ENVIRONMENT` INT(1),
`COF_ASSETS` INT(1),
`MAX_COF` INT(1),
`HAZARD_LEVEL` CHAR(1),
`CONTENTS_CLASS` VARCHAR(50),
`INSTALLATION_YEAR` DATE,
`INTERIOR_COATING` VARCHAR(50),
`EXTERIOR_COATING` VARCHAR(50),
`EXTERIOR_INSUL` VARCHAR(50),
`MANUFACTURER` VARCHAR(50),
`MANUFACTURE_DATE` DATE,
`MANUFACTURE_NO` VARCHAR(50),
`INTERNAL_ANODES` VARCHAR(50),
`ASSOC_PSV` VARCHAR(50),
`MAINT_ITEM_CODE` VARCHAR(50),
`SERV_TYPE` VARCHAR(50),
`EQUIP_CLASS` VARCHAR(50),
`EQUIP_SUBCLASS` VARCHAR(50),
`PROCESS_UNIT` VARCHAR(50),
`RECOMMENDED_RBI_INTERNAL_INTERVAL` DOUBLE,
`LAST_INSPECTION_DATE_INTERNAL` DATE,
`NEXT_INSPECTION_DATE_INTERNAL` DATE,
`RECOMMENDED_RBI_EXTERNAL_INTERVAL` DOUBLE,
`LAST_INSPECTION_DATE_EXTERNAL` DATE,
`NEXT_INSPECTION_DATE_EXTERNAL` DATE,
`DESIGN_LIFE` DOUBLE,
/*PRIMARY AND FOREIGN KEY DEFINITIONS*/
PRIMARY KEY (EQUIPMENT_ID),
/*INDEX FIELDS TO INCREASE PERFORMANCE REDUCES NUMBER OF ACCESSES (N) FROM N to LOG2(N) I.E. AN EXPONENTIAL DECREASE
*APPLYING THESE INDEXES TO PRIMARY KEY FIELDS ALSO REMOVES THE REQUIREMENT FOR THE SYSTEM TO SEARCH FOR DUPLICATES
*INDEXES ONLY INCREASE THE DISK SPACE REQUIRED OF THE DATABASE.
*ALL DATABASE SEARCHING SHOULD ALWAYS BE DONE ON THE PRIMARY KEY INDEXED FIELD.
*/
INDEX EQUIPMENT_INDEX (EQUIPMENT_ID)
) ENGINE=InnoDB;
CREATE TABLE IDMAS_VESSELS.DAMAGE_MECHANISM
( /*FIELD NAME*/ /*DATA TYPE AND RESTRICTIONS*/
`DAMAGE_MECHANISM_ID` VARCHAR(50) NOT NULL,
`EQUIPMENT_ID` VARCHAR(20),
`DESCRIPTION` VARCHAR(50),
`REMEDIATION_STATUS` VARCHAR(10),
`DAMAGE_MECHANISM_TYPE` enum('QUALITATIVE', 'QUANTITATIVE') default NULL,
`INSPECTION_STRATEGY` VARCHAR(20),
`AGE_RELATED` enum('YES', 'NO') NOT NULL default 'NO',
`FEASIBLE` enum('YES', 'NO') NOT NULL default 'NO',
`NOTES_ON_FEASIBILITY` TEXT,
`MIN_REMAINING_LIFE` DOUBLE,
`MAX_ACR` DOUBLE,
`MINIMUM_CALCULATED_RBI_INTERVAL` DOUBLE,
`MAXIMUM_CRITICALITY_RATING` VARCHAR(20),
`RECOMMENDED_RBI_INTERNAL_INSPECTION_INTERVAL` DOUBLE,
`LAST_INSPECTION_DATE_INTERNAL` DATE,
`NEXT_INSPECTION_DATE_INTERNAL` DATE,
`NOTES_ON_RECOMMENDED_RBI_INTERNAL_INSPECTION_INTERVAL` TEXT,
`RECOMMENDED_RBI_EXTERNAL_INSPECTION_INTERVAL` DOUBLE,
`LAST_INSPECTION_DATE_EXTERNAL` DATE,
`NEXT_INSPECTION_DATE_EXTERNAL` DATE,
`NOTES_ON_RECOMMENDED_RBI_EXTERNAL_INSPECTION_INTERVAL` TEXT,
/*PRIMARY AND FOREIGN KEY DEFINITIONS*/
PRIMARY KEY (DAMAGE_MECHANISM_ID, EQUIPMENT_ID),
FOREIGN KEY (EQUIPMENT_ID) REFERENCES EQUIPMENT(EQUIPMENT_ID),
/*ADD TABLE INDEXES TO IMPROVE PERFORMANCE*/
INDEX DAMAGE_MECHANISM_INDEX (EQUIPMENT_ID, DAMAGE_MECHANISM_ID)
) ENGINE=InnoDB;
CREATE TABLE IDMAS_VESSELS.CML
( /*FIELD NAME*/ /*DATA TYPE AND RESTRICTIONS*/
`CML_ID` INT NOT NULL,
`CML_LOCATION` VARCHAR(20),
`STATUS` enum('C','X','A','U') NOT NULL default 'C',
`REMEDIATION_STATUS` VARCHAR(1),
`EQUIPMENT_ID` VARCHAR(20),
`DAMAGE_MECHANISM_ID` VARCHAR(50),
`CML_TYPE` VARCHAR(50),
`LAST_INSPECTION_DATE` DATE,
`NOMINAL_DIAMETER_MM` DOUBLE,
`OUTSIDE_DIAMETER_MM` DOUBLE,
`MEAN_DIAMETER_MM` DOUBLE,
`NOMINAL_WALL_THICKNESS_MM` DOUBLE,
`MINIMUM_REMAINING_WALL_THICKNESS_MM` DOUBLE,
`DEPTH_OF_LOSS_FROM_NWT_MM` DOUBLE,
`THICKNESS_WALL_LOSS_PERCENTAGE` DOUBLE,
`PHOTO` INT,
`MINIMUM_ALLOWABLE_WALL_THICKNESS_MM` DOUBLE,
`ACTUAL_CORROSION_RATE_MM_PER_YEAR` DOUBLE,
`MAX_CALCULATED_CORROSION_RATE` DOUBLE,
`DAMAGE_LOOP_MAXIMUM_CORROSION_RATE` DOUBLE,
`APPLIED_CORROSION_RATE` DOUBLE,
`APPLIED_CORROSION_RATE_NOTES_AND_ENTERED_BY` TEXT,
`FAILURE_MODE` VARCHAR(50),
`REMAINING_LIFE_BASED_ON_ACR` DOUBLE,
`END_OF_LIFE` DATE,
`LR_LD_APPLIED` DOUBLE,
`POF_APPLIED` INT(1),
`DAMAGE_MECHANISM_VISUAL` TEXT,
`FAILURE_MODE_VISUAL` TEXT,
`POF_APPLIED_VISUAL` INT(1),
`POF_APPLIED_VISUAL_COMMENT` TEXT,
`CRITICALITY_RATING_VISUAL` INT(2),
`CRITICALITY_RATING_APPLIED` VARCHAR(10),
`DEGRADATION_MECHANISM` DOUBLE,
`RELIABLE_MONITORING` DOUBLE,
`MULTIPLE_INSPECTIONS` DOUBLE,
`CONFIDENCE_SCORE` DOUBLE,
`CONFIDENCE_RATING` VARCHAR(50),
`INSPECTION_INTERVAL_FACTOR_APPLIED` DOUBLE,
`RISK_BASED_INSPECTION_INTERVAL_APPLIED_YEARS` DOUBLE,
`RECOMMENDED_REMEDIATION` TEXT,
/*PRIMARY AND FOREIGN KEY DEFINITIONS*/
PRIMARY KEY (EQUIPMENT_ID, CML_ID),
FOREIGN KEY (EQUIPMENT_ID, DAMAGE_MECHANISM_ID) REFERENCES DAMAGE_MECHANISM(EQUIPMENT_ID, DAMAGE_MECHANISM_ID),
FOREIGN KEY (CML_TYPE) REFERENCES CML_TYPES(CML_TYPE),
FOREIGN KEY (REMEDIATION_STATUS) REFERENCES REMEDIATION_STATUSES(REMEDIATION_STATUS),
/*ADD TABLE INDEXES TO IMPROVE PERFORMANCE*/
INDEX CML_INDEX (EQUIPMENT_ID, CML_ID)
) ENGINE=InnoDB;
CREATE TABLE IDMAS_VESSELS.INSPECTION
( /*FIELD NAME*/ /*DATA TYPE AND RESTRICTIONS*/
`INSPECTION_NO` INT NOT NULL,
`STATUS` enum('P','A','S','R') NOT NULL default 'P',
`CML_ID` INT NOT NULL,
`EQUIPMENT_ID` VARCHAR(20),
`INSPECTION_TYPE` VARCHAR(50),
`SCOPE_COMMENTS` TEXT,
`NOMINAL_WALL_THICKNESS` DOUBLE,
`MINIMUM_REMAINING_WALL_THICKNESS` DOUBLE,
`WALL_LOSS` DOUBLE,
`SERVICE_YEARS` DOUBLE,
`CORROSION_RATE_ACTUAL_SHORT_TERM_MM_PER_YEAR` DOUBLE,
`CONDITION` VARCHAR(50),
`INSPECTION_DATE` DATE,
`NDT_REPORT_NUMBER` VARCHAR(20),
`INSPECTION_COMMENTS` TEXT,
`INSPECTION_SCOPE` TEXT,
`WORK_ORDER` TEXT,
`INPECTOR_NAME` VARCHAR(50),
`INPECTION_COMPANY` VARCHAR(50),
`PHOTO` INT NOT NULL AUTO_INCREMENT,
/*PRIMARY AND FOREIGN KEY DEFINITIONS*/
PRIMARY KEY (EQUIPMENT_ID, CML_ID, INSPECTION_NO),
FOREIGN KEY (EQUIPMENT_ID, CML_ID) REFERENCES CML(EQUIPMENT_ID, CML_ID),
/*ADD TABLE INDEXES TO IMPROVE PERFORMANCE*/
/*INDEX ON PHOTO FIELD REQUIRED TO ALLOW AUTO INCREMENTING OF FIELD*/
INDEX INSPECTION_INDEX (EQUIPMENT_ID, CML_ID, INSPECTION_NO),
INDEX `PHOTO` (`PHOTO`)
-- INDEX INSPECTION_DATE (INSPECTION_DATE)
) ENGINE=InnoDB;
推荐答案
建议更新所有设备的解决方案
UPDATE EQUIPMENT e
left join (SELECT EQUIPMENT_ID
,min(RECOMMENDED_RBI_INTERNAL_INSPECTION_INTERVAL) as MIN_RECOMMENDED_RBI_INTERNAL_INSPECTION_INTERVAL /*25. RECOMMENDED RBI INTERNAL INTERVAL*/
,min(RECOMMENDED_RBI_EXTERNAL_INSPECTION_INTERVAL) as MIN_RECOMMENDED_RBI_EXTERNAL_INSPECTION_INTERVAL /*28. RECOMMENDED RBI EXTERNAL INTERVAL*/
FROM DAMAGE_MECHANISM
group by EQUIPMENT_ID
) dm
on dm.EQUIPMENT_ID = e.EQUIPMENT_ID
left join (SELECT EQUIPMENT_ID
,max(case b.INTERNAL_EXTERNAL when 'INTERNAL' then a.LAST_INSPECTION_DATE end) MIN_LAST_INSPECTION_DATE_INTERNAL /*26. LAST INSPECTION DATE INTERNAL*/
,max(case b.INTERNAL_EXTERNAL when 'EXTERNAL' then a.LAST_INSPECTION_DATE end) MIN_LAST_INSPECTION_DATE_EXTERNAL /*29. LAST INSPECTION DATE EXERNAL*/
,min(END_OF_LIFE) as min_END_OF_LIFE /*31. END OF LIFE*/
,GROUP_CONCAT(DISTINCT REMEDIATION_STATUS) as GROUP_CONCAT_DISTINCT_REMEDIATION_STATUS /*XX. REMEDIATION STATUS*/
,max(APPLIED_CORROSION_RATE) as max_APPLIED_CORROSION_RATE
,min(REMAINING_LIFE_BASED_ON_ACR) as min_REMAINING_LIFE_BASED_ON_ACR
FROM CML a JOIN CML_TYPES b ON a.CML_TYPE = b.CML_TYPE
WHERE a.STATUS NOT IN('X')
group by EQUIPMENT_ID
) ct
on ct.EQUIPMENT_ID = e.EQUIPMENT_ID
SET e.MAX_COF = least(COF_SAFETY, COF_HEALTH, COF_ENVIRONMENT, COF_ASSETS) /*24. MAX COF*/
,e.RECOMMENDED_RBI_INTERNAL_INTERVAL = dm.MIN_RECOMMENDED_RBI_INTERNAL_INSPECTION_INTERVAL /*25. RECOMMENDED RBI INTERNAL INTERVAL*/
,e.LAST_INSPECTION_DATE_INTERNAL = ct.MIN_LAST_INSPECTION_DATE_INTERNAL /*26. LAST INSPECTION DATE INTERNAL*/
,e.NEXT_INSPECTION_DATE_INTERNAL = DATE_ADD(e.LAST_INSPECTION_DATE_INTERNAL,INTERVAL FLOOR(RECOMMENDED_RBI_INTERNAL_INTERVAL*365) DAY) /*27. NEXT INSPECTION DATE INTERNAL*/
,e.RECOMMENDED_RBI_EXTERNAL_INTERVAL = dm.MIN_RECOMMENDED_RBI_EXTERNAL_INSPECTION_INTERVAL /*28. RECOMMENDED RBI EXTERNAL INTERVAL*/
,e.LAST_INSPECTION_DATE_EXTERNAL = ct.MIN_LAST_INSPECTION_DATE_EXTERNAL /*29. LAST INSPECTION DATE EXERNAL*/
,e.NEXT_INSPECTION_DATE_EXTERNAL = DATE_ADD(e.LAST_INSPECTION_DATE_EXTERNAL,INTERVAL FLOOR(RECOMMENDED_RBI_EXTERNAL_INTERVAL*365) DAY) /*30. NEXT INSPECTION DATE EXTERNAL*/
,e.END_OF_LIFE = ct.min_END_OF_LIFE /*31. END OF LIFE*/
,e.REMEDIATION_STATUS = coalesce(ct.GROUP_CONCAT_DISTINCT_REMEDIATION_STATUS,'F') /*XX. REMEDIATION STATUS*/
,e.MAX_ACR = ct.max_APPLIED_CORROSION_RATE /*XX. MAX ACR*/
,e.MIN_REMAINING_LIFE = ct.min_REMAINING_LIFE_BASED_ON_ACR /*XX. MIN REMAINING LIFE*/
;
这篇关于MySQL-为复杂的计算提高UPDATE查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
