MySQL 进阶（一）【存储引擎】

最新推荐文章于 2024-07-22 14:36:59 发布
最新推荐文章于 2024-07-22 14:36:59 发布
阅读量616 收藏 14
点赞数 25
分类专栏： 数据库 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_64261982/article/details/140152982
版权

1、存储引擎

1.1、MySQL 体系结构

 

 自上而下可以分为

  • 连接层
    • 接受客户端的连接，完成连接的处理、认证授权、安全方案和最大连接数等
  • 服务层
    • 绝大部分的核心功能都是在服务层完成的，比如SQL 接口（DDL、DML、视图、触发器、存储过程）、解析器、查询优化器和缓存等
  • 存储引擎层
    • 存储引擎控制着数据的存储和提取的方式，服务器通过 API 和存储引擎通信
    • 注意：索引是在存储引擎层实现的，不同的存储引擎索引的结构是不同的
    • InnoDB 是 MySQL 5.5 之后默认的存储引擎
  • 存储层
    • 真正存储数据的地方，除了数据还有各种日志

1.2、存储引擎简介

        存储引擎是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可以被称为表类型。

1.2.1、在创建表时，指定存储引擎

CREATE TABLE xx (

)ENGINE=INNODB;

1.2.2、查看当前数据库支持的存储引擎

SHOW ENGINGS;

1.3、存储引擎特点

1.3.1、InnoDB

1）介绍

        InnoDB 是 MySQL 中一种兼顾高可靠性和高性能的通用存储引擎，MySQL 5.5 之后，InnoDB 是默认的 MySQL 存储引擎。

2）特点
  • DML 操作（表数据的CRUD）遵循 ACID 模型，支持事务；
  • 行级锁，提升高并发访问性能；
  • 支持外键约束，保证数据的完整性和正确性；
3）文件
  • xxx.ibd：xxx代表表名，每一个 InnoDB 的表都会对应这样一个表空间文件，存储该表的表结构（8.0 之后存储在 sdi 这个数据字典当中）、数据和索引；
  • 参数：innodb_file_per_table（MySQL 8.0 中是 ON，也就是每张表都有一个 InnoDB 文件）

我们可以查看数据库表对应的 InnoDB 文件： 

可以通过指令 "ibd2sdi student.ibd" 来查看这个二进制文件： 

