Mysql第六章：mysql进阶(事务和索引案例分析)

目录

1：什么是事务（ACID）

1.1：脏读（事务没有提交）

1.2：不可重复读（事务B修改了数据提交事务导致事务A查询读取出错）　　

1.3：虚读(幻读，事务b修插入数据提交事务A读取数据变大)

1.4：Mysql事务隔离级别分析

2:事务案例（银行转账）

3:索引

4:索引案例

---

1：什么是事务（ACID）

         数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。 事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。事务是数据库运行中的逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理（当系统断电时候，这种状况下重新启动数据库服务器会使用redo日志使数据库恢复断电前的状态，然后未commit的事务执行undo~）。

原子性（Atomicity)
事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。

一致性（Consistency）
　　一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

　　拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

隔离性（Isolation）
　　隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

　　即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

　　关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

持久性（Durability）
　　持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

　　例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

1.1：脏读（事务没有提交）

　　脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。见到来说也即是并发的时候读取了事务中的数据

　　当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户A向用户B转账100元，对应SQL命令如下

    update account set money=money+100 where name=’B’;  (此时A通知B)

    update account set money=money - 100 where name=’A’;

　　当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。

解决方案：每个事务有自己的缓存，缓存执行完之后更改数据库数据

1.2：不可重复读（事务B修改了数据提交事务导致事务A查询读取出错）
　　

不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

　　例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。

　　不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

　　在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题，例如对于同一个数据A和B依次查询就可能不同，A和B就可能打起来了……

解决方案：锁定事务A读取的行数据

1.3：虚读(幻读，事务b修插入数据提交事务A读取数据变大)

　　幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

　　幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

解决方案：锁定事务A读取的表

1.4：Mysql事务隔离级别分析

　　现在来看看MySQL数据库为我们提供的四种隔离级别：

　　① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。（锁表）

　　② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。（锁行）

　　③ Read committed (读已提交)：可避免脏读的发生。（事务有自己的缓存空间）

　　④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);//最底级别：只保证不会读到非法数据，上述3个问题有可能发生 
con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); //默认级别：可以防止脏读 
con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);//可以防止脏读和不可重复读取 
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); //最高级别：防止上述3种情况，事务串行执行，慎用

以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，执行效率就越低。像Serializable这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。在MySQL数据库中默认的隔离级别为Repeatable read (可重复读)。

　　在MySQL数据库中，支持上面四种隔离级别，默认的为Repeatable read (可重复读)；

而在Oracle数据库中，只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别，其中默认的为Read committed级别。

　　在MySQL数据库中查看当前事务的隔离级别：

show variables like 'transaction_isolation';

　　在MySQL数据库中设置事务的隔离 级别：

    set  [glogal | session]  transaction isolation level 隔离级别名称;

    set tx_isolation=’隔离级别名称;’

例1：查看当前事务的隔离级别：

　　

例2：将事务的隔离级别设置为Read uncommitted级别：

　　

或：

　　

记住：设置数据库的隔离级别一定要是在开启事务之前！

　　如果是使用JDBC对数据库的事务设置隔离级别的话，也应该是在调用Connection对象的setAutoCommit(false)方法之前。调用Connection对象的setTransactionIsolation(level)即可设置当前链接的隔离级别，至于参数level，可以使用Connection对象的字段：

　　

在JDBC中设置隔离级别的部分代码：

　　

　　后记：隔离级别的设置只对当前链接有效。对于使用MySQL命令窗口而言，一个窗口就相当于一个链接，当前窗口设置的隔离级别只对当前窗口中的事务有效；对于JDBC操作数据库来说，一个Connection对象相当于一个链接，而对于Connection对象设置的隔离级别只对该Connection对象有效，与其他链接Connection对象无关。

2:事务案例（银行转账）

存在银行表如下，丽丽账户1000，小明账户没钱，两个事务AB分别给小明转账1000和300，要保证丽丽的账户余额不能为负数。

//1://开始事务A 转账1000
START TRANSACTION
BEGIN
select * FROM bank
//丽丽-1000
UPDATE  bank SET num=( num-1000) where   id=1
select * FROM bank
//小明+1000
UPDATE  bank SET num=( num+1000) where   id=2
select * FROM bank

COMMIT


