引言


存储引擎概述

  数据库存储引擎是数据库管理系统（DBMS）的核心组件，负责管理数据的存储、查询、更新和删除操作。不同的存储引擎提供不同的存储机制、索引技术、锁定水平等功能，以满足各种应用场景的需求。目前，许多DBMS都支持多种存储引擎，而MySQL则是其中的典型代表 。


MySQL 5.7支持的存储引擎

  MySQL 5.7版本支持多种存储引擎，每种引擎都有其特定的功能和特性，适用于不同类型的数据库操作任务。使用SHOW ENGINES语句可以查看MySQL支持的所有存储引擎及其默认设置。MySQL 5.7支持的主要存储引擎包括InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、和BLACKHOLE 。

如何选择MySQL存储引擎

选择合适的存储引擎需要根据具体的应用需求进行评估。以下是一些选择存储引擎的基本原则：

  • InnoDB：事务型数据库的首选引擎，支持事务安全（ACID），支持行级锁定和外键约束，适用于需要高并发控制和数据一致性的应用 。
  • MyISAM：适用于以读操作为主的应用，如Web和数据仓储应用。MyISAM提供高效的插入和查询性能，但不支持事务和外键 。
  • Memory：将表数据存储在内存中，适用于需要快速访问的数据，通常用于临时表和中间结果存储 。
  • Archive：适用于需要存储大量历史归档数据的场景，如日志记录。Archive引擎支持高并发的插入操作，但不支持事务 。

MySQL默认存储引擎

  MySQL 5.5.5版本之后，InnoDB成为系统的默认存储引擎。这意味着除非在创建表时显式指定了其他引擎，否则MySQL将默认使用InnoDB引擎来创建新表 。


MyISAM 和 InnoDB 介绍


MyISAM 存储引擎

  MyISAM 是 MySQL 中的一个存储引擎，不支持事务和外键，主要特点是访问速度快，适用于以 SELECT、INSERT 为主的应用。每个 MyISAM 表在磁盘上存储成三个文件，它们分别为：

  • .frm 文件：存储表定义。
  • .MYD 文件：存储数据。
  • .MYI 文件：存储索引。

MyISAM 表支持三种不同的存储格式：

  1. 静态（固定长度）表：所有字段的长度固定，便于快速访问。
  2. 动态表：字段长度可变，能够节省空间。
  3. 压缩表：采用压缩算法存储数据，进一步节省空间。

InnoDB 存储引擎

InnoDB 是一个健壮的事务型存储引擎，广泛应用于互联网公司，适用于处理大规模数据存储。MySQL 从 5.5.5 版本开始，默认存储引擎为 InnoDB。其主要特点包括：

  • 事务支持：InnoDB 支持 ACID（原子性、一致性、隔离性、持久性）事务，是 MySQL 的标准事务引擎。
  • 行级锁定：提高并发操作的性能。
  • 外键约束：支持外键，确保数据完整性。
  • 自动灾难恢复：InnoDB 能够自动从灾难中恢复数据。
  • 支持自动增长列（AUTO_INCREMENT）。

InnoDB 的数据文件包括：

  • ibd 文件：存储数据表的数据。
  • .frm 文件：存储表的元数据。
  • .opt 文件：存储 MySQL 的配置信息，如编码和排序规则。

MyISAM 与 InnoDB 选择

在选择使用 MyISAM 还是 InnoDB 时，可以考虑以下几点：

  1. 事务需求：如果应用程序需要使用事务，建议选择 InnoDB 引擎。需要注意的是，InnoDB 的行级锁定在某些条件下可能会变成全表锁定，例如在 WHERE 条件未使用主键时。
  2. 查询性能：如果对查询性能要求较高，MyISAM 是更好的选择。MyISAM 的索引和数据是分开的，其索引是压缩的，可以更好地利用内存，从而提高查询性能并节省磁盘空间。

修改存储引擎的方法

通过配置文件修改默认存储引擎：

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
# 添加下面语句
default-storage-engine=MyISAM
[root@localhost ~]# systemctl restart mysqld

在创建表时指定存储引擎：

> use auth;
mysql> create table t3 (id int(10), name char(20)) engine=INNODB;
mysql> show table status from auth where name='t3'\G

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

SET default_storage_engine=<存储引擎名>;


MyISAM 与 InnoDB 的性能对比实验

  在本文档中，我们将对 MyISAM 和 InnoDB 两种存储引擎进行详细的性能对比实验。通过实验，我们将探讨两者在数据插入、查询效率等方面的不同表现，以便为选择合适的存储引擎提供参考

实验准备

首先，我们创建两个数据库表，分别使用 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

以上命令显示两张表的结构一致，但使用不同的存储引擎。


数据插入测试

为了测试两个存储引擎的存储效率，我们向每个表插入一千万条数据。我们使用存储过程自动插入数据：

  1. 设置 SQL 语句结束符：
DELIMITER $

2.创建存储过程：

CREATE PROCEDURE insertm()
BEGIN
    SET @i=1;
    WHILE @i<=10000000 DO
        INSERT INTO tm(name) VALUES(CONCAT("wy", @i));
        SET @i=@i+1;
    END WHILE;
END
$

CREATE PROCEDURE inserti()
BEGIN
    SET @i=1;
    WHILE @i<=10000000 DO
        INSERT INTO ti(name) VALUES(CONCAT("wy", @i));
        SET @i=@i+1;
    END WHILE;
END

3.恢复默认结束符：

DELIMITER ;

4.调用存储过程向两个表插入数据，并记录时间：

CALL insertm;  -- 插入 MyISAM 表
CALL inserti;  -- 插入 InnoDB 表

查询测试

  1. 查询数据总数：

我们使用 COUNT(*) 查询两个表的数据总数，并分析 SQL 语句：

DESC SELECT COUNT(*) FROM

InnoDB 表的查询分析结果：

id: 1
select_type: SIMPLE
table: ti
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 1000160
Extra: Using index

MyISAM 表的查询分析结果：

id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away

由于 MyISAM 表的数据存储在专门的 MYD 文件中，因此查询优化程度较高。

  1. 范围查询：

我们对没有索引的列进行范围查询，并记录时间：

SELECT * FROM tm WHERE name>"wy100" AND name<"wy10000000";
SELECT * FROM ti WHERE name>"wy100" AND name<"wy10000000";

接下来，我们对有索引的列进行范围查询：

SELECT * FROM tm WHERE id>10 AND id<999999;
SELECT * FROM ti WHERE id>10 AND id<999999;

结论

  通过实验可以看出，在数据插入方面，MyISAM 存储引擎表现优异，插入速度明显快于 InnoDB。但在涉及到事务和数据恢复等方面时，InnoDB 具有明显优势。InnoDB 的行级锁定和外键约束功能使其在更新密集的场景中表现更佳，而 MyISAM 则在查询性能和存储效率方面更具优势。

  对于需要高查询性能且不要求事务支持的应用，可以选择 MyISAM 存储引擎。而对于需要高并发更新和事务支持的应用，InnoDB 无疑是更好的选择。