MySQL面试问题合集1--基础

0 参考链接

1. 100道MySQL常见面试题总结
2. 【黑马程序员】2020最新MySQL高级教程

如有错误欢迎批评指正！

1 索引相关

1.1 什么是索引？

索引是一种数据结构，可以使数据库快速查询数据。

1.2 索引都有哪些数据结构？

索引的实现与具体数据库有关，在MySQL中常用的有B树索引、B+Tree索引与Hash索引。InnoDB中的默认索引是B+树索引。

1.3 B树与B+树的区别，InnoDB为什么用B+树做索引

区别：

1. B树中可以将索引和行放在内部节点中；B树可以在索引结构内部存放键和值，把频繁访问的数据放在靠近根节点的地方可以提高查询效率。
2. B+树的非叶子结点只包含导航信息，不包含实际的值，所有的叶子结点和相连的节点使用链表相连，便于区间查找和遍历。
   • B+树查询效率高，内部节点仅保存键
   • 查询效率稳定：每次都需要从根节点访问到叶子节点
   • B+更有利于全表扫描，B+树只需要访问叶子节点。就可以解决全部信息扫描。

1.4 Hash索引与B+Tree索引的区别?

Hash索引的底层就是Hash表，进行查找时，调用一次Hash函数就可以得到相应的键值。

1. hash索引进行等值查询更快(一般情况下)但是却无法进行范围查询.因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.

2. hash索引不支持模糊查询以及多列索引的最左前缀匹配,因为hash函数的不可预测,eg:AAAA和AAAAB的索引没有相关性.

3. hash索引任何时候都避免不了回表查询数据.

4. hash索引虽然在等值上查询叫快,但是不稳定,性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时查询效率可能极差.

5. hash索引不支持使用索引进行排序,因为hash函数的不可预测.

B+Tree的底层实现是多路平衡查找树。对于每一次查询都是从根结点出发到叶子节点才能找到所查键值。

1. B+树的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似)自然支持范围查询.

2. 在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询.不需要回表查询.

3. 查询效率比较稳定,对于查询都是从根节点到叶子节点,且树的高度较低.

结论：大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度,而不需要使用Hash索引。

1.4 聚簇索引、非聚簇索引、二级索引、索引覆盖、回表相关？

链接1，链接2

1.4.1 聚簇索引

1. 聚簇索引不单是索引结构，其还是数据存储的方式，数据存储在B+树的叶子索引上（如下图）。
   
2. 在InnoDB中，默认主键是聚簇索引，如果没有则会选择唯一的列，否则隐式创建主键列。

优点

1. 可以把相关数据保存在一起。
2. 数据访问更快。数据和索引保存在同一个 B+Tree 。
3. 使用覆盖索引扫描的查询可以直接使用页节点的主键值。

缺点

1. 提高了I/O密集型应用的性能，但是数据都在内存中优势就没有那么明显了
2. 插入速度严重依赖于主键顺序，如果不是按照自增顺序插入，速度将很慢
3. 更新聚簇的代缴很高，因为InnoDB会将行的所有记录移到新的位置

1.4.2 二级索引与覆盖查询

叶子节点中存储主键值，每次查找数据时，根据索引找到叶子节点中的主键值，根据主键值再到聚簇索引中得到完整的一行记录。如下图所示：

对于非聚簇索引：

• 如果查询语句中包含索引以外的列，则需要回表查询，即进行二次I/O。
• 如果不包含，则不需要回表查询。

1.5 索引设计原则有哪些？

• 在查询频次高、数据量大的表上建立索引
• 选择where子句中用于比较的字段
• 选取唯一索引增强区分度
• 选取短索引提高IO与CPU比较效率
• 适当索引，所以能够提升查询效率，但是会影响更新的效率，因为在更新时需要同步更新索引结构

1.6 *MySQL索引的使用有哪些注意事项？

或者说如何避免索引失效。

1. 遵循最左前缀法则（针对复合索引）
   • 从最左列索引开始，不能有跳跃，否则索引从跳跃处失效
   • 不从最左索引列开始，索引将全部失效
