MySQL面试题

Narcissus 收录于 MySQL

2022-08-16 约 14998 字预计阅读 30 分钟

1. MyISAM与InnoDB的区别

MySQL支持的存储引擎有MyISAM、InnoDB、MEMORY等。

在MySQL5.5之前，默认存储引擎是MyISAM，5.5版本之后，默认存储引擎变为了InnoDB。下面对比一下两者的区别：

是否支持事务

MyISAM不提供事务支持。InnoDB提供事务支持，具有提交和回滚事务的能力。对于InnoDB的每一条SQL语句都默认封装成事务，自动提交。

是否支持外键

MyISAM不支持外键，而InnoDB支持。

是否支持行级别锁

MyISAM只有表级锁，InnoDB支持行级锁和表级锁，默认行级锁。表锁意味着一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。

表级锁：MySQL中锁粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗比较少，加锁快，不会出现死锁。但锁粒度最大，并发度最低，MyISAM和InnoDB引擎都支持表级锁。

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

是否支持数据库异常崩溃后的安全恢复

MyISAM 不支持，而 InnoDB 支持。使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

MySQL InnoDB引擎使用redo log(重做日志)保证事务的持久性，使用undo log(回滚日志)保证事务的原子性。

MySQL InnoDB引擎通过锁机制、MVCC(多版本并发控制)等手段保证事务的隔离性。

保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

索引类型区别

InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快

为什么InnoDB没有了这个变量呢？

因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。

InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有

为什么InnoDB使用自增ID作为主键？

自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

2. 事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。事务支持是在引擎层实现的，MySQL是支持多引擎的系统，但并不是所有引擎都支持事务。

2.1 事务的ACID特性

事务的特性有ACID：即原子性、一致性、隔离性、持久性。

原子性（Atomicity）：事务是最小的执行单位，不允许分隔。事务的原子性确保动作要么全做，要么完全不起作用。
一致性（Consistency）：执行事务前后，数据保持一致性。例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的。
隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间的数据是独立的。
持久性（Durability）：一个事务被提交后，他对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

事务的隔离性由锁来实现，redo log(重做日志)用来保证事务的原子性和持久性，undo log（回滚日志）保证了事务的一致性。

2.2 并发事务带来了哪些问题？

并发事务会带来以下问题：写写并发会带来丢失更新的问题，读写并发会带来以下三个问题：

脏读：即读到了并一定最终存在的数据，当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，未提交意味着数据可能会回滚，也就是最终不会存到数据库中，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
不可重复读：指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，即在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据做了修改并且提交了。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读：幻读与不可重复读类似。它发生在一个事务（T1）读取了某个范围内的几行数据，接着另一个并发事务（T2）插入了一些数据在这个范围内。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

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

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

查看隔离级别：select @@trasaction_isolation;

2.3 事务的隔离级别

为了解决并发事务带来的问题，所以有了四个隔离级别：

读未提交(READ-UNCOMMITTED)：最低的隔离级别，一个事务还没提交时，他做的变更就能被别的事务看到。可能会导致脏读、幻读和不可重复读。
读提交(READ-COMMITTED)：一个事务提交后，他做的变更才会被其他事务看到。可以阻止脏读，但幻读和不可重复读仍可能发生。
可重复读(REPEATABLE-READ)：一个事务执行过程中看到的数据，总是跟这个事务启动时看到的数据保持一致。可以阻止脏读与不可重复读，但幻读仍可能发生。是InnoDB的默认隔离级别。
可串行化(SERIALIZABLE)：最高隔离级别，对同一行数据，读写都会加锁。可以防止脏读、不可重复读和幻读。

在实现上，数据库会创建一个视图，访问的时候以视图的逻辑结果为准。

“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念。

在“读提交”隔离级别下，视图在每个SQL语句开始执行的时候创建。

在“可重复读”隔离级别下，视图在事务启动时创建，整个事务存在期间都用这个视图。

“串行化”隔离级别下直接用加锁的方式避免并行访问。既不能并发写，也不能并发读。

