MySQL---４、事务、索引

目录

一、事务

（二）ACID原则

（三）事务并发

１、脏读

２、不可重复读

３、幻读

不可重复读与幻读的区别

（四）事务隔离

１、读未提交（Read uncommitted）

２、读已提交（Read committed）

３、可重复读（Repeated Read）

４、串行化（Serializable）

二、索引

（一）作用

（二）索引结构

（三）索引分类

（四）索引语法

１、创建索引

２、查看索引

３、删除索引

（五）性能分析

１、查看执行频率

​编辑

２、慢查询日志

（六）哪些情况需要创建索引

（七）哪些情况不需要创建索引

---

一、事务

事务就是将一组SQL语句放在同一批次内去执行

如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行

（一）事务的处理方法

方法一：

SET AUTOCOMMIT = 0; # 关闭自动提交模式

SET AUTOCOMMIT = 1; # 开启自动提交模式

#查看|设置事务提交方式
SELECT @@autocommit;
SET @@autocommit = 0;
#A余额减去500
UPDATE account SET cash = cash - 500.00 WHERE name="A";
#B余额增加500
UPDATE account SET cash = cash + 500.00 WHERE name="B";
#提交
COMMIT;、
#回滚
ROLLBACK;

方法二：

#开启事务
START TRANSACTION;
#A余额减去500
UPDATE account SET cash = cash - 500.00 WHERE name="A";
异常
#B余额增加500
UPDATE account SET cash = cash + 500.00 WHERE name="B";
#提交
COMMIT;
#回滚
ROLLBACK;

（二）ACID原则

• 原子性（Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

• 一致性（Consistency):事务完成时，必须使所有的数据都保持一致状态。

• 隔离性（lsolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

• 持久性（Durability)︰事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

（三）事务并发

１、脏读

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。

 

２、不可重复读

一个事务读取某些数据,在它结束读取之前，另一个事务可能完成了对数据行的更改。当第一个事务试图再次执行同一个查询，服务器就会返回不同的结果。

 

３、幻读
 

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据

不可重复读与幻读的区别

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，

幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了

（四）事务隔离

--查看事务隔离级别
select @@transaction_isolation;

--设置事务隔离级别
set [session|global] transaction isolation level {read uncommitted|read committed|repeatable read|serializable}

隔离级别	脏读	不可重复读	幻读
读未提交(Read uncommitted)	√	√	√
读已提交(Read committed)	×	√	√
可重复读(Repeatable read)	×	×	√
可串行化(Serializable)	×	×	×

１、读未提交（Read uncommitted）

最低隔离级别，允许事务读取其他事务未提交的更改。可能导致脏读、不可重复读、幻读问题。

２、读已提交（Read committed）

事务只能读取到已提交的数据。可以解决脏读问题，但是还会有可能不可重复读、幻读问题。

３、可重复读（Repeated Read）

专门针对“不可重复读”这种情况而制定的隔离级别。可以解决脏读和不可重复读问题，但还是有可能出现幻读问题。MYSQL默认的隔离级别

４、串行化（Serializable）

最高隔离级别，所有事务都串行化顺序执行，不存在并发，从而完全防止脏读、不可重复读和幻读问题。但是这种隔离级别性能较低，基本不会使用。

重点：

事务隔离级别越高，数据越安全，性能越低

事务隔离级别越低，数据越不安全，性能越

二、索引

（一）作用

提高查询速度

确保数据的唯一性

可以加速表和表之间的连接，实现表与表之间的参照完整性

使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间

全文检索字段进行搜索优化

（二）索引结构

（三）索引分类

分类	含义	特点	关键字
主键索引（PRIMARY KEY）	针对于表中主键创建的索引	默认自动创建，只有一个	PRIMARY
唯一索引（UNIQUE）	避免同一个表中某个数据列中的值重复	可以有多个	UNIQUE
常规索引（INDEX）	快速定位特定数据	可以有多个
全文索引（FULLTEXT）	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

#添加主键索引
ALTER table student add PRIMARY Key(StudentNo);
#添加唯一索引
ALTER table student add UNIQUE(StudentName);
#添加普通索引
ALTER table student add INDEX(IdentityCard);
#全文索引
ALTER table student add FULLTEXT(Phone)

explain  select .....可以看到执行效率

（四）索引语法

１、创建索引

CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名 (字段名,....)

２、查看索引

SHOW INDEX FROM 表名

３、删除索引

DROP INDEX 索引名 ON 表名

（五）性能分析

１、查看执行频率

SHOW GLOBAL STATUS LIKE "Com_______";

２、慢查询日志

#查看慢查询开启情况
SHOW VARIABLES LIKE 'slow_query_log';

慢查询日志记录了所有执行时间超过指定参数（long_query_time,单位：秒，默认100秒）的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置信息

#开启MySQL慢日志查询开关
slow_query_log = 1;
#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time = 2;

（六）哪些情况需要创建索引

• 主键自动建立唯一索引

• 频繁作为查询条件的字段应该创建索引

• 查询中与其他表关联的字段，外键关系建立索引

• 频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引

• WHERE条件里用不到的字段不创建索引

• 单键/组合 复合索引的选择问题，who?(在高并发下倾向创建组合索引)

• 查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度

• 查询中统计或者分组字段

（七）哪些情况不需要创建索引

• 表记录太少

• 经常增删改的表 （ 提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和 DELETE。 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。）

• 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。---