//-----------是当前读，读取的是最新的数据，select读取的是快照

//2.1//开启事务b转账300，金额扣件的时候余额大与扣费
START TRANSACTION
BEGIN
select * FROM bank ;
//此处加条件 num>0 因为转账的时候，update时，
//如果有其他事务A锁住了这条记录，update会等待，等到A
//事务释放锁，update会执行，但此时如果num的数量已经被修改，update的执行会返回影响行数为0。
UPDATE  bank SET num=( num-300) where   id=1 and num>0
//此处需要回滚
UPDATE  bank SET num=( num+300) where   id=2
COMMIT
ROLLBACK


//2.2//开启事务b转账300，直接转账，然后查询余额小于扣减，回滚，
START TRANSACTION
BEGIN
select * FROM bank
//先增加金额
UPDATE  bank SET num=( num+300) where   id=2
//然后查询丽丽的余额
//此处进行显示加锁等事务A提交之后查询真正的丽丽余额
//余额大于300 扣减否则回滚
select * from bank where id = 1 for update; 
UPDATE  bank SET num=( num-300) where   id=1
COMMIT
ROLLBACK

3:索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。上述SQL语句，在没有索引的情况下，数据库会遍历全部200条数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。如果我们把SQL语句换成“SELECT * FROM article WHERE id=2000000”，那么你是希望数据库按照顺序读取完200万行数据以后给你结果还是直接在索引中定位呢？上面的两个图片鲜明的用时对比已经给出了答案（注：一般数据库默认都会为主键生成索引）。

索引分为聚簇索引和非聚簇索引两种，

聚簇索引：是按照数据存放的物理位置为顺序的，数据和索引在一起，找到索引也就找到了数据，就像innodb的主键索引B+Tree的数据结构，聚簇索引具有唯一性，主键一般作为聚簇索引。

非聚簇索引索引：索引和数据是分开存放的，找到的索引指向的是数据的地址指针，而非聚簇索引对于单行的检索很快。myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

4:索引案例

索引分为：

1:普通索引（没有限制）

2:唯一索引（键值具有唯一性，比如身份证号）

3:主键索引（表中的主键id）

4:组合索引（将几个字段组合成一个索引，电话号码+名字）

//1:创建普通索引index（是最基本的索引，它没有任何限制）
--1.1在创建普通索引之前，查询
EXPLAIN select  * from bank  where age=11

DROP INDEX age_index on bank
--1.2创建索引，然后查询
CREATE INDEX age_index on bank(age)
EXPLAIN select  * from bank  where age>10 and age<33  （范围也不会命中索引）
EXPLAIN select  * from bank  where age=11

//2:创建唯一索引UNIQUE (该列必须是唯一值，可以为空)
--2.1:在创建唯一索引之前查询
EXPLAIN select  * from bank  where name='丽丽'
--2.2创建索引，然后查询(如果名字不唯一 报错)
CREATE UNIQUE INDEX name_index ON bank(name)
DROP INDEX name_index ON bank
EXPLAIN select  * from bank  where name='丽丽'
EXPLAIN select  * from bank  where name LIKE '%丽'   （like不会命中索引）
EXPLAIN select  * from bank  where name LIKE '丽%'   （like会命中索引,）


EXPLAIN select  * from bank where    age=14 or name='丽丽'
EXPLAIN select  * from bank where    age=14 and name='丽丽'


//3:主键索引
--3.1是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：
EXPLAIN select  *  FROM  bank where id=1

//4:组合索引（最左原则）将address和phone 构建组合索引(组合索引只有第一个字段存在的时候会生效)
--4.1:在创建唯一索引之前查询
EXPLAIN select  * from bank where address='Champs' and phone=12 and id=13
--4.2:创建组合索引
create index phone_addr_index on bank(phone,address);
EXPLAIN select  * from bank where address='Champs' and phone=12 (存在phone索引生效)
EXPLAIN select  * from bank where address='Champs' and phone>12 (存在phone索引生效)
EXPLAIN select  * from bank where address='Champs' or phone=12 (索引不会生效)


EXPLAIN select  * from bank where address='Champs'  (不存在phone索引不会生效)
EXPLAIN select  * from bank where phone=12  (索引会生效)
EXPLAIN select  * from bank where phone>12  (索引会生效)