慢查询

慢查询，顾名思义，执行很慢的查询。有多慢？超过 long_query_time 参数设定的时间阈值（默认10s），就被认为是慢的，是需要优化的。慢查询被记录在慢查询日志里。

当然，在实际生产应用环境中，不同的公司对慢查询的时间阈值有不同的规定，比如博主曾在的公司就规定SQL查询时间大于 1s 就算慢查询，需要优化；也有一些朋友所在公司设定为 3s、5s；这些设定都是根据公司实际业务及实际用户的忍耐度来评估设定，并没有一个统一的标准。

long_query_time设定值可以通过以下命令进行查看：

show variables like "%long_query_time%";

案例说明

为了避免暴露公司产品及数据库，本篇文章博主将模拟本次慢SQL场景重新设计表结构及数据，尽可能还原实际场景并完整记录优化过程。

假设：张三开了10家酒店；酒店房间有电脑，每台机器都需要付费才可开机，现需统计用户电脑消费时长、金额等记录；

优化步骤

1. 首先将生产SQL拷贝到本地；
2. 本地执行SQL并分析理解相关业务场景；
3. 查看SQL的执行计划；
4. 打开表设计对SQL进行分析；
5. 比较生产与本地SQL执行计划是否有出入（不同数据下会不同）；
6. 将相关字段值替换成本地数据以达到生产环境执行计划达到同等效果；
7. 在测试环境对相关字段建立索引进行优化并查看优化后执行计划；
8. 针对相关业务场景对SQL进行优化；
9. 梳理当前业务并确认是否可以进行一定优化；
10. 线上验证；

表结构

主表

CREATE TABLE `t_main` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `shop_id` int NOT NULL COMMENT '店id',
  `order_no` varchar(20) DEFAULT NULL COMMENT '单号',
  `computer_name` varchar(20) DEFAULT NULL,
  `computer_id` int DEFAULT NULL,
  `checkin_no` varchar(36) DEFAULT NULL COMMENT '入住号',
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `close_time` datetime DEFAULT NULL COMMENT '下机时间',
  `status` int DEFAULT '0' COMMENT '状态 -1未生效 0在线 1下机',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_checkin_no` (`checkin_no`) USING BTREE,
  KEY `idx_shop_id` (`shop_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

子表

