MySQL之SQL优化实战记录

MySQL之SQL优化实战记录

背景

本次SQL优化是针对javaweb中的表格查询做的。

部分网络架构图

业务简单说明

N个机台将业务数据发送至服务器，服务器程序将数据入库至MySQL数据库。服务器中的javaweb程序将数据展示到网页上供用户查看。

原数据库设计

windows单机主从分离已分表分库，按年分库，按天分表每张表大概20w左右的数据

原查询效率

3天数据查询70-80s

目标

3-5s

业务缺陷

无法使用sql分页，只能用java做分页。

问题排查

前台慢 or 后台慢

如果你配置了druid，可在druid页面中直接查看sql执行时间和uri请求时间在后台代码中用System.currentTimeMillis计算时间差。

结论 ： 后台慢，且查询sql慢

sql有什么问题

sql拼接过长，达到了3000行，有的甚至到8000行，大多都是union all的操作，且有不必要的嵌套查询和查询了不必要的字段利用explain查看执行计划，where条件中除时间外只有一个字段用到了索引

备注 ： 因优化完了，之前的sql实在找不到了，这里只能YY了。

查询优化

去除不必要的字段

效果没那么明显

去除不必要的嵌套查询

效果没那么明显

分解sql

将union all的操作分解，例如(一个union all的sql也很长)

select aa from bb_2018_10_01 left join ... on .. left join .. on .. where .. union all select aa from bb_2018_10_02 left join ... on .. left join .. on .. where .. union all select aa from bb_2018_10_03 left join ... on .. left join .. on .. where .. union all select aa from bb_2018_10_04 left join ... on .. left join .. on .. where ..

将如上sql分解成若干个sql去执行，最终汇总数据，***快了20s左右。

select aa from bb_2018_10_01 left join ... on .. left join .. on .. where ..

将分解的sql异步执行

利用java异步编程的操作，将分解的sql异步执行并最终汇总数据。这里用到了CountDownLatch和ExecutorService，示例代码如下：

// 获取时间段所有天数        List days = MyDateUtils.getDays(requestParams.getStartTime(), requestParams.getEndTime());        // 天数长度        int length = days.size();        // 初始化合并集合，并指定大小，防止数组越界        List<你想要的数据类型> list = Lists.newArrayListWithCapacity(length);        // 初始化线程池        ExecutorService pool = Executors.newFixedThreadPool(length);        // 初始化计数器        CountDownLatch latch = new CountDownLatch(length);        // 查询每天的时间并合并        for (String day : days) {            Map param = Maps.newHashMap();            // param 组装查询条件             pool.submit(new Runnable() {                @Override                public void run() {                    try {                        // mybatis查询sql                        // 将结果汇总                        list.addAll(查询结果);                    } catch (Exception e) {                        logger.error("getTime异常", e);                    } finally {                        latch.countDown();                    }                }            });        }          try {            // 等待所有查询结束            latch.await();        } catch (InterruptedException e) {            e.printStackTrace();        }         // list为汇总集合        // 如果有必要，可以组装下你想要的业务数据，计算什么的，如果没有就没了

结果又快了20-30s

优化MySQL配置

以下是我的配置示例。加了skip-name-resolve,快了4-5s。其他配置自行断定

被批准多久. # InnoDB在其拥有的锁表中自动检测事务死锁并且回滚事务. # 如果你使用 LOCK TABLES 指令, 或者在同样事务中使用除了InnoDB以外的其他事务安全的存储引擎 # 那么一个死锁可能发生而InnoDB无法注意到. # 这种情况下这个timeout值对于解决这种问题就非常有帮助. innodb_lock_wait_timeout=30# 开启定时event_scheduler=ON

根据业务，再加上筛选条件

快4-5s

将where条件中除时间条件外的字段建立联合索引

效果没那么明显

将where条件中索引条件使用inner join的方式去关联

针对这条，我自身觉得很诧异。原sql,b为索引

select aa from bb_2018_10_02 left join ... on .. left join .. on .. where b = 'xxx'

应该之前有union all，union all是一个一个的执行，***汇总的结果。修改为

select aa from bb_2018_10_02 left join ... on .. left join .. on .. inner join (     select 'xxx1' as b2     union all     select 'xxx2' as b2     union all     select 'xxx3' as b2     union all     select 'xxx3' as b2 ) t on b = t.b2

结果快了3-4s

性能瓶颈

根据以上操作，3天查询效率已经达到了8s左右，再也快不了了。查看mysql的cpu使用率和内存使用率都不高，到底为什么查这么慢了，3天最多才60w数据，关联的也都是一些字典表，不至于如此。继续根据网上提供的资料，一系列骚操作，基本没用，没辙。

环境对比

因分析过sql优化已经ok了，试想是不是磁盘读写问题。将优化过的程序，分别部署于不同的现场环境。一个有ssd，一个没有ssd。发现查询效率悬殊。用软件检测过发现ssd读写速度在700-800M/s,普通机械硬盘读写在70-80M/s。

优化结果及结论

优化结果：达到预期。优化结论：sql优化不仅仅是对sql本身的优化，还取决于本身硬件条件，其他应用的影响，外加自身代码的优化。

小结

优化的过程是自身的一个历练和考验，珍惜这种机会，不做只写业务代码的程序员。希望以上可以有助于你的思考，不足之处望指正。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。