参考文章： MySQL 三万字精华总结 + 面试100 问

存储引擎

有哪些引擎，有何区别

主要是InnoDB、MyISAM，InnoDB 现在是 MySQL 默认的存储引擎，支持事务、行级锁定和外键。
存储结构
- MyISAM有.frm和.MYD和.MYI文件，分别存储表结构定义信息、数据信息和索引信息
- InnoDb有.frm和.ibd(.ibdata)文件，.frm存储表结构定义信息；.ibd和ibdata文件区别是，用独享表空间存储方式，每个表一个.ibd文件；用共享表空间存储方式，所有表使用一个.ibdata文件。
InnoDB 支持**事务和外键**，MyISAM 不支持。
InnoDB 是聚簇索引，MyISAM 是非聚簇索引。
InnoDB 最小的锁粒度是**行锁**，MyISAM 最小的锁粒度是表锁。

InnoDB 的优缺点

优点如上
缺点有
- 执行效率比MYISAM低，但MYISAM并发效率低

InnoDB 是表锁还是行锁，为什么

在命中索引的情况下是行锁，没命中索引的情况下是表锁。

索引

常见索引实现方式，及其对比，时间复杂度

数据结构：B+树索引、Hash索引、Full-Text全文索引、R-Tree索引

InnoDB 的索引方式

聚簇索引

B+树索引是怎么实现的

这一部分有很多内容，需要回看参考文章，继续补充。

B+树只在叶子节点存放数据，非叶子节点存放索引字段值和链指针。所有叶子节点（即数据节点）之间是一种链式环结构，双向链表。
相对于b-树，所有的节点都存放了数据，b+树在节点大小(叶大小)固定的情况下(一般为16kb)，能存储更多的索引字段值，从而减少树的高度，减少磁盘IO。

为什么用 B+树做索引节点

相对于b-树，所有的节点都存放了数据，b+树在节点大小(叶大小)固定的情况下(一般为16kb)，能存储更多的索引字段值，从而减少树的高度，减少磁盘IO。
数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。
B+书叶子节点中是双向链表，且在链表的头结点和尾节点也是循环指向的。方便进行大小比较。

一个节点存了多少数据，怎么规定大小

一个节点对应了存储引擎中的页大小，与磁盘页对应，是数据库磁盘管理的最小单位，可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K，默认为16k
引申,磁道多个相邻的扇区的扇区组成了磁盘块（block），块是操作系统读取的最小单元，mysql中读取多个相邻的磁盘块组合成磁盘页。

聚簇索引和非聚簇索引对比

MYISAM是索引结构的叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址，索引文件与数据文件分离，这样的索引称为"非聚簇索引"。
InnoDB引擎，主键索引结构的叶子节点的数据域，存放的就是实际的数据记录，这样的索引被称为“聚簇索引”。而非主键索引的叶子节点的数据域存放的是主键，需用通过回表查询其他字段信息。

为什么非主键索引结构叶子节点存储的是主键值？

保证数据一致性，不然改了数据，不仅要改主键索引结构叶子节点数据，还得改非主键索引结构叶子节点数据。
节省存储空间

是否越多索引越好，为什么

否，影响增删改。通过联合索引或索引覆盖解决索引过多的问题。

那为什么推荐使用整型自增主键而不是选择UUID？

UUID消耗更多空间
UUID数据没有连续性，难以做范围查询
UUID主键，新建或删除可能会导致调整左侧子树的结构，消耗更多时间。

为何不采用Hash方式

Hash索引里，多个数据在存储关系上是完全没有任何顺序关系的，对于区间查询是无法直接通过索引查询的，就需要全表扫描。
存在哈希碰撞问题，如果重复减值多，会导致效率低。

mysql查询

MySQL中 in和 exists 的区别？

in：in查询相当于多个or条件的叠加
exists()查询是将主查询的结果集放到子查询中做验证，根据验证结果是true或false来决定主查询数据结果是否得以保存。

SQL执行顺序

group by 是对字段进行聚合，select后的字段可以通过sum()等函数进行统计。
having 是对group by后的一些字段的筛选，where是筛选表字段，无法筛选聚合字段。

select goods_category_id , avg(goods_price) as ag from sw_goods group by goods_category having ag > 1000

锁机制

事务

事务意义

一个事务是一个连续的一组数据库操作，就好像它是一个单一的工作单元进行。在SQL层面，可以理解为一个事务是由多条SQL组成用以完成一个业务功能的共同体。

举例子：MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

ACID

A (Atomicity) 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
C (Consistency) 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
I (Isolation)隔离性：一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰
D (Durability) 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚

脏读、幻读、不可重复读（并发事务处理带来的问题）

更新丢失，当两个事物或多个事务都更新了同一条数据，但是这些事务彼此之间都不知道其他事务进行的修改，因此第二个更改覆盖了第一次的更改，说白了，就是事务A还没有提交之后，但是这个时候事务B更新了数据，那么事务A就丢失更新了。
脏读(Dirty Reads)：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
不可重复读（Non-Repeatable Reads)：事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
幻读（Phantom Reads)：幻读与不可重复读类似。它发生在一个事务A读取了几行数据，接着另一个并发事务B插入了一些数据时。在随后的查询中，事务A就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

幻读和不可重复读的区别：

不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的数据不一样。（因为中间有其他事务提交了修改）