2.4 MySQL中如何实现事务隔离的

实现可重复读

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

我们在数据库表中看到的一行记录可能实际上有多个版本，每个版本除了记录数据本身外，还有一个表示版本的字段，记为row trx_id，是使其产生的事务的id。事务ID记为transaction id。

快照，学名叫做一致性视图，这也是可重复读和不可重复读的关键，可重复读是在事务开始的时候生成一个当前事务全局性的快照，而读提交则是每次执行语句的时候都重新生成一次快照。

2.5 快照读与当前读

表记录有两种读取方式：

快照读：读取的是快照版本。普通的SELECT就是快照读。通过mvcc来进行并发控制的，不用加锁。
当前读：读取的是最新版本。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。

快照读情况下，InnoDB通过mvcc机制避免了幻读现象。而mvcc机制无法避免当前读情况下出现的幻读现象。因为当前读每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。

那么MySQL是如何避免幻读？

在快照读情况下，MySQL通过mvcc来避免幻读。
在当前读情况下，MySQL通过next-key来避免幻读（加行锁和间隙锁来实现的）。
Serializable隔离级别也可以避免幻读，会锁住整张表，并发性极低，一般不会使用。

2.5 幻读

在可重复读隔离级别下，mysql通过mvcc来避免幻读。普通的select语句就是快照读。是不会看到别的事务插入的数据的。因此不会出现幻读问题。

如果查询语句后面加了for update，就是当前读。而当前读的规则就是要能读到所有已经提交的记录的最新值。

存在如下问题：为了实现并发写，我们通过加行锁的方式来解决，但是行锁只能锁住行，无法阻止新插入数据的问题，因为新插入记录这个动作，需要更新的是记录之间的间隙，这就造成了幻读问题。

如何解决当前读情况下的幻读问题？

因此InnoDB只好引入间隙锁(Gap Lock)，间隙锁就是锁住两个值之前的空隙。

注意，间隙锁之间不存在冲突，“往间隙中间插入一个记录"这个操作才和间隙锁有冲突。

间隙锁和行锁合成为next-key lock(临键锁)，每个next-key Lock 是前开后闭区间。
间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实影响了并发度。
RR(可重复读)隔离级别下间隙锁才有效，RC(读提交)隔离级别下没有间隙锁。

2.6 MVCC实现原理

MVCC(Multiversion concurrency control) 就是同一份数据保留多版本的一种方式，进而实现并发控制。在查询的时候，通过read view和版本链找到对应版本的数据。实现原理主要依赖记录中的三个隐式字段、undo log（回滚日志）、read view(读视图)来实现。

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

隐式字段

每行记录除了我们定义的字段外，还有三个隐式字段:DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID。

DB_ROW_ID：如果数据表没有主键，InnoDB会自动生成主键。DB_TRX_ID是当前操作该记录的事务ID,而DB_ROLL_PTR是一个回滚指针，用于配合undo日志，指向上一个旧版本。

因此每条表记录大概如下：

undo log

undo log主要分两种

Insert ungo log:代表事务在insert新记录时产生的回滚日志，只在事务回滚时需要，并且在事务提交后立即被删除。

update undo log:事务更新删除时产生的回滚日志，不仅在事务回滚时需要，在快照读时也需要，不能被随便删除，提交时放入 undo log 链表，等待 purge线程 进行最后的删除，只有在快照读或事务回滚不涉及该日志时才被删除。

read view (读视图)

Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务ID的链表，表示生成read view的时候还在活跃的事务。

不同的隔离级别生成read view的时机不同

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

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

read view记录筛选方式

前提：DATA_TRX_ID 表示每个数据行的最新的事务ID,即我们需要判断该行数据对我们的事务(即read view)是否可见的数据行事务ID。up_limit_id表示当前快照活跃事务ID 中最小事务ID；low_limit_id表示目前出现过的最大事务ID+1，即下一个将被分配的事务ID。

如果DATA_TRX_ID < up_limit_id：说明在创建read view时，修改该数据行的事务已提交，该版本的记录可被我们的事务读取到。

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

