MySQL面试题持续更新

六六村第一指定高手

已于 2024-09-07 01:20:50 修改

阅读量1.1k

点赞数 18

文章标签： mysql 数据库

于 2024-09-05 14:53:39 首次发布

本文链接：https://blog.csdn.net/qq2501055281/article/details/141931120

版权

构思导师还PUA我搞科研？搞完睡桥洞？MySQL面试题持续更新

char和varchar的区别

char是固定长度，设定好后不可改变；varchar是变长，一般情况下我们会说char的空间利用率是不如varchar的。但是这也导致了他们两个不同的使用场景，比如char我们用来存一些定长的内容，如手机号，身份证号等。varchar用来存变长的内容，如名字，简介等。
char是定长，所以存取速度比varchar要快

数据类型优化策略

更小的通常更好：char>varchar; 整型根据长度选择
简单的就好：整型比字符串操作代价低；时间累优于字符串；用整型存储IP
blog与text几乎不用，效率低下
使用枚举类代替字符串：e enum(‘man’,‘woman’) not null; 会压缩空间
datetime>timestamp>date; datetime与时区无关，timestamp与时区有关；datetime范围更大；date可以使用函数。

数据库三大范式

第一范式：保证字段不可分，原子性
第二范式：每一列都与主键有关系，消除部分依赖关系
第三范式：表的每一列与主键都有直接关系，不是间接关系，消除传递依赖

范式与反范式

反范式

优点：

所有数据都在一张表，避免关联
可以设计有效的索引

缺点：

表内的冗余多

范式

优点：

更新快
冗余少
数据小

缺点：

需要关联

主键的选择

代理主键：与业务无关，无意义的数字序列

自然主键：事务属性中的自然唯一标识

推荐使用代理主键：

他们不与业务耦合，更容易维护
一个大多数表，通用的键策略（主键生成器）能够减少需要编写的源码数量

主键优化

尽量降低长度
尽量顺序插入，使用自增主键
推荐使用代理主键
避免对主键的修改

SQL的执行顺序是什么？

首先我们要拿表，然后根据on条件将表进行连接，然后通过where条件进行筛选，通过group by进行分组，如果有聚合函数就需要执行聚合函数，通过having过滤分组，然后再执行select，再进行去重，然后进行排序，最后进行分页。

讲一讲MySQL的索引

首先索引是一种快速获取数据的数据结构，同时它在MySQL中是一个文件，占用一定的物理空间。（概念）
使用索引能够提高检索的速度，降低排序成本，但是会增加占用空间，降低更新数据的速度，因为还要更新索引。（优缺点）
索引的种类：
①普通索引：基本索引类型，允许定义索引的字段为空值和重复值。
②唯一索引：索引的值必须唯一，允许定义索引的字段为空值。
③主键索引：索引的值必须唯一，不可以为空。
④复合索引：多个字段加索引，遵守最左匹配规则。
⑤全局索引：只有在 MyISAM 引擎上才能使用。
根据MySQL使用的引擎不同，索引的组织形式以及索引使用的数据结构都会有所不同，InnoDB引擎是使用聚簇索引，也就是数据库文件与索引文件存储在一起，而MyISAM是数据库文件与索引文件分开存放。InnoDB、MyISAM索引默认使用的数据结构是B+树， Memory使用的是Hash表。
尽管InnoDB、MyISAM索引都是B+树，但是不同的引擎，B+树叶子节点中存放的内容是不同的
- InnoDB叶子节点直接放置整行数据
  - InnoDB默认对主键创建索引，如果没有主键，那么会选择唯一键，如果还没有，就会生成一个6字节的row_id作为主键，此时叶子节点是整行数据
  - 如果创建索引的键是其他字段，那么叶子结点存放的是该记录的主键，然后通过主键索引找到对应的记录。这个叫回表。数据量小的时候回表负优化，数据量大才有用。
- MyISAM存放的是一个指针，指针指向的位置为表的数据

B+树索引与Hash表索引、其他树的区别？（为什么最终选择B+树索引）

不使用哈希的原因

Hash表的索引是使用hash算法+数组+链表+红黑树的方式实现的，这导致了以下缺点：

利用hash存储的话需要将所有的数据文件添加到内存，比较浪费内存空间
如果所有的查询都是等值查询，hash确实很快，但是实际中范围查找比等值查找多得多，因此hash不适合
会产生hash冲突（使用扰动函数减少hash冲突），当有很多冲突的时候，要遍历所有的行指针，而且维护代价也很高
哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行（无法索引覆盖）
哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序

不使用二叉树的原因

无论是二叉树还是红黑树，都会因为树的深度过深而造成IO次数变多，影响读写效率，这是为什么要少用select * 的原因，select * 会扫描全盘，降低IO速度。

