MySQL面试总结

MySQL架构

连接层（验证客户端身份及权限）
服务层（缓存，解析器（词法分析、语法分析），优化器，执行器）
存储引擎层（数据存放地，Innodb、Myisam）

Buffer Pool：
是 InnoDB 维护的一个缓存区域，用来缓存数据和索引在内存中，主要用来加速数据的读写，如果 Buffer Pool 越大，那么 MySQL 就越像一个内存数据库，默认大小为 128M。
InnoDB 会将那些热点数据和一些 InnoDB 认为即将访问到的数据存在 Buffer Pool 中，以提升数据的读取性能。
InnoDB 在修改数据时，如果数据的页在 Buffer Pool 中，则会直接修改 Buffer Pool，此时我们称这个页为脏页，InnoDB 会以一定的频率将脏页刷新到磁盘，这样可以尽量减少磁盘I/O，提升性能。

redo log 保证持久性（存在于innodb引擎，采用WAL技术：Write Ahead Logging，先写日志再写磁盘），如果页损坏，采用doubble write（双写技术）解决数据页被破坏问题，实现数据页的可靠性
undo log 保证原子性（回滚日志）
binlog 用于主从复制或数据恢复（server层），以事件形式记录了所有的DDL和DML语句

脏页刷盘风险：InnoDB 的 page size一般是16KB，操作系统写文件是以4KB作为单位，那么每写一个 InnoDB 的 page 到磁盘上，操作系统需要写4个块。于是可能出现16K的数据，写入4K 时，发生了系统断电或系统崩溃，只有一部分写是成功的，这就是 partial page write（部分页写入）问题。这时会出现数据不完整的问题。
这时是无法通过 redo log 恢复的，因为 redo log 记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。doublewrite 就是用来解决该问题的。

为了解决 partial page write 问题，当 MySQL 将脏数据刷新到磁盘的时候，会进行以下操作：
1）先将脏数据复制到内存中的 doublewrite buffer
2）之后通过 doublewrite buffer 再分2次，每次1MB写入到共享表空间的磁盘上（顺序写，性能很高）
3）完成第二步之后，马上调用 fsync 函数，将doublewrite buffer中的脏页数据写入实际的各个表空间文件（离散写）。

InnoDB四大特性

1）、插入缓冲（insert buffer）：
对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池（Buffer pool）中，若在，则直接插入；若不在，则先放入到一个 Insert Buffer 对象中，然后再以一定的频率和情况进行 Insert Buffer 和辅助索引页子节点的 merge（合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚集索引插入的性能。
插入缓冲的使用需要满足以下两个条件：1）索引是辅助索引；2）索引不是唯一的。
2）、二次写（双写，double write）：
如果操作系统在将页写入磁盘的过程中发生崩溃，InnoDB 再次启动后，发现了一个 page 数据已经损坏，InnoDB 存储引擎可以从共享表空间的 doublewrite 中找到该页的一个最近的副本，用于进行数据恢复了。
3）、自适应哈希索引（adaptive hash index）：
InnoDB 会监控对表上索引的查找，如果观察到某些索引被频繁访问，索引成为热数据，建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应（adaptive）的。自适应哈希索引通过缓冲池的 B+ 树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB 会自动根据访问的频率和模式来为某些页建立哈希索引。
4）、预读（read ahead）：InnoDB 在 I/O 的优化上有个比较重要的特性为预读，当 InnoDB 预计某些 page 可能很快就会需要用到时，它会异步地将这些 page 提前读取到缓冲池（buffer pool）中，这其实有点像空间局部性的概念。
InnoDB使用两种预读算法来提高I/O性能：线性预读（linear read-ahead）和随机预读（randomread-ahead）。
其中，线性预读以 extent（块，1个 extent 等于64个 page）为单位，而随机预读放到以 extent 中的 page 为单位。线性预读着眼于将下一个extent 提前读取到 buffer pool 中，而随机预读着眼于将当前 extent 中的剩余的 page 提前读取到 buffer pool 中。
线性预读（Linear read-ahead）：线性预读方式有一个很重要的变量 innodb_read_ahead_threshold，可以控制 Innodb 执行预读操作的触发阈值。如果一个 extent 中的被顺序读取的 page 超过或者等于该参数变量时，Innodb将会异步的将下一个 extent 读取到 buffer pool中，innodb_read_ahead_threshold 可以设置为0-64（一个 extend 上限就是64页）的任何值，默认值为56，值越高，访问模式检查越严格。
随机预读（Random read-ahead）: 随机预读方式则是表示当同一个 extent 中的一些 page 在 buffer pool 中发现时，Innodb 会将该 extent 中的剩余 page 一并读到 buffer pool中，由于随机预读方式给 Innodb code 带来了一些不必要的复杂性，同时在性能也存在不稳定性，在5.5中已经将这种预读方式废弃。要启用此功能，请将配置变量设置 innodb_random_read_ahead 为ON。

