MySql常用面试题

1 MySql, Oracle ， Sql Service 的区别

Sql Service 只能在 Windows 上使用，而 MySql 和 Oracle 可以在其他系统上使用， 而且可以支持数

据库不同系统之间的移植

MySql 开源免费的， Sql Service 和 Oracle 要钱。

Oracle 支持大并发量，大访问量， Sql Service 还行，而 MySql 的话压力没这么大，因此现在的

MySql 的话最好是要使用集群或者缓存来搭配使用。（ mysql 单击并发量 500 左右）

2 数据库三大范式是什么

第一范式：每个列都不可以再拆分。

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

须有主键）

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。（表中的字段不

要有冗余）

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我

们经常会为了性能而妥协数据库的设计。

数据库设计软件： powerdesigner pd

3 数据库经常使用的函数

count(*/column) ：返回行数

sum(column) ： 返回指定列中唯一值的和

max(column) ：返回指定列或表达式中的数值最大值

min(column) ：返回指定列或表达式中的数值最小值

avg(column) ：返回指定列或表达式中的数值平均值

4 MyISAM 与 InnoDB 区别

Innodb 引擎 ： Innodb 引擎提供了对数据库 ACID 事务的支持。并且还提供了行级锁和外键的约束。

MyIASM 引擎 ( 原本 Mysql 的默认引擎 ) ：不提供事务的支持，也不支持行级锁和外键。

InnoDB 索引是聚簇索引， MyISAM 索引是非聚簇索引。

InnoDB 的主键索引的叶子节点存储着数据，因此主键索引非常高效。

MyISAM 索引的叶子节点存储的是数据的地址，需要再寻址一次才能得到数据。

InnoDB 非主键索引的叶子节点存储的是主键和其他带索引的数据，因此查询时做到覆盖索引会非

常高效。

5 聚簇索引和非聚簇索引

聚簇索引： 将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：将数据存储和索引分开结构，索引结构的叶子节点指向了数据的对应行。 6 什么是索引？

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新

数据库表中数据，索引的实现通常使用 B+ 树。

7 索引有哪些优缺点？

索引的优点 ：

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

索引的缺点

时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时

候，索引也要动态的维护，会降低增 / 改 / 删的执行效率；

空间方面：索引需要占物理空间。

8 有哪些索引，他们的作用是？

唯一索引：不允许有俩行具有相同的值

主键索引：为了保持数据库表与表之间的关系（唯一且不为空）

聚集索引：数据存储与索引放到了一块

非聚集索引：将数据存储和索引分开结构，索引结构的叶子节点指向了数据的对应行。

复合索引：在创建索引时，并不是只能对一列进行创建索引，可以与主键一样，讲多个组合为索引

( 最左匹配原则 )

全文索引： 全文索引为在字符串数据中进行复杂的词搜索提供有效支持

9 索引的数据结构

索引的数据结构和具体存储引擎的实现有关，在 MySQL 中使用较多的索引有 Hash 索引 ， B+ 树索引 等，

而我们经常使用的 InnoDB 存储引擎的默认索引实现为： B+ 树索引。对于哈希索引来说，底层的数据结

构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快。

10 B+ 树索引

叶子节点存数据，非叶子节点存主键 + 地址

叶子节点之间通过双向链表连接，这样方便范围查询（ id in between 1 and 3 ）

innoDB 中，叶子节点 16kb （页），假设一条数据 1kb ，那么一个叶子节点存 16 条数据；非叶子节

点主键按 bigint8 字节，指针（地址值） 6 字节， 16 1024/(8+6) =1170 。 B+ 树高度为 3 ，

1170**1170 16=2100 万，查询的时候只需要 3 次就能从 2100 万的数据里找到我们想要的数据。

不管有多少数据，只需要查 3 次，范围查询的数据也很快

hash 索引是不支持范围查询的。

11 索引使用原则

在离散度大的列上（重复值表较少的列）使用索引。在 B+Tree 里面的重复值太多， MySQL 的优

化器发现走索引跟使用全表扫描差不了多少的时候，就算建了索引，也不一定会走索引。

在用于 where 判断 order 排序和 join 的（ on ）字段上创建索引。（适合在条件字段上索引）

索引的个数不要过多。 —— 浪费空间，更新变慢。

