MySQL存储引擎

目录

一、存储引擎介绍

二、MyISAM存储引擎

1、MyISAM存储引擎特点：

2、MyISAM支持三种不同的存储格式

3、MyISAM适用的生产场景

三、InnoDB存储引擎

1、InnoDB存储引擎特点：

2、InnoDB使用的生产场景

四、InnoDB和MyISAM引擎的区别

五、配置适合的存储引擎

1、查看数据库支持的存储引擎类型

2、查看表正在使用的存储引擎类型

3、修改存储引擎类型

3.1、修改单表的存储引擎类型

3.2、修改Mysql的配置文件my.cnf，后重启MySQL

总结：

一、存储引擎介绍

引擎在MySQL服务器中的应用位置

MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的攻和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎。存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式。

MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储。

二、MyISAM存储引擎

1、MyISAM存储引擎特点：

①MyISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的

②访问速度快，对事务完整性没有要求

③MyISAM在磁盘上存储成三个文件，文件名和表名都相同，但是扩展名分别为：

.frm文件存储表结构的定义；

.MYD (MYData)数据文件的扩展名

.MYI(MYIndex)索引文件的扩展名

④表级锁定形式，数据在更新时锁定整个表

⑤数据库在读写过程中相互阻塞：串行操作，按照顺序操作，每次在读或写的时候会把全表锁起来会在数据写入的过程阻塞用户数据的读取，也会在数据读取的过程中阻塞用户的数据写入。（单向性）

⑥MyIsam是表级锁定，读或写无法同时进行

好处是:分开执行时，速度快、资源占用相对较少

2、MyISAM支持三种不同的存储格式

① 静态（固定长度）表

② 动态表

③压缩表

3、MyISAM适用的生产场景

① 公司业务不需要事务的支持

② 单方面读取或写入数据比较多的业务

③ MyISAM存储引擎数据读写都比较频繁场景不适合

④ 数据修改相对较少的业务

⑤ 对数据业务一致性要求不是非常高的业务服务器硬件资源相对比较差

⑥ 使用读写并发访问相对较低的业务

⑦服务器硬件资源相对比较差

MyIsam：适合于单方向的任务场景、同时并发量不高、对于事务要求不高的场景

三、InnoDB存储引擎

1、InnoDB存储引擎特点：

①支持事务，支持4个事务隔离级别，MySQL从5.5.5版本开始，默认的存储引擎为InnoDB

②行级锁定，但是全表扫描仍然会是表级锁定

③读写阻塞与事务隔离级别相关

④具有非常高效的缓存特性，能够缓存索引，也能缓存数据

⑤表和主键以簇的方式存储

⑥支持分区、表空间、类似Oracle数据库

⑦支持外键约束。5.5版本以前不支持全文索引，5.5版本以后的支持

⑧适合对硬件资源要求比较高的场合

⑨行级锁定,但是全表扫描仍然会是表级锁定

2、InnoDB使用的生产场景

① 业务需求事务的支持

② 行级锁定对高并发有很好的适应能力。但需要确保查询是通过索引来完成的

③ 业务数据更新较为频繁的场景，如：论坛，微博等

④ 业务数据一致性要求高，例如银行业务

⑤ 硬件设备内存比较大，利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO的压力

四、InnoDB和MyISAM引擎的区别

① InnoDB 支持事务，MyISAM 不支持：对于 InnoDB 每一条 SQL 语句都默认封装成事务进行提交，这样就会影响速度，优化速度的方式是将多条 SQL 语句放在 begin 和 commit 之间，组成一个事务。

② InnoDB 支持外键，而 MyISAM 不支持

③ InnoDB 不支持全文索引，而 MyISAM 支持全文索引，查询效率上 MyISAM 要高

InnoDB和MyISAM引擎的区别总结：

① 如果一个表写要求比较高的事务处理，可以选择 InnoDB

② 如果一个表读要求比较高的事务处理，可以选择 MyISAM

五、配置适合的存储引擎

1、查看数据库支持的存储引擎类型

使用show engines

mysql> mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
 

2、查看表正在使用的存储引擎类型

方法一：

使用show table status from 库名 where name='表名'  查看

mysql> mysql> show table status from class where name='member';
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| member | InnoDB |      10 | Dynamic    |    7 |           2340 |       16384 |               0 |            0 |         0 |           NULL | 2022-09-14 22:04:34 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

方法二：

使用show create table 表名  查询

mysql> show create table member;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| member | CREATE TABLE "member" (
  "id" int(10) DEFAULT NULL,
  "name" varchar(10) DEFAULT NULL,
  "cardid" varchar(18) DEFAULT NULL,
  "phone" varchar(11) DEFAULT NULL,
  "address" varchar(50) DEFAULT NULL,
  "remark" text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |              ### 存储引擎类型为InnoDB
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3、修改存储引擎类型

3.1、修改单表的存储引擎类型

使用命令：alter table 表名 engine=引擎名字

mysql> show create table member;             ### 查询member表的存储引擎为InnoDB
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| member | CREATE TABLE "member" (
  "id" int(10) DEFAULT NULL,
  "name" varchar(10) DEFAULT NULL,
  "cardid" varchar(18) DEFAULT NULL,
  "phone" varchar(11) DEFAULT NULL,
  "address" varchar(50) DEFAULT NULL,
  "remark" text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |     ### 查询member表的存储引擎为InnoDB
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> alter table member engine=MyISAM;   ### 修改member表的存储引擎为MyISAM
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0
 
mysql> show create table member;           ### 查询member表的存储引擎更改为MyISAM
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| member | CREATE TABLE "member" (
  "id" int(10) DEFAULT NULL,
  "name" varchar(10) DEFAULT NULL,
  "cardid" varchar(18) DEFAULT NULL,
  "phone" varchar(11) DEFAULT NULL,
  "address" varchar(50) DEFAULT NULL,
  "remark" text
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

3.2、修改Mysql的配置文件my.cnf，后重启MySQL

验证：新建一个表，查看他的默认使用的存储引擎

总结：

常用的两种MySQL存储引擎InnoDB和MyISAM。学习了他们之间的区别，和MySQL现用默认的存储引擎为InnoDB，学会了通过命令和修改配置文件的方式来修改存储引擎达到不同的目的