MySQL高级篇-5-存储引擎
查看存储引擎
查看存储引擎
1 | show engines; |
设置系统默认的存储引擎
查看默认的存储引擎
1 | show variables like '%storage_engine%'; |
修改默认的存储引擎
如果在创建表的语句中没有显式指定表的存储引擎的话,那就会默认使用InnoDB
作为表的存储引擎。 如果我们想改变表的默认存储引擎的话,可以这样写启动服务器的命令行
1 | SET DEFAULT_STORAGE_ENGINE=MyISAM; |
或者修改my.cnf
文件:
1 | default-storage-engine=MyISAM |
1 | # 重启服务 |
设置表的存储引擎
存储引擎是负责对表中的数据进行提取和写入工作的,可以为 不同的表设置不同的存储引擎 ,不同的表可以有不同的物理存储结构、不同的提取和写入方式。
创建表时指定存储引擎
创建表的语句都没有指定表的存储引擎,那就会使用默认的存储引擎 InnoDB 。如果我们想显 式的指定一下表的存储引擎 , 那可以这么写:
1 | CREATE TABLE 表名( |
修改表的存储引擎
如果表已经建好了,我们也可以使用下边这个语句来修改表的存储引擎:
1 | ALTER TABLE 表名 ENGINE = 存储引擎名称; |
引擎介绍
InnoDB 引擎
InnoDB 引擎:具备外键支持功能的事务存储引擎
- InnoDB是MySQL的
默认事务型引擎
,用来处理大量的短期(short-lived)事务。可以确保事务 的完整提交(Commit)和回滚(Rollback) 除了新增和查询外,还需要更新、删除操作,应优先选择InnoDB存储引擎
除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑InnoDB引擎
数据文件结构:
表名.frm
存储表结构(MySQL8.0时,合并在表名.ibd
中)表名.ibd
存储数据和索引
InnoDB是 为处理巨大数据量的最大性能设计 。
对比
MyISAM
的存储引擎,InnoDB
写的处理效率差一些 ,并且会占用更多的磁盘空间以保存数据和 索引MyISAM
只缓存索引,不缓存真实数据;InnoDB
不仅缓存索引还要缓存真实数据, 对内存要求较高 ,而且内存大小对性能有决定性的影响。
MyISAM 引擎
MyISAM 引擎 : 主要的非事务处理存储引擎
MyISAM
提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM
不支持事务、行级 锁、外键 ,有一个毫无疑问的缺陷就是 崩溃后无法安全恢复访问的 速度快 ,对事务完整性没有要求或者以SELECT、INSERT为主的应用
针对数据统计有额外的常数存储。故而
count(*)
的查询效率很高数据文件结构:
表名.frm
存储表结构表名.MYD
存储数据 (MYData)表名.MYI
存储索引 (MYIndex)
- 应用场景:只读应用或者以读为主的业务
其他引擎
引擎类型 | 说明 |
---|---|
Archive 引擎 | 用于数据存档 |
Blackhole 引擎 | 丢弃写操作,读操作会返回空内容 |
CSV 引擎 | 存储数据时,以逗号分隔各个数据项 |
Memory 引擎 | 置于内存的表 |
Federated 引擎 | 访问远程表 |
Merge 引擎 | 管理多个MyISAM表构成的表集合 |
NDB引擎 | MySQL集群专用存储引擎 |
引擎对比
MySQL中同一个数据库,不同的表可以选择不同的存储引擎。如下表对常用存储引擎做出了对比
特 点 | MyISAM | InnoDB | MEMORY | MERGE | NDB |
---|---|---|---|---|---|
存 储 限 制 | 有 | 64TB | 有 | 没有 | 没有 |
事 务 安 全 | 支持 | ||||
锁 机 制 | 表锁,即使操作一条 记录也会锁住整个 表,不适合高并发的 操作 | 行锁,操作时只锁某一行,不 对其它行有影响,适合高并发 的操作 | 表锁 | 表锁 | 行 锁 |
B树 索 引 | 支持 | 支持 | 支持 | 支持 | 支 持 |
哈 希 索 引 | 支持 | 支 持 | |||
全 文 索 引 | 支持 | ||||
集 群 索 引 | 支持 | ||||
数 据 缓 存 | 支持 | 支持 | 支 持 | ||
索 引缓 存 | 只缓存索引,不缓存 真实数据 | 不仅缓存索引还要缓存真实数 据,对内存要求较高,而且内 存大小对性能有决定性的影响 | 支持 | 支持 | 支持 |
数 据 可 压 缩 | 支持 | ||||
空 间 使 用 | 低 | 高 | N/A | 低 | 低 |
内 存 使 用 | 低 | 高 | 中等 | 低 | 高 |
批 量 插 入 的 速 度 | 高 | 低 | 高 | 高 | 高 |
支 持外 键 | 支持 |
MyISAM和InnoDB 对比
对比 项 | MyISAM | InnoDB |
---|---|---|
外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
行表锁 | 表锁,即使操作一条记录也会锁住 整个表,不适合高并发的操作 | 行锁,操作时只锁某一行,不对其它行有影响, 适合高并发的操作 |
缓存 | 只缓存索引,不缓存真实数据 | 不仅缓存索引还要缓存真实数据,对内存要求较 高,而且内存大小对性能有决定性的影响 |
自带系 统表使 用 | Y | N |
关注点 | 性能:节省资源、消耗少、简单业 务 | 事务:并发写、事务、更大资源 |
默认安 装 | Y | Y |
默认使 用 | N | Y |
InnoDB和ACID模型
解InnoDB存储引擎与ACID模型相同作用的四个方面:
原子方面:ACID的原子方面主要涉及InnoDB事务
与MySQL相关的特性主要包括:
- 自动提交设置
- COMMIT语句
- ROLLBACK语句
- 操作INFORMATION_SCHEMA库中的表数据。
一致性方面 : ACID模型的一致性主要涉及保护数据不崩溃的内部InnoDB处理过程
与MySQL相关的特性主要包括:
- InnoDB双写缓存
- InnoDB崩溃恢复
隔离方面 :隔离是应用于事务的级别
与MySQL相关的特性主要包括:
- 自动提交设置
- SET ISOLATION LEVEL语句
- InnoDB锁的低级别信息
耐久性方面 : ACID模型的耐久性主要涉及与硬件配置相互影响的MySQL软件特性。由于硬件复杂多样 化,耐久性方面没有具体的规则可循
与MySQL相关的特性有:
- InnoDB双写缓存,通过innodb_doublewrite配置项配置
- 配置项innodb_flush_log_at_trx_commit
- 配置项sync_binlog
- 配置项innodb_file_per_table
- 存储设备的写入缓存
- 存储设备的备用电池缓存
- 运行MySQL的操作系统
- 持续的电力供应
- 备份策略
- 对分布式或托管的应用,最主要的在于硬件设备的地点以及网络情况
InnoDB 架构
缓冲池
缓冲池是主内存中的一部分空间,用来缓存已使用的表和索引数据。缓冲池使得经常被使用的 数据能够直接在内存中获得,从而提高速度
更改缓存
更改缓存是一个特殊的数据结构,当受影响的索引页不在缓存中时,更改缓存会缓存辅助索 引页的更改。索引页被其他读取操作时会加载到缓存池,缓存的更改内容就会被合并。
不同于集群索 引,辅助索引并非独一无二的。当系统大部分闲置时,清除操作会定期运行,将更新的索引页刷入磁 盘。更新缓存合并期间,可能会大大降低查询的性能。
在内存中,更新缓存占用一部分InnoDB缓冲池。 在磁盘中,更新缓存是系统表空间的一部分。更新缓存的数据类型由innodb_change_buffering配置项管 理。
自适应哈希索引
自适应哈希索引将负载和足够的内存结合起来,使得InnoDB像内存数据库一样运行, 不需要降低事务上的性能或可靠性。这个特性通过innodb_adaptive_hash_index
选项配置,或者通过-- skip-innodb_adaptive_hash_index
命令行在服务启动时关闭。
重做日志缓存
重做日志缓存存放要放入重做日志的数据。重做日志缓存大小通过innodb_log_buffer_size
配置项配置。重做日志缓存会定期地将日志文件刷入磁盘。大型的重做日志缓存 使得大型事务能够正常运行而不需要写入磁盘。
系统表空间
系统表空间包括InnoDB数据字典、双写缓存、更新缓存和撤销日志,同时也包括表和索引 数据。多表共享,系统表空间被视为共享表空间
双写缓存
双写缓存位于系统表空间中,用于写入从缓存池刷新的数据页。只有在刷新并写入双写缓存 后,InnoDB才会将数据页写入合适的位置
撤销日志
撤销日志是一系列与事务相关的撤销记录的集合,包含如何撤销事务最近的更改。如果其他 事务要查询原始数据,可以从撤销日志记录中追溯未更改的数据。撤销日志存在于撤销日志片段中,这 些片段包含于回滚片段中
每个表一个文件的表空间
每个表一个文件的表空间是指每个单独的表空间创建在自身的数据文件中, 而不是系统表空间中。这个功能通过innodb_file_per_table
配置项开启。每个表空间由一个单独的.ibd数 据文件代表,该文件默认被创建在数据库目录中
通用表空间
使用CREATE TABLESPACE语法创建共享的InnoDB表空间。通用表空间可以创建在MySQL数 据目录之外能够管理多个表并支持所有行格式的表
撤销表空间
撤销表空间由一个或多个包含撤销日志的文件组成。撤销表空间的数量由innodb_undo_tablespaces
配置项配置
临时表空间
用户创建的临时表空间和基于磁盘的内部临时表都创建于临时表空间。
innodb_temp_data_file_path
配置项定义了相关的路径、名称、大小和属性。如果该值为空,默认会在 innodb_data_home_dir
变量指定的目录下创建一个自动扩展的数据文件。
重做日志
重做日志是基于磁盘的数据结构,在崩溃恢复期间使用,用来纠正数据。正常操作期间, 重做日志会将请求数据进行编码,这些请求会改变InnoDB表数据。遇到意外崩溃后,未完成的更改会自 动在初始化期间重新进行