MySQL常见面试题(2023年最新)

悠悠 2023-09-24 19:09 93阅读 0赞

### 目录 ###

*  前言
 *  1.char和varchar的区别
 *  2.数据库的三大范式
 *  3.你了解sql的执行顺序吗？
 *  4.索引是什么
 *  5.索引的优点和缺点
 *  6.索引的类型
 *  7.索引怎么设计(优化)
 *  8.怎么避免索引失效(也属于sql优化的一种)
 *  9.索引的数据类型
 *  10.索引为什么使用树结构
 *  11.二叉查找树、B树、B+树
 *  12.为什么使用B+树不用B树
 *  13.最左匹配原则
 *  14.Mysql怎么查看是否使用到索引或怎么查看sql执行计划
 *  15.一条sql查询非常慢，我们怎么去排查和优化？
 *  16.MylSAM和InnoDB、Memory的区别
 *  17.什么是事务
 *  18.事务的四大特性(ACID)
 *  19.脏读、不可重复读、幻读
 *  20.事务的隔离级别？
 *  21.怎么优化数据库
 *  22.SQL优化
 *  23.常用的聚合函数
 *  24.几种关联查询
 *  25.in和exists的区别
 *  26.drop、truncate、delete的区别

# 前言 #

[java最新面试题(java基础、集合、多线程、jvm、锁、算法、CAS、Redis、数据库、mybatis、spring、springMVC、springBoot、微服务)][java_java_jvm_CAS_Redis_mybatis_spring_springMVC_springBoot]

# 1.char和varchar的区别 #

①char设置多少长度就是多少长度，varchar可以改变长度，所以char的空间利用率不如varchar的空间利用率高。  
②因为长度固定，所以存取速度要比varchar快。  
③char适用于固定长度的字符串，比如身份证号、手机号等，varchar适用于不固定的字符串。

# 2.数据库的三大范式 #

**第一范式(1NF)：** 保证字段不可再分，保证原子性。  
**第二范式(2NF)：** 满足1NF前提下，表的每一列都必须和主键有关系。消除部分依赖关系。  
**第三范式(3NF)：** 满足2NF前提下，表的每一列比必须和主键有直接关系，不能是间接关系。消除传递依赖

# 3.你了解sql的执行顺序吗？ #

**⑧`select`** **⑨`distinct(去重)`** **⑥`聚合函数`**

**①`from`** 表1

**③`[inner join | left join | right join](连接)`** 表2

**②`on(连接条件)`** 表1.字段 = 表2.字段

**④`where`** 查询条件

**⑤`group by(分组)`** 字段

**⑦`having`** 分组过滤条件

**⑩`order by(排序)`** 字段

**⑪`limit(分页)` 0,10**

# 4.索引是什么 #

是一种高效获取数据的数据结构，相当于目录，更快的找到数据，是一个文件，占用物理空间。

# 5.索引的优点和缺点 #

**优点：**  
①提高检索的速度。  
②索引列对数据排序，降低排序成本。  
③mysql 8之后引入了，隐藏索引，当一个索引被隐藏就不会被优化器所使用，就可以看出来索引对数据库的影响，有利于调优。  
**缺点：**  
①索引也是一个文件，所以会占用空间。  
②降低更新的速度，因为不光要更新数据，还要更新索引。

# 6.索引的类型 #

**①普通索引：** 基本索引类型，允许定义索引的字段为空值和重复值。  
**②唯一索引：** 索引的值必须唯一，允许定义索引的字段为空值。  
**③主键索引：** 索引的值必须唯一，不可以为空。  
**④复合索引：** 多个字段加索引，遵守最左匹配规则。  
**⑤全局索引：** 只有在 MyISAM 引擎上才能使用。

# 7.索引怎么设计(优化) #

①选择唯一性索引：值是唯一的，查询的更快。  
②经常作为查询条件的字段加索引。  
③为经常需要排序、分组和联合操作的字段建立索引：order by、group by、union(联合)、distinct(去重)等。  
④限制索引个数：索引数量多，需要的磁盘空间就越多，更新表时，对索引的重构和更新就很费劲。  
⑤表数据少的不建议使用索引(百万级以内)：数据过少，有可能查询的速度，比遍历索引的速度都快。  
⑥删除不常用和不再使用的索引。  
⑦用类型小的类型做索引：比如：int和BIGINT能用int就使用int。因为类型小，查询速度快和索引占用的空间更少。  
⑧使用前缀索引，要是字符串越长，那么索引占的空间越大，并且比较起来就时间就越长。

