MySQL 存储引擎

一，存储引擎概述

1：什么是存储引擎

存储引擎是数据库管理系统中负责数据存储和检索的部分。在关系型数据库系统中，存储引擎定义了数据如何被物理地存储、索引以及如何执行事务。不同的存储引擎提供不同的功能集，例如支持事务处理、行级锁、全文搜索等。

2：MySQL 5.7 支持的存储引擎

MySQL 5.7 支持多种存储引擎，其中一些主要的包括：

• InnoDB：支持事务处理、行级锁和外键约束。这是 MySQL 5.7 中的默认存储引擎。
• MyISAM：不支持事务处理，但提供了较快的读取速度。在 MySQL 5.7 中主要用于某些特定场景。
• MEMORY (HEAP)：将表存储在内存中，适用于需要快速访问的小型临时表。
• ARCHIVE：用于压缩只读数据，占用磁盘空间较小。
• CSV：将数据以逗号分隔的格式存储在文件中，适用于简单的导入导出任务。
• FEDERATED：允许通过其他 MySQL 服务器访问表，可以用来连接外部数据源。

3：如何选择 MySQL 存储引擎

选择 MySQL 存储引擎时应考虑以下几个因素：

• 事务处理需求：如果应用程序需要支持事务，则应该使用 InnoDB。
• 并发控制：InnoDB 支持行级锁，适用于高并发的应用场景；而 MyISAM 使用表级锁，可能会导致等待。
• 索引类型：InnoDB 支持 B-tree 和哈希索引，而 MyISAM 支持 B-tree 和全文索引。
• 数据完整性：如果需要外键约束和事务回滚功能，InnoDB 是更好的选择。
• 性能需求：对于读多写少的应用，MyISAM 可能会提供更快的速度；而对于写操作频繁的应用，InnoDB 更适合。
• 存储需求：如果需要压缩数据以节省空间，可以选择 ARCHIVE 或者使用 InnoDB 的压缩特性。

4：可以根据以下的原则来选择 MySQL 存储引擎

• 大多数情况下：InnoDB 是最安全的选择，因为它提供了完整的事务支持，并且是默认存储引擎。
• 读密集型应用：如果应用主要是读取操作，可以考虑使用 MyISAM。
• 内存中的数据：对于需要高速访问的数据，可以使用 MEMORY 存储引擎。
• 历史记录或日志数据：对于不需要修改的历史数据，ARCHIVE 引擎是一个好的选择。
• 跨服务器查询：如果需要从其他服务器获取数据，FEDERATED 可以满足需求。

5：MySQL 默认存储引擎

InnoDB 是系统的默认引擎，支持可靠的事务处理。
使用下面的语句可以修改数据库临时的默认存储引擎

SET default_storage_engine=< 存储引擎名 >

二，操作存储引擎

1：查看mysql支持的存储引擎

要查看 MySQL 支持的存储引擎及其特性，可以使用 SHOW ENGINES 命令。

MariaDB [(none)]> show engines;

以下是 MySQL 5.7 中支持的存储引擎列表及其相关信息：

Engine	Support	Comment	Transactions	XA	Savepoints
InnoDB	DEFAULT	Percona-XtraDB, Supports transactions, row-level locking, and foreign keys	YES	YES	YES
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
MyISAM	YES	Non-transactional engine with good performance and small data footprint	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
CSV	YES	Stores tables as CSV files	NO	NO	NO
ARCHIVE	YES	Gzip-compresses tables for a low storage footprint	NO	NO	NO
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
FEDERATED	YES	Allows to access tables on other MariaDB servers, supports transactions and more	YES	NO	YES
Aria	YES	Crash-safe tables with MyISAM heritage	NO	NO	NO

2：MyISAM 存储引擎

MyISAM 存储引擎不支持事务处理和外键，但具有较快的读取速度，适用于不需要事务完整性的应用。每个 MyISAM 表在磁盘上存储为以下三种文件：

• frm: 存储表定义
• MYD: 存储数据
• MYI: 存储索引

MyISAM 支持三种存储格式：

• 静态表 (固定长度)
• 动态表
• 压缩表

3：lnnoDB 存储引擎

InnoDB 是一个事务型存储引擎，支持事务安全，行级锁定和外键约束。MySQL 从 5.5.5 版本开始，默认的存储引擎即为 InnoDB。InnoDB 表的数据文件以 .ibd 扩展名存储，表定义以 .frm 扩展名存储。

