MySQL 三万字精华总结 + 面试100 问，和面试官扯皮绰绰有余（收藏系列）

我会带着你远行 2023-09-30 11:17 15阅读 0赞

## ![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap_size_20_color_FFFFFF_t_70_g_se_x_16][] ##

写在之前：不建议那种上来就是各种面试题罗列，然后背书式的去记忆，对技术的提升帮助很小，对正经面试也没什么帮助，有点东西的面试官深挖下就懵逼了。

个人建议把面试题看作是费曼学习法中的回顾、简化的环节，准备面试的时候，跟着题目先自己讲给自己听，看看自己会满意吗，不满意就继续学习这个点，如此反复，好的offer离你不远的，奥利给

### 一、MySQL架构 ###

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，**插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离**。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

*  **连接层**：最上层是一些客户端和连接服务。**主要完成一些类似于连接处理、授权认证、及相关的安全方案**。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
 *  **服务层**：第二层服务层，主要完成大部分的核心服务功能， 包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等
 *  **引擎层**：第三层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取
 *  **存储层**：第四层为数据存储层，主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互

> 画出 MySQL 架构图，这种变态问题都能问的出来
> 
> MySQL 的查询流程具体是？or 一条SQL语句在MySQL中如何执行的？

客户端请求 ---> 连接器（验证用户身份，给予权限） ---> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） ---> 分析器（对SQL进行词法分析和语法分析操作） ---> 优化器（主要对执行的sql优化选择最优的执行方案方法） ---> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） ---> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap_size_20_color_FFFFFF_t_70_g_se_x_16 2][]

> 说说MySQL有哪些存储引擎？都有哪些区别？

### 二、存储引擎 ###

存储引擎是MySQL的组件，用于处理不同表类型的SQL操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

使用哪一种引擎可以灵活选择，**一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求**，使用合适的存储引擎，将会提高整个数据库的性能 。

MySQL服务器使用**可插拔**的存储引擎体系结构，可以从运行中的 MySQL 服务器加载或卸载存储引擎 。

#### 查看存储引擎 ####

-- 查看支持的存储引擎
    SHOW ENGINES
    
    -- 查看默认存储引擎
    SHOW VARIABLES LIKE 'storage_engine'
    
    --查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
    show create table tablename
    
    --准确查看某个数据库中的某一表所使用的存储引擎
    show table status like 'tablename'
    show table status from database where name="tablename"

**设置存储引擎**

-- 建表时指定存储引擎。默认的就是INNODB，不需要设置
    CREATE TABLE t1 (i INT) ENGINE = INNODB;
    CREATE TABLE t2 (i INT) ENGINE = CSV;
    CREATE TABLE t3 (i INT) ENGINE = MEMORY;
    
    -- 修改存储引擎
    ALTER TABLE t ENGINE = InnoDB;
    
    -- 修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎
    SET default_storage_engine=NDBCLUSTER;

更多java高级学习课程资料，java课件，源码，安装包等小编已经整理打包好！有兴趣想要学习的小伙伴点赞私信回复学习即可免费领取！

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap_size_20_color_FFFFFF_t_70_g_se_x_16 3][]

默认情况下，每当 `CREATE TABLE` 或 `ALTER TABLE` 不能使用默认存储引擎时，都会生成一个警告。为了防止在所需的引擎不可用时出现令人困惑的意外行为，可以启用 `NO_ENGINE_SUBSTITUTION SQL` 模式。如果所需的引擎不可用，则此设置将产生错误而不是警告，并且不会创建或更改表

#### 存储引擎对比 ####

常见的存储引擎就 InnoDB、MyISAM、Memory、NDB。

InnoDB 现在是 MySQL 默认的存储引擎，支持**事务、行级锁定和外键**

文件存储结构对比

在 MySQL中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的 `.frm` 文件，`.frm` 文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，与数据库存储引擎无关，也就是任何存储引擎的数据表都必须有`.frm`文件，命名方式为 数据表名.frm，如user.frm。

查看MySQL 数据保存在哪里：`show variables like 'data%'`

MyISAM 物理文件结构为：

*  `.frm`文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
 *  `.MYD` (`MYData`) 文件：MyISAM 存储引擎专用，用于存储MyISAM 表的数据
 *  `.MYI` (`MYIndex`)文件：MyISAM 存储引擎专用，用于存储MyISAM 表的索引相关信息