2. 不能再索引列上进行运算，否则失效
3. 字符串不加单引号，索引失效
4. 尽量使用覆盖索引，即索引包含查询的内容
5. OR分割条件，如果OR存在没有索引的列，则索引失效
6. 以%开头的模糊查询，索引失效。
7. 如果使用索引比全表扫描还蛮，则不使用
8. 尽量使用复合索引

1.7 *如何知道是否使用了索引？

可以在查询语句前添加explain来分析执行计划，其中可以通过三个字段可以判断索引是否命中：

• possible_key：可能用到的索引
• key：实际用到的索引
• key_len：索引长度

2 视图

2.1 什么是视图？

视图是一种预定义的虚拟的表，它不保存查询数据，仅保存查询逻辑，查询的数据在使用时动态生成。

2.2 视图的作用有哪些？

• 简化用户查询：使用户不必关心表结构、关联条件与过滤条件，只关心查询结果；
• 安全性：使用视图的用户只能看到虚拟的表，看不到具体的逻辑；
• 数据独立：数据库结构变动时，仅需要修改相应的视图逻辑，不会对用户造成影响。

3 事务相关

3.1 什么是事务？

事务就是逻辑上的一个执行流程，该流程要么全部执行，要么都不执行。

3.2 事务的ACID分别代表什么，详细解释？

• A=Atomicity：原子性，就是上面说的,要么全部成功,要么全部失败.不可能只执行一部分操作.

• C=Consistency：一致性，系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态,不会存在中间状态.

• I=Isolation：隔离性， 通常来说一个事务在完全提交之前,对其他事务是不可见的。注意前面的通常来说加了红色，意味着有例外情况。

• D=Durability：持久性，一旦事务提交,那么就永远是这样子了,哪怕系统崩溃也不会影响到这个事务的结果。

3.3 事务并发会带来哪些问题？

1. 脏读：事务A读取了另外事务B还未提交的内容，而事务B进行了回滚；
2. 不可重复读：事务A的两次读取结果不一致，事务B在事务A两次读取之间进行了更新操作
3. 幻读：事务A读取了一个范围的内容，同时事务B在此期间插入/删除了一条数据，造成了“幻觉”。

3.4 上述问题如何解决，MySQL的事务隔离级别了解吗?

• 读未提交（READ UNCOMMITTED）：其他事务能够看到未提交的修改部分，容易造成上述三种问题。
• 读已提交（READ COMMITTED）：其他事务只能读到已经提交的部分。但是无法预防不可重复读的问题。因为都的过程中，其他事务仍有机会更新记录。
• 可重复读（REPEATABLE READ）：在都的过程中不允许其他事务进行修改。但是无法解决幻影读的问题，因为其他语句仍能插入删除。InnoDB的默认级别时可重复读。
• 可串行化（SERIALIZABLE）：最高的隔离级别，事务被强制串行处理，安全程度最高，但是并发度也最低。

3.5 MySQL有哪些锁？各自的特点？

按类型分：

• 读锁：又称共享锁，读锁允许其他事务并行读，但是不允许修改
• 写锁：又称排他锁，写锁既不允许其他事务修改，也不允许其他事务读

按粒度分：

• 行级锁：锁粒度最小，开销大，加锁慢，会出现死锁，但并发程度最高。
• 页级锁：锁粒度与开销介于行级与页级之间，会出现死锁，并发程度一般
• 表级锁：锁粒度最大，开销最小，加锁最快，但并发程度最低

4 表结构设计

4.1 为什么要尽量设定一个主键？

• 提供唯一性保障
• 加速查询

4.2 使用主键自增还是UUID（随机生成）？

推荐使用主键自增：

• 在InnoDB中，主键还是作为聚簇索引存在的（主要使用B+树），按需添加会减少B+树结构调整的数据开销。

4.3 字段为什么要求定义not null

MySQL官网介绍

For MyISAM tables, NULL columns require additional space in the row to record whether their values are NULL. Each NULL column takes one bit extra, rounded up to the nearest byte.