注：
1、为什么先写日志再刷盘（磁盘）？
写日志是顺序IO，刷盘是随机IO

2、redo log大小固定，超出后前面已写的内容会被覆盖

3、innodb的页16kb，操作系统的页4kb，所以innodb的页写入磁盘需要4次

MySQL索引

索引：数据库管理系统中一个排序的数据结构，用于协助快速查询、更新数据库表中的数据
类型：普通索引、唯一索引（主键索引属于特殊的唯一索引）、全文索引

MySQL为什么使用 B+ 树，与其他索引相比有什么优点：
1）、数组，假设数组有序，采用二分查找效率还是比较高的，且等值和比较查询效率也很高，但是更新数据库会出现一个问题，可能要移动大量的数据（改变index），所以只适合存储静态数据；
2）、链表，可以很好的解决插入问题，但是查找效率太低，只能从某一端节点进行遍历查找；
3）、哈希表，集合了数组和链表的优点，查找和插入的效率都比较高，但是对于范围查询无法实现，另外，索引值也无法排序，也不支持最左匹配原则；
4）、二叉树，在插入和查找方面效率比较高，但是在极端情况下易形成斜树，此时又相当于链表了；
5）、平衡二叉树，虽然解决了斜树的问题，但二叉树每个节点最多只能有两个子节点，访问一个树节点进行一次IO，当数据量大时，树的深度也随之增加，查询效率极大降低；另外一个树节点仅存储一个索引，极大浪费了空间；
6）、红黑树，只有两路；不够平衡。
7）、多路平衡查找树（B树），基本可以满足索引，但存在缺陷，B树的节点保存着数据区，这样一个节点能存储的索引就会变少；因为数据区存储在根和枝个节点上，所以查找后IO次数也不太稳定；范围查找需要遍历树，效率存在一定问题；
8）、加强版多路平衡查找树（B+树），B树能解决的问题它都能解决，另外，B+树的数据区都保存在叶子节点，这样根节点和枝节点就能存放更多关键字，且每次拿取数据都要到叶子节点，IO次数比较稳定，即效率稳定；还有就是叶子节点之间有指针关联，排序能力比较强。
优点：1)它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。B Tree 解决的两大问题 是什么？（每个节点存储更多关键字；路数更多）
2)扫库、扫表能力更强（如果我们要对表进行全表扫描，只需要遍历叶子节点就可以 了，不需要遍历整棵 B+Tree 拿到所有的数据）
3) B+Tree 的磁盘读写能力相对于 B Tree 来说更强（根节点和枝节点不保存数据区， 所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）
4)排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表）
5)效率更加稳定（B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的）

索引的落地：
MyISAM：由.frm，.MYI，.MYD三个文件组成；.MYI的I代表Index，是MyISAM的索引文件，存放索引；.MYD的D代表Data，是MyISAM的数据文件，存放数据记录；即在MyISAM引擎中，索引和数据是两个独立的文件。
MyISAM 的 B+Tree 里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索引文件.MYI 中找到键值后，会到数据文件.MYD 中获取相应的数据记录。（辅助索引跟主键索引存储和检索数据的方式是没有任何区别的，一样是在索引文件 里面找到磁盘地址，然后到数据文件里面获取数据。）
InnoDB：由.frm，.ibd两个文件组成；InnoDB 里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件，都在.ibd 文件里面。
InnoDB 的主键索引的叶子节点上，它直接存储了我们的数据。（辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询，会根据主键值在主键索引中查询，最终取得数据。）
.frm是 MySQL 里面表结构定义的文件，不管你建表 的时候选用任何一个存储引擎都会生成。

InnoDB存储结构：表空间（2^32个页=64T）、段、簇（区，1M64个页）、页（16kb）、行

聚簇索引：将索引和数据行放到了一块，找到索引也就找到了数据。因为无需进行回表操作，所以效率很高。