如果up_limit_id <= DATA_TRX_ID < low_limit_i：

需要在活跃事务链表中查找是否存在ID为DATA_TRX_ID的值的事务。

如果存在，因为在活跃事务链表中的事务是未提交的，所以该记录对我们的事务是不可见的。此时需要通过版本链找到上一个版本，然后重新判断该版本的可见性。

如果不存在，说明事务DATA_TRX_ID 已经提交了，这行记录对我们的事务是可见的。

总结

InnoDB 的MVCC是通过 read view 和版本链实现的，版本链保存有历史版本记录，通过read view 判断当前版本的数据是否可见，如果不可见，再从版本链中找到上一个版本，继续进行判断，直到找到一个可见的版本。

3. 索引

索引是一种用于快速查询和检索数据的数据结构，**索引是在存储引擎层实现而不是在服务器层实现的。**常见的索引结构有: B 树， B+树和 Hash。

3.1 索引的优缺点

优点

使用索引可以加快数据检索速度
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点

创建、维护索引需要耗费许多时间。对数据进行增删改的时候，如果数据有索引，那么索引也需要动态修改，降低了SQL执行效率。
索引需要使用物理文件存储，也会耗费一定空间。

3.2 索引常见模型分析

哈希表

哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））。

为什么不使用哈希表作为索引的底层模型？

Hash冲突问题：多个不同的Key，可能最后会得到相同的Value。所以哈希索引的性能不稳定。

Hash索引不支持顺序和范围查询：这是哈希索引的最大缺点，哈希表只能够进行等值查询。

如果需要存储大量URL，并根据URL进行搜索查找，可以如下设计：

新增一个被索引的url_crc列，使用CRC32做哈希，就可以使用如下方式进行查询：

SELECT id FROM url WHERE url = "http://www.mysql.com" AND url_cc=CRC32("http://www.mysql.com");

则MySQL优化器会选择基于url_crc列的索引来完成查询。通过触发器来实现维护哈希值方式，为了处理哈希冲突，查询的时候必须带上原URL。

B树与B+树

B树也叫B-树，全称为多路平衡查找树。B+树是B树的一种变体。MyISAM 和 InnoDB 存储引擎，都使用 B+Tree的数据结构。

B树与B+树区别：

B树的所有节点既存放键也存放数据，而B+树只有叶子节点存放键和数据，其他节点只存放键。

B树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与他相邻的叶子节点。

为什么 MySQL 默认的存储引擎 InnoDB 会使用 B+ 树来存储数据？

B 树能够在非叶节点中存储数据，但是这也导致在查询连续数据时可能会带来更多的随机 I/O，而 B+ 树的所有叶节点可以通过指针相互连接，能够减少顺序遍历时产生的额外随机 I/O。

下图是建立在B+Tree上的索引：

B+Tree类型的索引对如下类型的查询有效：

全值匹配：指的是和索引中所有列进行匹配
匹配最左前缀
匹配列前缀：可以只匹配某一列的值的开头部分
匹配范围值
精确匹配某一列并范围匹配另外一列
只访问索引的查询

注意：索引也可以用于查询中的ORDER BY操作，只要子句满足前面的几种查询类型。

B+Tree索引的一些限制：

如果不是按照索引的最左列开始查找，则无法使用索引。
不能跳过索引中的列。
如果查询某个列的范围查询，则其右边所有列都无法使用索引优化查询。

3.3 聚集索引和非聚集索引

聚集索引

聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。在InnoDB引擎的表的.ibd文件就包含了该表的索引和数据，每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

优点：

聚集索引的查询速度非常快，因为整个B+树本身就是一颗多叉平衡树，叶子节点都是有序的，定位到索引节点就相当于定位到数据。

缺点：

依赖于有序数据：如果索引数据不是有序的，就需要在插入时排序。

更新代价大：对索引列的数据修改时，对应的索引也会被修改。并且索引的叶子节点还存放着数据，修改代价很大。

