3MySQL数据库- MySQL高级 - 事务 -索引 - 账户管理 - 全栈式开发31

一、事务（transaction）

（一）事务介绍

引入

• 例如一个银行的数据库有两张表：支票表和储蓄表。现在要从用户lane的支票账户转移200美元到她的储蓄账户

  • 1.检查支票的账户余额>200
  • 2.支票汇总扣除200
  • 3.储蓄汇总增加200

   1.检查支票账户的余额高于或者等于200美元
   SELECT * FROM checking WHERE money >=200;
   2.从支票账户余额中减去200美元
   UODATE checking SET money = money - 200 WHERE NAME = 'jane';
   3.在储蓄账户余额中增加200美元
   UPDATE savings SET money = money + 200 WHERE NAME =  'jane';
  

• 正常的流程走下来，支票账户扣了200元，储蓄账户增加200，皆大欢喜

• 那如果账户扣了钱之后，系统出现故障了呢？白白损失了200，而储蓄账户也没有收到本该属于它的200

• 以上的案例隐藏着一个前提条件：支票扣了200元与储蓄增加200，要么同时成功，要么同时失败，事务的需求就在于此，事务就能解决这个问题

• 在上面的操作必须打包到一个事务只能中，任何一个失败了，就回滚所有

定义

• 所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行
  它是一个不可分割的工作单位
• 事务的引擎必须是innodb，mysql默认的，其他不支持事务

作用

• 事务广泛的运用于订单系统、银行系统等多种场景

（二）事务的四大特性

• 事务的四大特性：要记大概的
  • 原子性（A：atomicity）
    一致性（C：consistency）
    隔离性（I：isolation）
    持久性（D：durability）

  • 原子性：
    一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

  • 一致性：
    数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户也不会平白失去200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中）

  • 隔离性
    通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的，就是其他客户端看不到没有提交事务的修改内容。包含事务的隔离级别问题，有兴趣可以进行扩展（在前面的例子中，当执行完第二条语句，第四条语句还未开始，此时另外一个的程序开始运行，则其看到支票账户的月并没有被减去200美元）
    同时开启两个事务，两个分别查不到还未被提交的语句结果

  • 持久性
    一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失）

（三）事务的状态

• 我们现在知道事务是一个抽象的概念，它其实对应着一个或多个数据库操作，设计数据库的人根据这些操作所执行的不同阶段把事务大致上划分为了这么几个状态
• 随着事务对应的数据库操作执行到不同那个阶段，事务的状态也在不断的变化，一个状态转化如下图所示：
  

活动的（active）

• 事务对应的数据库操作正在执行的过程中时，我们就说该事务处于活动的状态
• 开启事务，操作过程

部分提交的(partially commited)

• 当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘，我们就说该事务处于部分提交的状态

失败的(failed)

• 当事务处于活动的或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者认为的停止当前事务的执行，我们就说该事务处于失败的状态

中止的(aborted)

• 如果事务执行了半截而变为失败的状态，当 狗哥账户的钱被扣除，但是债主账户的钱没有增加时遇到了错误，从而当前事务处在了失败的状态，那么就需要把已经修改的狗哥账户余额调整为转账之前的余额，换句话说，就是撤销失败事务对当前数据库造成的影响，书面一点的话，我们把这个撤销的过程称之为回滚。当回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了中止的状态

提交的(commited)

• 当一个处于部分提交的状态的事务将修改各国的数据都同步到磁盘上之后，我们就可以说该事务处在了提交的状态
• 如果并没有开启事务，每个sql语句都是一个事务，默认是自动提交的，set autocommit = on

show variable like ' autocommit'
# set autocommit = on 默认
# 建议不要关闭，要不然执行所有语句都需要commit才能看到
set autocommit = off

（四）事务的实现

（1）开启事务

begin;

• 可增删改查

start transaction;

• 修饰符 read only; read write; 指定事务开启方式

（2）回滚

rollback

• 要提交前回滚

（3）保存点

定义

• 如果那你开启了一个事务，并且已经敲了很多的语句，忽然发现上面一条语句有点问题，你只好使用rollback语句来上数据库状态恢复到执行之前的样子，然后一切从头来过，总有一直能够一夜回到解放前的感觉，所以设计数据库的人们提出了一个保存点（savepoint）的概念，就是在事务对应的数据库语句中打出几个点，我们在调用rollback语句时可以指定会回滚到哪个点，而不是回到最初的原点
• 就是能够有选择的撤销还未提交的部分语句，而不是撤销全部的语句

创建保存点

• savepoint 保存点的名字;
• 名字不要取中文，名字只是一个节点的作用，作用于rollback

实现

• rollback to 保存点的名字;
  回滚到保存点的位置

删除保存点

• release savepoint 保存点的名字

注意

