2023最新MySQL常见的面试题总结

绝地灬酷狼 2023-09-27 22:11 154阅读 0赞

# 前言：本文章是对各知识点做总结，至于原理需要自行百度理解，但是在面试中，只要把最关键的点说出来就行了，说得太多面试官也没耐心听 #

# 1.Mysql中MyISAM和InnoDB引擎的区别 #

> myisam不支持事务，外键，索引结构只有非聚簇索引，锁结构只有表锁。
> 
> innodb支持事务，外键，索引结构有聚簇索引跟非聚簇索引，锁结构支持表锁跟行锁，mysql默认引擎是innodb

# 2.什么是索引 #

> 索引是一种可以提高数据库查询数据的一种数据结构，比较常见的有hash索引以及b+树，而innodb存储引擎默认使用的索引是b+树索引

# 3.索引的类型： #

> 普通索引：最基本的索引，没有任何限制，值可重复可为null，
> 
> 唯一索引：跟普通索引类似，不同点在于唯一索引的值不能重复，但是可以为null（可以创建多个唯一索引）
> 
> 主键索引：是特殊的唯一索引，值不能重复且不能为空，而且主键索引只能创建一个
> 
> 组合索引：多个字段联合创建索引，但是要遵循“最左前缀原则”

# 4.索引的优缺点： #

> 优点：加快数据的查询效率，同时还能通过唯一索引来确保数据的唯一性
> 
> 缺点：虽然提高了查询数据的速度，但是缺减低了更新数据的速度，这个是因为在更新数据的时候，还要额外的去更新索引文件

# 5.为什么不用hash索引： #

> 第一：可能存在hash冲突的问题。
> 
> 第二：如果是等值查询的话，那么hash索引的效率是比b+树要高的，因为利用hash算法一次就可以定位到数据的位置了，但如果是范围查询的，hash算法就起不了作用了，因为利用hash算法得到的值只是一个数组的下标并不是一个范围的值，所以只能全表扫描再找出符合条件的数据，而b+树可以用二分查找来进行范围查找，还有就是排序，hash索引的值大小跟索引列的值大小并不完全一样，所以就无法进行排序，而b+树在存储数据的时候就已经是按照大小来排序好的了，所以综合考虑用b+树当做innodb引擎的默认索引会好一些

# 6.为什么推荐使用自增id/什么是页分裂 #

> 因为MySQL的底层是用数据页为单位来存储数据的，一个数据页大小默认为 16k，也就是说当这一页满了，就要申请另外一页来写数据，用自增id的话，因为数据在插入b+树的时候，是按照主键值的大小来进行排序的，所以当这一页的数据写满的时候，可以直接申请另外一页来写，如果主键不是自增的话，那么在插入b+树的时候，会把数据插入到合适的位置，这就会导致部分数据可能会发生移动，而这个移动就可能会造成页分裂，页分裂是比较影响效率的，而且数据移动也影响效率

# 7.自增id的优缺点 #

> 优点：主要是自增id可以避免页分裂，而且int类型的字节比较少，这样在b+树的非叶子节点就能存储更多的主键，那么整棵b+树能存储的数据也就更多。
> 
> 缺点：无法用于分布式系统，因为用自增id的话没有办法进行表合并，如果业务太大，那么自增的范围就会超过最大值。而且自增主键有规律，容易被识破

# 8.在InnoDB中主键索引为什么比普通索引的查询性能高？ #

> 因为主键索引的话就直接在主键索引的b+树上根据主键来查询数据
> 
> 而普通索引要先在普通索引的b+树上查询到主键，然后再根据主键去主键索引的b+树上查询数据，所以主键索引查询一次，而普通索引查询两次，多出来的一次叫做回表查询

# 9.如何避免回表查询 #

> 我们使用索引覆盖可以避免回表查询，索引覆盖就是将要查询的字段，建立到联合索引里面去，例如我们把name字段创建索引，那么在select name的时候，就能查询到需要的值了，这个时候就不用回表查询，而如果是select name age的话，因为name跟age是没有建立联合索引的，这个时候就会进行一个回表查询，把age的数据给查出来

# 10.[最左前缀匹配原则][Link 1] #

> 在建立联合索引的时候会遵循最左前缀匹配原则，如果在使用联合索引字段来查询数据的时候，会从联合索引的最左边字段开始匹配数据
> 
> 这是因为联合索引的底层是b+树，所以在构建的时候是根据联合索引的最左边的索引字段来构建的，也就是说在这棵b+数叶子节点上，最左边的字段是有顺序的，而其他的字段是无序的，所以创建了联合索引后，在where条件中没有出现建立联合索引的最左边的字段，那么索引就不会生效，会进行全盘扫描