二级索引访问需要两次索引查找。并且二级索引可能比想象中要更大，因为二级索引的叶子节点包含了引用行的主键列。

注：因此使用InnoDB时应该尽可能地按照主键顺序插入数据，并且尽可能地使用单调增加的聚集索引键的值来插入新行。

顺序主键什么时候会造成更坏的结果？对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用，主键的上界可能会成为热点，因为所有的插入都发生在这里，并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制。

非聚集索引

非聚集索引即索引结构和数据分开存放的索引。

MYISAM 引擎的叶子节点存储索引和索引对应数据的指针，指针指向数据。

二级索引也属于非聚集索引。

则非聚集索引的叶子节点不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

优点：

更新代价比聚集索引小。

缺点：

和聚集索引一样，也依赖于有序的数据。

可能会二次查询（回表）：当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

覆盖索引

非聚集索引一定要回表查询吗？

非聚集索引不一定要回表查询。覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

3.4 索引失效

使用!=（<>）导致索引失效；
类型不一致；
函数导致索引失效

运算符导致索引失效，如果对列进行了+、-、*、/、!，都将不会走索引

OR引起索引失效

注意：并不是所有的OR都会使索引失效，如果OR连接同一个字段，那么索引不会失效。

模糊搜索导致索引失效

%放在匹配字段前不走索引，放在后面才走索引。

NOT IN、NOT EXISTS会导致索引失效。

3.5 最左匹配原则

最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。MySQL会一直向右匹配直到遇到范围查找(<、>、between、like)就停止匹配，比如a=1 and b=2 and c > 3 and d = 4如果建立(a,b,c,d)顺序的索引，则只会用到(a,b)的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则(a,b,d)索引都可以用到。

注意=和in可以乱序，MySQL的查询优化器会优化成可以识别的模式。

4. MySQL三大日志详解

MySQL日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。比较重要的有二进制日志（bin log：归档日志）和事务日志（redo log：重做日志、undo log：回滚日志）

4.1 redo log 重做日志

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。

MySQL中数据以页为单位，查询一条数据时，会把一页数据(数据页)加载放入到Buffer Pool中，类似于缓存思想，减少硬盘IO开销。更新数据的时候，如果Buffer Pool 中存在数据，则直接在Buffer Pool中进行更新；然后会把"在某个数据上做了什么修改"记录到重做日志缓存(redo log buffer)中，最后刷盘到redo log文件里。

刷盘时机

提供了三种策略,分别对应innodb_flush_log_at_trx_commit的三种值：

0：表示每次事务提交时不进行刷盘操作。
1：表示每次事务提交都进行刷盘操作（默认值）
2：表示每次事务提交都只把redo log buffer内容写入PagaCache中。

注意：InnoDB存储引擎有一个后台线程，每隔1秒，会把redo log buffer中内容写到文件系统缓存(PageCache)，然后调用fsync刷盘。

日志文件组

硬盘上存储的redo long日志文件不止一个，而是以一个日志文件组形式出现的。采用环形数组形式，从头开始写，写到末尾又回头循环写。

为什么不每次把修改后的数据页直接刷盘不就好了，还有redo log什么事？

数据页大小是16K，每次可能就修改了几byte数据；并且数据页刷盘是随机写因为一个数据页对应的位置可能在硬盘文件的随机位置，所以性能是很差。mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Boffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。因此引入了redo log来记录已成功提交事务的修改信息，并且会把redo log持久化到磁盘。

4.2 bin log 归档日志

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。

binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。

不管用什么存储引擎，只要发生了表数据更新，都会产生binlog日志。MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

写入机制

binlog的写入机制非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog中。并且为了确保一次性写入，系统会为每个线程分配一块内存作为binlog cache。

write是指把日志文件写入到文件系统的PageCache，并没有把数据持久化到磁盘，所以速度快。

fsync，才是将数据持久化到磁盘的操作。

write和fsync的时机，可以由参数sync_binlog控制，默认是0。

