mysql索引与事务笔记_MySQL学习笔记-数据存储、索引、事务、锁等

InnoDB引擎数据存储

数据库中数据存储的基本单位是一张表中的一条记录，记录是按照 行 为单位存储的，但数据库加载磁盘数据到内存中并不是以 行 为单位。InnoDB中按照页来存储数据，默认情况下，一页大小为16KB。数据库从磁盘中加载数据到内存中，不论读一条数据还是多条数据，都是将整个页加载到内存中。

1. 行记录在InnoDB中是如何进行存储的？

MySQL数据库中表的数据都是以“记录”为单位展示的，我们存储也是以记录为基本单位，也就是表中一条一条的数据。记录在磁盘上的存储方式被称为“行格式”或“记录格式”。

一条记录数据的存储可以分为两个部分：“额外信息”和“真实数据”。额外信息用来描述记录，分为变长字段、Null值列表和记录头信息

变长字段：MySQL中一些字段的数据类型长度不固定，比如VARCHAR(n)或TEXT。这些变长字段的真实长度都需要在这里。各变长字段数据占用的字节数按照列的顺序逆序存放

NULL值列表：记录中的某些列可能会存放null值，将这些null值都进行物理存储比较浪费空间，可以在null值列表里面存储这些列的标识，同样是按顺序逆序存放。

记录头信息：一共五个字节大小。

数据库会为真实数据部分添加一些隐藏列，用于完成数据快速查找、事务提交、回滚等操作

列名真实名称是否必须占用空间描述

roll_id

DB_ROW_ID

否

6字节

唯一标识一条记录ID

transaction_id

DB_TRX_ID

是

6字节

事务ID

roll_pointer

DB_ROLL_PTR

是

7字节

回滚指针

DB_ROW_ID：我们知道它是可有可无的，这跟Innodb主键的生成策略有关。Innodb优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。为什么InnoDB非要生成主键呢？因为数据库为了更好的进行范围查找和数据匹配，总是将记录按照主键从小到大存储的，有序对于数据查找非常重要，记录存储有序了，就能高效使用二分查找策略了。

DB_TRX_ID：用于记录记录的版本

DB_ROLL_PTR：指向undo log中的某个节点用于回滚

2. InnoDB中行记录是如何组织起来存储在页中的？

数据页的结构：

使用File Header和File Tailer来界定一个页的范围。而且在File Tailer部分，存储了页面的校验和、日志序列位置(LSN)，它们和File Header中存储的校验和与LSN相对应。Header 中还存储了该页属于哪个表空间，指向上一个页和下一个页的指针。

在物理存储上，各个数据页之间不一定是顺序存放的；但是MySQL会尽可能保证在物理空间上连续。

行记录存储在User Records部分；初始页中并没有行记录，随着记录的增加，User Records就会占用Free Space，直到Free Space变为零。这时要继续增加记录，就会产生一个新的页。

在User Records中存储的记录是按照设定的主键大小顺序存放的，物理位置也是连续的。

什么情况下索引会失效

like的模糊查询以%开头

如果MySQL预计使用全表扫描要比使用索引快，则不使用索引

如果索引列是可空的，是不会给其建立索引的

条件上包括函数也不会走索引，因为索引在建立时和计算后可能不同

索引

1. B+树和B树的区别

B+树的中间节点不保存数据，而B树的中间节点要保存数据，这使得B+树得层级更少，因为每个非叶子节点存储的关键字数更多

B+树查询必须 查询到叶子节点，B树只要匹配到即可不用管元素位置

B+树的范围查询更简单，只需要遍历叶子节点链表即可，B树却需要重复的中序遍历

如果非关系型数据库单点查询比较多的话，使用B树则更合适

在InnoDB中采用B+树来建立索引。一个三层的B+树就可以标识上百万条数据。

一般来说索引本身也很大，因此不可能全部存储与内存中，而只是在需要的时候才从磁盘去去除数据到内存中进行处理。这样，在索引查找的过程中就会产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级(磁盘访问有寻道时间和旋转时间)，所以评价一个数据结构作为索引的优劣的最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。

为了提高效率，磁盘往往不是严格按需存取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序往后读取一定长度的数据放入内存(局部性原理)。预读的长度一般为页的整数倍(计算机管理存储器的逻辑块)。

在InnoDB存储引擎中，记录也是按页(数据库中的页，大小和操作系统中的页相同)来进行存储的。这样在索引检索下，每次最多需要检索h个节点(h为树的高度)，每个节点的大小就是一个页的大小。

在InnoDB中，数据文件本身就是一个索引文件。因为InnoDB会根据主键来建立一个聚簇索引，这个索引的叶子节点中存储着整行数据；如果没有设定主键，会检查是否有字段设定了唯一性约束，如果有就用这个字段建立聚簇索引；如果没有，那么就会生成一个隐藏字段，以该字段来建立聚簇索引。也就是说，在InnoDB中，每一个表都至少并最多有一个聚簇索引。也就可以将这个数据文件看作是一个索引文件。

