阿里慢SQL治理5大经典案例

数据库118

菜鸟供应链金融慢sql治理已经有一段时间，自己负责的应用持续很长时间没有慢sql告警，现阶段在推进组内其他成员治理应用慢sql。这里把治理过程中的一些实践拿出来分享下。

一、全表扫描

1、案例

SELECT count(*) AS tmp_count FROM (

SELECT * FROM XXX_rules WHERE 1 = 1 ORDER BY gmt_create DESC ) a

2、溯源

在分页查询治理的文章里已经介绍过我们系统旧的分页查询逻辑，上面的查询sql明显就是分页查询获取总记录数，通过XXX_rules表的分页查询接口溯源，找到发起调用的页面是我们小二后台的一个操作商家准入的页面，页面打开后直接调用分页查询接口，除了分页参数，不传入其他任何查询参数，导致扫描全表。

3、分析

灵魂拷问：为什么要扫描全表？全表数据展示到页面，花里胡哨的数据有用吗？

调研：和经常使用这个页面的运营聊后了解到，打开页面查询出的全表数据对运营是没有用的，他们根本不看这些数据。运营的操作习惯是拿到商家id，在页面查询框中输入商家id，查到商家数据后进行操作。

4、解决方案

由此优化方案就很明朗了：打开页面时不直接查询全量数据，等运营输入商家id后，将商家id作为参数进行查询。XXX_rules表中，商家id这一常用查询条件设置为索引，再结合分页查询优化，全表扫描慢sql得以解决。

优化后的小二后台页面如下：

阿里慢SQL治理5大经典案例

打开页面时未查询任何数据，查询条件商家账户为必填项。

优化后的sql为:

SELECT count(*) AS tmp_count FROM (

SELECT * FROM xxx_rules WHERE 1 = 1 AND rule_value = '2928597xxx' ) a

执行EXPLAIN得到结果如下：

阿里慢SQL治理5大经典案例

可以看到命中了索引，扫描行数为3，查询速度明显提高。

5、思考

扫描全表治理简单来说就是加入查询条件，命中索引，去除全表扫描查询，虽然有些粗暴，但并不是没有道理。实际业务场景中，很少有要扫描全表获取全部数据的情况，限制调用上游必须传入查询条件，且该查询条件能命中索引，能很大程度上避免慢sql。

另外，再引申下，XXX_rules初始的用意是准入表，记录金融货主维度的准入情况，最多也就几千条数据，但是很多同事将这张表理解为规则表，写入很多业务相关规则，导致这个表膨胀到一百多万条数据，表不clean了。这就涉及到数据表的设计使用，明确表的使用规范，不乱写入数据，能给后期维护带来很大的便利。

二、索引混乱

1、示例

阿里慢SQL治理5大经典案例

2、分析

除了时间、操作人字段，XXX_rules表就rule_name、rule_value、status、product_code四个字段，表的索引对这四个字段做各种排列组合。存在如下问题：

1）rule_name离散度不高，放在索引首位不合适；

2）前三个索引重合度很高；

显然是对索引的命中规则不够了解。XXX_rules表很多业务有定时任务对其写入删除，索引多、混乱，对性能有很大的影响。

高性能的索引有哪些，再来回顾下：

①独立的列：索引列不能是表达式的一部分；

②选择区分度高的列作为索引；

③选择合适的索引列顺序：将选择性高的索引列放在最前列；

④覆盖索引：查询的列均在索引中，不需要回查聚簇索引；

⑤使用索引扫描来做排序;

⑥在遵守最左前缀的原则下，尽量扩展索引，而不是创建索引。

但凡记得第③和⑥规则，也不至于把索引建成这样。

3、治理

对索引进行整合如下：

阿里慢SQL治理5大经典案例

系统中有很多任务拉取整个产品下的准入记录，然后进行处理，所以将区分度较高的product_code放在索引首位，然后添加rule_name、status字段到索引里，进一步过滤数据，减少扫描行数，避免慢sql。针对常用的rule_value查询条件，可以命中UK，因此不用单独建立索引。