0表示每次提交事务都只write，有系统判断什么时候执行fsync。机器宕机时，PageCache里面的binlog会丢失。
1表示每次提交事务都会执行fsync，与redolog刷盘流程一致。
N（N>1）每次提交事务都write，累计N个事务后才fsync。

4.3 两阶段提交

redo log（重做日志）让InnoDB存储引擎拥有了崩溃恢复能力。

binlog（归档日志）保证了MySQL集群架构的数据一致性。

在执行跟新语句时，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。 这样就会出现什么问题呢？

以update语句为例，执行SQL语句update T set c=1 where id = 2。假设执行过程中写完redo log日志后，bin log日志写期间发生了异常，会有什么情况呢？

用binlog日志恢复数据时，就会少这一次更新，恢复出来的这一行c值是0，而原库因为redo log日志恢复，这一行c值是1，最终数据不一致。

为了解决两份日志之间逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。

原理为：将redo log的写入拆分成两个步骤prepare和commit，这就是两阶段提交。

这样，写入bin log时发生异常或者redo log设置commit阶段发生异常，都不会出现数据不一致问题，流程图如下：

undo log 回滚日志

回滚日志有两个作用：

用于如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用undo log将数据回滚到以前的样子；
undo log的另一个作用是MVCC，当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读。

总结

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。

MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

6、一条SQL语句在MySQL中如何被执行的？

6.1 MySQL基础架构分析

MySQL主要分为Server层和存储引擎层：

Server层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图、函数等，还有一个通用日志模块binlog。
存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持InnoDB，MyISAM，MeMory等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

Server层基本组件介绍：

连接器

执行SQL语句时，首先需要连接MySQL服务端，连接器负责跟客户端建立连接、获取权限、维持和管理连接。使用mysql -h ip -p port -u user -p建立连接。这个过程中连接器会验证用户名和密码并查询出拥有的权限。

建立连接过程比较复杂，使用中尽量减少建立连接的动作，使用长连接。但是这样会导致内存占用太大，被系统强行杀掉（OOM），即显示MySQL异常重启。有两个解决方案：

定期断开长连接。

如果使用MySQL5.7或更新版本，可以在每次执行一个较大操作后，执行mysql_reset_connection来重新初始化连接资源。

查询缓存

建立连接后，就会执行输入的查询语句了。MySQL拿到一个查询请求后，会先到查询缓存中看看，之前是不是执行过这一条语句。执行过的语句和结果可能会以key-value对形式缓存到内存中,如果不在缓存中，就会继续后面的阶段，如果在缓存中则会直接返回结果。

查询缓存对于更新压力大的数据库来说，缓存命中率会非常低。因此在8.0版本直接删除了整个模块。低版本也可以“按需使用”，将参数query_cache_type设置为DEMAND，默认就不会使用查询缓存，需要可以显示指定。

分析器

没有命中查询缓存，MySQL需要对SQL语句做解析，知道你要做什么。分析器会先做“词法分析”，再做“语法分析”。

优化器

经过分析器后，再开始执行前，需要经过优化器处理。优化器是在表里面有多个索引的时候，决定使用哪一个索引；或者在一个语句关联多个表的时候，决定关联的顺序。该阶段完成后，执行方案就确定了。

执行器

开始执行前，会先判断你对这个表有没有执行查询的权限，如果没有会返回没有权限的错误；如果有则会根据表的引擎定义，使用指定的引擎提供的接口查询结果并返回。

6.2 语法分析

sql语句可以分为查询和更新（增加、更新、删除）

查询语句

执行过程就是按上述server层组件顺序执行。

权限校验（如果命中缓存）—>查询缓存—>分析器—>优化器—>权限校验—>执行器—>引擎

更新语句

分析器—->权限校验—->执行器—>引擎—redo log(prepare 状态)—>binlog—>redo log(commit状态)

针对sql语句update tb_student A set A.age = '19' where A.name='张三'

先查询张三这一条数据，如果有缓存，则会用到缓存。
然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
更新完成。

7、锁

什么是锁

在数据库中，lock和latch都可以被称为锁，两者区别如下：