InnoDB 存储引擎适合以下场景：

• 更新密集型表
• 事务处理
• 自动灾难恢复
• 外键约束

4：关于 MyISAM 与 innoDB 选择使用

• 事务需求: 如果应用程序需要使用事务处理，应选择 InnoDB。需要注意的是，InnoDB 的行级锁在 WHERE 子句未使用主键时会退化为表级锁。
• 查询性能: 如果应用程序对查询性能要求较高，可以选择 MyISAM。由于 MyISAM 索引和数据分离并且索引被压缩，因此查询性能通常优于 InnoDB。

5：修改默认的存储引擎

（1）：创建表，并查看默认用的存储引擎

CREATE TABLE t1 (user_name CHAR(16), user_passwd CHAR(48));
SHOW TABLE STATUS FROM <database_name> WHERE Name = 't1' \G;

（2）：通过 alter table 修改

CREATE TABLE t1 (user_name CHAR(16), user_passwd CHAR(48));
SHOW TABLE STATUS FROM <database_name> WHERE Name = 't1' \G;

（3）：通过配置文件修改

• 在 /etc/my.cnf 中添加以下行：

[mysqld]
default-storage-engine=MyISAM

• 重启 MySQL 服务：

systemctl restart mysqld

• 登录 MySQL 并创建一张新表检查默认引擎：

USE <database_name>;
CREATE TABLE t2 (user_name CHAR(16), user_passwd CHAR(48));
SHOW TABLE STATUS FROM <database_name> WHERE Name = 't2' \G;

（4）：通过 create table 创建表时指定存储引擎

CREATE TABLE t3 (id INT(10), name CHAR(20)) ENGINE = INNODB;
SHOW TABLE STATUS FROM <database_name> WHERE Name = 't3' \G;

（5）：临时修改默认的存储引擎

SET default_storage_engine=<engine_name>;

三，MyISam 和 lnnoDB 实例比较

1：创建两张表分别以Mylsan 和 lnnoDB 作为存储引擎

首先，我们需要创建一个测试数据库，并在其中创建两张表，一张使用 myisam 存储引擎，另一张使用 innodb 存储引擎。

create database test;
use test;

create table tm (
  id int(20) primary key auto_increment,
  name char(30)
) engine=myisam;

create table ti (
  id int(20) primary key auto_increment,
  name char(30)
) engine=innodb;

验证表创建情况：

show create table tm\G
show create table ti\G

2：插入一个一千万数据，来比较两个存储引擎的存储效率

（1）：设置 sql 语句结束符

delimiter $

（2）：创建两个存储过程

create procedure insertm()
begin
  declare @i int default 1;
  while @i <= 10000000 do
    insert into tm(name) values(concat('wy', @i));
    set @i = @i + 1;
  end while;
end$

create procedure inserti()
begin
  declare @i int default 1;
  while @i <= 10000000 do
    insert into ti(name) values(concat('wy', @i));
    set @i = @i + 1;
  end while;
end$

（3）：利用存储过程向两个表添加数据

delimiter ;

call insertm();
call inserti();

3：查询数据总数目

查询每张表中的记录总数，以验证数据插入是否成功。

select count(*) from tm;
select count(*) from ti;

4：查询某一范围的数据

（1）：没有索引的列

explain select * from tm where name between 'wy1000000' and 'wy2000000';
explain select * from ti where name between 'wy1000000' and 'wy2000000';

（2）：有索引的列

为了比较索引的效果，我们先为两个表的 name 列创建索引。

alter table tm add index idx_name (name);
alter table ti add index idx_name (name);

然后进行有索引的查询。

explain select * from tm where name between 'wy1000000' and 'wy2000000';
explain select * from ti where name between 'wy1000000' and 'wy2000000';

比较结果

数据插入性能

• myisam: 插入一千万条数据耗时大约 1 分 49.74 秒。
• innodb: 插入相同数量的数据耗时大约 13 分 32.96 秒。

结论: 在大规模数据插入方面，myisam 显示出更好的性能。

查询性能

• 数据总数目: innodb 在查询数据总数时利用索引，而 myisam 则优化了表。
• 无索引查询: myisam 在无索引的查询中表现出更快的速度。
• 有索引查询: innodb 在有索引的查询中表现更优。

结论:

• 对于读取密集型的应用程序，尤其是不需要事务处理的情况，myisam 可能更适合。
• 对于需要事务处理、行级锁定和外键约束的应用程序，innodb 是更好的选择。