MySql索引详解(三)

一、一切从事务说起
数据库的事务
事务是什么？
事务是数据库操作的最小(不可再分割)工作但，是作为单个逻辑单元执行的一系列操作
场景：转账
张三欠了李四1000块钱，张三有钱了，他就问李四要了李四的银行账号，然后往李四卡里打钱，然后数据库要执行的2条SQL语句如下：
update user_account set balance=balance-1000 where userId=3;
update user_account set balance=balance+1000 where userId=1;
这个时候我们需要保证事物的一致性，不可以说，张三账户减少了1000然后异常了，李四账户没有加到1000，张三账户钱没了
为了解决上面的问题，数据库也有提供事务管理
我们可以手工开启 begin;/start transcation
只要没有commit/rollback （提交/回滚）执行的SQL是不会对数据库的数据进行更新的
事务ACID特性
原子性：最小的工作单元，整个工作单元要么一起提交成功，要么全部失败
一致性：事务中操作的数据改变是一致的，即写入自老的结果完全符合预设的规则
隔离性：事务内所操作的数据在提交之前，对其他事物的可见性设定
持久性：事务所做的修改会永久保存，不会因为系统意外导致数据的丢失

并发事务隔离性设定问题
1.脏读
2.不可重复读
3.幻读
事务的四种隔离级别
SQL92 ANSI/ISO标准：
Read Uncommitted（未提交） --未解决并发问题
事务未提交对其他事务也是可见的，脏读
Read committed(读提交) – 解决脏读问题
一个事务开始之后，只能看到自己提交的事务所做的修改，可以重复读
Repeatable Read（可重复读） --解决不可重复读问题
在同一个事务中多处读取同样的数据结果是一样的，这种隔离级别未定义解决幻读的问题
Serializable（串行化） --解决所有问题
最高的隔离级别，通过强制事务的串行执行
innodb引擎对标准的支持

二、innodb的锁机制详解
innodb的锁是行锁
表锁 VS 行锁
锁是用于控制不同事物对共享资源的并发访问
表锁与行锁的区别：
锁定力度：表锁>行锁
加锁效率：表锁>行锁
冲突概率：表锁>行锁
并发性能：表锁<行锁
innoDB存储引擎支持行锁和表锁（另类的表锁，他会把所有的行给锁住，就是表锁，MYISAM是表锁，锁住整个表）

innoDB锁的分类
共享锁(行锁)：Shared Locks
排他锁(行锁)：Exclusive locks
意向共享锁(表锁):intention sHARED locks
意向排它锁（表锁）:intertion Excluseive Locks
共享(S)锁和排他(X)锁
共享锁：
读锁(S锁)
多个是为对应同一数据可以共享访问，不能操作修改
使用方式：
SELECT * FROM users WHERE user_id = 1 LOCK IN SHARE MODE;加锁
COMMIT/ROLLBACK 释放锁
排他锁：
写锁(x锁)，互斥锁/独占锁
事务获取了一个数据的X锁，其他事务就不能再获取该行的锁（S锁，X锁），只有该锁取了排他锁的事务是可以对数据进行读取和修改
使用方式：
DELETE/UPDATE/INSERT 语句默认加上X锁
SELECT * FROM users WHERE user_id = 1 FOR UPDATE; 加锁
COMMIT/ROLLBACK 释放锁
innodb行锁如何实现
innoDB行锁是通过给索引上的索引项加锁来实现的
通过索引进行数据检索，innoDB才使用行级锁，不然innoDB将使用表锁(锁住索引的所有记录)

意向共享（IS）锁和意向排他（IX）锁
意向共享锁(IS)
一个数据行加共享锁前必须先取得该表的IS锁，意向共享锁之间是可以相互兼容的
意向排他锁（IX）
一个数据行加排他锁前必须先取得该表的IX锁，意向排它锁之间是可以相互兼容的
意向锁（IS，IX）是innoDB引擎操作数据之前自动加的，不需要用户干预
意义：
当事务想去进行锁表时，可以先判断意向锁是否存在，存在则可快速返回该表不能启用表锁

行锁算法之 临键锁
Next-key locks
当SQL执行按照索引进行数据的检索时
且查询条件为范围查找(between and ,< , >等)
且有数据命中时，该SQL语句加上的锁为Next -key locks
具体实现：锁命中记录区间+下一个区间(区间：左开右闭)

行锁算法之 间隙锁
Gap Locks
当SQL执行按照索引进行数据的检索时
且查询条件不存在时，
这时SQL语句加上的锁即为Gap locks
具体实现：锁住数据不存在的区间(区间：左开右开)

行锁算法之 记录锁
Record locks:
当SQL执行按照唯一性(Primark key,Unique key) 索引进行数据的检索
且查询条件等值匹配且查询的数据命中存在
这时SQL语句加上的锁即为Record Locks
具体实现：锁住具体索引的索引项