| Bug #95802 | It is very efficient to execute SQL statements alone, but it is very inefficient | ||
|---|---|---|---|
| Submitted: | 14 Jun 2019 2:12 | Modified: | 24 Jun 2019 12:45 |
| Reporter: | gao haidong | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S5 (Performance) |
| Version: | 8.0.16 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[14 Jun 2019 2:12]
gao haidong
[14 Jun 2019 12:33]
MySQL Verification Team
Hello Mr. Haldong, Thank you for your bug report. In order to process your report, we have to be able to repeat your problem and see it ourselves. You have not provided us with all information necessary for us to perform that job. Hence, we need the entire table, exact declaration of the stored routines and the exact manner of their invocation ....
[19 Jun 2019 7:46]
gao haidong
This kind of direct execution of SQL query statement is very fast when the amount of single table data is very large, but it is very slow to put the same SQL query statement into the stored procedure, and the performance of the stored procedure is very poor, mainly in version 8.0.
[19 Jun 2019 7:49]
gao haidong
Although the second method is faster than the first one, it is not as fast as the 5.7 version in the case of large amount of data.
[19 Jun 2019 12:33]
MySQL Verification Team
Hi Mr. Haldong, First of all, you did not provide us with the info that we requested before. Second, in your first example, that runs slowly on 8.0, you are using user's variable, instead of what is recommended by standard, which is local variable. Before answering us, please try that variant first and let us know if there are any changes.
[20 Jun 2019 1:39]
gao haidong
1、This writing is very poor in MySQL 8.0, but very good in MySQL 5.7.
CREATE PROCEDURE vocs.PRO_UpLdar_Report_GetReportDataBySealPoint(
InspectionCycleId VARCHAR(50),
LdarDeviceId VARCHAR(50),
CompanyId VARCHAR(50)
)
SQL SECURITY INVOKER
BEGIN
DECLARE $StrWhere varchar(8000);
SET $StrWhere = '';
SET @CompanyId=CompanyId;
SET @cycId=InspectionCycleId;
IF (LdarDeviceId IS NOT NULL AND LdarDeviceId !='') THEN
SET $StrWhere = CONCAT($StrWhere,' AND L.LdarDeviceId = \'',LdarDeviceId,'\' ');
END IF;
SET @TempSql = CONCAT('
SELECT
L.DeviceName,
count( 1 ) AS jd_number,
sum( IF ( L.IsStatic = 1, 1, 0 ) ) AS jdj_number,
count( 1 ) - sum( IF ( L.IsStatic = 1, 1, 0 ) ) AS jdd_number,
sum( IF ( L.IsReached = 0, 1, 0 ) ) AS bk_number,
sum( IF ( L.IsReached = 0 AND L.IsStatic = 1, 1, 0 ) ) AS bkj_number,
sum( IF ( L.IsReached = 0, 1, 0 ) ) - sum( IF ( L.IsReached = 0 AND L.IsStatic = 1, 1, 0 ) ) AS bkd_number,
sum( IF ( JC.SealPointCode IS NOT NULL, 1, 0 ) ) AS jc_number,
sum( IF ( JC.SealPointCode IS NOT NULL AND L.IsStatic = 1, 1, 0 ) ) AS jcj_number,
sum( IF ( JC.SealPointCode IS NOT NULL, 1, 0 ) ) - sum( IF ( JC.SealPointCode IS NOT NULL AND L.IsStatic = 1, 1, 0 ) ) AS jcd_number
FROM
(
SELECT
InspectionCycleId,
SealPointCode,
DeviceName,
IsReached,
CompanyId,
IF
( SealingPointTypeName = \'法兰\' OR SealingPointTypeName = \'连接件\' OR SealingPointTypeName = \'其他\', 1, 0 ) AS IsStatic
FROM
ldarsealpointlog
WHERE
InspectionCycleId = @cycId
AND DelState = 0
) L
LEFT JOIN (
SELECT
SealPointCode
FROM ldarworkplandetailed
WHERE InspectionCycleId = @cycId
AND CompanyId = @CompanyId
AND DelState = 0 AND StateId = 1 ) JC
ON JC.SealPointCode = L.SealPointCode
',$StrWhere,'
GROUP BY
L.DeviceName;');
##执行SQL语句
PREPARE stmt from @TempSql;
EXECUTE stmt;
END
[20 Jun 2019 1:40]
gao haidong
2.This writing is better in MySQL 8.0 than in the first, but not as good as in MySQL 5.7.
CREATE PROCEDURE vocs_dy.PRO_UpLdar_Report_GetReportDataBySealPoint(
InspectionCycleId VARCHAR(50),
LdarDeviceId VARCHAR(50),
CompanyId VARCHAR(50)
)
SQL SECURITY INVOKER
BEGIN
DECLARE $StrWhere varchar(8000);
SET $StrWhere = '';
IF (LdarDeviceId IS NOT NULL AND LdarDeviceId !='') THEN
SET $StrWhere = CONCAT($StrWhere,' AND L.LdarDeviceId = \'',LdarDeviceId,'\' ');
END IF;
SET @TempSql = CONCAT('
SELECT
L.DeviceName,
count( 1 ) AS jd_number,
sum( IF ( L.IsStatic = 1, 1, 0 ) ) AS jdj_number,
count( 1 ) - sum( IF ( L.IsStatic = 1, 1, 0 ) ) AS jdd_number,
sum( IF ( L.IsReached = 0, 1, 0 ) ) AS bk_number,
sum( IF ( L.IsReached = 0 AND L.IsStatic = 1, 1, 0 ) ) AS bkj_number,
sum( IF ( L.IsReached = 0, 1, 0 ) ) - sum( IF ( L.IsReached = 0 AND L.IsStatic = 1, 1, 0 ) ) AS bkd_number,
sum( IF ( JC.SealPointCode IS NOT NULL, 1, 0 ) ) AS jc_number,
sum( IF ( JC.SealPointCode IS NOT NULL AND L.IsStatic = 1, 1, 0 ) ) AS jcj_number,
sum( IF ( JC.SealPointCode IS NOT NULL, 1, 0 ) ) - sum( IF ( JC.SealPointCode IS NOT NULL AND L.IsStatic = 1, 1, 0 ) ) AS jcd_number
FROM
(
SELECT
InspectionCycleId,
SealPointCode,
DeviceName,
IsReached,
CompanyId,
case when ( SealingPointTypeName = ''法兰'' OR SealingPointTypeName = ''连接件'' OR SealingPointTypeName = ''其他'') then 1 else 0 end AS IsStatic
FROM ldarsealpointlog
WHERE CompanyId = ''',CompanyId,'''
AND InspectionCycleId = ''',InspectionCycleId,'''
AND DelState = 0
) L
LEFT JOIN (
SELECT SealPointCode
FROM ldarworkplandetailed
WHERE CompanyId =''',CompanyId,'''
AND InspectionCycleId = ''',InspectionCycleId,'''
AND DelState = 0 AND StateId = 1
) JC
ON JC.SealPointCode = L.SealPointCode
',$StrWhere,'
GROUP BY L.DeviceName;');
##执行SQL语句
PREPARE stmt from @TempSql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- option(reCompile);
END
[20 Jun 2019 1:42]
gao haidong
But it is very fast to execute SQL statements of this stored procedure with SQL scripts.
[20 Jun 2019 8:17]
gao haidong
To find out the reason, if the code is utf8, the performance of local variables is as fast as that of user variables in the stored procedure, but if utf8mb4 is used, user variables are very slow and local variables are very fast.
[20 Jun 2019 13:13]
MySQL Verification Team
Hi, First of all, it is expected behaviour that utf8mb3 is faster then utf8mb4. It is because the second one takes one byte more then the first one, for each character in the string. Second, it is also the expected behaviour that SQL statements are MUCH more efficient when executed from the script then in the stored routines. That is because stored routines in SQL servers are interpreted programming language, while you execute scripts through mysql CLI, which is made by the highly optimised C code. Third, single-thread performance in 8.0 is worse then with 5.7. That is because 8.0 has many new features and because 8.0 is highly optimised for the high concurrency loads. There are many benchmarks and verified performance bugs already that exist in this bugs database. In light of the above, we do not understand what it is that you want from us. We can measure the performance of your stored routines with 8.0 versus 5.7 and then proclaim this bug report as a duplicate of some previous verified bug. If that is what you want us to do, we have to have all the tables that are used in SQL statements in the stored routines .....
[21 Jun 2019 6:25]
gao haidong
Officially, 8.0 has twice as much performance improvement as 5.7. To upgrade to 8.0, it is found that the performance of version 5.7 is not as good as that of version 5.7
[21 Jun 2019 6:34]
gao haidong
have all the tables that are used in SQL statements in the stored routines CREATE TABLE vocs.ldarsealpointlog ( Id char(32) NOT NULL COMMENT '密封点日志Id', InspectionCycleId char(32) DEFAULT NULL COMMENT '检测核算周期Id', SealPointCode varchar(50) DEFAULT NULL COMMENT '密封点编码', LdarDeviceId char(32) DEFAULT NULL COMMENT '装置Id', DeviceName varchar(50) DEFAULT NULL COMMENT '装置名称', DeviceCode varchar(50) DEFAULT NULL COMMENT '装置编号', LdarGroupId char(32) DEFAULT NULL COMMENT '群组ID', GroupNumber varchar(50) DEFAULT NULL COMMENT '群组编号', GroupName varchar(200) DEFAULT NULL COMMENT '群组名称', GroupPlace varchar(200) DEFAULT NULL COMMENT '群组位置描述', GroupDescription varchar(100) DEFAULT NULL COMMENT '群组工艺描述', AreaId char(32) DEFAULT NULL COMMENT '区域Id', AREACode varchar(200) DEFAULT NULL COMMENT '组件所在装置的区域号', AreaName varchar(50) DEFAULT NULL COMMENT '区域名称', FloorId char(32) DEFAULT NULL COMMENT '平台层Id', FloorCode varchar(200) DEFAULT NULL COMMENT '组件所在楼层编号', FloorName decimal(10, 2) DEFAULT NULL COMMENT '响应因子值', PIDNo varchar(50) DEFAULT NULL COMMENT 'PID图号', EquipmentId char(32) DEFAULT NULL COMMENT '设备ID', EquipmentCode varchar(200) DEFAULT NULL COMMENT '组件所属设备或者附近设备编号', EquipmentName varchar(50) DEFAULT NULL COMMENT '设备名称', ExtendNumber varchar(50) DEFAULT NULL COMMENT '扩展号', PictureIdCode varchar(200) DEFAULT NULL COMMENT '组件所在图号流水号或挂牌编号', IsOrderChoose int(11) DEFAULT NULL COMMENT '是否被工单选择 0未选择 1已选择', NextRoundDate datetime DEFAULT NULL COMMENT '下轮开始检测日期', YearRound char(5) DEFAULT NULL COMMENT '当前检测轮', FirstClassify varchar(200) DEFAULT NULL COMMENT '组件一级分类', SecondClassify varchar(200) DEFAULT NULL COMMENT '组件二级分类', SealPointSeat varchar(50) DEFAULT NULL COMMENT '密封点位置描述', SealPointName varchar(50) DEFAULT NULL COMMENT '密封点工艺描述', SealingPointTypeId char(32) DEFAULT NULL COMMENT '密封点类型ID', SealingPointTypeName varchar(50) DEFAULT NULL COMMENT '密封点类型名称', MediumId char(32) DEFAULT NULL COMMENT '介质ID', MediumName varchar(50) DEFAULT NULL COMMENT '介质名称（物料名称）', MediumStateId char(32) DEFAULT NULL COMMENT '介质状态ID', MediumStateName varchar(50) DEFAULT NULL COMMENT '介质状态名称', SealPointSize varchar(100) DEFAULT NULL COMMENT '公称直径', IsHoldTemp varchar(50) DEFAULT NULL COMMENT '是否保温或保冷', IsReached int(11) DEFAULT NULL COMMENT '是否可达 0不可达 1可达', UnreachAbleReasonId varchar(32) DEFAULT NULL COMMENT '不可达原因Id', UnreachAbleReason varchar(200) DEFAULT NULL COMMENT '不可达原因', SignState int(11) DEFAULT NULL COMMENT '密封点当前检测状态（0：未检测，1：检测无泄漏，2：泄漏）', Leakage decimal(10, 2) DEFAULT NULL COMMENT '密封点当前泄漏量', FirstMeasureDate datetime DEFAULT NULL COMMENT '首次检测日期', LastMeasureDate datetime DEFAULT NULL COMMENT '密封点上次检测时间', NextMeasureDate datetime DEFAULT NULL COMMENT '下次检测日期', DetectionCycle int(11) DEFAULT NULL COMMENT '检测周期(天）', DetectionCycleDes varchar(200) DEFAULT NULL COMMENT '检测频率', LeakStandaRdage decimal(10, 2) DEFAULT NULL COMMENT '泄漏阀值', Pressure varchar(100) DEFAULT NULL COMMENT '压力', Temperature varchar(50) DEFAULT NULL COMMENT '温度', RunTimeperYear varchar(50) DEFAULT NULL COMMENT '年运行时间', TOCwf varchar(400) DEFAULT NULL COMMENT 'TOC质量分数', CH4wf varchar(400) DEFAULT NULL COMMENT '甲烷质量分数', VOCswf varchar(400) DEFAULT NULL COMMENT 'VOCs质量分数', EQUmodel varchar(400) DEFAULT NULL COMMENT '设备型号', Manufacturer varchar(100) DEFAULT NULL COMMENT '生产厂家', ProcessId varchar(32) DEFAULT NULL COMMENT '物料流id', ProcessName varchar(50) DEFAULT NULL COMMENT '物料流名称', Componet varchar(400) DEFAULT NULL COMMENT '流体成分', VOCsPercent varchar(200) DEFAULT NULL COMMENT '挥发性有机物质量占比%', Method varchar(200) DEFAULT NULL COMMENT '组件检测方式', Property varchar(200) DEFAULT NULL COMMENT '组件检测属性', StaticProperty varchar(200) DEFAULT NULL COMMENT '泄漏源动静属性', Remark varchar(200) DEFAULT NULL COMMENT '备注', CompanyId char(32) DEFAULT NULL COMMENT '公司Id', DepId char(32) DEFAULT NULL COMMENT '部门ID', EnabledBeginDate datetime DEFAULT NULL COMMENT '初次运行日期', EnabledEndDate datetime DEFAULT NULL COMMENT '有效截至日期', Enabled int(11) DEFAULT 1 COMMENT '是否有效 0 无效 1 有效', InUser char(32) DEFAULT NULL COMMENT '录入人员', InDate datetime DEFAULT NULL COMMENT '录入日期', EditUser char(32) DEFAULT NULL COMMENT '修改人员', EditDate datetime DEFAULT NULL COMMENT '修改日期', DelUser char(32) DEFAULT NULL COMMENT '删除人员', DelState int(11) DEFAULT 0 COMMENT '标识该行数据是否被删除 0未删除 1已删除', DelDate datetime DEFAULT NULL COMMENT '删除时间', SourceType int(11) DEFAULT 1 COMMENT ' 数据来源 1 Ldar企业信息管 2企业上报端', PutRecordDate datetime DEFAULT CURRENT_TIMESTAMP COMMENT '建档日期', PRIMARY KEY (Id) ) ENGINE = INNODB, AVG_ROW_LENGTH = 1089, CHARACTER SET utf8, COLLATE utf8_general_ci, COMMENT = '密封点基础信息表'; ALTER TABLE vocs.ldarsealpointlog ADD UNIQUE INDEX index_comidcycidcode (InspectionCycleId, SealPointCode); ALTER TABLE vocs.ldarsealpointlog ADD INDEX UK_InsDevicecode (CompanyId, InspectionCycleId, DeviceCode, LdarDeviceId); ALTER TABLE vocs.ldarsealpointlog ADD INDEX UK_ldarsealpointlog_CompanyId (CompanyId); ALTER TABLE vocs.ldarsealpointlog ADD INDEX UK_ldarsealpointlog_file (GroupNumber, InspectionCycleId, CompanyId); ALTER TABLE vocs.ldarsealpointlog ADD INDEX UK_ldarsealpointlog_sealpointcode (CompanyId, SealPointCode, InspectionCycleId);
[21 Jun 2019 6:34]
gao haidong
CREATE TABLE vocs.ldarworkplandetailed ( Id char(32) NOT NULL COMMENT '检测计划工单明细Id', InspectionCycleId char(32) DEFAULT NULL COMMENT '检测核算周期Id', WorkPlanId char(32) DEFAULT NULL COMMENT '计划ID', DepId char(32) DEFAULT NULL COMMENT '部门ID', PlanCode varchar(50) DEFAULT NULL COMMENT '计划编号', WorkOrderId char(32) DEFAULT NULL COMMENT '检测工单Id', WorkOrderCode varchar(50) DEFAULT NULL COMMENT '工单编号', DeviceInfoId char(32) DEFAULT NULL COMMENT '检测装置信息ID', SealPointId char(32) DEFAULT NULL COMMENT '密封点ID', LdarDeviceId char(32) DEFAULT NULL COMMENT '装置Id', LdarGroupId char(32) DEFAULT NULL COMMENT '群组ID', GroupPlace varchar(200) DEFAULT NULL COMMENT '群组位置描述', GroupDescribe varchar(200) DEFAULT NULL COMMENT '群组工业描述', DeviceName varchar(50) DEFAULT NULL COMMENT '装置名称', DeviceCode varchar(50) DEFAULT NULL COMMENT '装置编号', GroupNumber varchar(50) DEFAULT NULL COMMENT '群组编号', AREACode varchar(200) DEFAULT NULL COMMENT '组件所在装置的区域号', AreaName varchar(50) DEFAULT NULL COMMENT '区域名称', FloorCode varchar(200) DEFAULT NULL COMMENT '组件所在楼层编号', FloorName varchar(50) DEFAULT NULL COMMENT '平台（层）', SealPointSeat varchar(50) DEFAULT NULL COMMENT '密封点位置描述', SealPointName varchar(50) DEFAULT NULL COMMENT '密封点工艺描述', SealingPointTypeName varchar(50) DEFAULT NULL COMMENT '密封点类型名称', MediumId char(32) DEFAULT NULL COMMENT '介质ID', MediumName varchar(50) DEFAULT NULL COMMENT '介质名称', MediumStateId char(32) DEFAULT NULL COMMENT '介质状态ID', MediumStateName varchar(50) DEFAULT NULL COMMENT '介质状态名称', UnreachAbleReason varchar(200) DEFAULT NULL COMMENT '不可达原因', AreaId char(32) DEFAULT NULL COMMENT '区域Id', FloorId char(32) DEFAULT NULL COMMENT '平台层Id', SealingPointTypeId char(32) DEFAULT NULL COMMENT '密封点类型ID', ExtendNumber varchar(50) DEFAULT NULL COMMENT '扩展号', SealPointCode varchar(50) DEFAULT NULL COMMENT '密封点编码', StateId int(11) DEFAULT NULL COMMENT '明细状态（0：未完成，1：已完成）', SignState int(11) DEFAULT NULL COMMENT '密封点当前检测状态（0：未检测，1：检测无泄漏，2：泄漏）', SignNumber varchar(50) DEFAULT NULL COMMENT '红牌号码', Leakage decimal(10, 2) DEFAULT NULL COMMENT '净检测值', Bkgnd varchar(50) DEFAULT NULL COMMENT '环境本底值', Originalppm varchar(50) DEFAULT NULL COMMENT '泄露检测值', Concentration varchar(200) DEFAULT NULL COMMENT '仪器检测浓度', Ndc varchar(200) DEFAULT NULL COMMENT '扩展字段，暂时未用', LeakageDefinition varchar(200) DEFAULT NULL COMMENT '泄漏定义值', Description varchar(500) DEFAULT NULL COMMENT '泄漏描述', ImageList varchar(300) DEFAULT NULL COMMENT '泄漏拍照', DetectionMechanismId char(32) DEFAULT NULL COMMENT '第三方检测机构ID', TestUserId char(32) DEFAULT NULL COMMENT '检测人', TestUserName varchar(500) DEFAULT NULL COMMENT '检测人名称', TestDate datetime DEFAULT NULL COMMENT '检测时间', DaysOfQuarterly varchar(200) DEFAULT NULL COMMENT '本核算周期运行天数', HoursOfDaily varchar(200) DEFAULT NULL COMMENT '日均运行小时', ComPonent varchar(400) DEFAULT NULL COMMENT '流体成分', Medium varchar(200) DEFAULT NULL COMMENT '介质状态', VOCsPercent varchar(200) DEFAULT NULL COMMENT '挥发性有机物质量占比，%', Method varchar(200) DEFAULT NULL COMMENT '组件检测方式', Frequency varchar(200) DEFAULT NULL COMMENT '检测频率', Property varchar(200) DEFAULT NULL COMMENT '组件检测属性', IsReached int(11) DEFAULT NULL COMMENT '是否可达 0不可达 1可达', ReachedReason varchar(2000) DEFAULT NULL COMMENT '可达点或不可达点的判定依据', ProcessId varchar(32) DEFAULT NULL COMMENT '物料流Id', ProcessName varchar(50) DEFAULT NULL COMMENT '物料流名称', MachineCode varchar(50) DEFAULT NULL COMMENT '检测仪器编号', InstrumentName varchar(200) DEFAULT NULL COMMENT '检测仪器名称', Protection varchar(2000) DEFAULT NULL COMMENT '检测人员安全防护措施', Temperature varchar(50) DEFAULT NULL COMMENT '温度', WindDirection varchar(50) DEFAULT NULL COMMENT '风向', WindSpeed varchar(50) DEFAULT NULL COMMENT '风速', CompanyId char(32) DEFAULT NULL COMMENT '公司Id', Remark varchar(200) DEFAULT NULL COMMENT '备注', Enabled int(11) DEFAULT 1 COMMENT '是否有效 0 无效 1 有效', InUser char(32) DEFAULT NULL COMMENT '录入人员', InDate datetime DEFAULT NULL COMMENT '录入日期', EditUser char(32) DEFAULT NULL COMMENT '修改人员', EditDate datetime DEFAULT NULL COMMENT '修改日期', DelUser char(32) DEFAULT NULL COMMENT '删除人员', DelState int(11) DEFAULT 0 COMMENT '标识该行数据是否被删除 0未删除 1已删除', DelDate datetime DEFAULT NULL COMMENT '删除时间', SourceType int(11) DEFAULT 1 COMMENT ' 数据来源 1 Ldar企业信息管 2企业上报端', TestBeginTime datetime DEFAULT NULL COMMENT '检测开始时间', TestEndTime datetime DEFAULT NULL COMMENT '检测结束时间', TestStopTime int(11) DEFAULT NULL COMMENT '检测停留时间 秒', MachineCode2 varchar(255) DEFAULT NULL, ResponseFactor decimal(10, 2) DEFAULT NULL COMMENT '响应因子值', BeforeRepairValue decimal(10, 2) DEFAULT NULL COMMENT '修复前净检测值', IsUpdate varchar(255) DEFAULT NULL COMMENT '是否修正 0 否 1 是', AuditName varchar(255) DEFAULT NULL COMMENT '审核人名称', AuditId char(32) DEFAULT NULL COMMENT '审核人ID', AuditDate datetime DEFAULT NULL COMMENT '审核时间', DynamicStaticName varchar(255) DEFAULT NULL, MethanePer decimal(10, 4) DEFAULT NULL COMMENT '甲烷质量分数', NoVOCsPer decimal(10, 4) DEFAULT NULL COMMENT '其他非VOCs质量分数', CMALeakage decimal(10, 2) DEFAULT NULL COMMENT 'CMA国标响应因子修正后检测值', PRIMARY KEY (Id) ) ENGINE = INNODB, AVG_ROW_LENGTH = 787, CHARACTER SET utf8, COLLATE utf8_general_ci, COMMENT = '检测计划工单明细'; ALTER TABLE vocs.ldarworkplandetailed ADD INDEX IDX_ldarworkplandetailed (CompanyId, LdarDeviceId, TestDate); ALTER TABLE vocs.ldarworkplandetailed ADD INDEX index_comanyid2 (WorkOrderId, SealingPointTypeId, SealingPointTypeName); ALTER TABLE vocs.ldarworkplandetailed ADD INDEX index_companyid (CompanyId); ALTER TABLE vocs.ldarworkplandetailed ADD INDEX index_companyTime (TestDate); ALTER TABLE vocs.ldarworkplandetailed ADD INDEX index_DeviceInfoId (DeviceInfoId, SignState, IsReached, DelState); ALTER TABLE vocs.ldarworkplandetailed ADD INDEX index_id (DepId, LdarDeviceId); ALTER TABLE vocs.ldarworkplandetailed ADD INDEX index_Mworkplanid (WorkPlanId, DelState, SignState, CompanyId, DepId, LdarDeviceId); ALTER TABLE vocs.ldarworkplandetailed ADD INDEX index_planid (WorkOrderId, DeviceInfoId); ALTER TABLE vocs.ldarworkplandetailed ADD INDEX index_sealpoint (SignState, WorkPlanId, SealPointId); ALTER TABLE vocs.ldarworkplandetailed ADD UNIQUE INDEX index_sealpointtestdate (SealPointId, TestDate); ALTER TABLE vocs.ldarworkplandetailed ADD INDEX index_seapoint (WorkPlanId, SealingPointTypeId, DelState, LdarDeviceId, DepId); ALTER TABLE vocs.ldarworkplandetailed ADD INDEX index_sourceType (SourceType); ALTER TABLE vocs.ldarworkplandetailed ADD INDEX index_typeid (SealingPointTypeId, WorkPlanId, WorkOrderId); ALTER TABLE vocs.ldarworkplandetailed ADD INDEX index_workplanid (WorkPlanId, SealPointId);
[21 Jun 2019 12:44]
MySQL Verification Team
Hi, Yes, it is true, mysql 8.0 has twice the performance of 5.7, in high-concurrency situations and also in the cases where you can use more efficient SQL features, introduced in 8.0. Next, I do not see the contents of the two tables, which are necessary for us in order to reproduce the performance problem that you are reporting.
[24 Jun 2019 9:07]
gao haidong
It seems that the data you want can not be submitted here, because the amount of data is too large, there are more than a dozen Gs of data, which can not be uploaded here.
[24 Jun 2019 12:45]
MySQL Verification Team
Sorry, but in that case we can not even attempt to verify the bug. If you can create a sufficient number of rows that would enable us to see the drop in performance, then we would then be able to try to verify a performance regression. Otherwise, we simply can not repeat your problem.
