一.事务控制
1. mysql中的事务控制
begin; // 开始事务 start transaction;
多条sql语句
...
commit; // 提交事务, 最终确认，让所有修改生效
rollback; // 回滚事务，撤销事务内所有的修改
2. Java中的体现
jdbc 默认是让每条sql的执行作为一个独立的事务(自动提交)
让事务变为手动提交
try {
// 让事务变为手动提交
Connection.setAutoCommit(false);
// 执行多条sql 接下来，事务会在执行第一条 insert, update, delete 时 开始
// 提交事务
Connection.commit();
} catch(Exception e ) {
// 回滚事务
Connection.rollback();
}例如：删除student表中学号为1005和1007的记录
try(Connection conn = JdbcUtils.getConnection()) {
try {
// 让事务变成手动提交
conn.setAutoCommit(false);
PreparedStatement stmt = conn.prepareStatement("delete from student where sid = ?");
stmt.setInt(1, 1005);
// begin
stmt.executeUpdate();
int i = 1 / 0; // 出现异常
stmt.setInt(1, 1007);
stmt.executeUpdate();
conn.commit(); // 手动提交事务
} catch (SQLException e) {
conn.rollback(); // 手动回滚事务
}
} catch (SQLException e) {
e.printStackTrace();
}二.性能提升
1. 批量增删改
问题引入：往一张表加10 万条记录
create table big (
id int primary key auto_increment,
name varchar(100)
);一条记录一条记录加 --> 特别特变慢!!!
try(Connection conn = JdbcUtils.getConnection()) {
PreparedStatement psmt = conn.prepareStatement("insert into big(name) values(?)");
long start = System.currentTimeMillis();
for (int i = 1; i <= 100000; i++) {
psmt.setString(1, "张" +i);
psmt.executeUpdate();
}
long end = System.currentTimeMillis();
System.out.println("共花费了:" +(end-start)+"毫秒");
} catch (SQLException e) {
e.printStackTrace();
}java通过 批处理 减少跟数据库的交互
PreparedStatement.addBatch() 加入批处理包
PreparedStatement.executeBatch() 把批处理包中所有sql，一次性发送给数据库服务器对于mysql需要通过连接参数，开启批处理功能：
在连接字符串之后添加：jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
示例代码：
一次加100000条记录 --> 共花费了:3761毫秒
try(Connection conn = JdbcUtils.getConnection()){
PreparedStatement ps = conn.prepareStatement("insert into big(name) values(?)");
long start = System.currentTimeMillis();
for (int i = 1; i <= 100000; i++) {
ps.setString(1,"张"+i);
ps.addBatch();
}
ps.executeBatch();
long end = System.currentTimeMillis();
System.out.println("共花费了:" +(end-start)+"毫秒");
} catch (SQLException e) {
e.printStackTrace();
}
改进: 一次加10000条记录,加10次 --> 共花费了:2923毫秒
try(Connection conn = JdbcUtils.getConnection()) {
PreparedStatement psmt = conn.prepareStatement("insert into big(name) values(?)");
long start = System.currentTimeMillis();
for (int i = 1; i <= 100000; i++) {
psmt.setString(1, "张" +i);
psmt.addBatch(); // 10001 10002 .... ... 19999 20000
if(i % 10000 == 0) {
psmt.executeBatch(); // 发送给服务器并清空批处理包
}
}
psmt.executeBatch(); // 收尾的
long end = System.currentTimeMillis();
System.out.println("共花费了:" +(end-start)+"毫秒");
} catch (SQLException e) {
e.printStackTrace();
}2. 查询性能提升
假设总记录数10000
while(rs.next()){
//默认调用rs.next()时,一次性返回10000条记录,占用java内存比较多,不太好,会出现内存溢出(mysql的特性:查多少返回多少)
}
最好是每次少拿一点,即限制返回记录行数
PreparedStatement.setFetchSize(1000); //一次最多返回1000条记录
注:
* mysql 中需要在连接参数中加入： useCursorFetch=true，来开启抓取大小的限制功能
* setFetchSize这个值设的小了,跟数据库的通信次数就多了; 设的大了,相当于占的java这边的内存就多了 所以可以给fetchSize一个默认值： 连接参数中加入： defaultFetchSize=50*mysql的特性:默认查多少返回多少 所以给我们一个提示: 我们写sql语句的时候不需要把所有的结果都查出来,mysql中我们可以用limit来限制你的结果返回数,不要一次都把它查出来 如:select *...limit 20;
提升查询性能最有效的方法，建立数据库索引
索引是对现有的数据进行排序，在排序的结果中搜索，效率会很高
索引的数据结构是:B+树
create index 索引名 on 表(列);例如: 向 big表 的name 建立一个索引
create index idx_name on big(name);
mysql的主键会自动创建索引，用主键作为查询条件，搜索速度最快
索引使用的注意事项：
* 使用了空间换了时间，会占用额外存储
* 索引提升查询性能的同时，影响数据的增删改( 在这张表的查询远高于增删改时，建立索引有意义 )
* null 值不适合索引，要加索引的列上一般添加一个not null约束, 并给一个默认值
* 区分度低的列不适合建索引
男，女
50 50
索引值和总记录数的比例越小，区分度就高
3 / 30万
* mysql 主键索引(聚簇索引， 把这一行的值都存储到叶子节点上) 和 普通索引（叶子节点只存储了主键的值和索引列的值）
所以查询普通索引时，会搜索两遍，首先走普通索引，再走主键索引，（称为回表）避免回表的办法，1. 不用滥用select * ， 2. 建立复合（组合）索引
create index idx_name on big(name, sex, age);
先按name排序，name相同的再按sex排序where name='张1' and sex = '男'
最左匹配规则* 从大量的数据中找到少量数据时，才能充分利用
删除索引:
语法:
alter table 表名 drop key 索引名;
例如: alter table big drop key idx_name;
测试: 两个列 都是姓名 值
张1 张1
...
其中一个列建索引，另一个不建索引
比较两者的效率
测试代码如下:
try(Connection conn = JdbcUtils.getConnection()) {
PreparedStatement psmt = conn.prepareStatement("select * from big where name=?");
long start = System.nanoTime();
psmt.setString(1,"张999");
ResultSet rs = psmt.executeQuery();
long end = System.nanoTime();
System.out.println(end-start);
} catch (SQLException e) {
e.printStackTrace();
}
运行结果:
不加索引:49157821
加索引: 21120907
3. sql语句的执行流程
1) sql 发送给数据库服务器
连接层
2) 管理连接，验证连接是否有效（连接池）
服务层
3) 查询缓存处理，缓存中已有的数据直接返回, 从mysql 8.0开始，这个功能没有了
select * from student 10 条学生记录放入了查询缓存
insert ...
update
delete
4) 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
5) 进行sql的优化处理，选择索引 -- 由优化器
6) 调用存储引擎 -- 执行器
存储引擎层
7) 去索引搜索，读取磁盘数据
mysql 默认的存储引擎是InnoDB( 支持事务，支持外键，聚簇索引按（主键顺序排列，在叶子节点存储所有记录） )
MyISAM( 不支持事务，不支持外键, 不支持灾难恢复 )
Memory( 内存 , hash索引, 只能支持等值比较)
4. 连接池 (connection pool)
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();// 归还连接池了
}三.单元测试
junit （java 单元 测试工具）
测试方法的格式:
1) 必须是public的
2) 没有返回值
3) 没有参数
4) 必须在方法上加一个 @Test注解
注:
* 每个测试方法都可以作为一个独立的入口, 可以运行类上测试来执行类中的所有测试方法
* Assert.assertEquals(期望值，实际值); 如果测试通过会显示绿色对勾, 如果测试不通过会显示黄色的叉号
* junit内部使用了反射知识完成单元测试
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
