一个遗留项目的SQL优化实战录

CodeSheep

来源丨xiaozhutefannao

my.oschina.net/xiaozhutefannao/blog/2243432

首先说在前面：数据库很重要，尤其以后工作做后端开发，数据库当家呀，确实得花精力好好学

实战背景

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

部分网络架构图

业务简单说明

N个机台将业务数据发送至服务器，服务器程序将数据入库至MySQL数据库。服务器中的Java Web程序将数据展示到网页上供用户查看，怎么样，简单吧

原数据库设计

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

原查询效率

3天数据查询 70 - 80s

目标

3-5s

业务缺陷

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

问题排查

前台慢 or 后台慢

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

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

sql有什么问题

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

SQL优化过程

1、去除不必要的字段

效果没那么明显

2、去除不必要的嵌套查询

效果没那么明显

3、分解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 ..

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

4、将分解的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

5、优化MySQL配置

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

[client]
port=3306
[mysql]
no-beep
default-character-set=utf8
[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
slave-skip-errors=all #跳过所有错误
skip-name-resolve

port=3306
datadir="D:/mysql-slave/data"
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

log-output=FILE
general-log=0
general_log_file="WINDOWS-8E8V2OD.log"
slow-query-log=1
slow_query_log_file="WINDOWS-8E8V2OD-slow.log"
long_query_time=10

# Binary Logging.
# log-bin

# Error Logging.
log-error="WINDOWS-8E8V2OD.err"


# 整个数据库最大连接（用户）数
max_connections=1000
# 每个客户端连接最大的错误允许数量
max_connect_errors=100
# 表描述符缓存大小，可减少文件打开/关闭次数
table_open_cache=2000
# 服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB字段一起工作时相当必要)
# 每个连接独立的大小.大小动态增加
max_allowed_packet=64M
# 在排序发生时由每个线程分配
sort_buffer_size=8M
# 当全联合发生时,在每个线程中分配
join_buffer_size=8M
# cache中保留多少线程用于重用
thread_cache_size=128
# 此允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量.
thread_concurrency=64
# 查询缓存
query_cache_size=128M
# 只有小于此设定值的结果才会被缓冲
# 此设置用来保护查询缓冲,防止一个极大的结果集将其他所有的查询结果都覆盖
query_cache_limit=2M
# InnoDB使用一个缓冲池来保存索引和原始数据
# 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.
# 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%
# 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.
innodb_buffer_pool_size=1G
# 用来同步IO操作的IO线程的数量
# 此值在Unix下被硬编码为4,但是在Windows磁盘I/O可能在一个大数值下表现的更好.
innodb_read_io_threads=16
innodb_write_io_threads=16
# 在InnoDb核心内的允许线程数量.
# 最优值依赖于应用程序,硬件以及操作系统的调度方式.
# 过高的值可能导致线程的互斥颠簸.
innodb_thread_concurrency=9

# 0代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘.
# 1 ,InnoDB会在每次提交后刷新(fsync)事务日志到磁盘上
# 2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上
innodb_flush_log_at_trx_commit=2
# 用来缓冲日志数据的缓冲区的大小.
innodb_log_buffer_size=16M
# 在日志组中每个日志文件的大小.
innodb_log_file_size=48M
# 在日志组中的文件总数.
innodb_log_files_in_group=3
# 在被回滚前,一个InnoDB的事务应该等待一个锁被批准多久.
# InnoDB在其拥有的锁表中自动检测事务死锁并且回滚事务.
# 如果你使用 LOCK TABLES 指令, 或者在同样事务中使用除了InnoDB以外的其他事务安全的存储引擎
# 那么一个死锁可能发生而InnoDB无法注意到.
# 这种情况下这个timeout值对于解决这种问题就非常有帮助.
innodb_lock_wait_timeout=30
# 开启定时
event_scheduler=ON

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

快 4 - 5s

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

效果没那么明显

8、将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