CREATE TABLE `t_detail` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `shop_id` int NOT NULL COMMENT '店id',
  `order_no` varchar(20) DEFAULT NULL COMMENT '单号',
  `pay_no` varchar(20) DEFAULT NULL COMMENT '支付号',
  `computer_name` varchar(20) DEFAULT NULL,
  `computer_id` int DEFAULT NULL,
  `checkin_no` varchar(36) DEFAULT NULL COMMENT '入住号',
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `close_time` datetime DEFAULT NULL COMMENT '下机时间',
  `type` int DEFAULT NULL COMMENT '业务类型:1-上机 2-续时 3-换机 4:下机... ',
  `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
  `status` int DEFAULT '0' COMMENT '状态 -1未生效 0已生效',
  `time` int DEFAULT NULL COMMENT '上机时长',
  `amount` int DEFAULT NULL COMMENT '消费金额',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_checkin_no` (`checkin_no`) USING BTREE,
  KEY `idx_shop_id` (`shop_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

SQL

SELECT s.name, s.id shopId, r.pay_time payTime, a.start_time startTime, a.close_time closeTime,  max(rc.time) time, SUM(rc.amount) amount, r.order_no orderNo,r.checkin_no checkinNo
FROM t_main a
INNER JOIN (
    SELECT MAX(id) id, order_no FROM t_detail 
    WHERE shop_id in (1,2,3,4,5) AND `status` != -1 AND type in (1,2) GROUP BY order_no
) rid ON rid.order_no = a.order_no    
LEFT JOIN (
    SELECT SUM(IF(a.time IS NULL, 0, a.time)) time, 
        SUM(IF(a.amount IS NULL, 0, a.amount)) amount, a.order_no, a.checkin_no
    FROM t_detail a
    INNER JOIN t_main b ON a.order_no = b.order_no
        WHERE a.shop_id IN (1,2,3,4,5) AND a.`status` != -1 AND b.`status` != -1 AND b.shop_id in (1,2,3,4,5) GROUP BY order_no
) rc ON rc.order_no = a.order_no
LEFT JOIN t_detail r ON a.order_no = r.order_no AND r.id=rid.id    
LEFT JOIN tbl_shop s ON r.shop_id=s.id    
    WHERE r.shop_id IN (1,2,3,4,5) AND r.status!=-1 AND a.shop_id IN (1,2,3,4,5) AND a.status!=-1
    GROUP BY a.checkin_no 
    ORDER BY a.start_time desc LIMIT 0, 10;

优化

首先看一下SQLl执行计划

从SQL看及执行计划看，MySQL分析可能走idx_shop_id索引，但显然最后索引没生效（此处MySql认为全表扫描比索引更快）；经查询发现，此功能实际使用的用户不多，导致库内数据基本上都是张三家的，由此导致子查询中产生了全表扫描！！！且分组条件未加索引；先给表加上索引：

ALTER TABLE t_main ADD INDEX `idx_order_no` (`order_no`) USING BTREE;
ALTER TABLE t_detail ADD INDEX `idx_order_no` (`order_no`) USING BTREE;

再看一下SQL的执行计划

发现还有一个全表扫描和一个子表全索引扫描，但SQL执行时间优化明显，首次查询已到2s以内，MySql缓存查询时间500ms以下，此时已经可以接受了；但从SQL角度还可以进一步优化：

分析一下SQL，产品要的数据是按照主表入住房间（入住号）来统计；而SQL中对子表进行了全索引扫描，几乎是查询了全表的数据；

我们首先想到的是对主表条件进行分页，然后关联查询子表相应数据进行统计

SELECT s.name, s.id shopId, r.pay_time payTime, a.start_time startTime, a.close_time closeTime,  max(rc.time) time, SUM(rc.amount) amount, r.order_no orderNo,r.checkin_no checkinNo
FROM t_main a
INNER JOIN (
    SELECT MAX(r.id) id, r.order_no FROM (
        SELECT order_no FROM t_main WHERE checkin_no in (
            SELECT t.checkin_no FROM (SELECT a.checkin_no checkin_no FROM t_main a    
            WHERE a.shop_id IN (1,2,3,4,5) AND a.status!=-1
            GROUP BY a.checkin_no ORDER BY a.start_time desc LIMIT 0, 10) as t
        )
    ) a LEFT JOIN t_detail r on a.order_no = r.order_no
        WHERE r.`status` != -1 AND r.type in (1,2) GROUP BY r.order_no
) rid ON rid.order_no = a.order_no    
LEFT JOIN (
    SELECT SUM(IF(a.time IS NULL, 0, a.time)) time, 
    SUM(IF(a.amount IS NULL, 0, a.amount)) amount, a.order_no, a.checkin_no
    FROM t_detail a
    INNER JOIN 
    (
        SELECT order_no FROM t_main WHERE checkin_no in (
            SELECT t.checkin_no FROM (SELECT a.checkin_no checkin_no FROM t_main a    
            WHERE a.shop_id IN (1,2,3,4,5) AND a.status!=-1
            GROUP BY a.checkin_no ORDER BY a.start_time desc LIMIT 0, 10) as t
        )
    ) b ON a.order_no = b.order_no
        WHERE a.`status` != -1
        GROUP BY a.order_no
) rc ON rc.order_no = a.order_no
LEFT JOIN t_detail r ON a.order_no = r.order_no AND r.id=rid.id 
LEFT JOIN tbl_shop s ON r.shop_id=s.id    
    WHERE r.shop_id IN (1,2,3,4,5) AND r.status!=-1 AND a.shop_id IN (1,2,3,4,5) AND a.status!=-1
    GROUP BY a.checkin_no ORDER BY a.start_time desc LIMIT 0, 10;

以下，我们来看一下优化后SQL执行计划

全表扫描已经没有了，子表的全索引扫描也被改为扫描主表的索引。

当然，这样优化从当前数量级来看，起到了一定的作用，经实际验证发现，SQL查询时间缩小到上步的1/10左右；

小结

博主本次优化的SQL是基于mysql8+数据库，数据库配置8核32G，产品属于SASS云平台，而本次出现的几个慢SQL由于未正确建立索引导致查询时常超过30s，而接口在多个慢SQL的存在下使得页面完全无法展示，而用户在消耗掉其为数不多的耐心下进行了多次点击，导致数据库越来越卡。

在建立索引后，SQL在当前业务场景下执行时间已经缩小到2s以内，可以满足生产需求，虽然此时有一定的优化空间，但并非一定需要进行改动，而且随着数据量的改变，也会产生不同的问题，所以及时关注满足当前业务需求即可！

另外，除了处理SQL外，我们也可以从其他方面进行优化，比如：限流、页面loding加载等，还可以从业务角度出发，通过调整业务后更新SQL达到优化的目的。无论如何优化，只要最终满足业务需求即可！！！