["ibd2sdi"
,
{
        "type": 1,
        "id": 713,
        "object":
                {
    "mysqld_version_id": 80030,
    "dd_version": 80023,
    "sdi_version": 80019,
    "dd_object_type": "Table",
    "dd_object": {
        "name": "student",
        "mysql_version_id": 80030,
        "created": 20240513090135,
        "last_altered": 20240513090135,
        "hidden": 1,
        "options": "avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
        "columns": [
            {
                "name": "id",
                "type": 4,
                "is_nullable": true,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 1,
                "ordinal_position": 1,
                "char_length": 11,
                "numeric_precision": 10,
                "numeric_scale": 0,
                "numeric_scale_null": false,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": true,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "interval_count=0;",
                "se_private_data": "physical_pos=3;table_id=1187;",
                "engine_attribute": "",
                "secondary_engine_attribute": "",
                "column_key": 1,
                "column_type_utf8": "int",
                "elements": [],
                "collation_id": 8,
                "is_explicit_collation": false
            },
            {
                "name": "name",
                "type": 16,
                "is_nullable": true,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 1,
                "ordinal_position": 2,
                "char_length": 24,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "numeric_scale_null": true,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": true,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "interval_count=0;",
                "se_private_data": "physical_pos=4;table_id=1187;",
                "engine_attribute": "",
                "secondary_engine_attribute": "",
                "column_key": 1,
                "column_type_utf8": "varchar(6)",
                "elements": [],
                "collation_id": 255,
                "is_explicit_collation": false
            },
            {
                "name": "sex",
                "type": 16,
                "is_nullable": true,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 1,
                "ordinal_position": 3,
                "char_length": 4,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "numeric_scale_null": true,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": true,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "interval_count=0;",
                "se_private_data": "physical_pos=5;table_id=1187;",
                "engine_attribute": "",
                "secondary_engine_attribute": "",
                "column_key": 1,
                "column_type_utf8": "varchar(1)",
                "elements": [],
                "collation_id": 255,
                "is_explicit_collation": false
            },
            {
                "name": "age",
                "type": 4,
                "is_nullable": true,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 1,
                "ordinal_position": 4,
                "char_length": 11,
                "numeric_precision": 10,
                "numeric_scale": 0,
                "numeric_scale_null": false,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": true,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "interval_count=0;",
                "se_private_data": "default_null=1;physical_pos=6;table_id=1187;version_added=1;",
                "engine_attribute": "",
                "secondary_engine_attribute": "",
                "column_key": 1,
                "column_type_utf8": "int",
                "elements": [],
                "collation_id": 255,
                "is_explicit_collation": false
            },
            {
                "name": "DB_ROW_ID",
                "type": 10,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 2,
                "ordinal_position": 5,
                "char_length": 6,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "numeric_scale_null": true,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": true,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "",
                "se_private_data": "physical_pos=0;table_id=1187;",
                "engine_attribute": "",
                "secondary_engine_attribute": "",
                "column_key": 1,
                "column_type_utf8": "",
                "elements": [],
                "collation_id": 63,
                "is_explicit_collation": false
            },
            {
                "name": "DB_TRX_ID",
                "type": 10,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 2,
                "ordinal_position": 6,
                "char_length": 6,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "numeric_scale_null": true,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": true,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "",
                "se_private_data": "physical_pos=1;table_id=1187;",
                "engine_attribute": "",
                "secondary_engine_attribute": "",
                "column_key": 1,
                "column_type_utf8": "",
                "elements": [],
                "collation_id": 63,
                "is_explicit_collation": false
            },
            {
                "name": "DB_ROLL_PTR",
                "type": 9,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 2,
                "ordinal_position": 7,
                "char_length": 7,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "numeric_scale_null": true,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": true,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "",
                "se_private_data": "physical_pos=2;table_id=1187;",
                "engine_attribute": "",
                "secondary_engine_attribute": "",
                "column_key": 1,
                "column_type_utf8": "",
                "elements": [],
                "collation_id": 63,
                "is_explicit_collation": false
            }
        ],
        "schema_ref": "test",
        "se_private_id": 1187,
        "engine": "InnoDB",
        "last_checked_for_upgrade_version_id": 0,
        "comment": "",
        "se_private_data": "",
        "engine_attribute": "",
        "secondary_engine_attribute": "",
        "row_format": 2,
        "partition_type": 0,
        "partition_expression": "",
        "partition_expression_utf8": "",
        "default_partitioning": 0,
        "subpartition_type": 0,
        "subpartition_expression": "",
        "subpartition_expression_utf8": "",
        "default_subpartitioning": 0,
        "indexes": [
            {
                "name": "PRIMARY",
                "hidden": true,
                "is_generated": false,
                "ordinal_position": 1,
                "comment": "",
                "options": "",
                "se_private_data": "id=344;root=4;space_id=126;table_id=1187;trx_id=38435;",
                "type": 2,
                "algorithm": 2,
                "is_algorithm_explicit": false,
                "is_visible": true,
                "engine": "InnoDB",
                "engine_attribute": "",
                "secondary_engine_attribute": "",
                "elements": [
                    {
                        "ordinal_position": 1,
                        "length": 4294967295,
                        "order": 2,
                        "hidden": true,
                        "column_opx": 4
                    },
                    {
                        "ordinal_position": 2,
                        "length": 4294967295,
                        "order": 2,
                        "hidden": true,
                        "column_opx": 5
                    },
                    {
                        "ordinal_position": 3,
                        "length": 4294967295,
                        "order": 2,
                        "hidden": true,
                        "column_opx": 6
                    },
                    {
                        "ordinal_position": 4,
                        "length": 4294967295,
                        "order": 2,
                        "hidden": true,
                        "column_opx": 0
                    },
                    {
                        "ordinal_position": 5,
                        "length": 4294967295,
                        "order": 2,
                        "hidden": true,
                        "column_opx": 1
                    },
                    {
                        "ordinal_position": 6,
                        "length": 4294967295,
                        "order": 2,
                        "hidden": true,
                        "column_opx": 2
                    },
                    {
                        "ordinal_position": 7,
                        "length": 4294967295,
                        "order": 2,
                        "hidden": true,
                        "column_opx": 3
                    }
                ],
                "tablespace_ref": "test/student"
            }
        ],
        "foreign_keys": [],
        "check_constraints": [],
        "partitions": [],
        "collation_id": 255
    }
}
}
,
{
        "type": 2,
        "id": 131,
        "object":
                {
    "mysqld_version_id": 80030,
    "dd_version": 80023,
    "sdi_version": 80019,
    "dd_object_type": "Tablespace",
    "dd_object": {
        "name": "test/student",
        "comment": "",
        "options": "autoextend_size=0;encryption=N;",
        "se_private_data": "flags=16417;id=126;server_version=80030;space_version=1;state=normal;",
        "engine": "InnoDB",
        "engine_attribute": "",
        "files": [
            {
                "ordinal_position": 1,
                "filename": ".\\test\\student.ibd",
                "se_private_data": "id=126;"
            }
        ]
    }
}
}
]

