1 mysql基本架构
1.1 架构
三层
- 线程 连接处理
连接处理,授权认证,安全等
每个客户端在服务器中有一个线程用以查询,线程轮流在CPU中运行,服务器进行线程缓存,无需为新建的连接创建或销毁线程。(新增线程池)
SSL连接,然后继续验证是否具备某个特定查询的权限。
- 查询 缓存 解析(核心)
查询解析,分析,优化,缓存,内置函数(日期,时间,加密函数,math等)。夸存储引擎功能实现:存储过程,触发器,视图
解析查询——创建内部数据结构(解析树)——优化(重写查询,军队表读取顺序,选择合适的索引) (优化器 hint提示优化器,explain解释决策优化)
优化器不关心存储引擎具体,但是会请求引擎提供容量或者某个具体操作的开销,以及表数据的统计信息(某系引擎索引可以对特定查询优化)。
select解析查询前先查询缓存,能找到对应的查询则不必再执行查询解析,优化与执行的过程而直接返回查询结果
- 存储引擎
服务器通过API与存储引擎同学,接口屏蔽不同存储引擎的差异,使查询透明。 底层函数很多 用于执行事务或者提取操作。(不解析sql(innodb会),引擎之间无通信,仅提供向上的支持)
1.2 并发控制
对于普通的锁服务,锁会造成等待,资源浪费。
1.2.1 读写锁
共享锁 读锁:相互不阻塞,多个客户可以在同一时刻读取同样的资源,互不干扰(读锁的存在意义:与写锁互斥,与读锁相容)
排他锁 写锁:阻塞,同一时间只能执行一个写操作,锁定时也会导致不能读
1.2.2 锁粒度
锁定数据量越少 并发越高
加锁需要消耗资源(加锁,检测锁,释放锁)
需要在锁开销与安全性之间寻求平衡
表锁:最小的锁开销,用户写操作会锁定整张表,阻塞其他读写操作,写锁清秋可能会插入读锁前面。
行锁:最大的锁开销,最大程度支撑并发,innodb xtradb等实现,仅在存储引擎层实现,
1.3 事务
事务是一组Atomic的SQL查询,独立的工作单元。
ACID
原子性:全部失败回滚或全部成功
一致性:从一个一致性状态转为另一个一致性状态
隔离性:通常来说,事务执行的修改其他事务不可见
持久性:一旦事务提交,所做的修改就会永久保存到数据库
并发操作带来的数据不一致性:
- 更新丢失:两个事务同时更新一行数据,因为没有锁机制,导致一个事务的更新覆盖了另一个
- 脏读:一个事务读取到另一个人事务为提交的结果,危险,回滚
- 不可重复读:一个事务对同一行数据读取两次,得到不同的结果
- 虚读:T1读取某一数据,T2对其修改,T1再读时与之前不同值
- 幻读:事务执行过程中,一二次查询结果之间出现了不一样的数据,由于执行过程中另一个事务插入数据造成
1.3.1 事务隔离等级:
- 未提交读:(只处理更新丢失)没上排他锁时,写操作执行中数据被读导致脏读(排他写锁)
- 提交读:(处理更新丢失与脏读)大部分数据库默认的隔离级别,mysql不是 。读取数据的事务允许其他事务继续访问,未提交的写事务禁止其他事务(瞬间共享读锁+排他写锁)
- 可重复读: (处理更新丢失,脏读以及不可重复读)读取事务禁止写,允许读,读事务禁止吉他事务(共享读锁+排他写锁)
- 序列化:(严格事务隔离)一个接一个执行,不能并发执行(行锁无法实现,需要其他类似于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