开启成长之旅！这是我参与「日新计划 12 月更文应战」的第一天，点击检查活动概况

大家好，我是Zhan，一名个人操练时长一年半的大二后台操练生，最近在学MySQL高级篇，欢迎各路大佬一起交流讨论

本篇速览

早在MySQL基础篇的学习中，咱们就一向看到innodb这个存储引擎，可是好像关于其他的存储引擎也没有去学习和了解，而innodb有何种特色也不得而知，而本篇将从一下四点，带你逐个攻破Ta：

  • 1️⃣首要体系地介绍一下MySQL数据库服务器的体系结构，在学习存储引擎之前，咱们需求了解存储引擎在MySQL数据库服务器中所占的位置和作用

  • 2️⃣再介绍一下MySQL数据库中的存储引擎，即MySQL有哪些存储引擎，以及咱们怎么去挑选存储引擎，此处的挑选是指设置存储引擎为某个存储引擎

  • 3️⃣介绍各个存储引擎的特色和差异，只有了解了存储引擎之间的差异以及各自的优势，咱们才能在实际环境下挑选最适宜的存储引擎

  • 4️⃣第四个方面，解说各个存储引擎的使用场景，体系的总结各个存储引擎适宜的使用场景，方便后续挑选时的思路确定

1️⃣ MySQL数据库服务器的体系结构

以下MySQL数据库服务器的体系架构图，最上面的一层便是作为后端开发的我经常会运用到的客户端衔接器JDBC，当然，除了Java，Python,PHP,Ruby也能够成为客户端衔接器 而咱们首要关注的是客户端衔接器以下的，MySQL服务端的体系结构，即MySQL Server

首要第一层：衔接层。 它的作用是：接收客户端衔接，完结衔接的处理，认证授权，安全方案，检查是否超过最大衔接数…… 其间认证授权便是咱们在运用用户名和密码登陆的时分的进程，并赋予该用户对应的权限

再便是第二层：服务层。 核心功用基本上都在这一层完结：SQL接口、解析器、查询优化器、缓存，像创立存储进程、视图、触发器这些都在服务层完结

「MySQL高级篇」MySQL存储引擎
第三层：引擎层。 上图中一个个紫色的圆筒便是不同种类的存储引擎，其间第一个便是咱们常用的innodb，上图中是MySQL提供给咱们的存储引擎，而咱们自己也能够对存储引擎进行扩展 它被称为：可插拔式存储引擎，类似于接口，咱们需求就把它插上来，不需求就把它拔掉 存储引擎控制的是咱们的数据存储和拿取的办法

这儿还有一点要注意的是：索引是在存储引擎这一层决定的

最终一层：存储层。 引擎层仅仅决定了咱们怎么去存取数据，而数据实际上是存储在磁盘中的，同时，数据库的日志也会存储在磁盘当中

2️⃣ MySQL数据库中的存储引擎

尽管前面介绍了这么多，可是什么是存储引擎呢？咱们在操作数据库的时分怎么去指定存储引擎也是一个问题。

存储引擎

存储引擎便是存储数据、建立索引、更新/查询数据等技能的实现办法。存储引擎是基于表的，也便是说一张表运用一个 存储引擎，数据库中的不同表的存储引擎不一定相同，存储引擎因而能够被称为表类型。

在之前MySQL基础篇的时分，咱们是没有指定存储引擎的，可是在下面导出创立表SQL句子的时分，咱们会发现它的存储引擎是innodb

「MySQL高级篇」MySQL存储引擎
这是因为在MySQL5.5今后，数据库表的默许存储引擎是innodb

指定存储引擎

其实有了上述导出的那条创表的SQL句子，咱们其实不难得出指定存储引擎的办法，也便是：

create table `table_name`(
	`字段1` 字段1类型 [COMMENT 补白],
	...,
	`字段n` 字段n类型 [COMMENT 补白]
) ENGINE=引擎类型 [COMMENT 补白]

可是咱们怎么知道当时数据库版别支撑哪些存储引擎呢，这就需求履行SHOW ENGINES

「MySQL高级篇」MySQL存储引擎
经过这张表咱们能得到的信息：

  • Support字段也便是当时MySQL版别是否支撑
    • 咱们能够得到的信息有：
    • 支撑的有：MEMORY,MRG_MYISAM,CSV,PERFORMANCE_SCHEMA,MYISAM,InnoDB,BLACKHOLE,ARCHIVE
    • 不支撑的有：FEDERATED
    • 默许的存储引擎为InnoDB
  • 补充信息：MyIsAM是以前版别的默许存储引擎

3️⃣各个存储引擎的特色和差异

这儿首要对这三个首要运用的存储引擎InnoDB,MYISAM,MEMORY进行介绍

⚙InnoDB

