MySQL 面试知识点总结

MySQL默认端口：3306

1. MyISAM 和 InnoDB 的区别

索引结构不同

• MyISAM 是非聚簇索引：叶子节点中只保存了索引和与索引对应记录的物理页地址。
• InnoDB 是聚簇索引：即叶子节点中保存了完整的行记录，所以 InnoDB 文件自身就是索引文件。另一方面 InnoDB 的辅助索引树中，叶子节点中存储的是主索引，而不是地址和数据。换句话说，InnoDB 中使用辅助索引时，需要先遍历辅助索引树找到主索引，在依据主索引来主索引树中寻找对应的记录。

是否支持行级锁

• MyISAM 只有表级锁，不支持行级锁；
• InnoDB 支持行级锁、表级锁。默认为： 行级锁；

是否支持事务和奔溃后的安全恢复

• MyISAM 强调的是性能，每次查询具有原子性，其执行速度比 InnoDB 更快，但是不支持事务。
• InnoDB 支持事务，外部键等高级数据库功能。是具有事务(commit)、回滚（rollback）和奔溃修复能力（crash recovery capabilities）的事务安全（transaction-safe (ACID compliant)）型表。

是否支持外键

• MyISAM： 不支持
• InnoDB： 支持

是否支持 MVCC

仅 InnoDB 支持。应对高并发事务, MVCC 比单纯的加锁更高效；
MVCC 只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作；
MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁 来实现；

B+ 树

• 二叉排序树，查询平均复杂度为 $O(logN)$，当树陷入不平衡态时，将退化为链表，时间复杂度为 $O(N)$。
• AVL 树，查询复杂度为 $O(logN)$，当插入数据时，需要 1 次旋转维持平衡。删除数据时，需要 $O(logN)$ 次维持平衡。因此不利于频繁修改元素的需求。
• 红黑树： 从根节点到叶节点的最长的可能路径不多于最短的可能路径的两倍长。插入或者删除，O(1)次的旋转或者变色。主要问题还是每个节点的最大出度是2，所以树很深。
• B树：每个非叶子节点有很多个子树。每个节点中都存数据。
• B+树：只有叶子节点存储数据。此外叶子节点之间还通过双向链表进行连接（便于范围查询）。

与B树相比的优势

• 由于只有叶子节点存储存储记录，因此其非叶子节点的出度就会很大。换句话说，就是树高更小，查询很快。（最大的优势）

• 更适用范围查询。只对叶节点的指针链表进行遍历即可。

• 查询更稳定。因为数据都在叶子节点上，叶子节点都在同一层。所以查询时间复杂度稳定。

与 Hash 相比的优势

• 首先，使用 Hash法 时需要加载整个 Hash 表到内存中，如果数据量答，将非常消耗内存。B + 按照节点分段加载，可以减少内存消耗。

• 其次，Hash 更因该被使用在唯一查找一个值时（= 和 in 操作）。而不利于范围查找。而 B+ 树可以通过叶子节点中的链表来进行范围查找。

一些计算

InnoDB 引擎中，每个节点使用一个物理页（page），页的大小是 16KB，其中管理数据大约只占128个字节，其余空间都用来存储数据。

• 对于非叶子节点，记录中只包含索引键和指向下一层的指针。假设每个非叶子节点存储 $1000$ 条记录，则每条记录大约占用 $\frac{16KB}{1000}=16$ 字节。但索引是较短的字符串或INT时，这个假设是很合理的。 反之，当索引列长度过大时，很明显，每个非叶子节点所能存储的记录数就会减少，进而导致树的高度增加，索引查询的效果就会大打折扣，并且索引文件还浪费了更多的空间。
• 对与叶子节点：记录中包含了索引的键和数据（InnoDB），数据量更大。这里假设每个叶子节点存储100条记录（实际上对于InnoDB 的主索引树来说可能不足100， 而对于辅助索引可能远远大于100）。

那么基于以上假设，一个 3 层的 B+ 树：

第一层：有 $1$ 页，存储 1000 条记录
第二层：有 $1000$ 页， 存储 1000*1000 条记录
第三层：有 $1000^2$ 页， 存储 $1000^2\times 100$ ， 即 1 亿条记录。

相比于二叉树：一亿条记录需要 $\lceil lo{g}_{2}10^8\rceil =27$ 层。

事务的四大特性（ACID）

• 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保一个事务中的操作要么全部完成，要么都不完成；
• 一致性（Consistency）： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
• 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的（与数据库的隔离级别有关）；
• 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

事务隔离级别

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致：脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但不可避免幻读。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但是效率低下。

MySQL 默认隔离级别

InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ。使用的是 Next-Key Lock 算法，因此可以避免幻读的产生。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE 隔离级别。

脏读、幻读、不可重复读

