最新消息:www.yxkong.com也可以访问

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

mysql/mariadb yxkong 127浏览 0评论

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

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

  1. 数据字段多,索引也多;

  2. 随着数据的流转,数据一直在更新;以下数据是参考测试表结构的模拟;

-- 创建表,多加了几个字段为了占用空间填充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查询的解决

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址