三、非必要排序

1、问题描述

很多业务逻辑中，需要拉取满足某个条件的记录列表，查询的sql语句带有order by，记录比较多的情况，排序代价往往很大，但是查询出来的记录是否有序对业务逻辑没有影响，比如分页治理里讨论的count语句，只需要统计条数，order by对条数没有影响，再比如查出记录列表后，不依赖记录的顺序遍历列表处理数据，这时候order by多此一举。

2、解决方案

查询sql无limit语句，且业务处理逻辑不依赖于order by后列表记录的顺序，则去除查询sql中的order by语句。

四、粗粒度查询

1、问题描述

业务中有很多定时任务，扫描某个表中某个产品下所有数据，对数据进行处理，比如：

SELECT * FROM XXX_rules

WHERE rule_name = 'apf_distributors'

AND status = '00'

AND product_code = 'ADVANCE'

三个查询条件都是区分度不高的列，查出的数据有27W条，加索引意义也不大。

2、分析

实际业务量没那么大，顶多几千条数据，表里的数据是从上游同步过来的，最好的办法是让上游精简数据，但是由于业务太久远，找上游的人维护难度太大，因此只能想其他的办法。

这个定时任务目的是拉出XXX_rules表的某些产品下的数据，和另一张表数据对比，更新有差异的数据。每天凌晨处理，对时效性没有很高的要求，因此，能不能转移任务处理的地方，不在本应用机器上实时处理那么多条数据？

3、解决方案

数据是离线任务odps同步过来的，首先想到的就是dataWork数据处理平台。

建立数据对比任务，将定时任务做的数据对比逻辑放到dataWork上用sql实现，每天差异数据最多几百条，且结果集含有区分度很高的列，将差异数据写入odps表，再将数据回流到idb。

新建定时任务，通过回流回来的差异数据中区分度高的列作为查询条件查询XXX_rules，更新XXX_rules，解决了慢sql问题。

这个方法的前提是对数据实效性要求不高，且离线产出的结果集很小。

五、OR导致索引失效

1、案例

SELECT count(*)

FROM XXX_level_report

WHERE 1 = 1