# 8.怎么避免索引失效(也属于sql优化的一种) #

①某列使用范围查询(>、<、like、between and)时, 右边的所有列索引也会失效。  
②不要对索引字段进行运算。  
③在where子句中不要使用 OR、!=、<>和对值null的判断。  
④避免使用’%'开头的like的模糊查询。  
⑤字符串不加单引号，造成索引失效。

# 9.索引的数据类型 #

**Hash：** 查询时调用Hash函数获得地址，回表查询实际数据。(InnoDB和MylSAM不支持，Memory支持)。  
**B+树：** 每次从根节点出发去查询，然后得到地址，回表查询实际数据。

# 10.索引为什么使用树结构 #

因为可以加快查询效率，而且可以保持有序。

# 11.二叉查找树、B树、B+树 #

**二叉查找树(二叉排序树、二叉搜索树)：** 一个节点最多两个子节点(左小右大)，查询次数和比较次数都是最小的，但是索引是存在磁盘的，当数据量过大的时候，不能直接把整个索引文件加载到内存，需要分多次IO，最坏的情况IO的次数就是树的高度，为了减少IO，需要把树从竖向变成横向。  
**B树( B- )：** 是一种多路查询树，每个节点包含K个子节点，节点都存储索引值和数据，K是B树的阶(树高被称为树的阶)。虽然比较的次数比较多，但是是在内存的比较，可以忽略不计，但是B树IO的次数要比二叉查找树要少，因为B树的高度可以更低。  
**B+树：** B树的升级版，只有叶子节点储存的是索引值指向的数据库的数据。

# 12.为什么使用B+树不用B树 #

**①B树只适合随机检索，而B+树同时支持随机检索和顺序检索(因为叶子节点相当于链表，保存索引值都是有序的)。**  
**顺序检索：** 按照序列顺序遍历比较找到给定值。  
**随机检索：** 不断从序列中随机抽取数据进行比较，最终找到结果。

**②减少了磁盘IO，提高空间利用率：** 因为B+树非叶子节点不会存放数据，只有索引值，所以非叶子节点可以保存更多的索引值，这样B+树就可以更矮，减少IO次数。

