MYSQL面试题

1.mysql体系架构是什么样？

链接层：主要是用于客户端和服务连接服务，主要完成是一些类似于链接处理、授权认证。

服务层：主要包括系统管理和控制工具、链接池、SQL接口、解析器、优化器等；

存储引擎层：主要负责数据的写入和读取，负责和底层文件得到交互。

系统文件层：主要包括系统文件。

2.存储引擎包括哪几种？

1.InnoDB：使用B+树作为索引结构，系统文件是以.ibd为后缀的文件，使用MVVC来获得高并发，并且实现了sql的标准的四种隔离级别，默认为可重复读。使用一种被称为next-key locking来避免幻读；还提供了插入缓存、二次写等功能。

每张表的存储都是按照主键的顺序进行存放。如果没有显示在表定义时指定主键，Innodb会为每一行生成一个6字节的rowid，并以此作为主键。

2.Memory：使用hash作为索引结构，将表中的数据进行存放在内存中，如果数据库发生崩溃后者重启，表中的数据会全部消失，非常适合存储临时数据和临时表。

3.MyIsam：使用B+树作为索引结构，系统文件是以MYD和MYI组成，MYD是用来存放数据文件，MYI是用来存放索引文件。缓冲池中只缓存索引文件，而不缓冲数据文件。

3.Innodb和Myisam区别？

1.Innodb支持事务、外键，Myisam不支持；

2.Innodb是聚集索引，如果是按照主键索引，叶子节点就包括主键索引和该行数据，只需要一次就可以查询出来；如果是按照辅助索引，B+Tree 的叶子节点中只存了索引列和主键的信息，所以需要先查询到主键在通过主键去主键索引查询数据，这也叫做回表。

Myisam是非聚集索引，使用b+树作为索引结构，索引和数据文件是分开的，索引保存的是数据文件的指针，所以在查询b+树的索引之后会根据地址去数据文件中去查询数据。

3.存储文件：Innodb是.frm 和.ibd文件 myisam是.frm和.myi/myd

4.InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁;行锁是实现在索引上，如果没有命中索引，也无法使用行锁，会退化为表锁。

4.如何选择应该用哪个引擎？

1.如果表中绝大多数只有读的操作可以使用myisam，如果既有读又有写操作就使用innodb；

2.如果需要支持事务，那么使用innodb；

3.在系统崩溃后，恢复数据myisam更加困难。

4.默认还是使用innodb。

5.请说下b+树的原理？

B+ Tree 是 B 树的一种变形，它是基于 B Tree 和叶子节点顺序访问指针进行实现，通常用于数据库和操作系统的文件系统中。

B+树有两种类型的节点：一种是内部节点（索引节点）和叶子节点，内部节点不存储数据，只存储索引；而数据都存在叶子节点中。内部节点的key都是按照从小到大的顺序进行排列，对于内部节点的key，其左子树的key都小于他，右子树的key大于他；叶子节点也是按照从小到大排列，并且节点之间通过指针相连。

6. b+树相比较平衡二叉树和b树的好处？

平衡二叉树：每个非叶子节点只允许最多两个子节点存在，每个非叶子节点的左子节点小于当前节点，右子节点大于该节点。

当数据量达到百万、千万之后，树的层数仍然会比较大，先不说AVL树维护平衡所需的代价，单论AVL树的层数就无法达到我们的要求，树的层级将会非常高，查询时每多经过一层，就会多进行一次io，树的层级多了之后查找效率就会很慢

b树： 1个节点可以储存超过2个元素，可以拥有超过两个子节点，每个节点的所有子树高度一致。但是如果查询一个范围的数据，那么的有些数据不在同一个节点上，就会查询多次，需要重头开始进行查询，又会增加io次数。

b+树：

针对于b树，做了相应的优化：

在叶子节点相邻节点增加双向指针，可以通过这个双向指针快速找到相邻节点。

非叶子节点只保存索引，通过冗余索引来构成B+树来查询，这样每次io可以查询到更多的索引元素。

7.索引类型有哪些？

