mysql体系架构
- 客户端(java、php、python等)
- mysql服务层（连接管理器、查询解析器、查询优化器、查询缓存）
- mysql存储引擎（innodb、myisam等）
存储引擎针对表而言的，不针对库
mysql存储引擎
mysql存储引擎之MyISAM
5.5前默认的存储引擎、临时表（查询、排序时由查询优化器生成的表）、系统表
- 特性：
- 并发性与锁级别（表级锁），
- 表损坏修复，检查
check table tablename，修复repair table tablename
此外也可以通过myisamchk对表进行修复，但是必须注意的是使用该命令进行修复时需要将mysql服务停止后进行。否则有可能对数据表造成更大的损坏。
- myisam支持全文索引
- myisam表支持数据压缩，可以使用
myisampack命令进行压缩，如：myasimpack -b -f tablename.MYI。对已经压缩后的表不能进行写操作，因此要慎重操作。
- 限制：
- 5.0前，单表文件最大支持4G。如存储大表需要修改MAX_Rows和AVG_ROW_LENGTH；5.0后默认支持256TB
- 使用场景
- 非事务型应用
- 只读类应用
- 空间类应用
mysql存储引擎之Innodb
MySQL5.5及以后版本默认存储引擎
- Innodb使用表空间进行数据存储
由innodb_file_per_table参数的值进行控制，如果为ON，则独立表空间（tablename.ibd）。如果为OFF，则会存储到系统共享表空间ibdataX。
如何选择系统表空间和独立表空间
- 系统表空间无法简单的收缩文件大小
- 独立表空间可以通过optimize table命令收缩系统文件
- 系统表空间会产生IO瓶颈
- 独立表空间可以同时向多个文件刷新数据
建议对Innodb使用独立的表空间。5.6后已经是默认方式。
- 如何将系统表空间转换为独立表空间
- 1、使用mysqldump导出所有表数据
- 2、停止Mysql服务，修改参数，并删除Innodb相关文件。如果是主从架构可以先处理从库再处理主库。
- 3、重启Mysql服务，重建Innodb系统表空间
- 4、重新导入数据
- Innodb存储引擎的特性
Innodb数据字典信息
Innodb的特性
- 事务性存储引擎
- 完全支持事务的ACID特性，
- Redo Log（重做日志，已提交事务，实现事务的持久性）和Undo Log（未提交事务进行回滚）
- Innodb支持行级锁
- 支持更多的写并发
- 行级锁由存储引擎层实现的。
- 什么是锁？
- 管理资源的并发访问
- 实现事务的隔离性
- 锁的类型
- 共享锁（读锁）
- 独占锁（写锁），排他性
- 锁的粒度
行级锁、页级锁、表级锁
- 表级锁
- 行级锁
- 阻塞和死锁
- 什么是阻塞，不同锁之间的兼容性，不同锁之间需要等待锁响应。
- 什么是死锁，两个或两个以上，互相占用资源而互相等待。一般情况下系统会自动处理。
- Innodb状态检查
shown engine innodb status
- 使用场景
- Innodb适用于大多数OLTP应用
mysql存储引擎之CSV
文件存储特点：csv文件作为存储引擎。数据以文件方式存储在文件中。
- 特点
- 以csv格式进行数据存储
- 所有的列定义不能是NULL
- 不支持索引（不适用于大表及在线系统）
- 可以直接对数据文件进行编辑
- 使用场景
- 适用于数据交换的中间表
mysql存储引擎之Archive
- 特点
- 以zlib对表数据进行压缩，磁盘I/O更少
- 存储在ARZ为后缀的文件中
存储特点：
- 只支持insert和select操作，支持行级锁
- 只允许在自增ID列上添加索引
使用场景
- 日志和数据采集类应用
mysql存储引擎之Memory
- 特点
也成为Heap存储引擎，所有数据保存在内存中。一旦mysql重启，则数据会丢失，表结构会保存。
- 功能特点：
- 支持hash索引（等值查询）和btree（范围查询）索引，默认为hash索引
- 所有字段固定长度为char(10)
- 不支持blog和text等大字段
- 使用表级锁，因此性能也不一定会很高。
- 表最大大小由参数max_heap_table_size参数决定
- 容易混淆的概念
- 临时表，系统使用的临时表；create temporary table建立的表。
- 使用场景
- 用于查找或映射表，如邮编和地区的对应表。
- 保存数据的中间表
- 混存周期性聚合数据的结果表
mysql存储引擎之Federated
- 特点
- 提供了远程访问mysql服务器上表的方法
- 本地不存储数据，数据全部存储在远程服务器上
- 本地会保存远程数据库表结构和远程连接的信息
- 如何使用
由于可以通过其他方式替代，因而默认是禁止的。但可以在启动时通过federated参数
mysql://user_name[:password]@host_name[:port]/dbname/tablename
- 使用场景
- 偶尔的统计分析和手工查询
如何选择存储引擎
参考条件
- 事务
- 备份，热备
- 崩溃恢复
- 存储引擎的特性
尽量不要同时混合使用多种存储引擎