mysql的架构图

mysql如何选择合适的引擎

• 如果业务系统中对数据要求比较高，且需要支持事务，那么 InnoDB 是不错的选择。
• 如果业务系统中查询多，更新少，而且对查询性能要求比较高，可以选择 MyISAM 引擎。
• 如果业务系统中在查询中有建立临时表的需求，那么可以考虑使用 Memory 引擎。

mysql为什么要采用B+树？

这里我们需要先了解索引，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构。再直白点就是我们可以把索引理解成图书或者字典的目录。

索引包含的内容：

• 索引值：就是表里面索引列对应的值，因为我们查询就是通过索引值来查询的，所以索引值必然要存储。
• 数据的磁盘地址（通过磁盘地址找到当前数据）或者直接存储整条数据：通过索引搜索的目的其实是需要找到当前对应的整条数据，所以必然需要存储地址或者数据。
• 子节点的地址：任何时候查询都是从根节点开始的，当发现根节点的数据并不是我们想要的数据，我们需要继续往下查询，所以需要知道当前根节点中所有子节点的引用地址。

B+树的特点：

• B+ 树的关键字的数量是跟路数相等的。
• B+ 树的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。而搜索到关键字也不会直接返回，也仍然会到最后一层的叶子节点。
• B+ 树的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。

那么为什么要采用B+树来存储呢？

因为当我们选择将数据存放到磁盘的时候，我们不可能直接将数据存放到磁盘，然后每一次存取的时候去磁盘中寻找，我们肯定会使用到数据结构来增加速度，我们会很快的想到选择使用树这种结构，但是我们使用二叉树的时候可能会出现二叉树退化为链表，所以我们会想到使用平衡二叉树来解决这个问题。

但是在 InnoDB 存储引擎中，页（Page）是用于管理数据的最小磁盘单位，页的默认大小为 16KB（不同版本会有差异）。而这个页也就是对应了上图中的每一个节点，每查询一次节点就需要进行一次 IO 操作。

如果使用平衡二叉树的话，那么每一个节点存放不了太多的数据，所以这个时候我们就会选择B+树这种数据结构。

使用B+树的优势:

• 扫库、扫表能力更强：如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵 B+ 树，因为其数据只存储在叶子节点。
• B+ 树的磁盘读写能力相对于 B 树来说更强： B+ 树的根节点和枝节点不保存数据， 所以一个节点可以保存更多的关键字，一次磁盘加载（即一次 IO 操作）能获取到相对更多的关键字。
• 天然具备排序能力：叶子节点上有下一个数据区的指针，数据形成了链表。
• 效率稳定：B+ 树永远是在叶子节点拿到数据，所以 IO 次数是稳定的，而 B 树运气好根节点就拿到数据，运气不好就要到叶子节点才能拿到数据，所花费的时间会有差异。

索引

MyISAM 引擎

MyISAM 存储引擎不支持行级锁，只有表级锁；不支持事务，也不支持外键，主要面向 OLAP 应用，是 MySQL 数据库5.5.8 版本之前默认的存储引擎，MyISAM 适用于不需要关心事务，读多写少的场景。

每张 MyISAM 表在磁盘上会创建三个文件：.frm，.MYD 和 .MYI，其中 .frm 文件为表结构文件，每个存储引擎都会有这个文件，.MYD 文件用来存储数据，.MYI 文件用来存储索引，也就是说 MyISAM 表的数据和索引是分开存储的，这一点和 InnoDB 不一样。

MyISAM 的 B+ 树里面，叶子节点存储的是当前索引的值以及当前数据文件对应的磁盘地址。所以如果从索引文件 .MYI 中找到键值后，会根据其存储的磁盘地址到数据文件 .MYD 中获取相应的数据记录，在 MyISAM 引擎中，主键索引和非主键索引没有差别，都是一样存储，查询速度也没有差别。

Innodb 引擎

InnoDB 存储引擎支持事务，主要是为了面向在线事务处理（OLTP）的应用而生，支持行锁和外键，其通过使用多版本并发控制（MVCC）来提升高并发性能，实现了 SQL92 标准的 4 种隔离级别。

从 MySQL 数据库 5.5.8 版本开始，InnoDB 为 MySQL 默认存储引擎。每张 InnoDB 表在磁盘上会创建两个文件：.frm 和 .ibd，其中 .frm 文件和 MyISAM 引擎一样，用来存储表结构的，.ibd 文件存储的是索引和数据，InnoDB 中索引和数据放在同一个文件中。

