MySQL面试题

---

1、mysql中一个select语句的执行过程？

（1）客户端首先通过连接命令mysql -u user -p与mysql服务器建立连接，如果用户名和密码正确，则服务器端的连接器会到mysql的权限表中查询当前连接拥有的权限，只要这个连接没有断开，则这个连接涉及到的权限操作都会依赖此时查询到的权限；如果用户名或者密码错误，则mysql会提示Access denied for user。

（2）建立连接后，执行select语句时，首先会查询缓存中查询是否执行过当前select语句，如果执行过相应的select语句，则执行过的select语句和查询结果会以key-value的形式存放在查询缓存中；如果查询命中缓存，则直接返回查询缓存中的数据，否则继续执行后续的查询阶段，执行完成后，会将结果缓存到查询缓存中(不建议开启查询缓存，因为查询缓存失效频繁)。

（3）缓存失效后，分析器会对select语句进行词法分析和语法分析操作，词法分析会识别出来select语句中每个字符串的含义，语法分析判断select语句是否满足语法规则；如果不满足，mysql则会提示相应的错误信息。

（4）词法和语法分析后，select语句要经过优化器进行优化处理，比如select语句如果使用了多个索引，则优化器会决定使用哪个索引来查询数据；select语句中的多表关联操作，优化器会决定各表的连接顺序。

（5）优化处理后，select语句进入执行阶段，执行器会对当前连接是否对数据表具有查询权限，如果没有查询权限，则会返回没有权限的错误；如果具有对数据表的查询权限，则会继续执行最终返回结果数据。

2、mysql存储引擎

mysql存储引擎是数据库如何存储数据、怎样建立索引以及如何查询更新数据等技术的实现方法。
每一种存储引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能。

插件式存储引擎是MySQL最重要的特性之一，MySQL默认支持多种存储引擎，以适应于不同领域的
数据库的需要。用户可以选择使用不同的存储引擎来提高应用的效率。

mysql支持的存储引擎包括：MyISAM、InnoDB、Memory、CSV、Archive，
常用的：InnoDB、MyISAM。

InnoDB和MyISAM的区别？

InnoDB是mysql默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它的存储引擎。

• InnoDB支持事务和MVCC，MyISAM没有事务支持和MVCC。
• MyISAM只支持表级锁，而InnoDB还支持行级锁。
• InnoDB支持外键操作，MyISAM不支持。
• MyISAM只支持表级锁，而InnoDB还支持行级锁。
• MyISAM崩溃后发生损坏的概率比InnoDB高，而且恢复速度也更慢。
• InnoDB是聚簇索引，聚簇索引的文件存放在主键索引的叶子结点上，因此InnoDB必须要有主键，通过主键索引效率很高；而MyISAM是非聚簇索引，索引保存的是数据文件的地址。
• MyISAM支持全文索引、压缩表和空间索引等。

MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用。

3、聚簇索引和非聚簇索引的区别

索引是对数据表中一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。但是它需要占用一定的存储空间，当基本表更新时，索引要进行相应的维护，这些都会增加数据库的负担，因此要根据实际应用的需要有选择地创建索引。

聚簇索引：
表数据按照索引的顺序来存储，即索引项的顺序与表中记录的物理顺序一致，
在聚簇索引中，叶子结点存储了真实的数据行，不再有另外单独的数据页。
一张表中最多只能创建一个聚簇索引，因为真实数据的物理顺序只能有一种。
聚簇索引是一种稀疏索引，数据页上一级索引存储的是页指针，而不是行指针。

非聚簇索引：
表数据存储与索引顺序无关。在非聚簇索引中，叶子结点不存储数据，存储的是数据行的地址。
一个表中可以拥有多个非聚簇索引。
非聚簇索引是密集索引，数据页的上一级索引页尾每一个数据行存储的一条索引记录。

4、索引的分类

• 主键索引：根据主键建立索引，不允许重复，不允许空值。
• 唯一索引：用来建立索引的列的值必须是唯一的，允许空值。
• 普通索引：mysql中的基本索引类型，允许在定义索引的列中插入重复值和空值。
• 全文索引：使用大文本对象的列构建的索引，在定义索引的列上支持值的全文查找，允许在列中插入重复值和空值。mysql中只有MyISAM存储引擎支持全文索引。
• 组合索引：在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀原则，并且组合索引这多个列中的值不允许有空值。（遵循最左前缀原则，把最常用作为检索或排序的列放在最左）

