MySQL存储引擎：全面解析

MySQL体系结构

MySQL服务器的架构可以分为四个主要层次：

连接层

负责处理客户端的连接请求，完成身份验证、授权以及安全连接（如SSL）等操作。

服务层

包括SQL接口、查询分析器、优化器等核心组件，负责解析和优化SQL语句，执行查询并管理缓存。

存储引擎层

MySQL的存储引擎是插件式的，支持多种存储引擎（如InnoDB、MyISAM等），每个引擎负责数据的存储和提取。

存储层

数据的存储依赖于文件系统，包含数据文件、索引文件等。

存储引擎概述

MySQL的存储引擎是数据库的核心，决定了数据的存储方式和性能特点。与其他数据库不同，MySQL支持插件式存储引擎，允许根据需求选择合适的引擎或自定义引擎。

主要特点

插件式架构
MySQL支持多种存储引擎，如InnoDB、MyISAM、MEMORY等，可以根据具体需求选择。

事务支持
只有InnoDB存储引擎支持事务安全，提供事务的提交、回滚和崩溃恢复能力。

外键支持
InnoDB是唯一支持外键约束的存储引擎，其他引擎如MyISAM不支持外键。

存储引擎对比

1. InnoDB

特点
- 支持事务安全（ACID）。
- 行锁机制，适合高并发环境。
- 支持外键约束。
- 数据和索引存储在不同的文件中。

优点
适合需要事务支持和外键约束的复杂应用。

缺点
写操作效率较低，占用更多磁盘空间。

2. MyISAM

特点
- 不支持事务安全。
- 表锁机制，适合单线程写入的场景。
- 数据和索引存储在同一文件中。

优点
读操作和插入操作速度快。

缺点
不支持事务和外键约束，适合简单的数据处理。

3. MEMORY

特点
数据存储在内存中，访问速度极快。

优点
适合小数据表和临时表。

缺点
数据丢失于服务中止，恢复困难。

4. MERGE

特点
由多个MyISAM表组成，支持逻辑合并。

优点
适合数据仓储和大数据存储。

缺点
管理复杂，插入操作受限。

存储引擎选择

适用场景

事务处理

选择InnoDB，支持事务安全和外键约束。

高并发读写

MyISAM适合读操作和插入操作为主的场景。

临时数据

使用MEMORY存储在内存中，提升访问速度。

大数据存储

使用MERGE表实现数据集群和分布式存储。

示例代码

创建InnoDB表

CREATE DATABASE demo_02 DEFAULT CHARSET=utf8mb4;CREATE TABLE goods_innodb (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(20) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入数据

INSERT INTO goods_innodb (id, name) VALUES (NULL, 'Meta21');

查看数据

SELECT * FROM goods_innodb;

外键约束示例

CREATE TABLE country_innodb (    country_id INT AUTO_INCREMENT PRIMARY KEY,    country_name VARCHAR(100) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;CREATE TABLE city_innodb (    city_id INT AUTO_INCREMENT PRIMARY KEY,    city_name VARCHAR(50) NOT NULL,    country_id INT NOT NULL,    FOREIGN KEY (country_id) REFERENCES country_innodb (country_id) ON DELETE RESTRICT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO country_innodb VALUES (1, 'China'), (2, 'America');INSERT INTO city_innodb VALUES (1, 'Xian', 1), (2, 'New York', 2);