1.hash索引：以o(1)的时间去查找，但是失去有序性；只支持精确查找，不支持部分查找和范围查找。当某个索引值 被使用的非常频繁，会在B+树的索引上在创建一个哈希索引，这样就让B+树具有哈希索引的一些优点，快速的hash查找。

2.B+树索引：InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找，这个过程也被称作回表。

3.全文索引：

8.B+树和B树的区别？

B+树的磁盘IO更低：因为B+树的内部节点只存储索引，内部节点更小，按照每页读取一次读取到内存中的关键字的数量就越多，io读写次数就越少。

B+树元素遍历效率更高：B+树可以只需要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中范围的查询是非常普遍的，B树不支持。

B+树的查询效率更加稳定：所有的关键字查询都是经过跟节点到叶子节点，所以关键字的查询路径长度相同，导致每一个数据的查询效率相当。

9.InnoDb的四大特性？

1.插入缓存（性能上的提升）是用来解决插入数据时对辅助索引的离散插入而产生的IO问题。对于非聚集索引的插入和修改，并不是每次都插入索引页中，而是先判断插入的索引页是否在缓冲池中，如果在缓冲池中，则直接插入；如果不在，则先放入到一个insertbuffer对象中，然后在以一定的频率进行insertbuffer和索引叶子节点合并操作。只限制为辅助索引和索引不是唯一的；

Change Buffer：1.0x版本之后，可以对DML操作-INSERT、DELETE、UPDAATE都进行缓冲。

Change Buffer可以细分为:Insert Buffer、Delete Buffer、Purge Buffer；

2.两次写（数据页的可靠性）：因为在之前存储引擎正在将页写入到数据表中，但是此时服务器挂了，数据只写了一部分，造成了数据的部分失效。

工作流程：

1.当一系列机制触发数据缓冲池中的脏页刷新时，并不直接写入磁盘数据文件中，而是先拷贝至内存中的doublewrite buffer中；

2.接着从两次写缓冲区分两次写入磁盘共享表空间中(连续存储，顺序写，性能很高)，每次写1MB；（备份，恢复使用；保证异常宕机时，数据完整性；）

3.待第二步完成后，再将doublewrite buffer中的脏页数据写入实际的各个表空间文件(离散写)；（数据持久化）

4.脏页数据的恢复

3.自适应hash索引：Innodb存储引擎会监控对表上各个索引的查询，如果可以建立hash索引提升查询速度，则建立hash索引。

只能用于等值比较；无法用于排序；MySQL自动管理，人为无法干预

4.预读： 预读请求是一个i/o请求，它会异步地在缓冲池中预先回迁多个页面，预计很快就会需要这些页面，这些请求在一个范围内引入所有页面。

数据库请求数据的时候，会将读请求交给文件系统，放入请求队列中；相关进程从请求队列中将读请求取出，根据需求到相关数据区(内存、磁盘)读取数据；取出的数据，放入响应队列中，最后数据库就会从响应队列中将数据取走，完成一次数据读操作过程；

判断后面几个数据读请求的数据是否相邻，再根据自身系统IO带宽处理量，进行预读，进行读请求的合并处理，一次性读取多块数据放入响应队列中，再被数据库取走。

10.高并发的情况下，如何做到安全的修改同一行数据？

1.使用乐观锁：基本上都是采用版本号进行更新，实现就是所有的请求都会去修改这行数据，但是需要进行比较版本号，版本号相同的话就去修改。

2.FIFO:缓存

3.使用悲观锁：所以一个事务拥有（获得）悲观锁后，其他任何事务都不能对数据进行修改，只能等待锁被释放才可以执行。，select * from User where name=‘jay’ for update

4.使用redis分布式锁。

11.请解释下乐观锁和悲观锁？

实现两种标准的行级锁：

共享锁：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

排它锁：会阻塞其他用户对同一表的读和写操；

悲观锁：先进行锁表，在进行业务更新。使用数据库内部的行级锁来对记录进行加锁，通常使用select 。。。for update。

乐观锁：先进行业务操作，在进行锁表。一般是在需要锁的数据上加上时间戳或者版本号。

悲观锁就是使用的共享锁和排它锁。而乐观锁通过版本号，从而实现CAS的原子性更新。

12.SQL优化的一般步骤是什么？

