MySQL事务和优化

事务

事务的概念

??完成某个独立业务(功能)的一个或者一组步骤(指令)组成的一个整体，要么所有步骤全部执行成功，要么全部失败。

??一旦某个业务(功能)被事务托管(管理)，所有步骤执行成功，会提交事务。如果其中一部失败会回滚事务，回滚到事务开启之前的状态。

事务特征：

??原子性：不可再分，转账就是一个原子性操作，所有步骤全部成功或者全部失败

??持久性：转账成功，双方的金额会持久化的保存到存储设备中（数据库）

??一致性：转账之前和转账之后，数据总量不变，例如：张三向李四转账 500，转账之前（张三1000，李四1000），转账之后（张三500,李四1500)

??隔离性：事务A和事务B保持隔离

?但是多个事务同时操作同一张表，就会发生一系列的问题，需要设置事务隔离级别来解决该问题

例：两人转账

转账步骤

场景：张三向李四转账500

步骤：判断张三账户余额有没有500

??开启事务

??张三账号-500

??-- 停电了

?? 李四账户+500

??当两个update语句成功执行

?? 提交事务

??如果两个update没有成功执行，回滚事务（回滚到事务开启之前的状态）

1. 开启事务
2. 执行业务(此时是转账)
3. 执行业务全部成功提交事务，否则回滚事务

-- 开启事务语法:   start transaction;
-- 提交事务语法：  commit;
-- 回滚事务语法：  rollback;  只有出现异常状态(catch块)才会执行回滚的操作

转账操作

1. 创建一张tb_account表:id、account_name 账户名称、account_balance 余额

2. 插入数据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)：数据变动是永久的

事务隔离级别

??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个字段，必须为表建立主键，主键一定要使用整数类型

13. 如果表中的某个字段(手机号码)，都是唯一的，一定要为其建立唯一索引
14. 联合索引列数据类型最好一致
15. 多表联合查询，查询的次数尽量少，工作中可以将多表联合查询的数据放入程序的缓存中，后面从缓存中获取
16. 数据量大的情况下，使用多次提交代替一次提交

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

MySQL事务和优化

原文：https://www.cnblogs.com/lyang-a/p/15042722.html