InnoDB 物理文件结构为：

*  `.frm` 文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
 *  `.ibd` 文件或 `.ibdata` 文件： 这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用**共享表空间**存放存储数据，还是用**独享表空间**存放存储数据。
    
    独享表空间存储方式使用`.ibd`文件，并且每个表一个`.ibd`文件 共享表空间存储方式使用`.ibdata`文件，所有表共同使用一个`.ibdata`文件（或多个，可自己配置）

> ps：正经公司，这些都有专业运维去做，数据备份、恢复啥的，让我一个 Javaer 搞这的话，加钱不？

面试这么回答

1.  InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
2.  InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
3.  InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4.  InnoDB 不保存表的具体行数，执行`select count(*) from table` 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
5.  InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

<table> 
 <thead> 
  <tr> 
   <th>对比项</th> 
   <th>MyISAM</th> 
   <th>InnoDB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主外键</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>事务</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>行表锁</td> 
   <td>表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作</td> 
   <td>行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作</td> 
  </tr> 
  <tr> 
   <td>缓存</td> 
   <td>只缓存索引，不缓存真实数据</td> 
   <td>不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响</td> 
  </tr> 
  <tr> 
   <td>表空间</td> 
   <td>小</td> 
   <td>大</td> 
  </tr> 
  <tr> 
   <td>关注点</td> 
   <td>性能</td> 
   <td>事务</td> 
  </tr> 
  <tr> 
   <td>默认安装</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
 </tbody> 
</table>

> 一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失；

如果表的类型是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。

> 哪个存储引擎执行 select count(\*) 更快，为什么?

MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。

*  在 MyISAM 存储引擎中，把表的总行数存储在磁盘上，当执行 select count(\*) from t 时，直接返回总数据。
 *  在 InnoDB 存储引擎中，跟 MyISAM 不一样，没有将总行数存储在磁盘上，当执行 select count(\*) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量。

InnoDB 中 count(\*) 语句是在执行的时候，全表扫描统计总数量，所以当数据越来越大时，语句就越来越耗时了，为什么 InnoDB 引擎不像 MyISAM 引擎一样，将总行数存储到磁盘上？这跟 InnoDB 的事务特性有关，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

### 三、数据类型 ###

主要包括以下五大类：

*  整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
 *  浮点数类型：FLOAT、DOUBLE、DECIMAL
 *  字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
 *  日期类型：Date、DateTime、TimeStamp、Time、Year
 *  其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap_size_20_color_FFFFFF_t_70_g_se_x_16 4][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap_size_20_color_FFFFFF_t_70_g_se_x_16 5][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap_size_20_color_FFFFFF_t_70_g_se_x_16 6][]

> CHAR 和 VARCHAR 的区别？

char是固定长度，varchar长度可变：

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

存储时，前者不管实际存储数据的长度，直接按 char 规定的长度分配存储空间；而后者会根据实际存储的数据分配最终的存储空间

相同点：

1.  char(n)，varchar(n)中的n都代表字符的个数
2.  超过char，varchar最大长度n的限制后，字符串会被截断。

不同点：

1.  char不论实际存储的字符数都会占用n个字符的空间，而varchar只会占用实际字符应该占用的字节空间加1（实际长度length，0<=length<255）或加2（length>255）。因为varchar保存数据时除了要保存字符串之外还会加一个字节来记录长度（如果列声明长度大于255则使用两个字节来保存长度）。
2.  能存储的最大空间限制不一样：char的存储上限为255字节。
3.  char在存储时会截断尾部的空格，而varchar不会。

char是适合存储很短的、一般固定长度的字符串。例如，char非常适合存储密码的MD5值，因为这是一个定长的值。对于非常短的列，char比varchar在存储空间上也更有效率。

> 列的字符串类型可以是什么？

字符串类型是：SET、BLOB、ENUM、CHAR、TEXT、VARCHAR

> BLOB和TEXT有什么区别？

BLOB是一个二进制对象，可以容纳可变数量的数据。有四种类型的BLOB：TINYBLOB、BLOB、MEDIUMBLO和 LONGBLOB

TEXT是一个不区分大小写的BLOB。四种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。

BLOB 保存二进制数据，TEXT 保存字符数据。

--------------------

