存储引擎
1 查看存储引擎
存储引擎之间并不是迭代关系,而是各有优缺点,应在合适的场景选择合适的存储引擎
-
查看 MySQL 提供了哪些存储引擎:
show engines;
-
Support:表示 MySQL 是否支持
-
Comment:说明
-
Transaction:是否支持事务
-
XA:是否支持分布式事务
-
Savepoints:保存点,是否支持事务的回滚
2 设置系统默认的存储引擎
- 查看默认的存储引擎
show variables like '%storage_engine%';
或
SELECT @@default_storage_engine;
- 修改默认的存储引擎
若在创建表时,没有显式的指定表的存储引擎,则默认会使用InnoDB
作为表的存储引擎
SET DEAFULT_STORAGE_ENGINE=MyISAM;
或者修改 my.cnf
文件 ,后重启服务器
default-storage-engine=MyISAM
3. 设置表的存储引擎
可以为不同表设置不同的存储引擎
3.1 创建表时指定存储引擎
若不指定表的存储引擎,则默认使用 InnoDB
CREATE TABLE 表名(
字段名1 字段类型
) ENGINE = 存储引擎名称;
3.2 修改表的存储引擎
ALTER TABLE 表名 ENGINE = 存储引擎名;
4. 引擎介绍
4.1 InnoDB 引擎:支持外键的事务存储引擎 ⭐
- 在 MySQL >= 5.5 后,就默认使用 InnoDB 引擎
- InnoDB 是 MySQL 的
默认事务型引擎
,被设计用来处理大量的短期(short-lived)事务,可以确保事务的完整提交(Commit)和回滚(Rollback)。 - 除了 查询 和 添加 外,若需要更新、删除操作,则应该优先使用 InnoDB 存储引擎
- 除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑InnoDB引擎。
- 数据文件结构:
- 表名.frm 存储表结构(在 MySQL8 ,合并到了 ibd 文件中)
- 表名.ibd 存储数据和索引
- InnoDB 就是为了
处理巨大的数据量的最大性能设计
- 相比于 MyISAM 的存储引擎,
InnoDB写操作的处理效率会较差一些
,并且会占用更多的磁盘空间来保存数据和索引 - MyISAM 只缓存索引,不缓存真实数据;InnoDB 则两者都需要缓存,
对内存的要求更高
,而且内存大小对性能有决定性的影响
4.2 MyISAM 引擎:非事务处理存储引擎 ⭐
- MyISAM 有大量的特性,包括全文索引、压缩、空间函数(GIS) 等,但 MyISAM
不支持事务、行级锁、外键
,缺点:崩溃后无法安全恢复
- 5.5 之前默认的存储引擎
- 优势:访问速度快,对事务完整性没有要求获取以 SELECT、INSERT 为主的应用
- 对数据有额外的常数存储。即有一个单独的变量来存储数据的条目数,所以 count(*) 的查询效率较高
- 数据文件结构:
- 表名.frm 存储表结构
- 表名.MYD 存储数据(MYData)
- 表名.MYI 存储索引(MyIndex)
- 应用场景:只读应用或者以读为主的业务
4.3 Archive 引擎:用于数据存档 ⭐
- archive:就是存档的意思,此存储引擎仅仅支持
插入
和查询
功能(不可修改) - 在 MySQL 5.5 以后支持 索引
- 有很好的压缩机制,使用
zlib 压缩库
, - 在创建表时,文件名则为:表名.ARZ
- Archive 存储引擎使用
行级锁
,并支持AUTO_INCREMENT
列属性 - 适合
存储日志和数据采集(归档)
;适合存储大量、独立的数据例如:历史记录,插入数据的速度很高,查询的效率较低
4.4 Blackhole 引擎:丢弃写操作,读操作会返回空内容
4.5 CSV 引擎:存储数据时,以逗号分隔各个数据项
创建 CSV 表,还会创建相应的元文件
,用于存储表的状态
和表中存储的行数
,文件名:表名.CSM
数据文件,文件名:表名.CSV
举例:
mysql> CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+---+------------+
| i | c |
+---+------------+
| 1 | record one |
| 2 | record two |
+---+------------+
2 rows in set (0.00 sec)
文件为 .CSV 后缀 可以通过 NotePad++打开,还可以通过 Excel 表格打开
4.6 Memory 引擎:置于内存的表
逻辑介质:内存
,响应速度很快
,但是当 mysqld 守护线程崩溃时数据会丢失。要求:存储的数据必须是数据长度不变的格式
主要特征:
- 同时支持
支持哈希 (HASH) 索引
和B+树索引
- Memory 表比 MyISAM
快一个数量级
- Memory 表的大小有限制。主要取决于两个参数 ,
max_rows
和max_heap_table_size
。max_rows 可以在创建表时,指定;max_heap_table_size 的大小默认为 16MB,可以按需扩大 - 缺点:极易丢失数据
应用场景:
- 目标数据较小,并且频繁的进行访问
- 数据是临时的,则立即可用 的
- 丢失也没关系的数据
4.7 Federated 引擎:访问远程表
- Federated引擎是访问其他MySQL服务器的一个
代理
,尽管该引擎看起来提供了一种很好的跨服务器的灵活性
,但也经常带来问题,因此默认是禁用的
。
4.8 Merge 引擎:管理多个 MyISAM 表构成的表集合
4.9 NDB 引擎:MySQL 集群专用存储引擎
也叫做 NDB Cluster 存储引擎,主要用于 MySQL Cluster 分布式集群 环境,类似于 Oracle 的 RAC 集群。
5. MyISAM 和 InnoDB⭐
项 | MyISAM | InnoDB |
---|---|---|
外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
行表锁 | 表锁,即使操作一条数据也会锁住整张表,不适合高并发操作 | 行锁,只锁住操作某一行数据,适合高并发操作 |
缓存 | 只缓存索引,不缓存真实数据 | 既要缓存索引也要缓存真实数据,对内存要求高 |
自带系统表使用 | Y | N |
关注点 | 性能:节省资源、消耗少、简单业务 | 事务:并发、事务、更大资源 |
默认安装 | Y | Y |
默认使用 | N | Y |
版权声明:
本站所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自
临川羡鱼!
喜欢就支持一下吧