记一次生产慢sql查询的解决

今天测试在验证的时候，测试反馈工单后台查看数据特别慢，慢到数据无法展示。那就看下呗。看了下有慢sql。

本着对生产敬畏的心态，转移到测试环境进行验证。测试数据不够，自己造呗。工单表具备以下特征：

数据字段多，索引也多；
随着数据的流转，数据一直在更新；以下数据是参考测试表结构的模拟；

-- 创建表，多加了几个字段为了占用空间填充CREATE TABLE `t_loan_order` (  `app_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '工单ID',  `customer_id` bigint(20) NOT NULL COMMENT '客户ID',  `customer_name` varchar(20) DEFAULT NULL COMMENT '客户姓名',  `customer_certid` varchar(18) DEFAULT NULL COMMENT '客户身份证号',  `phone_no` varchar(15) DEFAULT NULL COMMENT '手机号码',  `purpose` varchar(30) DEFAULT NULL COMMENT '借款用途',  `app_status` int(11) DEFAULT NULL COMMENT '工单状态',  `product_name` varchar(255) DEFAULT NULL COMMENT '产品名称',  `create_time` datetime DEFAULT NULL COMMENT '创建时间',  PRIMARY KEY (`app_id`),  KEY `union_idx_id_status` (`app_id`,`app_status`) USING BTREE,  KEY `idx_create_time` (`create_time`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT  CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='工单表';
-- 批量造100万数据DROP PROCEDURE IF EXISTS `batch_insert`;CREATE PROCEDURE  `batch_insert`(IN `num` int)BEGIN   declare i int default 0;   set autocommit= 0;   while i<num do     set i=i+1;     insert into t_loan_order(`customer_id`,`customer_name`,`customer_certid`,`phone_no`,`purpose`,`app_status`,`product_name`,`create_time`) values(RAND()*100000+1000,'用户姓名占位','110128199111112325','15650000000','冲动消费',5,'消费贷',now()-interval i second);   end while;   commit;END;
-- 将状态打乱UPDATE t_loan_order t SET t.app_status=6 where t.app_id%2=0;

-- 翻页sqlEXPLAINSELECT *FROM t_loan_order tWHERE t.app_status = 5 AND create_time >= DATE('2021-02-28')ORDER BY t.app_id DESCLIMIT pagesize*num, 10

，很正常呀，怎么会慢呢？一定是姿势不对。

先回想下innodb的索引机制。

INNODB聚簇索引

INNODB非聚簇索引

区别:

1，聚簇索引的子节点保存的是主键和记录的映射；

2，非聚簇索引子节点保存的是索引字段和主键的映射；

想了会没问题。

又转到生产环境，EXPLAIN 和测试环境一模一样。

直接执行sql 30秒没有查询出来。

那我先看下数据吧

SELECT * from t_loan_order t ORDER BY t.app_id DESC LIMIT 100;

不对劲，好多数据的创建时间是19年，20年的。

再换种方式查询

SELECT * from t_loan_order t ORDER BY t.create_time DESC LIMIT 100;

主键不是连贯的

再看下表结构

CREATE TABLE `t_loan_order` (  `app_id` bigint(20) NOT NULL COMMENT '工单ID',  .....  PRIMARY KEY (`app_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='工单表';

...... 主键不是自增的。

先把慢的问题解决，调整了下索引。

SELECT *FROM t_loan_order tWHERE t.app_status = 5 AND create_time >= '2021-02-28'ORDER BY t.create_time DESC  -- 将order by 改为LIMIT  90810, 10

上线，翻页没问题了。

再回过头来推导下

当数据查找时顺着索引结构，

按create_time查询，只查询一个或几个连续的数据页就能将数据都找到。

按app_id查询，找到对应的数据，再检索时间，最后全表查了。

后记：

一定一定要保证生产和测试库结构的同步。

敬畏生产。

记一次生产慢sql查询的解决

相关文章：

文章评论