【数据库篇】MySQL基础知识

【数据库篇】MySQL 事务篇

MySQL架构

MySQL体系架构

MySQL语句是怎么执行的

• MySQL 客户端与服务器间建立连接，客户端发送一条查询给服务器；

• 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果；否则进入下一阶段；

• 服务器端进行 SQL 解析、预处理，生成合法的解析树；

• 再由优化器生成对应的执行计划；

• MySQL 根据优化器生成的执行计划，调用相应的存储引擎的读写 API 来执行，并将执行结果返回给客户端。

存储引擎

InnoDB 存储引擎

MyISAM 存储引擎

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

MySQL原理

1.1 索引

索引是帮助mysql高效获取数据的排序好的 的数据结构

每次给字段建一个索引，字段中的数据就会被复制一份出来。用于生成索引，（考虑磁盘空间）。不管何种方式查表，最终都会利用主键通过聚集索引来定位到数据。聚集索引（主键）是通往真实数据的唯一出路。

B树、B+树、红黑树、Hash区别

树（balance tree）和b+树应用在数据库索引，可以认为是m叉的多路平衡查找树，但是从理论上讲，二叉树查找速度和比较次数都是最小的，为什么不用二叉树呢？

因为我们要考虑磁盘IO的影响，它相对于内存来说是很慢的。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。

所以我们要减少IO次数，对于树来说，IO次数就是树的高度，而“矮胖”就是b树的特征之一，它的每个节点最多包含m个孩子，m称为b树的阶，m的大小取决于磁盘页的大小。

B树、B+树区别

不同点：

• 1.B树的叶子节点没有指针，B+树有，有指针可以更加方便范围查询，同一种范围查询，b树可能得多次从头节点开始遍历；

• 2.b树没有冗余索引，但是b+树有；

• 3.b树非叶子节点也存放数据，但是b+树只有叶子节点存放数据；

• 4.存放同样的数据，b树的高度可能比b+树要高。

• 5.b树查找相当于二分查找，可以在非叶节点结束，且若经常访问的元素离根节点较近，则访问更加迅速。而b+树的查找路径是由根到叶子节点，每次查找路径长度比较稳定。

索引分类，索引失效条件

1. MyISAM索引实现：

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

1）主键索引：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

2）辅助索引（Secondary key）

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

2. InnoDB索引实现

然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同.

1）主键索引：

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体细节依赖于其实现方式。

MySQL数据库中innodb存储引擎，B+树索引可以分为聚簇索引（也称聚集索引，clustered index）和辅助索引（有时也称非聚簇索引或二级索引，secondary index，non-clustered index）。这两种索引内部都是B+树，聚集索引的叶子节点存放着一整行的数据。

Innobd中的主键索引是一种聚簇索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。

Innodb使用的是聚簇索引，MyISam使用的是非聚簇索引

聚簇索引(聚集索引)

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

聚簇索引的优缺点

优点：

• 1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

• 2.聚簇索引对于主键的排序查找和范围查找速度非常快

缺点：

• 1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键

• 2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

• 3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

辅助索引（非聚簇索引）

在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。

辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。

索引失效条件

失效条件
条件是or,如果还想让or条件生效，给or每个字段加个索引
like查询，以%开发
内部函数
对索引列进行计算
is null不会用，is not null 会用
查询数据量过大，走全表扫描

• 对索引列进行计算导致索引失效

参开文档：https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