1.查看sql的执行频率：show status like 'innodb_rows_%';通过查看可以看到数据库以哪些操作为主；

2.定位执行效率低的操作：show processlist; 在info里边会有相关的sql语句；

3..explain分析执行计划：

id：id相同加载顺序从上至下执行；id值越大优先级别越高，越先被执行；

type:

SIMPLE 简单的select查询，查询中不包含子查询或者UNION（联合查询） PRIMARY: 查询中若包含了子查询，最外层查询标记为该标识 SUBQUERY: 在select 或 where 列表中包含了子查询

DERIVED	在FROM列表中包含的子查询，被标记为DERIVED（衍生）MYSQL会递归执行这些子查询，把结果放到临时表中

UNION:使用union关键字来查询，若第二个 select 出现在 UNION 之后，则标记为UNION。

type:显示的是访问类型;

NULL:mysql不访问任何表和索引；

system：表中只有一条数据，（等于系统表），const的特殊形式；

const:表示通过一次索引就找到了，

eq_ref：使用的是唯一性索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描；

ref：非唯一性索引扫描，返回匹配某个单独值得所有行。

range：只检索给定返回的行，使用一个索引进行选择行。where 之后出现between，<，>，in等操作

index：遍历索引树，通常比ALL快；

ALL:遍历全表以找到匹配的行数据。

key:实际使用的索引名字

rows:扫描行数

extra：

using file sort：文件排序，效率低；不通过索引直接返回排序结果的排序都是filesort排序。

①where条件和order by使用相同的索引 ②order by的顺序和索引顺序相同 ③orderby的字段都是升序或者降序

文件排序：借助内存或磁盘空间进行的排序操作统称为文件排序。

using temporary 使用临时表保存中间结果，MySQL在对查询结果排序时使用临时表，常见于order by和 group by 效率低 using index 表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率高；

4.show profile分析SQL:show profiles;命令来查看这些SQL语句再执行过程中耗时记录

5.确定问题并采取响应措施；

13.数据库死锁是什么意思？mysql怎样解决死锁？

死锁是两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种相互等待锁的现象；

1.两个事务进行等待时，当一个等待时间超过设置的某一个阈值时，其中一个事务进行回滚，另一个事务就可以进行正常执行；

2.wait-for graph：Innodb采用的是这种方式，是一种较为主动的死锁检测机制，每个事务请求锁并发生等待都会判断是否回路，若存在死锁，则采用回滚undo量最小的事务。

14.索引的类型有哪些？如何创建合理的索引？索引如何优化？

逻辑角度，索引类型包括:

单列索引包括：

主键索引：数据列不允许重复，不允许为null，一个表只有一个主键。

普通索引：最基本的索引，没有任何的约束，没有唯一性的限制，允许为NULL。

唯一索引：数据列不允许重复，允许为NULL，一个表可以有多个唯一索引。

前缀索引： 对于Blob、text和较大的varchar类型的列，不允许索引这一列的完整长度。截取该列的前几位进行查询基数，前缀的基数应该接近于完整的基数就可以使用了。区分度的公式是 COUNT(DISTINCT col)/COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少

多列索引包括：

覆盖索引：查询列要被创建的索引覆盖，不必读取数据行。不需要回表， 由于覆盖索引可以减少树的搜索次数，显著提升查询性能。

复合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并。当创建(a,b,c)复合索引时，想要索引生效的话，只能使用 a和ab、ac和abc三种组合！索引只能用于查找key是否存在（相等） ，遇到范围查询 (>、<、between、like左匹配)等就不能进一步匹配了，

最左匹配原则：当索引类型为联合索引时， 数据库会依据联合索引最左的字段来构建B+树，也叫最左匹配原则。

物理角度索引分为：

1.聚集索引：叶子节点存放的是整行数据，由于实际的数据页只能按照一棵B+树进行排序，所以一张表只能有一个聚集索引，聚集索引对于主键索引的查找和范围查询更快，叶子节点就是要查询的所有数据。

@1.当你定义一个主键时，innodb存储引擎把他当做聚集索引；

@2.当你没有定义主键时，innodb会定义第一个唯一索引，该索引的所有列均为非空，则就作为聚集索引；