频繁更新的值，不要作为主键或者索引

联合索引把散列性高（区分度高）的值放在前 12 联合索引是什么？为什么需要注意联合索引中的顺序？

MySQL 可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，

需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。（最左匹配原则）

13 索引失效的情况

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描

2. 尽量避免使用 in 和 not in ，会导致引擎走全表扫描

3. 尽量避免使用 or ，会导致数据库引擎放弃索引进行全表扫描

4. 尽量避免进行 null 值的判断，会导致数据库引擎放弃索引进行全表扫描

5. 尽量避免在 where 条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表

扫描 where age+1=5

6. 当数据量大时，避免使用 where 1=1 的条件。通常为了方便拼装查询条件，我们会默认使用该条

件，数据库引擎会放弃索引进行全表扫描

7. 查询条件不能用 < > 或者 != union

8. where 条件仅包含复合索引非前置列

9. 隐式类型转换造成不使用索引 where age = '1'

14 怎么解决慢查询问题

1 开启慢查询日志，并且设置慢查询时间，我们当时设置的时间是 1 秒。

2 在服务器上打开慢查询日志文件（ mysql-slow.log ）， sql 语句执行时间超过 1 秒的都会在慢日志里记

录

3 定位到 sql 语句以后，要分析 sql 语句问题，有木有加索引，没有加索引可以考虑加索引。加了索引那么

就需要查看 sql 是否走了索引

4 explain + sql 语句，查看 type 指标，类型至少是 range 以上级别，如果低于 range ，也就是 index 、

ALL 级别，就需要考虑 sql 语句索引失效的问题。

15 undo log

1 事务回滚日志 ：

是 Innodb 存储引擎层生成的日志，实现了事务中的 原子性 ，主要 用于事务回滚和 MVCC 。

我们在执行执行一条 “ 增删改 ” 语句的时候 ， MySQL 会隐式开启事务来执行 “ 增删改 ” 语句的，执行完就

自动提交事务的，这样就保证了执行完 “ 增删改 ” 语句后，我们可以及时在数据库表看到 “ 增删改 ” 的结果

了。

执行一条语句是否自动提交事务，是由 autocommit 参数决定的，默认是开启。所以，执行一条

update 语句也是会使用事务的。

undo log 是一种用于撤销回退的日志。在事务没提交之前， MySQL 会先记录更新前的数据到 undo log

日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚。 2 MVCC （多版本并发控制）

undo log 还有一个作用，通过 ReadView + undo log 实现 MVCC （多版本并发控制） 。

16 Buffer Pool

缓冲池 ： 提高数据库的读写性能 ，原理跟 redis 差不多

当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否

则再去磁盘中读取。

当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后

将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘 I/O ，不会立即

将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

17 redo log

Buffer Pool 是提高了读写效率没错，但是问题来了， Buffer Pool 是基于内存的，而内存总是不可靠，

万一断电重启，还没来得及落盘的脏页数据就会丢失。 为了防止断电导致数据丢失的问题，当有一条记

录需要更新的时候， InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完

成了。 在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要将缓存在 Buffer Pool 里的脏页数据

持久化到磁盘。当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重

启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

redo log 记录的是事务完成以后数据的操作，可以解决 Buffer Pool 因为缓存数据丢失的问题。

18 undo log 和 redo log

redo log 记录了此次事务 「完成后」 的数据状态，记录的是更新之 「后」 的值；

undo log 记录了此次事务 「开始前」 的数据状态，记录的是更新之 「前」 的值；

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通

过 redo log 恢复事务。

19 binlog

undo log 和 redo log 这两个日志都是 Innodb 存储引擎生成的。

MySQL 在完成一条更新操作后， Server 层还会生成一条 binlog ，等之后事务提交的时候，会将该事物

执行过程中产生的所有 数据操作 统一写 入 binlog 文件。

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作 。

所有存储引擎都可以使用 20 主从复制是怎么实现？

MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。

复制的过程就是将 binlog 中的数据从主库传输到从库上。

MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog ，再提交事务，更新存储引擎中

的数据，事务提交完成后，返回给客户端 “ 操作成功 ” 的响应。

从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把

binlog 信息写入 relay log 的中继日志里，再返回给主库 “ 复制成功 ” 的响应。

从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎

中的数据，最终实现主从的数据一致性。