Mysql面试题

CV工程师呀

已于 2022-07-08 10:27:45 修改

阅读量384

点赞数

分类专栏：题目篇文章标签： mysql 数据库 java

于 2022-07-06 21:55:53 首次发布

本文链接：https://blog.csdn.net/qq_44954571/article/details/125648380

版权

题目篇专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1.MyISAM和InnoDB的区别是什么?

InnoDB ⽀持事务，MyISAM 不⽀持事务。
InnoDB ⽀持外键，MyISAM 不⽀持。
InnoDB 是聚集索引，MyISAM 是⾮聚集索引。
InnoDB 不保存表的具体⾏数，MyISAM ⽤⼀个变量保存了整个表的⾏数。
InnoDB 最⼩的锁粒度是⾏锁，MyISAM 最⼩的锁粒度是表锁。

2.数据库表设计时，字段你会如何选择？

字段类型优先级，在满足使用情况下使用占内存小的字段类型。
可以选整型就不选字符串。（整型是定⻓的，没有国家/地区之间的字符集差异）
尽量避免使⽤NULL。（NULL不利于索引，也不利于查询。对数据库操作只有使⽤is null或者is not null才可以。）
char处理速度要⽐varchar快很多，但是相对较费存储空间；反之使用varchar

3.事务的基本特性

原⼦性指的是⼀个事务中的操作要么全部成功，要么全部失败。
⼀致性指的是数据库总是从⼀个⼀致性的状态转换到另外⼀个⼀致性的状态。
隔离性指的是⼀个事务的修改在最终提交前，对其他事务是不可⻅的。
持久性指的是⼀旦事务提交，所做的修改就会永久保存到数据库中。

4.事务并发可能引发什么问题？

脏读
在事务A执⾏过程中，事务A对数据资源进⾏了修改，事务B读取了事务A修改后的数据。由于某些原因，事务A并没有完成提交，发⽣了RollBack操作，则事务B读取的数据就是脏数据。

不可重复读
事务B读取了两次数据资源，在这两次读取的过程中事务A修改了数据，导致事务B在这两次读取出来的数据不⼀致。（这可能是两次查询过程中间进行了数据更新。）

幻读
事务B前后两次读取同⼀个范围的数据，在事务B两次读取的过程中事务A新增了数据，导致事务B后⼀次读取到前⼀次查询没有看到的⾏。

幻读强调的是集合的增减，不可重复读强调的是单条数据的更新

5.简单描述下MySQL各种索引及对数据库的性能的影响？

主键索引
建⽴在主键上的索引被称为主键索引，⼀张数据表只能有⼀个主键索引，索引列值不允许有空值。

唯⼀索引
建⽴在UNIQUE字段上的索引被称为唯⼀索引，⼀张表可以有多个唯⼀索引，索引列值允许为空，列值中出现多个空值不会发⽣重复冲突。

普通索引
建⽴在普通字段上的索引被称为普通索引。

前缀索引
前缀索引是指对字符类型字段的前⼏个字符或对⼆进制类型字段的前⼏个bytes建⽴的索引，⽽不是在整个字段上建索引。前缀索引能提升索引的查询效率。但mysql⽆法使⽤前缀索引做 order by 和 group by

索引可以极大的提高数据的查询速度。但是会降低插入、删除、更新表的速度。索引还需要占物理空间，维持索引状态也需要一定的开销。

6.什么是三星索引？

⼀个查询相关的索引⾏是相邻的或者⾄少相距⾜够靠近的则获得⼀星；
如果索引中的数据顺序和查找中的排列顺序⼀致则获得⼆星；
如果索引中的列包含了查询中需要的全部列则获得三星。

7.如何提⾼insert的性能？

1、合并多条 insert 为⼀条，减少磁盘IO

2、调⼤批量插⼊的缓存。（修改参数 bulk_insert_buffer_size）

