MySQL面试题汇总

1. 存储引擎选择

1. MyISAM：

• 适用于管理非事务表，不支持外键，只支持表锁，它提供高速存储和检索， 以及全文搜索能力的场景。比如博客系统、新闻门户网站。
• 是非聚簇索引

1. InnoDB：

• 适用于新增，更新，删除操作频繁，或者要保证数据的完整性，并发量高，支持事务和外键的场景，且支持表锁行锁。比如OA自动化办公系统。

2.索引

2.1 索引的介绍

索引是一种数据结构，是数据库管理系统中一个排序的数据结构，以协助快速查询数据库表中数据。索引方法的实现通常使用B+树或hash表。
更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。

2.2 索引有哪些优缺点？

1. 索引的优点：

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 通过使用索引，可以在查询的过程中，使用优化器，提高系统的性能。

1. 索引的缺点：

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/删/改的执行效率；
• 空间方面：索引需要占物理空间。

2.3 索引有哪些类型？

1. 主键索引： 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
2. 唯一索引： 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

• 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
• 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

1. 普通索引： 基本的索引类型，没有唯一性的限制，允许为NULL值，一个表允许多个列创建普通索引。

• 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引，对应Navicat中的索引类型是NORMAL
• 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引，对应Navicat中的索引类型是NORMAL

1. 全文索引： 是目前搜索引擎使用的一种关键技术，MyISAM存储引擎才有全文索引。

• 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

2.4 索引的数据结构（B+树，Hash）

2.4.1 B+Tree

1. B+Tree的优点:

• B+Tree的数据页只存储在叶子节点中，并且叶子节点之间通过指针相连，为双向链表结构。

• 充分利用空间局部性原理，适合磁盘存储。
• 树的高度很低，能够在存储大量数据情况下，进行较少的磁盘IO
• 能够很好支持单值，范围查询，有序性查询。
• 索引和数据分开存储，让更多的索引存储在内存中。
• B+树的叶子节点之间存在一个指针连接,那么顺着叶子节点从左往右即可完成对数据的遍历，极大了简化了排序操作。不仅仅能方便查找，而且有助于排序，在mysql的索引中叶子节点之间数双向链表可正反遍历，更加灵活.
• mysql采用B+树的优点：IO读取次数少（每次都是页读取），范围查找更快捷（相邻页之间有指针）

1. 需要注意点：

• InnoDB是通过B+Tree结构对主键创建索引，然后叶子节点中存储记录，如果没有主键，那么会选择唯一键，如果没有唯一键，那么会生成一个6字节的row_id来作为主键
• 如果创建索引的键是其他字段，那么在叶子节点中存储的是该记录的主键，然后再通过主键索引找到对应的这条信息记录，这叫回表

• 总上述所说

  • ==回表==：如：前述[id主键索引，name普通索引]select * from table_name where name = ?,他会先根据name字段的索引tree中找到对应的id值，再根据id值去主键字段所有tree中找到这条信息的记录，这就是回表，如果数据量少，反而会降低查询效率，因为回表造成了多次的IO的读取；
  • ==索引覆盖==：如：前述[id主键索引，name普通索引]select id from table_name where name = ?,他会去name字段的索引B+Tree中找到对应的id值，之后直接返回给用户；推荐使用，在某些场景中，可以考虑将要查询的所有列都变成组合索引，此时也会使用索引覆盖

- ==索引下推==：在MySQL5.6版本以下则没有采取索引下推，5.6开始采取索引下推，索引下推指的是当`where name=? and age = ?`的时候都是在磁盘中操作的，没采用索引下推则是先根据`name`字段去查询，查询之后在MySQL的服务端再次筛选`age`字段，最终返回数据给用户；`总结`：索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。
- ==最左匹配==：如上面的组合索引中，有两个name,age组合一起的索引，会采取最左匹配原则，说白了就是按照name,age的顺序去从左往右看

2.4.2 Hash

1. 缺点：

• 需要做一个比较好的Hash算法，如果算法不好的话，会导致hash碰撞，hash冲突，导致数据散列不均匀，如下图，数据都在1，4了

• 做范围查找的时候需要挨个遍历，效率非常低

2.5 索引算法有 BTree算法和Hash算法

1. BTree算法

BTree是最常用的mysql数据库索引算法，也是mysql默认的算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量

• 例如：

-- 只要它的查询条件是一个不以通配符开头的常量
select * from user where name like 'jack%';
-- 如果一通配符开头，或者没有使用常量，则不会使用索引，例如：
select * from user where name like '%jack';

1. Hash算法

Hash算法只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到叶子节点这样多次IO访问，所以检索效率远高于BTree索引。

2.6 创建索引的原则？索引设计的原则？

