MySQL架构与历史

架构和历史

架构

三层架构

• 客户端 连接处理/授权认证/安全等
• 核心服务 解析，分析，优化，缓存 同时也是实现存储过程，触发器，视图的一个层面
• 存储引擎 负责：存储和提取 服务器通过api进行通信
• [外链图片转存失败,源站可能有防盗链机制,
• 

几个特性：

• 每个客户端在连接数据库的时候都会拥有独立的线程
• 优化器：请求存储引擎提供容量或者某个具体操作的开销信息，以及表数据的统计信息。
• 缓存机制 优先在缓存中查询找

锁

• 当两个事务同时到一个地方时候，此时为了错乱现象就出现了锁，当一个进去后就会对其加锁，只有等第一个完毕后第二个才会进去。
• 共享锁、排他锁 （读锁和写锁） 锁粒度：锁住的范围的大小
• 锁策略：在所得开销和数据的安全性之间寻求平衡。----一般为在表上施加行级锁，
  • 表锁 开销最小的策略 其中写锁比读锁有更高的优先级。
  • 行级锁：可以最大程度的支持并发处理 同时也带来了最大的锁开销 innoDB XtrDB 对于mysql 每一种存储引擎都可以实现自己的锁策略和锁粒度。
  • 

死锁

• 之两个或者多个事务在统一资源上相互作用，并请求锁定对方占用的资源，从而导致恶性循环的现象。
• 当多个事务识图以不同的书匈奴锁定资源时，就可能产生死锁。
• 多个事务同时锁定一个资源也会产生死锁
  • 比如两个事务一个将两外一个要执行的锁住了，这样两者就都会在等待对方释放锁，无限循环。除非外力
• 所以基于此，数据库实现了各种死锁检测和死锁超时机制目前InnoDB采取的方案是：将持有最少行级排他锁的事务进行回滚。
• 处理死锁：大多数情况下只需要重新执行因死锁回滚的事务即可。

事务

• 就是一组原子性的SQL查询，或者说是一个独立的工作单元。
• ACID：
  • 原子性（atomicity）：被分割为一个最小的工作单元。
  • 一致性（consistency）、
  • 隔离性（isolation）
  • 持久性（durability)
  • 一个运行良好的事务处理系统必须具备着这些标准特征。
• 事务日志
  • 写日志的操作是磁盘事务日志的方式相对来说要快很多，事务日志持久以后内存中被修改的数据在后台可以慢慢的刷会到磁盘。大多数都采取该方式。通常称之为预写式日志 修改数据需要写两次磁盘。
  • 就是说每次只需要将操作命令记录到持久在影片上的事务日志中，这样会快好多，然后系统慢慢的在将其刻录到硬盘上，如果在刻录前系统崩溃，当重启后，则会继续在持久日志中重新给磁盘刻录。
• mysql中的事务
  • 提供了两种事务类型的存储引擎：InnoDB、NDB Cluster 以及第三方的如：XtraDB、PBXT
  • 采取自动提交的模式 show variables like ‘autocommit’ set autocommiy = 1 其中 1/on是启用 0/off 表示禁用 如果为禁用时，当执行commit 或者rollback 才算一个事务的结束，同时开始一个新的事务。
  • 存在一些命令 在执行执行之前会强制执行commit提交当前的活动事务。如在事务定义语言中(DDL) 如果导致大量数据改变 如：alter table 、lock table
  • 设置隔离级别 set session transaction isolation level read commited; 目前mysql能够识别的有4个ANSI隔离级别 InnoDB支持所有的隔离级别。
  • 每张表选择合适的存储引擎非常重要。 如果在食物中混合使用了事务性和非事务性的表 eg InnDB、MyISAM 在正常提交是没有问题的但是在回滚的时候就会存在不一致的情况。
  • 隐式和显示锁定
    • InnoDB采用的 是两阶段锁定协议 会根据隔离界别在需要的时候枷锁 同时也支持通过特定的语句及逆行显示加锁。这些语句不属于sql规范。
      select … lock in share mode
      select … for update
      lock table、unlock table 在服务器层实现和存储引擎无关。
  • 一般建议：除非禁用了自动提交 才可以使用lock tables之外 其他热河时候都不要显示的执行lock tables 不管什么存储引擎

隔离级别

• 未提交读 read uncommitted 脏读 一般很少使用
• 提交读 read committed (大多数数据库默认的隔离级别) 一旦事务开始就只能案件自己提交的事务所作的修改。从头到尾都是不可见的 也叫不可重复读 因为两次可能查询出来的结果不一致
• 可重复读 repeated read 解决了脏读的问题 但是没有解决幻读的问题在该事务时其他数据进来了导致读取的数据不一致
• 可串行化 serializable 是最高的隔离级别 强制事务川航执行，避免前面的幻读 不过每一行都枷锁的话，可能导致大量的超时和锁争用的问题，，一般很少使用。

历史

具体的后面百度搜下 此处不再罗列

存储引擎

在系统中mysql将每个数据库（scheme）保存为数据目录下的一个子目录 创建表的时候mysql会在数据库子目录下的创建一个和表同名的.frm文件保存表的定义。