@3.当既没有主键和符合条件的唯一索引，则会自动生成一个隐藏行的6字节的行id聚集索引。

2.非聚集索引：叶子节点存放的是键值还包含了一个书签，该书签可以告诉Innodb存储引擎哪里可以找到与索引相对应的行数据。

索引优化：

1.尽量避免全表扫描，order by或者group by 涉及到的列进行建立索引；

2.尽量避免where子句对字段进行null值判断，使用！=，<>会进行全表扫描；

3.应该尽量避免在where字句中使用or字来连接条件，就会进行全表扫描；

4.应尽量避免在where⼦句中对字段进⾏函数操作，这将导致引擎放弃使⽤索引⽽进⾏全表扫描；

5.在使⽤索引字段作为条件时，如果该索引是复合索引，那么必须使⽤到该索引中的第⼀个字段作为条件时才能保证系统使⽤该索引，否则该索引将不会被使⽤，并且应尽可能的让字段顺序与索引顺序相⼀致。

6.Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调⽤会引起明显的性能消耗，同时带来⼤量⽇志；

7..select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是⼀定要杜绝的。

8..任何地⽅都不要使⽤ select * from t ，⽤具体的字段列表代替“*”，不要返回⽤不到的任何字段。

9.当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层

10.尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询；

11.强制转换类型会全表扫描；

12.尽量避免在where语句中对字段进行null的判断。

15.select for update是什么含义？会锁表还是会锁行？

select for update会锁定查询出来的数据，其他事务不能将其进行读和写操作，避免了数据的不一致，直至数据库事务完成，才会释放这个锁。排它锁。

由于InnoDB 预设是Row-Level Lock，所以只有「明确」的指定主键，并且查询到数据，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。两者查询不到数据则无锁。

16.ACID是什么？

原子性：每次所有操作要么全部成功，要么全部失败；每个事务操作（增删改）都会记录一条与之对应的 undo log。

一致性：一致性即在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

隔离性：多个事务并发执行时，一个事务的执行不应影响其他事务的执行

持久性：事务结束后，所有的数据都会持久化到磁盘中，即使是断电或者崩溃，也会将数据进行恢复。实现基于redo-log,重做的全过程是：

将原始从磁盘中加载数据到内存，修改加载到内存的数据，写入到redo-log-buffer,之后提交事务，将重做日志缓存区的内容刷新到重做日志文件中，将内存中的脏页数据刷回到磁盘中。

17.请解释下redo log和undo log

redo log ：重做日志。主要用于系统崩溃数据的恢复。

存储在内存中的重做日志缓存区、存储在磁盘中的重做日志文件。

以下情况重做日志的缓存区数据写入到重做日志文件：

1.redo log buffer的日志占据redo log buffer总容量的一半时，将redo log写入磁盘；

2.一个事务提交时，他的redo log都刷入磁盘，这样可以保证数据绝不丢失；

3.后台线程进行定时刷新，每一秒将redolog写入到磁盘中；

4.mysql关闭时，redo log将数据写入到磁盘中。

参数 ：innodb_flush_log_at_trx_commit设置：

1.设置参数为1：表示事务执行提交时必须调用一次fsync操作，最可靠的设置。

2.设置参数为0：表示事务提交时不进行写入redo log操作，需要master thread在后台每秒进行一次重做的fsync操作。可能会丢失1s的事务。

3.设置参数为2：事务提交时只做write操作，只保证将redo log buffer写入到页面缓存中，不进行fsync操作。

undo-log：

在事务的修改之前，会把记录的原值先保存起来在做修改，以便在修改的过程中出错能够恢复原值。可以利用undo 日志，撤销未提交事务对数据库产生的影响；

事务在提交时，并不会立刻删除undo log,innodb会将改事务对应的undo log放入到删除列表中，后面通过后台purge thread 进行回收处理。

作用：

事务回滚-原子性： 事务处理过程中出现错误或者rollback语句，mysql利用undo log的备份将数据恢复到事务之前的数据；

mvcc：事务未提交前，当读取到某一行被其他事务锁定，他可以从undo log中分析出该记录以前的数据是什么，从而提供该版本版本信息。

