(mysql学习笔记10）事务、索引

事务（Transaction）

什么是事务

一个事务是一个完整的业务逻辑单元，不可再分。

比如：银行账户转账，从A账户向B账户转账10000，需要执行两条update语句

update t_act set balance = balance - 10000 where actno = 'act-001';

update t_act set balance = balance + 10000 where actno = 'act-002';

以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败

要想保证以上的两条DML语句同时成功或同时失败，那么就需要使用数据库的“事务机制”。

和事务相关的语句只有：DML语句（insert、delete、update）

这三个语句都是和数据库表当中“数据”相关的

事务的存在就是为了保证数据的完整性，安全性

假设所有的事务都能使用一条DML语句解决，还需要事务吗？

不需要

但实际情况不是这样的，通常一个业务（事务）需要多条DML语句

事务的特性

事务包括四大特性

• 原子性：事务必须是最小的工作单元，不可再分
• 一致性：事务必须保证多条DML语句同时成功或者同时失败
• 隔离性：事务A和事务B之间具有隔离性
• 持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束

关于事物之间的隔离性

事务隔离性存在隔离级别，理论上隔离级别包括4个：

           第一级别：读未提交（read uncommitted）

                      对方事务还未提交，我们当前事务可以读取到对方未提交的数据

                      读未提交存在脏读（dirty read）现象：表示读到了脏的数据

           第二级别：读已提交（read committed）

                      对方事务提交之后的数据我方可以读取到

                      这种隔离级别解决了：脏读现象没有了

                      读已提交存在的问题：不可重复读

           第三级别：可重复读（repeatable read）

                      这种隔离级别解决了：不可重复读问题

                      这种隔离级别存在的问题是：读取的数据是幻想

           第四级别：序列化读/串行化读

                      解决了所有问题

                      效率低，需要事务排队

Oracle数据库默认的隔离级别是：读已提交

mysql数据库默认的隔离级别是：可重复读

演示事务

mysql事务默认情况下是自动提交的

• 准备表

• 演示：mysql中的事务是支持自动提交的，只要执行一条DML，则提交一次

• 演示：使用start transaction；关闭自动提交机制

索引

什么是索引 有什么用

索引就相当于一本书的目录，通过目录可以快速的找到对应资源

在数据库方面，查询一张表的时候有两种检索方式：

           第一种方式：全表扫描

          第二种方式：根据索引检索（效率很高）

索引为什么能提高检索效率

其实最根本的原理是缩小了扫描的范围

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改 这样就不适合添加索引，因为数据一旦修改，索引就需要重新排序，进行维护

添加索引是给某一个字段，或者说某些字段添加索引

select ename,sal from emp where ename = 'smith';

当ename字段上没有添加索引的时候，以上SQL语句会进行全表检索，扫描ename字段中所有的值

当ename字段上添加索引的时候，以上SQL语句会根据索引扫描，快速定位

怎么创建索引 怎么删除索引对象

创建索引

          create index 索引名称 on 表名（字段名）；

删除索引

          drop index 索引名称 on 表名；

什么时候考虑给字段添加索引（满足什么条件）

• 数据量庞大（根据客户需求，根据线上环境）
• 该字段很少使用DML操作（因为字段进行修改操作，索引也需要维护）
• 该字段经常出现在where子句中（经常根据哪个字段查询）

主键和具有unique约束的字段会自动添加索引

根据主键查询效率较高，尽量根据主键检索

查看SQL语句的执行效率

explain select ename,sal from emp where sal = 5000;

索引底层采用的数据结构是：B+Tree

索引的实现原理

通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址” 最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

select ename from emp where ename = ‘Smith’；

通过索引转换为：

select ename from emp where 物理地址 = 0x3；

索引的分类

单一索引：给多个字段添加索引

复合索引：给多个字段联合起来添加1个索引

主键索引：主键上会自动添加索引

唯一索引：有unique约束的字段上会自动添加索引

·····

索引什么时候失效

select ename from emp where ename like '%A%';

模糊查询的时候，第一个通配符使用的是%，这个时候索引会失效。