# 11.聚簇索引和非聚簇索引 #

> 聚簇索引：将数据与索引放到了一块，b+树的叶子节点保存了行数据，Innodb会通过主键来收集数据来构建聚簇索引，如果没有主键的话，那就用唯一的非空索引来代替，要是唯一的非空索引都没有，InnoDB会隐式定义一个主键来构建聚簇索引，因此在 InnoDB 引擎下，一个表里面肯定会有一个聚簇索引。
> 
> 非聚簇索引：将数据与索引分开存储，b+树的叶子节点保存了对应数据的主键值，我们在工作中自行添加的索引都是辅助索引，辅助索引就是为了寻找主键的二级索引，找到主键后再通过主键索引来寻找对应的行数据。

# 12.索引失效 #

> 1.or前后字段必须都设置索引，就会失效，  
> 2.模糊查询的百分号要放在最后面，  
> 3.如果列类型是字符串，那在查询条件中需要将数据用引号引用起来，否则不走索引  
> 4.违背最左前缀匹配原则  
> 5.使用不等于符号，或对索引列进行数学运算  
> 6.mysql估计使用全表扫描要比使用索引快,则不使用索引

# 13.MySQL优化 #

> MySQL优化我觉得主要可以从SQL语句上进行优化，虽然进行分库拆表，提高网络带宽或者提高服务器的性能，都可以对MySQL进行优化。但我们日常开发还是以SQL语句为主，所以要优先对SQL语句进行优化。  
> 当我们写的SQL语句执行的比较慢的时候，可以使用 explain 来查看SQL语句的执行过程，看下是不是走了索引，如果没走索引，再做一个针对性的优化，平时写SQL语句时候，select语句中不使用星号，要写明具体的字段，然后对于有索引的字段要避免索引失效各种情况，还要遵循最左前缀匹配原则，对于连表查询尽量不要用子查询，使用外连接时，要用小表来驱动大表，如果连表查询的效率太慢可以先单独查询各自的表，然后在Java代码中进行数据处理

# 14.事务及四大特性 #

> 事务是一个独立的工作单元，在这个独立的工作单元内的操作，要么都成功，要么都失败
> 
> 事务的四大特性（ACID）：
> 
> 原子性，隔离性，一致性，持久性
> 
> 原子性：指的是这些操作如果成功的话，就都成功，要是有一条失败的话，就都失败。
> 
> 一致性：事务执行前跟执行后的数据都应该要保持一致，例如账号A跟账号B之间的资金是500，不管他们之间相互转账了多少次，事务结束后，他们两个的账号资金加起来还是得要等于500 。
> 
> 隔离性：隔离性是指多个用户并发的去操作要一张表的时候，数据库会为每个用户开启的独立的事务，多个并发事务之间要相互隔离。
> 
> 持久性：一个事务一旦提交了，那么对数据库中的数据的改变就是永久性的。

# 15.脏读，幻读，不可重复读 #

> 脏读：当前事务读取到了别的事务想要修改成为但是没有成功修改的数据
> 
> 不可重复读：事务A读取了数据，在对这个数据进行数据处理的时候，事务B对这条数据进行了修改，当事务A再次读取这条数据的时候，会发现两次读取到的数据不匹配，这就是不可重复读
> 
> 幻读：事务A以一定的条件来搜索数据，事务A在进行数据处理的时候，事务B插入了相同查询条件的数据，当事务A再次以相同的条件来查询数据的时候，这个时候会把事务B插入的数据一起查询出来，这就是幻读

# 16.隔离级别 #

> 读为提交：事务A可以读取到事务B还没有提交的执行结果
> 
> 读已提交：事务A只能读取到事务B提交的执行结果
> 
> 可重复读：这个是mysql的默认隔离级别，这个能确保同一个事务的不同实例在并发的情况下读取到相同的数据
> 
> 串行化：最高隔离级别，这个是通过强制性的排序事务，来解决幻读的问题

# 17.MVCC的理解： #

