MySQL事务和优化
事务
事务的概念
完成某个独立业务(功能)的一个或者一组步骤(指令)组成的一个整体,要么所有步骤全部执行成功,要么全部失败。
一旦某个业务(功能)被事务托管(管理),所有步骤执行成功,会提交事务。如果其中一部失败会回滚事务,回滚到事务开启之前的状态。
事务特征:
原子性:不可再分,转账就是一个原子性操作,所有步骤全部成功或者全部失败
持久性:转账成功,双方的金额会持久化的保存到存储设备中(数据库)
一致性:转账之前和转账之后,数据总量不变,例如:张三向李四转账 500,转账之前(张三1000,李四1000),转账之后(张三500,李四1500)
隔离性:事务A和事务B保持隔离
但是多个事务同时操作同一张表,就会发生一系列的问题,需要设置事务隔离级别来解决该问题
例:两人转账
转账步骤
场景:张三向李四转账500
步骤:判断张三账户余额有没有500
开启事务
张三账号-500
-- 停电了
李四账户+500
当两个update语句成功执行
提交事务
如果两个update没有成功执行,回滚事务(回滚到事务开启之前的状态)
1. 开启事务
2. 执行业务(此时是转账)
3. 执行业务全部成功提交事务,否则回滚事务
-- 开启事务语法: start transaction;
-- 提交事务语法: commit;
-- 回滚事务语法: rollback; 只有出现异常状态(catch块)才会执行回滚的操作
转账操作
-
创建一张tb_account表:id、account_name 账户名称、account_balance 余额
-
插入数据kxh和cl都是1000
create table tb_account(
id int(11) auto_increment,
account_name varchar(30) not null,
account_balance int(11) ,
primary key (id)
)default charset=utf8mb4,engine=INNODB;
insert into tb_account(account_name,account_balance)values('zs',1000),('ls',1000);
场景:在不使用事务的情况下转账
update tb_account set account_balance = account_balance-500 where id= 1;
停电了
update tb_account set account_balance = account_balance+500 where id= 2;
-- 此时没有使用事务:部分成功、部分失败 kxh(500) cl(100)
场景:使用事务来管理转账,好处一旦转账出现异常操作,立马回滚
-- 1. 开启事务
start transaction;
-- 2. 执行转账
update tb_account set account_balance = account_balance-500 where id= 1;
停电了
update tb_account set account_balance = account_balance+500 where id= 2;
-- 3. 如果全部成功提交事务,否则回滚事务
rollback;
场景:使用事务来管理转账,执行成功提交事务
-- 1. 开启事务
start transaction;
-- 2. 执行转账
update tb_account set account_balance = account_balance-500 where id= 1;
-- 停电了
update tb_account set account_balance = account_balance+500 where id= 2;
-- 3. 如果全部成功提交事务
commit;
事务提交方式
MySQL事务默认是自动提交
没有设置start transaction 可以不用commit提交,会自动提交
一旦设置了start traansaction 必须手动commit;
Oracle事务默认为手动提交
一旦执行了DML,必须手动commit;
事务四大特征(ACID)
原子性(Atomicity):所有的数据修改,要么一起执行,要么不执行
一致性(Correspondence):所有的数据修改同时得到反应
隔离性(Isolation): 另一个事务需要在此事务结束之后才能执行
持久性(Durability):数据变动是永久的
事务隔离级别
事务隔离级别 | 脏读(Dirty) | 虚读/不可重复读(NonRepeatable Read) | 幻读(Phantom Read) |
---|---|---|---|
读未提交(read uncommitted) | √ | √ | √ |
读已提交(read committed) | × | √ | √ |
可重复读(repeatable read) | × | × | × |
串行化(serializable) | × | × | × |
serializable:解决所有问题,多个事务同时访问某一张表,最多一个线程进去(其他线程访问表被锁住了)
读未提交 (read uncommitted)
最低的一个事务隔离级别,工作中一般不会用
出现的问题:一个事务会读取到另外一个事务没有提交的数据(脏读)
A事务:kxh向cl转账(500), A事务开启了事务,执行了update,但是没有提交(1000,1000)
B事务:读取到了A事务没有提交的数据(500,1000)
演示:脏读
脏读步骤:(查询未提交的数据)
先设置执行 读未提交(READ UNCOMMITTED)
在启动事务,更新数据,不提交
再次读取数据,发现数据已经被修改了,这就是所谓的“脏读”
提交事务
再次读取数据
-- 1将事务隔离级别设置为读未提交
set global transaction isolation level read uncommitted;
-- 重新打开命令行窗口
-- 2 开启事务
start transaction;
-- 3 执行转账
update tb_account set account_balance = account_balance-500 where id= 1;
-- 停电了
update tb_account set account_balance = account_balance+500 where id= 2;
-- 4 暂时不提交 如果全部成功提交事务
commit;
-- 打开另外一个命令行窗口
start transaction;
-- 执行select语句
select * from tb_account;
-- 读取到上一个事务没有提交的数据(脏读)
读已提交 (read committed)
能够解决脏读的问题,但是还会引发其他的问题(同一个事务中多次读取的数据不一致)
演示:解决脏读
-- 1设置隔离级别读已提交
set global transaction isolation level read committed;
-- 2 开启事务
start transaction;
-- 3 执行转账
update tb_account set account_balance = account_balance-500 where id= 1;
-- 停电了
update tb_account set account_balance = account_balance+500 where id= 2;
-- 4 如果全部成功提交事务
commit;
-- 打开另外一个命令行窗口
start transaction;
-- 执行select语句
select * from tb_account;
-- 上一个事务提交
-- 继续执行select语句
-- 两次读取数据不一致(幻读)
select * from tb_account;
可重复读 (repeatable read)
实例:解决虚读(同一事务多次读取数据不一致)的问题
演示:
-- 1设置隔离级别读已提交
set global transaction isolation level repeatable read;
-- 2 开启事务
start transaction;
-- 3 执行转账
update tb_account set account_balance = account_balance-500 where id= 1;
-- 停电了
update tb_account set account_balance = account_balance+500 where id= 2;
-- 4 如果全部成功提交事务
commit;
repeatable read解决虚读的问题,但是会引发幻读(A事务对所有账户添加了100块钱,B事务新插入了一条数据。A事务无法操作B事务新插入的数据)
幻读场景:A事务对所有账户添加了100块钱,事务新插入了一条数据(王五). A事务无法操作王五的数据
串行化 (serializable)
串行化:能够解决所有问题(脏读、虚读、幻读)
-- 1设置隔离级别读已提交
set global transaction isolation level serializable;
-- 2 开启事务
start transaction;
-- 3 执行转账
update tb_account set account_balance = account_balance-500 where id= 1;
-- 停电了
update tb_account set account_balance = account_balance+500 where id= 2;
-- 4 如果全部成功提交事务
commit;
小结:A事务开启了,正在操作tb_account表,没有提交
B事务稍后开启了,向通过select语句查询tb_acount表,但是A事务没有提交,所有B事务查询不到结果(此时tb_account表被锁住了)
隔离级别越高,消耗的内存资源越多,性能越低
工作中事务隔离级别设置:读已提交和可重复读
串行化 serializable类似于Java中的同步synchronize,同一个表(资源)在同一个事务中访问,其他事务只能在外面等待
查询事务隔离级别:
-- tx 事务简称 isolation 隔离
select @@tx_isolation;
设置事务隔离级别
set global transaction isolation level 隔离级别;
-- 例如:隔离级别设置为可重复读
-- global 全局的 level级别
set global transaction isolation level repeatable read;
MySQL优化
存储引擎
存储引擎能够确定你插入的数据在数据文件中的存储方式
MySQL有哪些存储引擎呢?
-- 通过该命令可以查询存储引擎
show engines;
-- 使用频率最高的就是INNODB存储引擎
-- 特征:支持事务、支持外键、支持行级锁(触发器)
-- INNODB优点:安全、稳定、可以确保数据完整性
-- INNODB缺点:执行DML语句会用到外键,所有会有检查数据的合法性操作,速度相对其他存储引擎会慢一点
-- MyISAM:检索数据最快的存储引擎,缺点不支持外键,无法确保数据完整性
-- 创建表必须指定存储引擎和编码方式
索引
是一种检索数据的数据结构,创建的索引会存储到数据库的数据文件中
索引分类:
1)B-Tree索引 B表示平衡 支持> >= < <= like 但是不支持in 不支持前后模糊中间精确,也不支持前面模糊后面精确
2)Hash索引类似于HashMap
既然索引类似于书的目录,那么是不是所有的列都适合建立索引呢?
不经常检索的列不适合建立索引(例如:worker表的worker_image)
数据大量重复的列不适合建立索引(例如:worker_sex 除了M就是F)
text和longtext类型的字段不适合建立索引
那些列适合建立索引?
主键列:创建主键约束会自动建立一个索引
经常需要where的字段建立索引
经常group by的列
经常 order by的列建立索引
唯一约束就是一个唯一索引(例如:worker_mobile字段)
一张表手动创建索引数量不要超过3个,主键、唯一除外
因为:创建索引会生成一张目录,目录的数据存储到数据文件中,如果索引过多,那么数据文件会很大,性能反而低下。
所有的分类:
普通索引
-- 为worker表的worker_name 创建普通索引
-- 列名称表示表的某一列建立索引
-- create index 索引名称 on 表名称(列名称);
create index index_worker_name on worker(worker_name);
show create table worker ;
-- explain 关键字执行SQL语句可以查看SQL语句是否支持索引
explain select * from worker ;
唯一索引
-- 创建唯一索引语法
-- create unique index 索引名称 on 表名称(列名称);
create unique index index_un_dept_loc on dept(loc);
-- 查看所有是否创建成功语法
-- show create table 表名称;
show create table dept;
-- 删除索引语法
-- alter table 表名称 drop index 索引名称;
alter table dept drop index index_un_dept_loc ;
联合索引
特征:对表的多个字段建立索引
多个字段同时用户where条件
语法:
-- create index 索引名称 on 表名称(字段1,字段2);
-- 例子:对emp表的comm和sal列建立联合索引
create index index_emp_salcomm on emp(sal,comm);
-- 注意:联合索引的字段数据类型最好保持一致
-- select ename,job from emp where comm>100 没有使用索引,因为comm字段是联合索引的第二个字段
-- select ename,job from emp where sal>100 会使用索引
-- select ename,job from emp where sal>100 or comm=100 会使用索引
-- select ename,job from emp where sal>100 and comm=100 会使用索引
全文本索引(5.7版本新增的)
为大数据文本建立索引 ,关键字fulltext
-- 语法
-- create fulltext 索引名称 on 表名称(列名称);
-- 示例
create fulltext index fullindex_emp_ename on emp(ename);
-- 注意:全文索引最好在text或者longtext字段上建立
大数据量的测试
场景1:根据id查询customer_bak3表的一行记录,300万条数据耗时8毫秒,因为创建主键就会默认创建一个索引,所以效率高。工作中可以频繁使用根据主键查询该行的数据
-- 使用到索引,效率高8毫秒
EXPLAIN
select id,cust_name,cust_address,cust_mobile, cust_ticket,cust_desc
from customer_bak3 where id=345678
-- 没有使用索引耗时1.3秒
EXPLAIN
select id,cust_name,cust_address,cust_mobile, cust_ticket,cust_desc
from customer_bak3 where cust_name='Ronald Bradford663022';
-- 上面例子:根据名称查询对应的实体耗时1.3秒,效率相对低下
-- 为customer_bak3表的cust_name字段建立索引
-- 300W创建索引耗时20.8秒
create index index_customer_name on customer_bak3 (cust_name);
-- 创建索引之后查询耗时8毫秒
select id,cust_name,cust_address,cust_mobile, cust_ticket,cust_desc
from customer_bak3 where cust_name='Ronald Bradford663022';
-- 使用like查询
EXPLAIN
-- 前面模糊后面精确 没有使用索引 耗时1.5秒
-- 前面精确后面模糊 使用了索引 耗时0.002秒
-- 前后模糊中间精确 没有使用索引 耗时1.6秒
-- in关键字使用了索引
select id,cust_name,cust_address,cust_mobile, cust_ticket,cust_desc
from customer_bak3 where cust_name not in ('Ronald Bradford663022');
场景2:为customer_bak3创建唯一索引
-- 为customer_bak3 的cust_mobile创建唯一索引 15秒
create unique index un_customer_mobile on customer_bak3(cust_mobile);
-- 使用索引耗时0.003秒
-- 不使用索引1.5秒
explain
select id,cust_name,cust_address,cust_mobile, cust_ticket,cust_desc
from customer_bak3
where cust_mobile = '13001408124'
-- 删除索引
alter table customer_bak3 drop index un_customer_mobile;
场景:为customer_bak3的cust_address和cust_desc创建联合索引
-- 创建联合索引14.2秒
create index customer_mobile_address on customer_bak3(cust_desc,cust_address);
explain
-- 使用索引2.2秒
-- 不使用索引1.3秒
-- 使用索引效率反而低了
select * from customer_bak3 where cust_desc='CVBCDE' and cust_address='Shenyang';
-- 删除索引
alter table customer_bak3 drop index customer_mobile_address
SQL语句的优化
1.不要在select语句中有*,需要几列就写几列
2.工作中SQL语句全部使用大写,即使你使用了小写,程序运行时也会将其转换为大写
3.在数据量大的情况下慎用like (前后模糊中间精确,前面模糊后面精确)
4.少用in和not in 虽然支持索引但是工作中效率不高
-- 查询没有为客户送水的送水工信息
select id,worker_name,worker_sal,worker_ticheng
from worker
where id not in(
select distinct worker_id from history
);
-- 使用多表联合查询代替in
select w.id,w.worker_name,w.worker_sal,w.worker_ticheng
from worker w left join history h on w.id=h.worker_id
where h.worker_id is null
5.工作中鼓励使用all和any
6.工作中删除表数据使用truncate关键字代替delete
7.尽量少用having,鼓励使用where
8.order by排序的字段一定要建立索引
9.group by后面分组的字段足够的少
例如:计算工资 group by 送水工,底薪,提成 为三个字段进行分组,可以做适当的优化,group by w.id ,因为w.id是工人表的唯一标识
10.适当的建立索引:经常where、group by、order by
11.如果where后面有多个字段,过滤数据多的放在前面
-- 如果cust_address='Shenyang'过滤的数据多将其放在前面,
select * from customer_bak3 where cust_address='Shenyang' and cust_desc='JUIJK';
12.一张表不要超过15个字段,必须为表建立主键,主键一定要使用整数类型
- 如果表中的某个字段(手机号码),都是唯一的,一定要为其建立唯一索引
- 联合索引列数据类型最好一致
- 多表联合查询,查询的次数尽量少,工作中可以将多表联合查询的数据放入程序的缓存中,后面从缓存中获取
- 数据量大的情况下,使用多次提交代替一次提交
JDBC
JDBC是JavaEE的一个技术,负责将数据库的数据和程序进行交互
使用JDBC编写一个程序,在Java程序中编写SQL语句(update), 通过JDBC执行,最终将执行的结果更新到数据库中
JDBC全称:Java DataBase Connectivity
使用JDBC连接数据库:
步骤:
1. 创建在项目下创建lib目录
2. 把jar包(mysql-connector-java-5.1.40.jar)导入lib目录下
3. 加载MySQL驱动包的驱动,将MYSQL驱动包注册到Java程序中
4. 定义类JDBC_mysql.java
5. 定义Java连接数据库的URL、用户名、密码
因为Java和MySQL数据库不是同一个厂商,也不在同一个进程下,所以Java程序向MySQL数据库发起连接必须使用URL
URL格式:
主协议:子协议://主机地址:端口/数据库名称?连接参数
jdbc:mysql://localhost:3306/j0812?useUnicode=true&characterEncoding=utf8&useSSL=false
主协议:jdbc 子协议:mysql
mysql主机名称:localhost
mysql端口号:3306 (mysql数据库安装的时候默认端口号)
连接参数:useUnicode=true 使用useUnicode编码
charsetEncoding=utf8 字符集编码utf8格式
useSSL=false 不使用Socket套接字安全协议
用户名:就是你登录MySQL的用户名 默认 root
密码:就是你登录MySQL的密码 默认 root
6. 创建数据库连接对象Connection,将url、用户名、密码作为参数来创建连接
7. 定义SQL语句,该语句就是向数据库发送的命令
8. 创建执行SQL语句的对象Statement,该对象用来执行SQL语句
9. 执行SQL语句
10. 释放连接对象的资源
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBC_mysql {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
try {
// 程序运行时告诉JVM我使用的数据库驱动为MySQL驱动
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/j0812?useUnicode=true&characterEncoding=utf8&useSSL=false";
String user = "root";
String password = "root";
// Java先MySQL发起连接请求
conn = DriverManager.getConnection(url, user, password);
String sql = "update tb_account set account_balance = 5000 where id=1;";
st = conn.createStatement();
// rows 表示受影响行数 如果小于等于0,没有成功执行update
int rows = st.executeUpdate(sql);
System.out.println(rows);
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放连接对象的资源
// 先开启的后关闭,后开启的先关闭
try {
st.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}