mysql索引、锁、事务、MVCC

Mysql：

索引：是帮助mysql高效获取数据的数据结构，索引数据结构，目的：提高查找效率。
1、索引：排好序的快速查找数据结构，

2、索引的优势：

提高数据的检索效率，降低数据库的IO成本
通过索引对数据进行排序，降低数据排序的成本，降低了CPU消耗

3、索引的缺点：

空间上：占内存空间：
时间上：创建和维护所用需要大量的时间，索引提高了查询速度，降低了更新表的速度，如增删改，

4、索引分类：

（1）普通索引：基本的索引类型，没有唯一性的限制，允许为null
（2）唯一索引：索引列的值必须唯一，不允许重复，但允许为null，一个表允许多个列创建唯一索引
（3）复合索引：全文索引
（4）主键索引：一个表只能有一个主键，数据列不允许重复，不允许为null，一个表只能有一个主键

5、索引结构

B树索引是用B+树实现的。
B树和B+树的区别：
（1） B树的内部节点和叶子节点都存放键和值，B+树内部节点只存放键，所有的数据都存放在叶子节点。
（2） B+树的所有叶子结点使用一条链相连，而B树所有叶子结点互相独立
使用B树的优势：
由于B树在内部节点可以存储键和数据，因此提高了热点数据的查询效率，对于特定数据频繁访问的场景更加有效
使用B+树优势：
（1） 由于B+树内部节点只存储键，不存储数据，因此一次查找可以获取更多的键，有利于快速缩小查找范围
（2） B+树中的所有叶子结点使用一条链相连，便于遍历全部数据。
哪些情况需要建立索引？
（1） 主键自动建立唯一索引。
（2） 频繁作为查询条件的子段应该创建索引
（3） 查询中与其他表关联的子段，外键关系建立索引
（4） 频繁更新的子段不适合创建索引
（5） Where条件中使用不到的子段不要创建索引
（6） 单键索引/组合索引的选择问题
（7） 查询排序中的子段，排序子段若通过索引去访问将大大提高排序速度。
（8） 查询中统计获取分组的子段
哪些情况不需要建立索引？
（1） 表的记录太少
（2） 经常增删改的表

事务的隔离主要是对事务的读写之间进行隔离，通过锁来实现事务的读写之间的隔离。
通过对事务的读写操作加锁情况的不同，划分出不同的事务隔离级别。

索引：

索引和实际数据都是存储在磁盘中，只不过进行数据读取的时候，优先吧索引架加载到内存中
客户端
服务端：
存储引擎：表示不同的数据文件在磁盘的不同组织形式
myISAM存储引擎，索引和数据分开文件存储
innodb存储引擎，索引和数据一起存储在一个文件中
mysql5.1之后，默认存储引擎为innodb

一般索引的格式为K-V，就需要一种数据结构存储K-V格式的数据，一般有hash表，数，这里为什么选择B+树？
因为索引和实际数据都是存储在磁盘中，只不过进行数据读取的时候，优先吧索引加载到内存中，但是如果索引大小超过内存大小，则无法依次读取，因此当内存无法满足的时候，出现分块读取，即分而治之思想。当硬件无法满足的时候，
因此从软件方面解决方式，要尽可能提高IO效率：从两个方面：一是减少IO量，二是减少IO次数。因此在设计索引时尽可能考虑这两个问题。
操作系统两个知识：
一是局部性原理：时间局部性：之前访问过的数据可能再次被访问。空间局部性：数据和程序都有聚集成群倾向。
二是磁盘预读：内存跟磁盘交互的时候有一个最小的逻辑单位，这个单位称之为页或者datapage，一般是4k或者8k，有操作系统决定，我们在读取数据的时候，一般会读取页的整数倍，也就是4k，8k，16k等等，innodb存储引擎在进行数据加载的时候读取的是16k的数据。
使用hash表的问题

缺点一：需要好的hash算法，如果算法不好，会导致hash碰撞，hash冲突，导致数据散列不均匀，影响存储空间
缺点二：hash表数据结构是无序的，当需要进行范围查找时需要挨个遍历，效率比较低。
内存的存储引擎支持的就是hash索引，同时注意innodb存储引擎支持的就是自适应hash。
使用树结构：