非聚簇索引：数据存储和索引分开，找到索引后，需要通过对应的地址找到对应的数据行。MyISAM 的索引方式就是非聚簇索引。

覆盖索引：当索引上包含了查询语句中的所有列时，我们无需进行回表查询就能拿到所有的请求数据，因此速度会很快，一般针对辅助索引。

前缀索引：当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可 以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。

联合索引（复合索引）的底层实现？最左前缀原则？
联合索引底层还是使用B+树索引，并且还是只有一棵树，只是此时的排序会：首先按照第一个索引排序，在第一个索引相同的情况下，再按第二个索引排序，依次类推。
这也是为什么有“最左前缀原则”的原因，因为右边（后面）的索引都是在左边（前面）的索引排序的基础上进行排序的，如果没有左边的索引，单独看右边的索引，其实是无序的。

MySQL 中的索引叶子节点存放的是什么：
MyISAM和InnoDB都是采用的B+树作为索引结构，但是叶子节点的存储上有些不同。
MyISAM：主键索引和辅助索引（普通索引）的叶子节点都是存放 key 和 key 对应数据行的地址。在MyISAM 中，主键索引和辅助索引没有任何区别。
InnoDB：主键索引存放的是 key 和 key 对应的数据行。辅助索引存放的是 key 和 key 对应的主键值。因此在使用辅助索引时，通常需要检索两次索引，首先检索辅助索引获得主键值，然后用主键值到主键索引中检索获得记录。

什么是回表查询：
InnoDB 中，对于主键索引，只需要走一遍主键索引的查询就能在叶子节点拿到数据。
而对于普通索引，叶子节点存储的是 key + 主键值，因此需要再走一次主键索引，通过主键索引找到行记录，这就是所谓的回表查询，先定位主键值，再定位行记录。

走普通索引，一定会出现回表查询吗：
不一定，如果查询语句所要求的字段全部命中了索引，那么就不必再进行回表查询。
很容易理解，有一个 user 表，主键为 id，name 为普通索引，则再执行：select id, name from user where name = ‘joonwhee’ 时，通过name 的索引就能拿到 id 和 name了，因此无需再回表去查数据行了。

索引的创建：
1、在用于 where 判断 order 排序和 join 的（on）字段上创建索引
2、索引的个数不要过多（浪费空间，更新变慢）。
3、区分度低的字段，例如性别，不要建索引（离散度太低，导致扫描行数过多）。
4、频繁更新的值，不要作为主键或者索引（页分裂）。
5、组合索引把散列性高（区分度高）的值放在前面。
6、创建复合索引，而不是修改单列索引。
7、过长的字段，怎么建立索引？（前缀索引）
8、为什么不建议用无序的值（例如身份证、UUID ）作为索引？
1）、首先要转化为统一的数据比较大小，消耗时间；
2）、非递增顺序增加，会频繁导致页分裂与合并；
3）、占用字节太多，导致每个节点存储量变少，从而占用更多的磁盘空间以及降低IO的性能。

索引失效：
1、索引列上使用函数（replace、substr、concat、sum、count、avg…）、表达式、计算（+ - * /）；
2、字符串不加引号，出现隐式转换；
3、like条件中前面带%；
4、负向查询（not like，!=、<>、not in、not exist部分情况会失效）；
5、使用is null或is not null；
6、or语句前后没有同时使用索引；
7、违反最左匹配原则；
8、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效（mysql基于cost开销）。

MySQL事务

事务：数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成

开启事务方式：1、begin；2、start transaction
结束事务方式：1、提交事务commit；2、回滚事务rollback；3、客户端的连接断开

四大特性：ACID
A（Atomicity）原子性：事务内的一组操作，要么都成功，要么都失败
C（Consistent）一致性：一旦事务完成，前后数据完整性必须保持一致，现实中的数据不应该被破坏
I（Isolation）隔离性：指多个用户并发访问数据库时，一个用户的事务不能被其他用户的事务所打扰，多个并发事务之间数据要相互隔离
D（Durable）持久性：指一个事务一旦被提交，它对数据库的改变就是永久性的，即使数据库发生故障也不应该对其有任何影响
注：原子性、隔离性及持久性最后都是为了实现一致性