CREATE TABLE `task_engine_base_data` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `task_type_id` int(11) NOT NULL DEFAULT '0' COMMENT '任务类型ID',
  `outer_task_id` varchar(64) NOT NULL DEFAULT '' COMMENT '外部任务ID',
  PRIMARY KEY (`id`),
  KEY `idx_outer_id_type_id` (`outer_task_id`,`task_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='子任务规则表';

-- 慢SQL
SELECT * FROM task_engine_base_data WHERE outer_task_id = 1001871

字段类型为字符串，但查询条件的值为整型，导致 MySQL 隐式转换。列值会被转换成浮点数类型，即 CAST(col_name AS DOUBLE) = XXX，对列值进行了计算，无法使用索引。

联合索引

联合索引a、b、c，查询条件a=1，c0这题，会走联合索引，但是只会用到a这个条件。

在找出所有a=1的记录后，不会直接回表，利用索引下推淘汰掉不满足c0的记录之后，可能会MRR（对主键排序）后再回表，可以将随机读变成顺序读。

InnoDB并没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于20的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表一次。

索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。

磁盘顺序读的优势

• 对于 Myisam，在去磁盘获取完整数据之前，会先按照 rowid 排好序，再去顺序的读取磁盘。

• 对于 Innodb，则会按照聚簇索引键值排好序，再顺序的读取聚簇索引。

顺序读带来了几个好处：

1、磁盘和磁头不再需要来回做机械运动；
2、可以充分利用磁盘预读
3、在一次查询中，每一页的数据只会从磁盘读取一次

辅助索引

非聚集索引都可以被称作辅助索引，其叶子节点不包含行记录的全部数据，仅包含索引中的所有键及一个用于查找对应行记录的 即主键或者说聚集索引

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

对于一个字段,它的更新操作很频繁,那么应该使用普通索引还是唯一索引?

如果业务能够保证唯一性应该尽量选择普通索引而不是唯一索引

从查询和更新两方面说明：

查询：

查询来说二者的区别是
普通索引查到符合条件的项后会继续查找下一项，如果下一项不符合再返回
唯一索引则是查到符合条件的项后就直接返回

其实这两种方式效率几乎没有差别，因为查找都是先读取数据项然后在内存中进行的所以多读取一次并不会带来很大的影响

更新：

change buffer

对于更新操作二者还是有很大区别的，要理解他们之间的区别首先要理解change buffer change
buffer是用来记录更新操作的一种行为，在没有把数据项从硬盘读取到内存中时，进行更新操作会先将操作记录的change buffer中
在下一次进行select的时候在把数据项读取到内存中时，会对数据项执行change buffer中的命令，这个过程也成为merge

• 所以唯一索引的更新操作
  首先判断要插入的项在数据库中存不存在，这里就涉及到了一个读的问题，往往这个时候就会把数据从硬盘读取到内存中，如果这个时候还使用change buffer的意义并不大，因为change buffer存在的意义就是减少磁盘于内存的IO，现在数据项已经在内存中了，可以直接修改，所以唯一索引是不适用change buffer的

• 普通索引的更新操作
  普通索引往往是将操作记录到change buffer中，在下一次读取的时候执行这些操作，可以显著减少磁盘与内存的IO操作，从而提高效率

• 选择：
  如果是读取远大于更新和插入的表，唯一索引和普通索引都可以，但是如果业务需求相反，个人觉得应该使用普通索引，
  当然如果是那种更新完要求立即可见的需求，就是刚更新完就要再查询的，这种情况下反而不推荐普通索引，因为这样会频繁的产生merge操作，起不到change buffer的作用，反而需要额外空间来维护change buffer就有点得不偿失了

索引与优化

MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：

(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。

(3)尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

一、聚集索引

聚集索引（Clustered Index）是一种在关系数据库管理系统中常见的索引类型。它的作用是通过在表中创建索引，将表中的数据物理上按照索引的键值顺序进行排序，并以此为依据进行访问和处理。聚集索引仅能有一个，并且它的顺序决定了整个表中记录的物理存储顺序。

聚集索引的主要优点包括：

提升检索效率：通过将表中的记录按照索引的顺序进行排序，聚集索引可以更快地进行数据检索，提升查询效率。

降低磁盘I/O操作：聚集索引由于将表中数据物理上存储在同一块磁盘区域，降低了磁盘I/O操作的开销，进而提高数据库的性能。

聚集索引的物理排序可以为某些表的查询提供帮助：例如，对于需要按照时间或者数值等规则进行排序或做范围查找操作时，聚集索引可以提高数据检索效率。

总之，聚集索引对于缩短数据检索时间和提高数据库性能非常重要，但在使用时需要根据具体应用需求进行权衡和调整。

聚集索引的缺点包括：

一次只能创建一个聚集索引：每个表只能创建一个聚集索引，而不是多个，因此在不同的情况下需要根据具体应用需求进行合理选择。

聚集索引会随着数据的变化而产生碎片：当对表中的数据进行插入、删除或更新时，聚集索引的物理排序可能会发生变化，导致产生碎片，需要定期维护和重建。

修改和插入操作代价较高：当聚集索引的键发生变化时，数据库需要重新进行排序和存储，因此修改和插入操作的代价较高。

索引占用更多的空间：聚集索引对于数据的物理排序需要存储额外的信息，因此它所占用的磁盘空间比非聚集索引更大。

索引可能会降低性能：有时候使用聚集索引并不一定会提高性能，甚至可能会降低性能。例如，聚集索引排序的方式可能与具体查询的方式不匹配，导致查询效率下降。

综上所述，虽然聚集索引可以大大提高查询效率，但在一些场景下可能会带来一定的负面影响。因此，在使用聚集索引时需要根据数据表的大小、查询方式和修改操作的频率等因素进行综合考虑和取舍。

二、非聚集索引

非聚集索引（Non-Clustered Index）是一种常见的数据库索引类型，与聚集索引不同的是，非聚集索引并不改变表中数据的物理存储方式。

非聚集索引通过创建一个独立的数据结构来记录索引值以及指向与该索引相关的行的指针，从而加速数据的检索。在非聚集索引的建立过程中，数据库会在磁盘上创建索引文件，其中记录了索引值和行指针的映射关系。

与聚集索引不同，每个表可以拥有多个非聚集索引，每个非聚集索引都有自己的结构，可以包含表中的一部分数据。非聚集索引通常可以加快查询效率，提高数据库的性能。

需要注意的是，更新表中数据时，非聚集索引将会耗费更多的时间和磁盘空间来维护。因为对于每一个数据行的插入、更新、删除操作都会导致非聚集索引的更新和维护。

总的来说，非聚集索引可以提高查询效率，但是对于修改操作需要更多的时间和磁盘空间支持。在实际应用中，需要根据查询和修改操作的特点，考虑使用哪种类型的索引。

非聚集索引的有点包括：

不改变数据表的物理存储格式：与聚集索引不同，非聚集索引并不改变数据表的物理存储格式，因此对于数据表的插入、删除和更新操作的影响较小。

可以创建多个索引：每个数据表可以创建多个非聚集索引，每个非聚集索引都有自己的结构，可以包含表的一部分数据，并且可以被不同的查询使用，从而进一步提高查询效率。

数据表可以被多个非聚集索引覆盖：如果某个查询涉及到表的多个列，可以设置覆盖索引来避免查询需要访问数据表，从而提高查询效率。

能够加速特定的查询：非聚集索引可以根据列值、列的组合或表达式快速查找记录，针对特定的查询可以提高查询效率。

综上所述，非聚集索引可以提高查询效率，且对数据表的插入、删除和更新操作的影响较小，因此在实际应用中，根据查询和修改操作的特点，结合查询和修改操作的比重及数据表的大小等因素，可以考虑使用非聚集索引来优化数据库性能。

非聚集索引的缺点包括：

占用更多的磁盘空间：与聚集索引不同，非聚集索引需要维护独立的数据结构，因此需要占用更多的磁盘空间。

查询时需要访问索引和数据表：非聚集索引只存储索引值和行指针的映射关系，因此在进行查询时需要同时访问索引和数据表，这会增加查询的成本和响应时间。

频繁的修改操作会影响性能：对于频繁的数据修改操作（如插入、更新和删除），需要维护非聚集索引的完整性，因此会对数据库的性能造成影响。

数据表和非聚集索引需要使用不同的I/O操作：非聚集索引和数据表使用不同的I/O操作，因此在查询过程中需要进行多次I/O操作，这会增加查询的成本和响应时间。

综上所述，非聚集索引虽然可以提高查询效率，但需要占用更多的磁盘空间，并且查询时需要访问索引和数据表，对于频繁的修改操作也会对数据库的性能造成影响。在使用非聚集索引时需要综合考虑数据库的实际情况、查询的特点和修改的频率等因素。

三、聚集索引的非聚集索引的区别

聚集索引和非聚集索引在数据库中起着不同的作用，它们的主要区别在于：

物理存储方式不同：聚集索引的数据行的物理存储顺序与索引的排序顺序相同，因此每张表只能有一个聚集索引。而非聚集索引的数据行的物理存储顺序与索引的排序顺序无关，因此每张表可以有多个非聚集索引。

插入、删除和更新操作时的影响不同：对于聚集索引，由于数据行的物理存储顺序与索引的排序顺序相同，因此在进行插入、删除和更新操作时需要对数据行的物理位置进行修改，这会对索引和数据表的性能造成一定的影响。而对于非聚集索引，它只存储了索引值和记录指针之间的映射关系，因此在进行插入、删除和更新操作时，只需要修改非聚集索引中的映射关系即可，对数据表的性能影响相对较小。

查询时的性能不同：聚集索引可以直接定位到数据行，因此在查询单条记录时有较好的性能表现。而非聚集索引需要先定位到索引，然后再通过记录指针查找数据行，因此在查询多条记录时会有一定的性能损失。

对内存的使用不同：由于聚集索引的所有数据行都按照索引的排序顺序存储，因此可以被缓存在内存中，以提高查询效率。而非聚集索引只存储了映射关系，无法完全缓存在内存中，因此查询效率相对而言较低。

综上所述，聚集索引和非聚集索引在数据库中的作用及特点是不同的。数据库设计时需要根据数据表的特点和查询需求来选择适合的索引类型。聚集索引适合于少量大批量的单行查询，而非聚集索引适合于多条件、大批量的查询。
————————————————

                        版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/m0_53396342/article/details/130256304

1.3 锁

https://www.jianshu.com/p/478bc84a7721

锁的兼容性（属性分类）：

• 共享锁（读锁）：其他事务可以读，但不能写。
• 排他锁（写锁） ：其他事务不能读取，也不能写。

事务可以通过语句显式给记录集加共享锁或排他锁

共享锁（S）

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。

排他锁 (x)

排他锁（EXclusive Lock），又称X锁、独占锁、写锁。针对行锁。

当一个事务对某行数据进行 update 或 delete 操作时都要先获取到该记录上的 X 锁，如果已经有其他事务获取到了该记录上的 X 锁，那么当前事务会阻塞等待直到上一事务释放了对应记录上的 X 锁

当有事务对数据加写锁后，其他事务不能再对锁定的数据加任何锁，又因为InnoDB对select语句默认不加锁，所以其他事务除了不能写操作外，照样是允许读的（尽管不允许加读锁）。

📢主要为了在事务进行写操作时，不允许其他事务修改。
加锁方式：
自动：DML语句默认加写锁
手动：select * from T where id=1 for update;
释放方式：
commit、rollback;

对于InnoDB 在RR(MySQL默认隔离级别) 而言，对于 update、delete 和 insert 语句， 会自动给涉及数据集加排它锁（X）；
对于普通 select 语句，innodb 不会加任何锁。如果想在select操作的时候加上 S锁 或者 X锁，需要我们手动加锁。

锁模式

记录锁

1、记录锁（Record Locks）

记录锁其实很好理解，对表中的记录加锁，叫做记录锁，简称行锁。比如

SELECT * FROM `test` WHERE `id`=1 FOR UPDATE;

它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。

需要注意的是：

• id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁(有关临键锁下面会讲)。
• 同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁。

在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：

-- id 列为主键列或唯一索引列 
UPDATE SET age = 50 WHERE id = 1;

记录锁也是排它(X)锁,所以会阻塞其他事务对其插入、更新、删除。

意向锁

意向锁（Intention Lock），又称I锁。针对表锁。

当有事务给表的数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了，其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。特别是表中的记录很多时，逐行判断加表锁的方式效率很低。而这个标识就是意向锁。

• 意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。
• 意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁。

间隙锁（gap锁）

间隙锁 是 Innodb 在 RR(可重复读) 隔离级别 下为了解决幻读问题时引入的锁机制。间隙锁是innodb中行锁的一种。

请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;

即所有在（1，10）区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。

除了手动加锁外，在执行完某些 SQL 后，InnoDB 也会自动加间隙锁

间隙锁其实是“共享”的。也就是说，多个事务可以获取同一个区间的间隙锁。

可能产生间隙锁的位置
对于操作的数据是主键索引和普通索引，有不同的加锁规则，如下：

1)、唯一索引只有锁住多条记录或者一条不存在的记录的时候，才会产生间隙锁，指定给某条存在的记录加锁的时候，只会加记录锁，不会产生间隙锁；
2)、普通索引不管是锁住单条，还是多条记录，都会产生间隙锁；

插入意向锁

插入意向锁(Insert Intention Locks) 也是一种间隙锁，由 INSERT 操作在行数据插入之前获取

在插入一条记录前，需要先定位到该记录在 B+ 树中的存储位置，然后判断待插入位置的下一条记录上是否添加了 Gap Locks，如果下一条记录上存在 Gap Locks，那么插入操作就需要阻塞等待，直到拥有 Gap Locks 的那个事务提交，同时执行插入操作等待的事务也会在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但目前处于阻塞状态，生成的锁结构就是插入意向锁

对于语句 select * from test where id = 25 for update 因当前表中不存在该记录，在可重复读隔离级别下，为了避免幻读，会给 (20, 30] 间隙加上 Gap Locks

从锁日志可以看出 session 1 给记录 30 添加了间隙锁(lock_mode X locks gap before rec)

RECORD LOCKS space id 133 page no 3 n bits 80 index PRIMARY of table `test`.`test` trx id 38849 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 8000001e; asc     30 ;;
 1: len 6; hex 00000000969c; asc       ;;
 2: len 7; hex a60000011a0128; asc       (;;
 3: len 4; hex 8000001e; asc     ;;

当 session 2 插入记录 26 时，会在 B+ 树中先定位到待插入位置，再判断插入位置的间隙是否存在 Gap Locks，也就是判断待插入位置的后一记录 id = 30 是否存在 Gap Locks，如果存在需要在该记录上生成插入意向锁等待

此时 session 2 和 session 3 都在 id = 30 的记录上添加了插入意向锁等待 session 1 上的 Gap Locks 释放，生成的锁记录如下：

临键锁（Next-Key Locks）

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

也可以理解为一种特殊的间隙锁。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

当查询的索引含有唯一属性时，也就是查询的列是主键索引或唯一索引时，InnoDB存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围。若唯一索引由多个列组成，而查询仅是查找多个唯一索引列中的其中一个，那么查询其实是range类型查询，而不是point类型查询，InnoDB存储引擎依然使用Next-Key Lock进行锁定。

我们执行一条SQL语句：SELECT * FROM user WHERE id > 7 AND id < 11 FOR UPDATE，锁住的不是9这单个值，而是对(5，9 ] 、(9，12 ] 这2个区间加了X锁。因此任何对于这个范围的插入都是不被允许的，从而避免幻读。

InnoDB存储引擎默认的事务隔离级别是REPEATABLE READ，在该隔离级别下，其采用Next-Key Locking的方式来加锁。而在事务隔离级别READ COMMITTED下，其仅采用Record Lock。

这里对 记录锁、间隙锁、临键锁 做一个总结

• InnoDB 中的行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁。
• 记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。
• 间隙锁存在于非唯一索引中，锁定开区间范围内的一段间隔，它是基于临键锁实现的。
• 临键锁存在于非唯一索引中，该类型的每条记录的索引上都存在这种锁，它是一种特殊的间隙锁，锁定一段左开右闭的索引区间。

锁粒度分类

MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现：

• MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）
• BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁
• InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令。

表级锁：

MyISAM表锁

• 表共享 读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
• 表独占 写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

行级锁

• 隐式锁定：
  InnoDB在事务执行过程中，使用两阶段锁协议：

随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；

锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

意向锁是 InnoDB 自动加的， 不需用户干预。

加锁机制

悲观锁

悲观锁方案：每次获取商品时，对该商品加排他锁。也就是在用户A获取获取 id=1 的商品信息时对该行记录加锁，期间其他用户阻塞等待访问该记录。悲观锁适合写入频繁的场景。

begin;

select * from goods where id = 1 for update;

update goods set stock = stock - 1 where id = 1;

commit;

乐观锁

乐观锁不是数据库层面上的锁，需要用户手动去加的锁。一般我们在数据库表中添加一个版本字段version来实现，例如操作1和操作2在更新User表的时，执行语句如下：

update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version}，

乐观锁方案：每次获取商品时，不对该商品加锁。在更新数据的时候需要比较程序中的库存量与数据库中的库存量是否相等，如果相等则进行更新，反之程序重新获取库存量，再次进行比较，直到两个库存量的数值相等才进行数据更新。

乐观锁适合读取频繁的场景。

不加锁获取 id=1 的商品对象

select * from goods where id = 1

begin;

#更新 stock 值，这里需要注意 where 条件 “stock = cur_stock”，只有程序中获取到的库存量与数据库中的库存量相等才执行更新

update goods set stock = stock - 1 where id = 1 and stock = cur_stock;

commit;

如果我们需要设计一个商城系统，该选择以上的哪种方案呢？

查询商品的频率比下单支付的频次高，基于以上我可能会优先考虑第二种方案（当然还有其他的方案，这里只考虑以上两种方案）。

乐观锁更适合解决冲突概率极小的情况；
而悲观锁则适合解决并发竞争激烈的情况，尽量用行锁，缩小加锁粒度，以提高并发处理能力，即便加行锁的时间比加表锁的要长

mysql死锁问题

表锁等待死锁
一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

死锁的第二种情况

两个事务执行过程时间上有交集，并且过程发生在两者提交之前
事务1更新uid=1的记录，事务2更新uid=2的记录，在RR级别，由于uid是唯一索引，因此两个事务将分别持有uid=1和2所在行的独占锁
事务1执行到第二条更新语句时，发现uid=2的行被锁住，进入阻塞等待锁释放；
事务2执行到第二条语句时发现uid=1的行被锁，同样进入阻塞
两个事务互相等待，死锁产生。

解决方法：

1、对于按钮等控件，点击后使其立刻失效，不让用户重复点击，避免对同时对同一条记录操作。
2、使用乐观锁进行控制。乐观锁大多是基于数据版本（Version）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是 通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数 据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中的数据 库加锁开销（用户A和用户B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。Hibernate 在其数据访问引擎中内置了乐观锁实现。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造 成脏数据被更新到数据库中。

3、使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的Select … for update语句，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。如一个金融系统， 当某个操作员读取用户的数据，并在读出的用户数据的基础上进行修改时（如更改用户账户余额），如果采用悲观锁机制，也就意味着整个操作过程中（从操作员读 出数据、开始修改直至提交修改结果的全过程，甚至还包括操作员中途去煮咖啡的时间），数据库记录始终处于加锁状态，可以想见，如果面对成百上千个并发，这 样的情况将导致灾难性的后果。所以，采用悲观锁进行控制时一定要考虑清楚。

4.避免循环更新，优化为一条where锁定要更新的记录批量更新
5.如果非要循环更新，尝试取消事务（能接受的话），即每一条更新为一个独立的事务

死锁的第三种情况

如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情 况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

https://www.jianshu.com/p/2f17a939e030

gap lock/next keys lock导致死锁

死锁原因分析：

事务1执行delete age = 27，务2执行delete age = 31，在RR级别，操作条件不是唯一索引时，行锁会升级为next keys lock（可以理解为间隙锁），因此事务1锁住了25到27和27到29的区间，事务2锁住了29到31的区间
事务1执行insert age = 30，等待事务2释放锁
事务2执行insert age = 28，等待事务1释放锁
死锁产生，死锁日志显示lock_mode X locks gap before rec insert intention waiting
解决方案：

• 降低事务隔离级别到Read Committed，该隔离级别下间隙锁降级为行锁，可以减少死锁发生的概率
• 避免这种场景- -

避免死锁的原则：

• 建立合适的索引，减小锁的粒度
• 选择合适的事务隔离级别
• 大事务拆成小事务，一个事务中的锁尽量少

1.3 mysql日志篇

日志种类

MySQL日志主要包括
错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。

其中比较重要的就是二进制日志binlog（归档日志）、事务日志redo log（重做日志）和undo log（回滚日志）。

binlog （mysql server层日志）

binlog 是 一种二进制日志，做数据库主从同步。其主要是用来记录对 MySQL 数据更新或潜在发生更新的 SQL 语句，并以 “事务”的形式保存在磁盘中。

MySQL 基础架构分为 Server 层和存储引擎层两部分。

• Server 层的日志是 binlog，记录 SQL 语句的原始逻辑，用来归档和复制。
• InnoDB 引擎的日志是 redo log，记录数据页的修改，用来实现 crash-safe 能力。

查看数据库binlog模式：SHOW VARIABLES LIKE 'binlog_format';

binlog 主要有以下作用：

• 复制：MySQL 主从复制在 Master 端开启 binlog，Master 把它的二进制日志传递给 slaves 并回放来达到 master-slave 数据一致的目的
• 数据恢复：用于数据库的基于时间点的还原（ mysqlbinlog 工具恢复数据）
• 增量备份

几个知识点：

• binlog 不会记录不修改数据的语句，比如Select或者Show
• binlog 会重写日志中的密码，保证不以纯文本的形式出现
• MySQL 8 之后的版本可以选择对 binlog 进行加密
• 具体的写入时间：在事务提交的时候，数据库会把 binlog cache 写入 binlog 文件中，但并没有执行fsync()操作，即只将文件内容写入到 OS 缓存中。随后根据配置判断是否执行 fsync。
• 删除时间：保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

binlog日志有三种格式，Statement、MiXED、ROW

binlog 的不同模式有什么区别呢？

• statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。

  但是，正是由于 Statement 模式只记录 SQL，而如果一些 SQL 中 包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就得到另外一个结果了。

  所以使用 Statement 格式会出现一些数据一致性问题。

• row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。

• mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

rr rc 的主从复制区别？

1> RC 隔离级别不支持 statement 格式的bin log，因为该格式的复制，会导致主从数据的不一致；只能使用 mixed 或者 row 格式的bin log; 这也是为什么MySQL默认使用RR隔离级别的原因。复制时，我们最好使用：binlog_format=row

Redo log (InnoDB存储引擎独有日志)

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL有了崩溃恢复的能力。

redo log 的作用主要是实现 ACID 中的持久性，保证提交的数据不丢失，它记录了事务提交的变更操作，服务器意外宕机重启时，利用 redo log 进行回放，重新执行已提交的变更操作

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。

checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。循环覆盖较旧的日志

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

当MySQL实例挂了或者宕机了，重启的时候InnoDB存储引擎会使用rede log日志恢复数据，保证事务的持久性和完整性。如下图：

重做日志用来实现事务的持久性，即D特性。它由两部分组成：

①内存中的重做日志缓冲
②重做日志文件

• 事务提交时，首先将变更写入 redo log，事务就视为成功。至于数据页（表、索引）上的变更，可以放在后面慢慢做
  • 数据页上的变更宕机丢失也没事，因为 redo log 里已经记录了
  • 数据页在磁盘上位置随机，写入速度慢，redo log 的写入是顺序的速度快
    它由两部分组成，内存中的 redo log buffer，磁盘上的 redo log file
  • redo log file 由一组文件组成，当写满了会循环覆盖较旧的日志，这意味着不能无限依赖 redo log，更早的数据恢复需要 binlog
  • buffer 和 file 两部分组成意味着，写入了文件才真正安全，同步策略由下面的参数控制
  • innodb_flush_log_at_trx_commit
    • 0 - 每隔 1s 将日志 write and flush 到磁盘
    • 1 - 每次事务提交将日志 write and flush（默认值）
    • 2 - 每次事务提交将日志 write，每隔 1s flush 到磁盘，意味着 write 意味着写入操作系统缓存，如果 MySQL 挂了，而操作系统没挂，那么数据不会丢失

redo log、binlog不同。

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

• redo log 是物理日志，记的是“在某个数据页上做了什么修改”，是数据页面的修改之后的物理记录；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

• redo log 是循环写的，空间固定会用完（会覆盖）；binlog 是可以追加写入的。追加写是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

• 作用不同：redo log是保证事务的持久性的，是事务层面的，binlog作为还原的功能，是数据库层面的（当然也可以精确到事务层面的），虽然都有还原的意思，但是其保护数据的层次是不一样的。

Undo log

第二部分是Undo log，它可以实现如下两个功能：
1.实现事务回滚
2.实现MVCC

什么是MySQL两阶段提交

当有数据修改时，会先将修改redo log cache和binlog cache然后在刷入到磁盘形成redo log file，当redo log file全都刷入到磁盘时（prepare 状态）和提交成功后才能将binlog cache刷入磁盘，

当binlog全部刷新到磁盘后会记录一个xid，然后在relo log file上打上commit标志（commit阶段）。

说明：

prepare: redo log 写入 log buffer，并 fsync() 持久化到磁盘，在redo log事务记录 2PC 的 XID, 并在redo log事务上打上 prepare 标识；

commit: binlog 写入 log buffer ，并 fsync() 持久化到磁盘，在 binlog 事务中记录 2PC 的 XID，同时在 redo log事务上打上 commit 标识。

注意：prepare 和 commit 阶段所提到的“事务”，都是指内部XA事务，即2PC

日志问题

binlog、redolog谁先产生、会有数据不一致吗

• 从进程角度：几乎同时产生
• 从日志落盘角度：redolog先产生
  因为binlog是事务提交后才会做持久化，但是redolog有种刷盘机制

redolog里面记录有事务状态, 数据库宕机后数据会恢复

1.3.1 日志log记录流程

msyql redo log undolog什么时候刷盘？

undo log，它的写入和刷盘是在事务执行期间进行的，每当事务执行一次更改操作时，对应的undo log就会被写入内存缓冲区，并在一段时间后被刷入磁盘，以保证事务的原子性和一致性。

redo log中的内容是在事务提交前写入内存缓冲区的，只有在系统需要把内存缓冲区的内容刷入磁盘时，才会将redo log的内容一同刷入磁盘，这个过程称为"checkpoint"。

因此，redo log和undo log的刷盘时间是不同的

• undo log的刷盘时间是在事务执行期间，每当有数据更新时就会写入磁盘。
• redo log的刷盘时间是在事务提交时

1.3.3 undo log 回滚日志

undo log 和 redo log 其实都不是 MySQL 数据库层面的日志，而是 InnoDB 存储引擎的日志。二者的作用联系紧密，事务的隔离性由锁来实现，原子性、一致性、持久性通过数据库的 redo log 或 redo log 来完成。

undo log 用来保证事务的原子性和 MVCC，主要为事务的回滚服务
redo log 又称为重做日志，用来保证事务的持久性，

undo log是逻辑日志，可以理解为:

当delete一条记录时，undo log中会记录一条对应的insert记录
当insert一条记录时，undo log中会记录一条对应的delete记录
当update一条记录时，它记录一条对应相反的update记录

1.3.4 redo log 重做日志

什么是redo log?

redo log 的作用主要是实现 ACID 中的持久性，保证提交的数据不丢失

• 它记录了事务提交的变更操作，服务器意外宕机重启时，尚有脏页未写入磁盘，在重启mysql服务的时候，利用 redo log 进行回放，重新执行已提交的变更操作。

redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。

redo日志格式

type：该条redo日志的类型，redo日志设计大约有53种不同的类型日志。
space ID：表空间ID。
page number：页号。
data：该条redo日志的具体内容。（比如：某个事务将系统表空间中的第100号页面中偏移量为1000处的那个字节的值1改成2）

后续，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术。

WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

redo日志刷盘时机

• log buffer空间不足时，log buffer的大小是有限的（通过系统变量innodb_log_buffer_size指定），如果不停的往这个有限大小的log buffer里塞入日志，很快它就会被填满。InnoDB认为如果当前写入log buffer的redo日志量已经占满了log buffer总容量的大约一半左右，就需要把这些日志刷新到磁盘上。

• 事务提交时，我们前边说过之所以使用redo日志主要是因为它占用的空间少，还是顺序写，在事务提交时可以不把修改过的Buffer Pool页面刷新到磁盘，但是为了保证持久性，必须要把修改这些页面对应的redo日志刷新到磁盘。

• 后台有一个线程，大约每秒都会刷新一次log buffer中的redo日志到磁盘。

redo log刷盘配置 innodb_flush_log_at_trx_commit

为了保证事务的持久性，用户线程在事务提交时需要将该事务执行过程中产生的所有redo日志都刷新到磁盘上。会很明显的降低数据库性能。如果对事务的持久性要求不是那么强烈的话，可以选择修改一个称为innodb_flush_log_at_trx_commit的系统变量的值。
该变量有3个可选的值：

0：当该系统变量值为0时，表示在事务提交时不立即向磁盘中同步redo日志，这个任务是交给后台线程做的。 这样很明显会加快请求处理速度，但是如果事务提交后服务器挂了，后台线程没有及时将redo日志刷新到磁盘，那么该事务对页面的修改会丢失。

1：当该系统变量值为1时，表示在事务提交时需要将redo日志同步到磁盘，可以保证事务的持久性。1也是innodb_flush_log_at_trx_commit的默认值。

2：当该系统变量值为2时，表示在事务提交时需要将redo日志写到操作系统的缓冲区中，但并不需要保证将日志真正的刷新到磁盘。 这种情况下如果数据库挂了，操作系统没挂的话，事务的持久性还是可以保证的，但是操作系统也挂了的话，那就不能保证持久性了。

redo log写一半时 掉电怎么办？

双一配貴下 写redo写到一半掉电说明事务提交还没有结束，这个时候需要回滚

具体做法是crash recovery的时候把那些已经完整写到磁盘
的redo先回放 不在commit状态那些会跳过的，
然后由于此时 undo标志位是active 状态 因此会通过 undo回滚这个事务的修改 保证事务原子性

如果是异步写redo log,则没办法保证，innodb可能会丢数据

redo log 与undo log区别

undo log不是redo log的逆向过程，其实它们都算是用来恢复的日志：

• 1.redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
• 2.undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

REDO LOG 和 BINLOG 日志有以下三点不同:

• 1.redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
• 2.redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
• 3.redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

1.4 innodb和myisam相关

聚集索引和非聚集索引

• 聚簇索引默认是主键
• 如果没定义主键，innodb会选择一个唯一的非空索引
• 如果没这样的索引，在内部会生成一个GEN_CLUST_INDEX 的隐式聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体细节依赖于其实现方式。

聚簇索引(聚集索引)
　　聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

聚簇索引的优缺点

• 优点：

  1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

  2.聚簇索引对于主键的排序查找和范围查找速度非常快

• 缺点：

  1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键

  2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

  3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

辅助索引（非聚簇索引）
　　在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

什么是插入缓冲（InnoDB Insert Buffer）？

一般情况下应用程序中主键是唯一且自增的，数据是按照主键递增的顺序进行插入的，所以插入聚集索引一般都是顺序的，不需要磁盘的随机读取。对于这种插入操作，速度是非常快的。如果主键是UUID这种非自增的方式，那么插入方式和辅助索引一样都是随机的。

辅助索引是非顺序的插入，需要离散地访问辅助索引页，由于随机读取磁盘所以会导致插入性能的下降。

为了解决上述问题，InnoDB存储引擎设计了插入缓冲（Insert Buffer）。
辅助索引的插入操作，会先判断插入的辅助索引页是否在缓冲池中，若在则直接插入；否则先放到插入缓冲中，然后以一定的频率和情况进行Insert Buffer和辅助索引叶子节点的合并操作，通过将多个插入合并到一个操作中（因为在一个索引页中），大大提高了对于辅助索引的插入性能。

innodb和myisam区别(事务，索引，锁）

InnoDB MyISAM
支持事务 不支持事务
支持外键 不支持外键
支持行锁 不支持行锁，支持表锁
写操作效率高 读操作效率高（执行大量读的操作，是很不错的选择）
支持全文索引 支持全文索引
表占用空间较小 表占用空间较大
聚集（主键）索引 非聚集索引

索引的数据结构为什么是B+数而不是hashmap或者B树？

• 1.B+树的磁盘读写代价更低
  B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

• B+树的查询效率更加稳定
  由于非叶子节点只存索引信息，而没有真正的数据信息，所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

• B+树更加适合在区间查询的情况
  由于B+树的数据都存储在叶子结点中，非叶子结点均为索引，只需要扫一遍叶子结点即可得到所有数据信息，但是B树因为其非叶子结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B树更加适合在区间查询的情况，通常B+树用于数据库索引。

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引

非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

MyISAM 中的查询为什么比 InnoDB 快？

MyISAM索引实现

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址

b+树

一个m阶的B+树具有如下几个特征：
1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3.所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素

树

1. 平衡二叉树

平衡二叉树（Balanced Binary Tree）的定义是这样的——空树，或者任一节点左、右子树高度差的绝对值不超过1，称为平衡二叉树。

https://blog.csdn.net/y506798278/article/details/103533241

红黑树

红黑树（Red Black Tree）是一种自平衡的二叉查找树，它与平衡二叉树相同的地方在于都是为了维护查找树的平衡而构建的数据结构，它的主要特征是在二叉查找树的每个节点上添加了一个属性表示颜色，颜色有两种，红与黑。

• 每个节点是红色或者黑色；
• 根节点是黑色；
• 所有叶子节点都是黑色（叶子是NIL节点，也称为外节点）;
• 每个红色节点的子节点都是黑色（从每个叶子节点到根节点的所有路径上不能有两个连续的红色节点）；
• 从红黑树的任一节点到其每个叶子节点的所有路径都包含相同数目的黑色节点（包含黑色节点的数目称为该节点的黑高度）。

红黑树与平衡二叉树的区别

• 平衡二叉树通过保持任一节点左、右子树高度差的绝对值不超过1来维持二叉树的平衡；而红黑树是根据查找路径上黑色节点的个数以及红、黑节点之间的联系来维持二叉树的平衡。
• 平衡二叉树在插入或者删除节点时为了保证左右子树的高度差会进行旋转，这一个旋转根据数据的不同旋转的复杂度也会不一样，所以在插入或者删除平衡二叉树的节点时，旋转的次数不可知，这也导致在频繁的插入、修改中造成的效率问题；红黑树在执行插入修改的操作时会发生旋转与变色（红变黑，或者黑变红）以确保没有一条路径会比其它路径长出两倍。
• 总体来说，在插入或者删除节点时，红黑树旋转的次数比平衡二叉树少，因此在插入与删除操作比较频繁的情况下，选用红黑树。

B树

B-树由来

定义：B-树是一类树，包括B-树、B+树、B*树等，是一棵自平衡的搜索树，它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。
B-树是专门为外部存储器设计的，如磁盘，它对于读取和写入大块数据有良好的性能，所以一般被用在文件系统及数据库中。

先来看看为什么会出现B-树这类数据结构。
传统用来搜索的平衡二叉树有很多，如 AVL （平衡二叉搜索树）树，红黑树等。这些树在一般情况下查询性能非常好，但当数据非常大的时候它们就无能为力了。原因当数据量非常大时，内存不够用，大部分数据只能存放在磁盘上，只有需要的数据才加载到内存中。一般而言内存访问的时间约为 50 ns，而磁盘在 10 ms 左右。速度相差了近 5 个数量级，磁盘读取时间远远超过了数据在内存中比较的时间。这说明程序大部分时间会阻塞在磁盘 IO 上。那么我们如何提高程序性能？减少磁盘 IO 次数，像 AVL 树，红黑树这类平衡二叉树从设计上无法“迎合”磁盘。

https://www.yuque.com/gotaoey/vaeroo/nurxv3

我们从“迎合”磁盘的角度来看看B-树的设计。
索引的效率依赖与磁盘 IO 的次数，快速索引需要有效的减少磁盘 IO 次数，如何快速索引呢？索引的原理其实是不断的缩小查找范围，就如我们平时用字典查单词一样，先找首字母缩小范围，再第二个字母等等。平衡二叉树是每次将范围分割为两个区间。==为了更快，B-树每次将范围分割为多个区间，区间越多，定位数据越快越精确。==那么如果节点为区间范围，每个节点就较大了。所以新建节点时，直接申请页大小的空间（磁盘是按 block 分的，一般为 512 Byte。磁盘 IO 一次读取若干个 block，我们称为一页，具体大小和操作系统有关，一般为 4 k，8 k或 16 k），计算机内存分配是按页对齐的，这样就实现了一个节点只需要一次 IO。

上图是一棵简化的B-树，多叉的好处非常明显，有效的降低了B-树的高度，为底数很大的 log n，底数大小与节点的子节点数目有关，一般一棵B-树的高度在 3 层左右。层数低，每个节点区确定的范围更精确，范围缩小的速度越快。

B树（B-tree）是一种树状数据结构，它能够存储数据、对其进行排序并允许以O(log
n)的时间复杂度运行进行查找、顺序读取、插入和删除的数据结构
B树，概括来说是一个节点可以拥有多于2个子节点的二叉查找树。与自平衡二叉查找树不同，B-树为系统最优化大块数据的读和写操作。B-tree算法减少定位记录时所经历的中间过程，从而加快存取速度。普遍运用在数据库和文件系统。

叶节点具有相同的深度，叶节点指针为空
所有索引元素不重复
节点中数据索引从左到右递增排序

关键字集合分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
其搜索性能等价于在关键字全集内做一次二分查找；

B+树

b+树，是b树的一种变体，查询性能更好。m阶的b+树的特征：

• 有n棵子树的非叶子结点中含有n个关键字（b树是n-1个），这些关键字不保存数据，只用来索引，所有数据都保存在叶子节点（b树是每个关键字都保存数据）。
• 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
• 所有的非叶子结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
• 通常在b+树上有两个头指针，一个指向根结点，一个指向关键字最小的叶子结点。
  同一个数字会在不同节点中重复出现，根节点的最大元素就是b+树的最大元素。

B树与B+树区别（为啥mysql中用Bplus，而mongo用B-tree？）

所以，B+树在B树的基础上做了优化，它与B树的差异在于：

（1）有 k 个子节点的节点必然有 k 个key；

（2）非叶子节点仅具有索引作用，数据存放在叶子节点中，能容纳更多节点元素，可以使得树更矮，所以IO操作次数更少。。

（3）所有的叶子结点使用链表相连，便于区间查找和遍历。B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

b只要查找到匹配元素，直接返回。而b+查询必须查找到叶子节，相对于b，b+无需返回上层节点重复遍历查找工作，所以得出b查找并不稳定，而b+是稳定的，b+树通过双向链表将叶子节点串联起来,基于空间换时间。目的就是支持倒叙和顺序查询。

执行计划Extra列可能出现的值及含义：

• Using where：表示优化器需要通过索引回表查询数据。
• Using index：即覆盖索引，表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表，通常是通过将待查询字段建立联合索引实现。
• Using index condition：在5.6版本后加入的新特性，即大名鼎鼎的索引下推，是MySQL关于减少回表次数的重大优化。
• Using filesort:文件排序，这个一般在ORDER BY时候，数据量过大，MySQL会将所有数据召回内存中排序，比较消耗资源。

1.6 主从同步的原理和形式：

1.三种主要实现粒度

详细的主从同步主要有三种形式：statement、row、mixed

• statement: 会将对数据库操作的sql语句写道binlog中
• row: 会将每一条数据的变化写道binlog中。
• mixed: statement与row的混合。Mysql决定何时写statement格式的binlog, 何时写row格式的binlog。

2.主从同步的原理

• 主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog

• 从库生成两个线程，一个I/O线程，一个SQL线程；**

  • i/o线程去请求主库 的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中；
  • SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；

3.MySql数据库从库同步的延迟问题

当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。

首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高次要原因：读写binlog带来的性能影响，网络传输延迟。

1.8 ddl

由上图可知online DDL大体可以分为3部分：

1、copy（ALGORITHM=COPY）这部分是offline的，在DDL执行期间其他DML不能并行，也是5.6版本前的DDL执行方法。其间生成临时表（server层的操作支持所有引擎），用于写入原表修改过的数据，同时在原表路径下会生成临时表的.frm和.ibd文件。在innodb中不支持使用inplace的操作都会自动使用copy方式执行，而MyISAM表只能使用copy方式。

2、inplace（ALGORITHM=INPLACE）所有操作在innodb引擎层完成，不需要经过临时表的中转。除上图两种特殊索引创建外，其他以inplace方式执行的操作都是online的，执行期间其他DML操作可以并行，其中又以是否重建表又分为两个部分rebuild和no-rebuild。

rebuild部分涉及表的重建，在原表路径下创建新的.frm和.ibd文件，消耗的IO会较多。期间（原表可以修改）会申请row log空间记录DDL执行期间的DML操作，这部分操作会在DDL提交阶段应用新的表空间中。

no-rebuild部分由于不涉及表的重建，除创建添加索引，会产生部分二级索引的写入操作外，其余操作均只修改元数据项，即只在原表路径下产生.frm文件，不会申请row log，不会消耗过多的IO，速度通常很快。

3、inplace but offline的几种特殊DDL操作，本身是按inplace方式执行，但是执行期间DML语句却不能并行。

1.9 优化

不要建立太多索引

我们虽然可以根据我们的喜好在不同的列上建立索引，但是建立索引是有代价的：

• 空间上的代价
  每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，可想而知会占多少存储空间了

• 时间上的代价
  每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。在B+ 树上每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收啥的操作来维护好节点和记录的排序。

mysql 分区

https://zhuanlan.zhihu.com/p/101030536

实现方式
具体如何实现上面链接里有写，这里只需记住如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

这个是数据库分的，应用透明，代码无需修改任何东西。

mysql 分库分表

分库分表区别:
1.分库：磁盘利用度、数据库连接数等
2.分表：单纯业务数据量级问题

量不是很多，用mysql，

像消息等 hbase去存， bigtable, 分布式，根据分区键扩容

每个月创表，16位uid hash/16，分到16个表

分页：双写

个人用户查询一般是按最近，某年，三年前这样的粒度，这样就可以不用按用户在搞一张表。你就可以对后台用户的查询做些要求，比如每次只按年查，又或者，三年一个粒度，这个都可以调整。

MySQL死锁

死锁产生情况

RR隔离级别- 间隙锁导致的死锁

id（主键索引) no(普通索引) name
1 1001 小明
2 1002 小李
3 1003 小华
4 1004 小黄

第一步:
在上述两个事物当中，事物A首先开启了，然后执行一条查询的sql语句:也就是select…for update这样的语句。
因为记录的最大值为1004，1007不在这一个范围当中。此时，事物A对于表当中no范围为(1004,+∞)的no索引加上了一把锁间隙锁。

第二步:

事物B开启了，因为no值为1008的记录，不在范围(1004,+∞)的范围之内。因此，事物B也会加一个间隙锁，范围是(1004,+∞);由于间隙锁之间是互容的，因此事物B在执行select语句的时候，不会发生阻塞。

第三步: 事物A执行了一条插入的索引为1007的数值。但是，由于事物B对于事物A插入的范围加上了间隙锁，因此事物A一定要等待到事物B释放锁，才可以继续执行。

第四步:

事物B执行了一条插入的索引值为1008的sql语句。但是，由于事物A对于(1004,+∞)的范围加锁了。因此，事物B一定需要等待到事物A释放锁，才可以继续执行。

可以看到，此时，两个事物互相阻塞了。

RR隔离级别-先删后插入

create table `test` (
    `id` int(11) NOT NULL,
    `num` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `num` (`num`)
) ENGINE = InnoDB;

insert into
    test
values
(10, 10),
(20, 20),
(30, 30),
(40, 40),
(50, 50);

@Transaction
public void service(Integer id) {
    delete(id);
    insert(id);
}

对于上面的业务代码可能存在下面两种情况:

传入的参数 id 在原数据库中不存在
传入的参数 id 在原数据库中存在
本次主要会针对 id 记录在原数据库中不存在进行分析

在 T4 时刻 session 2 执行插入语句，同样会因插入位置的后一条记录中存在 Gap Locks 而需要生成 Insert Intention Locks 等待。此时很明显就形成了死锁，session 1 生成插入意向锁等待 session 2 和 session 3 上的 Gap 锁释放，而 session 2 同样生成插入意向锁等待 session 1 和 session 3 上的 Gap 锁释放

在 T4 时刻检测到死锁后，Mysql 会选择其中一个事务进行回滚，假设此时 session 2 被回滚，释放了其持有的所有锁资源，session 1 可以继续执行吗？ 很明显不可以，session 1 还同时在等待 session 3 上的 Gap 锁释放，继续阻塞等待

在 T5 时刻 session 3 开始执行插入语句，此时同 T4 时刻，死锁形成，session 1 生成的插入意向锁正在等待 session 3 上的 Gap Locks 释放，session 3 上生成的插入意向锁正在等待 session 1 上的 Gap Locks 释放，此时 session 3 回滚释放所有锁资源后，session 1 才可以最终执行成功

在完成了三个并发线程的死锁分析后，可能有人会想虽然有死锁，但通过死锁检测可以很快的检测出，程序也可以正常的执行，这有什么问题呢？ 其实上面没有问题主要是因为并发量较小，死锁检测可以很快检测出，如果此时将并发量扩大 100 倍甚至 1000 倍后，还会没有问题吗？

并发情况造成锁等待队列中的事务越来越多，而 Mysql 的整体死锁检测时间复杂度为 O(n^2)，锁等待队列中的事务较多时，每一次有新事务进行锁等待，死锁检测都需要遍历锁等待队列中在其之前等待的事务，判断是否会因自己的加入形成环，此时检测会非常消耗 CPU 资源，造成数据库整体性能下降，死锁检测耗时增加，且Mysql 活跃连接数大幅增加，并且因锁等待而连接无法释放，最终造成应用层连接池被打满。

如何避免MySQL当中的死锁现象 ？

1. 避免死锁常见的算法有有序资源分配法、银行家算法。

2. 可以通过增加超时时间、修改隔离级别、使用InnoDB引擎、优化SQL语句、分批处理数据等方法来防止死锁问题的发生。

3.使用SHOW ENGINE INNODB STATUS命令和MySQL的错误日志可以查看MySQL中的死锁情况。

方案一、设置任务超时等待时间
当在一个任务的等待时间超过了这个时间之后，就进行回滚;

在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

方案二、主动开启死锁检测
将参数 innodb_deadlock_detect 设置为 on。

当innodb检测发现死锁之后，就会进行回滚死锁的事物。

方案三、对于更新频繁的字段，采用唯一索引的设置方案
例如在上面的例子当中，可以把no字段设置成唯一索引。

方案四、将RR隔离级别改成RC（RC没有间隙锁）

在 MySQL 数据库中，可以通过以下方法来检测死锁：

1. 查看错误日志
   在 MySQL 的错误日志中，会记录每次出现死锁时的详细信息，包括死锁的事务 ID、死锁的表和锁方式等信息。你可以根据错误日志中的提示来查询和解决死锁问题。

2. 查询 INNODB_LOCKS 和 INNODB_LOCK_WAITS 系统表
   可以使用 SHOW ENGINE INNODB STATUS 命令来输出当前 InnoDB 存储引擎的状态信息，并在其中查找死锁相关信息。也可以手动查询 INNODB_LOCKS 和 INNODB_LOCK_WAITS 系统表，以了解当前的锁信息和等待队列信息。

具体操作步骤如下：

• 执行 SHOW ENGINE INNODB STATUS 命令，将输出详细状态信息。

• 在状态信息的输出内容中，寻找 “LATEST DETECTED DEADLOCK” 一行，该行下面的内容为最近一次检测到的死锁信息，包括事务 ID、死锁查询语句、锁等待、锁持有等信息。

• 在状态信息的输出内容中，查找 “TRANSACTIONS” 一节，该节下面的内容为当前所有的活跃事务信息，包括事务 ID，锁等待等信息。

• 执行以下 SQL 语句，查询 INNODB_LOCKS 和 INNODB_LOCK_WAITS 系统表，以查看当前锁信息和等待队列信息：