而这些二叉树的缺点是：
当需要向这些树中插入更多的数据时，会导致当前树更得更加高。当树越高，次时会加大读取的次数，影响查询效率
当使用B树时候：

B树：
假设innodb，每次读取16k，每个磁盘块包含三种类型数据：key，data，还有指针。此时B树内部节点的data占据每个磁盘块的很大部分，当数据量越大时，需要增加树的深度，因此会增加io次数，此时效率越低，因此，使用B+树，将内部节点的data去掉，存储更多的key，
B+树：

使用B+树，使得非叶子节点不存储data，只存key增大key的存储量，是的存储更多的数据
一般3-4层的B+树足以支持千万级别的数据
由于一个磁盘16k，假设指针和key为一组，大小为10字节，即10b，则一个磁盘可以存储161000/10个指针和key的组合。则三层B+树，假设第三层data为1kb，则三层B+树可以存储，1610016100*16= 40960000个数据，即4千万个数据，因此在设计索引时，尽可能让key占据更小的存储空间
聚簇索引和非聚簇索引：
数据和索引存储在一起的叫聚簇索引，没有存储在一起的叫非聚簇索引
Innodb存储引擎在进行数据插入的时候，数据必须要和某一个索引存储在一起，这个索引列可以是主键，如果没有主键，选择唯一键，如果没有唯一键，选择6字节的rowid进行存储
此时数据必定是和某一个索引绑定在一起的，绑定数据的索引成为聚簇索引
其他索引的叶子节点存储的数据不在是整行的记录，而是聚簇索引的id值。
例如有一行数据：id name age gender
其中id是主键、name是普通索引，id是聚簇索引，则name对应的索引的B+树上的叶子节点存储的就是id值
Innodb中既有聚簇索引，也有非聚簇索引
MyISAM中只有非聚簇索引
Mysql：

1、回表

：例如表中有id name age gende，其中id是主键，name是普通索引
有一个sql语句 select * from table where name = “zhangsan”;
针对这个sql语句，先根据name的B+树匹配到叶子结点，查询到对应行记录的id值，在根据id去id的B+树中检索整行记录，这个过程就称为回表。
要尽量避免回表操作

2、索引覆盖：

例如：一个表中有id name age gender
其中id是主键，name是普通索引
对于sql语句：select id,name from table where name=”zhangsan”;
首先根据name的值去name的B+树中查询相应的行记录，由于id主键，此时获取能够获取到id的属性值，索引（nameB+树）的叶子结点包含了查询的所有列（即id，name），此时不需要回表，这个过程称为索引覆盖，using index的提示信息。如果上面的查询语句包含age，则需要回表，因为没有包含所有列。
推荐使用索引覆盖：则某些场景中，可以考虑将要查询的所有列都变为组合索引，此时会使用索引覆盖，加快查询效率。

3、最左匹配

：创建索引的时候，可以选择多个列来共同组成索引，此时叫做组合索引或者联合索引。要遵循最左匹配原则：
例如：id name age gender;
Id 是主键，name，age组合索引
对于sql语句：
（1） select * from table where name=”zhangsan” and age=12;
（2） select * from table where name=”zhangsan”;
（3） select * from table where age=12;
（4） select * from table where age=12 and name=”zhangsan”;
对于这四个查询语句，只有第三个sql语句不会执行组合索引，因为要按照最左匹配的原则。name在前，age在后，先匹配name，1、2、4都可以，对于4、通过优化实现

4、索引下推：

例如：对于sql语句：select * from table where name=”zhangsan” and age=12;
如果没有索引下推：先是根据name从存储引擎层录取数据到server层，然后在server层对age进行数据过滤。
当采用索引下推之后：
根据name和age两个条件来做数据筛选，将筛选之后的结果返回给server层

Mysql事务隔离级别和锁的关系

