MySQL常见面试题

1. innodb 的一些特点

innodb 是 MySQL 5.5版本之后的默认存储引擎，特点：更新插入删除（DML）操作遵循ACID模型，支持事务。  锁的最小粒度是行级锁。  支持外键约束，保证数据完整性和正确性。

2. 你还知道一些其他引擎吗

MyISAM是MySQL早期的默认存储引擎。 特点：不支持事务，不支持外键约束。  锁的最小粒度是表级锁，不能并发操作同一个锁。  访问速度快。

Memory 将表数据存在内存中，存取速度快。但是如果断电，不能持久化。只能当作临时表或缓存使用

3. MySQL 的索引有哪几种

按字段个数分：单列索引、联合索引

按字段类型分：主键索引、普通索引、唯一索引、前缀索引

按物理存储分类：聚簇索引、二级索引

4. MySQL 的事务有了解吗

事务四大特性ACID：

原子性：事务操作只有成功或失败

一致性：事务操作前后数据总数保持不变

持久性：事务操作后对数据的修改是永久的

隔离性：多个事务不会互相影响

多个事务可能互相影响导致出现：脏读（读到另一个事务还没提交的）、不可重复读（事务开启后，两次读取同一个记录变化了）、幻读问题（事务开启后，两次读取记录数量变化了）

为了解决上面三种问题，Innodb出来了隔离级别：读未提交、读已提交（解决了脏读）、可重复读（解决了不可重复读）、串行化（解决了幻读）

Innodb默认隔离级别是可重复读。并且针对不同的执行语句采取不同的策略极大避免了幻读问题：针对普通select语句，采用MVCC+undolog实现。针对更新删除插入语句，使用加锁的方式。

5. 你可以给我介绍一下这几个日志（undolog，redolog）和 MVCC 吗？

undolog 是

MVCC多版本并发控制，在可重复读隔离级别下，开启事务时，会生成一个ReadView，ReadView有四个字段，创建该快照的事务id、最小活跃事务id、活跃事务id列表、还有下一次ReadView应分配的事务id。在事务开启后就用这个ReadView。当查询某条记录时，记录后面有两个隐藏列，一个是上次操作这个记录的事务id，还有一个是指向之前旧纪录的指针。通过比较当前事务id大小和ReadView中最小活跃id大小来判断这个记录是否可见。

当记录操作事务id < ReadView最小的活跃事务id时，说明这条记录是在事务创建前就生成了，可见

当记录操作事务id >= ReadView最大的活跃事务id时，说明这个事务是在ReadView创建后生成的，不可见

当记录操作事务id > ReadView最小的活跃事务id < ReadView最大的活跃事务id时，会再去判断记录操作事务id有没有在活跃id列表中存在，如果存在，说明还没有提交，不可见，如果不存在，说明提交过了可见。

6. MySQL 中有哪几种锁?

全局锁：加锁只读。用于备份数据

表级锁：

表锁，加锁锁全表。其他线程对表操作会阻塞，只能等释放。

元数据锁，对表结构更新时上的锁

意向锁，对某条记录上锁时会先对表上一个意向锁。当其他线程访问到时，看到有意向锁就知道是否可以操作了。不用再进入表中找到记录再判断

行级锁：

记录锁，锁住一条记录，分为s型和x型记录。ss共享，sx xx互斥

间隙锁，锁住的是一个范围，左开右开。只在可重复读隔离级别。多个相同的间隙锁可以存在

临建锁，锁定一个范围，并且也锁定右边界的记录

插入意向锁，事务插入一条数据，会判断当前位置是否有间隙锁，如果有就上一个插入意向锁。并且阻塞在这里，等待间隙锁释放。

7. 索引是越多越好么？

索引不是越多越好

如果对很多字段都建立索引，每个索引都是一个b+树，占用磁盘空间。

索引提升了查询的速度，但是在删除更新修改时，每次都要维护索引的数据有序性，每个b+树都要求页内容是按照值大小排序的，并且每个数据页可存放的数据大小是固定的。可能会造成因为插入一条数据，导致发生记录从一个数据页挪到另一个数据页中。这样不停不停的移动，非常的耗费时间。

8. 怎么防止sql注入的

防止SQL注入，尽量不采用原生SQL语句，不在项目中使用占位符将SQL拼接。提高数据库访问权限。使用封装好的操作数据库方法。

9. mysql的联合索引，范围查询，模糊查询一定失效吗?

