浅析MySql语句优化

package beetlsql;

import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.core.query.Query;
import org.beetl.sql.ext.DebugInterceptor;

import java.io.Serializable;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;

public class TestBeetSql {
    public static void main(String[] args) {
        ConnectionSource source = ConnectionSourceHelper.getSimple("com.mysql.jdbc.Driver", "jdbc:mysql://10.202.4.25:3306/whht?useUnicode=true&characterEncoding=UTF8&useSSL=true", "osms", "osms");
        DBStyle mysql = new MySqlStyle();
        // sql语句放在classpagth的/sql 目录下
        SQLLoader loader = new ClasspathLoader("/sql");
        // 数据库命名跟java命名一样，所以采用DefaultNameConversion，还有一个是UnderlinedNameConversion，下划线风格的，
        NameConversion nc = new MyConversion();
        // 最后，创建一个SQLManager,DebugInterceptor 不是必须的，但可以通过它查看sql执行情况
        SQLManager sqlManager = new SQLManager(mysql, loader, source, nc, new Interceptor[]{new DebugInterceptor()});
        //1.查询大数据量变慢时
        //分页查询是最常用的场景之一，但也通常也是最容易出问题的地方。比如对于下面简单的语句，
        // 一般 想到的办法是在字段上加组合索引。这样条件排序都能有效的利用到索引，性能迅速提升。
        //SELECT * FROM tt_router WHERE STATUS = '1' LIMIT 500000,10;  //1.1
        //但当 LIMIT 子句变成 “LIMIT 500000,10” 时，我只取10条记录为什么还是慢
        //要知道数据库也并不知道第1000000条记录从什么地方开始，即使有索引也需要从头计算一次。
        //在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值(id或者其他字段)当成参数作为查询条件的。SQL 重新设计如下：
        //SELECT * FROM tt_router WHERE STATUS = '1' and id>=500712 ORDER BY id ASC  LIMIT 0,10; //0.002
        //2.关联更新、删除
        //比如下面 UPDATE 语句，MySQL 实际执行的是循环/嵌套子查询（DEPENDENT SUBQUERY)，其执行时间可想而知。
        //在慢查优化都反复强调过 explain 的重要性，但有时候肉眼看不出 explain 结果如何指导优化，这时候还需要有一些其他基础知识的佐助，
        // 甚至需要了解 MySQL 实现原理，如子查询慢查优化。
        //看到 SQL 执行计划中 select_type 字段中出现“ DEPENDENT SUBQUERY”时，要打起精神了！
        //EXPLAIN UPDATE tt_router r SET r.status = '0'
        //WHERE r.sf_waybill IN ( SELECT sf_waybill FROM (SELECT t.sf_waybill FROM tt_router t WHERE t.OPERATOR_ID = 'test89003424') t )

        //优化
        //重写为 JOIN 之后，子查询的选择模式从 DEPENDENT SUBQUERY 变成 DERIVED，执行速度大大加快
        //EXPLAIN UPDATE tt_router r
        //JOIN (SELECT t.sf_waybill FROM tt_router t WHERE t.OPERATOR_ID = 'test89003424') t
        //ON r.sf_waybill = t.sf_waybill
        //SET r.status = '0';

        //3.混合排序**
        //MySQL 不能利用索引进行混合排序。但在某些场景，还是有机会使用特殊方法提升性能的。
        //EXPLAIN SELECT * FROM tt_router ORDER BY STATUS ASC , bar_time DESC LIMIT 0,20; //0.3
        //执行计划显示为全表扫描：
        //由于 STATUS 只有0,1,2三种状态，我们按照下面的方法重写后，提升效率
        //EXPLAIN SELECT * FROM ((SELECT * FROM tt_router WHERE STATUS = '0' ORDER BY  bar_time DESC LIMIT 0,20  ) UNION ALL
        //(SELECT * FROM tt_router WHERE STATUS = '1' ORDER BY bar_time DESC LIMIT 0,20 ) UNION ALL
        //(SELECT * FROM tt_router WHERE STATUS = '2' ORDER BY bar_time DESC LIMIT 0,20 )) t
        //ORDER BY t.STATUS ASC ,t.bar_time DESC LIMIT 20; //0.002

        //4.EXISTS
        //MySQL 对待 EXISTS 子句时，仍然采用嵌套子查询的执行方式
        //EXPLAIN SELECT * FROM tt_router r WHERE EXISTS(SELECT 1  FROM tse_epiemp_abroad_etl e WHERE r.`operator_id` = e.`EPIEMP_CODE`) //2.2 秒
        //去掉 exists 更改为 join，能够避免嵌套子查询
        //EXPLAIN SELECT * FROM tt_router r LEFT JOIN tse_epiemp_abroad_etl e ON r.`operator_id` = e.`EPIEMP_CODE` //0.002秒

        //5.条件下推
        //外部查询条件不能够下推到复杂的视图或子查询的情况有：
        //1、聚合子查询； 2、含有 LIMIT 的子查询； 3、UNION 或 UNION ALL 子查询； 4、输出字段中的子查询；
        //如下面的语句，从执行计划可以看出其条件作用于聚合子查询之后：
        //EXPLAIN SELECT * FROM (SELECT operator_id,COUNT(*) FROM tt_router GROUP BY operator_id) t WHERE t.operator_id = '01331817';
        //确定从语义上查询条件可以直接下推后，重写如下：
        //EXPLAIN SELECT operator_id,COUNT(*) FROM tt_router WHERE operator_id = '01331817'  GROUP BY operator_id;

        //6.提前缩小范围
        //SELECT * FROM tt_router r
        //LEFT JOIN tse_epiemp_abroad_etl e ON r.`operator_id` = e.`EPIEMP_CODE`
        //LEFT JOIN tt_branch b ON r.net_num = b.BRANCH_CODE
        //WHERE r.status = '0'
        //ORDER BY r.bar_time DESC
        //LIMIT 0,20;
        //该SQL语句原意是：先做一系列的左连接，然后排序取前15条记录。从执行计划也可以看出，最后一步估算排序记录数为50万，时间消耗为5秒。
        //优化
        //由于最后 WHERE 条件以及排序均针对最左主表，因此可以先对 my_order 排序提前缩小数据量再做左连接。SQL 重写后如下

        //SELECT * FROM (SELECT * FROM tt_router r WHERE r.status='0' ORDER BY r.bar_time DESC LIMIT 0,20) r
        //LEFT JOIN tse_epiemp_abroad_etl e ON r.`operator_id` = e.`EPIEMP_CODE`
        //LEFT JOIN tt_branch b ON r.net_num = b.BRANCH_CODE
        //ORDER BY r.bar_time DESC LIMIT 0,20;


        //7.中间结果集下推
        //EXPLAIN SELECT * FROM
        //(SELECT * FROM  tse_epiemp_abroad_etl t WHERE STATUS = '1' ORDER BY EPIEMP_CODE LIMIT 20) t
        //LEFT JOIN
        //(SELECT * FROM tt_branch b GROUP BY BRANCH_CODE) c
        //ON t.GC_ORG_NETCODE = c.BRANCH_CODE
        //tt_branch 为全表扫描

        //修改
        //SELECT * FROM
        //(SELECT * FROM  tse_epiemp_abroad_etl t WHERE STATUS = '1' ORDER BY EPIEMP_CODE LIMIT 20) t
        //LEFT JOIN
        //(SELECT BRANCH_CODE FROM tt_branch b,(SELECT GC_ORG_NETCODE FROM  tse_epiemp_abroad_etl t WHERE STATUS = '1' ORDER BY EPIEMP_CODE LIMIT 20) c
        //WHERE b.BRANCH_CODE = c.GC_ORG_NETCODE
        //GROUP BY b.BRANCH_CODE ) c
        //ON t.GC_ORG_NETCODE = c.BRANCH_CODE

        //案例，对查询变慢时优化
        Map<String, Object> param = new HashMap<>();
        param.put("order", "id asc");
        param.put("id", 0);
        recursion(param, sqlManager);
    }