事务并发产生的问题：
1、脏读：一个事务读取了另一个事务未提交的数据，若数据被回滚，就会导致数据不一致
2、不可重复读：一个事务读取了另一个事务已提交的数据导致前后两次读取数据不一致
3、幻读：一个事务读取了另一事务提交了的insert数据，导致前后两次查询数据不一致（强调数量变化）

隔离级别：
1、Read Uncommitted（读未提交）：一个事务可以读取到其他事务未提交的数据
2、Read Committed（读已提交）：一个事务可以读取到其他事务已提交的数据，解决了脏读
3、Repeatable Read（可重复读）：同一个事务多次读取同样的数据结果一致，解决了脏读及不可重复读
4、Serializable（串行化）：所有的事务都串行执行，已经不存在并发事务了，解决了脏读、不可重复读以及幻读
注：
1、spring的隔离级别还有个default，即默认数据库的隔离级别（mysq数据库默认l隔离级别是Repeatable Read，Oracle数据库默认隔离级别是Read Committed）
2、Innodb引擎在RR（Repeatable Read）下就解决了幻读问题

如果要解决读一致性的问题，保证一个事务中前后两次读取数据 结果一致，实现事务隔离，应该怎么做？
1、LBCC（Lock Based Concurrency Control）基于锁的并发控制：读取数据的时候，锁定要操作的数据，不允许其他的事务修改； 缺点：极大影响操作数据的效率
2、MVCC（Multi Version Concurrency Control）多版本的并发控制：如果要让一个事务前后两次读取的数据保持一致， 那么可以在修改数据的时候给它建立一个备份或者叫快照，后面再来读取这个快照 就行了

MySQL锁及锁的算法

Innodb锁分类：
从性能上分为乐观锁(用版本对比来实现)和悲观锁
从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)
读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁
注：读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞
从对数据操作的粒度分，分为表锁和行锁 ：
行锁：
1、共享锁（Shared Locks，也叫S锁或读锁，加锁方式：select … lock in share mode）
2、排他锁（Exclusive Locks，也叫X锁或写锁，手动加锁方式：select … for update，自动加锁方式：insert、delete或update）
表锁：
1、意向共享锁：给一行数据加上共享锁之前，数据库会自动在这张表上面加一个 意向共享锁（如果一张表上面至少有一个意向共享锁，说明有其他的事务给其中的某些数据行加 上了共享锁）
2、意向排他锁：给一行数据加上排他锁之前，数据库会自动在这张表上面加一个意向排他锁（如果一张表上面至少有一个意向排他锁，说明有其他的事务给其中的某些数据行加 上了排他锁）

InnoDB 的行锁是怎么实现的：
InnoDB 行锁是通过索引上的索引项来实现的。意味着：只有通过索引条件检索数据，InnoDB 才会使用行级锁，否则，InnoDB将使用表锁！（InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。）
对于主键索引：直接锁住锁住主键索引即可。
对于普通索引：先锁住普通索引，接着锁住主键索引，这是因为一张表的索引可能存在多个，通过主键索引才能确保锁是唯一的，不然如果同时有2个事务对同1条数据的不同索引分别加锁，那就可能存在2个事务同时操作一条数据了。

为什么innodb通过唯一索引给数据行加锁，主键索引也会被锁住？
在innodb辅助索引里面，索引存储的是二级索引和主键值，而主键索引里面除了索引之外，还存储了完整的数据。所以我们通过辅助索引锁定 一行数据的时候，它跟我们检索数据的步骤是一样的，会通过主键值找到主键索引

一张表会不会没有索引？
1、若定义了主键索引（primary key），那么innodb会选择主键索引做为聚集索引
2、若没有显示定义主键，那么innodb会选择第一个不包含有NULL值的唯一索引做为主键索引
3、若也没有这样的唯一索引，则innodb会选择内置6字节长的rowid做为隐藏的聚集索引，它会随着行记录的写入而主键递增

InnoDB 锁的算法：
Record lock：记录锁，单条索引记录上加锁，锁住的永远是索引，而非记录本身。
Gap lock：间隙锁，在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。
Next-key lock：Record lock 和 Gap lock 的结合，即除了锁住记录本身，也锁住索引之间的间隙。

快照读：就是普通的select
当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。
select … lock in share mode;
select … for update;
insert;
update ;
delete;

