原文地址：程序员大彬的 MySQL经典36问！

目录

事务的四大特性？

事务隔离级别有哪些？

索引

什么是索引？

索引的优缺点？

索引的作用？

什么情况下需要建索引？

什么情况下不建索引？

索引的数据结构

Hash 索引和 B+ 树索引的区别？

为什么 B+ 树比 B 树更适合实现数据库索引？

索引有什么分类？

什么是最左匹配原则？

什么是聚集索引？

什么是覆盖索引？

索引的设计原则？

索引什么时候会失效？

什么是前缀索引？

常见的存储引擎有哪些？

MyISAM 和 InnoDB 的区别？

MVCC 实现原理？

快照读和当前读

共享锁和排他锁

大表怎么优化？

bin log / redo log / undo log

bin log 和 redo log 有什么区别？

讲一下 MySQL 架构？

分库分表

什么是分区表？

分区表类型

查询语句执行流程？

更新语句执行过程？

exist 和 in 的区别？

truncate、delete 与 drop 区别？

having 和 where 的区别？

什么是 MySQL 主从同步？

为什么要做主从同步？

乐观锁和悲观锁是什么？

用过 processlist 吗？

事务特性 ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。

一致性是指一个事务执行之前和执行之后都必须处于一致性状态。比如 a 与 b 账户共有 1000 块，两人之间转账之后无论成功还是失败，他们的账户总和还是 1000。

隔离性。跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改。

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

先了解下几个概念：脏读、不可重复读、幻读。

脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

不可重复读是指在对于数据库中的某行记录，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，另一个事务修改了数据并提交了。

幻读是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行，就像产生幻觉一样，这就是发生了幻读。

不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

幻读和不可重复读都是读取了另一条已经提交的事务，不同的是不可重复读的重点是修改，幻读的重点在于新增或者删除。

事务隔离就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。

MySQL 数据库为我们提供的四种隔离级别：

Serializable (串行化)：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。

Repeatable read (可重复读)：MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决了不可重复读的问题。

Read committed (读已提交)：一个事务只能看见已经提交事务所做的改变。可避免脏读的发生。

Read uncommitted (读未提交)：所有事务都可以看到其他未提交事务的执行结果。

查看隔离级别：

设置隔离级别：

索引是存储引擎用于提高数据库表的访问速度的一种数据结构。

优点：

加快数据查找的速度

为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度

加快表与表之间连接的速度

缺点：

建立索引需要占用物理空间

会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为 B+ 树的高度一般在 2-4 层，最多只需要读取 2-4 次磁盘，查询速度大大提升。

经常用于查询的字段

经常用于连接的字段建立索引，可以加快连接的速度

经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度

where条件中用不到的字段不适合建立索引

表记录较少

需要经常增删改

参与列计算的列不适合建索引

区分度不高的字段不适合建立索引，如性别等

索引的数据结构主要有 B+ 树和哈希表，对应的索引分别为 B+ 树索引和哈希索引。InnoDB 引擎的索引类型有 B+ 树索引和哈希索引，默认的索引类型为 B+ 树索引。

B+ 树索引

B+ 树是基于 B 树和叶子节点顺序访问指针进行实现，它具有 B 树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

在 B+ 树中，节点中的 key 从左到右递增排列，如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，则该指针指向所有 key 大于等于 keyi 且小于等于 keyi+1 的节点。

进行查找操作时，首先在根节点进行二分查找，找到key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的数据项。

MySQL 数据库使用最多的索引类型是BTREE索引，底层基于 B+ 树数据结构来实现。

哈希索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的 key 值，将指向数据行的指针作为哈希表的 value 值。这样查找一个数据的时间复杂度就是 O(1)，一般多用于精确查找。

哈希索引不支持排序，因为哈希表是无序的。

哈希索引不支持范围查找。

哈希索引不支持模糊查询及多列索引的最左前缀匹配。

因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而 B+ 树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

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

B+ 树的节点只存储索引 key 值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中可以存放更多的节点，减少更多的 I/O 支出。

B+ 树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

1、主键索引：名为 primary 的唯一非空索引，不允许有空值。

2、唯一索引：索引列中的值必须是唯一的，但是允许为空值。唯一索引和主键索引的区别是：唯一约束的列可以为null且可以存在多个null值。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。创建唯一索引的 SQL 语句如下：