• 脏读：一个事务对数据进行了修改，但没有 commit（因此可以 roll back）。而另一个并发事务读取到了这一数据。这就是脏读。

• 不可复读：同一个事物中，多次读取出来的结果（记录值）可能不相同：这是因为，在这一事务两次读取操作之间，有其他事务进行了 UPDATE 提交。

• 幻读：与不可复读类似。在一个事务两次读取操作之间，如果其他事务进行了 UPDATE 操作并 commit，那么该事务两次读取的结果是一直的。但是当其他事务进行的是 UPDATE 和 INSERT 操作，那么第二次的读取结果就会出现新的一行，但是其余值没变（依旧屏蔽了 UPDATE 操作）。这就是幻读。

幻读与不可重复读的区别：
.

不可重复读的重点是修改（UPDATE）比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除（INSERT 和 DROP/DELETE）比如多次读取一条记录发现记录增多或减少了。

MySQL 中的锁机制

强推，参考！
  Mysql用到了很多锁机制，比如行锁，表锁等；读锁，写锁等；都是在做操作之前先上锁。这些锁统称为悲观锁(Pessimistic Lock)。

• MyISAM 存储引擎采用表级锁(table-level locking)。
• InnoDB 存储引擎支持行级锁(row-level locking)和表级锁,默认为行级锁

表级锁和行级锁对比

• 表级锁： MySQL中锁定 粒度最大 的一种锁，对当前操作的整张表加锁。

  • 实现简单；
  • 开销小，加锁快；
  • 不会出现死锁；
  • 锁定粒度最大，触发锁冲突的概率最高，并发度最低。

• 行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。

  • 开销最大，加锁慢；
  • 会出现死锁；
  • 其加锁粒度最小，能大大减少数据库操作的冲突，并发度高。

• 页面锁：什么都处于 行级锁 与 表级锁 之间。

从上述特点可以看出，每个锁都各有特点，没有绝对的好坏。只能就具体应用的特点来选取锁。如果仅从锁的角度来说：

• 表级锁：更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用
• 行级锁：则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

MyISAM 的表锁

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

即一个用户对 MyISAM 表发起读操作，不会阻塞其他用户对同一个表的读请求。但此时会阻塞其余用户对同一个表的写请求；

• 注：
  • 如果表中不存在空洞（如：1,2,5,6；表中存在空洞：缺了3, 4项），则允许其他线程对该表进行并发插入操作（表尾插入）。
  • 当前会话如果对一个表加了读锁，则该会话将只能查询锁定表中的记录，不能对其他表进行更新或者访问）。

写操作与其他操作都是互斥的。如果有一个用户请求写操作，则该用户会锁住整张表，其他用户的读与写都将被阻塞，直到该写锁被释放）。

• 加读锁：lock table <表名> read;
• 加写锁：lock table <表名> write;
• 释放锁：unlock tables

MyISAM 给表加锁时，总是同时一次性的取得SQL语句中所需要的全部锁，因此 MyISAM 不会出现死锁。

InnoDB 行锁

InnoDB 与 MyISAM 的最大不同有两点：

• 一是支持事务（TRANSACTION）；
• 二是默认采用行级锁。

分析系统上的行锁争夺情况语句：

SHOW STATUS LIKE 'innodb_row_lock%';

如果发现锁争用比较严重，如：InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置 InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

InnoDB 的行锁模式

InnoDB 实现了以下两种类型的行锁：

• 共享锁（S）：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事物 T1 对数据对象 A 加上 S 锁，则事物 T1 可以读对象 A 但不能修改 A，其他事物只能再对 A 加 S 锁，而不能去加一个 X 锁，直到 T1 释放 A 上的 S 锁。这保证了其他事物可以读 A，但在 T 释放 A 上的 S 锁之前不能对 A 做任何的修改。

  -- 加共享锁 S
  SELECT * FROM <表名> WHERE ... LOCK IN SHARE MODE;
  

• 排他锁（X）：又称写锁。允许拥有该排他锁的事务更新数据，阻止其他事务对该数据集加 S 锁或 X 锁。直到该排他锁被释放。（如果使用不加锁的普通读是可以正常读取的）

  -- 加排他锁 X
  SELECT * FROM <表名> WHERE ... FOR UPDATE;
  

行锁和表锁是可以共存的，以实现多粒度的锁机制，InnoDB 还有两种内部使用的意向锁，这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行加共享锁，事务给一个数据行加共享锁前必须先取得该表的 IS 锁
• 意向排他锁（IX）：事务打算给数据行加排他锁，事务给一个数据行加排他锁前必须先取得该表的 IX 锁

意向锁是 InnoDB 自动加的，不需要用户干预。对于 UPDATE, DELETE, INSERT 语句，InnoDB 会自动给涉及的数据集加排他锁（X）；对于普通的 SELECT 语句不会加任和锁。