在 InnoDB 引擎中的 B+ 树叶子节点直接存储的是整条数据记录，而不是记录磁盘地址。InnoDB 引擎和 MyISAM 引擎还有一个最大的不同就是 InnoDB 引擎是以主键索引来组织数据的（主键索引和非主键索引的存储结构是不同的），InnoDB 存储引擎中这种组织数据的方式被称之为索引组织表（index-organized table），其中的主键索引也被称之为聚集索引（clustered index）。

聚集索引

聚集索引（又称之为聚簇索引），聚集的术语表示的是索引键值和数据紧凑的存储在一起。而数据又不可能同时存在两个地方，所以 InnoDB 每张表都有且只有一个聚集索引。换言之，也就是说每张表都必须有且只有一个主键。说到这里可能很多人就要反问了，我建表的时候没有主键索引也可以建表成功，那么这又是为什么呢？

其实如果我们没有显示的指定主键，InnoDB 会选择一个非空的唯一索引列作为主键，如果我们也没有创建非空的唯一索引，那么 InnoDB 就会选择其自己内置的一个 6 字节长的 ROWID 自增列作为主键。InnoDB 中聚集索引叶子节点直接存储的是整条数据，也就是说索引搜索到叶子节点之后就可以直接返回数据了，无需再去磁盘获取数据。

非聚集索引

非聚集索引的叶子节点存储的是当前索引的键值和主键索引的键值。所以非聚集索引查询数据和聚集索引查询数据是不同的，因为非聚集索引的叶子节点只有当前索引的键值和主键的键值，也就是说查询数据的时候获取到非聚集索引的叶子节点只能拿到当前索引值和主键索引值。

myqsl是怎么选择索引的

在mysql中输入SHOW INDEX FROM table_name 。

• Cardinality：索引中唯一值的估计值。这个数字越接近总数，则表示索引的选择性越高，如果这个数很小，那么可以考虑删除这个索引，因为重复值太多，选择性就不高，用到索引的概率也相对较低。

Cardinality 是通过采样来实现计算的，也就是说并不是一个精确值，而是一个统计值。而且这个值并不一定会实时更新（如果表太小，可能会实时进行更新），因为如果表过大的话，每次更新都会带来消耗，如果想要手动更新的话，在 InnoDB 引擎下可以执行命令 ANALYZE TABLE 表名来进行手动更新。

注意写sql语句的时候：

最左匹配原则一般针对的是模糊查询或者多列联合索引的场景。对模糊查询（like 语句）而言，会对关键字从左到右开始匹配，如果第一个字符就是不确定的，那么就无法使用索引，因为检索的时候无法从根节点根据关键字开始检索，只能全表查询；对于多列联合索引而言，如果索引有 a 和 b 两列，当查询条件只有 b 这一列，因为 a 列并未作为条件，也会导致无法从根节点开始检索，从而导致索引失效。

• 在索引列上使用函数（replace/substr/concat/sum/count/avg 等），使用表达式或者计算（+、-、*、/）。
• 字符串不加引号，会出现隐式转换，相当于使用函数 to_char()。
• 使用 !，<>，not like，not in 等反向查询。

事务的ACID原理

A（Atomicity）原子性

原子性指的是数据库事务是不可分割的一部分，只有一个事务中的所有操作都成功，这个事务才算执行成功，一旦有一个操作失败，那么其他成功的操作也必须回滚。

C（Consistent）一致性

一致性指的是在事务开始之前和事务结束之后，数据库的完整性约束都没有被破坏，事务执行的前后都是合法的数据状态。

I（Isolation）隔离性

隔离性就是说每个事务之间的操作应该相互隔离，互不干扰。比如说一个事务提交之前对另一个事务不可见。

D（Durable）持久性

持久性这个概念就比较容易理解了，就是说事务一旦提交成功了，那么就应该是持久的，即使是数据库重启，服务器宕机等情况发生，数据都不会丢失（当然这个不能包括因为地震等自然灾害导致的存储数据的硬盘发生不可逆的损坏）。

事务的四大隔离级别

• 未提交读简称：RU，表示一个事务可以读取到其他事务未提交的数据，这种也叫做脏读。未提交读是最低的隔离级别，等于没有隔离，基本上没有数据库会使用这个级别。

• 已提交读简称：RC。表示一个事务只能读取到其他事务已提交的数据（已提交读是 Oracle 和 SQL Server 数据库默认的隔离级别）。就是说在一个事务里面，执行同样的查询，会出现两次不一样的结果。这种隔离级别解决了未提交读产生的脏读问题，但是会出现不可重复读的问题。

