前言:
本章概要描述 MySQL 服务器架构, 各种存储引擎间的主要区别及区别的重要性
回顾 MySQL 历史背景, 基准测试, 通过简化细节和演示案例来讨论 MySQL 的原理
正文:
MySQL 架构可在多种不同场景中应用, 可嵌入到应用程序中农, 支持数据仓库, 内容索引, 部署软件, 高可用冗余系统, 在线事务处理系统等;
MySQL 最重要的特性是他的存储引擎架构, 使得查询处理及其他系统任务和数据存储, 提取分离;
1.1MySQL 逻辑架构
1.2 并发控制
锁粒度:
锁策略: 在锁开销和数据安全性间寻求平衡, 每个存储引擎可实现指定锁策略和粒度
表锁: table lock 最基本的 开销最小 锁定整表
行级锁: row lock 最大程度支持并发 最大的锁开销 在存储引擎层 (以自己的方式) 实现
1.3 事务
独立工作单元, 一组原子性 SQL 查询
隔离级别:
四种, 每种规定了事务中所作的修改, 较低的隔离可以执行更高的并发, 开销也更低
READ UNCOMMITTED 未提交读
事务中的修改及时没有提交, 对其他事务也是可见的; 事务读取未提交的数据: 脏读; 很少使用
READ COMMITTED 提交读
almost 库默认隔离级别, 非 MySQL; 事务从开始到结束只看见已提交的事务所作的修改, 本身所做的修改对其他事务不可见; 不可重复读: 两次执行同样的查询, 结果可能不一样(其他事务的修改)
REPEATABLE READ 可重复读
MySQL 默认, 解决了脏读, 同一事务多次读同样结果; 幻读: 当某个事务在读取某个范围内的记录时, 另一个事务在该范围内插入新的记录, 当前事务再次读取该范围记录, 幻行
SERIALIZABLE: 可串行化
最高, 强制事务串行执行, 避免幻读问题, 读取每行数据时加锁(可导致大量超时和锁争用), 很少使用
死锁
1, 两个多个事务在同一个资源上相互占用并请求锁定对方占用的资源;
2, 多个事务试图以不同的顺序锁定资源, 可能产生死锁;
3, 多个事务同时锁定同一个资源;
锁的行为和顺序和存取引擎相关, 同样的顺序执行语句, 一些存储引擎会产生死锁一些不会;
死锁产生的双重原因: 因为真正的数据冲突(很难避免), 因为存储引擎的实现方式导致;
死锁发送后, 只有部分或完全回滚其中一个事务, 才能打破死锁: InnoDB 即回滚持有最少行级排他锁的事务;
1.3.4MySQL 中的事务: 存储引擎实现
MySQL 两种事务型存储引擎: InnoDB,NDB Cluster
自动提交 AUTOCOMMIT;
默认采用自动提交模式, 如果不显式开始一个事务, 则每个查询都被当做一个事务执行提交操作, 可通过 AUTOCOMMIT 变量来启用 = 1 =ON , 禁用 = 0 =OFF(all 查询都在一个事务中直到显式 commit rollback)事务结束同时开始新的事务, 修改这个变量对非事务型表没有任何影响;
MySQL 可以通过 set transaction isolation level 设置隔离级别, 新的级别在下一个事务开始时生效, 配置文件设置整个库的, 也可只改变当前会话的隔离级别
set session transaction isolation level read committed;
建议: 不管何时都不要显示执行 LOCK TABLES , 不管使用的是什么存储引擎
1.4 多版本并发控制 MVCC
数据库 MySQL,Oracle,postgresql 等都实现了 MVCC, 各自实现机制不同[源 https://www.cnblogs.com/chenpingzhao/p/5065316.html ]
MVCC: 每个连接到数据库的读, 在某个瞬间看到的是数据库的快照, 写操作在提交之前对外不可见;[源 https://www.cnblogs.com/YFYkuner/p/5178684.html ]
更新时, 将旧数据标记为过时且在别处增加新版本的数据(多个版本的数据, 只有一个最新), 容许读取之前的数据
特点:
1, 每行数据都存在一个版本, 每次数据更新时都更新该版本
2, 修改时 copy 出当前版本, 随意修改, 各事务间不干扰
3, 保存时比较版本号, 成功 commit 则覆盖原纪录, 失败则放弃 rollback
4, 只在 REPEATABLE READ 和 READ COMMITTED 两个隔离级别下工作
1.5MySQL 存储引擎
mysql 将每个数据库保存位数据目录下的一个子目录, 创建表示, mysql 在子目录下创建与表同名的. frm 文件保存表的定义, 不同存储引擎保存数据和索引的方式不同, 但表的定义在 MySQL 服务层同一处理;
InnoDB: 默认事务型引擎, 最重要, 广泛使用
处理大量短期事务; 其性能和自动崩溃恢复特性, 非事务型存储的需求中也很流行
数据存储在由 InnoDB 管理的表空间中, 由一系列数据文件组成;
使用 MVCC 支持高并发, 并实现了四个标准的隔离级别, 默认是 REPEATABLE READ 可重复读, 通过间隙锁 next-key locking 防止幻读, 间隙锁使得 InnoDB 锁定查询设计的行还锁定索引中的间隙防止唤影行;
间隙锁:
当使用范围条件并请求锁时, InnoDB 给符合条件的已有数据记录的索引项加锁, 对应键值在条件范围内但是不存在的记录 (间隙) 加锁, 间隙锁:[源 https://blog.csdn.net/xiaobluesky/article/details/50412069 ]
- // 如 emp 表中有 101 条记录, 其 empid 的值分别是 1,2,...,100,101
- Select * from emp where empid> 100 for update;
InnoDB 对符合条件的 empid 值为 101 的记录加锁, 也会对 empid 大于 101(这些记录并不存在)的 "间隙" 加锁;
1, 上面的例子, 如果不使用间隙锁, 如果其他事务插入大于 100 的记录, 本事务再次执行则幻读, 但是会造成锁等待, 在并发插入比较多时, 要尽量优化业务逻辑, 使用相等条件来访问更新数据, 避免使用范围条件;
2, 在使用相等条件请求给一个不存在的记录加锁时, 也会使用间隙锁, 当我们通过参数删除一条记录时, 如果参数在数据库中不存在, 库会扫描索引, 发现不存在, delete 语句获得一个间隙锁, 库向左扫描扫到第一个比给定参数小的值, 向右扫描到第一个比给定参数大的值, 构建一个区间, 锁住整个区间内数据;[源 https://blog.csdn.net/andyxm/article/details/44810417 ]
1.5.2MyIsSAM 存储引擎
全文索引, 压缩, 空间函数, 不支持事务和行级锁, 崩溃后无法安全恢复
存储:
将表存储在两个文件中: 数据. MYD, 索引文件. MYI
表可以包含动态或静态 (长度固定) 行, MySQL 据表定义来决定采用何种行格式
表如是变长行, 默认配置只能处理 256TB 数据(指向记录的指针长度 6 字节), 改变表指针长度, 修改表的 MAX_ROWS 和 AVG_ROW_LENGTH, 两者相乘 = 表可到达的 max 大小, 修改会导致重建整个表, 表 all 索引;
特性:
1, 对整张表加锁, 读, 共享锁, 写, 排他锁, 但在读的同时可从表中插入新记录: 并发插入
2, 修复: 可手工, 自动执行检查和修复操作, CHECK TABLE mytable 检查表错误, REPAIR TABLE mytable 进行修复, 执行修复可能会丢失些数据, 如果服务器关闭, myisamchk 命令行根据检查和修复操作;
3, 索引特性: 支持全文索引, 基于分词创建的索引, 支持复杂查询
4, 延迟更新索引键 Delayed Key Write, 如果指定了 DELAY_KEY_WRITE 选项, 每次修改完, 不会立即将修改的索引数据写入磁盘, 写入到内存的键缓冲区, 清理此区或关闭表时将对应的索引块写入到磁盘, 提升写性能, 但是在库或主机崩溃时造成索引损坏, 需要执行修复操作
压缩表:
表在创建并导入数据后, 不再修改, 比较适合, 可使用 myisampack 对 MyISAM 表压缩(打包), 压缩表不能修改(除非先解除压缩, 修改数据, 再次压缩); 减少磁盘空间占用, 磁盘 IO, 提升查询性能, 也支持只读索引;
现在的硬件能力, 读取压缩表数据时解压的开销不大, 减少 IO 带来的好处大得多, 压缩时表记录独立压缩, 读取单行时不需要解压整个表
性能:
设计简单, 紧密格式存储; 典型的性能问题是表锁的问题, 长期处于 locked 状态: 找表锁
1.5.3 内建的其他存储引擎
Archive: 适合日志和数据采集类应用, 针对高速插入和压缩优化, 支持行级锁和专业缓存区, 缓存写利用 zlib 压缩插入的行, select 扫描全表;
Blackhole: 复制架构和日志审核, 其服务器记录 blackhole 表日志, 可复制数据到备库 日志;
CSV: 数据交换机制, 将 CSV 文件作为 MySQL 表来处理, 不支持索引;
Federated: 访问其他 MySQL 服务器的代理, 创建远程 mysql 的客户端连接将查询传输到远程服务器执行, 提取发送需要的数据, 默认禁用;
Memory: 快速访问不会被修改的数据, 数据保存在内存, 不 IO, 表结构重启后还在但数据没了
1, 查找 或 映射 表 ,2, 缓存周期性聚合数据, 3, 保存数据分析中产生的中间数据
支持 hash 索引, 表级锁, 查找快并发写入性能低, 不支持 BLOB/TEXT 类型的列, 每行长度固定, 内存浪费
Merge:myisam 变种, 多个 myisam 合并的虚拟表
NDB 集群引擎:
1.5.4 第三方存储引擎
OLTP 类:
XtraDB 基于 InnoDB 改进, 性能, 可测量性, 操作灵活
PBXT:ACID/MVCC, 引擎级别的复制, 外键约束, 较复杂架构对固态存储 SSD 适当支持, 较大值类型 BLOB 优化
TokuDB: 大数据, 高压缩比, 大数据量创大量索引
RethinkDB: 固态存储
面向列的
列单独存储, 压缩效率高
Infobright: 大数据量, 数据分析, 仓库应用设计的, 高度压缩, 按照块 (一组元数据) 排序; 块结构准索引, 不支持索引(量大索引也没用), 如查询无法再存储层使用面向列的模式执行, 则需要在服务器层转换成按行处理
社区存储引擎:***
1.5.5 选择合适的引擎
除非需要用到某些 InnoDB 不具备的特性, 且无办法可以替代, 否则优先选择 InnoDB 引擎
不要混合使用多种存储引擎, 如果需要不同的存储引擎:
1, 事务: 需要事务支出, InnoDB XtraDB; 不需要 主要是 select insert 那 MyISAM
2, 备份: 定期关闭服务器来执行备份, 该因素可忽略; 在线热备份, InnoDB
3, 崩溃恢复: 数据量较大, MyISAM 崩后损坏概率比 InnoDB 高很多, 恢复速度慢
4, 持有的特性:
1.5.6 转换表的引擎
ALTER TABLE: 最简单
ALTER TABLE mytable ENGINE=InnoDB
此会执行很长时间, MySQL 按行将数据从原表复制到新表中, 在复制期间可能会消耗掉系统 all 的 I/O 能力, 同时原表上加读锁; 会失去和原引擎相关的 all 特性
导出与导入:
mysqldump 工具将数据导出到文件, 修改文件中 CREATE_TABLE 语句的存储引擎选项, 同时修改表名(同一个库不能存在相同的表名),mysqldump 默认会自动在 CREATE_TABLE 语句前加上 DROP TABLE 语句
创建与查询: CREATE SELECT
综合上述两种方法: 先建新存储引擎表, 利用 INSERT......SELECT 语法导数
- CREATE TABLE innodb_table LIKE myisam_table
- ALTER TABLE innodb_table ENGINE=InnoDB;
- INSERT INTO innodb_table SELECT * FROM myisam_table;
数据量大的话, 分批处理(放事务中)
1.6MySQL 时间线 Timeline
早期 MySQL 破坏性创新, 有诸多限制, 且很多功能只能说是二流的, 但特性支持和较低的使用成本, 使受欢迎; 5.x 早起引入视图, 存储过程等, 期望成为 "企业级" 数据库, 但不算成功, 5.5 显著改善
1.7MySQL 开发模式
遵循 GPL 开源协议, 全部源代码开发给社区, 部分插件收费;
1.8 总结
mysql 分层架构, 上层是服务器层的访问和查询执行引擎, 下层存储引擎(最重要)
来源: https://juejin.im/entry/5b640d87e51d4519700f965b