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();
}
}
}
}