幻读的重点在于新增或者删除：在同一事务中，同样的条件,，第一次和第二次读出来的记录数不一样。（因为中间有其他事务提交了插入/删除）

隔离级别

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

InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。

与 SQL 标准不同的地方在于，InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别下使用的是Next-Key Lock 算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）已经可以完全保证事务的隔离性要求。

如何解决脏读、幻读、不可重复读

加锁
MVCC

MVCC

意义：MVCC（多版本并发控制），可以认为是行级锁的一个变种，它在很多情况下避免了加锁操作。
实现：MVCC 的实现是通过保存数据在某个时间点的快照来实现的，分为乐观（optimistic）并发控制和悲观（pressimistic）并发控制。
限制：只在 COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作。
描述不到位，需要找另外的文档看看。

事务实现

事务的实现就是如何实现ACID特性。
事务的隔离性是通过锁实现，而**事务的原子性、一致性和持久性则是通过事务日志实现 **。

事务日志

定义：事务提交时，INNODB将修改内容追加到磁盘的事务日志文件上，当修改内容持久化到事务日志后，INNODB使用一个后台线程智能地刷新这些变更到.ibd(或.ibdata)。此方式称之为预写式日志。
事务日志包含：重做日志redo和回滚日志undo。redo_log是恢复提交事务修改的页操作，而undo_log是回滚行记录到特定版本。二者记录的内容不同，redo_log是物理日志，记录页的物理修改操作，而undo_log是逻辑日志，根据每行记录进行记录。
redo log（重做日志）
- 实现持久化和原子性
- 事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中
- 在事务提交之前,这些缓冲的日志都需要提前刷新到磁盘上持久化
- 事务提交之后，才会慢慢刷新到磁盘的数据文件上。
undo log（回滚日志）
- 实现一致性
- undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。

MySQL 有多少种日志吗？ 1.错误日志 2.查询日志 3.慢查询日志 4.事务日志 5.二进制日志(记录对数据库执行更改的所有操作) 6.中继日志(中继日志也是二进制日志，用来给slave 库恢复)

分布式事务提交

待补充，2PC、3PC

分布式

高可用方案

有没有用分库分表，如何实现

锁

锁的分类

MyISAM

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

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。

INNODB

存在行锁和表锁，行锁保含：
共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

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

索引失效会导致行锁变表锁。比如 vchar 查询不写单引号的情况。

加锁机制

乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题

乐观锁会“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式
悲观锁会“悲观地”假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。语句就是for update，update时INNODB会加上排他锁。

锁模式(InnoDB有三种行锁的算法)

记录锁(Record Locks)：单个行记录上的锁。对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
```
SELECT * FROM table WHERE id = 1 FOR UPDATE;
```
间隙锁（Gap Locks）：当我们使用范围条件不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录，叫做**“间隙”**。他事务不能在锁范围内插入数据,这样就防止了别的事务新增幻影行。GAP锁的目的，是防止同一事务的两次当前读，出现幻读的情况
临键锁(Next-key Locks)：临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。(临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。)

select for update有什么含义，会锁表还是锁行还是其他

for update 仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时，
通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的读取，更新与删除操作都会阻塞。
排他锁包含行锁、表锁。
InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

死锁

死锁产生

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环
当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁
锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方

死锁恢复

死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁。InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。但如果涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。

避免死锁

MyISAM避免死锁：在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁
InnoDB避免死锁：
- 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
- 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁
- 改变事务隔离级别，为什么改变隔离级别能避免锁未能理解。

出现死锁，怎么处理

用 show engine innodb status;命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

MYSQL优化

待补充

分区、分表、分库

分区

数据量较大时（一般千万条记录级别以上），MySQL的性能就会开始下降，这时我们就需要将数据分散到多组存储文件，保证其单个文件的执行效率

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。mysql将会根据指定的拆分策略，,把数据放在不同的表文件上。相当于在文件上,被拆成了小块.但是,对外给客户的感觉还是一张表，透明的。按照 range 来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的。
LIST分区：类似于按RANGE分区，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。
KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

为什么很少使用分区

表关联查询时，无法控制分区键是否在同一个数据文件，可能导致慢查询。

数据分片

指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中。数据分片的有效手段就是对关系型数据库进行分库和分表。

分表

垂直分表，通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表。然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系一般都是一对一的。
水平拆分，单表的容量不超过500W，否则建议水平拆分。是把一个表复制成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能；当然这些结构一样的表，可以放在一个或多个数据库中。方式有：MD5哈希拆分、时间拆分，热度拆分等。

分库

为什么要分库?

数据库集群环境后都是多台 slave，基本满足了读取操作; 但是写入或者说大数据、频繁的写入操作对master性能影响就比较大，这个时候，单库并不能解决大规模并发写入的问题，所以就会考虑分库。

主从复制

复制的基本原理

slave 会从 master 读取 binlog 来进行数据同步。复制的最大问题是延时。

master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
salve 将 master 的 binary log events 拷贝到它的中继日志（relay log）;
slave 重做中继日志中的事件，将改变应用到自己的数据库中。MySQL 复制是异步且是串行化的。

其他问题

说一说三个范式

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况）。即非主键字段需要完全依赖主键字段，不能依赖主键字段的一部分。如果主键是一个字段，则符合2NF。
第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：关键字段 → 非关键字段 x → 非关键字段y。即满足2NF后，非主键字段需各自独立，不存在相互之间的依赖关系。

mysql整体导读