InnoDB 行锁的实现方式

InnoDB 行锁是通过给索引上的索引项加锁来实现的。这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则InnoDB 将使用表锁。

有时即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决 定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突 时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

间隙锁（Next-Key锁）

当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在范围内，但表中并不存在的该条记录，叫做“间隙（GAP）”，InnoDB 也将会对这个“间隙”加锁，这就是间隙锁。

举个例子：假如emp表中只有101条记录，其 empid 的值分别是 1,2,…,50，51，下面的SQL：

# 加了一个排他锁
SELECT * FROM  emp WHERE empid > 50 FOR UPDATE;

这是一个范围条件的检索，InnoDB 不仅会对符合条件的 empid 值为 $51$ 的记录加锁，也会对empid $>51$（这些记录并不存在）的“间隙”加锁。
其目的：是为了解决幻读问题，以满足隔离级别的要求。对于上面的例子，如果不加间隙锁，如果其他事物插入一条 empid $ge50$ 的任何记录，那么本事务如果再次执行 SELECT 语句，就会发生幻读。

乐观锁与悲观锁

参考

什么是MVCC，做了什么

参考

行级锁与表级锁本来就有许多不同之处，另外，事务的引入（主要是并发）也带来了一些新问题：如脏读、不可重复读、幻读。

脏读、不可重复读、幻读，都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本分为以下两种。

• 一种是在读取数据之前，对其加锁，阻止其他事务对数据进行修改。（并发度大大降低）
• 另一种是不加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户角度看，好像是数据库可以提供同一数据的多个版本。因此，这种技术叫做数据多版本并发控制（MVCC），也经常叫多版本数据库。

MVCC

MVCC(Mutil-Version Concurrency Control)，就是多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。

锁机制可以控制并发操作，但是其系统开销较大。MVCC可以在大多数情况下代替行级锁，使用MVCC，能降低其系统开销。

在 InnoDB 引擎中就是指在已提交读(READ COMMITTD)和可重复读(REPEATABLE READ)这两种隔离级别下的事务对于SELECT 操作会访问版本链中的记录的过程。

这就使得别的事务可以修改这条记录，反正每次修改都会在版本链中记录。SELECT可以去版本链中拿记录，这就实现了读-写，写-读的并发执行，提升了系统的性能。

---

在 MVCC 并发控制中，读操作可以分成两类：

• 快照读 (snapshot read)：读取的是记录的可见版本 (有可能是历史版本)，不用加锁。
• 当前读 (current read)：读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？

• 快照读：简单的 SELECT 操作，属于快照读，不加锁。（当然也有例外）。

  SELECT * FROM <表名> WHERE ?;
  

• 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

  下面的 SQL 语句都属于当前读，读取记录的最新版本。并且，读取之后还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加 S 锁 (共享锁)外，其他的操作，都加的是 X 锁 (排它锁)。

  SELECT * FROM <表名> WHERE ? lock in share mode; # 共享锁
  SELECT * FROM <表名> WHERE ? FOR UPDATE; # 排他锁
  INSERT INTO <表名> VALUES(...);
  UPDATE <表名> set ? WHERE ?;
  DELETE FROM <表名> WHERE ?;
  

MVCC 控制逻辑

1. 事务ID并非事务begin时分配，是延迟到需要分配时才分配的。

2. 事务在首次快照读时创建快照，并将快照版本的可见范围控制信息记录在read_view对象中。

MySQL表优化

当单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

限定数据的范围

务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；或者，当我们要读一行数据时，可以在语句中加上 LIMIT 1 的限制语句。

语句优化

• 应尽量 避免 在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
• 应尽量避免在 where 子句中使用 !=, <, > 操作符，否则引擎将放弃使用索引而进行全表扫描。
• 应尽量 避免 在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。
• like模糊全匹配也将导致全表扫描

读写分离

经典的数据库拆分方案，主库负责写，从库负责读；

垂直分区

根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。
　　简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。

优点： 可以使得列数据变小，在查询时减少读取的 Block 数，减少 I/O 次数。此外，垂直分区可以简化表的结构，易于维护。

缺点： 主键会出现冗余，需要管理冗余列，并会引起 Join 操作，可以通过在应用层进行 Join 来解决。此外，垂直分区会让事务变得更加复杂；

水平分区

  保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

  水平拆分是指数据表 行 的拆分，表的行数超过 200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

  水平拆分可以支持非常大的数据量。需要注意的一点是：分表 仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库 。

  水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，且跨节点 Join 性能较差，逻辑复杂。一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

  下面补充一下数据库分片的两种常见方案：

• 客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
• 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

千万级大表的优化方案

参考链接