5、索引的优缺点

索引的优点：

• 创建索引可以提高系统查询性能；
• 通过索引的唯一性，可以保证数据库的每一行数据的唯一性；
• 可以加速多表之间的连接，降低查询中分组和排序的时间。

索引的缺点：

• 索引的存储需要占用磁盘空间；
• 当数据量巨大时，索引的创建和维护需要的成本也会增大；
• 每次增删改时，需要对索引进行动态维护，降低了效率。

6、数据库常见的索引模型

实现索引的方式有很多种，所以引入了索引模型的概念。

• 哈希表：哈希表是一种以键-值存储数据的结构，根据key就可以找到对应的value。多个值经过哈希函数的换算，可能会出现同一个值，处理这种情况的一种方法是，使用链表进行连接。
  优点是新增记录时速度很快，缺点是value不是有序的，做区间查询很慢。适合于等值查询的场景。

• 有序数组：哈希表不适合区间查询，而有序数组在等值查询和区间查询场景中都很优秀，利用二分法查询，时间复杂度为O(logn)，但缺点是更新数据是代价高，比如往中间插入数据时，后面的数据都要往后移动。

• 二叉搜索树：优点是平均时间复杂度为O(logn)，缺点是索引结构耗费存储空间、单独访问效率低。

• B树：m叉平衡树，所有叶子结点在同一层，优点是比二叉树低，而且结构平衡，非叶子结点上存放值，缺点时查找效率不稳定。

• B+树：B+树是基于B树和叶子结点顺序访问指针进行实现，具有B树的平衡性，并且通过顺序访问指针来提高区间查询性能，非叶子结点起到索引作用，叶子结点存放数据记录。

7、mysql的索引为什么使用B+树？

首先mysql的数据存放在磁盘当中，在访问数据时可能会导致高昂的磁盘I/O开销，所以应该尽量减少在磁盘上的I/O操作，尽管存在其他数据结构，比如哈希表和跳表可以进行高效查询，但是哈希表只能等值查询，而不能支持范围查询，而跳表虽然可以实现高效区间查询，但是随着数据量的递增，索引层也随着数据量的增多而增加，所以采用树结构，树结构的遍历方式本身就支持按区间查询，并且插入等操作没有线性结构数组那样大的开销。

树结构有很多，为什么采用B+树，是因为二叉树在极端情况下会退化成链表，查询效率下降，而平衡二叉树在数据量过多时会具有过高的高度，这样会导致磁盘做过多的I/O查询操作，B树虽然解决了上述问题，但只能高效单查询，并不能高效的区间查询，所以才有了B+树，B+树是B树的升级，其中所有非叶子结点都用来做索引，叶子结点存储数据，并且叶子结点之间通过双向链表链接，相对于B树来说，更适合于范围查询。

8、Hash 索引和 B+ 树索引区别是什么？

• 如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值，但是前提是键是唯一的，如果键不唯一，则需要先找到改键所在的位置，然后再根据链表往后扫描，直到找到相应的数据；
• 哈希表不支持范围查询，因为有序的键值经过哈希算法后可能就变成无序的了，没办法利用索引完成范围查询；同时哈希索引也没办法利用索引完成排序；
• 哈希索引不支持多列联合索引的最左匹配规则；
• B+树索引的关键字检索效率平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率是非常低的，因为存在哈希冲突问题。

通常来说，B+树适用于绝大多数场景，而下面这种场景使用哈希索引才更有优势：
如果存储数据重复度低，并且对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引。

9、什么是最左前缀原则？什么是最左匹配原则？

最左前缀原则是说，在创建多列索引时，根据业务需求，将访问最频繁的一列放在最左边。
最左匹配原则：以最左边的为起点，任何连续的索引都能匹配上，mysql会一直向右匹配直到遇到范围查询(>,<,between,like)就停止匹配。

10、索引不适用于哪些场景？

• 数据量小的表不要使用索引；
• 有大量重复数据的列上不要建立索引；
• 频繁更新的表也不适合用索引。

参考
https://www.php.cn/mysql-tutorials-132050.html
https://blog.csdn.net/wadfdhsajd/article/details/125046726
如有侵权，请联系作者删除