MySQL常见面试题

参考：程序员大斌、javauide、小林coding

1.事务是什么？

由多个操作组成的一个逻辑单元，逻辑单元的多个操作要么同时成功，要么同时失败。

2.事务的四大特性

原子性：一个事务内的操作统一成功或失败

一致性：一个事务执行之前和执行之后都必须处于一致性状态（两人转账，合计100元）

隔离性：事务与事务之间相互不影响

持久性：事务一旦提交数据的改变不可逆

3.数据库三大范式

第一范式：每个列原子性，都不可再分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不能存在传递依赖。

4.事务的隔离级别

在高并发情况下，并发事务会产生脏读、不可重复读、幻读问题，这时需要用隔离级别来控制

读未提交： 允许一个事务读取另一个事务已提交的数据，可能出现脏读、不可重复读，幻读。

读已提交： 只允许事务读取另一个事务没有提交的数据可能出现不可重复读，幻读。

可重复读： 确保同一字段多次读取结果一致，可能出现欢幻读。

可串行化： 所有事务逐次执行，没有并发问题

InnoDB 默认隔离级别为可重复读级别，分为快照度和当前读，并且通过间隙锁解决了幻读问题。

5.并发事务存在的问题

脏读：在一个事务中，可以读到另一个事务还没有提交的数据

不可重复读：在一个事务中，前后两次的查询结果不一致。比如，在两次查询间隔期间，其他的事务修改了数据。

幻读：在一个事务中，读取操作的结论不能支撑之后业务的执行。比如，先查询一条id为2的数据不存在，然后进行新增id为2的数据，却发现新增失败，这个期间可能被其他的事务新增了id为2的数据，产生了幻读现象。

6.事务靠什么保证

原子性：由undolog日志保证（见36题）

一致性：由其他三大特性共同保证，是事务的目的

隔离性：由MVCC保证，给每个事务添加版本号

持久性：由redolog日志保证（见35题）

7.MVCC是什么

MVCC是多版本并发控制，为每次事务生成一个新版本数据，每个事务都由自己的版本，从而不加锁就解决读写冲突，这种读叫做快照读。只在读已提交和可重复读中生效。

实现原理由四个东西保证，他们是

undolog日志：记录了数据历史版本

readView:事务进行快照读时生成的内存快照，记录了当前系统中活跃的事务id

隐藏字段DB_TRC_ID： 最近修改记录的事务ID

隐藏字段DB_Roll_PTR： 回滚指针，配合undolog指向数据的上一个版本

8.生产环境数据库一般用的什么隔离级别？

生产环境大多使用RC。而不是RR。

• 在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多
• 在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行!

9.索引是什么？

索引是帮助Mysql高效获取数据的一种排好序的数据结构，底层使用B+树来实现。

10.索引的优缺点？

优点：基于b+树实现，加快数据查找的速度。

缺点：建立索引占用物理空间，增删改需要动态维护索引。

11.索引越多越好吗？

不是的，索引越多，维护索引结构的代价也就越大，会影响增删改的效率

12.什么字段需要建索引？

1.主键或者外键

2.where order_by group_by on 后面的字段

3.频繁访问的字段

4.表记录多的。

5.需要排序的字段。

13.什么字段不需要建索引？

1.空值或重复值多的字段

2.经常增删改的字段。增删改后，需要维护索引。

3.参与列计算。

4.区分度不高。如性别：男/女

4.表记录少的。

14.索引什么时候会失效？

（1）where条件中有or

（2）like查询用%开头

（3）索引列参与计算

（4）违背最左匹配原则

（5）索引字段发生类型转换

（6）MySQL觉得全表扫描比走索引更快

15.索引的分类(数据结构/存储方式/应用维度)

1. 按数据结构分：B+树索引和哈希索引。

BTree 索引：MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是使用 B+Tree

哈希索引：类似键值对的形式，一次即可定位

2. 按存储方式分：聚簇索引和非聚簇索引。

聚簇索引：索引值和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。

非聚簇索引：索引值和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。

3. 按应用维度分：

主键索引：一张表只能有一个主键索引，不能有空值和重复值

唯一索引：不能有相同值，但允许为空

普通索引：仅加速查询。

组合索引：对多个字段建立一个联合索引，减少索引开销，遵循最左匹配原则

全文索引：只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引。

16.B和B+树的区别，为什么使用B+树

B树：每个节点都存储了索引值+具体数据，若要进行范围查询，要进行多次回溯，开销大

