MySQL面试题总结

一、概念基础

1.1 概念

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

1.2 基本数据类型

数值类型： 包括：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，这几个为 整数类型，其中TINYINT占1字节、SMALLINT占2个字节、MEDIUMINT占3个字节、INT占4个字节、BIGINT占8个字节。这几个都可以加UNSIGNED（无符号整数）属性，都可以指定数据长度。

小数类型： FLOAT、DOUBLE、DECIMAL。

字符串类型： VARCHAR、CHAR、TEXT、BLOB。

日期类型： DATETIME、DATE 、 TIMESTAMP。

1.3 innerjoin、leftjoin、rightjoin三者之间的区别

innerjoin(等值连接) 只返回两个表中联结字段相等的行
leftjoin(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
rightjoin(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

1.4 SQL语句中where与having的区别

where是一个约束声明，使用where来约束来之数据库的数据，where是在结果返回之前起作用的。
having是一个过滤声明，having是对分组之后的结果筛选。

1.5 char与varchar的区别

数据长度：CHAR 是定长的，而 VARCHAR 是变长。
存储方式：CHAR 对英文字符（ASCII）占用 1 字节，对一个汉字使用用 2 字节。而 VARCHAR 对每个字符均使用 2 字节。

1.6 数据库三大范式

第一范式（1NF）： 字段（或属性）是不可分割的最小单元，即不会有重复的列，体现原子性。

第二范式（2NF）： 满足 1NF 前提下，存在一个候选码，非主属性全部依赖该候选码，即存在主键，体现唯一性，专业术语则是消除部分函数依赖。

第三范式（3NF）： 满足 2NF 前提下，非主属性必须互不依赖，消除传递依赖。

二、MySQL事务

2.1 数据库事务是什么？

事务是作为一个逻辑单元执行的一系列操作，一个逻辑工作单元必须有四个属性，称为ACID（原子性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务：

原子性事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

一致性事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如B树索引或双向链表）都必须是正确的。

隔离性由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。

持久性事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

2.2 事务的四大特性（ACID）

原子性（Atomicity）： 事务是一个完整的操作，事务的每个操作是不可分的，要么都执行，要么都不执行。
一致性（Consistency）： 当事务完成时，数据必须处于一致状态。
隔离性（Isolation）： 并发事务之间彼此隔离、独立，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。
持久性（Durability）： 事务完成后，对数据库的修改永久保持。

2.3 事务并发问题

脏读、幻读和不可重复读。

2.4 什么是脏读、幻读和不可重复读？

脏读：一个事务读取到另一个事务尚未提交的数据。 对于事务A和事务B，事务A读取事务B修改后的数据，如果事务B未提交，然后事务B回滚，那么事务A读取的就是脏数据。

不可重复读：一个事务中两次读取的数据的内容不一致。 事务A多次读取同一条数据，如果事务B在事务A读取过程中，对数据修改并提交，那么事务A多次读取的数据不一致。

幻读：一个事务中两次读取的数据量不一致。 事务A读取表数据量是，如果事务B在事务A读取过程中插入了几条数据，那么事务A就会出现两次读取数据量不一致。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。

2.5 事务的隔离级别

隔离级别	脏读	不可重复读	幻读
ReadUncommitted（读取未提交）	是	是	是
ReadCommitted（读已提交）	否	是	是
RepeatableRead（可重复读）	否	否	是
Serializable（可串行化）	否	否	否

mysql默认的事务隔离级别为RepeatableRead（可重复读），但它解决了脏读、不可重复读、幻读。

三、MySQL索引

3.1 索引数据结构

MySQL主要有两种结构：hash索引和B+Tree索引，InnoDB引擎默认是B+Tree索引。

3.2 索引分类

聚簇索引： 指索引的键值的逻辑顺序与表中相应行的物理顺序一致，即每张表只能有一个聚簇索引，也就是我们常说的主键索引。

非聚簇索引： 的逻辑顺序则与数据行的物理顺序不一致。

普通索引： MySQL 中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了提高查询效率。通过 ALTER TABLE table_name ADD INDEX index_name (column) 创建。

唯一索引： 索引列中的值必须是唯一的，但是允许为空值。通过 ALTER TABLE table_name ADD UNIQUE index_name (column) 创建。

主键索引： 特殊的唯一索引，也成聚簇索引，不允许有空值，并由数据库帮我们自动创建。

组合索引： 组合表中多个字段创建的索引，遵守最左前缀匹配规则。

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

3.3 为什么建议InnoDB必须建主键？

对于InnoDB来说，如果不手动建主键索引，MySQL底层依然会帮我们创建一个聚集索引来维护整张表的所有数据，因为B+Tree必须依靠索引才能建立。为什么建议InnoDB必须建主键呢？因为本身数据库的资源就非常宝贵，我们尽量能手动做的就不要麻烦MySQL去帮我们维护，说白了就是降低数据库开销。

3.4 为什么推荐使用整型主键？

InnoDB引擎B+Tree索引数据结构，右边叶子节点大于父节点、左边叶子节点小于父节点，如果使用UUID字符串比较大小，效率很低，因为字符串需要遍历比较，显然整型更具有优势。

3.5 Hash索引与B+Tree索引的区别

Hash索引：
1）Hash 进行等值查询更快，但无法进行范围查询。因为经过 Hash 函数建立索引之后，索引的顺序与原顺序无法保持一致，故不能支持范围查询。同理，也不支持使用索引进行排序。

2）Hash 不支持模糊查询以及多列索引的最左前缀匹配,因为 Hash 函数的值不可预测，如 AA 和 AB 的算出的值没有相关性。

3）Hash 任何时候都避免不了回表查询数据.

4）虽然在等值上查询效率高，但性能不稳定，因为当某个键值存在大量重复时，产生 Hash 碰撞，此时查询效率反而可能降低。

B+Tree索引：
1）B+ 树本质是一棵查找树，自然支持范围查询和排序。

2）在符合某些条件（聚簇索引、覆盖索引等）时候可以只通过索引完成查询，不需要回表。

3）查询效率比较稳定，因为每次查询都是从根节点到叶子节点，且为树的高度。

3.6 为什么使用B+Tree？

B+Tree将数据的存储都放在了叶子节点，非叶子节点全部用来存放冗余索引，这样可以保证非叶子节点可以存储更多的索引，因为决定B+Tree高度的就是非叶子节点，如果非叶子节点可以存储更多的值就会使树的整体高度变少，从而降低磁盘IO次数，降低系统消耗。

3.7 什么是最左匹配原则？

顾名思义，最左优先，以最左边为起点任何连续的索引都能匹配上。同时遇到范围查询（>、<、between、like）就会停止匹配。

3.8 索引下推

索引下推（Index condition pushdown） 简称 ICP，在 Mysql 5.6 版本上推出的一项用于优化查询的技术。

在不使用索引下推的情况下，在使用非主键索引进行查询时，存储引擎通过索引检索到数据，然后返回给 MySQL 服务器，服务器判断数据是否符合条件。

而有了索引下推之后，如果存在某些被索引列的判断条件时，MySQL 服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL 服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少 MySQL 服务器从存储引擎接收数据的次数。

四、索引优化和SQL优化

4.1 like语句左边的’%'不会使用索引

select * from abc where title like '%XX';   #不能使用索引
select * from abc where title like 'XX%';   #非前导模糊查询，可以使用索引

4.2 范围条件右边不会使用索引

范围条件有：<、<=、>、>=、between等。

索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

select * from abc where a>0;             #可以使用索引
select * from abc where a>0 and b>0;     #不会使用索引

4.3 负向条件不会使用索引

负向条件有：!=、<>、not in、not exists、not like 等。

select * from abc where a!=0 and a!=1;             #不会使用索引
select * from abc where a in(0,1,2,3);             #优化查询可以使用索引

4.4 在索引列任何操作（函数，计算、表达式）会导致索引失效

select * from abc where year(create_time) <= '2022'   #索引失效
select * from abc where create_time <= '2022-01-01'   #可以使用索引

select * from abc where a/10=2   #索引失效
select * from abc where a=10*2   #可以使用索引

4.5 强制类型转换会导致索引失效

字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换,相当于在索引列上进行了操作。

#前提a字段是varchar类型
select * from abc where a=8393881     #索引失效
select * from abc where a='8393881'   #可以使用索引

4.6 组合索引要匹配最左前缀原则

组合索引的字段数不允许超过5个。如果在a,b,c三个字段上建立联合索引 index(a,b,c)，那么他会自动建立 a、(a,b)、(a,b,c) 三组索引。

4.7 减少select*的使用

MySQL数据库是按照行的方式存储，而数据存取操作都是以一个页大小进行IO操作的，每个IO单元中存储了多行，每行都是存储了该行的所有字段。所以无论取一个字段还是多个字段，实际上数据库在表中需要访问的数据量其实是一样的。

4.8 优化Group by，使用where子句替换Having子句

避免使用having子句，having只会在检索出所有记录之后才会对结果集进行过滤，这个处理需要排序分组，如果能通过where子句提前过滤查询的目，就可以减少这方面的开销。

on、where、having这三个都可以加条件的子句，on是最先执行，where次之，having最后。

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。

select a,b,c from abc group by a having a=0      #效率低
select a,b,c from abc where a=0 group by a       #效率高

4.9 使用union all 替换 union

当SQL语句需要union两个查询结果集合时，这两个结果集合会以union all的方式被合并，然后再输出最终结果前进行排序。如果用union all替代union，这样排序就不是不要了，效率就会因此得到提高.。需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录。

4.10 优化深度分页的场景：利用延迟关联或者子查询

对于 limit m, n 的分页查询，越往后面翻页（即m越大的情况下）SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行Join关联查询。因为MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。

# 延迟关联：通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据

# 覆盖索引：select的数据列只用从索引中就能够得到，不用回表查询

select a.* from 表1 a,(select id from 表1 where 条件 limit 100000,20) b where a.id=b.id;

五、锁

5.1 锁分类

锁粒度： 表锁、页锁、行锁。

锁性质： 共享（读）锁、排他（写）锁、意向共享（读）锁、意向排他（写）锁。

锁思想： 悲观锁、乐观锁。

5.2 表锁

表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表定，所以可以很好的避免困扰我们的死锁问题。

使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

5.3 行锁

与表锁正相反，行锁最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力从而提高系统的整体性能。

虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

5.4 页锁

除了表锁、行锁外，MySQL还有一种相对偏中性的页级锁，页锁是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

使用页级锁定的主要是BerkeleyDB存储引擎。

5.5 共享（读）锁（Share Lock）

共享锁，又叫读锁，是读取操作（SELECT）时创建的锁。其他用户可以并发读取数据，但在读锁未释放前，也就是查询事务结束前，任何事务都不能对数据进行修改（获取数据上的写锁），直到已释放所有读锁。

如果事务A对数据B（1024房）加上读锁后，则其他事务只能对数据B上加读锁，不能加写锁。获得读锁的事务只能读数据，不能修改数据。

SELECT … LOCK IN SHARE MODE;

在查询语句后面增加LOCK IN SHARE MODE，MySQL就会对查询结果中的每行都加读锁，当没有其他线程对查询结果集中的任何一行使用写锁时，可以成功申请读锁，否则会被阻塞。其他线程也可以读取使用了读锁的表，而且这些线程读取的是同一个版本的数据。

5.6 排他（写）锁（Exclusive Lock）

排他锁又称写锁、独占锁，如果事务A对数据B加上写锁后，则其他事务不能再对数据B加任何类型的锁。获得写锁的事务既能读数据，又能修改数据。

SELECT … FOR UPDATE;

在查询语句后面增加FOR UPDATE，MySQL 就会对查询结果中的每行都加写锁，当没有其他线程对查询结果集中的任何一行使用写锁时，可以成功申请写锁，否则会被阻塞。另外成功申请写锁后，也要先等待该事务前的读锁释放才能操作。

5.7 意向锁（Intention Lock）

意向锁属于表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB 中的两个表锁：

意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；

意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是 InnoDB 自动加的，不需要用户干预。

再强调一下，对于INSERT、UPDATE和DELETE，InnoDB 会自动给涉及的数据加排他锁；对于一般的SELECT语句，InnoDB 不会加任何锁，事务可以通过以下语句显式加共享锁或排他锁。

5.8 MVCC

MVCC 的英文全称是 Multiversion Concurrency Control，中文意思是多版本并发控制，可以做到读写互相不阻塞，主要用于解决不可重复读和幻读问题时提高并发效率。

其原理是通过数据行的多个版本管理来实现数据库的并发控制，简单来说就是保存数据的历史版本。可以通过比较版本号决定数据是否显示出来。读取数据的时候不需要加锁可以保证事务的隔离效果。

5.9 隔离级别和锁关系

1）在 Read Uncommitted 级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突；

2）在 Read Committed 级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

3）在 Repeatable Read 级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁；

4）在 SERIALIZABLE 级别下，限制性最强，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

六、日志

6.1 日志分类

MySQL主要包括以下7种日志

重做日志（redo log）
回滚日志（undo log）
归档日志（binlog）
错误日志（errorlog）
慢查询日志（slow query log）
一般查询日志（general log）
中继日志（relay log）

6.2 重做日志（redo log）

如果每次更新操作都需要写磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了WAL 技术来提升更新效率。

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘

具体来说，当有一条update语句要执行的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。InnoDB 的 redo log 是固定大小的。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe

crash-safe 就是落盘处理，将数据存储到了磁盘上，断电重启也不会丢失。

6.3 归档日志（binlog）

MySQL 其实是分为 server层 和 引擎层两部分，Server 层：它主要做的是 MySQL 功能层面的事情，引擎层：负责存储相关的具体事宜。

redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为binlog（归档日志），其实就是用来恢复数据用的。

6.4 重做日志（redo log）与归档日志（binlog）的区别

1）redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎共用。

2）redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=1 这一行的 c 字段加 1 ”。

3）redo log 是循环写的，空间固定会用完然后复写；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

6.5 两阶段提交

由于存储引擎层与server层之间采用的是内部XA(保证两个事务的一致性,这里主要保证redo log和binlog的原子性)，所以提交分为prepare阶段与commit阶段，也就是我们说的两阶段提交。

下面这条sql语句分析两阶段提交

update `user` set integral = integral+10 where user_id = 542;

1）执行器先从内存中找user_id = 542这条数据，如果有直接返回，否则从磁盘读取到内存，再返回。

2）数据修改，执行器拿到数据，把integral字段+10。

3）写redo log，引擎将数据写入内存中，同时将此更新操作写入redo log，此时redo log处于prepare阶段。

4）写biglog，然后告知执行器执行完成了，随时可以提交事务。执行器生成这个操作的 binlog，并把 binlog 同步到磁盘。

5）执行器调用引擎的提交事务接口执行修改操作，需要将在二级索引上做的修改，写入到change buffer page，等到下次有其他sql需要读取该二级索引时，再去与二级索引做merge，引擎把刚刚写入的 redo log 标记上（commit）状态，实际上是加上了一个与binlog对应的XID，使两个日志逻辑保持一致，到此结束，更新流程闭环。

文章持续更新中…

本文参照以下博客

索引优化SQL优化
MySQL锁机制
redo log和binlog