不一定失效，需要看表中的字段，如果表中只有两个字段并且都有索引，那么不管是联合索引没遵循最左匹配或者是走了模糊查询。也不会失效，最差也是走了一遍二级索引就找到数据了。

10. 什么是聚簇索引？什么是非聚簇索引？

聚簇索引是主键索引，除主键之外的键加了索引叫非聚簇索引

11. 一般选择什么样的字段来建立索引？

很少更新的字段，经常用来查询的字段

经常用于Group by orderby 的字段，因为B+树中存放的就是有序的数据。

区分度高的字段

12. 索引的目的是什么？

索引像一个书的目录，要找东西在目录中找会更快，所以索引可以提高查询的速度，可以更快的找到记录。

13. 什么情况会影响，降低索引的查询效率？

频繁插入更新删除索引列字段数据。 可能导致索引物理结构发生变化，产生一些空洞或者不连续的区域。会影响索引的存储效率和查询效率。

14. 建立了索引，查询的时候一定会用到索引吗？

不一定，索引失效的情况有很多：

左右模糊匹配会失效

联合索引没有按照最左匹配原则

查询条件中对索引列使用了函数、计算、隐式转换

where条件前一个用了索引后一个没有用索引

15. 什么情况下使用联合索引？

如果两个字段需要共同使用作为查询的条件，可以加联合索引。这样能形成索引覆盖，提高where的查询效率

16. B树有哪些缺点呢？

B树在非叶子节点也存放的记录的全部数据，数据页的大小是有限的，在同样数据量下，B树的数据页会多于B+树，在查询时，B树可能要进行更多次的查询操作。

B树在叶子节点上没有双向链表，在进行范围查询时还需要重新遍历树。不适用于范围查询情况。

B树在非叶子节点里没有存放冗余数据，当需要删除某个记录数据时，还需要改变结构去维护树型结构。相较于B+树，B+树在非叶子节点存放有冗余数据，当删除时，不需要改变树形结构，只需要遍历到目标数据删除记录。

17. 主键索引和唯一索引的区别

主键索引：加在主键上的索引，主键索引一定是唯一索引

唯一索引：唯一索引

主键列不允许空值、唯一列允许空值。

一张表只允许有一个主键，但是可以有多个唯一性索引

18. mysql两种存储引擎的区别和应用场景

mysql存储引起有innodb和myISAM。

innodb支持事务，myISAM不支持事务

innodb锁的最小单位是行级锁，myISAM的锁的最小单位是表级

innodb支持外键约束，myISAM不支持外键约束。

myISAM适用不需要事务支持的场景，一般读数据比较多的场景，并发访问相对较低的业务。

innodb适用需要事务支持的场景，对数据读写更新都比较频繁的场景、对数据一致性要求很高的业务

19. 什么是事务，特性？

事务，用户定义的一系列执行语句，这些语句要么同时成功要么全部失败。

特性：ACID 原子性、一致性、持久性、隔离性

20. B+树插入分裂的操作是怎么样的

m阶B+树每个节点最多有m-1个记录。当第m个记录插入进来后，达到了分裂的条件，此时会推举一个中间值作为父节点，它的左子树节点都小于父节点，它的右子树节点都大于等于父节点。

21. 组合索引为什么要最左匹配

组合索引中构建的B+树中是优先按照联合索引左边条件进行查找的，查到后再按照右边的条件查找。如果不遵循最左匹配原则，无法使用组合索引，走全表扫描

22. 红黑树特点，和二叉查找树区别

红黑树：所有节点不是红色就是黑色。根节点是黑色的。叶子节点都是nil黑色节点。红色节点的父子节点不能为红色。每个节点到叶子节点间的每个路径黑色节点个数都相等。

二叉查找树确保左子节点小于父节点，右子节点大于父节点。在非常情况下会退化成一个链表。查询效率大大降低

自平衡二叉树：左右子树高度差不会超过1。不会出现退化成链表的情况

红黑树：左右子树满足红黑树五个条件时就可以高度差大于1。二叉树只要不平衡就会旋转，红黑树不会，某些情况下只用改变颜色就能达到平衡。

23. 为什么不建议使用select *

select *相当于将全部字段查询出来，查询要从磁盘读取数据，大量数据会增大磁盘IO开销。

用不了覆盖索引了，还要进行回表操作

24. mysql索引优化有了解吗 怎么优化查询

前缀索引：对于字段数据很大的字段，可以采取使用前缀索引。只对前面一些字符加索引，这样进行查询的时候会加快查询速度

覆盖索引：不需要查询一条记录的全部信息，减少了回表操作

