高性能mysql 第一章（架构与历史）

1 mysql基本架构

1.1 架构
三层

1. 线程 连接处理
   连接处理，授权认证，安全等

每个客户端在服务器中有一个线程用以查询，线程轮流在CPU中运行，服务器进行线程缓存，无需为新建的连接创建或销毁线程。（新增线程池）
SSL连接，然后继续验证是否具备某个特定查询的权限。

2. 查询 缓存 解析（核心）
   查询解析，分析，优化，缓存，内置函数（日期，时间，加密函数，math等）。夸存储引擎功能实现：存储过程，触发器，视图

解析查询——创建内部数据结构（解析树）——优化（重写查询，军队表读取顺序，选择合适的索引） （优化器 hint提示优化器，explain解释决策优化）
优化器不关心存储引擎具体，但是会请求引擎提供容量或者某个具体操作的开销，以及表数据的统计信息（某系引擎索引可以对特定查询优化）。
select解析查询前先查询缓存，能找到对应的查询则不必再执行查询解析，优化与执行的过程而直接返回查询结果

3. 存储引擎
   服务器通过API与存储引擎同学，接口屏蔽不同存储引擎的差异，使查询透明。 底层函数很多 用于执行事务或者提取操作。（不解析sql（innodb会），引擎之间无通信，仅提供向上的支持）

1.2 并发控制
对于普通的锁服务，锁会造成等待，资源浪费。

1.2.1 读写锁
共享锁 读锁：相互不阻塞，多个客户可以在同一时刻读取同样的资源，互不干扰（读锁的存在意义：与写锁互斥，与读锁相容）
排他锁 写锁：阻塞，同一时间只能执行一个写操作，锁定时也会导致不能读

1.2.2 锁粒度
锁定数据量越少 并发越高
加锁需要消耗资源（加锁，检测锁，释放锁）
需要在锁开销与安全性之间寻求平衡
表锁：最小的锁开销，用户写操作会锁定整张表，阻塞其他读写操作，写锁清秋可能会插入读锁前面。
行锁：最大的锁开销，最大程度支撑并发，innodb xtradb等实现，仅在存储引擎层实现，

1.3 事务
事务是一组Atomic的SQL查询，独立的工作单元。
ACID
原子性：全部失败回滚或全部成功
一致性：从一个一致性状态转为另一个一致性状态
隔离性：通常来说，事务执行的修改其他事务不可见
持久性：一旦事务提交，所做的修改就会永久保存到数据库
并发操作带来的数据不一致性：

1. 更新丢失：两个事务同时更新一行数据，因为没有锁机制，导致一个事务的更新覆盖了另一个
2. 脏读：一个事务读取到另一个人事务为提交的结果，危险，回滚
3. 不可重复读：一个事务对同一行数据读取两次，得到不同的结果
4. 虚读：T1读取某一数据，T2对其修改，T1再读时与之前不同值
5. 幻读：事务执行过程中，一二次查询结果之间出现了不一样的数据，由于执行过程中另一个事务插入数据造成

1.3.1 事务隔离等级：

1. 未提交读：（只处理更新丢失）没上排他锁时，写操作执行中数据被读导致脏读（排他写锁）
2. 提交读：（处理更新丢失与脏读）大部分数据库默认的隔离级别，mysql不是 。读取数据的事务允许其他事务继续访问，未提交的写事务禁止其他事务（瞬间共享读锁+排他写锁）
3. 可重复读： （处理更新丢失，脏读以及不可重复读）读取事务禁止写，允许读，读事务禁止吉他事务（共享读锁+排他写锁）
4. 序列化：（严格事务隔离）一个接一个执行，不能并发执行（行锁无法实现，需要其他类似于MQ的机制支持）

1.3.2 死锁
多个事务执行时相互请求对方占用的资源，造成恶性循环的现象。
1 innodb可以很快检测到死锁的循环依赖返回错误（可以将持有最少航迹排他锁的事务进行回滚）
2 锁超时，放弃锁请求（不太好）
一般来说 部分或者完全回滚一个事务才能打破死锁，然后重新执行回滚的事务即可
越是轻量的事务，占有越少的锁资源，死锁概率更低。
措施：避免子查询，尽量使用主键；大事务拆分为小事务；尽快提交事务，减少锁时间；尽量一次性锁定资源；添加合理的索引

1.3.3 事务日志
存储引擎修改数据时先 修改内存拷贝，然后将行为记录到事务日志（追加的方式），使用磁盘一小块io，相对快很多。
事务日志持久化后，内存中修改的数据可以在后台慢慢刷回磁盘。两次写入磁盘
事务日志写入磁盘但是内存尚未持久化，机器宕机，重启时会按照事务日志恢复数据。

1.3.4 mysql的事务
执行事务的引擎：innodb NDB cluster（第三方 XtraDB PBXT）

自动提交：
autocommit 0所有查询在一个事务，知道现实执行commit或者rollback 事务结束。 对于mysiam而言无变化，一直处于开启状态
set session transaction isolation level read committed

1.4 多版本并发控制
使用MVCC可以做到多版本并发控制
行级锁的变种，版本号控制
事务导致版本号增加，事务插入更新则更新行版本号，select选取早于版本号前的数据行，delete删除则更新删除标识
MVCC不是乐观锁，但是innodb的MVCC是乐观的，属于乐观锁手段
只能在 可重复读 提交读中实现

1.5 存储引擎简介
innodb

处理大量短期事务，自动崩溃恢复。
特性：
利用排序创建索引，删除或者增加索引不需要复制全表数据，新的支持压缩的存储格式，新的大型列值BLOB。
tablespace中存储数据
MVCC支持高并发 默认可重复读级别
间隙锁防止幻读
聚簇索引 主键查询提高性能 二级索引中必须包含主键列
可预测性预读，自动在内存中hash索引加速读，加速插入操作的缓冲区

myisam

全文索引，压缩，不支持事务和行锁，崩溃后无法恢复
只读数据或者小表，可容忍修复
最大256T
表锁（很影响性能）
共享读锁+排他写锁，读的时候可以写（并发插入）
手动修复
delay_key_write选项提供内存缓冲区（cache）知道需要清理或者关闭表时持久化
压缩表，IO提升，解压开销小

其他：
CSV csv交互
ARchive insert select IO极低
Blackhole 记录日志
memory 类cache重启时失效，支持hash索引，超出自动转myisam
NDB 集群容灾
第三方：
XtraDB innodb的改进
PBXT ssd支持

无需事务，只是select+insert的话 mysiam比较不错
在线热备 innodb好
崩溃恢复 innodb好
myisam支持地理空间收索

innodb 性能换效率的做法（相对）

引擎转换：
Alter table
导出导入 mysqldump
创建+查询 create+select