3、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。

4、全文索引：只有在MyISAM引擎上才能使用，只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引。

如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>、 2 and c = 3，那么 a、b 两个字段能用到索引，而 c 无法使用索引，因为 b 字段是范围查询，导致后面的字段无法使用索引。

如下图，对 (a, b) 建立索引，a 在索引树中是全局有序的，而 b 是全局无序，局部有序（当 a 相等时，会根据 b 进行排序）。

当 a 的值确定的时候，b 是有序的。例如a = 1时，b 值为 1，2 是有序的状态。当执行a = 1 and b = 2时 a 和 b 字段能用到索引。而对于查询条件a < 4 and b = 2时，a 字段能用到索引，b 字段则用不到索引。因为 a 的值此时是一个范围，不是固定的，在这个范围内 b 的值不是有序的，因此 b 字段无法使用索引。

InnoDB 使用表的主键构造主键索引树，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。

聚集索引的叶子节点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。

对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引。如果没有主键也没有合适的唯一索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为 6 个字节，它的值会随着数据的插入自增。

select的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以 MySQL 使用 b+ 树索引做覆盖索引。

对于使用了覆盖索引的查询，在查询前面使用explain，输出的 extra 列会显示为using index。

比如user_like 用户点赞表，组合索引为(user_id, blog_id)，user_id和blog_id都不为null。

explain结果的Extra列为Using index，查询的列被索引覆盖，并且 where 筛选条件符合最左前缀原则，通过索引查找就能直接找到符合条件的数据，不需要回表查询数据。

explain结果的Extra列为Using where; Using index， 查询的列被索引覆盖，where 筛选条件不符合最左前缀原则，无法通过索引查找找到符合条件的数据，但可以通过索引扫描找到符合条件的数据，也不需要回表查询数据。

索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。

尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘 I/O 较少，查询速度更快。

索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。

利用最左前缀原则。

导致索引失效的情况：

对于组合索引，不是使用组合索引最左边的字段，则不会使用索引

以 % 开头的 like 查询如%abc，无法使用索引；非 % 开头的 like 查询如abc%，相当于范围查询，会使用索引

查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效

判断索引列是否不等于某个值时

对索引列进行运算

查询条件使用or连接，也会导致索引失效

有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。

前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的数据行。

建立前缀索引的方式：

MySQL 中常用的四种存储引擎分别是：MyISAM、InnoDB、MEMORY、ARCHIVE。MySQL 5.5 版本后默认的存储引擎为InnoDB。

InnoDB 存储引擎

InnoDB 是 MySQL 默认的事务型存储引擎，使用最广泛，基于聚簇索引建立的。InnoDB 内部做了很多优化，如能够自动在内存中创建自适应 hash 索引，以加速读操作。

优点：支持事务和崩溃修复能力；引入了行级锁和外键约束。

缺点：占用的数据空间相对较大。

适用场景：需要事务支持，并且有较高的并发读写频率。

MyISAM 存储引擎

数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，可以使用 MyISAM 引擎。MyISAM 会将表存储在两个文件中，数据文件.MYD和索引文件.MYI。

优点：访问速度快。

缺点：MyISAM 不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。

适用场景：对事务完整性没有要求；表的数据都是只读的。

MEMORY 存储引擎

MEMORY 引擎将数据全部放在内存中，访问速度较快，但是一旦系统崩溃的话，数据都会丢失。

MEMORY 引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。

优点：访问速度较快。

缺点：

哈希索引数据不是按照索引值顺序存储，无法用于排序。

不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。

只支持等值比较，不支持范围查询。

当出现哈希冲突时，存储引擎需要遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的行。

ARCHIVE 存储引擎

ARCHIVE 存储引擎非常适合存储大量独立的、作为历史记录的数据。ARCHIVE 提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差。

是否支持行级锁 : MyISAM 只有表级锁，而InnoDB 支持行级锁和表级锁，默认为行级锁。

是否支持事务和崩溃后的安全恢复：MyISAM 不提供事务支持，而InnoDB提供事务支持，具有事务、回滚和崩溃修复能力。

是否支持外键： MyISAM不支持，而InnoDB支持。

是否支持MVCC ：MyISAM不支持，InnoDB支持。应对高并发事务，MVCC 比单纯的加锁更高效。