• 修改数据的命令会自动的触发事务，包括insert、update、delete
• 而在SQL语句中有手动开启事务的原因是：乐意进行多次的数据的修改，如果成功一起成功，否则一起会回滚到之前的数据，类似打包一起的

（五）事务的隔离级别

隔离级别

• 隔离性其实比想象要复杂，在sql中定能够以了四种隔离的级别，每一只能够隔离级别都规定能够了一个事务中的修改，哪些是在事务内和事务间可见的，哪些是不可见的。较低级别的隔离通常来说能承受更高的并发，系统的开销也会更小

查看当前的事务级别

• select @@tx_isolation;

设置mysql的隔离级别

• set session transaction isolation level 设置事务隔离级别

分类

• READ UNCOMMITTED（未提交读 ）
  • 在READ UNCOMMITTED级别，事务的修改即使没有踢几脚，对其他事物也都是可见的。事务的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读。这个级别的隔离会导致很多问题，虽然在性能方面是最优的，但是缺乏其他级别的很多好处，所以这种隔离的级别很少在实际中应用

    # READ UNCOMMITTED实践，开启两个mysql session，并将mysql的默认隔	离级别设置为READ UNCOMMITTED，两个能相互读取未提交的修改语句效果
     set session transaction isolation level read uncommitted;
    

• READ COMMITTED（读已提交）
  • 大多数数据库系统默认的隔离级别都是READ COMMITTED（但mysql不是），“读已提交”简单的定义：一个事务只能看见已经提交的事务的修改结果，换句话说，一个事务从开启事务到提交事务之前，对其他事务都是不可见的，因此在同一个事物中的两次相同查询将结果可能不一样，故这种隔离级别有时候也叫不可重复读（nonrepeatable read）。
  • 当前事务可能查询结果多次结果会不一样，因为其他事务提交后，当前事务就会跟着改变

    set session transaction isolation level read committed;
    

• REPEATABLE READ（可重复读）
  • 可重复读是mysql的默认事务隔离级别。REPEATABLE READ解决了脏读的问题，该级别保证了再同一次事务中多次查询相同的语句结果是一致的。但是可重复读隔离级别无法避免产生幻行（plantrow）的问题，mysql的innoDB引擎通过多版本并发控制（MVCC）解决了幻读的问题
  • 当前事务查询结果都是一样的，其他事务提交了，当前事务的查询结果也不会改变

    set session transaction isolation level repeatable read;
     
    

• SERIALIZABLE（可串行化）
  • SERIALIZABLE是最高的隔离级别，它通常通过强制事务串行，避免了前面说的幻读问题，简单来说，“可串行化”会在读取每一行数据上都加锁，所以可能会导致大量的所等待和超时问题，所以在实际的生产环境中很少会用到这个隔离级别，只有在非常需要确保数据的一致性且可以接受没有并发的情况下，才会考虑使用这个隔离级别
  • 可串行化是通过对每一行数据都加锁的方式来避免你能幻行问题，这种方式效率非常的低，很容易造成长时间的锁等待
  • 当前事务在修改未提交事务的时候，会阻塞其他事务修改，同时也查不到修改内容，锁的是整张表，但如果都没有进行修改操作的话，仍然可以正常查询。
• 谁先开启事务顺序不是阻塞的顺序，谁先修改的才是，锁的是整张表，不是某各行

   set session transaction isolation level serializable;
  

（六）隔离级别案例测试

create table money(id int unsigned not null primary key auto_increment,money int unsigned not null);

insert into money values(1,100),(2,200),(3,0);
-- 开启两个事务，查看事务之间的隔离性
begin;
update money set money = money-100 where id = 1
select * from money
commit;

begin;
update money set money = money+100 where id = 3
select * from money
commit;

READ UNCOMMITTED（未提交读 ）

READ COMMITTED（读已提交）

REPEATABLE READ可重复读

• 默认是REPEATABLE READ可重复读
  SERIALIZABLE（可串行化）
  

二、索引

引入

• 思考：在图书馆中如何找到一本书，当数据库中数据量很大时，查询数据会变得很慢
• 优化方案：索引
• 除了字典，生活中随处可见索引的例子，如火车站的车次表，图书的目录等，它们的原理都是一样的，通过不断的缩小想要数据的范围来筛选出最终想要的结果，同时把随机的时间变成顺序的时间，也就是我们总是通过同一种查询方式来锁定数据。
• 数据库也是一样，但显然要复杂很多，因为不仅面临等值查询，还有范围查询（>、<、between、in）、模糊查询（like）、并集查询（or）等等。数据库应该选择怎么样的方式来应对所有的问题呢

定义

• 索引也是一个特殊文件（innoDB数据表上的索引是 表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针
• 更通俗的说，数据库索引好比是一个书前面的目录，能加快数据库的查询速度

作用