user表定义说明

• Name 表名
• engine 表的存储引擎 旧版中是type
• row_format 行格式
• rows
• avg_row_length 平均每行包含的字节数
• data_length 表数据的大小（字节）
• max_data_length 表数据的最大容量 存储值与存储引擎有关
• index_length 索引大小 字节
• data_free 在myisam 表示已分配但是目前么有使用的空间包括之前删除的行以及后续可以被insert利用的空间
• auto_increment 下一个auto_increment 的值
• create_time
• update_time
• check_time 最后一次查表时间
• collation 表默认字符集和字符排序规则eg:utf8_bin
• create_options
• comment 其他项

InnoDB存储引擎

• 默认事务引擎
• 被设计用来处理大量的短期任务 短期任务正常情况是正常提交的 甚少被回滚

历史

概览

• InnoDB 的数据存储在表空间中，是由InnoDB管理的一个黑盒子 由一些列的数据文件组成
• 在4.1版本之后，其可以将每个表的数据和索引存放在单独的文件中。
• 采用MVCC来支持高并发 并且实现了四个标准的隔离级别。其默认级别是可重复读并且通过间隙锁策略来防止幻读的出现
• 基于聚簇索引建立的
• 扩展阅读：InnoDB事务模型和锁
• 支持热备份

MyISAM存储引擎

• 不支持事务和行级锁
• 崩溃后无法安全恢复
• 最整张表加锁 读取时需获取整个表的共享加锁 写入时则需要夹排他锁
• 最典型的性能问题还是锁的问题

内建的其他存储引擎

• 还有一些其它用途的已经不在支持
• Archive引擎
  • 只支持insert 和select
  • 适合日志和数据采集类的应用
  • 需要全盘扫描、或者需要更快速的insert操作的场合
  • 支持行级锁和装用的缓存区 可以实现高并发的插入
• Blackhole引擎
  • 没有实现任何存储机制
  • 可以用于复制数据库到备库
  • 简单的记录到日志
  • 已存在很多问题 不推荐
• CSV引擎
  • CSV文件（逗号分隔值的文件）
  • 不支持索引
  • 作为一种数据交换的机制
  • 如果在excel中将文件存为CSV格式 将其复制到MyQL数据目录下就在在mysql中打开
• Federated引擎
  • 访问其他MySQL服务器的一个代理
  • 会创建一个远程MySQL服务器的客户端连接 并将查询传输到远程服务器执行，然后提取或者发送所需要的数据
• Memory 引擎
  • 快速的访问数据，并且这些数据不被修改 重启后丢失也没有关系 以前也叫HEAP表
  • 有用
  • 因为其存储在内存中不需要进行磁盘 I/O 所以其比MyISAM表要快一个数量级
  • 以下场景发挥更好的作用
    • 用于查找（lookup）或者映射（mapping） 例如邮编和州映射的表
    • 用户缓存周期性聚合数据的结果
    • 用于保存数据产生的中间数据
  • 支持Hash索引
  • 表级锁 并发写入的性能较低
  • 不支持BLOB TEXT 并且每行的长度是固定的
• 临时表和Memory表
  • 临时表：使用create temporart table 创建的表其可以存储任何引擎
  • 临时表只在单个连接中可见 切断后也就不复存在
• Merge引擎
  • MyISAM引擎的一个变种
  • 多个MyISAM合并的虚拟表
  • 常用于日志或者数据仓库类应用
  • 该表在引入分区功能后 已经废弃
• NDB集群引擎

第三方存储引擎

• OLTP 类引擎
• 面向列的存储引擎
• 社区存储引擎

选择合适的引擎

• 大多数InnoDB是正确的选择
• 事务
  • 如果需要事务 那么InnoDB或者XtraDB 是目前最稳定的并且经过严重的选择
  • 如果不需要事务 主要是select insert name MyISAM 一般日志应用等
• 备份
  • 需要在线热备份的话选择InnoDB是基本要求
• 崩溃恢复
• 特有的特性
• 日志型应用
  • 对插入速度有较高的要求
  • 选择MyISAN、Archive 其开销也低
• 大数据量
  • innoDB 3~5TB
  • 如果数据量增长到10TB 那么就需要建立数据仓库
  • Infobright 是mysql最成功的解决方案
  • TokuDB

转换表的引擎

• alter table
  • alter table mytable ENGINE = InniDB
  • 弊端：需要很长的时间 mysql会按行将数据从原表复制到一张新表中，在复制期间可能会系统的I/O 同时原表上面会读锁，所以在繁忙的表上执行操作的时候要特别的小心，
  • 替代的方案是手工的的导入导出
• 导入导出
  • 使用mysqldump 导出到文件—>修改文件中crate table 的存储引擎选项，注意同时修改表名，同时注意drop table
• 创建与查询
• percona Toolki 提供了一个pt-online-schma-change的工具

总结

• 核心：理解mysql在存储引擎和服务层之间处理查询时如何通过API来回交互
• 最初基于ISAM构建—>后来被MyISAM取代
• oracle 将InnoDB以及MySQL收购 也是希望两者可以更快的协同发展
• MySQL依然基于GPL协议开发全部源代码