为什么B树可以

B树的每个节点都有多个数据，减少IO
B树的深度更潜，减少IO

为什么最终使用B+树

因为B树的非叶子结点中存储数据会导致B树能够容纳的范围变小。

优势：

B+树在相同内存空间中能够容纳更多的数据
B+树有两种头指针，一个指向根节点一个指向最小叶子结点，所以既可以范围查找，又可以从根节点开始随机查找

B+树到底是3层还是4层？

取决于索引的类型，一个节点能够容纳的范围等于，节点大小除以索引的大小，所以索引越小，能够容纳的越多，层数也就越小，所以多建议使用int来做索引而不是varchar或text。

索引有哪些匹配方式

全值匹配：和索引中的所有列进行匹配，出现在组合索引中。
匹配最左前缀：只匹配索引中的前面几个字段
匹配列前缀：匹配某一列的值的开头部分explain select * from staffs where name like 'J%'
匹配范围值：可以查找某一范围的数据explain select * from staffs where name >'Mary'
精确匹配某一列并范围匹配另外一列：可以查询第一列的全部，和第二列的部分explain select * from staffs where name='Mary' and age > 25，范围查找后的索引会失效
只访问索引的查询：查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引。explain select name,age,pos from staffs where name='Mary' and age=25 and pos='Beijin'

什么是回表、覆盖索引、最左匹配、索引下推、谓词下推？

回表：查非主键的字段，首先会走一次索引找主键，然后再通过主键索引找对应的行。走两次索引就叫做回表
索引覆盖：子查询有索引的列，不触发二次回表的查询就是索引覆盖。
最左匹配：对于复合索引，只有按顺序匹配部分索引才遵循了最左匹配原则。如对于索引(1,2,3)，查询(1,2)是会走索引的，而查询(2,3)不走索引。
索引下推：MySQL中的数据拉去是用户—>mysql server----->存储引擎---->磁盘，如果有索引下推那么不需要server做任何的数据筛选，在磁盘就讲数据进行了筛选，磁盘中数据时顺序存储的，筛选会更快，并且整体IO会更少。
谓词下推：对于这样一条SQLselect t1.name,t2.name from t1 join t2 on t1.id=t2.id;，mysql有两种做法：
- 先做表连接（全表连接），组合出中间表，然后再查询需要的列
- 先把需要的列（列连接）都拿出来，然后再做关联：谓词下推，IO少，效率高

为什么like以%开头的模糊查询索引会失效？

首先，并不是以%开头的模糊查询就一定会索引失效，当我们使用索引覆盖技巧时（只查询主键和索引列）以%开头的模糊查询其实并不会失效。其他情况失效的原因是：

B+树的索引顺序是按照首字母大小进行排序，%开头的话就无法按照首字母的排序进行查询了，因为无法确定开头字母。而%在右边的话，首字母是确定的，所以可以走索引。若%%，也是用不上索引的。

前缀索引的设计思路

有时候需要索引很长的字符串，这会让索引变大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，索引的选择性越高则查询效率越高，因为选择性高的索引可以让，mysql在查找的时候过滤掉更多的行。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应BLOB，TEXT，VARCHAR类型的列，必须使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

添加前缀索引

alter table xxx add index index_1(city);

注意：前缀索引是一种能使索引最小最快的有效方法，但是也包含缺点：mysql无法使用前缀索引做order by和group by。

索引优化

使用索引列进行查询的时候尽量不要使用表达式，因为表达式会使索引失效
尽量使用主键索引
选择合适长度的前缀索引
使用索引来排序
union all, in, or都是用索引，但是推荐使用in，因为or走索引只在单列索引和全部的列都是索引的情况。
范围查询放最后，因为范围查询会导致后面的列索引失效
不要用类型转换，转换后会导致全表扫描
经常更新的字段不适合添加索引
创建索引的列不要有null，会导致不符合预期的结果。 mysql内部认为null是相等的，所以导致当插入过多null值，造成重复率过多，is null不会走索引。而is not null因为查询的结果过多，优化器选择了全表扫描。
创建索引的时候应该避免以下错误概念
- 索引越多越好
- 过早优化，在不了解系统的情况下进行优化

Insert优化

执行批量插入
```
Insert into tb values (),(),()...
```
手动提交事务
```
start transaction
Insert ...
commit;
```
主键顺序插入

乱序插入会导致索引重排，在B+树中设计到结点的分裂与合并
大批量插入数据使用load指令

Select优化