• 为常作为查询条件的字段建立索引，where子句中的列，或者连接子句中指定的列
• 为经常需要排序、分组操作的字段建立索引
• 更新频繁字段不适合创建索引
• 不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
• 对于定义为text、image和bit的数据类型的列不要建立索引
• 最左前缀原则，就是最左边的优先。指的是联合索引中，优先走最左边列的索引。
• 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值
• 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长

2.7 什么情况使用了索引，查询还是慢

1. 索引全表扫描
2. 索引过滤性不好
3. 频繁回表的开销
4. like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
5. or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
6. 组合索引，不是使用第一列索引，索引失效。
7. 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
8. 在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引
9. 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
10. 对索引字段进行计算操作、字段上使用函数。
11. 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。
12. sql语句中select id from table where age + 1 = 5 此时age为普通索引，这样会走索引，如果select * from table where age + 1 = 5 ，查询所有，此时不会走索引
13. or关联

• 如果是单列索引or会使用索引，如果是组合索引

• 如果是组合索引：

  • 全部列都是索引，那么会使用全部列所对应的索引
  • 如果部分列是组合索引，那么不会走索引

1. 遇到单列字段是索引的额话，可以用范围条件<,<=,>,>=,between,但是范围列后面的列无法使用索引，导致索引失效
2. 当表连接的时候，两张表同一个字段类型不一样的话，会导致索引失效

2.7 MySQL使用自增主键的好处

1. 自增主键按顺序存放，增删数据速度快，对于检索非常有利；
2. 数字型，占用空间小，易排序；

2.8 聚簇索引和非聚簇索引（主键索引）

聚簇索引：将数据与索引放到了一块，索引结构的叶子节点存储了行数据，找到索引也就找到了数据

非聚簇索引：将数据与索引分开存储，索引结构的叶子节点存储的是行数据的地址

1. 聚簇索引的优点

• 数据访问更快。聚族索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据通常比非聚族索引中查找更快。
• 使用覆盖索引扫描的查询可以直接使用节点中的主键值。

1. 聚簇索引的缺点

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列作为主键。
• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
• 通过辅助索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。从而引起了回表

1. 几个概念

• 对于普通索引，如 name 字段，则需要根据 name 字段的索引树（非聚簇索引）找到叶子节点对应的主键，然后再通过主键去主键索引树查询一遍，才可以得到要找的记录，这就叫回表查询。先定位主键值，再定位行记录，它的性能较扫描一遍索引树的效率更低
• InnoDB的行锁是建立在索引的基础之上的，行锁锁的是索引，不是数据，所以提高并发写的能力要在查询字段添加索引
• B+树：不管是什么索引，在mysql中的数据结构都是B+树的结构，可以充分利用数据块，来减少IO查询的次数，提升查询的效率。一个数据块data里面，存储了很多个相邻key的value值，所有的非叶子节点都不存储数据，都是指针。

3. 常用SQL查询语句优化方法

1. 不要使用select * from t，用具体的字段列表代替“*”，使用星号会降低查询效率，如果数据库字段改变，可能出现不可预知隐患。
2. 应尽量避免在where子句中使用!=或<>操作符，避免在where子句中字段进行null值判断，存储引擎将放弃使用索引而进行全表扫描。
3. 避免使用左模糊，左模糊查询将导致全表扫描。
4. IN语句查询时包含的值不应过多，否则将导致全表扫描。
5. 为经常作为查询条件的字段，经常需要排序、分组操作的字段建立索引。
6. 在使用联合索引字段作为条件时，应遵循最左前缀原则。
7. OR前后两个条件都要有索引，整个SQL才会使用索引，只要有一个条件没索引整个SQL就不会使用索引。
8. 尽量用union all代替union，union需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。

4. MySQL的优化细节

1. 加索引
2. 看执行计划
3. 优化sql语句

5. 事务的四大特性(ACID)介绍一下

5.1 什么是脏读？不可重复读？幻读？

1. 脏读(Dirty Read)

• 一个事务读取到另外一个事务未提交的数据。
• 举例：一个事务1读取了被另一个事务2修改但还未提交的数据。由于某种异常事务2回滚，则事务1读取的是无效数据。

1. 不可重复读(Non-repeatable read)

• 一个事务读取同一条记录2次，得到的结果不一致。这可能是两次查询过程中间，另一个事务更新了这条记录。

1. 幻读(Phantom Read)

• 幻读发生在两个完全相同的查询，得到的结果不一致。这可能是两次查询过程中间，另一个事务增加或者减少了行记录。

1. 不可重复读和幻读区别

不可重复读的重点是 修改，幻读的重点在于新增或者删除。

5.2 事务的隔离级别？MySQL的默认的隔离级别是什么？

√表示会导致的问题

1. 事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。
2. MVCC多版本并发控制

• 有三个隐藏字段

• undolog的回滚日志，会产生多个历史版本状态