不管字段是否为null，都会设置为占用空间，且会多一个字节，最后向上取到最接近的byte数。

4.4 如果要存储用户的密码散列,应该使用什么字段进行存储?

对于固定长度的字符串，应使用固定长度的char而非varchar，这样可以节省空间并且提高检测效率。

5 *查询优化

5.1 插入优化大批量插入数据

推荐按照主键顺序插入，因为InnoDB默认主键使用B+树作为索引，有序插入会降低B+树结构调整的成本。

5.2 order by优化

尽量使用索引排序，所有不经过索引的排序都会进行FileSort，比较耗时。

5.2.1 FIleSort优化

两次扫描

先加载排序字段和行指针，根据排序结果到表中提取查询的字段。

一次扫描

一次就把所有的结果都读取到内存中，内存开销大，但是查询效率高。

5.3 group by优化

因为group by也会进行排序操作，如果没有排序要求，可以使用order by null来禁止排序。

5.4 使用join优化嵌套查询优化

连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

5.5 超大分页优化

• 尽量在索引上进行分页，避免排序

  select * from table where age > 20 limit 1000000,10;
  -- 改成
  select * from table where id in (select id from table where age > 20 limit 1000000,10;
  

6 使用EXPLAIN分析SQL语句

使用方法：在SQL语句前添加explain。

• id：表示table加载的优先级，优先级越越先被加载，相同说明处在同一级
• select_type：表示SELECT的类型
  • SIMPLE：简单的查询，不包括子查询或者UNION
  • PRIMARY：包含子查询，最外层的显示该标识
  • SUBQUARY：子查询的标志
  • DERIVED：from中包含的zichaxun
  • UNION：第二个SELECT出现在UNION后显示的标记
  • UNION RESULT：从UNION表获取结果的SELECT
• table：分析记录所属的表是哪个
• type：访问的类型
  • NULL：MySQL不访问任何表，索引，直接返回结果
  • system： 表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
  • const：表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。
  • eq_ref：类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
  • range：只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
  • index： 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。
  • all：全表扫描
• key：
  • possible_keys : 显示可能应用在这张表的索引， 一个或多个。
  • key ： 实际使用的索引， 如果为NULL， 则没有使用索引。
  • key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。
• rows：扫描行的数量
• extra：
  • using filesort：使用了文件排序，效率低
  • using temporary：使用了临时表保存的结果
  • using index：使用索引覆盖

7 存储引擎

7.1 MySQL都支持哪些存储引擎？

MySQL支持多种数据引擎，并且可以为每张表指定不同的引擎，主要包括：InnoDB、MyISAM、MEMORY、NDB。

7.2 InnoDB与MyISAM的区别有哪些？

• InnoDB支持事务
• InnoDB支持行级锁
• InnoDB支持外键
• InnoDB支持MVVC（Multi-Version Concurrency Control，多版本并发控制）：乐观锁的思想实现无锁并发
• MyISAM支持全文索引，MYSQL5.6之后InnoDB也支持全文索引：用于大量文本数据的索引

8 其他MySQL相关

8.1 什么是存储过程、函数？有哪些不同？

存储过程：存储过程是预先编译好的，存储在数据库中的一段SQL语句集合，有如下优点

• 减少数据传输
• 预编译，执行效率高
• 减轻开发人员压力

函数：一个有返回的过程

不同：

• 使用方法不同，函数可以像聚合函数COUNT那样在SQL语句中调用，而存储过程使用CALL关键字
• 存储过程可以有多个返回，函数有且只有一个返回

9 数据库原理相关

9.1 数据库的范式有哪些？

• 第一范式：每个列不可以再分
• 第二范式：非主键列完全依赖于主键，不能存在只依赖部分主键的列
• 第三范式：非主键列值不依赖于其他非主属性列（消除非主属性传递依赖）
• BCNF
  • 非主属性完全依赖于每一个主码
  • 所有主属性都完全依赖于不包含他的码
  • 没有任何属性完全函数依赖于非码的任何一组属性