    private static void recursion(Map<String, Object> param, SQLManager sqlManager) {
        List<TtRouter> ttRouters = search(sqlManager, param, 10);
        ttRouters.stream().map(TtRouter::getId).forEach(System.out::println);
        TtRouter ttRouter = ttRouters.get(ttRouters.size() - 1);
        param.put("id", ttRouter.getId());
        recursion(param, sqlManager);
    }

    private static List<TtRouter> search(SQLManager sqlManager, Map<String, Object> param, int pageSize) {
        return query(q -> {
            if (param.get("order") != null) {
                q.orderBy(param.get("order").toString());
            }
            if (param.get("id") != null) {
                q.andGreat("id", param.get("id").toString());
            }
            q.limit(1, pageSize);
        }, sqlManager, TtRouter.class).select();
    }

    private static <T> Query<T> query(Consumer<Query<T>> queryConsumer, SQLManager sqlManager, Class<T> clazz) {
        Query<T> query = sqlManager.query(clazz);
        if (queryConsumer != null) {
            queryConsumer.accept(query);
        }
        return query;
    }
}

class TtRouter implements Serializable {
    private Integer id;
    private String netNum;
    private String operatorId;
    //发送状态（0，成功 1，失败 2，待发送）
    private Integer status;
    private Date barDate;
    private Date barTime;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getNetNum() {
        return netNum;
    }

    public void setNetNum(String netNum) {
        this.netNum = netNum;
    }

    public String getOperatorId() {
        return operatorId;
    }

    public void setOperatorId(String operatorId) {
        this.operatorId = operatorId;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    public Date getBarDate() {
        return barDate;
    }

    public void setBarDate(Date barDate) {
        this.barDate = barDate;
    }

    public Date getBarTime() {
        return barTime;
    }

    public void setBarTime(Date barTime) {
        this.barTime = barTime;
    }
}