关于MySQL单表优化问题

🛸 其他技术问题
,
1

image
image1579×278 15.4 KB

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

2

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

4

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

5

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

6

表结构: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

7

我试试

8

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

9

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

10

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

11

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

12

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

1 个赞
13

是滴，谢谢大佬

14

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

15

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

16

表结构看看

17

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

18

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

1 个赞
19

有表结构吗

20

现在这条SQL耗时多少，期望优化到耗时多少之内？ 感觉优化一定得有个可量化 的目标。

21

字段 公司 + 时间创建索引