> mvcc是一个多版本并发控制，也是一种管理并发控制的方法，主要是为了提高数据库的并发性能，在读已提交跟可重复读的隔离级别下，对于select操作会访问版本链中的数据，这就使得其他的事务可以对这条数据进行修改，修改完毕之后会保存到版本链中，就这实现了读写的并发执行，可以提高数据库的性能。
> 
> 读指的是快照读，而不是当前读，而且 mvcc不能在读未提交跟串行化的隔离级别下使用，因为读未提交每次读取都是最新的值，而串行化读写都会加锁，没有办法实现读写的并发执行

# 18.MVCC的原理： #

> 在说mvcc原理在之前，要说一下什么是版本链，版本链主要是由回滚指针跟undolog两个比较重要的部分所组成，回滚指针是行数据里面的一个隐藏字段，指向了上一个版本的历史记录，而undolog则是这条数据的所有历史记录。在版本链中有很多条记录，至于要读取哪一条数据是由readview来决定的。readview里面有四个比较重要的参数， 分别是：在生成ReadView时，当前数据库中还没commit的事务id的列表，事务列表中最小的值，生成ReadView时，数据库应该分配给下一个事务的id值，生成该ReadView的事务的事务id值。然后按照特定的规则来决定要读取的是哪一条记录。
> 
> 然后读已提交跟可重复读这两个隔离级别在mvcc下的最大的区别就是生成reidview的时机不一样，在读已提交的隔离级别下，每一次进行select操作都会生成一个新的readview，这就是为什么在读已提交的隔离级别下会产生不可重复读的原因，在可重复读隔离级别下，在同一个事务中，不管select多少次，它都只会使用第一次的select生成的readview，所以这就保证了可重复读。

# 19.锁 #

> **锁是在数据库并发访问的时候，保证数据的一致性跟完整性的机制**
> 
> 按颗粒：表锁，行锁
> 
> 行锁就是给这一行数据加锁，加锁的方式有共享锁跟排他锁：
> 
> **共享锁：**当莫个事务给某条数据加共享锁后，其他事务可以对这个数据进行读取或者加共享锁，其他事务要对这条数据修改的时候，要等该数据的共享锁全部释放，才能进行修改。
> 
> 在a事务中对某一个数据加共享锁，那这条数据可以在a事务以及其他事务中读取，在其他事务也可以对这条数据加共享锁，但是加了共享锁之后，这条数据就无法修改。要想修改就必须等所有共享锁都释放完之后才可以进行修改（当只有一个事务a的时候，在a里面先对这条数据加共享锁，这个时候在事务a里面还是可以对这条数据做修改的，做修改操作的时候会自动变成排他锁）
> 
> **排他锁：**当有事务对某条数据加排他锁后，其他事务就不能在对这条数据加任何锁，但是在执行select语句默认是不加锁的，所以其他事务还是可以对这条数据进行一个查询  
>   
> 
> 
> **表锁：**
> 
> 表级别锁有共享锁跟排他锁，用法跟行级别的差不多，只不过这个是作用在表上，表锁还有一个意向锁，意向锁包括意向共享锁跟意向排他锁，意向锁是inndb自己维护的，目的是为了快速判断表里是否有行数据被加锁，从而提高性能
> 
> 意向共享锁：事务在给一行记录加共享锁前，必须先取得该表的意向共享锁
> 
> 意向排他锁：事务在给一行记录加排他锁前，必须先取得该表的意向排他锁
> 
>   
> 
> 
> **悲观锁跟乐观锁（这段话可同样适用于java）**
> 
> 悲观锁跟乐观锁是锁的一种抽象概念，悲观锁认为数据被并发访问修改的概率比较大，所以需要在修改之前先加锁。采取的是一种“先取到锁再访问”的保守策略，而乐观锁则是认为数据在一般情况下不会被修改的，所以读取的时候不加锁，但是在更新的时候会做一个判断，判断这个数据是否被别人修改过了，如果没有修改就更新数据，如果有被修改就进行重试，这个判断通常是根据版本号来进行的，但是这样子会存在一个aba问题
> 
> aba：因为乐观锁会进行版本号判断，假设版本号原本是a，这个a值被别的线程改成了b再改回了a，那么乐观锁对这个版本号进行判断的时候是没有办法知道这条数据是否有被修改过的，这就是aba的问题
> 
>  **间隙锁：**解决的是可重复读隔离级别下的“幻读”问题，如果事务中都是快照读，那么是不会产生幻读问题的，但是快照读和当前读一起使用的时候就会产生幻读问题，间隙锁是锁住一段范围的值，这个范围是左开右开区间的

[Link 1]: https://www.cnblogs.com/ljl150/p/12934071.html