由于只能有一个聚簇索引，如果要对其他字段建立索引，那么这些索引就叫作辅助索引。辅助索引的叶子节点不保存具体数据，而是保存该条记录相应主键的值。也就是说，如果要通过辅助索引查询整行数据，那么首先会走辅助索引，得到主键值和走聚簇索引，通过聚簇索引找到最终的数据。

2. 索引建立的几大原则

最左前缀匹配原则。MySQL会一直向右匹配直到遇到范围查询(>, 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

尽量选择区分度高的列作为索引

索引列不能参与计算

尽量扩展索引，而不要新建索引

3. 索引选择性和前缀索引

索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担；另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。

如果表中记录比较少，就没有必要建立索引。而且建立索引的列选择性不能很低。

事务

1. 事务的概念

事务就是指一连串数据操作指令要么都做，要么都不做。会把数据库从一种一致状态转换为另一种一致状态。

2. 事务的要求

ACID：原子性，一致性，隔离性，持久性

3. 隔离性是如何实现的？

因为可能会有多个事务同时执行， 某个事务在执行的过程中，不能收到其他事务的影响。这就产生了隔离性的要求。隔离性是由锁来实现的。

事务在执行中可能会出现：脏读、不可重复读和幻读等问题，这些问题都是由于其他事务的影响造成的。由于不一定必须要完全解决这三种问题，所以数据库中出现了事务隔离级别的设置，设置不同的隔离级别可以解决不同的问题：Read Uncommitted、Read Committed、Repeatable Read、Serializable

如果是RU，则三种问题都不能解决；如果是RC，则可以解决脏读问题；RR则可以解决不可重复读问题，而在InnoDB存储引擎中，还能解决幻读问题；Serializable则可以解决全部问题。

RC和RR在MySQL中都是采用MVCC来解决的，MVCC就是存储了数据库行记录的多个版本，在RC模式下，事务会读MVCC存储的最新的版本；在RR模式下，事务会读取MVCC存储的事务开始时算起的最老的版本。

要解决幻读，在MySQL中是采用加next-key锁的方式。也就是即锁定当条记录，如果读取的是索引列的话，锁定该记录之前和之后的某一个范围。如果没有索引的话，那么数据库会为整个表加上锁。

MySQL是如何实现MVCC的呢？

MVCC是通过在表中增加三个隐藏列来实现的。一个列保存了创建的时间(创建该条行记录时系统的版本号)，另一列保存删除的时间(删除该条记录时系统的版本号)，第三列为DB_ROLL_PTR，指向当前记录项的undo log记录，找之前的版本需要此指针。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号来进行比较。当然，还必须结合undo log日志

SELECT

InnoDB会根据以下条件检查每行记录

InnoDB只查找版本早于当前事务版本的数据行(行的系统版本号小于或等于查询事务的版本号)，这样就可以确保事务读取的行要么是在事务开始之前已经存在的，要么是事务自身插入或者修改过的。

行的删除版本号要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前没有被删除

INSERT

InnoDB为新插入的行保存当前系统版本号作为版本号。如果是新起的事务版本号就会递增。

DELETE

InnoDB为删除的每一行保存当前系统版本号作为行删除标识

UPDATE

更新操作也会插入一条新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识

MySQL中的锁又是如何实现的呢？

锁的等级

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

页面锁：开销和加锁时间位于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间；并发度一般

行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

在数据库中，锁又分为乐观锁和悲观锁

乐观锁

使用数据版本来实现的。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值是否相同，如果相同则可以更新，否则认为是过期数据。也可以用时间戳来实现。同样是在需要乐观锁控制的table中增加一个字段，字段类型使用时间戳，在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取得的时间戳是否相同。如果发现冲突了就让用户决定如何做。

悲观锁

与Java的悲观锁类似。它是由数据库自己实现的，要用的时候直接调用数据库的相关语句。悲观锁又分为共享锁和排他锁。

共享锁

共享锁又可称为读锁，是读取操作创建的锁。其他事务可以并发读取数据，也可以再加共享锁，但是其他事务不能修改数据。加共享锁的语句为：Lock in share mode。在查询语句后加这一句话，会对查询结果中的每行都加共享锁，当没有其他线程对查询结果中的任一行使用排他锁时，可以成功申请到锁，否则会被阻塞。

排他锁

排他锁又可称作写锁。加上排他锁后，其他事务可以读加锁的记录，但是不能对其加上任何锁，也不能写记录。在需要执行的语句后加for update就可以使用排他锁。

行锁

InnoDB中，行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表锁。

原因

一个表至少且最多只有一个聚集索引，该聚集索引的叶子节点存储的数据整个页，该页里面存储着许多行记录。如果加锁的字段没有索引，那么在查询该字段或者更新的时候都会全表扫描来找到匹配的字段，这样的话会对每一行都加行锁。会造成很大的开销，还不如直接加表锁。

4. 原子性，一致性和持久性是怎么实现的？

原子性和持久性一般使用redo log(重做日志)来实现。一致性使用undo log(回滚日志)来实现。undo并不是redo的逆过程。

redo log通常是物理日志，记录的是数据页得物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)

为什么需要Redo log

