Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 关于MySQL单表优化问题
|
username: sxli
Regarding the issue of slow SQL queries on a single MySQL table, the index used here is on the column UPDATELOAD_ALERT_TIME, and the MySQL version is 5.7. Besides creating a composite index, are there any other methods to optimize such slow single-table queries?
Please send the table structure as well. Additionally, your filter conditions are suitable for creating a composite index, which will be much faster.
Please provide more information, such as the table data volume, table structure, and the expected number of qualifying entries.
At first glance, it looks like it’s missing an index 
. The company field seems to have good selectivity, so I suggest creating a composite index with company and time. If other columns also have good selectivity, you can add them as well, placing them before the time field.
Table Structure:
|
username: 
|
username: 
|
username: CREATE TABLE `tf_tax_orderinfo_uploading` (
`ID` bigint(19) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`ORDER_ID` bigint(19) NOT NULL,
`COMPANY_CODE` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`ORDER_ORIGIN` smallint(2) DEFAULT NULL COMMENT 'Order Source: 1: Timer Pull, 2: MQ Compensation, 3: Script Insert',
`UPLOAD_FLAG` smallint(1) DEFAULT '0' COMMENT 'Upload Flag: 0 No Basic Data, 1 Pending Upload, 2 First Upload, 3 Second Upload, 4 Third Upload',
`FIRST_EXCEPTION_TYPE` varchar(4) COLLATE utf8_bin DEFAULT NULL COMMENT 'First Exception Type: 1: Information Missing, 2: Upload Interception',
`CURRENT_INTERCEPT_REASON_MESS` varchar(500) COLLATE utf8_bin DEFAULT NULL COMMENT 'Reason for Failure to Upload Basic Data, All Required Fields Empty, etc.',
`SECND_EXCEPTION_TYPE` varchar(4) COLLATE utf8_bin DEFAULT NULL COMMENT 'Second Exception Type: 1: Information Missing, 2: Upload Interception',
`SECND_INTERCEPT_REASON_MESS` varchar(500) COLLATE utf8_bin DEFAULT NULL COMMENT 'Reason for Second Data Extraction Interception',
`THIRD_EXCEPTION_TYPE` varchar(4) COLLATE utf8_bin DEFAULT NULL COMMENT 'Third Exception Type: 1: Information Missing, 2: Upload Interception',
`THIRD_INTERCEPT_REASON_MESS` varchar(500) COLLATE utf8_bin DEFAULT NULL COMMENT 'Reason for Third Data Extraction Interception',
`DESPATCH_TIME` datetime DEFAULT NULL COMMENT 'Dispatch Time',
`VALID_TIME` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT 'Valid Duration',
`UP_CAPITAL_FLAG` smallint(1) DEFAULT '0',
`DRIVER_SETTLE_FLAG` smallint(1) DEFAULT '0',
`ETC_RESULT_FLAG` smallint(1) DEFAULT '0',
`CONSIGNOR_INVOICE_FLAG` smallint(1) DEFAULT '0',
`RECEIPT_APPROVE_FLAG` smallint(6) DEFAULT NULL,
`TRACE_NORMAL_FLAG` smallint(6) DEFAULT NULL,
`ALTER_FLAG` smallint(1) DEFAULT '0' COMMENT 'Need to Modify Upload: 0 Yes, 1 No',
`UPDATELOAD_FIRST_RESPONSE_CODE` varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT 'First Upload Response Code',
`UPDATELOAD_FIRST_RESPONSE_MESS` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT 'First Upload Response Message',
`UPDATELOAD_FIRST_TIME` datetime DEFAULT NULL COMMENT 'First Upload Time',
`UPDATELOAD_SECND_RESPONSE_CODE` varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT 'Second Upload Response Code',
`UPDATELOAD_SECND_RESPONSE_MESS` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT 'Second Upload Response Message',
`UPDATELOAD_SECND_TIME` datetime DEFAULT NULL COMMENT 'Second Upload Time',
`UPDATELOAD_THIRD_RESPONSE_CODE` varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT 'Third Upload Response Code',
`UPDATELOAD_THIRD_RESPONSE_MESS` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT 'Third Upload Response Message',
`UPDATELOAD_THIRD_TIME` datetime DEFAULT NULL COMMENT 'Third Upload Time',
`UPDATELOAD_ALERT_RESPONSE_CODE` varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT 'Modify Upload Response Code',
`UPDATELOAD_ALERT_RESPONSE_MESS` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT 'Modify Upload Response Message',
`ALERT_EXCEPTION_TYPE` varchar(4) COLLATE utf8_bin DEFAULT NULL COMMENT 'Modify Exception Type: 1: Information Missing, 2: Upload Interception',
`UPDATELOAD_ALERT_TIME` datetime DEFAULT NULL COMMENT 'Modify Upload Time',
`CREATED_TIME` datetime DEFAULT NULL COMMENT 'Creation Time',
`LAST_UPT_TIME` datetime DEFAULT NULL COMMENT 'Modification Time',
`DELETE_FLAG` smallint(1) DEFAULT '0' COMMENT 'Delete Flag: 0 No, 1 Yes',
`VEHICLE_NUMBER` varchar(32) COLLATE utf8_bin DEFAULT NULL,
`USER_NM` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT 'User Name',
`CUSTOMER_NM` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT 'Customer Name',
`CONSIGNOR_NAME` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT 'Consignor Name',
`REMARK` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'Remark',
`REMARK2` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'Second Remark',
`REMARK3` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'Third Remark',
`REMARK4` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'First Modify Remark',
`REMARK5` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'Fifth Remark',
`OPERATE_NAME` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT 'Operator Name',
`LOW_CAPITAL_FLAG` smallint(1) DEFAULT '0' COMMENT 'Low Capital Flag',
`CONSIGNOR_SETTLE_FLAG` smallint(1) DEFAULT '0' COMMENT 'Consignor Settle Flag',
`CONSIGNOR_USER_ID` bigint(19) DEFAULT NULL COMMENT 'Consignor User ID',
`CARRIER_USER_ID` bigint(19) DEFAULT NULL COMMENT 'Carrier User ID',
`CONSIGNOR_USER_TYPE` char(2) COLLATE utf8_bin DEFAULT NULL COMMENT 'Consignor User Type',
`CARRIER_USER_TYPE` char(2) COLLATE utf8_bin DEFAULT NULL COMMENT 'Carrier User Type',
`IS_BEYOND_FIVE_VEHICLE` smallint(1) DEFAULT '0' COMMENT 'Is Beyond Five Vehicles',
`CHECK_TIME` datetime DEFAULT NULL COMMENT 'Check Time',
`HAVE_SECND_UPLOAD_TIME` datetime DEFAULT NULL COMMENT 'Second Upload Time',
`FIRST_DISPLAY_TIME` datetime DEFAULT NULL,
`BUSINESS_AREA_CODE` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT 'Business Area Code',
`BUSINESS_AREA_NAME` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT 'Business Area Name',
`PENDING_REVIEW_REASON` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT 'Pending Review Reason',
`TRACK_RELEASE_REASON` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT 'Track Release Reason',
`FIRST_REUPLOAD_NUM` smallint(6) DEFAULT '0' COMMENT 'First Reupload Count',
`SECOND_REUPLOAD_NUM` smallint(6) DEFAULT '0' COMMENT 'Second Reupload Count',
`THIRD_REUPLOAD_NUM` smallint(6) DEFAULT '0' COMMENT 'Third Reupload Count',
`DELIVER_FLAG` smallint(1) DEFAULT '0',
`ARRIVAL_INFO_EXCEPTION_TYPE` varchar(4) COLLATE utf8_bin DEFAULT NULL,
`ARRIVAL_INFO_UPLOAD_TIME` datetime DEFAULT NULL,
`ARRIVAL_INFO_RESPONSE_CODE` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`ARRIVAL_INFO_RESPONSE_MESS` varchar(200) COLLATE utf8_bin DEFAULT NULL,
`ARRIVALINFO_REUPLOAD_NUM` smallint(6) DEFAULT '0' COMMENT 'Arrival Info Reupload Count',
`IS_EXIST_ADJUST` smallint(1) DEFAULT '0',
`ADJUST_MONEY` decimal(19,3) DEFAULT NULL,
`REVOCATION_SEC_STATUS` smallint(1) DEFAULT '0' COMMENT 'Revocation Second Data Status: 0 Not Revoked, 1 Revoked, 2 Revocation Failed',
`REVOCATION_SEC_RESPONSE_MESS` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT 'Revocation Response Message',
`REVOCATION_SEC_TIME` datetime DEFAULT NULL COMMENT 'Revocation Time',
`TAX_EXCEPTION_TYPE` smallint(6) DEFAULT '1',
`IS_REMOVE_CONTROL_OPEN_INVOICE` smallint(6) DEFAULT NULL COMMENT 'Is Remove Control Open Invoice: 0 Controlled, 1 Removed',
PRIMARY KEY (`ID`),
UNIQUE KEY `ORDER_ID_INDEX` (`ORDER_ID`),
KEY `idx_VEHICLE_NUMBER` (`VEHICLE_NUMBER`),
KEY `idx_USER_NM` (`USER_NM`),
KEY `idx_CUSTOMER_NM` (`CUSTOMER_NM`),
KEY `idx_CONSIGNOR_USER_ID` (`CONSIGNOR_USER_ID`),
KEY `idx_CARRIER_USER_ID` (`CARRIER_USER_ID`),
KEY `index_HAVE_SECND_UPLOAD_TIME` (`HAVE_SECND_UPLOAD_TIME`),
KEY `index_FIRST_DISPLAY_TIME` (`FIRST_DISPLAY_TIME`),
KEY `index_DESPATCH_TIME` (`DESPATCH_TIME`),
KEY `index_UPDATELOAD_FIRST_TIME` (`UPDATELOAD_FIRST_TIME`),
KEY `index_UPDATELOAD_SECND_TIME` (`UPDATELOAD_SECND_TIME`),
KEY `index_UPDATELOAD_THIRD_TIME` (`UPDATELOAD_THIRD_TIME`),
KEY `index_UPDATELOAD_ALERT_TIME` (`UPDATELOAD_ALERT_TIME`),
KEY `idx_LAST_UPT_TIME` (`LAST_UPT_TIME`)
) ENGINE=InnoDB AUTO_INCREMENT=3071 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Data Volume: 1,594,126
For indexes established based on time, if the interval span is too large, it will be slow even with indexing. It is recommended to adjust the index based on the field’s cardinality.
I’ll go test it out, check the data volume for this time period, and then see how to add indexes appropriately.
It should be this issue. I checked, and out of the entire table with more than 1.5 million rows, this time period alone has more than 1.15 million rows. Even with the index, there are still so many rows. Indeed, adding a composite index would be more appropriate.
The index basically has little effect. Use UPDATELOAD_ALERT_TIME to see if there is a field with a wider distribution in the query fields. Create an index or a composite index to reduce the data query range.
You can try creating a composite index in the order of CONSIGNOR_NAME, ALERT_EXCEPTION_TYPE, and UPDATELOAD_ALERT_TIME.
Indexes are generally used when the data found is below 20%. If it’s more than that, it depends on whether the index selectivity is sufficient; otherwise, consider using other columns.
Composite indexes should be added based on the actual situation.
When the data volume exceeds 40%, the index will not be used. The business logic also needs to be optimized to filter as much data as possible.
How long does this SQL currently take, and what is the expected time after optimization? I feel that optimization must have a quantifiable goal.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: