mysql 索引级别_【Java笔记】MySQL索引与隔离级别

MySQL索引与隔离级别

前言：以下内容均基于MySQL(5.0之后)默认存储引擎InnoDB

一：InnoDB行存储

在InnoDB中，数据是存储在页(page)中，每个页可以存放多条记录，这些页以树型结构组织(B+树)，维护表中所有数据的这棵B树索引称为聚集索引，在InnoDB引擎中，若一张表有主键，则会以主键生成聚集索引。若一张表中没有主键，则寻找值唯一的列生成聚集索引，若既没有主键，也没有值唯一的列，则会产生一个隐藏字段(row-id)来生成聚集索引。

聚集索引是指数据表中每一行的物理顺序与键值的逻辑顺序相同，一张表只能有一个聚集索引。

除了自动生成的聚集索引，用户可以自定义索引来对MySQL数据库进行调优，用户自定义的索引包含两类单列索引和组合索引，主键索引就是聚集索引，也包含在单列索引中。

单列索引又分为唯一索引(允许有空值)和普通索引，唯一索引是对值唯一的列生成的索引，普通索引是对除了主键和唯一值列之外其它列生成的索引，普通索引会在构建索引树时自动加入主键构建索引。

组合索引是一个索引包含两个或者两个以上的列。

创建索引的方式：

普通索引：

CREATE INDEX index_name ON 'TableName'('字段名');

唯一索引：

CREATE UNIQUE INDEX index_name ON 'TableName'('字段名');

组合索引：

CREATE INDEX index_name ON 'TableName'('字段名','字段名',......);

在组合索引中，采用最左前缀原则，只有左边部分匹配了索引，才能使用索引进行检索。

二：行格式

在InnoDB(MySQL5.0之后)中有三种行格式(Compact、Compressed、Dynamic)

这里以Compact举例

变长字段长度列表NULL标志位记录头信息列1数据列2数据。。。。

变长字段长度列表：按顺序存储变长字段的长度，小于2字节

NULL标志位：按顺序存储行数据中是否有NULL值，有则置为1

记录头信息：固定占5字节

在行格式中主要重要的三个信息是row-id(行id，6字节)、transaction-id(事务id，6字节)、roll-pointer(回滚指针，7字节)，在之后会用到。

这三种行格式在主要特性上没太大区别，主要区别在行溢出上：

行溢出：当一行的数据内容过大时，可能一行的空间放不下，甚至一页的空间(16kb)也放不下这时便会行溢出。

在Compact行格式中，若发生行溢出，则会从下一行开始，将数据分割，一行一行存储数据。

而在Compressed和Dynamic中，一旦发生行溢出，就会采用完全溢出的形式，即之前行只存放20字节的指针，将所有数据放入新开辟的页中保存。

而Compressed和Dynamic的区别是，Compressed在存储数据时会采用压缩算法进性存储，节省空间，但是对cpu的要求更高。

三：页目录与目录页

在InnoDB中，尽管产生了聚集索引，但是由于数据存储的分页机制，一旦数据量过大时，查询效率也会很低，这时，对于每一页数据来说，变产生了页目录，页目录将数据进行分组，缩小搜索范围，提高在本页搜索数据的效率。

而数据量大时会导致页数过多，于是同理产生了目录页，它将每一页进行编号，同理形成目录页，增加检索效率。

存储结构一般如下：

此页最小的数据

页码

四：MySQL事务

事务的四大特性：

原子性：事务要么成功要么失败，没有中间状态

隔离性：事务之间互不影响

一致性：当前事务完成时，必须使所有的数据具有一致的状态

持久性：数据永久存储，即使服务器崩溃数据也不会丢失，数据始终被写入外部

原子性和一致性保证了隔离性

事务的隐式提交：在事务执行过程中发生了表结构的更改或者数据库结构的更改，事务会默认提交。

回滚点：执行事务时保存回滚点，在回滚时不需要全部回滚，生成回滚点代码：

SAVEPOINT POINTNAME

五：事务的隔离级别

MySQL中事务有四个隔离级别：

读未提交：可以读取别的事务还没提交时修改的记录，产生脏读、不可重复读、幻读

读已提交：只读取别的事务已提交的记录，产生不可重复读和脏读

可重复读：MySQL默认的隔离级别，解决了脏读、不可重复读，注意：MySQL在此级别解决了幻读的问题。

串行化：对于操作同一条记录的事务实施串行运行，不可用于高并发。

注：

脏读：读取了不真实的数据

不可重复读：对于同一条数据，两次读取结果不同

幻读：对于同一条查询语句，两次查询出的行条数不同

六：MySQL事务隔离原理

在MySQL中，能够保证事务的隔离级别的基础便是MVCC(多版本并发控制)，多版本并发控制是一种用来解决读写冲突的无锁并发控制，也就是说为事务分配单向增长的时间戳，为每一次修改保存一个版本，版本与时间戳相关联，读操作只读取该事务开始前的数据库快照。

原理：前面提高在行格式中的两个数据：事务id和回滚指针

在可重复读级别中，一个事务读取一行数据，会将当前操作此数据的所有事务的事务id记录在一个数组中m_ids[]，而数据库本身会生成一个由数据、事务id、回滚指针组成，并按照时间戳排序的版本链，这个事务第二次读取数据时，会忽略点数组中已存在的事务id或版本链中新加入的事务，只读取第一次读取的结果，便解决了脏读和不可重复读。

而在MySQL中可重复级别是证明解决幻读的呢？

这里便涉及到MySQL中的锁了

七：MySQL中的锁

MySQL中对于每一行来说，有读锁(S锁)和写锁(X锁)两种，它们的关系如下：

XS

X

冲突

冲突

S

冲突

不冲突

值得注意的是，普通select语句不加锁，便不会起任何冲突。

update语句和delete语句都会对数据加写锁

insert语句会对数据加隐式锁，在insert事务未提交时，如果别的事务要来操作已修改但未提交的数据，便会加上隐式锁。

可以通俗的理解为update、insert、delete语句都会给行数据加写锁

怎么加锁呢：

读锁：

select ... lock in share mode

写锁

select ... for update

而MySQL中可重复读级别是怎么解决幻读的呢？

这里就要涉及到MySQL的行锁机制了

MySQL中行锁分为行锁、间隙锁、范围锁

行锁顾名思义就是锁一行

间隙锁锁间隙，举个例子，数据库中有主键为4和主键为6的数据，现在你要查找4到6的数据，这时，尽管没有主键为5的数据，但是数据库会锁住主键为5的间隙，即其它事务不能插入主键为5的数据，即锁住这个间隙。

范围锁即为行锁加间隙锁，即锁行也锁间隙。

最后要说的是表锁，表锁会和行锁冲突，一张表加入了行锁便不能加入表锁，这里就要涉及到一个问题了，一张表有许多行，要判断其中有没有行锁，便需要遍历全部数据，效率十分低，在这种情况下，MySQL引入了另一种锁：意向锁，包括读意向锁(IS)和写意向锁(IX)，在对某一行加写锁或读锁时，该事务会自动给整张表加意向锁，加了意向锁后便不能再加入表锁，也就是说，其实表锁是和意向锁冲突。

本文为本人学习时做的笔记，如有错误，欢迎指正！