MySQL数据库 server 引擎层结构图 mysql存储引擎层和服务器层

转载

网络安全战士 2024-01-04 11:22:25

文章标签 mysql 存储引擎主键 MySQL 文章分类 MySQL 数据库

MySQL

一、MySQL架构

MySQL数据库 server 引擎层结构图 mysql存储引擎层和服务器层_存储引擎

连接层：最上层是一些客户端和连接服务。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

服务层：第二层服务层，主要完成大部分的核心服务功能，包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等

引擎层：第三层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取

存储层：第四层为数据存储层，主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互

二、存储引擎

2.1.存储引擎简介

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

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

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

数据库操作存储引擎相关的sql

-- 查看支持的存储引擎
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;

2.2存储引擎类别

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

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

2.2.1不同存储引擎的文件存储结构

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

MySQL数据库 server 引擎层结构图 mysql存储引擎层和服务器层_存储引擎_02

查看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文件（或多个，可自己配置）

三、数据类型

主要包括以下五大类：

整数类型：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等

MySQL数据库 server 引擎层结构图 mysql存储引擎层和服务器层_存储引擎_03

MySQL数据库 server 引擎层结构图 mysql存储引擎层和服务器层_存储引擎_04

MySQL数据库 server 引擎层结构图 mysql存储引擎层和服务器层_主键_05

几个简单的问题

1.MySQL 的查询流程具体是？或者说一条SQL语句在MySQL中如何执行的？

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

MySQL数据库 server 引擎层结构图 mysql存储引擎层和服务器层_主键_06

感觉下面这个图画的更好一点

MySQL数据库 server 引擎层结构图 mysql存储引擎层和服务器层_mysql_07

update的，图里涉及到的Buffer Pool等内容将在后面详细讨论。

MySQL数据库 server 引擎层结构图 mysql存储引擎层和服务器层_mysql_08

来源：https://mp.weixin.qq.com/s/b7Qnzh1EIM4wbExwmIkJyA

2.InnoDB与MyISAM的区别，以及如何选择

答：主要区别如下：

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

引擎的选择：

如果存在并发的增删改操作，选择InnoDB引擎，支持事务，能够做到并发安全。

如果只是单纯的查询操作，选择MyISAM引擎，不支持事务，不需要向InnoDB那样，基于MVCC的判断而带来的效率开销。

可参考：

特性	MyISAM	InnoDB	MEMORY
存储限制	有(平台对文件系统的限制)	64TB	有(平台的内存限制)
事务安全	不支持	支持	不支持
锁机制	表锁	表锁/行锁	表锁
B+Tree索引	支持	支持	支持
哈希索引	不支持	不支持	支持
全文索引	支持	支持	不支持
集群索引	不支持	支持	不支持
数据索引	不支持	支持	支持
数据缓存	不支持	支持	N/A
索引缓存	支持	支持	N/A
数据可压缩	支持	不支持	不支持
空间使用	低	高	N/A
内存使用	低	高	中等
批量插入速度	高	低	高
外键	不支持	支持	不支持

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

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

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

自增锁模式设置参考：MySQL自增锁模式

唯一键冲突和事务回滚时自增主键+1后都不会恢复，会造成主键不连续

设置和查看当前表的自增主键值

-- 查看自增主键值
SELECT
AUTO_INCREMENT
FROM
INFORMATION_SCHEMA. TABLES
WHERE
TABLE_NAME = 'key_test'

-- 设置自增主键值
ALTER TABLE key_test  AUTO_INCREMENT=11000;

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

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

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

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

当然，如果有where条件的话， MyISAM 引擎和InnoDB引擎一样需要扫描表。

5.CHAR 和 VARCHAR 的区别？

主要就是抓住一个固定长度一个变长展开。

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

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

相同点：

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

不同点：

char不论实际存储的字符数都会占用n个字符的空间（会造成磁盘空间的浪费），而varchar会占用实际字符应该占用的字节空间加1（实际长度length，0<=length<255）或加2（length>255）。因为varchar保存数据时除了要保存字符串之外还会加一个字节来记录长度（如果列声明长度大于255则使用两个字节来保存长度）。
能存储的最大空间限制不一样：char的存储上限为255字节，varchar是65535个字节
char在存储时会截断尾部的空格，而varchar不会（跟版本有关系）。
varchar由于是变长的，当数据发生更新时可能会引发页分裂等。

项目中的选择建议：

虽然VARCHAR数据类型可以节省存储空间，提高数据处理的效率。但是其可变长度带来的一些负面效应，有时候会抵消其带来的优势。为此在某些情况下，还是需要使用CHAR数据类型。

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

二是考虑其长度的是否相近。如果某个字段其长度虽然比较长，但是其长度总是近似的，如一般在90个到100个字符之间，甚至是相同的长度。如果最大长度的值比平均长度大很多，建议使用VARCHAR。

三是从碎片角度进行考虑。CHAR字符型时，由于存储空间都是一次性分配的。为此某个字段的内容，其都是存储在一起的。而VARCHAR其存储的长度是可变的。当其更改前后数据长度不一致时，就不可避免的会出现碎片的问题。

四是即使使用VARCHAR数据类型，也不能够太过于慷慨。VARCHAR(100)与VARCHAR(200)用来存储90个字符的数据，其存储空间相同。但是对于内存的消耗是不同的。对于VARCHAR数据类型来说，硬盘上的存储空间虽然都是根据实际字符长度来分配存储空间的，但是对于内存来说，是使用固定大小的内存块来保存值。简单的说，就是使用字符类型中定义的长度，即200个字符空间。显然，这对于排序或者临时表(这些内容都需要通过内存来实现)作业会产生比较大的不利影响。所以如果某些字段会涉及到文件排序或者基于磁盘的临时表时，分配VARCHAR数据类型时仍然不能够太过于慷慨。还是要评估实际需要的长度，然后选择一个最长的字段来设置字符长度。如果为了考虑冗余，可以留10%左右的字符长度。

参考：CHAR与VARCHAR之争