在修改数据库中数据的时候，数据会先存在于内存中，再重写回磁盘中。如果在写回磁盘之前进程崩溃那么数据就丢失了。因此，提出使用redo log来先将要修改的数据写到这个日志文件中，并保证该日志文件比数据更早的写到磁盘中。这样在进程崩溃重启的时候，就可以根据redo log中的内容恢复数据。这也就保证了原子性和持久性

redo log和二进制日志的区别

二进制日志是在存储引擎上层产生的，不管是什么存储引擎，对数据库进行了修改都会产生二进制日志文件。而redo log是innodb层产生的，只记录该存储引擎中表的变化。

记录的内容不同。二进制日志记录的是逻辑更改情况，关于事务的具体操作内容；redo log记录的是关于每个页的更改的物理情况

写入的时间不同。二进制日志文件仅在事务提交前进行提交，只写磁盘一次；而在事务执行过程中会不断写redo log

redo log是循环写，日志空间大小固定；binlog是追加写，一份日志文件写到一定大小的时候会更换下一个文件，不会覆盖

binlog可以作为换肤数据使用，主从复制搭建；redo log作为异常宕机或者介质故障后的数据恢复备用

undo log主要用于提供回滚和实现MVCC

在数据修改的时候不仅记录了redo log，还记录了相应的undo log，如果因为某些原因导致事务失败或回滚了，可以使用undo log进行回滚。

undo log是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录；当update一条记录时，它记录一条对应相反的update记录。

死锁发生的情况和如何降低死锁的概率

1.死锁发生的情况

死锁案例一

死锁案例二

死锁案例三

2.如何降低死锁的概率

案例一和三中，对索引加锁顺序的不一致可能会导致死锁。所以我们应该尽量以相同的顺序来访问索引记录和表。

案例二中，由于默认情况下MySQL的隔离级别为RR，而且为了防止幻读会使用gap锁，如果能够确定幻读对应用的影响不大可以将隔离级别调为RC

为表添加合理的索引

避免大事务

设置锁超时等待时间：innodb_lock_wait_timeout

一条SQL语句在MySQL中是如何执行的

1. 查询语句

select * from tb_student A where A.age='18' and A.name='张三';

客户端发送一条查询语句，首先会经过连接器(它会完成与数据库的连接，并进行权限验证)，如果没有权限那么就会直接返回错误信息；如果有权限，那么会先走缓存，看看缓存中是否有结果，没有的话才继续进行下一步

经过分析器，它提取语句中的关键数据，并完成对语句的词法、语法分析。如果没有问题则进行下一步

经过优化器，优化器会选择执行效率更好的一个方案，比如上述查询语句，可以选择先找名字等于”张三“的再找年龄等于18的，也可以反过来。

开始由存储引擎执行语句来查找数据

2. 更新语句

update tb_student A set A.age='19' where A.name='张三';

开始的步骤和查询语句差不多，不过执行更新的时候会记录日志。mysql自带bin log(归档日志)，所有存储引擎都可以使用，而InnoDB中还提供redo log(重做日志)，用于帮助实现事务的原子性和持久性。具体操作过程：

先查询到张三这一条数据，如果有缓存，还是会走缓存，如果没在缓存中，就先从磁盘中读入内存，然后返回给执行器。

执行器拿到引擎给的行数据，把年龄改为19，然后调用引擎API接口，写入这一行数据。

引擎把这行数据更新到内存中，同时把这个更新操作记录到redo log中，此时redo log进入prepare状态。然后告诉执行器执行完成了，随时可以提交事务。(此时redo log文件中(不是缓冲)已经记录了操作的日志)

执行器收到通知后，生成这个操作的binlog，并把binlog写入磁盘。此时事务还没有提交，也就是说在事务提交之前binlog已经写入了磁盘

执行器调用引擎的事务提交接口，引擎把刚刚写入的redo log改为提交状态，更新完成。

注意

写入重做日志文件的操作不是直接写，而是先写入一个重做日志缓冲，然后按照一定的条件顺序地写入日志文件。从重做日志缓冲往磁盘写入时，是按512个字节，也就是一个扇区的大小进行写入。因为扇区是写入的最小单位，因此可以保证写入必定是成功的。因此在重做日志的写入过程中不需要有double write。

即使某个事务还没有提交，InnoDB存储引擎仍然每秒会将重做日志缓冲中的内容刷新到重做日志文件中。另一个触发写磁盘的过程是由innodb_flush_log_at_trx_commit控制的，表示在提交操作时，处理重做日志的方式。innodb_flush_log_at_trx_commit的有效值有0、1、2。0代表当提交事务时，并不将事务的重做日志缓冲写入磁盘上的日志文件，而是等待主线程每秒的刷新；1和2不同的地方在于：1表示在执行commit时将重做日志缓冲同步到磁盘，即伴有fsync的调用。2表示将重做日志缓冲异步写到磁盘，即写到文件系统的缓冲中。因此不能完全保证在执行commit时肯定会写入到重做日志文件，只是有这个动作发生。一般将值设为1来保证事务的ACID特性。