mysql

高性能Mysql笔记-第1章

第一章 架构与历史

逻辑架构:
客户端
=>连接/线程处理
=>查询缓存/解析器
=>优化器
=>存储引擎.

其中只有innodb引擎会解析SQL中的外键定义.其他引擎不会解析SQL.
所有内容都应该同时关注两个层面的实现: 服务器层和存储引擎层.

1.1.1 连接管理

每个客户端连接都会在服务器进程中拥有一个线程,这个连接的查询只会在这个单独的线程中执行. 服务器会负责缓存线程,因此不需要为每个新连接创建或销毁线程.

1.1.2 优化与执行

首先查询缓存中有没有,如果没有:

Mysql会解析查询,并创建内部数据结构(解析树),然后进行优化,包括重写查询,决定表的读取顺序以及选择合适的索引.

用户可以使用hint指令指导优化.

优化器并不关心底层存储引擎的具体实现,但会向底层存储引擎请求一些数据.

1.2 并发控制

1.2.1 读写锁

读锁: 共享锁,不干扰其他锁.
写锁: 排他锁,排除其他读写锁.

1.2.2 锁粒度

  • 表锁
    开销最小,但并发度低.
    // ALTER TABLE语句都会使用表锁,不管底层引擎是什么.

  • 行锁
    最大程度的并发,但也有最大的锁开销. 由存储引擎各自实现.

1.3 事务

基本流程:

  1. Start transaction;
  2. 执行一组SQL
  3. commit.

事务的4个指标(ACID):

  1. 原子性: 要么全部成功,要么全部回滚.
  2. 一致性: 事务中间状态的修改不会保存到数据库中.(感觉和原子性有重合)
  3. 隔离性: 事务在最终提交之前,对其他事务是不可见的.
  4. 持久性: 一旦事务提交,修改就会永久保存到数据库中.

1.3.1 隔离级别

4种隔离级别(由低到高):
1.Read uncommitted:

未提交读. 有脏读. 能读到别的事务还没commit的修改. 太弱, 基本不用.

2.Read committed (Mysql以外的数据库默认级别)

提交读. 一个事务的修改,提交前对其他事务不可见.
存在不可重复读问题.
比如如下流程:

  1. A事务读V;
  2. B事务提交V;
  3. A事务读V.
    由于B事务中间提交了一次,A事务两次读到的值不一样,也就是不可重复读.

3.Repeatable Read (Mysql默认级别)

可重复读. 可以重复读单条记录. (方法,InnoDb增加多版本并发控制MVCC)
存在幻读问题.
比如如下流程:

  1. A事务读范围行Vs;
  2. B事务插入v到Vs中;
  3. A事务读范围行Vs.
    由于B事务中交提交了一次,A事务两次读到的范围行不一样,也就是幻读.
    (可以用MVCC+间隙锁解决.)

4.Serialable

可串行化. 解决了幻读.
强制事务串行执行. 增加了每一行锁.

总结问题:

  1. 未提交读: 脏读,不可重复读,幻读.
  2. 提交读: 不可重复读,幻读.
  3. 可重复读: 幻读.
  4. 可串行: 慢.

mysql默认是可重复读,解决正确使用的幻读，使用MVCC+间隙锁;
MVCC: 解决两次快照读的幻读; (两次快照读一定一样)
间隙锁: 解决两次当前读的幻读; (两次当前读一定一样)

而，快照读和当前读的结果对于mysql的可重复读级别来说，可能结果不一样。
因此我们定义的正确使用: 程序￿员不应假设”快照读”和”当前读”的结果一样。

快照读: select xxx
当前读: select xxx for update; 或者update/insert语句中隐含的读(基于当前数据库的数据状态)

如果在最高隔离级别(串行)下，事务中的所有读都会被加上间歇锁，
因此保证了快照读和当前读的结果。// 也就是即使程序员不懂这方面知识、不正确使用，也能得到正确的结果。

1.3.2 事务死锁

Innodb:
检查到死锁的循环依赖
=>立即返回错误.

  • 处理死锁:
    将持有最少行级写锁(排他锁)的事务进行回滚.

1.3.3 事务日志

WAL(Write-Ahead Logging) 异步刷盘.

1
2
1. 数据追加写到日志里(顺序IO);
2. 数据修改到原数据里. (随机IO).

第二步如果失败崩溃,可以利用第一步的日志修复.

1.3.4 Mysql中的事务

Mysql事务型引擎: InnoDB, NDB Cluster. (MyIsam不支持事务)
第三方引擎: XtraDB,PBXT
InnoDb支持所有4个隔离级别.

自动提交
Mysql默认使用自动提交. 如果不显式得开始一个事务,每个查询都被当成一个独立的事务.

可能导致强制提交事务的命令:

  1. Alter Table
  2. Lock Tables
  3. 其他导致大量数据更改的语句…

在事务中混合使用存储引擎(不推荐)

  • 服务层: 不管理事务;
  • 存储引擎: 具体实现事务.

混合使用InnoDb,MyISAM: (事务型表+非事务型表)

回滚时,非事务型表上的变更无法撤销, 导致数据库处于不一致状态.

隐式和显式锁定
InnoDb采用两阶段锁定协议. 