MyISAM不支持聚集索引，InnoDB支持聚集索引。

MVCC(Multiversion concurrency control) 就是同一份数据保留多版本的一种方式，进而实现并发控制。在查询的时候，通过read view和版本链找到对应版本的数据。

作用：提升并发性能。对于高并发场景，MVCC 比行级锁开销更小。

MVCC 实现原理如下：

MVCC 的实现依赖于版本链，版本链是通过表的三个隐藏字段实现。

DB_TRX_ID：当前事务 id，通过事务 id 的大小判断事务的时间顺序。

DB_ROLL_PRT：回滚指针，指向当前行记录的上一个版本，通过这个指针将数据的多个版本连接在一起构成undo log版本链。

DB_ROLL_ID：主键，如果数据表没有主键，InnoDB 会自动生成主键。

每条表记录大概是这样的：

使用事务更新行记录的时候，就会生成版本链，执行过程如下：

用排他锁锁住该行；

将该行原本的值拷贝到undo log，作为旧版本用于回滚；

修改当前行的值，生成一个新版本，更新事务 id，使回滚指针指向旧版本的记录，这样就形成一条版本链。

下面举个例子方便大家理解。

1、初始数据如下，其中DB_ROW_ID和DB_ROLL_PTR为空。

2、事务A对该行数据做了修改，将age修改为 12，效果如下：

3、之后事务B也对该行记录做了修改，将age修改为 8，效果如下：

4、此时 undo log 有两行记录，并且通过回滚指针连在一起。

接下来了解下 read view 的概念。

read view可以理解成将数据在每个时刻的状态拍成“照片”记录下来。在获取某时刻 t 的数据时，到 t 时间点拍的“照片”上取数据。

在read view内部维护一个活跃事务链表，表示生成read view的时候还在活跃的事务。这个链表包含在创建read view之前还未提交的事务，不包含创建read view之后提交的事务。

不同隔离级别创建 read view 的时机不同。

read committed：每次执行 select 都会创建新的 read_view，保证能读取到其他事务已经提交的修改。

repeatable read：在一个事务范围内，第一次 select 时更新这个 read_view，以后不会再更新，后续所有的 select 都是复用之前的 read_view。这样可以保证事务范围内每次读取的内容都一样，即可重复读。

read view 的记录筛选方式

前提：DATA_TRX_ID 表示每个数据行的最新的事务 ID；up_limit_id表示当前快照中的最先开始的事务；low_limit_id表示当前快照中的最慢开始的事务，即最后一个事务。

如果DATA_TRX_ID = low_limit_id：说明当前版本的记录的事务是在创建read view之后生成的，该版本的数据行不可以被当前事务访问。此时需要通过版本链找到上一个版本，然后重新判断该版本的记录对当前事务的可见性。

如果up_limit_id  delete。

二者作用的对象不同，where子句作用于表和视图，having作用于组。

where在数据分组前进行过滤，having在数据分组后进行过滤。

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。

因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

读写分离，使数据库能支撑更大的并发。

在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。

数据备份，保证数据的安全。

数据库中的并发控制是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观锁和悲观锁是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否数据是否被修改过。给表增加version字段，在修改提交之前检查version与原来取到的version值是否相等，若相等，表示数据没有被修改，可以更新，否则，数据为脏数据，不能更新。实现方式：乐观锁一般使用版本号机制或CAS算法实现。

show processlist 或 show full processlist 可以查看当前 MySQL 是否有压力，正在运行的SQL，有没有慢SQL正在执行。返回参数如下：

id：线程 ID，可以用kill id杀死某个线程

db：数据库名称

user：数据库用户

host：数据库实例的 IP

command：当前执行的命令，比如Sleep，Query，Connect等

time：消耗时间，单位秒

state：执行状态，主要有以下状态：

Sleep，线程正在等待客户端发送新的请求

Locked，线程正在等待锁

Sending data，正在处理SELECT查询的记录，同时把结果发送给客户端

Kill，正在执行kill语句，杀死指定线程

Connect，一个从节点连上了主节点

Quit，线程正在退出

Sorting for group，正在为GROUP BY做排序

Sorting for order，正在为ORDER BY做排序

info：正在执行的SQL语句