Mysql面试题

笨手笨脚の

于 2023-07-03 11:46:20 发布

阅读量118

点赞数

分类专栏：面试文章标签： mysql 数据库秋招面试

本文链接：https://blog.csdn.net/qq_56517253/article/details/131512801

版权

面试专栏收录该内容

4 篇文章 1 订阅

订阅专栏

Mysql面试题

SQL语句在执行时：解析器->优化器->执行器调用存储引擎接口

1.索引的基本原理

索引可以快速的查找那些具有特定值的记录。如果没有索引，那么查询的时候要遍历整张表。

索引的原理就是把无序的数据变为有序的。

链接：https://www.bilibili.com/video/BV1Wm4y147mS

2.Mysql聚簇和非聚簇索引的区别

都是B+树的数据结构

聚簇索引：将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的
非聚簇索引：叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置再去磁盘查找数据，这个就有点类似一本书的目录，比如我们要找第三章第一节，那我们先在这个目录里面找，找到对应的页码后再去对应的页码看文章。

优点：

聚簇索引查询时能直接查到数据行信息，非聚簇索引需要二次查询
聚簇索引对于查询的效率很高，因为其数据是按照某种关系排列的
聚簇索引更适合需要排序的场合

缺点：

维护索引比较复杂，比如插入新行恰好导致需要分页
表有的时候会使用UUID作为主键，散列度较高，有可能导致聚簇索引查询比遍历更慢
如果主键比较大，那么辅助索引等也会占用更多的空间

3.Mysql索引的数据结构以及其优劣

索引的数据结构通常和具体的存储引擎的实现有关，Mysql中通常使用的索引的数据结构为B+树或Hash索引。InnoDB的默认存储引擎的索引方式为：B+树索引。对于Hash索引来说，底层其实是Hash表，因此在绝大多数情况为单条记录查询的情况下性能较优，其余大部分场景都不适合。

B+树： B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。因此，B+树索引被广泛应用于数据库、文件系统等场景。

哈希索引：哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

优劣分析：

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；前提是键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据。
如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；哈希索引也不支持多列联合索引的最左匹配规则；
B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在哈希碰撞问题。

4.索引设计的原则

查询更快、占用空间更小

适合索引的列是出现在where子句中的列，或者连接子句中指定的列
基数较小的表，索引效果较差，没有必要在此列建立索引
使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
定义有外键的数据列一定要建立索引。
更新频繁字段不适合创建索引
若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
对于定义为text、image和bit的数据类型的列不要建立索引。

5.什么是最左前缀原则与最左匹配原则

最左前缀原则指当⼀个SQL想要利⽤索引时，就⼀定要提供该索引所对应的字段中最左边的字段，也就是排在最前⾯的字段，⽐如针对a,b,c三个字段建⽴了⼀个联合索引，那么在写⼀个sql时就⼀定要提供a字段的条件，这样才能⽤到联合索引，这是由于在建⽴a,b,c三个字段的联合索引时，底层的B+树是按照a,b,c三个字段从左往右去⽐较⼤⼩进⾏排序的，所以如果想要利⽤B+树进⾏快速查找也得符合这个规则。

最左匹配原则是指在想要利用联合索引查询时，一定要加上联合索引最左字段对应的查询条件，不然是无法使用此索引的。

6.Mysql中锁的类型

7.InnoDB存储引擎的锁的算法

8.事务的基本特性和隔离级别

基本特性即A（原子性）、C（一致性）、I（隔离性）、D（持久性）

AID保证了C的实现。

9.ACID靠什么保证的

A：原子性，指一个事务中的所有操作要么同时成功，要么同时失败。主要靠undo log日志来保证，每个事务会在磁盘中生成一个undo log日志，一般情况下对一行数据的一次修改就是一条日志，当事务中的某个操作失败后，根据undo log日志中的内容逆向执行操作。

I：隔离性，指不同事务之间不能相互干扰，不能查看彼此尚未提交的数据。隔离性主要分为写-写隔离和读-写隔离，分别靠锁和MVCC保证。在多个事务执行时，通常有以下三大明显问题：

A事务执行过程中，读取到了B事务修改但未提交的数据，称为脏读。
A事务执行过程中要对某数据进行多次读取，A在一次读取数据后，B事务修改了该数据并进行提交，A事务再次读取时发现和之前的数据不一致，称为不可重复读。
A事务执行过程中对某集合进行多次查询，一次查询后B事务对此集合里的数据进行了增加或删除操作并进行提交，A再次读取时发现数据数量与之前不一致，称为幻读。

D：持久性，指事务一旦进行提交，就不会因为电源故障、系统崩溃等意外情况而发生变化。意外情况一般分为以下两种：

内存缓冲区中的数据在写入磁盘前已经被损毁，InnoDB采用redo log机制来解决这个问题。
Mysql一般以16KB大小的页为最小传输单位，但操作系统通常以4KB大小进行数据传输，发生故障时有可能数据页没有完整传输，一般通过建立双写缓冲区，采用同步恢复机制来解决。

10.什么是MVCC

MVCC即多版本并发控制：读取数据时通过⼀种类似快照的⽅式将数据保存下来，这样读锁就和写锁不冲突了，不同的事务session会看到⾃⼰特定版本的数据，有版本链的概念。

已提交读隔离级别下的事务在每次查询的开始都会⽣成⼀个独⽴的ReadView，⽽可重复读隔离级别则在第⼀次读的时候⽣成⼀个ReadView，之后的读都复⽤之前的ReadView。

这就是Mysql的MVCC，通过版本链，实现多版本，可并发读-写，写-读。通过ReadView⽣成策略的不同实现不同的隔离级别。

11.简述MyISAM和InnoDB的区别

MyISAM：

不⽀持事务，但是每次查询都是原⼦的；
⽀持表级锁，即每次操作是对整个表加锁；存储表的总⾏数；
⼀个MYISAM表有三个⽂件：索引⽂件、表结构⽂件、数据⽂件；采⽤⾮聚簇索引，索引⽂件的数据域存储指向数据⽂件的指针。辅索引与主索引基本⼀致，但是辅索引不⽤保证唯⼀性。

InnoDb：

⽀持ACID的事务，⽀持事务的四种隔离级别；
⽀持⾏级锁及外键约束：因此可以⽀持写并发；
不存储总⾏数；⼀个InnoDb引擎存储在⼀个⽂件空间（共享表空间，表⼤⼩不受操作系统控制，⼀个表可能分布在多个⽂件⾥），也有可能为多个（设置为独⽴表空，表⼤⼩受操作系统⽂件⼤⼩限制，⼀般为 2G），受操作系统⽂件⼤⼩的限制；
主键索引采⽤聚集索引（索引的数据域存储数据⽂件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使⽤⾃增主键，防⽌插入数据时，为维持B+树结构，⽂件的⼤调整。