### 四、索引 ###

> 说说你对 MySQL 索引的理解？
> 
> 数据库索引的原理，为什么要用 B+树，为什么不用二叉树？
> 
> 聚集索引与非聚集索引的区别？
> 
> InnoDB引擎中的索引策略，了解过吗？
> 
> 创建索引的方式有哪些？
> 
> 聚簇索引/非聚簇索引，mysql索引底层实现，为什么不用B-tree，为什么不用hash，叶子结点存放的是数据还是指向数据的内存地址，使用索引需要注意的几个地方？

*  MYSQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构，所以说**索引的本质是：数据结构**
 *  索引的目的在于提高查询效率，可以类比字典、 火车站的车次表、图书的目录等 。
 *  可以简单的理解为“排好序的快速查找数据结构”，数据本身之外，**数据库还维护者一个满足特定查找算法的数据结构**，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图是一种可能的索引方式示例。
    
    ![format_png][]
    
    左边的数据表，一共有两列七条记录，最左边的是数据记录的物理地址
    
    为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值，和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到对应的数据，从而快速检索出符合条件的记录。
 *  索引本身也很大，不可能全部存储在内存中，**一般以索引文件的形式存储在磁盘上**
 *  平常说的索引，没有特别指明的话，就是B+树（多路搜索树，不一定是二叉树）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。此外还有哈希索引等。

#### 基本语法： ####

*  创建：
    
     *  创建索引：`CREATE [UNIQUE] INDEX indexName ON mytable(username(length));`
        
        如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
     *  修改表结构(添加索引)：`ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)`
 *  删除：`DROP INDEX [indexName] ON mytable;`
 *  查看：`SHOW INDEX FROM table_name\G` \--可以通过添加 \\G 来格式化输出信息。
 *  使用ALERT命令
    
     *  `ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):` 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
     *  `ALTER TABLE tbl_name ADD UNIQUE index_name (column_list` 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
     *  `ALTER TABLE tbl_name ADD INDEX index_name (column_list)` 添加普通索引，索引值可出现多次。
     *  `ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)`该语句指定了索引为 FULLTEXT ，用于全文索引。

#### 优势 ####

*  **提高数据检索效率，降低数据库IO成本**
 *  **降低数据排序的成本，降低CPU的消耗**

#### 劣势 ####

*  索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存
 *  虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息

#### MySQL索引分类 ####

数据结构角度

*  B+树索引
 *  Hash索引
 *  Full-Text全文索引
 *  R-Tree索引

从物理存储角度

*  聚集索引（clustered index）
 *  非聚集索引（non-clustered index），也叫辅助索引（secondary index）
    
    聚集索引和非聚集索引都是B+树结构

从逻辑角度

*  主键索引：主键索引是一种特殊的唯一索引，不允许有空值
 *  普通索引或者单列索引：每个索引只包含单个列，一个表可以有多个单列索引
 *  多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
 *  唯一索引或者非唯一索引
 *  空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。 MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

> 为什么MySQL 索引中用B+tree，不用B-tree 或者其他树，为什么不用 Hash 索引
> 
> 聚簇索引/非聚簇索引，MySQL 索引底层实现，叶子结点存放的是数据还是指向数据的内存地址，使用索引需要注意的几个地方？
> 
> 使用索引查询一定能提高查询的性能吗？为什么?

#### MySQL索引结构 ####

**首先要明白索引（index&\#x**

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/da886610f4b5416b86099bde8e7f796e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/29cd154068f64c6c81968eb7ce2e74b8.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/034222481a0143ddbac49db0b36b7c4e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/fae6f81179e742f1b6286ab0235dfdf3.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://img-blog.csdnimg.cn/d60764bd258442ef91e2eeaf4aad6364.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap_size_20_color_FFFFFF_t_70_g_se_x_16 5]: https://img-blog.csdnimg.cn/5ec2fc29d39f43a194f5719386be1d10.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap_size_20_color_FFFFFF_t_70_g_se_x_16 6]: https://img-blog.csdnimg.cn/50f030138ef343a89552a272af7fb776.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap,size_20,color_FFFFFF,t_70,g_se,x_16
[format_png]: https://img-blog.csdnimg.cn/img_convert/fbe1949dd940229ea29cb5ff530f52f0.webp?x-oss-process=image/format,png