MySQL高级_第05章_存储引擎
1. 查看存储引擎
- 查看mysql提供什么存储引擎:
show engines ;
show engines \G ;
*************************** 1. row ***************************Engine : InnoDBSupport: DEFAULTComment : Supports transactions, row - level locking, and foreign keysTransactions: YESXA : YESSavepoints: YES*************************** 2. row ***************************Engine : MRG_MYISAMSupport: YESComment : Collection of identical MyISAM tablesTransactions: NOXA : NOSavepoints: NO*************************** 3. row ***************************Engine : MEMORYSupport: YESComment : Hash based, stored in memory, useful for temporary tablesTransactions: NOXA : NOSavepoints: NO*************************** 4. row ***************************Engine : BLACKHOLESupport: YESComment : /dev/null storage engine (anything you write to it disappears)Transactions: NOXA : NOSavepoints: NO*************************** 5. row ***************************Engine : MyISAMSupport: YESComment : MyISAM storage engineTransactions: NOXA : NOSavepoints: NO*************************** 6. row ***************************Engine : CSVSupport: YESComment : CSV storage engineTransactions: NOXA : NOSavepoints: NO*************************** 7. row ***************************Engine : ARCHIVESupport: YESComment : Archive storage engineTransactions: NOXA : NOSavepoints: NO*************************** 8. row ***************************Engine : PERFORMANCE_SCHEMASupport: YESComment : Performance SchemaTransactions: NOXA : NOSavepoints: NO*************************** 9. row ***************************Engine : FEDERATEDSupport: NOComment : Federated MySQL storage engineTransactions: NULLXA : NULLSavepoints: NULL
2. 设置系统默认的存储引擎
- 查看默认的存储引擎:
show variables like '%storage_engine%' ;# 或SELECT @@default_storage_engine ;
- 修改默认的存储引擎
SET DEFAULT_STORAGE_ENGINE=MyISAM;
default-storage-engine = MyISAM# 重启服务systemctl restart mysqld.service
3. 设置表的存储引擎
3.1 创建表时指定存储引擎
CREATE TABLE 表名 (建表语句 ;) ENGINE = 存储引擎名称 ;
3.2 修改表的存储引擎
ALTER TABLE 表名 ENGINE = 存储引擎名称 ;
mysql> ALTER TABLE engine_demo_table ENGINE = InnoDB ;Query OK, 0 rows affected ( 0.05 sec)Records: 0 Duplicates: 0 Warnings : 0
mysql> SHOW CREATE TABLE engine_demo_table \G*************************** 1. row ***************************Table : engine_demo_tableCreate Table : CREATE TABLE `engine_demo_table` (`i` int ( 11 ) DEFAULT NULL) ENGINE = InnoDB DEFAULT CHARSET =utf81 row in set ( 0.01 sec)
4. 引擎介绍
4.1 InnoDB 引擎:具备外键支持功能的事务存储引擎
- MySQL从3.23.34a开始就包含InnoDB存储引擎。 大于等于5.5之后,默认采用InnoDB引擎 。
- InnoDB是MySQL的 默认事务型引擎 ,它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。
- 除了增加和查询外,还需要更新、删除操作,那么,应优先选择InnoDB存储引擎。
- 除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑InnoDB引擎。
-
数据文件结构:(在《第02章_MySQL数据目录》章节已讲)
- 表名.frm 存储表结构(MySQL8.0时,合并在表名.ibd中)
- 表名.ibd 存储数据和索引
-
InnoDB是 为处理巨大数据量的最大性能设计 。
- 在以前的版本中,字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除 了。比如: .frm , .par , .trn , .isl , .db.opt 等都在MySQL8.0中不存在了。
- 对比MyISAM的存储引擎, InnoDB写的处理效率差一些 ,并且会占用更多的磁盘空间以保存数据和索引。
- MyISAM只缓存索引,不缓存真实数据;InnoDB不仅缓存索引还要缓存真实数据, 对内存要求较高 ,而且内存大小对性能有决定性的影响。
4.2 MyISAM 引擎:主要的非事务处理存储引擎
- MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM 不支持事务、行级锁、外键 ,有一个毫无疑问的缺陷就是 崩溃后无法安全恢复 。
- 5.5之前默认的存储引擎
- 优势是访问的 速度快 ,对事务完整性没有要求或者以SELECT、INSERT为主的应用
- 针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高
-
数据文件结构:(在《第02章_MySQL数据目录》章节已讲)
- 表名.frm 存储表结构
- 表名.MYD 存储数据 (MYData)
- 表名.MYI 存储索引 (MYIndex)
- 应用场景:只读应用或者以读为主的业务
4.3 Archive 引擎:用于数据存档
- 下表展示了ARCHIVE 存储引擎功能
特征
|
支持
|
B
树索引
|
不支持
|
备份
/
时间点恢复
(在服务器中实现,而不是在存储引擎中)
|
支持
|
集群数据库支持
|
不支持
|
聚集索引
|
不支持
|
压缩数据
|
支持
|
数据缓存
|
不支持
|
加密数据(加密功能在服务器中实现)
|
支持
|
外键支持
|
不支持
|
全文检索索引
|
不支持
|
地理空间数据类型支持
|
支持
|
地理空间索引支持
|
不支持 |
哈希索引
|
不支持
|
索引缓存
|
不支持
|
锁粒度
|
行锁
|
MVCC
|
不支持
|
存储限制
|
没有任何限制
|
交易
|
不支持
|
更新数据字典的统计信息
|
支持
|
4.4 Blackhole 引擎:丢弃写操作,读操作会返回空内容
4.5 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 : 0mysql> SELECT * FROM test;+---+------------+| i | c |+---+------------+| 1 | record one || 2 | record two |+---+------------+2 rows in set ( 0.00 sec)
文章来源:https://www.toymoban.com/news/detail-448912.html
"1","record one""2","record two"
4.6 Memory 引擎:置于内存的表
- Memory同时 支持哈希(HASH)索引 和 B+树索引 。
- Memory表至少比MyISAM表要 快一个数量级 。
- MEMORY 表的大小是受到限制 的。表的大小主要取决于两个参数,分别是 max_rows 和 max_heap_table_size 。其中,max_rows可以在创建表时指定;max_heap_table_size的大小默认为16MB,可以按需要进行扩大。
- 数据文件与索引文件分开存储。
- 缺点:其数据易丢失,生命周期短。基于这个缺陷,选择MEMORY存储引擎时需要特别小心。
4.7 Federated 引擎:访问远程表
4.8 Merge引擎:管理多个MyISAM表构成的表集合
4.9 NDB引擎:MySQL集群专用存储引擎
4.10 引擎对比
特
点
|
MyISAM
|
InnoDB
|
MEMORY
|
MERGE
|
NDB
|
存
储
限
制
|
有
|
64TB
|
有
|
没有
|
有
|
事
务安
全
|
支持
|
||||
锁
机
制
|
表锁,即使操作一条
记录也会锁住整个
表,不适合高并发的
操作
|
行锁,操作时只锁某一行,不
对其它行有影响,适合高并发
的操作
|
表锁
|
表锁
|
行
锁
|
B
树
索
引
|
支持
|
支持
|
支持
|
支持
|
支
持
|
哈
希
索
引
|
支持
|
支
持
|
|||
全
文
索
引
|
支持
|
||||
集
群
索
引
|
支持
|
||||
数
据
缓
存
|
支持
|
支持
|
支
持
|
||
索
引缓
存
|
只缓存索引,不缓存
真实数据
|
不仅缓存索引还要缓存真实数
据,对内存要求较高,而且内
存大小对性能有决定性的影响
|
支持
|
支持
|
支
持
|
数
据
可
压
缩
|
支持
|
||||
空
间
使
用
|
低
|
高
|
N/A | 低 | 低 |
特
点
|
MyISAM
|
InnoDB
|
MEMORY
|
MERGE
|
NDB
|
内
存
使
用
|
低
|
高
|
中等
|
低
|
高
|
批
量
插
入
的
速
度
|
高
|
低
|
高
|
高
|
高
|
支
持外
键
|
支持
|
5. MyISAM和InnoDB
对比
项
|
MyISAM
|
InnoDB
|
外键
|
不支持
|
支持
|
事务
|
不支持
|
支持
|
行表锁
|
表锁,即使操作一条记录也会锁住
整个表,不适合高并发的操作
|
行锁,操作时只锁某一行,不对其它行有影响,
适合高并发的操作
|
缓存
|
只缓存索引,不缓存真实数据
|
不仅缓存索引还要缓存真实数据,对内存要求较
高,而且内存大小对性能有决定性的影响
|
自带系
统表使
用
|
Y
|
N
|
关注点
|
性能:节省资源、消耗少、简单业
务
|
事务:并发写、事务、更大资源
|
默认安
装
|
Y
|
Y
|
默认使
用
|
N
|
Y
|
6. 阿里巴巴、淘宝用哪个
文章来源地址https://www.toymoban.com/news/detail-448912.html
- Percona 为 MySQL 数据库服务器进行了改进,在功能和性能上较 MySQL 有很显著的提升。
- 该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具;另外有更多的参数和命令来控制服务器行为。
- 该公司新建了一款存储引擎叫 Xtradb 完全可以替代 Innodb ,并且在性能和并发上做得更好
- 阿里巴巴大部分mysql数据库其实使用的percona的原型加以修改。
课外补充:
1、InnoDB表的优势
2、InnoDB和ACID模型
- 自动提交设置。
- COMMIT语句。
- ROLLBACK语句。
- 操作INFORMATION_SCHEMA库中的表数据。
- InnoDB双写缓存。
- InnoDB崩溃恢复。
- 自动提交设置。
- SET ISOLATION LEVEL语句。
- InnoDB锁的低级别信息。
- InnoDB双写缓存,通过innodb_doublewrite配置项配置。
- 配置项innodb_flush_log_at_trx_commit。
- 配置项sync_binlog。
- 配置项innodb_file_per_table。
- 存储设备的写入缓存。
- 存储设备的备用电池缓存。
- 运行MySQL的操作系统。
- 持续的电力供应。
- 备份策略。
- 对分布式或托管的应用,最主要的在于硬件设备的地点以及网络情况。
3、InnoDB架构
到了这里,关于MySQL高级_第05章_存储引擎的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!