B+树：非叶子节点只存储索引值，叶子节点才存储索引值+具体数据，从小到大用双向链表连接在一起，范围查询直接遍历叶子节点不需要回溯

因为mysql中经常用到范围查询，所以使用B+树更为合适。

17.B+树索引和哈希索引的区别？

• 哈希索引不支持排序，因为哈希表是无序的。
• 哈希索引不支持范围查找。
• 哈希索引不支持模糊查询及多列索引的最左前缀匹配。
• 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

18.聚簇索引和非聚簇索引的区别

1. 聚簇索引叶子节点存储的是行数据；而非聚簇索引叶子节点存储的是主键值。
2. 聚簇索引查询效率更高；而非聚簇索引需要进行回表查询，效率低。
3. 聚簇索引一般为主键索引，所以聚簇索引一个表只有一个；而非聚簇索引则没有数量上的限制。

19.聚集索引选取规则？

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果都不存在，则InnoDB会自动生成一个row_id作为隐藏的聚集索引。

20.什么是最左前缀匹配原则？

最左前缀匹配原则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，那么这一列后面的索引都会失效。

21.回表查询是什么?回表原因？

key：主键字段、唯一索引字段、隐藏的row_id

回表查询：先到二级索引中查找数据，找到key（主键值），然后再到聚集索引中根据key（主键值），获取数据的方式.

回表的原因：select查询要的字段在二级索引的字段中不存在，需要去主键索引获取，因为主键索引含有整行记录值

22.什么是覆盖索引？

需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

23.什么是前缀索引？

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

24.什么是索引下推?

当存在索引的字段做为判断条件时，把一部分原来需要在Server端完成的条件判断转交给存储引擎去处理。

优点：减少回表查询次数，提高查询效率

缺点：只适合于非主键索引且为组合索引

25.执行计划中有哪些重要字段？

id:select查询的优先级，id越大优先级越高

type:扫描类型，效率从底到高为：ALL > index > range > ref > eq_ref > const > system

key:实际使用到的索引

rows:估算大概多少行数据被查找了

extra:MySQL如何查询额外信息，常见的有：

Using filesort:排序时使用外部排序而不是索引排序

backward index scan:按照降序排序时反向扫描索引

Using index:表示使用覆盖索引

Using temporary:查询时要建立一个临时表存放数据

26.常见的存储引擎有哪些？

1. 存储引擎是用来把数据存储在文件或内存的技术；
2. MySQL常用的索引有InnoDB、MyISAM、MEMORY；
3. InnoDB支持事务，外键，行级锁MySQL5.6版本以上才支持全文索引。
   MyISAM不支持事务，外键，行级锁，支持表锁，支持全文索引。

27.MyIsAm和InnoDB的区别

InnoDB有三大特性，分别是事务、外键、行级锁，这些都是MyIsAm不支持的，

另外InnoDB是聚簇索引，MyIAm是非聚簇索引，

InnoDB不支持全文索引，MyIAm支持

InnoDB支持自增和MVCC模式的读写，MyIAm不支持

MyIsAM的访问速度一般InnoDB快，差异在于innodb的mvcc、行锁会比较消耗性能，还可能有回表的过程（先去辅助索引中查询key（主键值），再通过key（主键值）回表到聚簇索引树查找数据）

28.MySQL有哪些锁

按锁粒度分类：行级锁、表级锁和页级锁。

1. 表级锁：对整张表加锁，粒度大并发小

2. 行级锁：对行加锁，粒度小并发大

   行级锁的类型主要有三类：

   • 记录锁(Record Lock)，也就是仅仅把一条记录锁上；
   • 间隙锁(Gap Lock)，锁定一个范围，但是不包含记录本身；
   • 临键锁(Next-Key Lock)：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

3. 间隙锁：间隙锁，锁住表的一个区间，间隙锁之间不会冲突只在可重复读下才生效，解决了幻读

---

按锁级别分类：共享锁、排他锁和意向锁。

1. 共享锁：又称读锁，一个事务为表加了读锁，其它事务只能加读锁，不能加写锁
2. 排他锁：又称写锁，一个事务加写锁之后，其他事务不能再加任何锁，避免脏读问题
3. 意向锁：设计目的为了在一个事务中揭示下一行将要被请求锁的类型。 InnoDB 自动加的，不需要用户干预。
   • 意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；
   • 意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

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

共享锁：SELECT … LOCK IN SHARE MODE;