**③B+树适合范围查找：** 这才是关键，因为数据库大部分都是范围查找，B+树的叶子节点是有序链表，直接遍历就行，而B树的范围查找可能两个节点距离很远，只能通过中序遍历去查找，所以使用B+树更合适。  
**中序遍历：** (根在中，从左往右，一棵树的左子树永远在根前面，根永远在右子树前面）

# 13.最左匹配原则 #

最左优先，以最左边为起点任何连续的索引都能匹配上。同时遇到范围查询（>、<、between and、like）就会停止匹配。

例如：Z表建立联合索引 (a,b,c)

//这样索引abc列都起效，因为符合最左匹配原则，where子句几个搜索条件顺序调换不影响查询结果，因为Mysql中有查询优化器，会自动优化查询顺序
    select  *  from Z where a = 1 and b = 2 and c = 3 
    
    //因为a列是起点，没有a列匹配不上，所以索引失效
    select * from table_name where  b = 2 and c = 3 
    
    //因为连续不到b，所以只有a列索引生效
    select * from table_name where  a = 1 and c = 3

# 14.Mysql怎么查看是否使用到索引或怎么查看sql执行计划 #

使用**explain**

> 例如：explain select \* from 表名 where 条件  
> 结果：会查出key，key就是你使用的索引。还有type这个字段，可以看到索引是全表扫描还是索引扫描等等。  
> type字段内容性能对比：ALL < index < range ~ index\_merge < ref < eq\_ref < const < system

# 15.一条sql查询非常慢，我们怎么去排查和优化？ #

**排查：**  
(1) 开启慢查询。  
(2) 查看慢查询日志(定位低效率sql，命令：show processlist)。  
(3) 使用explain查看sql的执行计划(看看索引是否失效或者性能低)

**优化：**  
sql优化 + 索引 + 数据库结构优化 + 优化器优化

# 16.MylSAM和InnoDB、Memory的区别 #

**MylSAM：** mysql5.5之前的存储引擎，是表锁（悲观锁）级别的，不支持事务和外键。  
**InnoDB：** mysql5.5之后的存储引擎，是行锁（乐观锁）级别的，支持事务和外键。  
**Memory：** 内存数据库引擎，因为在内存操作，所以读写很快，但是Mysql服务重启，会丢失数据，不支持事务和外键。

# 17.什么是事务 #

**[事务和隔离级别详解及实际应用][Link 1]**

事务是对数据库中一系列操作进行统一的回滚或者提交的操作，主要用来保证数据的完整性和一致性。

# 18.事务的四大特性(ACID) #

**原子性(Atomicity)：** 要么全部成功要么全部失败。  
**一致性(Consistency)：** 事务执行前和事务执行后，原本和数据库一致的数据仍然一致。  
**隔离性(Isolation)：** 事务与事务之间互不干扰。  
**持久性(Durability)：** 事务一旦被提交了，那么对数据库中的数据的改变就是永久的。

# 19.脏读、不可重复读、幻读 #

**脏读：** 也叫"读未提交"，顾名思义，就是某一事务A读取到了事务B未提交的数据。  
**不可重复读：** 在一个事务内，多次读取同一个数据，却返回了不同的结果。实际上，这是因为在该事务间隔读取数据的期间，有其他事务对这段数据进行了修改，并且已经提交，就会发生不可重复读事故。  
**幻读：** 在同一个事务中，第一次读取到结果集和第二次读取到的结果集不同。像幻觉一样所以叫幻读。  
**从上面可以看出脏读和不可重复读是基于数据值的错误，幻读是基于条数增加或者减少的错误**

# 20.事务的隔离级别？ #

**① read uncommited(读取未提交内容)：** 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。读取未提交的数据，也被称之为脏读（Dirty Read）  
**② read committed(读取提交内容)：** 这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。一个事务只能看见已经提交事务所做的改变。可解决脏读  
**③ repeatable read(可重读)：** 这是MySQL的默认事务隔离级别，同一事务的多个实例在并发读取数据时，会看到同样的数据。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。可解决脏读、不可重复读  
**④ serializable(可串行化) ：** 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。可解决脏读、不可重复读、幻读。

# 21.怎么优化数据库 #

①SQL优化  
②加缓存  
③分表  
④读写分离

# 22.SQL优化 #

①不要用select \*，要使用具体字段。  
②使用数值代替字符串，比如：0=唱，1=跳，2=rap。  
③避免返回大量数据，采用分页最好。  
④使用索引，提升查询速度，不宜建太多索引，不能建在重复数据比较多的字段上。  
⑤批量插入比单条插入要快，因为事务只需要开启一次，数据量太小体现不了。  
⑥避免子查询，优化为多表连接查询。  
⑦尽量使用union all替代union，因为union会自动去重。

# 23.常用的聚合函数 #

①sum(列名) 求和 　　　　  
②max(列名) 最大值 　　　　  
③min(列名) 最小值 　　　　  
④avg(列名) 平均值 　　　　  
⑤first(列名) 第一条记录  
⑥last(列名) 最后一条记录  
⑦count(列名) 统计记录数不包含null值 count(\*)包含null值。

# 24.几种关联查询 #

**内连接(inner join)：** 查询两个表匹配数据。  
**左连接(left join)：** 查询左表全部行以及右表匹配的行。  
**右连接(right join)：** 查询右表全部行以及左表匹配的行。

# 25.in和exists的区别 #

**in()：** 适合子表(子查询)比主表数据小的情况。  
**exists()：** 适合子表(子查询)比主表数据大的情况。

# 26.drop、truncate、delete的区别 #

**速度：** drop > truncate > delete。  
**回滚：** delete支持，truncate和drop不支持。  
**删除内容：** delete表结构还在，删除部分或全部数据，不释放空间。truncate表结构还在，删除全部数据，释放空间。drop表结构和数据不在，包括索引和权限，释放空间。

[java_java_jvm_CAS_Redis_mybatis_spring_springMVC_springBoot]: https://blog.csdn.net/twotwo22222/article/details/128937317?spm=1001.2014.3001.5502
[Link 1]: https://blog.csdn.net/twotwo22222/article/details/127731715?csdn_share_tail=%7B%22type%22:%22blog%22,%22rType%22:%22article%22,%22rId%22:%22127731715%22,%22source%22:%22twotwo22222%22%7D