今天我们一起来讨论讨论MySQL存储引擎。

1.什么是存储引擎

2.都有哪些存储引擎（对比）

3.存储引擎能帮我们做什么（对比）

4.他为什么能做到这些（对比）

5.他背后的底层原理是什么（对比）

首先我们先来看看MySQL内部都有哪些部分吧



MySQL存储引擎_客户端


分为三个层次

最上方-客户端：命令行、jdbc、navicat可视化工具都归属于客户端，用来提交SQL语句

第二层-mysqlserver：要运行sql必须要启动mysql的服务，如果不启动的话是运行不了的

第三层-存储引擎：可以认为是不同数据在存储时的不同形式

连接器

频繁产生对接、断开，不可能每次执行一次SQL就连接断开，于是出现连接池，可以帮助保持一段时间连接

druid：提供了监控的功能，springboot里面默认集成了hekarui连接池



MySQL存储引擎_数据库_02


补充知识：

  • Id: 就是这个线程的唯一标识，当我们发现这个线程有问题的时候，可以通过 kill 命令，加上这个Id值将这个线程杀掉。前面我们说了show processlist 显示的信息时来自information_schema.processlist 表，所以这个Id就是这个表的主键。
  • User: 就是指启动这个线程的用户。
  • Host: 记录了发送请求的客户端的 IP 和 端口号。通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求。
  • DB: 当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。
  • Command: 是指此刻该线程正在执行的命令。这个很复杂，下面单独解释
  • Time: 表示该线程处于当前状态的时间。
  • State: 线程的状态，和 Command 对应，下面单独解释。
  • Info: 一般记录的是线程执行的语句。默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist。

分析器

SQL就是一条字符串，只不过mysql拿到之后做了一些处理，把where、from等关键字拿出来之后截取对应的字符串，验证有没有问题（词法分析）

执行的过程就是语法分析，这个语法分析最后会变成AST（语法树）

优化器

mysql调优、jvm调优等等，比方说已经写好了一个AST树，有N种解析方式，在mysql定义了很多种方式去帮你更好的解析，CBO（基于成本的优化）、RBO（基于规则的优化），所以mysql其实已经帮助我们进行了优化，只不过我们不知道而已

优化器自动优化，通过“explain select STRATGHT_JOIN”我们就可以查看SQL的执行方式



MySQL存储引擎_客户端_03


执行器

当优化器优化好sql之后，选择好路之后（会选择一个具体的执行过程），然后执行器根据选择执行过程之后去存储引擎取数据或者过滤数据，在返回给客户端

存储引擎

存储引擎是可插拔式的，有很多种，想用哪个就用哪个？

查看MySQL支持的存储引擎

使用show engines；可以查看当前mysql所支持的存储引擎



MySQL存储引擎_客户端_04


  • Engine存储引擎的名称。
  • Support服务器对存储引擎的支持级别，如下表所示。
  • Comment存储引擎的简要说明。
  • Transactions存储引擎是否支持事务。
  • XA存储引擎是否支持XA事务。
  • Savepoints存储引擎是否支持保存点。

指定存储引擎

不同的存储引擎它的特性也不一样。从图上可以发现InnoDB是默认的一种存储引擎。在5.5版本之前默认采用MYISAM存储引擎，在5.5开始采用InnoDB存储引擎。所以我们在创建的时候不指定就是默认的



MySQL存储引擎_mysql_05


创建时也可以指定存储引擎

- 指定其他存储引擎
CREATE TABLE admin(
id int,
userid int,
score int
) ENGINE = MyISAM;

对于innodb到底有什么作用？

1、存储（磁盘）

2、检索（将数据存到哪个文件？这个文件怎么组织的？存储的部分怎么检索到内存中，因为我们只有在内存中才能做CRUD。



MySQL存储引擎_客户端_06


看文件夹数据存储的两种形式

默认是innodb存储引擎，生成.frm和.ibd文件



MySQL存储引擎_存储引擎_07


我们讲存储引擎是为了更好的辅助我们之后学习索引、锁、事务更好的去理解他们

那如何查看表的一个存储引擎呢？有两种方式（展示navicat）

那如何查看表的一个存储引擎呢？有两种方式

方法一：

show table STATUS（展示数据库所有表的定义信息）

我们创建表的话会自动生成对应的.frm和.ibd文件，.frm放了定义信息



MySQL存储引擎_mysql_08


一般能存多少数量其实适合你的字段数量有关系

方法二：

SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'dlm_share_test' AND TABLE_NAME = 'user_info';