关于MySQL单表优化问题


关于mysql单表查询慢sql优化问题,这里用的索引是UPDATELOAD_ALERT_TIME这列的索引,mysql版本是5.7,除了创建联合索引外还有什么其他方法来优化这种单表查询的慢sql

把表结构一起发出来,再就是你的过滤条件适合建立复合索引,会快很多

再给点信息,表数据量,表结构,预期的符合条件的数量

一眼看上去就是缺索引啊 :joy_cat:,那个公司字段猜起来过滤性不错,建议建公司+时间复合索引,如果其他列选择性也好,也可以加进去,加到时间前边

表结构: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 ‘订单来源 1:定时器拉取 2:mq补偿 3:脚本插入’,
UPLOAD_FLAG smallint(1) DEFAULT ‘0’ COMMENT ‘上传标记:0 无基础数据,1 待上传 ,2:已第一次上传 3:已第二次上传 4:已第三次上传’,
FIRST_EXCEPTION_TYPE varchar(4) COLLATE utf8_bin DEFAULT NULL COMMENT ‘第一次异常类型:1:信息缺失 2:上传拦截’,
CURRENT_INTERCEPT_REASON_MESS varchar(500) COLLATE utf8_bin DEFAULT NULL COMMENT ‘基础数据拉取失败未上传原因,所有必填字段为空等’,
SECND_EXCEPTION_TYPE varchar(4) COLLATE utf8_bin DEFAULT NULL COMMENT ‘第二次异常类型:1:信息缺失 2:上传拦截’,
SECND_INTERCEPT_REASON_MESS varchar(500) COLLATE utf8_bin DEFAULT NULL COMMENT ‘第二数据抽取拦截原因’,
THIRD_EXCEPTION_TYPE varchar(4) COLLATE utf8_bin DEFAULT NULL COMMENT ‘第三次异常类型:1:信息缺失 2:上传拦截’,
THIRD_INTERCEPT_REASON_MESS varchar(500) COLLATE utf8_bin DEFAULT NULL COMMENT ‘第三次数据抽取拦截原因’,
DESPATCH_TIME datetime DEFAULT NULL COMMENT ‘发货时间’,
VALID_TIME varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT ‘有效时长’,
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 ‘是否需修改上传:0是 1否’,
UPDATELOAD_FIRST_RESPONSE_CODE varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT ‘第一次上传回参code值’,
UPDATELOAD_FIRST_RESPONSE_MESS varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT ‘第一次上传返回信息’,
UPDATELOAD_FIRST_TIME datetime DEFAULT NULL COMMENT ‘第一次上传时间’,
UPDATELOAD_SECND_RESPONSE_CODE varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT ‘第二次上传回参code值’,
UPDATELOAD_SECND_RESPONSE_MESS varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT ‘第二次上传返回信息’,
UPDATELOAD_SECND_TIME datetime DEFAULT NULL COMMENT ‘第二次上传时间’,
UPDATELOAD_THIRD_RESPONSE_CODE varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT ‘第三次上传回参code值’,
UPDATELOAD_THIRD_RESPONSE_MESS varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT ‘第三次上传返回信息’,
UPDATELOAD_THIRD_TIME datetime DEFAULT NULL COMMENT ‘第三次上传时间’,
UPDATELOAD_ALERT_RESPONSE_CODE varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT ‘修改上传回参code值’,
UPDATELOAD_ALERT_RESPONSE_MESS varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT ‘修改上传返回信息’,
ALERT_EXCEPTION_TYPE varchar(4) COLLATE utf8_bin DEFAULT NULL COMMENT ‘修改异常类型 1:信息缺失 2:上传拦截’,
UPDATELOAD_ALERT_TIME datetime DEFAULT NULL COMMENT ‘修改上传时间’,
CREATED_TIME datetime DEFAULT NULL COMMENT ‘创建时间’,
LAST_UPT_TIME datetime DEFAULT NULL COMMENT ‘修改时间’,
DELETE_FLAG smallint(1) DEFAULT ‘0’ COMMENT ‘删除标记:0 否,1 是’,
VEHICLE_NUMBER varchar(32) COLLATE utf8_bin DEFAULT NULL
USER_NM varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT
CUSTOMER_NM varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT
CONSIGNOR_NAME varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT
REMARK varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT ‘备注’,
REMARK2 varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT ‘第二次备注’,
REMARK3 varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT ‘第三次备注’,
REMARK4 varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT ‘第一次修改备注’,
REMARK5 varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT
OPERATE_NAME varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT
LOW_CAPITAL_FLAG smallint(1) DEFAULT ‘0’ COMMENT
CONSIGNOR_SETTLE_FLAG smallint(1) DEFAULT ‘0’ COMMENT
CONSIGNOR_USER_ID bigint(19) DEFAULT NULL COMMENT
CARRIER_USER_ID bigint(19) DEFAULT NULL COMMENT
CONSIGNOR_USER_TYPE char(2) COLLATE utf8_bin DEFAULT NULL COMMENT
CARRIER_USER_TYPE char(2) COLLATE utf8_bin DEFAULT NULL COMMENT
IS_BEYOND_FIVE_VEHICLE smallint(1) DEFAULT ‘0’ COMMENT
CHECK_TIME datetime DEFAULT NULL COMMENT
HAVE_SECND_UPLOAD_TIME datetime DEFAULT NULL COMMENT
FIRST_DISPLAY_TIME datetime DEFAULT NULL
BUSINESS_AREA_CODE varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT ‘业务归属区域CODE’,
BUSINESS_AREA_NAME varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT ‘业务归属区域名称’,
PENDING_REVIEW_REASON varchar(512) COLLATE utf8_bin DEFAULT NULL
TRACK_RELEASE_REASON varchar(512) COLLATE utf8_bin DEFAULT NULL
FIRST_REUPLOAD_NUM smallint(6) DEFAULT ‘0’ COMMENT ‘第一次重传次数’,
SECOND_REUPLOAD_NUM smallint(6) DEFAULT ‘0’ COMMENT ‘第二次重传次数’,
THIRD_REUPLOAD_NUM smallint(6) DEFAULT ‘0’ COMMENT ‘第三次重传次数’,
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 ‘运抵信息重传次数’,
IS_EXIST_ADJUST smallint(1) DEFAULT ‘0’
ADJUST_MONEY decimal(19,3) DEFAULT NULL
REVOCATION_SEC_STATUS smallint(1) DEFAULT ‘0’ COMMENT ‘撤回第二次数据状态: 0 未撤回, 1 已撤回,2撤回失败’,
REVOCATION_SEC_RESPONSE_MESS varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT ‘撤回返回信息’,
REVOCATION_SEC_TIME datetime DEFAULT NULL COMMENT ‘撤回时间’,
TAX_EXCEPTION_TYPE smallint(6) DEFAULT ‘1’
IS_REMOVE_CONTROL_OPEN_INVOICE smallint(6) DEFAULT NULL COMMENT ‘是否解除管控开票: 0管控中,1已解除’,
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

数据量:1594126

我试试

针对时间建立的索引,区间跨度太大的情况下,即使走索引也会很慢,建议根据字段区分度来调整索引

我去测测看,看这个时间段的数据量,然后再看看如何加索引合适

应该是这个问题了,看了下,全表150w多条数据,这个时间段就有115w多条数据,走了索引还有这么多数据,确实加个联合索引比较合适

索引基本上效果不大,用UPDATELOAD_ALERT_TIME,看看查询字段里,有没有字段的分布更广一点的,建一个索引或联合索引,减少数据查询范围

CONSIGNOR_NAME、ALERT_EXCEPTION_TYPE、UPDATELOAD_ALERT_TIME,可以尝试按这个顺序建立一个联合索引

1 个赞

是滴,谢谢大佬

我去试试,差不多就这个方向

索引能找到的数据一般20%以下才走索引,再多就看索引区分度是不是不够,换其他列

表结构看看

复合索引根据实际情况,选择度添加

数据量超过40%j就不走索引了,业务也要优化一下,尽可能多过滤数据。

1 个赞