本文详细介绍了MySQL的两种主要存储引擎MyISAM和InnoDB的特性和应用场景。MyISAM适用于读取操作频繁且不需事务处理的场景，而InnoDB支持事务，提供行级锁定，适合高并发和数据一致性的需求。了解存储引擎的选择依据，如字段类型、锁定类型、索引支持和事务处理，有助于配置合适的存储引擎。
前言：存储引擎的实质就是如何实现存储数据，为存储的数据建立索引以及查询、更改、删除数据等技术实现的方法。不同于很多数据库系统仅仅选择一种类型的数据存储方式，MySQL数据库支持多种不同的数据引擎，可以根据具体的要求，针对每一个表使用不同的数据引擎，摒弃了很多其他数据库采用“一个尺码满足一切需求”的解决方案。正式这种灵活性，使得MySQL数据库如此受欢迎。
一、MySQL存储引擎
1.存储引擎介绍
MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体功能。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎
MySQL提供了多个不同的存储引擎，可以预先设置或者在MySQL服务器中启用。根据实际需要还可以分别选择使用于服务器、数据库或者表格的存储引擎，以便在后续检索这些信息等操作过程中提供最大的灵活性
从上图MySQL的体系架构中，可以清楚地看出MySQL是由SQL接口、解析器、优化器、缓存、存储引擎组成
- Connectors：连接组件
- Enterprise Management Service & Utilities：管理服务和控制组件
- Connection Pool：连接池组件
- SQL Interface：SQL接口组件
- Parser：查询分析器组件
- Optimizer：优化器组件
- Caches & Buffers：缓存组件
- Pluggable Storage Engines：插件式存储引擎
- File System：文件系统
- File & Logs：存储文件和日志
2.MyISAM的特点
1）介绍
- 是一个定义明确且经历时间考研的数据表格管理方法，在设计之时就考虑到数据库被查询的次数要远大于更新的次数
- 执行读取操作的速度更快
- 不占用大量的内存和存储资源
- 不足之处是不支持事物处理，也不能容错，如果硬盘崩溃，数据文件则无法恢复
MyISAM管理非事务表，是MySQL的ISAM扩展格式。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作。MyISAM提供高速存储和检索，以及全文搜索能力，从而受到Web开发者的青睐，因为在Web开发过程中需要进行大量的数据读取操作，所以大多数虚拟主机提供商和Internet平台提供商只允许使用MyISAM格式
2）特点
- 不支持事务，需要事务支持的系统不能使用MyISAM作为存储引擎
- 表级锁定形式，数据在更新时锁定整个表
- 数据库在读写过程中相互阻塞
- 在数据写入的过程中阻塞用户对数据的读取
- 在数据读取的过程中阻塞用户写入数据
- 可以通过key_buffer_size来设置缓存索引，提高访问的性能，减少磁盘IO的压力
- 采用MyISAM存储引擎进行数据单独写入或读取，速度较快且占用资源相对要少
- MyISAM存储引擎不支持外键约束，只支持全文索引
- 每个MyISAM在磁盘上存储成三个文件，每一个文件的名字均以表的名字开始，拓展名指出文件类型
- .frm文件存储表定义
- 数据文件的拓展名为：.MYD(MYData)
- 索引文件的拓展名为：.MYI(MYIndex)
二、InnoDB存储引擎
1.InnoDB特点
- 支持事务，支持四个事务隔离级别
- 行级锁定，但是全表扫描仍然会是表级锁定
- 读写阻塞与事务隔离级别相关
- 具有非常高效的缓存特性，能缓存索引，也能缓存数据
- 表与主键以簇的方式存储
- 支持分区、表空间，类似Oracle数据库
- 支持外键约束
- 适合对硬件资源要求比较高得场合
2.InnoDB适用得生产场景
- 业务需要事务的支持
- 行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引来完成
- 业务数据更新较为频繁的场景，如论坛、微博等
- 业务数据一致性要求较高，例如银行业务
- 硬件设备内存较大，利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO的压力
3. 选择依据
1）存储引擎支持的字段和数据类型
- 所有引擎都支持通用的数据类型，但不是所有的引擎都支持其他的字段类型，如二进制对象
2）锁定类型
- 不同的存储引擎支持不同级别的锁定
- 表锁定：MyISAM支持
- 行锁定：InnoDB支持
3）索引的支持
- 建立索引在搜索和恢复数据库中的数据时能够显著提高性能
- 不同的存储引擎提供不同的制作索引的技术
- 有些存储引擎根本不支持索引
4）事务处理的支持
- 事务处理功能提供向表中更新和插入信息期间的可靠性
- 可根据企业业务是否要支持事务选择存储引擎
三、配置合适的存储引擎
- 步骤一：查看数据库可配置的存储引擎类型
- 步骤二：查看表正在使用的存储引擎类型
- 步骤三：配置存储引擎为所选择的类型
1.查看数据库可配置的存储引擎类型
mysql> show engines; //使用show engines可以查看mysql默认使用的存储引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| 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)
- Engine列表示当前版本的MySQL所支持的引擎类型
- Support列表示对应引擎是否能使用
- Transactions列表示对应引擎是否支持事务
- Default表示该引擎为当前默认存储引擎
- InnoDB是默认的存储引擎，它支持事务的操作
- MyISAM在当前版本的MySQL中是支持的，还有一些其他的存储引擎也是支持的，修改存储引擎时可以制定
2.查看表正在使用的存储引擎
查看表正在使用的存储引擎类型，有两种方式
1）使用show table status命令
mysql> show table status from mysql where name='user';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
| 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 |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
| user | MyISAM | 10 | Dynamic | 3 | 126 | 380 | 281474976710655 | 4096 | 0 | NULL | 2020-01-06 18:25:27 | 2020-01-06 18:44:01 | NULL | utf8_bin | NULL | | Users and global privileges |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
1 row in set (0.00 sec)
//可以看到user表使用的存储引擎是MyISAM
2）使用show create命令
mysql> show create table user;
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' |
。。。省略部分内容
3.配置存储引擎为所选择的类型
当需要选择非默认存储引擎时，修改存储引擎的方法有四种
1）使用alter table命令修改
mysql> use school; //进入数据库
Database changed
mysql> show create table biao; //查看引擎
。。。。省略部分内容
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | //默认为InnoDB
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table biao engine=MyISAM; //修改存储引擎为MyISAM
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table biao; //查看
。。。省略部分内容
) ENGINE=MyISAM DEFAULT CHARSET=utf8 | //修改成
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2）修改MySQL的配置文件
修改MySQL的配置文件my.cnf，可以指定default-storage-engine选项设置默认的存储引擎
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
default-storage-engine=MyISAM
。。。。省略部分内容
//这里指定默认存储引擎是MyISAM，需要重新启动MySQL服务器。创建新表将使用MyISAM存储引擎
[root@localhost ~]# systemctl restart mysqld //重启服务
[root@localhost ~]# mysql -uroot -p1234
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> create database text;
Query OK, 1 row affected (0.00 sec)
mysql> use text;
Database changed
mysql> create table b(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table b;
。。。省略部分内容
) ENGINE=MyISAM DEFAULT CHARSET=utf8 | //新表的存储引擎是MyISAM
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.05 sec)
3）使用create table创建表时用engine指定
使用create table 创建表时用engine指定存储引擎，会使用指定的存储引擎。现在数据库的默认引擎是MyISAM，但创建新表时指定使用InnoDB，则新表的存储引擎就是InnoDB。
mysql> use text;
Database changed
mysql> create table a(id int) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table a;
。。。省略部分内容
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | //新表存储引擎是InnoDB
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4）使用mysql_convert_table_format 命令
使用mysql_convert_table_format 命令可以批量转换存储引擎，命令格式如下
mysql_convert_table_format --user=root --password=密码 --socket=/temp/mysql.sock --engine=引擎 库名 表名
--force //如果碰到错误强制进行转换
--host //转换的主机名
--password //当前进行转换用户的密码
--port //如果不是以localhost连接的话，请指定端口
--socket //socket文件存在的位置
--user //连接的用户名
总结
- MyISAM适合在无事务要求，读写并发少的场景使用
- InnoDB适合在有事务要求，读写并发多的场景使用
- MySQL存储引擎是MySQL数据库服务器中的一个组件，负责为数据库执行实际的数据I/O操作
- 修改存储引擎有很多种方式，应根据当前数据库的状态使用不同的方式
扫一扫
3088
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