我们讨论的锁都是lock，lock的对象是事务，用来锁定数据库中的对象（表、页、行），并且一般仅在事务commit或rollback后进行释放。

InnoDB存储引擎中的锁

InnoDB实现了两种标准的行级锁：

共享锁(S lock)，允许事务读一行数据
排它锁(X lock)，允许事务删除或更新一行数据。

它们之间的兼容性如下：

注意：S和X锁都是行锁，兼容是指对同一记录锁的兼容性情况。

意向锁

为什么需要意向锁？

mysql支持表锁，InnoDB支持行锁，如果事务A锁定了表的某一行，只能读不能写；而事务B获取了整个表的写锁，就会有冲突。为了避免这种冲突，就需要做两步判断：1.判断该表是否有其他事务上了表锁，2.一行一行判断是否有行锁。第二步的判断效率很低，因此有了表意向锁。作用是提高并发，减少行锁判断。

InnoDB存储引擎支持表级别的意向锁，有两种类型：

意向共享锁(IS lock)：事务想要获得一张表中某几行的共享锁。
意向排它锁(IX lock)：事务想要获得一张表中某几行的排它锁。

表级意向锁和表锁锁兼容性如下：

一致性非锁读

一致性的非锁读（即快照读）是指InnoDB存储引擎通过MVCC的方式读取当前执行时间数据库中行的数据，如果读取行正在执行DELETE或UPDATE操作，读取操作不会等待行上的锁释放，而是去读取行上的一个快照。具体原理见MVCC实现。

一致性锁定读

一致性锁定读（即当前读），InnoDB存储引擎对于select语句支持两种一致性锁定度操作：

select * from table where id<6 lock in share mode;--共享锁
select * from table where id<6 for update;--排他锁

这两种方式主要的不同在于LOCK IN SHARE MODE多个事务同时更新同一个表单时很容易造成死锁。

申请排他锁的前提是，没有线程对该结果集的任何行数据使用排它锁或者共享锁，否则申请会受到阻塞。在进行事务操作时，MySQL会对查询结果集的每行数据添加排它锁，其他线程对这些数据的更改或删除操作会被阻塞（只能读操作），直到该语句的事务被commit语句或rollback语句结束为止。

SELECT... FOR UPDATE 使用注意事项：

for update 仅适用于innodb，且必须在事务范围内才能生效。
根据主键进行查询，查询条件为like或者不等于，主键字段产生表锁。
根据非索引字段进行查询，会产生表锁。
即使读取的行已经被执行了select … for update，也是可以进行读取的。

行锁三种算法

InnoDB有三种行锁的算法：

Record Lock：单个行记录上的锁。
Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。
Next-Key Lock：Record Lock+Gap Lock锁定一个范围并且锁定记录本身。

在默认事务隔离级别下，（即可重复读REPEATABLE READ）InnoDB存储引擎采用Next-Key Lock机制来避免幻读问题。

锁的规则

对记录加锁时，加锁的基本单位是next-key lock，它是由记录锁和间隙锁组合而成的。但是在一些场景下next-key lock会退化成记录锁或间隙锁。

规则如下

唯一索引等值查询：

当查询的记录存在时，next-key lock会退化为记录锁。

当查询记录不存在时，next-key lock会退化为间隙锁。

非唯一索引等值查询时：

当查询记录存在时，除了加next-key lock外，还加额外间隙锁，也就是加两把锁。

当查询记录不存在时，只会加next-key lock，然后退化为间隙锁，只加一把锁。

非唯一索引和唯一索引的范围查询加锁规则不同点在于：

唯一索引在满足一些条件的时候，next-key lock 退化为间隙锁和记录锁。

非唯一索引范围查询，next-key lock 不会退化为间隙锁和记录锁。

用如下案例分析：

id是主键索引(唯一索引)，b是普通索引(非唯一索引)，a是普通列。

主键索引等值查询

当我们使用唯一索引进行等值查询的时候，查询记录存不存在，加锁规则会不同：