AND EXISTS (

SELECT 1

FROM XXX_white_list t

WHERE (t.biz_id = customer_id

OR customer_id LIKE CONCAT(t.biz_id, '@%'))

AND t.status = 1

AND (t.start_time

Original: https://www.cnblogs.com/88223100/p/5_Classic_Cases_of_Alibaba_Slow_SQL_Governance.html
Author: 古道轻风
Title: 阿里慢SQL治理5大经典案例

相关阅读1

Title: SpringBoot邮件报警

SpringBoot邮件报警

一、介绍

邮件报警，大体思路就是收集服务器发生的异常发送到邮箱，做到服务器出问题第一时间知道，当然要是不关注邮箱当我没说

（1）、引入依赖

    <dependency>
            <groupid>org.springframework.boot</groupid>
            <artifactid>spring-boot-starter-mail</artifactid>
        </dependency>

二、配置邮箱

（1）、注册两个邮箱账号（一个用来发送，一个用来接收）

（2）、找到邮箱设置打开协议

阿里慢SQL治理5大经典案例

三、配置yml

非阿里云下方的配置文件需要授权码（不了解可以百度xx邮箱怎么获取授权码）

spring:
  mail:
    host:  #发送邮件服务器
    username:  #发送邮件的邮箱地址
    password:    #(如果是阿里云)阿里云邮箱没有qq邮箱所谓的授权码,所以此处填的是阿里云的登陆密码
    from:   # 发送邮件的地址，和上面username一致
    to:     # 接受邮件的地址
    properties.mail.smtp.starttls.enable: true
    properties.mail.smtp.starttls.required: true
    properties.mail.smtp.ssl.enable: true
    default-encoding: utf-8

四、编写代码

（1）、编写发送邮件工具类（EmailUtils）

package cn.zz.practice.util;

import cn.zz.practice.config.MailProperties;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.mail.SimpleMailMessage;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;

@Component
@Slf4j
public class EmailUtils {

    @Resource
    private JavaMailSender sender;
    @Resource
    private MailProperties mailProperties;

    @Value("${spring.mail.from}")
    private String from;
//    @Value("${spring.profiles.active}")
//    private String active;
    @Value("${spring.mail.to}")
    private String to;

    /**
     * 发送纯文本的简单邮件
     *
     * @param content 发送的文本内容
     */
    public void sendSimpleMail(String content) {
//        //如果是开发环境,直接返回,不发送邮件
//        if (StringUtils.equals(active,"dev")) {
//            return;
//        }
        //邮件主题
        String subject = "~~~~~~~~~未知异常~~~~~~~~~";
        SimpleMailMessage message = new SimpleMailMessage();
        //发件人邮箱
        message.setFrom(this.mailProperties.getFrom());
        //要发送的邮箱
        message.setTo(this.mailProperties.getTo());
        message.setSubject(subject);
        message.setText(content);
        try {
            sender.send(message);
            log.info("简单邮件已经发送。");
        } catch (Exception e) {
            log.error("发送简单邮件时发生异常！", e);
        }
    }
}

（2）、拦截所有异常并发送邮件

package cn.zz.practice.aop;

import cn.zz.practice.util.EmailUtils;
import lombok.extern.slf4j.Slf4j;
import net.minidev.json.JSONObject;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.StringWriter;

@Slf4j
@RestControllerAdvice
public class WebRestControllerAdvice {

    @Resource
    private EmailUtils mailUtil;

    @ExceptionHandler(Exception.class)
    public String handleRuntimeException(Exception runtimeException, HttpServletRequest req) {
        log.error(runtimeException.getMessage());
        StringWriter sw = new StringWriter();
        PrintWriter pw = new PrintWriter(sw);
        runtimeException.printStackTrace(pw);
        String body = this.readBody(req);
        String content = String.format("url:%s\n请求参数:%s\n请求body:%s\n栈信息:%s", req.getRequestURL(), JSONObject.toJSONString(req.getParameterMap()), body, sw.toString());
        this.mailUtil.sendSimpleMail(content);
        return "服务器错误";
    }

    public String readBody(HttpServletRequest request) {

        BufferedReader br = null;
        StringBuilder sb = new StringBuilder("");
        try {
            br = request.getReader();
            String str;
            while ((str = br.readLine()) != null) {
                sb.append(str);
            }
            br.close();
        } catch (IOException e) {
            log.error("[严重异常]读取请求body异常", e);
        } finally {
            if (null != br) {
                try {
                    br.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return sb.toString();
    }

}

ExceptionHandler 的使用场景就是在 Controller 中捕获异常，全局统一处理，而不是在每个 handler 中都进行繁琐的异常捕获操作，优点就是代码整洁。
ExceptionHandler 异常处理过程大体为：执行 handler 方法如果抛出了异常，就根据异常类型查找到对应的异常处理方法，然后执行对应的方法

（3）、编写测试crontroller写个异常

@RestController
@RequestMapping("/demoController")
public class DemoController {
    @GetMapping
    public void sb(){
        int i= 1/0;
    }
}

然后就会接到邮件
阿里慢SQL治理5大经典案例

迷途者寻影而行

Original: https://www.cnblogs.com/pkkyh/p/14631405.html
Author: 迷途者寻影而行
Title: SpringBoot邮件报警

相关阅读2

Title: [Mysql]Ubuntu如何安装Mysql+启用远程连接[完整版]

唉。下面是我花了不知道多少个小时踩过的所有坑总结出来的血泪史，希望能帮你们少踩一些坑吧，正常来讲一步一步下来就不会出现任何问题了。

背景

用的是百度云的云服务器（其他云服务器同理），系统是Ubuntu 20.04 LTS，Mysql版本8.0+，需求是在Windows上开发，可以随时远程连接读写服务器上的Mysql

建立到服务器的远程连接

用ssh客户端或者云服务器厂家提供的网页版控制台都行，只要你能连上服务器就行
阿里慢SQL治理5大经典案例

顺便私心推荐一个好看又好用的ssh客户端：NextSSH

用apt-get安装mysql

先更新一下apt仓库：

sudo apt-get update

顺便说一下，因为不知道你们用的都是什么账户，我也搞不清楚哪些指令权限要求比较高，所以我所有指令都加 sudo了，这样不管是谁复制粘贴都能直接用，不会出现权限问题。
然后安装mysql-server:

sudo apt-get install mysql-server -y

到这一步其实mysql就已经安完了并且自动启动了，可以看一下：

sudo service mysql status

阿里慢SQL治理5大经典案例

设置root密码

此时mysql的root账户没有设置密码，可以直接用 mysql指令登录：
阿里慢SQL治理5大经典案例

设置一下root的密码(mynewpassword部分改成你自己要设置的密码)：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'mynewpassword';

阿里慢SQL治理5大经典案例

退出，输入 mysql指令发现不能直接登录了：
阿里慢SQL治理5大经典案例

目前为止可以直接在服务器上用mysql了。

编辑配置文件监听远程连接

默认情况下，MySQL 数据库仅监听本地连接，如果想让外网远程连接到数据库，我们需要修改配置文件，让 MySQL 可以监听远程固定 ip 或者监听所有远程 ip。
这里需要使用一个命令行文本编辑器，我用的vim所以就教一下vim，你们要是会nano或者别的自然知道该怎么弄，要是听不懂就照我的来。安装vim：

sudo apt-get install vim -y

然后用vim打开 mysqld.cnf配置文件：

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

找到 bind-address = 127.0.0.1这一行：
阿里慢SQL治理5大经典案例

这个值是 127.0.0.1的时候只监听本地连接，改成 0.0.0.0可以监听所有连接，或者也可以改成仅允许指定ip连接都可以。
现在vim是阅读模式，按一下 i进入编辑模式，然后用上下左右键定位到这行（最下面显示INSERT的时候表明处于编辑模式，按Esc可退出返回到阅读模式）：
阿里慢SQL治理5大经典案例

改完之后按Esc退出编辑模式，然后输入 :wq保存退出。（若输入 :q则退出但不保存）
重启mysql service使刚才的修改生效：

sudo service mysql restart

允许root账号使用远程连接

mysql默认只允许root账号在本地使用，需要修改一下允许远程使用root账号（没试过其他账号的情况，但原理一致）。先登录mysql:
阿里慢SQL治理5大经典案例

mysql -u root -p

输入密码，登录。
然后选择 mysql数据库：

use mysql;

阿里慢SQL治理5大经典案例

查看账号的主机权限：

select user, host from user;

阿里慢SQL治理5大经典案例

host处为 localhost时只允许本地使用，改成 %即可远程使用：

update user set host='%' where user='root';

阿里慢SQL治理5大经典案例

刷新权限：

FLUSH PRIVILEGES;

退出mysql。

检查ubuntu自带的防火墙状态

sudo ufw status

阿里慢SQL治理5大经典案例

如果是 inactive说明防火墙没开，那就不用管了。防火墙是干嘛的呢，我自己的理解就是，如果开了防火墙，那服务器上所有端口都是默认禁止连接的，只有你允许的端口才允许连接，类似于这种：
阿里慢SQL治理5大经典案例

所以如果防火墙开了，那要么把防火墙直接关了：

sudo ufw disable

要么添加一条规则让防火墙放行3306端口(mysql的默认端口)：

sudo ufw allow 3306

检查云服务器厂商的防火墙状态

打开云服务器的后台管理页面，找到防火墙：
阿里慢SQL治理5大经典案例

云服务器厂商默认只开启几个最常用的端口，其他端口都是默认关闭的，所以也要在这里添加一条规则放行3306端口：
阿里慢SQL治理5大经典案例

测试连接

随便找个数据库管理的软件测试一下：
阿里慢SQL治理5大经典案例

就连上了。常见的问题应该都提到了，如果还是连不上那你们再想想办法吧。

Original: https://www.cnblogs.com/m1ds/p/16386828.html
Author: m1ds
Title: [Mysql]Ubuntu如何安装Mysql+启用远程连接[完整版]

相关阅读3

Title: 手把手教你分析MySQL查询性能瓶颈，包教包会

当一条SQL执行较慢，需要分析性能瓶颈，到底慢在哪？

我们一般会使用 Explain查看其执行计划，从执行计划中得知这条SQL有没有使用索引？使用了哪个索引？

阿里慢SQL治理5大经典案例

但是执行计划显示内容不够详细，如果显示用到了某个索引，查询依然很慢，我们就无法得知具体是哪一步比较耗时？

好在MySQL提供一个SQL性能分析工具 — Profile。

Profile 可以帮助我们分析SQL性能瓶颈和资源消耗情况。

1. 查看Profile配置

show variables like '%profil%';

阿里慢SQL治理5大经典案例

have_profiling 表示是否支持profile功能，YES表示支持
profiling 表示是否开启profile功能，ON开启，OFF关闭，默认是关闭状态
profiling_history_size 表示保存最近15条历史数据

2. 开启Profile功能

set profiling=1;

阿里慢SQL治理5大经典案例

注意：修改配置，只对当前会话生效，会话关闭， Profile历史信息被清空。

3. 使用Profile

先造点数据，创建一张用户表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` tinyint NOT NULL  DEFAULT 0 NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

执行一条耗时SQL：

select * from user order by name;

下面轮到主角 Profile出场了。

我们执行的所有SQL语句都会被记录到 Profile里面，包括执行失败的SQL语句。

可以使用 show profiles命令查看：

阿里慢SQL治理5大经典案例

输出参数详解：

Query_ID 表示自动分配的查询ID，顺序递增。
Duration 表示SQL语句执行耗时
Query 表示SQL语句内容

然后，我们再使用 Query_ID去 Profile中查看具体每一步的耗时情况：

show profile for query 1;

阿里慢SQL治理5大经典案例

可以清楚的看到耗时主要花在 创建排序索引（Creating sort index）上面。

再试一条SQL：

select distinct name from user;

阿里慢SQL治理5大经典案例

这次的耗时主要花在了，创建临时文件、拷贝文件到磁盘、发送数据、删除临时表上面。

由此，可以得知 distinct函数会创建临时文件，提醒我们建索引。

我们还可以扩展一下这条分析语句，查看一下cpu和block io的使用情况：

show profile cpu,block io for query 2;

阿里慢SQL治理5大经典案例

另外，其实所有 Profile历史数据都被记录在 information_schema.profiling表中，我们也可以查询表得到结果：

select * from information_schema.profiling where Query_ID=2;

阿里慢SQL治理5大经典案例

以上数据都是基于 MySQL5.7版本，在 MySQL8.0版本的输出结果字段有些变化。

另外，细心的你应该发现了，在我们每执行完一条SQL，都显示了一条 warning信息，我们查看一下具体的 warning信息：

show warnings;

阿里慢SQL治理5大经典案例

意思就是， Profile工具将来有可能被删除，不建议继续使用了。

好吧，下篇文章我们再一块学习一下MySQL提供的，用来替换 Profile的最新性能瓶颈分析工具，使用更便捷。

文章持续更新，可以微信搜一搜「 一灯架构 」第一时间阅读更多技术干货。
阿里慢SQL治理5大经典案例

Original: https://www.cnblogs.com/yidengjiagou/p/16587437.html
Author: 一灯架构
Title: 手把手教你分析MySQL查询性能瓶颈，包教包会