• MVCC的实现，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同时刻不同事务看到的相同表里的数据可能是不同的。

  • 比如，在MySQL的可重复读的隔离级别情况下：事务A和事务B，同事开启事务，并且两个事务都第一次查询数据（数据快照），当事务B更改数据且提交，此时事务A再次（第二次）select的数据，它查询的数据并非是事务B提交后的新数据，依然是第一次的数据结果
  • 事务A和事务B，同事开启事务，事务B先去更改数据且提交，事务A第一次select查询（数据快照），则可以查看到事务B提交后的新数据

1. MVCC多版本控制原理

• 同一个事务中，多次查询的结果还是原来数据 底层采用MVCC多版本控制机制实现，读取原来快照数据。
• 此时允许幻读，但不允许重复读与脏读。

1. 可重复读每次都是用的第一次快照吗？

• 不是；
• 事务A和事务B同时开启事务，事务AB分别查询数据，事务B开始新增数据且提交，此时事务A也去更改数据，（update from table set a = 1）,会更新事务A第一次快照数据条数+事务B上一次新增数据的条数，然后事务A第二次做查询的时候就会查询最新的数据了

1. MVCC的基础认识的当前读，快照读

1. 最终MVCC解决的是读写冲突，一个事务在写，另一个在读，读的是快照

6. 锁

6.1. 对MySQL的锁了解吗

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

6.2. 隔离级别与锁的关系

1. 读未提交（Read Uncommitted）：读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突
2. 读已提交（Read Committed）：读操作需要加共享锁，在语句执行完以后释放共享锁；
3. 可重复读（Repeatable Read）：读操作需要加共享锁，事务执行完毕后才释放共享锁。
4. 串行化（SERIALIZABLE）：是限制性最强的隔离级别，该级别下锁定整个范围的键，并一直持有锁，直到事务完成。

6.3. 按照锁的粒度分数据库锁有哪些

在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

1. MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁(table-level locking)。
• InnoDB支持行级锁(row-level locking)和表级锁，默认采用行级锁(行锁加载索引上，如果没有索引则是表锁)

1. 行级锁，表级锁和页级锁对比

• 行级锁：行级锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁(读锁) 和 排他锁（写锁）。

==特点==：锁定粒度最小，对当前操作的行记录加锁，发生锁冲突的概率最低，并发度也最高；加锁开销大，加锁慢；会出现死锁；

• 表级锁：表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

==特点==：锁定粒度大，对当前操作的整张表加锁，发出锁冲突的概率最高，并发度最低；加锁开销小，加锁快；不会出现死锁；

• 页级锁（间隙锁）：页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

==特点==：销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

6.4. 共享锁和排他锁的区别

1. 共享锁（读锁）

• 使用方式：在需要执行的语句后面加上 for update就可以了
• 读取为什么要加读锁呢：防止数据在被读取的时候被别的线程加上写锁
• 总结：共享锁 share lock 又称读锁 read lock，简称S锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
• 示例：如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获得共享锁的事务只能读数据，不能修改数据

1. 排他锁（写锁）

• 排他锁 exclusive lock 又称写锁 writer lock，简称X锁。排他锁是悲观锁的一种实现。
• 若事务T对数据A加上排他锁，则只允许事务T读取和修改数据A，其他任何事务都不能再对A加任何类型的锁，直到事务T释放X锁。排他锁会阻塞所有的排他锁和共享锁

6.5. 数据库的乐观锁和悲观锁是什么？怎么实现的？

1. 乐观锁：

假设不会发生并发冲突，每次去查询数据的时候都认为别人不会修改，所以不会上锁，在修改数据的时候才把事务锁起来。实现方式：乐观锁一般会使用版本号机制或CAS算法实现

1. 悲观锁：MySQL都是悲观锁

假定会发生并发冲突，每次去查询数据的时候都认为别人会修改，每次查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

1. 两种锁的使用场景：

• 乐观锁：适用于写比较少的情况下（多读场景）
• 悲观锁：一般多写的场景下用悲观锁就比较合适

6.6. 避免死锁现象的产生

1. MySQL会智能发现死锁，若发现死锁就会将两个事务所执行的语句全部回滚
2. 业务中进来采取小的事务，避免使用大事务
3. 同一个事务中尽量做到一次锁定所需要的所有资源

7. SQL优化

7.1. SQL执行流程，SQL的生命周期

1. 连接器
2. 查询缓存
3. 分析器
4. 优化器
5. 执行器

8. 日志分析

8.1. 重要的日志模块：binlog，和InnoDB的redo log

1. binlog有几种录入格式

• statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
• row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。
• mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

1. 这两种日志有以下三点不同。

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

9. MySQL的主从复制原理以及流程

9.1. 主从复制

1. 概念

将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行，从而使得从数据库的数据与主数据库保持一致。

1. 主从复制的作用

• 高可用和故障切换：主数据库出现问题，可以切换到从数据库。
• 负载均衡：可以进行数据库层面的读写分离。
• 数据备份：可以在从数据库上进行日常备份。