RR 隔离级别下，普通的 select 使用快照读(snapshot read)，底层使用 MVCC 来实现。 加锁的select(select … in share mode / select … for update)以及更新操作 update, delete 等语句使用当前读（current read），底层使用记录锁、或者间隙锁、 临键锁（间隙锁是在可重复读隔离级别下才会生效）
RC 隔离级别下，普通的 select 都是快照读，使用 MVCC 实现。 加锁的 select 都使用记录锁，因为没有 Gap Lock。 除了两种特殊情况——外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会使用间隙锁封锁区间。 所以 RC 会出现幻读的问题。

锁优化建议：
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件范围，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
尽可能低级别事务隔离

MySQL优化

explain字段：（主要关注 type、key、rows、extra 等字段。主要是看是否使用了索引，是否扫描了过多的行数，是否出现 Using temporary、Using filesort 等一些影响性能的主要指标）
1、id：标识符，是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的（id越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。）
2、select_type：查询类型
1）、simple：简单查询。查询不包含子查询和union
2）、primary：复杂查询中最外层的 select
3）、subquery：包含在 select 中的子查询（不在 from 子句中）
4）、derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含 义）
5）、union：在 union 中的第二个和随后的 select
3、table：输出结果集的表
4、partitions：匹配的分区
5、type：表的连接类型
system > const > eq_ref > ref > range > index > ALL （一般来说，得保证查询达到range级别，最好达到ref）
1）、system：system是 const的特例，表里只有一条元组匹配时为system
2）、const：通过主键或唯一键查询，并且结果只有1行（也就是用等号查询）。因为仅有一行，所以优化器的其余部分可以将这一行中的列值视为常量
3）、eq_ref：通常出现于两表关联查询时，使用主键或者非空唯一键关联，并且查询条件不是主键或唯一键的等号查询
4）、ref：通过普通索引查询，并且使用的等号查询
5）、range：索引的范围查找（>=、<、in、between 等）
6）、index：全索引扫描
7）、ALL：全表扫描
6、possible_keys：查询时，可能使用的索引
7、key：实际使用的索引（如果key列为null而possible_keys列不为null，是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询）
8、key_len：使用的索引字段的长度（可以算出联合索引中具体使用了索引中的哪些列）
9、ref：列与索引的比较（这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有： const（常量），字段名（例：film.id））
10、rows：是mysql估计要读取并检测的行数（越少越好）
11、filtered：按表条件过滤的行百分比
12、extra：附加信息
1）、Using index：使用了覆盖索引，不需要回表
2）、Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖
3）、Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围
4）、Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化
5）、Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一 般也是要考虑使用索引来优化的
6）、Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是

如何定位慢查询SQL：打开慢查询日志开关，记录到慢日志文件，使用mysqldumpslow工具进行慢日志分析
slow_query_log 默认是off关闭的，使用时，需要改为on 打开　　　　　　
slow_query_log_file 记录的是慢日志的记录文件
long_query_time 默认是10S，每次执行的sql达到这个时长，就会被记录

mysqldumpslow工具统计结果分析：
Count 代表这个 SQL 执行了多少次；
Time 代表执行的时间，括号里面是累计时间；
Lock 表示锁定的时间，括号是累计；
Rows 表示返回的记录数，括号是累计。

如何做慢 SQL 优化：
首先要搞明白慢的原因是什么：是查询条件没有命中索引？还是 load 了不需要的数据列？还是数据量太大？所以优化也是针对这三个方向来的。
首先用 explain 分析语句的执行计划，查看使用索引的情况，是不是查询没走索引，如果可以加索引解决，优先采用加索引解决。
分析语句，看看是否存在一些导致索引失效的用法，是否 load 了额外的数据，是否加载了许多结果中并不需要的列，对语句进行分析以及重写。
如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行垂直拆分或者水平拆分。

union 和 union all 的区别：
union all：对两个结果集直接进行并集操作，记录可能有重复，不会进行排序。
union：对两个结果集进行并集操作，会进行去重，记录不会重复，按字段的默认规则排序。
因此，从效率上说，UNION ALL 要比 UNION 更快。

扩展

在CPU访问寄存器时，无论是存取数据抑或存取指令，都趋于聚集在一片连续的区域中，这就被称为局部性原理。
时间局部性（temporal locality）：被引用过一次的存储器位置在未来会被多次引用（通常在循环中）。
空间局部性（spatial locality）：如果一个存储器的位置被引用，那么将来他附近的位置也会被引用。（如果一个数据项被访问，那么与他地址相邻的数据项也可能很快被访问。）

参考

面试必问的 MySQL，你懂了吗？