排他锁：SELECT … FOR UPDATE;

29.快照读和当前读

快照读读取的是当前数据的可见版本，可能会是过期数据。如不加锁的select就是快照读

当前读读取的是数据的最新版本，并且当前读返回的记录都会上锁，保证其他事务不会并发修改这条记录。如update、insert、delete、select for undate（排他锁）、select lock in share mode（共享锁） 都是当前读

30.什么情况下会产生死锁?

事务1已经获取数据A的写锁，想要去获取数据B的写锁，然后事务2获取了B的写锁，想要去获取A的写锁，相互等待形成死锁。

31.MySQL解决死锁的两个机制

1. 等待， 直到超时
2. 发起死锁检测，主动回滚一条事务

死锁检测的原理：是构建一个以事务为顶点、 锁为边的有向图， 判断有向图是否存在环， 存在即有死锁。

32.MySQL 中常见的日志有哪些？

bin log（二进制日志）和 redo log（重做日志）和 undo log（回滚日志）、慢查询日志

bin log中记录的是整个mysql数据库的操作内容，对所有的引擎都适用，可以用来进行数据库的恢复及控制。

redo log中记录的是要更新的数据，比如一条数据已提交成功，并不会立即同步到磁盘，而是记录到redo log中，等待合适的时机再刷盘，为了实现事务的持久性。

undo log中记录的是当前操作的相反操作，如一条insert语句在undo log中会对应一条delete语句，在任务回滚时会用到undo log,实现事务的原子性，同时会用在MVCC中，undolog会有一条记录的多个版本，用在快照读中。

33.慢查询日志有什么用？

记录在 MySQL 中执行时间超过指定时间的查询语句。

34.binlog 和redolog有什么区别？

bin log会记录所有日志记录，包括InnoDB、MyISAM等存储引擎的日志；redo log只记录innoDB自身的事务日志。

bin log只在事务提交前写入到磁盘，一个事务只写一次；而在事务进行过程，会有redo log不断写入磁盘。

bin log是逻辑日志，记录的是SQL语句的原始逻辑；redo log是物理日志，记录的是在某个数据页上做了什么修改。

35.redolog如何保证事务的持久性？

redo log中记录的是要更新的数据，如一条数据已提交成功，并不会立即同步到磁盘，而是记录到redo log中，等待合适的时机再刷盘，为了实现事务的持久性。

36.undolog如何保证事务的原子性？

undo log中记录的是当前操作的相反操作，如一条insert语句在undo log中会对应一条delete语句，在任务回滚时会用到undo log,实现事务的原子性

37.读写分离

• 读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。 这样的话，就能够小幅提升写性能，大幅提升读性能。
• 读写分离基于主从复制，MySQL 主从复制是依赖于 binlog 。

38.分库分表

为什么要分库分表？

当单表的数据量达到千万级之后，优化索引、添加从库等可能对数据库性能提升效果不明显。

划分方式？

垂直划分：

垂直划分数据库是根据业务进行划分，例如购物场景，可以将库中涉及商品、订单、用户的表分别划分出成一个库，通过降低单库的大小来提高性能。同样的，分表的情况就是将一个大表根据业务功能拆分成一个个子表，例如商品基本信息和商品描述，商品基本信息一般会展示在商品列表，商品描述在商品详情页，可以将商品基本信息和商品描述拆分成两张表。

优点：行记录变小，数据页可以存放更多记录，在查询时减少I/O次数。

缺点：

• 主键出现冗余，需要管理冗余列；
• 会引起表连接JOIN操作，可以通过在业务服务器上进行join来减少数据库压力；
• 依然存在单表数据量过大的问题。

水平划分：

水平划分是根据一定规则，例如时间或id序列值等进行数据的拆分。比如根据年份来拆分不同的数据库。每个数据库结构一致，但是数据拆分了，从而提升性能。

优点：单库（表）的数据量减少，提高性能；切分出的表结构相同，程序改动较少。

缺点：

• 分片事务一致性难以解决
• 跨节点JOIN性能差，逻辑复杂
• 数据分片在扩容时需要迁移

39.执行一条 SQL 查询语句，期间发生了什么？

MySQL架构主要分为 Server 层和存储引擎层：Server 层负责建立连接、分析和执行 SQL；存储引擎层负责数据的存储和提取。

连接器： 当客户端连接 MySQL 时，server层会对其进行身份认证和权限校验。

