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<String> 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<String, Object> 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配置

（编辑：鹰潭站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!