Mysql的锁

数据库里面的锁是基于索引实现的，命中索引就是一个行锁，没有命中则是一个表锁。

按照属性：共享锁、排他锁
按照粒度：表锁、行锁、间隙锁、记录锁、临键锁
按照意向：意向共享锁、意向排他锁

共享锁（Share S锁）

开启事务加上一个读锁之后，其他事务只能对该记录加读锁而不能加写锁，需要等到事务完成之后才能获得写锁。主要为了支持高并发的访问。

排它锁（eXclusive X锁）

加上排他锁之后及即不允许读，也不允许写，可以避免脏读脏数据的问题。

表级锁

表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问；
特点：粒度大，加锁简单，容易冲突；

行级锁

行锁是指上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问；
特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高；

记录锁

记录锁(Record Lock) 记录锁也属于行锁中的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁住的只是表的某一条记录。
触发条件：精准条件命中，并且命中的条件字段是唯一索引；
例如：update user_info set name=’张三’ where id=1 ,这里的id是唯一索引。
记录锁的作用：加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。

间隙锁

为了防止幻读问题，在表的ID之间如果出现空隙就会形成一个左开右闭的区间。举个例子1、5、6、11 就会形成-无穷到1，1到5，6到11这样的间隙空间，如果使用索引查询这个区间就会锁定这个ID区间之内的索引，不能再修改，防止insert造成的幻读问题

临键锁

连带间隙和下一个区间一同锁住。

意向锁

加共享锁和排它锁之前需要先尝试获得意向共享锁和意向排它锁。意向锁的作用，相当于就是在低层次资源是否使用，加了一个tag来标识而已。对于步骤2的执行可以大大加速，仅此而已。
有了意向锁之后，事务A的操作步骤如下：
1）事务A上行锁时，先对表上一个写意向锁。
2）事务A对r行上一个写锁。
我们假设一个场景，一个事务A正在写某个表T的第r行加了写锁，另一个事务B尝试去对整个表做操作（例如修改表结构的时候），B尝试去对整个表加一个写锁。则此时，需要执行两个步骤：

事务B想对表上写锁，操作步骤如下：
1）检查表T是否被上了读锁或写锁。
2）检查表是否被上了意向锁（因为所有行的读写之前，会先上意向锁），即可判断表中是否有任何一行被上了读锁或写锁。步骤2在检查逻辑的时间复杂度，由于增加了意向锁之后，时间复杂度由O(n)，提升到了O(1)，效率大幅提升。另外，从这个逻辑来看，读和写的意向锁，两者是彼此兼容，当时读到这他们之间的兼容关系的时候，一直不太理解，没理解到点上。

索引的分类

MVCC机制

MVCC叫做多版本并发控制，在数据库中实现并发的数据访问。

当前读
像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务
快照读
快照读是为了解决读-写冲突不加锁而出现的。同时解决了脏读、幻读、不可重复读等事务隔离问题，但不能解决数据更新丢失问题。

隐式字段

DB_TRX_ID
DB_ROLL_PTR

每行记录除了我们自己设计的数据库表结构之外，还有三个隐式字段DB_TRX_ID（最后一次修改这个记录的事务ID）、DB_ROLL_PTR（指向未被修改的该记录的值）、DB_ID（隐式主键，如果没有创建主键，会自动生成一个6字节长度的主键）

UndoLog

update undoLog，在update操作的时候产生，可以用于事务回滚和快照读。
倘若有一个事务对该条记录进行了修改，再次修改后

Read View

事务对某个记录进行读操作的时候产生的快照视图，用来控制该事务能够看到该记录undolog的哪一阶段。比如此刻事务2去访问该条记录，这条记录最终被事务四所修改。进入逻辑判断。

//活跃事务
List<Work> activeList
//活跃事务中最小的事务ID
Integer minWorkID
//ReadView生成时刻系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1
Integer nextWorkID

boolen changes_visible(work,workID) {
	//work4 workId = 4
	if(workID < minWorkID) {
    	//可以看到,work生成在minWord之前
   } else if(workID >= nextWorkID){
        //不可见,work在ReadView生成后出现的
   } else if(activeList.contains(work)) {
        //不可见
   } else {
        //可见的
   }
}

4>minActive，不能判断具体情况，4<nextWorkId,只能说明4是在ReadView形成之前commit的，继续判断是否活跃。不在activeList中则2能看到最新的记录。

总结mvcc

说一说你对于MVCC的理解

MVCC叫做多版本并发控制，用来实现mysql读-写冲突不加锁访问，是一种基于隐式字段、undoLog和Read View实现的快照读取策略。