• 可重复读简称 RR。这种隔离级别解决了不可重复读问题（有时候一个事务中出现不可重复读会影响到系统），就是说在同一个事务中，不管在任何时候执行相同的查询语句，结果都是一样的（对于如何实现可重复读我们在稍后介绍）。

• 可重复读的隔离级别虽然解决了不可重复读的问题，但是这种级别会出现幻读问题。

• 串行化也就是说所有的事务都是串行执行的，也就不存在并发事务，脏读，可重复读和幻读。

MVCC

MVCC（Multi Version Concurrency Control），多版本的并发控制。当修改数据的时候，可以为这条数据创建一个快照，后面就可以直接读取这个快照。

可重复读的实现方式正是基于 MVCC 控制的，而 MVCC 模式下，查询的数据是从快照中读取，所以也被称之为快照读。

mvcc是在并发访问数据库时，通过对数据做多版本管理，避免因为写锁的阻塞而造成读数据的并发阻塞问题。

通俗的讲就是MVCC通过保存数据的历史版本，根据比较版本号来处理数据的是否显示，从而达到读取数据的时候不需要加锁就可以保证事务隔离性的效果。

什么是锁

锁是一种用于保证在并发场景下每个事务仍能以一致性的方式读取和修改数据的方式，当一个事务对某一条数据上锁之后，其他事务就不能修改或者只能阻塞等待锁的释放，所以锁的粒度大小一定程度上可以影响到访问数据库的性能。

从锁的粒度上来说，我们可以将锁分为全局锁，表锁和行锁。

在 InnoDB 引擎中，是允许行锁和表锁共存的。但是这样就会有一个问题，假如事务 A 给 t 表其中一行数据上锁了，这时候事务 B 想给 t 表上一个表锁，这时候怎么办呢？事务 B 怎么知道 t 表有没有行锁的存在，如果采用全表遍历的情况，当表中的数据很大的话，加锁都要加半天，效率直线下降，所以为了避免这个问题， MySQL 中就又引入了意向锁。

意向锁也是属于表锁的一种，分为两种类型：意向共享锁（Intention Shared Lock）和意向排他锁（Intention Exclusive Lock），这两种锁又分别可以简称为 IS 锁和 IX 锁。

意向锁是 MySQL 自己维护的，用户无法手动加意向，意向锁有两大加锁规则：

• 当需要给一行数据加上 S 锁的时候，MySQL 会先给这张表加上 IS 锁。
• 当需要给一行数据加上 X 锁的时候，MySQL 会先给这张表加上 IX 锁。

这样的话上面的问题就迎刃而解了，当需要给一张表上表锁的时候，只需要看这张表是否有对应的意向锁就可以了，无需遍历整张表。

行锁锁住的是索引，一旦表没有索引，则会进行锁表

当辅助索引被锁住后，其对应的主键索引也会被锁住，而在 InnoDB 中，索引即数据，所以主键索引被锁，就相当于整条数据被锁住。

只使用覆盖索引主键索引还是会被锁住

锁怎么解决幻读？

实际上行锁有三种算法：记录锁（Record Lock），间隙锁（Gap Lock）和临键锁（Next-Key Lock），而之所以能做到防止幻读，正是临键锁起的作用。

• 记录锁比较容易理解，就像前面的例子中当我们的查询语句能命中一条记录的时候，InnoDB 就会使用记录锁，锁住所命中的这一行记录。

• 当我们的查询没有命中记录的时候，InnoDB 就会使用间隙锁。

• 间隙锁与间隙锁之间不冲突，也就是事务 A 加了间隙锁，事务 B 可以在同一个间隙中加间隙锁。

• 间隙锁主要是会阻塞插入操作。

• 这里仅针对 RR 隔离级别，对于 RC 隔离级除了外键约束和唯一性约束会加间隙锁，其他情况并没有间隙锁，自然也就没有了临键锁，所以 RC 级别下加的行锁可以认为都是记录锁，没有命中记录则不加锁，也就是 RC 的隔离级别是没有解决幻读问题的。

行锁的加锁规则

1. MySQL 中默认使用 RR 隔离级别，而 RR 隔离级别下默认使用临键锁，也就是默认遵循左开右闭区间范围加锁。
2. 当使用主键或者唯一索引命中记录时，临键锁会退化为记录锁（如文中最开始例子）。
3. 当查询语句未命中任何记录时候，临键锁会退化为间隙锁（如间隙锁例子）。
4. 查找过程中访问到的对象才会加锁。
5. 索引上进行等值查询时，在向右遍历时发现最后一个值不满足等值条件的时候，临键锁会退化为间隙锁，即一般会锁住最后一个命中的 索引 value 和其下一个左开右闭区间。