JDBC性能优化及连接池

君子的力量永远是行动的力量，而不是语言的力量。

批量增删改

对10万条数据进行增加，采取3种方式来进行增加对比效果。

由于CPU资源有限，所以可能耗时比较长，但是好的是都在一个CPU下进行的采用控制变量法可以很好的对比出来。

1. 采用PrepraredStatement 进行增加(执行一条增加一条)

public class TestJdbc2 {
    public static void main(String[] args) {
        try(Connection conn = JdbcUtils.getConn()) {
            String sql="insert into user(id,name) values (null,?)";
            try(PreparedStatement psmt = conn.prepareStatement(sql)){
                long start = System.currentTimeMillis();
                for (int i = 1; i < 100000; i++) {
                    psmt.setString(1,"zhang"+i);
                    psmt.executeUpdate();
                }
                long end = System.currentTimeMillis();
                System.out.println("总共花费了"+(end - start)+"毫秒");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

结果

耗时： 323475毫秒

2.采用Batch，将10万条记录放入其中，最后一起执行

在实现这个批处理的操作时，首先要对mysql进行设置，因为mysql默认是关闭的，所以我们得打开，那么再哪里打开呢？
就在我们连接时就得传参，就在URL中加？后面加参数，如果有多个参数直接用&符号。
url 的格式 :

jdbc:mysql://ip地址:端口号/数据库名?参数&参数

再这里打开数据库的批处理功能的参数是rewriteBatchedStatements=true
在这里就是

jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true

public class TestJdbc2 {
    public static void main(String[] args) {
        try(Connection conn = JdbcUtils.getConn()) {
            String sql="insert into user(id,name) values (null,?)";
            try(PreparedStatement psmt = conn.prepareStatement(sql)){
                long start = System.currentTimeMillis();
                for (int i = 1; i < 100000; i++) {
                    psmt.setString(1,"zhang"+i);
                    psmt.addBatch();
                }
                psmt.executeBatch();
                long end = System.currentTimeMillis();
                System.out.println("总共花费了"+(end - start)+"毫秒");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

结果

	耗时：2118

3.将10万条记录加入Batch，每1万进行执行一次

同样打开批处理功能

jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true

ublic class TestJdbc2 {
    public static void main(String[] args) {
        try(Connection conn = JdbcUtils.getConn()) {
            String sql="insert into user(id,name) values (null,?)";
            try(PreparedStatement psmt = conn.prepareStatement(sql)){
                long start = System.currentTimeMillis();
                for (int i = 1; i < 100000; i++) {
                    psmt.setString(1,"zhang"+i);
                    psmt.addBatch();
                    if (i %10000 == 0){
                        psmt.executeBatch();
                    }
                }
                long end = System.currentTimeMillis();
                System.out.println("总共花费了"+(end - start)+"毫秒");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

结果

	耗时：1446

由此可鉴，第三种没加入1万就执行一次的效率最高。

查询性能优化

1.设置最优的预取值
defaultRowPrefetch：预取条数默认值
defaultBatchValue：触发查询操作的批量请求值
这两个参数的默认值都是10，我们可以通过增加这两个参数值来减少数据库请求以提高查询效率，当然具体值大小要视具体情况而定。

2.通过连接池获取连接
创建连接的代价很大，通过连接池获取连接可省去创建连接时间。

3.设置检索时的批量值
Statement.getFetchSize(); 获取一次检索的批量值
Statement.setFetchSize(30); 设置批量

4.设置ResultSet的批量值
ResultSet.getFetchSize(); 获取默认批量值
ResultSet.setFetchSize(50); 设置批量值
处理大数据时可显著提高处理速度

5.优化查询SQL

比如避免使用select * from table where condition…,因为这么做会把所有的数据项目查询出来，比如我们只需要Salary的话，我们就写成select salary from employee where name=RR,避免不必要数据的检索。

6.迭代分批次获取数据替代一次大批量获取数据

某些情况下，应用程序可能会通过JDBC一次请求大量数据，而应用程序可能会一次把所有数据返回给客户端，这样会用掉很多时间，可以采取如下方式解决：
在Server端缓存数据，分批次发给Client端，比如Server端查询出1000条数据，可以分10批次每次传送100条给Client端不在Server端缓存数据，而通过存储过程迭代的返回小批量数据。

其实对于查询最有效的还是建立索引：
建立数据库索引
索引是对现有的数据进行排序，在排序的结果中搜索，效率会很高
索引的数据结构是:B+树
create index 索引名 on 表(列);
// 向 big 的name 建立一个索引
create index idx_name on big(name);

mysql的主键会自动创建索引，用主键作为查询条件，搜索速度最快
而建立索引后，对于查询的sql语句还有影响，
示例；
id name sex
1 张三男
id 为主键，在name 建立索引，那么name建立索引后上就存放有id，name 并没有sex。
假如现在查询id，name 不查询sex的值，那么查询速度最快，只需要查询name索引即可。
如果还要查询sex的值，那么此时，就要二次查询，先通过name索引查到主键id，然后通过主键id再查询sex。所以就进行了二次查询，相对速度就慢了。

示例
未创建索引前查询

mysql> select * from user where name like 'zhang222%' order by id desc;
+------+----------+
| id   | name     |
+------+----------+
| 2190 | zhang222 |
| 1221 | zhang222 |
|  222 | zhang222 |
+------+----------+
3 rows in set (0.01 sec)

用时0.01
创建索引

create index idx_name on user(name);

执行查询

mysql> select * from user where name like 'zhang222%' order by id desc;
+------+----------+
| id   | name     |
+------+----------+
| 2190 | zhang222 |
| 1221 | zhang222 |
|  222 | zhang222 |
+------+----------+
3 rows in set (0.00 sec)

用时0.00

由此可鉴，创建索引的优越性。

删除索引

alter table 表名 drop key 索引名
alter table user drop key idx_name;

SQL语句的执行顺序

sql 发送给数据库服务器
连接层
管理连接，验证连接是否有效（连接池）
服务层
查询缓存处理，缓存中已有的数据直接返回, 从mysql 8.0开始，这个功能没有了
select * from student 10 条学生记录放入了查询缓存
```
 insert ... 
 update
 delete
```
sql 需要词法分析、语法分析，生成语法解析树 – 由分析器完成
select * from student where id = 1;
select * from student where id = 2;
select * from student where id = 3;

select * from student where id = ?
? 1
? 2
mysql 中默认没有打开这个功能，需要打开开关：
useServerPrepStmts=true
cachePrepStmts=true
prepStmtCacheSize=25
进行sql的优化处理，选择索引 – 由优化器
调用存储引擎 – 执行器
存储引擎层
去索引搜索，读取磁盘数据
mysql 默认的存储引擎是InnoDB( 支持事务，支持外键，聚簇索引按（主键顺序排列，在叶子节点存储所有记录） )
MyISAM( 不支持事务，不支持外键, 不支持灾难恢复 )
Memory( 内存 , hash索引, 只能支持等值比较)

连接池

apache dbcp 最老牌
c3p0 连接池
alibaba druid (德鲁伊) sql监控

javax.sql.DataSource 连接池接口
Conneciton conn = datasource.getConnection(); 从池中返回连接对象

conn.close(); // 一般都覆盖了关闭方法，将连接归还给连接池，而不是真正关闭

用DriverManager 获取连接，称为直连
用DataSource 获取连接，称为池连

DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl("jdbc:mysql://localhost:3306/test");
dataSource.setUsername("root");
dataSource.setPassword("root");

// dataSource.setDriverClassName("com.mysql.jdbc.Driver"); 可选步骤，注册驱动
dataSource.setInitialSize(5); // 初始连接数
dataSource.setMaxActive(10); // 最大连接数
dataSource.setMinIdle(5);    // 最小连接数
dataSource.setValidationQuery("select 1"); // 一条简单的sql语句，用来保活
dataSource.setTestWhileIdle(true); // 当空闲时时不时检查一下连接的有效性, 利用ValidationQuery中的sql语句
dataSource.setTimeBetweenEvictionRunsMillis(60*1000); // 默认一分钟

try(Connection conn = dataSource.getConnection()){

    // conn.close();// 归还连接池了
}