mysql每条数据记录都有两个隐藏字段，最后一次修改该条记录的事务ID字段，一个是回滚指针，指向上一次未修改的记录。undolog，每个事务update一条记录后，产生一条记录，记录更新后的内容和之前的全部的记录状态。

某一时刻一个事务访问一条记录会产生一个ReadView，记录当前活跃的事务、当前活跃事务中最小的事务ID，ReadView生成时刻mysql未分配的下一个事务ID。2访问这条记录，最后一次修改这个事务的id是4，若4<minActiveID,则说明事务是在4提交之后形成的，可以看到。如果4>minActiveID继续判断4是不是大于未分配的下一个事务ID，是的话说明4是在readView形成之后出现的，该条记录不可见。4<minActive判断其是否活跃，如果是活跃状态说明事务还未提交，改记录不可见，不活跃说明事务已经提交是可见的。

创建高性能的索引

索引相当于一本书的目录，如果想在一本书中找到某个特定的主题，一般会先看书的索引，找到对应的页码。

独立的列

列中不要包含运算值

mysql> 
SELECT 
actor_id 
FROM sakila.actor 
WHERE actor_id + 1 = 5

前缀索引和索引选择性

select 
COUNT(DISTINCT LEFT(city,3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(city,4))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(city,5))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(city,6))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(city,7))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(city,8))/COUNT(*) AS sel3,
From table

当前缀达到一个值后，后续的sel变化不再明显，这个值就可以作为索引的长度

//创建前缀索引
mysql> ALTER TABLE sakila.city_demo ADD KEY (city(7));

复合索引的情况

不要让index_merged这种情况出现

选择合适的索引顺序

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584

在创建索引的时候，将分辨度更高的staff_id放到前面

mysql> ALTER TABLE payment ADD KEY(customer_id,staff_id);

使用索引的注意事项

1.在经常需要搜索的列上建立索引，加快索引的速度

2.在经常使用where的列上按照顺序创建索引，加快条件判断的速度

3.在经常需要排序的列上创建索引，利用索引的排序，加快排序查询时间

4.在经常连接的列上创建索引，加快链接的速度

5.不要在where子句中对字段释加函数，会造成无法命中索引

选择索引和编写利用这些索引的查询原则

读出的数据块中要尽可能包含多的所需要的行
按照顺序访问范围数据是十分快的，第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多。第二、如果服务器能够按照需要顺序读取数据，就不再需要额外的排列
尽量使得索引覆盖

最左前缀原则

Mysql中的索引可以以一定的顺序引用多列，这种索引叫做联合索引。如User表的name和city加联合索引就是（name，city），而最左前缀原则指的是，如果查询的时候条件精确匹配索引的左边连续一列或者几列，则此列就可以被用到。

避免冗余索引

在Mysql5.7版本之后，可以通过查询sys库的schame_redundant_indexes查看冗余索引

主键和唯一索引的区别

主键索引只能有一个，而唯一索引可以有多个
主键索引的字段必须是NOT NULL的，而唯一索引的值可以是为NULL的
主键索引的列可以作为外键，而唯一索引是不可行的

//主键索引
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`)
//唯一索引
ALTER TABLE `table_name` ADD UNIQUE (`column`)
//添加普通索引
ALTER TABLE `table_name` ADD INDEX index_name(`column`)
//添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name (`column1`,`column2`,`column3`)

唯一索引和普通索引的区别

普通索引查到符合条件的项后会继续查询
唯一索引查到符合条件的项后会停止
查询性能相似
普通索引是可以使用ChangeBuffer的，更新数据的时候，如果数据页不在内存中，可以先更新到change Buffer中，下次需要访问这个数据页面的时候将数据页读入内存中，然后执行。change buffer是为了减少和磁盘的I/O次数，将change buffer合并到原数据页的方式称为merge，数据库的关闭、访问普通索引、或者隔一段时间都会触发merge操作

mysql原子性是如何实现的

redoLog（持久性）

mysql为了性能并不会把每次的修改都实时同步到磁盘中，而是先缓存到缓存中，然后再使用线程去做到缓冲池和磁盘的同步，如果突然停电就会出现问题，那么没有持久化到磁盘的信息也会丢失，事务提交会把所有的信息存放到redo Log中，并且把redo Log持久化到磁盘当中去，当电脑重启就会重新执行redo Log完成持久化

undoLog（原子性）

undo Log称之为回滚日志，每条数据的变化都会产生一条记录，并且持久化到磁盘，undoLog用来记录一条删除该信息的语句，这样需要回滚的时候undo log就会执行删除之前插入的记录

mysiam的适用场景

MyISAM 对事务一致性要求不高，主要用来执行查询操作，比如日志类的

Mysql锁和索引