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 聚簇索引和非聚簇索引

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

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

6 什么是索引？

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据，索引的实现通常使用B+树。

7 索引有哪些优缺点？

索引的优点 ：

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

索引的缺点

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

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

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

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

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

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

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

• 复合索引：在创建索引时，并不是只能对一列进行创建索引，可以与主键一样，讲多个组合为索引(最左匹配原则)

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

9 索引的数据结构

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快。

10 B+树索引

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

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

• innoDB中，叶子节点16kb（页），假设一条数据1kb，那么一个叶子节点存16条数据；非叶子节点主键按bigint8字节，指针（地址值）6字节，161024/(8+6) =1170。B+树高度为3，1170**117016=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 更新存储引擎中的数据，最终实现主从的数据一致性。