3、⼿动使⽤事务。（因为mysql默认是autocommit的，这样每插⼊⼀条数据，都会进⾏⼀次commit；手动使用事务可以减少提交次数以提高效率）

8.什么是全局锁、共享锁、排它锁？

全局锁就是对整个数据库实例加锁，它的典型使⽤场景就是做全库逻辑备份。这个命令可以使整个库处于只读状态。

共享锁⼜称读锁，是读取操作创建的锁。其他⽤户可以并发读取数据，但任何事务都不能对数据进⾏修改。

排他锁 ⼜称写锁。若某个事物对某⼀⾏加上了排他锁，只能这个事务对其进⾏读写，在此事务结束之前，其他事务不能对其进⾏加任何锁，其他进程可以读取,不能进⾏写操作，需等待其释放。

9.索引的基本原理

索引的原理：就是把无序的数据变成有序的查询

把创建了索引的列的内容进行排序
对排序结果生成倒排表
在倒排表内容上拼上数据地址链
在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

10.mysql聚簇和非聚簇索引的区别

都是B+树的数据结构

聚簇索引：将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的

非聚簇索引：叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置再取磁盘查找数据

优势：
1、查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高
2、聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的
3、聚簇索引适合用在排序的场合，非聚簇索引不适合
劣势：
1、维护索引很昂贵，移动的行数据可能造成碎片
2、不建议使用UUId（随机ID）作为主键，使数据存储稀疏，降低对表的扫描速度
3、如果主键比较大的话，那辅助索引将会变的更大，过长的主键值，会导致非叶子节点占用占用更多的物理空间

11.mysql索引的数据结构，各自优劣

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针
相互链接。

哈希索引就是采用一定的哈希算法，只需一次哈希算法即可立刻定位到相应的位置，速度非常快

1.单条记录查询的时候，可以选择哈希索引，查询性能最快；
2.如果是范围查询检索，哈希索引就毫无用武之地了。经过哈希算法后，数据有可能变成不连续的了，存在哈希碰撞问题。
3.哈希索引也没办法利用索引完成排序，以及模糊查询（本质上也是范围查询）
4.哈希索引也不支持多列联合索引的最左匹配规则；

12.索引设计的原则？

查询更快、占用空间更小

适合索引的列是出现在where子句中的列，或者连接子句中指定的列
基数较小的表没有必要在此列建立索引。（索引效果较差）
使用短索引。（如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间）
不要过度索引。（索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进
行更新甚至重构）
定义有外键的数据列一定要建立索引。
更新频繁字段不适合创建索引。
尽量扩展索引，不要新建索引。
对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。（如性别，男女未知，最多也就三种，区分度实在太低）

13.锁的类型有哪些

表锁

表锁是指上锁的时候锁住的是整个表。（当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问；特点：粒度大，加锁简单，容易冲突；）

行锁

行锁是指上锁的时候锁住的是表的某一行或多行记录。（他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问；特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高；）

记录锁(Record Lock)

记录锁也属于行锁中的一种，只不过记录锁的范围只是表中的某一条记录。（记录锁是说事务在加锁后锁住的只是表的某一条记录。精准条件命中，并且命中的条件字段是唯一索引加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。）

页锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。(表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般)

间隙锁(Gap Lock)

属于行锁中的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间。（当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。范围查询并且查询未命中记录，查询条件必须命中索引、间隙锁只会出现在REPEATABLE_READ（重复读)的事务级别中。触发条件：防止幻读问题，事务并发的时候，如果没有间隙锁，就会发生如下图的问题，在同一个事务里，A事务的两次查询出的结果会不一样。比如表里面的数据ID 为 1,4,5,7,10 ,那么会形成以下几个间隙区间，-n-1区间，1-4区间，7-10 区间，10-n区间（-n代表负无穷大，n代表正无穷大））

临建锁(Next-Key Lock)