查询缓存: 执行查询语句的时候，会先查询缓存，先校验这个 sql 是否执行过，如果有缓存这个 sql，就会直接返回给客户端，如果没有命中，就会执行后续的操作。

解析器: 没有命中缓存的话，SQL 语句就会经过分析器，主要分为两步，词法分析和语法分析，先看 SQL 语句要做什么，再检查 SQL 语句语法是否正确。

优化器： 优化器对查询进行优化（如对索引的选择），选择查询成本最小的执行计划。

执行器： 首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会根据执行计划去调用引擎的接口，返回结果。

40.说说count(1)、count(*)和count(字段名)的区别

执行效果上：

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
• count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
• count(字段名)只包括列名那一列，在统计结果的时候，会忽略列值为NULL的计数

执行效率上：

• 如果有主键，则count(主键)最快
• 如果表只有一个字段，则count(*)最快
• 如果表多个列并且没有主键，则 count(1)最快
• 字段名为主键，count(字段名)会比count(1)快
• 字段名不为主键，count(1)会比count(字段名)快

41.说一下常用的聚合函数？

①sum(列名) 求和

②max(列名) 最大值

③min(列名) 最小值

④avg(列名) 平均值

⑤first(列名) 第一条记录

⑥last(列名) 最后一条记录

⑦count(列名) 统计记录数不包含null值 count(*)包含null值。

42.几种关联查询

内连接(inner join)： 查询两个表匹配数据。

左外连接(left join)： 查询左表全部行以及右表匹配的行。

右外连接(right join)： 查询右表全部行以及左表匹配的行。

43.为什么要用内连接而不用外连接？

用外连接的话连接顺序是固定死的，比如left join，他必须先对左表进行全表扫描，然后一条条到右表去匹配；

而内连接的话MySQL会自己根据查询优化器去判断用哪个表做驱动表。

44.多表查询关系

一对一：外键唯一

一对多：两张表，多的表加外键

多对多：三张表，关系表加外键

45.说说数据库约束有哪些？

主键约束，保证某列的每行值唯一并且非空

唯一约束，保证某列的每行值唯一

非空约束，指示某列不能存储 NULL 值

默认约束，规定没有给列赋值时的默认值

检查约束，保证列中的值符合指定的条件

外键约束，指表中某个字段的值依赖于另一张表中某个字段的值,而被依赖的字段必须且有主键约束或者唯一约束

46.drop、truncate、delete的区别

速度： drop > truncate > delete。
回滚： delete支持，truncate和drop不支持。
删除内容：drop表结构和数据不在。truncate表结构还在，删除全部数据。 delete表结构还在，删除部分或全部数据。

47.having和where区别？

• 二者作用的对象不同，where子句作用于表和视图，having作用于组。
• where在数据分组前进行过滤，having在数据分组后进行过滤。

48.in和exists的区别

in()： 适合子表(子查询)比主表数据小的情况。
exists()： 适合子表(子查询)比主表数据大的情况。

49.SQL优化

（1）不要用select *，要使用具体字段。

（2）使用数值代替字符串，比如：0=唱，1=跳，2=rap。

（3）避免返回大量数据，采用分页最好。

（4）使用索引，提升查询速度，不宜建太多索引，不能建在重复数据比较多的字段上。

（5）批量插入比单条插入要快，因为事务只需要开启一次，数据量太小体现不了。

（6）避免子查询，优化为多表连接查询。

50.MySQL插入百万级的数据如何优化？

（1）批量插入数据，减少写redolog日志和binlog日志的次数

（2）按照索引有序插入

（3）可以分表后多线程插入

51.sql执行顺序

from、 on 、join 、where 、group by、having、select、order by、limit

52.char和varchar的区别

1、char最大长度是255字符，varchar最大长度是65535个字节。

2、char是定长的，不足的部分用隐藏空格填充，varchar是不定长的。

3、char会浪费空间，varchar会更加节省空间。

4、char查找效率会很高，varchar查找效率会更低。

53.MySQL 删除自增 id，随后重启 MySQL 服务，再插入数据，自增 id 会从几开始？

innodb 引擎：

MySQL8.0前，下次自增会取表中最大 id + 1。原理是重启之后会重新读取表中最大的id

MySQL8.0后，仍从删除数据 id 后算起。原理是它将最大id记录在redolog里了

myisam 引擎：

自增的 id 都从删除数据 id 后算起。原理是它将最大id记录到数据文件里了