可以看到，它是 json 格式存储的，里面描述了这张表的字段、索引等各种信息。

4）逻辑存储结构

一个 ibd 文件就代表一个表空间

         在 InnoDB 的逻辑存储结构中，Page 是磁盘操作的最小单位，一个区就是 1 MB，一个 Page 16KB，一个区可以存放 64 页。

1.3.2、MyISAM

1）介绍

MyISAM 是 MySQL 早期默认的存储引擎；

2）特点 
  • 不支持事务和外键；
  • 支持表锁，不支持行锁；
  • 访问速度快；
3）文件
  • xxx.sdi：存储表结构信息
  • xxx.MYD：存储数据
  • xxx.MYI：存储索引

1.3.3、Memory

1）介绍

Memory 引擎的表数据存储在内存，所以这些表只能做临时表

2）特点
  • 数据存储在内存，访问速度快
  • hash 索引（默认）
3）文件
  • xxx.sdi：存储表结构信息

1.3.4、总结

让线程再跑一会
关注
  • 25
    点赞
  • 踩
  • 14
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
6.MySQL进阶 存储引擎.xmind
10-31
个人总结的MYSQL总结，希望对您有到帮助。
MySQL入门到精通-MySQL进阶篇
02-06
在“MySQL入门到精通-MySQL进阶篇”中，我们将深入探讨存储引擎、索引和SQL优化等关键概念。 首先，MySQL的体系结构分为四个层次：连接层、服务层、引擎层和存储层。连接层主要处理客户端的连接请求，包括认证和...
MySQL进阶——存储引擎
qq_47966193的博客
06-16 527
如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。，xxx代表的是表名, innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构(frm-早期的、sdi-新版的)、数据和索引。3)区 : 区是表空间的单元结构，每个区的大小为1M。
【MySQL进阶】存储引擎
Javascript_tsj的博客
12-10 2689
而我们在选择引擎的时候，需要在合适的场景，选择合适的存储引擎，就像在直升机上，我们不能选择舰载机的引擎一样。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
MySQL进阶之存储引擎和索引
风间琉璃の博客
06-01 265
1. 存储引擎 1.1 体系结构 MySQL体系结构主要分为四层： 连接层：完成连接处理、授权认证等相关的安全方案；服务器也会验证接入的客户端的操作权限； 服务层：完成大多数的核心服务功能，SQL接口、缓存的查询、SQL分析和优化、函数等。 引擎层：负责MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同存储引擎功能不同。 存储层：讲数据存储在文件系统上，完成和存储引擎之间的交互。 1.2 存储引擎简介 存储引擎就是存储数据、建立索引、更新/查询数据的实现方式。 存储引擎是基于表的
MySQL进阶-存储引擎
十二间记，知不足而奋斗，望远山而前行。千里不辞行路远，时光早晚到天涯。
11-01 151
介绍了MySQL存储引擎中的InnoDB、MyISAM、Memory。
MYSQL进阶——存储引擎
ME_Liao_2022的博客
11-21 78
MySql进阶-存储引擎
littlewhitevg的博客
04-24 260
文章目录1. 存储引擎概述2. 各种存储引擎特性3. InnoDB**事务控制****外键约束****存储方式**4. MyISAM不支持事务文件存储方式5. MEMORY6. MERGE7. 存储引擎的选择 1. 存储引擎概述 和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。 存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。...
【MySQL进阶】 存储引擎
行为产生结果
05-14 120
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式存储引擎是基于表的（不同表可以使用不同存储引擎，一个数据库下的多张表可以选择不同的存储引擎），而不是基于库的，所以存储引擎也被称为表类型。
【MYSQL进阶】存储引擎
weixin_66502306的博客
12-16 112
MYSQL体系结构，存储引擎介绍、特点及选择
MySQL进阶（存储引擎篇）
Paracosm629的博客
07-09 50
MySQL的存储引擎篇
Mysql进阶-存储引擎篇
weixin_64133130的博客
10-24 2385
存储引擎这个词相信大家是比较陌生，但引擎这个词,大家都是听过的，引擎就是发动机，是一个机器的核心组件。比如，对于舰载机、直升机、火箭来说，他们都有各自的引擎，是他们最为核心的组件。而我们在选择 引擎的时候，需要在合适的场景，选择合适的存储引擎，就像在直升机上，我们不能选择舰载机的引擎 一样。而对于存储引擎，也是一样，他是mysql数据库的核心，我们也需要在合适的场景选择合适的存储引擎。即根据业务表的需求来选择合适的存储引擎，注意存储引擎是基于表的。所以存储引擎也可被称为表类型。
mysql进阶知识整合
09-16
MySQL进阶知识整合涵盖了多个核心领域，包括事务管理、索引、SQL优化、视图、存储过程、锁、逻辑存储引擎和事务原理等。下面将逐一详细阐述这些知识点。 1. **事务**：事务是数据库操作的基本单位，确保一组操作的...
mysql进阶版笔记总结
11-10
MySQL是一种广泛使用的开源关系型数据库管理系统，其进阶特性使其在处理复杂业务场景时表现出色。以下是关于MySQL的一些核心知识点： 1. **复制与高可用性**：MySQL支持主从复制，这意味着数据可以从一个主服务器...
测试开发面试题---MySQL
最新发布
m0_53302824的博客
07-22 730
索引是数据库管理系统中用于提高数据检索速度的数据结构，它可以帮助快速定位数据不需要扫描全表。提高查询速度；唯一性约束，确保数据不会重复；辅助排序和分组；减少磁盘IO。
MySQL（事务、索引）&&MyBatis
hycccccch的博客
07-19 716
事务指的是一组操作的集合，是一个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败默认MySQL的事务是自动提交，即当执行一条DML语句时，MySQL会立即隐式的提交事务开启事务后运行事务不会对数据改变，commit后才会改变数据如果有命令发生错误，需要进行rollback进行回滚、
【MySQL】根据binlog日志获取回滚sql的一个开发思路
weixin_45385457的博客
07-19 467
不同客户端之间会交替追加在 binlog 中，需要通过 👆的 binlog 匹配流程来控制匹配。行，需要再次寻找 thread_id 相同的行，匹配到后执行上一个流程。在 binlog 中的线程 ID 记录为 thread_id。一个线程执行多个 sql 回滚到同一个文件可能带来的问题。对应 binlog 日志中的 thread_id=指定 mysql 客户端 开始时间和结束时间。打开 mysql 客户端 开始时间。关闭 mysql 客户端 结束时间。先匹配 thread_id 相同的。
mysql、oracle、db2数据库连接参数
卿本佳人的博客
07-18 378
mysql、oracle、db2数据库连接参数 参数/数据库 driver url Mysql com.mysql.jdbc.Driver 或 com.mysql.cj.jdbc.Driver jdbc:mysql://localhost:3306/数据库名 Oracle oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@localhost:1521:orcl //orcl为数据库SID DB2 com.ibm.db2.jcc.D
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

让线程再跑一会

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值