主键最好设置成自增：B+树是按照主键顺序存放的，如果主键值是随机的，会出现主键值插入到中间页的情况，可能会产生页数据分裂，影响查询效率。  主键长度不要太长。

索引最好设置成not null 。 b+树会将null值看成最小值，放在链表最左边用链表连起来。在查询时链表查询没有树块。

25. MySQL进行一次查询时一定会访问磁盘吗？对MySQL文件的组织有没有了解？

不会，当一个查询语句到来时，首先会先和数据库建立连接，连接建立后首先会去看缓存中是否有之前查询的记录，如果有直接就返回了，不会再去访问磁盘了。如果缓存中没有记录，下来就会进行词法分析、语法分析，构建语法树。在这阶段会对语句正确性进行判断。然后到用预处理阶段，这个阶段会检查字段表存不存在，函数处理，select *展开都在这里处理。然后会选择一个执行计划，然后交给执行器，执行器按照计划去执行。最后从磁盘获取到数据并返回。

MySQL文件组织

26. Mysql数据放在什么地方？分引擎讨论

innodb数据放在磁盘中

Memory存储引擎数据放在内存中

MyISAM存储引擎数据存放在磁盘上

27. 死锁是什么，如何避免？

互相访问加锁的资源，又不会主动释放。一直在等待对方释放锁。 产生死锁。

在MySQL中，当两个事务都对某个范围的记录加了间隙锁。然后都想在范围内插入一条记录，在插入记录时，会加一个插入意向锁，但是插入意向锁不能和间隙锁同时存在，于是会阻塞。知道另一个事务释放了锁，插入意向锁才能加上。于是出现了死锁。

死锁满足四个条件：互斥、不可强占用、循环等待、占有等待

如果避免，破坏其中一个条件即可。 设置事务等待锁的超时时间，开启死锁主动检测，发现死锁后，主动回滚死锁链条中某一个事务，让其他事务执行

28. Mysql索引下推？

当where后有两个条件时，第一个条件列有索引，第二个条件列没有索引，正常情况下如果满足了第一个条件，就会回表然后在主键索引中找到记录，然后再判断后面数据是否符合。后来mysql引入了索引下推技术，当第一个条件满足后，不会立即进行回表操作，而是再比较后面的条件列看是否符合要求，如果不符合就不会进行回表操作。这样减少了回表次数。

29. 为什么索引用 B+树？而不用B树或者二叉树？

二叉树，每个节点只能由左右子点，树的层级会非常高。查询效率低。

B树，每个节点可能有多个子节点，每个节点都存放数据。

B+树，每个节点可能有多个字节点，在非叶子节点，B+树不存数据，只在叶子节点存放全部数据。并且B+树有很多冗余节点，在插入删除时效率不会引起树形结构变化。影响操作速度。并且B+树对于范围查询也非常有优势，叶子节点通过双向链表相连接，在范围查询时可以更快找到记录。

30. 说一下什么是幻读？

事务过程中，两次读到记录条数不同

31. 单表查询数据量比较高，如何优化查询效率？

走索引查询，针对列的字段特点建立合适的索引

32. 创建索引的原则？

遵循最左匹配原则

经常用于where后的字段，或者groupby orderby的字段

选择区分度高的列作为索引列

索引列不能参与计算

尽量扩展索引，不要新建索引

33. 事务的ACID是怎么实现的

原子性：通过undolog实现的，当事务对数据库记录修改时，会生成一个undolog，如果最终rollback了，也会通过之前undolog日志回到事务执行前的样子

一致性：通过原子性、隔离性、持久性三个共同实现了一致性

隔离性：通过MVCC+锁实现

持久性：通过redo log实现，当数据修改时，会先写到buffer pool一份，还会在redolog中记录操作，当事务提交时，会进行刷盘操作。如果MySQL宕机，重启后还可以读取redolog数据来恢复。

34. innodb自哈希索引？

innodb支持自适应哈希索引，innodb会根据表的使用情况自动为表生成哈希索引，不能人为干预。

B+树在查找数据时，会通过根节点和左右节点大小关系来一级一级找到最终的记录。中间寻迹的过程比较耗费时间。

自适应哈希索引 主要是为了加速索引寻路的。减少了索引寻找记录页的时间。如果innodb发现，很多SQL存在这类很长的寻路，innodb会在自己的内存缓冲区里，开辟一块区域，建立自适应哈希索引，来加速查询。 key是索引键值，value是数据页位置

如果大量SQL都是范围查询或者模糊查询，维护自适应哈希索引反而会增加系统负担，降低系统效率