记录一次 Springboot + mysql SQL调优（集成 druid 数据库连接池）

当数据量上去了，就会面临一个很严峻的问题，那就是程序运行速度太慢。就像下图这样：

这个是在日活用户 2W 多的时候记录的：

主要的解决手段无非就是新增索引、优化 SQL 语句、适当运用缓存等等等…
下面记录一次对 sql 优化的过程以及思路

一、 第一步集成 druid 数据库连接池用于做 sql 监控。

1. maven 引入：

		<!--mysql-->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>${mysql-connector-java.version}</version>
		</dependency>
		<!--druid-->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.10</version>
		</dependency>
		<!--log4j-->
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
		</dependency>

2. application.properties 配置：

### mysql ###
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver


###  druid  ###
# 数据源类型
spring.datasource.type= com.alibaba.druid.pool.DruidDataSource
# 池启动时创建的连接数量
spring.datasource.initialSize=5
# 池里不会被释放的最多空闲连接数量。设置为0时表示无限制。
spring.datasource.minIdle=5
# 同一时间可以从池分配的最多连接数量。设置为0时表示无限制
spring.datasource.maxActive=20
# 在抛出异常之前，池等待连接被回收的最长时间（当没有可用连接时），单位是毫秒。设置为-1表示无限等待。
spring.datasource.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=300000
# 校验SQL，如果不配validationQuery项，则下面三项配置无用
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.filters=stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
spring.datasource.useGlobalDataSourceStat=true

3. Druid 配置类：

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;


@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DruidDataSource druidDataSource(){
       return new DruidDataSource();
    }

    @Bean
    public ServletRegistrationBean druidServlet() {
        DruidDataSource dataSource = druidDataSource();
        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings(new String[]{"/druid/*"});
        // 使用数据库登录用户名密码登录
        reg.addInitParameter("loginUsername", dataSource.getUsername());
        reg.addInitParameter("loginPassword", dataSource.getPassword());
        return reg;
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns(new String[]{"/*"});
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        return filterRegistrationBean;
    }
   
}

项目启动后访问 http://localhost:8080/druid 进入监控登录界面如下图所示

二、 优化思路一：根据最慢 sql 排序，优化慢 sql

一般慢 sql 都是 select , 可以为 where 下的关键字段加上索引，需注意索引的生效规则调整 sql 语句
如果数据量太大或者多表关联统计查询，索引怕是也没有什么用武之地，下图中红色部分是是打算把数据存入 elasticsearch 中再进行查询统计。

三、 优化思路二：根据 sql 执行数排序，优化频繁重复调用的 sql

在业务代码中经常会有需要某一个数据的时候，比如我的停车场云平台每当有记录上报的时候我都需要获取数据对应的车场，所以根据车场编号查询车场信息这个sql 就会被我频繁的调用。
所以呢我就把查到的车场信息放到缓存中去（我是放在本地缓存，也可以放到 redis 中去，前提是数据量有限哈…），每次查之前先去查缓存数据，查不到再去读库，这样可以大大减少数据库的压力。
ps：当然这样做需要在车场数据更新的时候同时更新去缓存哈…

优化完之后效果还是很明显的 ヽ(✿ﾟ▽ﾟ)ノ：