mysql数据库存储结构 mysql的存储结构是什么

文章目录

• 一、MYSQL的体系结构

• 1、连接器
• 2、查询缓存
• 3、分析器（要做什么）
• 4、优化器（怎么做）
• 5、执行器
• 6、数据库引擎

• 1）mysql支持的引擎
• 2）常用的mysql引擎比较
• 3）索引组织表、堆组织表
• 4）内存临时表
• 5）不同的引擎对于自增值的保存策略
• 6）自增值不连续的场景：

一、MYSQL的体系结构

MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。从 MySQL 5.5.5 版本开始InnoDB成为了默认存储引擎。同存储引擎的表数据存取方式不同，支持的功能也不同。

1、连接器

1）连接完成后，如果你没有后续的动作，这个连接就处于空闲状态：

show PROCESSLIST;执行命令查看连接，sleep表示空闲链接

MySQL服务器所支持的最大连接数是有上限的，因为每个连接的建立都会消耗内存，因此我们希望客户端在连接到My SQL Server 处理完相应的操作后，应该断开连接并释放占用的内存。

2）客户端如果太长时间没动静，连接器就会自动将它断开【数据库连接器默认8小时断开链接】。
interactive_timeout：服务器关闭交互式连接前等待活动的秒数。（eg:客户端连接交互式连接）
wait_timeout:服务器关闭非交互连接之前等待活动的秒数。（eg:jdbc连接）

show VARIABLES like '%timeout%'//执行命令查看两个参数

数据库连接器默认8小时断开链接

• 当只改wait_timeout 是不能改interactive_timeout的，wait_timeout是修改session变量，是不起作用的，当关闭此次会话，退出mysql ，重新登录发现还是原来的值：
• 但若只改interactive_timeout，wait_timeout也会跟着改。所以修改global的interactive_timeout参数，可以真正调整超时时间。

Windows下在%MySQL HOME%/bin下有mysql.ini配置文件，需修改配置文件。

linux的服务器上的mysql:
set global interactive_timeout=31536000;
show VARIABLES like ‘%timeout%’;

3）用来控制mysql实例同时存在的连接数上线

SHOW variables like '%max_connections%'

2、查询缓存

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空，可以通过query_cache_type设置 。MySQL 8.0 版本直接将查询缓存的整块功能删掉了。

3、分析器（要做什么）

分析器会做“词法分析”，“语法分析”
eg:select写错时

mysql> elect * from t where ID=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘elect * from t where ID=1’ at line 1

4、优化器（怎么做）

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

5、执行器

先判断一下你对这个表 T 有没有执行权限，如果没有，就会返回没有权限的错误
根据表的引擎定义去使用引擎提供的接口方法。比如触发器，只有执行阶段才能判断有无权限，不只是表面表权限的检查。

6、数据库引擎

存储引擎的概念是MySQL的特点，Oracle中没有专门的存储引擎的概念，Oracle有OLTP（OnLine Transaction Processsing 联机事务处理, OLAP（OnLine Analytical Processing联机分析处理.

1）mysql支持的引擎

show ENGINES; 查看mysql支持的引擎

SHOW variables like '%engine%'查看数据库默认引擎;

2）常用的mysql引擎比较

特点	MyISAM	InnoDB	BDB	Memory	Archive
批量插入的速度	高	低	高	高	非常高
事务安全		支持	支持
全文索引	支持
锁机制	表锁	行锁	页锁	表锁	行锁
存储限制	没有	64TB	没有	有	没有
B树索引	支持	支持	支持	支持
哈希索引		支持		支持
集群索引		支持
数据缓存		支持		支持
索引缓存	支持	支持		支持
数据可压缩	支持				支持
空间使用	低	高	低	中等	低
支持外键		支持

（1）InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制,并支持类似 Oracle 的非锁定读,即默认读取操作不会产生锁。支持行锁。 从 MySQL 5.5.8 版本开始是默认的存储引擎。InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准的4种隔离级别,默认为 REPEATABLE 级别,同时使用一种称为 netx-key locking 的策略来避免幻读(phantom)现象的产生。除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。
（2）MyISAM：支持表锁。插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。MyISAM 存储引擎的另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与大多数的数据库都不相同。
（3）MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。
同一个数据库也可以使用多种存储引擎的表，不同引擎的表可以关联查询。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。
正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。
（4）NDB 存储引擎
　　NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。
（5）Infobright 存储引擎
第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。
（6）NTSE 存储引擎
网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。
（7）BLACKHOLE
黑洞存储引擎，可以应用于主备复制中的分发主库。
MySQL 数据库还有很多其他存储引擎,上述只是列举了最为常用的一些引擎。如果 你喜欢,完全可以编写专属于自己的引擎,这就是开源赋予我们的能力,也是开源的魅 力所在。

3）索引组织表、堆组织表

【补充】InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）。
而 Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。

4）内存临时表

内存临时表刚好可以无视内存表的两个不足，主要是下面的三个原因：

• 临时表不会被其他线程访问，没有并发性的问题；
• 临时表重启后也是需要删除的，清空数据这个问题不存在；
• 备库的临时表也不会影响主库的用户线程。

Memory 引擎的几个特性。可以看到，由于重启会丢数据，如果一个备库重启，会导致主备同步线程停止；如果主库跟这个备库是双 M 架构，还可能导致主库的内存表数据被删掉。因此，在生产上，我不建议你使用普通内存表。如果你是 DBA，可以在建表的审核系统中增加这类规则，要求业务改用 InnoDB 表。

从中我们可以看出，这两个引擎的一些典型不同:

• 1.InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；
• 2.当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；
• 3.数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引；
• 4.InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的；
• 5.InnoDB 支持变长数据类型，不同记录的长度可能不同; 内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

5）不同的引擎对于自增值的保存策略

MyISAM 引擎的自增值保存在数据文件中。
InnoDB具体情况是：在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。MySQL 8.0 版本后，才有了“自增值持久化”的能力，保存在了内存里。

6）自增值不连续的场景：

（1）唯一键冲突是导致自增主键 id 不连续的第一种原因。
（2）同样地，事务回滚也会产生类似的现象，这就是第二种原因。
（3）对于批量插入数据的语句，MySQL 有一个批量申请自增 id 的策略：语句执行过程中，第一次申请自增 id，会分配 1 个；1 个用完以后，这个语句第二次申请自增 id，会分配 2 个；2 个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个；依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。这就是第三种原因。