undo log和之前说的redo log记录物理日志不一样，它是逻辑日志，可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录；

18.redo是如何保证事务的持久性的呢？

当事务提交时，innodb先将redo log buffer写入到redo log file进行持久化，待事务commit操作完成后才算完成。

19.mysql的in 和exist的区别？

in:先查询内表，再把内表结果与外表结果匹配，而内表多大都需要进行查询，不可避免，故外表大使用in，可加快效率。

exist：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或者 FALSE）来决定主查询数据结果是否得到保留。exist对外表进行loop循环，每次loop循环在对内表进行查询。只有当子查询的表数量远远大于外部表数据的时候，用exist查询效率好于in；

20.如何解决隔离性?事务的隔离级别？mvvc？

首先说一下会出现的几个问题： 脏读：读到其他事务未提交的数据。

可重复读：在一个事务中，最开始读到的数据和事务结束后读到的数据是一样的。

不可重复读：在同一个事务内，不同时刻读取数据是不一样的，例如在某个时刻，另一个事务将数据修改了并提交了。

幻读：针对于插入操作，假设A事务对某些行的内容进行修改，但是未提交，此时事务B插入了A修改之前的一样的数据，在A事务查询的时候，会发现刚才修改没有起作用。

事务隔离级别：

读未提交：读到其他事务未提交的数据，但没有办法保证你读到的数据最终一定是提交后的数据，如果中间发生回滚，那就会出现脏数据问题，读未提交没办法解决脏数据问题。

读已提交：读提交就是一个事务只能读到其他事务已经提交过的数据，也就是其他事务调用 commit 命令之后的数据。Oracle默认的隔离级别。

可重复读：事务不会读到其他事务对已有数据的修改，即使其他事务已提交，也就是说，事务开始时读到的已有数据是什么，在事务提交前的任意时刻，这些数据的值都是一样的。

串行化：解决了脏读、可重复读、幻读的问题，但是效果最差，它将事务的执行变为顺序执行，与其他三个隔离级别相比，它就相当于单线程，后一个事务的执行必须等待前一个事务结束。

为了解决不可重复读，或者为了实现可重复读，MySQL 采用了 MVVC (多版本并发控制) 的方式。

mvvc的实现原理：

三个隐式字段： 每条记录除了包含我们自定义的字段，还包括

最近修改的事务Id

回滚指针：指向这条记录的上一个版本

隐含的自增ID

事务1对name进行修改，

在事务修改该行记录时，数据库会对该行进行加锁；

然后把该行数据拷贝到undo log日志中，将事务id进行加1，同时将回滚指针指向undo log中的数据。事务提交后解锁。

Read View视图：

快照读：不对读操作加任何锁，读取的时候根据版本链和read view进行可见性判断，所以读取的数据不一定是数据库中的最新值

当执行查询sql时会生成一致性视图read view ，他由执行查询时所有未提交的事务ID数组，数组里最小的id为min id和已经创建的最大事务id（max_id）组成，查询数据结果需要根read view做比对从而得到快照结果。

版本比对规则：

如果事务id小于min_id,则这个版本是已提交的事务生成的，数据是可见的；

如果事务id大于max_id,则这个版本是未启动的事务，指定是不可见的；

如果事务id小于max_id并且大于min_id,事务id在数组中，表示还没有提交的事务生成的，不可见；

如果事务id小于max_id并且大于min_id,事务id不在数组中，表示还提交的事务生成的，可见；

21.什么是回表？覆盖索引有什么作用？

回表是指数据库根据索引找到指定的记录所在行后，还需要再根据rowid再次去数据块中取数据的操作。

解决回表的是建立覆盖索引，将所有的查询的字段放在索引中，查询的时候就避免回表。

22.一条SQL语句执行得很慢的原因有哪些？

1、大多数情况是正常的，只是偶尔会出现很慢的情况。

2、在数据量不变的情况下，这条SQL语句一直以来都执行的很慢。

针对第一种情况：

1.数据库刷新脏页。

2.拿不到锁；

针对第二种情况：

1.没有用到索引；

2.数据库用错索引；

\