SpringBoot ShardingSphere 配置Druid监控

一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第18天，点击查看活动详情。

前言

Druid监控, 对于没有用云数据库和小公司自建机房，基础设施不完善的情况下，Druid是一个相对接入成本最快的一个解决方案。 Druid 提供强大的监控和扩展功能。比如慢查询监控等等。

其他参考官网： GitHub - alibaba/druid: 阿里云计算平台DataWorks(https://help.aliyun.com/document_detail/137663.html) 团队出品，为监控而生的数据库连接池

配置

对应的版本信息：
SpringBoot 2.3.1.RELEASE
shardingsphere 版本 4.1.1
druid 版本 1.1.22

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.22</version>
    <exclusions>
        <exclusion>
            <groupId>com.sun</groupId>
            <artifactId>tools</artifactId>
        </exclusion>
        <exclusion>
            <groupId>com.sun</groupId>
            <artifactId>jconsole</artifactId>
        </exclusion>
    </exclusions>
</dependency>

# spring 配置
spring.main.allow-bean-definition-overriding=true
spring.jackson.time-zone=GMT+8
spring.jackson.date-format=yyyy-MM-dd HH:mm:ss
spring.shardingsphere.datasource.names=ds0
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456
spring.shardingsphere.datasource.ds0.initial-size=10
spring.shardingsphere.datasource.ds0.max-active=100
spring.shardingsphere.datasource.ds0.min-idle=10
spring.shardingsphere.datasource.ds0.max-wait=60000
spring.shardingsphere.datasource.ds0.pool-prepared-statements=true
spring.shardingsphere.datasource.ds0.max-pool-prepared-statement-per-connection-size=20
spring.shardingsphere.datasource.ds0.time-between-eviction-runs-millis=60000
spring.shardingsphere.datasource.ds0.min-evictable-idle-time-millis=300000
spring.shardingsphere.datasource.ds0.validation-query=SELECT 1 FROM DUAL
spring.shardingsphere.datasource.ds0.test-while-idle=true
spring.shardingsphere.datasource.ds0.test-on-borrow=false
spring.shardingsphere.datasource.ds0.test-on-return=false
spring.shardingsphere.datasource.ds0.stat-view-servlet.enabled=true
spring.shardingsphere.datasource.ds0.stat-view-servlet.url-pattern=/druid/*
spring.shardingsphere.datasource.ds0.filter.stat.log-slow-sql=true
spring.shardingsphere.datasource.ds0.filter.stat.slow-sql-millis=3000
spring.shardingsphere.datasource.ds0.filter.stat.merge-sql=true
spring.shardingsphere.datasource.ds0.filter.wall.config.multi-statement-allow=true
spring.shardingsphere.datasource.ds0.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
spring.shardingsphere.datasource.ds0.filters=stat
spring.shardingsphere.sharding.tables.t_crowd_record.actualDataNodes=ds0.t_crowd_record_$->{0..15}_$->{0..4}
spring.shardingsphere.sharding.tables.t_crowd_record.tableStrategy.complex.sharding-columns=task_id,seq
spring.shardingsphere.sharding.tables.itp_crowd_record.tableStrategy.complex.algorithm-class-name=com.dazhen.operation.itp.config.sharding.CrowdRecordComplexAlgo
spring.shardingsphere.props.sql.show=false

最后的效果

其他配置

1、 慢 SQL 记录

（1）有时系统会存在一些 SQL 执行很慢，如果希望日志记录下来，可以添加如下配置开启 Druid 的慢 SQL 记录功能。

注意：slow-sql-millis 用来配置 SQL 慢的标准，执行时间超过 slow-sql-millis 的就是慢。slow-sql-millis 的缺省值为 3000，也就是3秒

# 开启慢SQL统计（这里超过10毫秒则判定为慢SQL）
spring.datasource.druid.filter.stat.enabled=true
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=3000