也属于行锁的一种，并且它是INNODB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住。（再之它会把相邻的下一个区间也会锁住触发条件：范围查询并命中，查询命中了索引。结合记录锁和间隙锁的特性，临键锁避免了在范围查询时出现脏读、重复读、幻读问题。加了临键锁之后，在范围区间内数据不允许被修改和插入。）

意向共享锁

当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁

意向排他锁

当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的意向排它锁。

14.关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化过？

首先分析语句，对语句进行分析以及重写，去除不必要的查询多余列
分析语句的执行计划，然后获得其使用索引的情况，使得语句可以尽可能的命中索引。
如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表

15.事务的隔离级别

read uncommit 读未提交，可能会读到其他事务未提交的数据，也叫做脏读。
read commit 读已提交，两次读取结果不一致，叫做不可重复读。
repeatable read 可重复复读，这是mysql的默认级别，就是每次读取结果都一样，但是有可能产生幻读。
serializable 串行，一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。

16.ACID靠什么保证的？

A原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
C一致性由其他三大特性保证、程序代码要保证业务上的一致性
I隔离性由MVCC来保证
D持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log恢复

InnoDB redo log 写盘，InnoDB 事务进入 prepare 状态。如果前面 prepare 成功，binlog 写盘，再继续将事务日志持久化到 binlog，如果持久化成功，那么 InnoDB 事务则进入 commit 状态(在 redo log 里面写一个 commit 记录)

redolog的刷盘会在系统空闲时进行

17.什么是MVCC

多版本并发控制：读取数据时通过一种类似快照的方式将数据保存下来，这样读锁就和写锁不冲突了，不同的事务session会看到自己特定版本的数据，版本链.

MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

聚簇索引记录中有两个必要的隐藏列：

trx_id：用来存储每次对某条聚簇索引记录进行修改的时候的事务id。

roll_pointer：每次对哪条聚簇索引记录有修改的时候，都会把老版本写入undo日志中。这个roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。(注意插入操作的undo日志没有这个属性，因为它没有老版本)

已提交读和可重复读的区别就在于它们生成ReadView的策略不同。
开始事务时创建readview，readView维护当前活动的事务id，即未提交的事务id，排序生成一个数组访问数据，获取数据中的事务id（获取的是事务id最大的记录），对比readview： InnoDB redo log 写盘，InnoDB 事务进入 prepare 状态。如果前面 prepare 成功，binlog 写盘，再继续将事务日志持久化到 binlog，如果持久化成功，那么 InnoDB 事务则进入 commit 状态(在 redo log 里面写一个 commit 记录)

如果在readview的左边（比readview都小），可以访问（在左边意味着该事务已经提交）

如果在readview的右边（比readview都大）或者就在readview中，不可以访问，获取roll_pointer，取上一版本重新对比（在右边意味着，该事务在readview生成之后出现，在readview中意味着该事务还未提交）

已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView。

18.mysql主从同步原理

Mysql的主从复制中主要有三个线程： master（binlog dump thread）、slave（I/O thread 、SQL thread），Master一条线程和Slave中的两条线程。

1.主节点 binlog，主从复制的基础是主库记录数据库的所有变更记录到 binlog。binlog 是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。
2.主节点 log dump 线程，当 binlog 有变动时，log dump 线程读取其内容并发送给从节点。
3.从节点 I/O线程接收 binlog 内容，并将其写入到 relay log 文件中。
4.从节点的SQL 线程读取 relay log 文件内容对数据更新进行重放，最终保证主从数据库的一致性。

注：主从节点使用 binglog 文件 + position 偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从 position 的位置发起同步。

由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

全同步复制
主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半同步复制
和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

19.索引覆盖是什么

索引覆盖就是⼀个SQL在执⾏时，可以利⽤索引来快速查找，并且此SQL所要查询的字段在当前索引对应的字段中都包含了，那么就表示此SQL⾛完索引后不⽤回表了，所需要的字段都在当前索引的叶⼦节点上存在，可以直接作为结果返回了

20.最左前缀原则是什么

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