：
1、读未提交：
允许事务读取未被其他事务提交的更新。实现方式：读事务不加锁，写事务添加共享锁（由于是共享锁，写事务未提交前其他事务仍然能读），因此会出现脏读。
2、读已提交：只允许读取已经被其他事务提交的更新。实现方式：写事务添加排它锁，阻塞其他事务的读写操作，事务结束才释放。读事务添加共享锁，读完立即释放，而不是事务结束释放（即每执行一行语句就释放一次读锁，下一行语句执行之填加共享锁）。因此造成不可重复读，读事务第一次读取数据结束后，立即释放共享锁，此时其他事务可以更新数据，该事务第二次读取数据时发现与第一次读取数据不同）
3、可重复读：在当前事务持续期间，禁止其他事务对该字段进行更新操作，确保事务可以从一个子段中读取相同的值。实现方式：写事务添加排它锁，阻塞其他事务的读写操作，事务技术菜释放。读事务添加读锁，在事务提交之后才释放读锁。读事务和写事务加的锁都持续到事务结束才释放，解决的不可重复读。仍然存在幻读（因为读事务和写事务只是在所读的行加了行锁，其他事务虽然不能更改这些行，但是可以添加新的行，因此出现幻读）。
4、串行化：在当前事务持续期间，禁止其他事务对该表进行读写（增删改查）操作。确保事务可以从一个表中读取相同的行。实现方式：对读事务添加表锁。避免其他所有事务对该表的操作。
按照锁的粒度划分为：
行级锁（Innodb引擎）、表级锁（MyISAM引擎）、页级锁（BDB引擎）
MyISAM采用表级锁
InnoDB支持行级锁和表级锁，默认为行级锁
从锁的类别分为：共享锁（读锁或者s锁）、排他锁（写锁或者x锁）

表锁：（偏读）

（1）读锁：是共享锁。读锁写阻塞
在当前session添加表的读锁：
Lock table 表名 read；
第一：当前session可以查询该锁定的表，其他session也可以查询该锁定的表。
第二：当前session不能查询其他没有锁定的表，其他session可以查询或者更新没有锁定的表
第三：当前session插入或者更新锁定的表都会提示错误，其他session插入或者更新锁定的表会一直等待获得锁，即一直等待锁定的表解锁
第四：当前session释放锁，其他session获得锁，才会进行等待的插入的或者更新表的操作
（2）写锁：
在当前session添加表的写锁，lock table 表名 write；
第一：当前session对写锁的表的增删改查都可以，其他session对于写锁表的的查询，修改被阻塞，需要等待写锁被释放。
第二：当前session释放写锁，其他session获得锁，查询返回
Mysql表锁有两种模式：
表共享读锁
表独占写锁
MyISAM在执行查询语句前，会给所有的表添加读锁，在执行增删改的操作前，会自动给涉及的表添加写锁。
总结：

MyISAM

1、对MyISAM表加读锁：对当前进程的读操作不会影响，对当前线程的进行写操作，会出错。不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求，只有当读锁释放后，才会执行其他进程的写操作。
2、对MyISAM表加写锁：会阻塞其他进程对同一表读和写操作，只有放写锁释放后，才会执行其他进程的读写操作。
读锁会阻塞写但是不会阻塞度，写锁则会吧读和写都阻塞

如何分析系统上的表锁定？

通过两个状态变量来分析表锁定：
（1）、Table_locks_immediate:产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；
（2）、Table_locks_waited：出现表级锁定争用而等待的次数（不能立即获取锁的次数，每等待一次，值加1）此值越高，说明存在着较为严重的表级锁定争用情况。
由于MyISAM的读写调度是写优先，这也是MyISAM不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新是的查询很难得到锁，从而造成永远阻塞。
行锁（偏向innoDB存储引擎）
开销大，加锁慢，会出现死锁，锁粒度最小，发生锁冲突的概率最低，并发度也是最高的。
Innodb和MyISAM的两大不同点：一是innodb支持事物，二是innodb采用了行锁。

行级锁

Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
特点：开销大，加锁慢；会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
表级锁 表级锁是MySQ中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
页级锁 是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
Mysql中innodb引擎的行锁是怎么实现的？
行锁是基于索引来实现的。Innodb中只有通过索引条件检索数据，innodb才使用的是行锁，否则innodb将使用表锁。
注意：
在不通过索引条件查询的时候，innodb使用的是表锁，而不是行锁

基础知识：
当前读：读取的是数据的最新版本，总是读取到最新的数据
快照读：读取的是历史版本的记录，

MVCC原理：

第一部分：隐藏子段

：
在sql表中，每一行的记录的上都会包含几个用户看不见的子段：
（1）DB_TRX_ID：创建或者最后一次修改该记录的事务id
（2）DB_ROW_ID：表示隐藏主键。在聚簇索引那个部分使用
（3）DB_ROLL_PTR：回滚指针，指向上一个回滚之前的历史记录，和undolog配合使用

第二部分：

undolog：回滚日志，保存的是数据的历史版本状态

假设有两个事务，当事务2修改name时，会将原始的状态存储在undolog中，然后在事务2中进行修改，同时在事务的隐藏子段中修改相应的值，还有DB_ROOL_PTR存储的是undolog中的历史版本的地址，指向undolog。
即undolog存储历史版本数据
注意：当不同的事务对同一条事务做修改的时候，会导致该记录的undolog形成一个线性表，即链表，链表头部是最新的历史记录，而链尾是最早的历史记录。
如果有一个新的事务，那么新的事务读取到的是那一条历史版本数据？这里按照一定规则进行

第三部分：readview，

事务在进行快照读的时候产生的读视图。
Readview：读视图：包含几个子段：
（1） trx_list:事务列表：系统活跃的事务id
（2） up_limit_id:列表中事务最小的id
（3） low_limit_id: 系统尚未分配的下一个事务id

实际场景：
假设有四个事务，修改同一条记录：让事务4修改该记录的某一个值，修改完成之后提交事务4，提交之后，在事务2进行快照读。能否读取到刚刚修改的记录值？

在上表之后，事务在进行快照读之后产生的读视图有三个子段：
Trx_list: 系统中活跃的事务个数，事务1，事务2，事务3，因此事务4已经提交，
Up_limit_id:列表中事务最小的id：1
Low_limit_id:系统尚未分配的下一个事务id：5，
此时当前记录中隐藏子段：
DB_TRX_ID: 4,因为事务4最后进行修改
对于以上子段，存在一个规则：
1、 首先比较：DB_TRX_ID<up_limit_id:则当前事务能看到DB_TRX_ID所在行的记录，否则进入下一步：
2、 接下来判断DB_TRX_ID>=low_limit_id，如果大于等于，则代表DB_TRX_ID所在的记录在redaView生成之后才出现的，对于当前事务肯定看不见，否则进入下一步：
3、 判断DB_TRX_ID是否在活跃事务中，如果在，代表readview生成时刻，这个事务还是活跃状态，还没有提交，修改的数据，当前的事务也看不到，，如果不在，在说明这个事务咋readview生成之前就已经开始提交了，那么修改的结果可以看到。

因此对于上表，经过当前可见性算法判断之后，可以得到结论，能看到修改的记录。
如果是RC隔离级别：能看到，
如果是RR隔离级别，看不到
以上两个最大的区别是readview生成的时机是不同的。
在RC隔离级别下，每次进行快照读的时候，都会生成新的readview
RR隔离级别下，只有在第一次进行快照读的时候才会生成readview，之后的读操作都会用第一次生成的readview

事务的四个特性：

原子性：通过undolog实现
一致性：其他三个特性共同实现了一致性
隔离性：通过MVCC实现
持久性：通过redolog实现，二阶段提交。WAL（write ahead log提前写）日志
在数据更新的时候，先写日志，在写数据。
由于数据放在磁盘，当对某一条记录进行增删改操作时，先到磁盘中找到对应的数据值，吧磁盘中的数据加载到内存中，进行修改，修改完成之后，在写到磁盘中。当从磁盘中随机读取一个数据的时候，是比较慢的，如果写不成功，丢失怎么办？这里涉及两个技术名词：
随机读写：效率慢
顺序读写：效率快
因此随机读写的效率低于顺序读写，为了保证数据的一致性，可以先将数据按照顺序读写的方式写到日志文件中，然后再将数据写入到对应的磁盘文件中，这个过程顺序的效率远远高于随机的效率。换句话说，如果实际的数据没有写入到磁盘，只要日志文件保存成功了，那么数据就不会丢失，可以根据日志进行数据的恢复
由于写日志快，查数据比较慢，为了保证数据能够进行
Mysql本身提供了一个日志binlog（二进制日志文件，属于mysql）
而innodb存储引擎最开始是插件引擎，后来集成到mysql中惹到。
但是在innodb中有两种日志：
Undolog和redolog

因为两种日志属于不同的组件，所以为了保证数据的一致性，要保证binlog和redolog一致，所以有了二阶段提交的概念。

如果一个事务只有快照读，不会出现幻读，如果一个事务既有快照读，又有当前读，则会出现幻读