MySql存储引擎

 

目录

引言

存储引擎是什么？

存储引擎有哪些？

MyISAM

InnoDB

MEMORY

横向对比

如何查看使用的那种引擎？

使用SQL查询查看表的存储引擎：

查看MySQL支持的存储引擎：

查看默认的存储引擎：

如何更换存储引擎？

方法

步奏

---

引言

MySQL是目前最流行的开源关系型数据库系统之一，其核心组件之一就是存储引擎。存储引擎是MySQL中用来处理数据表中数据的模块，不同的存储引擎提供了不同的数据存取方式、索引技术、锁定水平及事务处理等。下面我们就来介绍MySQL中常用的几种存储引擎以及它们各自的优缺点和使用场景。

存储引擎是什么？

存储引擎是MySQL中用来处理数据表中数据的模块

存储引擎有哪些？

MyISAM

MyISAM MyISAM是MySQL中最早的默认存储引擎，它非常适合用在读操作远多于写操作的情况。MyISAM不支持事务，也不支持行级锁定，只支持表级锁定。
它的优点是速度快，占用的存储空间小，但缺点是数据的安全性不高，不支持事务，不支持外键等。

特点：

每一个采用MyISAM引擎的数据表在实际存储中都是由三个文件组成，分别是frm文件，MYD文件和MYI文件，文件后缀为上述三个，文件名与数据表名相同。

frm文件保存表的结构，MYD保存表的数据，MYI保存表的索引文件，MYD和MYI与MyISAM引擎有很深的关联。

说明：从MySQL8.0开始，它就可以被称为过时了 – MySQL已经确保在使用InnoDB时，MyISAM可以完成的所有工作都可以完成，因此目前，只有当您希望简单的COUNT（*）查询更快时，MyISAM才非常有用。这样的查询会更快，因为MyISAM将表记录数数字存储在表元数据中–其他 MySQL存储引擎则没有。

InnoDB

InnoDB是MySQL 5.5及以后版本的默认存储引擎，它支持事务，支持外键，支持行级锁定，并且提供了崩溃恢复和多版本并发控制等高级特性。
InnoDB的优点是数据安全性高，支持事务，支持行级锁定，但缺点是占用的存储空间相对较大，性能上也没有MyISAM快。

MEMORY

MEMORY存储引擎使用内存作为数据存储介质，这使得其读写速度非常快，但是数据的持久性得不到保证，一旦数据库服务重启，所有的数据都会消失。
MEMORY存储引擎适合用于需要快速访问数据，但数据的持久性不是特别重要的场景，例如用于缓存或者临时表等。

横向对比

引擎名称	优点	缺点	使用场景	更新引擎方式
InnoDB	1. 支持事务处理（ACID兼容）	1. 相对于MyISAM，写操作可能稍慢一些	1. 需要高并发写入、事务支持和数据完整性的应用	ALTER TABLE table_name ENGINE=InnoDB;
	2. 支持行级锁定和外键约束	2. 可能增加一些额外的开销	2. 电商网站、金融系统等
	3. 崩溃恢复能力强
	4. 提供聚簇索引，提高查询效率
MyISAM	1. 读取速度快，适合大量查询操作	1. 不支持事务处理	1. 大量读取操作、不需要事务支持的应用	ALTER TABLE table_name ENGINE=MyISAM;
	2. 表级锁定，简化锁定机制	2. 只支持表级锁定，并发性能相对较低	2. 静态网页内容、只读数据等
	3. 支持全文索引，适用于文本搜索	3. 崩溃恢复能力较弱
Memory	1. 数据存储在内存中，读取速度非常快	1. 数据易失，重启数据库后数据会丢失	1. 临时表、缓存数据	ALTER TABLE table_name ENGINE=MEMORY;
	2. 支持哈希索引，提高查询性能	2. 适合存储的数据量较小	2. 频繁访问的小数据集

如何查看使用的那种引擎？

1. 使用SQL查询查看表的存储引擎：

   • 打开MySQL客户端，并连接到MySQL服务器。
   • 运行以下SQL查询：SHOW TABLE STATUS;。这将列出所有数据库中的表以及与每个表相关的信息，包括存储引擎。在结果中，可以查看“Engine”列，该列显示了每个表所使用的存储引擎。

2. 查看MySQL支持的存储引擎：

   • 使用SHOW ENGINES;语句，可以以表格形式显示系统所支持的引擎类型。其中“Support”列的值表示某种引擎是否能使用，“YES”表示可以使用，“NO”表示不能使用，“DEFAULT”表示该引擎为当前默认引擎。

3. 查看默认的存储引擎：

   • 使用SHOW VARIABLES LIKE 'default_storage_engine%';命令，可以查看系统的默认存储引擎。

如何更换存储引擎？

方法

在MySQL中，你可以使用ALTER TABLE语句来更改表的存储引擎。例如，如果你想要将名为table_name的表从MyISAM引擎更改为InnoDB引擎，你可以执行以下SQL语句：

ALTER TABLE table_name ENGINE=InnoDB;

步奏

请注意，在执行此类操作之前，务必备份相关数据以防止数据丢失或损坏

1. 备份数据：
   在切换存储引擎之前，务必备份你的数据。这可以防止在切换过程中发生任何意外情况导致数据丢失。你可以使用mysqldump命令或其他第三方备份工具来完成数据备份。

2. 选择新的存储引擎：
   根据你的业务需求和数据特点，选择适合的存储引擎。例如，如果你需要事务支持和行级锁定，InnoDB可能是一个好选择。而如果你主要进行大量读取操作且不需要事务支持，MyISAM可能更合适。

3. 修改表的存储引擎：
   使用ALTER TABLE语句来更改表的存储引擎。语法如下：

   ALTER TABLE table_name ENGINE=new_engine;

   
   其中table_name是你要更改的表的名称，new_engine是你想要设置的新存储引擎的名称（如InnoDB、MyISAM等）。

4. 校验并修复数据：
   在切换存储引擎后，建议进行数据校验和修复，以确保数据的完整性和正确性。这可以通过运行相应的校验和修复工具或命令来完成。

5. 检查并优化性能：
   切换存储引擎后，检查数据库的性能，并根据需要进行优化。这包括调整索引、查询优化等。

请注意，更换存储引擎是一个可能会影响数据完整性和性能的操作，因此建议在低峰时段进行，并仔细评估潜在的影响。此外，如果你正在使用的是MySQL 5.7或更高版本，并且表中有正在运行的查询或事务，你需要在切换期间暂停这些活动，以防止错误或数据不一致等问题。