1
2
加锁: 事务执行过程中随时加锁;
解锁: 仅当Commit或RollBack时.

隐式加锁: 存储引擎自动加锁
显式加锁: 明确指定语句: Select ... Lock In Share Mode

  • tip

    尽量不要显式加锁,而是交给Innodb实现. 以避免无法预料的错误.
    (除非研究得很深了.)

1.4 多版本并发控制 MVCC

MVCC: 行级锁的一个变种. 在很多情况下避免了加锁操作, 开销更低.
大多实现了非阻塞的读操作, 写操作只锁定必要的行.

  • 实现
    每个事务开始的时候保存数据在某个时间点的快照.

InnoDb的MVCC(空间换时间,少加锁)

每行记录后保存两个隐藏列:

  1. 行的创建系统版本号;
  2. 行的删除系统版本号.

系统版本号反映了时间.
每开始一个新的事务,系统版本号都会递增.
事务开始时刻的系统版本号会作为事务的版本号,
用来和查询到的每行记录的版本号比较.

##Innodb的可重复读级别下
Select

1
2
1. 创建版本号<=当前事务版本的数据;
2. 删除版本号>当前事务版本的数据 (或者没有删除).

Insert

1
1. 插入新行,创建版本号=当前事务版本号.

Delete(标记删)

1
2
1. 删除的行,删除版本号=当前事务版本号.
(打标记,而不是真的删)

Update(标记删+插入新)

1
2
1. 插入新行,创建版本号=当前事务版本号.
2. 原来的行,删除版本号=当前事务版本号.

底层原理

实际实现参见: https://liuzhengyang.github.io/2017/04/18/innodb-mvcc/
是将旧版本的行存在undo log中，如果没有依赖这部分undo log的事务结束了(提交或者回滚),
这部分undo log是会销毁的，因此不会造成永久的存储负担。

MVCC与隔离级别:

  1. 读未提交: 不使用MVCC;
  2. 读已提交: 使用MVCC; // 可能版本号条件不同
  3. 可重复读: 使用MVCC+当前读间隙锁; // 解决正确使用情况下的幻读
  4. 可串行读: MVCC+快照读和当前读都有间隙锁. // 解决所有情况下的幻读

MVCC相当于乐观锁或者无锁、空间换时间；
间隙锁相当于悲观锁。真实地锁了索引。

1.5 Mysql的存储引擎

  • 每个数据库: 一个目录

  • 每个数据表:

  1. 元数据(表定义): 表同名的.frm文件; (服务层统一实现)
  2. 数据; (存储引擎分别实现)
  3. 索引. (存储引擎分别实现)

元数据(表定义具体内容)

表的相关属性:

  • Name
  • Engine
  • Row_format:
  1. Dynamic: 行长度可变,包含Varchar或Blob的行.
  2. Fixed: 只包含固定长度列.
  3. Compressed: 压缩表.
  • Rows: 行数. Innodb:估算值. MyISAM: 精确值;
  • Index_length: 索引长度
  • Collcation: 默认字符集和字符排序规则.
  • 其他信息…

1.5.1 InnoDB存储引擎

设计目标: 大量短期事务.
Mysql主推引擎.

存储
数据: 表空间中,一系列数据文件组成.
索引: 表空间中,独立的索引文件.

隔离级别的实现:

  1. MVCC;
  2. 间隙锁: 不仅锁定查询涉及的行,还锁定索引中的间隙,防止幻读.

存储格式:(跨平台)

  1. 主键: 指向物理地址;
  2. 二级索引: 非主键索引,指向主键,所以主键应尽可能小.

MyISam

存储

  1. 数据: .MYD
  2. 索引: .MYI

变长行: 默认最大数据量256TB,因为指向数据记录的指针长度是6B.
可以修改Max_Rows和Avg_row_length的值,来改变指针长度.
(两者相乘=表最大容量)

并发
整张表加锁.

索引
支持全文索引,基于分词创建的索引.

压缩表
不再修改的表可以进行压缩,提高查询性能,减少磁盘空间.

1.5.3 其他引擎

Archive: 只支持插入和查询,不支持修改和删除.
BlackHole: 丢弃所有插入的数据,只记录日志. 有很多问题.
CSV: 存储CSV文件. (这个场景应该考虑使用Sql Server)
Federated: 访问其他Mysql服务器的代理,默认禁用.

  • Memory: 磁盘只保存结构,数据在内存,重启后丢失数据.
    使用场景:
  1. 查找或映射表;// 邮编和州名的映射表;
  2. 缓存周期性聚合数据;
  3. 保存数据分析中产生的中间数据.

限制:

  1. 表级锁;
  2. 只支持定长列, 如果指定了varchar,会转换成char.

Merge引擎:
多个MyIsam表合并的虚拟表;
引入分区功能后弃用.

NDB引擎:
Mysql集群版.

1.5.5 选择合适的引擎

场景:

  1. 全文索引: InnoDB + Sphinx
  2. 在线热备份: InnoDB
  3. 记录日志: MyISAM,Archive
  4. 10TB以下: InnoDB
  5. 10TB以上: InfoBright.
  6. 大部分情况优先考虑: Innodb

推荐文章