• 索引的目的在于提高查询效率，可以类比字典，如果要查““mysql””这个单词，我们肯定要定位到m字母，然后从上往下找到y字母，在找到剩下的sql
• 一般的引用系统对数据库的读写比例在10:1左右（既有10次查询操作有1次写的操作）
• 而且插入操作和更新操作很少出现性能问题
  遇到最多、最容易出问题还是一些复杂的查询操作，所以查询语句的优化显然是重中之重

索引的原理

• b+树：会帮我们分成小部分，通过小范围切分
  

（一）查看索引

show index from 表名；

• 主键默认就建立了索引

show index from goodes

（二）创建索引

create [unique] index 索引名字 on 表名字(字段);

• [unique] 创建唯一索引，字段不能重复
• 字符串的话可以指定字符串的长度，不写也可以，例如name(20)
• 指定索引方式
  • innoDB只支持BTree
  • memory支持HASH等值索引和BTree范围索引，默认HASH等值索引
    • create index idx_titile using btree on 表(字段)

（三）删除索引

• drop index 索引名字 on 表名;

三、账户管理

• 在生产环境下操作数据库时，绝对不可以使用root账户链接，而是创建特定的账户，授予这个账户特定的操作权限，然后链接进行操作，主要的操作就是数据的crud
• MySQL账户体系：根据账户所具有的的权限不同，MySQL账户可以分为以下几种
  • 服务实例级账号：启动了一个msql，即为 一个数据库实例；如果某账户如root，拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库，联通这些库中的表
  • 数据库级别账号：对特定数据库执行增删改查的所有操作
  • 数据表级别账号：对特定表执行增删改查等所有操作
  • 字段级别的权限：对某些表的特定字段进行操作
  • 储存程序级别的账号：对存储程序进行增删改查的操作做
• 账户的操作主要包括创建账户、删除账户、修改密码、授予权限等
• 这个部分作为了解，该部分一般都是管理人员、运维操作

（一）用户基本信息创建

创建用户

• CREATE USER ‘username’@host’ IDENTIFIED BY ‘password’;
• 说明：
  • username：登录的用户名
  • password：登录的密码
  • host：指定可以登录的主机，其中localhost表示本机，%表示所有主机
    • 访问主机通常使用能够百分号%表示账户可以使用任何ip登录访问次数据库
    • 访问主机可以设置成localhost或具体 ip，表示只允许有特定主机访问

修改用户

• mysql修改用哪个户的信息使用alter user 语句，比如我们要修改当前用户的密码

alter user() identified by '123456'

删除用户

• drop user [if exists] user[,user] …
• 删除用户，要用root用户进行删除

drop user zs;

（二）授予权限

• 需要使用实力级别账户登录后操作，以root为例
• 常用权限主要包括：create、alter、drop、insert、update、delete、select
• 如果分配所有权限，可以使用 all privileges

创建账户权限

• grant 权限列表 on 数据库 to ‘用户名’@‘访问主机’ identified by ‘密码’;
  • 刚创建用户只有默认的数据库，并且不能自己创建数据库，没有任何权限
  • 使用grant进行授权时，如果该用户不存在，可以在grant后面跟上identified by直接创建该用户，不过如果在创建用户时要设置更详细的信息，则应该使用create user语句来创建用户

权 限	作用范围	作 用
all	服务器	所有权限
select	表、列	选择行
insert	表、列	插入行
update	表、列	更新行
delete	表	删除行
create	数据库、表、	索引 创建
drop	数据库、表、视图	删除
reload	服务器	允许使用flush语句
shutdown	服务器	关闭服务
process	服务器	查看线程信息
file	服务器	文件操作
grant option	数据库、表、存储过程	授权
references	数据库、表	外键约束的父表
index	表	创建/删除索引
alter	表	修改表结构
show databases	服务器	查看数据库名称
super	服务器	超级权限
create temporary tables	表	创建临时表
lock tables	数据库	锁表
execute	存储过程	执行
replication client	服务器	允许查看主/从/二进制日志状态
replication slave	服务器	主从复制
create view	视图	创建视图
show view	视图	查看视图
create routine	存储过程	创建存储过程
alter routine	存储过程	修改/删除存储过程
create user	服务器	创建用户
event	数据库	创建/更改/删除/查看事件
trigger	表	触发器
create tablespace	服务器	创建/更改/删除表空间/日志文件
proxy	服务器	代理成为其它用户
usage	服务器	没有权限

查看用户哪些权限

• show grants from laowang@localhost;

实例1

• 创建laowang的账号，密码为123456，只能通过本地访问，并且只能对jing_dong数据库中的所有表进行读操作
• 可以操作数据库的所有表：jing_dong.*
  step1：适用root登录

grant select on jing_dong.* to 'laowang'@'localhost' identified by '123456';