当查询记录存在时，用唯一索引进行等值查询时，next-key lock会退化为记录锁。
当查询记录不存在时，用唯一索引进行等值查询时，next-key lock会退化为间隙锁。

先看记录存在时：

加锁的基本单位是next-key lock，因此会话1加锁范围是(8,16]，但是由于用唯一索引进行等值查询且记录存在，所以退化为记录锁，最终加锁范围是id=16这一行。

记录不存在时：

加锁的基本单位是next-key lock，因此会话1加锁范围是(8,16]，但是由于用唯一索引进行等值查询且记录不存在，所以退化为间隙锁，最终加锁范围是(8,16)。

主键索引范围查询

范围查询和等值查询的加锁规则不同，如下两条语句，虽然结果一样，但是加锁范围不同。

最开始找到第一行id=8，因此next-key lock(4,8]，但由于id是唯一索引且记录存在，则胡退化为记录锁，只加锁id=8这一行。由于范围查找，会继续往后找存在的记录，即找到id=16这一行，加上next-key lock(8,16]，但由于id=16不满足id<9，退化为间隙锁，加锁范围为(8,16)。最终加锁是记录锁 id=8 和间隙锁(8, 16)。

非唯一索引等值查询

用非唯一索引进行等值查询的时候，查询记录存在不存在，加锁规则不同：

当查询记录存在时，除了会加next-key lock外，还额外加间隙锁，也就是会加两把锁。
当查询记录不存在时，只会加next-key lock，然后会退化为间隙锁，只加了一把锁。

查询值存在情况：

先会对普通索引b加上next-key lock，范围是(4,8]；然后因为是非唯一索引，且查询的记录是存在的，所以还会加上间隙锁，规则是向下遍历到第一个不符合条件的值才能停止，因此间隙锁范围是(8,16)。

其他省略。

阻塞

因为不同锁之间的兼容性问题，会产生阻塞，在InnoDB存储引擎中，参数innodb_lock_wait_timeout用来控制等待的时间（默认是50秒），innodb_rollback_on_timeout用来设定是否等待超时时对进行中的事务进行回滚操作（默认不回滚）。

解决死锁的机制

超时机制

即当两个事物互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就继续进行。根据FIFO的顺序选择回滚对象。

缺点：如果回滚的事务更新了很多行，占用了较多的undo log，那么在回滚的时候花费的时间比另外一个正常执行的事务花费的时间可能还要多，就不太合适。

等待图

等待图(wait-for graph)，死锁碰撞检测（通过DFS），是一种较为主动的死锁检测机制，要求数据库保存锁的信息链表和事务等待链表两部分信息，通过这两个部分信息构造出一张图，在每个事务请求锁并发生等待时都会判断是否存在回路，如果在图中检测到回路，就表明有死锁产生，这时候InnoDB存储引擎会选择回滚undo量最小的事务。

8、mysql相关语句

left join、right join、inner join

9、三范式

第一范式 1NF

确保数据库字段的原子性。即数据库表中的每一列都是不可分割的原子数据项。

比如字段 userInfo: 广东省 10086' ，依照第一范式必须拆分成 userInfo: 广东省 userTel:10086两个字段。

第二范式 2NF

首先必须满足第一范式，另外包含两部分内容，一是表必须有一个主键；二是非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。

举个例子。假定选课关系表为student_course(student_no, student_name, age, course_name, grade, credit)，主键为(student_no, course_name)。其中学分完全依赖于课程名称，姓名年龄完全依赖学号，不符合第二范式，会导致数据冗余（学生选n门课，姓名年龄有n条记录）、插入异常（插入一门新课，因为没有学号，无法保存新课记录）等问题。

可以拆分成三个表：学生：student(stuent_no, student_name, 年龄)；课程：course(course_name, credit)；选课关系：student_course_relation(student_no, course_name, grade)。

第三范式 3NF

首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

2NF和3NF的区别？

2NF依据是非主键列是否完全依赖于主键，还是依赖于主键的一部分。
3NF依据是非主键列是直接依赖于主键，还是直接依赖于非主键。

目录