它的特色能够用数据库中查询出来的COMMENT中的三点来概括：

  • Supports transactions:支撑业务。在运用InnoDB作为存储引擎的时分，进行DML操作的时分遵从ACID模型，支撑业务
    • DML：关于数据的增删改操作
    • ACID：原子性(atomicity)、一致性(consistency)、阻隔性(isolation)、持久性(durablility)
  • row-level locking：行级别锁。提高并发拜访的性能。
  • foreign keys：支撑外键约束，确保数据的完好性和正确性

除此之外还要谈谈InnoDB存储数据的文件

  • 它存储数据库文件的类型为xxx.ibd，其间xxx表明表名，也便是说每一个运用InnoDB存储引擎的表都会对应一个ibd文件，存储该表的表结构(frm,sdi)、数据和索引
  • 其间表结构文件，前期的时分有一个文件叫frm，表结构都存储在这个里面，可是后来在MySQL8.0,今后，表结构都存储在了sdi文件中，而sdi又融入到了ibd文件中。
  • 综上所述，一张表对应一个ibd文件，好像说了半天的废话hhh
  • 同时，咱们能够经过装备把多张表放进一个ibd文件中（体系默许是一张表对应一个ibd文件）

说了这么多ibd文件，咱们不妨去找找ibd文件的位置，咱们能够在Data中找到对应的ibd文件：

「MySQL高级篇」MySQL存储引擎
而该文件是二进制编码，咱们无法直接打开，能够经过命令ibd2sdi，从ibd文件中提取sdi的表结构数据

「MySQL高级篇」MySQL存储引擎

最终再简略介绍一下InnoDB的逻辑存储结构：

「MySQL高级篇」MySQL存储引擎
其实便是一层一层的包括关系：表空间包括多个段，一个段又包括多个区，一个区的固定巨细为1M，最多能包括64页，因为一页的固定巨细为16K，一页能包括多列，一列便是咱们数据库中的一条数据，当然除了记载数据以外还有Trx_id业务id，Roll_pointer指针。

此处仅仅做简略介绍，后续在SQL优化的时分会具体解说。

⚔MyIsAM

MyISAM是MySQL前期默许的存储引擎，说它的特色咱们能够与现在的默许存储引擎InnoDB进行比照：

  • MyISAM 不支撑业务，也不支撑外键
  • MyISAM 不支撑行锁，可是支撑表锁
  • 因为不确保它的业务，因而它的拜访速度更快

而MyISAM涉及到的存储文件的类型有三种：MYD,MYI,SDI

「MySQL高级篇」MySQL存储引擎

  • 其间sdi与上面的结构一样，用于存储表结构
  • MYD寄存数据库中的数据
  • MYI寄存数据库中的索引

MEMORY

MEMORY 最大的特色便是，它的表数据是存储在内存中的，因而容易受宕机、断电……的影响，导致数据丢掉，因而一般 MEMORY 存储引擎用于暂时表和缓存 它的特色因为和前面两种存储引擎类型不同，不做比较：

  • 它因为是内存寄存，因而拜访速度很快
  • 支撑Hash索引，后续在讲索引的时分会具体讲到

而说到存储它的文件类型，就只有sdi一种类型，因为数据都寄存在内存而不是磁盘中，因而不需更多的文件类型去寄存数据，只需求一个sdi文件去记载表结构

总结

特色 InnoDB MyISAM MEMORY
存储约束 64TB 有 有
业务安全 支撑 – –
锁机制 行锁 表锁 表锁
B+tree索引 支撑 支撑 支撑
Hash索引 – – 支撑
全文索引 支撑（5.6今后） 支撑 –
空间运用 高 低 N/A
内存运用 高 低 中等
批量刺进速度 低 高 高
支撑外键 支撑 – –

4️⃣ 存储引擎的挑选

其实不难发现，学到现在，各种存储引擎都没有所谓的凹凸之分，没有说哪个最好，只能说依据使用体系的特色去挑选适宜的存储引擎，不一定全部选InnoDB，关于复杂的使用体系，咱们能够挑选多种存储引擎组合运用，以到达最好的效果

  1. InnoDB：如果关于业务的完好性有比较高的要求，在并发条件下要求数据的一致性，挑选InnoDB是不错的挑选
  2. MyISAM：如果数据以读和刺进为主，更新和删去很少，关于业务的完好性、并发性要求不高，那么这个存储引擎更为适宜。最开始我是没有想到有什么场景不需求完好性的，可是其实电商渠道的浏览记载就很符合这个特色，刺进和查询居多，而且关于业务的完好性要求不高，少那么一两条其实也问题不大
  3. MEMORY：一般用于暂时表和缓存，它的缺陷便是因为内存有限，因而表的巨细也有约束，同时，它无法确保数据的安全性。