order by优化
- 根据排序字段建立合适的索引，多字段排序时，遵循最左前缀法则
- 尽量使用覆盖索引，如果是select * 则不会走索引
- 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则，默认不会用索引，索引是按升序排列
- 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size
group by优化
- 通过索引提高效率
- 满足最左前缀法则
limit优化：考虑使用子查询来代替，经典题：如何查询第1000条数据后的第7条。
count优化：
- count(*)与count(1)都是不取字段的
- count(字段)<count(主键 id)<count(1) = count(*)

update优化

update的根据索引字段更新是行锁，根据普通字段更新是表锁。

事务的四大特性（ACID原则）

原子性：不可分割，要么全成功，要么全失败
一致性：事务完成时，所有数据保持一致状态
隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行，事务与事务之间不会影响。
持久性：一旦提交或回滚，改变是永久的

事务的并发问题与隔离级别

问题	描述
脏读	一个事务读取到另一个事务还没提交的数据
不可重复读	一个事务先后读取到同一条记录，但两次读取的数据不同，称之为不可重复度，中间被其他事务影响
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时又发现了这行数据已经存在。

MySQL使用不同的事务隔离级别能够解决对应的事务并发问题，但是隔离性越高，性能越低，安全性越高：

隔离级别	脏读	不可重复读	幻读
read uncommitted（读未提交）	√	√	√
read committed（读已提交）	x	√	√
repeatable read（可重复度，默认）	x	x	√
serializable（序列化）	x	x	x

事务的原理

按照事务的ACID原则来回答这个问题。原子性、一致性、持久性由redo log和undo log来实现，隔离性由MVCC（多版本并发控制）和LBCC（锁机制）来实现。

redo log

是重做日志，记录的事事务提交时数据页的物理修改，是用来实现事务的持久性的。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）和重做日志文件（redo log file），前者是在内存中，后者是在磁盘中。当事务提交后会把所有修改信息都存到该日志文件中，用于刷新脏页到磁盘发生错误时，进行数据恢复时用。执行过程如下：

事务提交时先将磁盘的数据页读到buffer中
然后再buffer中执行更新操作，此时数据页叫脏页
一段时间后，将脏页的数据刷新到磁盘中
在此之前，会将数据页的变化记录到redu log buffer中
事务一旦提交就会将log中的记录刷新到磁盘中
此时如果脏页刷新失败，就可以用log中的记录恢复磁盘中的数据
如果脏页的数据成功刷新到磁盘中，那么redo log中的记录会被清理

为什么不立即将buffer写入磁盘？

因为buffer与磁盘之间的读写是随机的，而随机IO的性能很低，而redo log写到磁盘是顺序的，效率高（先写日志机制）

undo log

是回滚日志，用于记录数据被修改前的信息，保证了事务的原子性。他的作用有二：提供回滚和MVCC。

undo log和redo log记录物理日志不一样，他是逻辑日志，当执行一条语句时，它会记录一条相反的操作。当执行roll back时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

MVCC

多版本并发控制，维护了一个数据的多个版本，使得读写操作之间没有冲突，每个事务都能读取到正确的数据版本。MVCC的具体实现依赖于快照读、数据库记录中的三个隐藏字段、undo log、readView。

LBCC(锁)

其中的临键锁(Next-Key Lock)是行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙。它的运用能够解决幻读问题，但是锁的应用也导致了并发环境下效率的降低。

MVCC原理

MVCC由以下四个核心来实现：记录中的隐藏字段、undo log、undo log版本链、readView。接下俩详细讲解这四个核心。

快照读：不加锁的select就是快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁是非阻塞读。相对应的加锁的是当前读，读取的是记录的最新版本，保证其他并发事务不能修改当前记录。
- Read Committed：每次select，都生成一个快照读
- Repeatable Read：开启事务后第一个select语句才是快照读
- Serializable：快照读会退化为当前读
记录中的隐藏字段
- DB_TRX_ID：最近修改事务ID，插入这条记录或最后一次修改该记录的事务ID
- DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
- DB_ROW_ID：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。
undo log：当insert的时候，产生的undo log日志只在回滚时需要，在事务提交之后，可被立即删除。而update、delete的时候，产生的undo log不仅在回滚时需要，在快照读时也需要，不会立即被删除。
undo log版本链

不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链，链表的头部是最新的记录号，链表尾是最老的旧记录。
readView

ReadView(读视图)是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交）id。

ReadView中包含了四个核心字段：
- 当前活跃的事务ID集合
- 最小活跃事务ID
- 预分配事务ID，当前最大事务ID+1
- ReadView创建者的事务ID
根据快照读不同生成时机，然后结合版本链访问规则查询正确的数据。总的来说这个访问规则就是看当前这个版本是不是当前这个事务的或者是已经提交的事务版本，是就返回这个版本。