读书笔记 -- 高性能 MySQL 第一章: MySQL 架构与历史

摸鱼研究员
233 阅读10分钟

每一种锁都有自己的使用场景, 性能好不好看使用的场景

名词

  • 索引选择性: 不重复索引的值(也叫基数, cardinality 就是那个 show indexes from xx_table ) 和数据表的记录总数(T)的比值, 范围是 [1/T , 1] 唯一性索引的索引选择性是 1, 主键索引也是一种特殊的唯一索引, 所以主键索引的选择性也是 1.
  • schema: 数据库

MySQL 架构

MySQL 架构图 存储引擎层 图片来自网络 MySQL 8.x 之后没有了查询缓存组件了 MySQL 服务器逻辑结构图 最上层的服务并不是 MySQL 独有的, 大多数 c/s 的服务都是类似的结构, 负责 链接处理, 授权认证, 安全等等

第二层: 大多数 MySQL 的核心服务功能都在这一层, 包括查询缓存(8.x 之后彻底去掉了这个组件), 分析, 优化, 缓存以及所有的内置函数(比如, 日期, 时间, 数学, 加密函数), 所有的跨存储引擎的功能都在这一层实现: 存储过程, 触发器, 视图

第三层: 包含了存储引擎层. 存储引擎负责 MySQL 中数据的存储和提取. 每个存储引擎都有它的优势和劣势. 服务器通过 API 与存储引擎进行通信, 屏蔽了不同引擎之间的差异.

链接管理与安全性

每个客户端都会在服务器进程中拥有一个线程, 这个线程的查询只会在这个单独的线程中执行, 该线程只能轮流在某个 cpu 核心或者 cpu 中运行(它们是互相独立的), 服务器会负责缓存线程, 因此不需要为每个线程的创建或者销毁线程.

优化与执行

MySQL 会解析查询, 并创建内部的数据结构(解析树)(语法错误是在解析器层抛出的), 然后优化器对其进行优化, 包括重写查询语句, 决定表的读取顺序(JOIN 表可能的个数为 n 的全排列), 以及选择合适的索引(选错索引是在优化器层)等等, 可以通过关键字选择指定的索引或者忽略指定的索引等等操作

优化器并不关心表使用的是什么存储引擎, 到那时存储引擎对于优化查询是由影响的。优化器会请求存储引擎提供容量或者某个具体操作的开销, 已经表的统计信息等(索引的选择性行对于优化器使用哪个索引影响很大)

并发控制

无论何时, 只要多个查询在同一时刻修改数据, 都会产生并发控制的问题.

读写锁的问题

  • 读锁(共享锁), 可以多读, 读写互斥, 写写互斥
  • 写锁(排他锁), 读写互斥, 写写互斥

在编程语言的实现和硬件平台有关

锁的粒度区分

  • 表锁 table lock, 锁住整张表, 并且是开销最小的策略. 只有没有写锁时, 其他的用户才能获得读锁, 读锁之间是不互相互斥的, 在特定的场景, 表示也是有良好的性能的, 比如 read local 表锁, ALTER TABLE 之类的语句会使用表锁, 忽略存储引擎的锁机制
  • 行锁 row lock, 行级锁可以最大限度地支持并发处理 (同时也带来最大的锁开销). 行级锁只在存储引擎层实现, 而 MySQL 服务器层实现

事务

事务就是一组原子性的 SQL 查询, 或者说一个独立工作单元. 如果数据库引擎能够成功地对数据库应用该组查询的全部语句, 那么就执行该组查询. 如果其中有任何一条语句因为崩溃或其他原因无法执行, 那么所有的语句都不会执行. 也就是说事务内的语句, 要么全部执行成功, 要么全部执行失败.

事务的四个特征: A: 原子性: 对于一个事务来说, 要么全部成功, 要不全部失败 C: 一致性: 数据从一个一致性转到另外一个一致性(最重要的特征) I: 隔离性: 一个事务所所的修改在事务提交之前对其他的事物不可见(Innodb 中通过 MVCC 来实现) D: 持久性: 一个事务, 一旦提交, 则其所做的修改就会永久保存到数据库中. 即使系统奔溃了, 数据也不会丢失

支持事务的引擎可以选择事务来保护数据的一致性, 如果是不支持事务的引擎, 可以选择锁表来保护数据的一致性

事务隔离级别:

READ UNCOMMITTED(读未提交): 事务中的修改, 即使没有提交, 对其他的事务也是可见的. 一个事务可以读取其他事务未提交的数据 READ COMMITTED (读提交) RC: 一个事务从开始知道提交结束之前只能看到已经提交的事务提交的数据. REPEATABLE READ (可重复读) RR: 这个隔离级别保证了同一个事物中多次读取同样的数据的结果是一致的. 可重复读隔离级别没有解决的问题, 幻读: 指某个事务在读取某个范围的记录时, 另外一个事务又在该范围内插入新的记录, 当之前的事务再次读取到该范围的记录时会产生幻行. MVCC 解决了幻读的问题(使用间隙锁)

隔离级别脏读可能性不可重复度幻读加锁读
READ UNCOMMITTEDYYYN
READ COMMITTEDNYYN
REPEATABLE READNNYN
SERIALIZABLENNNY

dead lock 问题

死锁: 是两个或者多个事务在同一资源上相互占用, 请求锁定对方占用的资源 从而导致恶行循环的现象. 当多个事务视图以不同的顺序锁定资源时, 就会产生死锁.

InnoDB 的两阶段锁协议: 等到需要锁的时候才申请锁, 等到事务提交或者事务回滚的时候才释放锁.

事务的日志

事务的日志可以提高事务的效率. 使用事务日志, 存储引擎在修改表数据时, 只需要修改其内存的拷贝, 再把对应的修改行为保存追加到日志文件中, 而不用每次修改的时候都将修改的数据持久化到磁盘. 事务日志采用追加的方式, 因此写日志的操作再小块内存上是顺序 i/o, 而不是像随机 i/o 需要在磁盘的多个地方移动磁头, 所以事务日志的方式快得多(同样的技术: 刷脏页, change buffer, insert buffer, 刷邻近页, LRU 等等), 事务日志被持久化后, 内存中被修改的数据在后台慢慢地刷会磁盘, 通常称这种技术为预写日志的方式(Write Ahead Logging), 修改数据需要两次写磁盘(一次是写日志, 一次是刷脏页)

MySQL 中的事务

MySQL server 社区版本默认 Innodb 是支持事务的,

MySQL 提供两种事务类型的存储引擎, InnoDB 和 NDB cluster;

  • 自动提交事务, autocommit 默认每个查询语句都是一个事务执行.

多版本并发控制

MVCC (Multi Version Concurrency Control), 主要是实现非阻塞读, 写操作也只是锁定必要的行. MVCC 的实现, 是通过保存数据在某个时间点的快照来实现的. 也就是说, 不管需要执行多长时间, 每个事务看到的数据都是一致的(只是对于 RR 隔离级别来说是一致的). 根据事务开始的时间不同, 每个事务对同一张表, 同一时刻看到的数据有可能不一致的(比如数据有发生修改操作).

InnoDB 的实现简化版本: 通过在每行记录的后面保存连个隐藏的列来实现的, 这两个列, 一个保存创建的 事务 系统版本号(创建事务的版本号), 一个保存删除的版本号(删除事务的版本号), 事务的版本号: 事务开始时候, 系统赋值的版本号, 系统的版本号是递增的.

REPEATABLE READ(RR) 隔离级别下的, MVCC 的具体操作.

SELECT

InnoDB 会根据以下连个条件检查每行的记录:

  1. InnoDB 只会查找版本查找版本号早于当前事务版本的数据行(低水位行) (也就是, 行的系统版本号要小于或等于当前事务版本号, 如果版本号等于当前的事务 id, 表示这行的数据是当前事务创建的或者修改的), 这样可以确保读取到事务的行, 要么在事务开始之前已经存在, 要么是事务自身插入或者修改的.
  2. 行的删除版本要么是未定义的, 要么大于当前事务的版本号, 这样可以确保读取到的行在事务开始之前没有被删除

INSERT

InnoDB 为新插入的每一行保存当前的系统版本号作为行的版本号

DELETE

InnoDB 为删除的每一行保存当前的系统版本号作为行删除的标识

UPDATE

InnoDB 为插入一行新记录, 保存当前的系统版本号作为行的版本号, 同时, 保存当前的系统版本号到原来的行作为删除的标识

保存这两个额外的系统版本号, 使大多数的读操作都可以不用加锁. 这样设计操作简单, 性能好. 并且保证读取到符合标准的操作(低水位的版本不会读取到高水位的数据), 不足就是同一行的数据可能需要维护多个版本, 读取数据的时候, 需要进行版本号的比较.

MVCC 只在 REPEATABLE READ(RR) 和 READ COMMITTED(RC) 两个隔离级别下才工作. 其他的两个隔离级别下, 不兼容. READ UNCOMMITTED 总是读取最新的行数据, 不需要数据的多个版本. SERIALIZABLE 对数据的所有操作都是加锁的.

SHOW　TABLE STATUS;

mysql> show table  status LIKE 't2'\G
*************************** 1. row ***************************
          Name: t2
        Engine: InnoDB
       Version: 10
    Row_format: Dynamic
          Rows: 2
Avg_row_length: 8192
   Data_length: 16384
Max_data_length: 0
  Index_length: 16384
     Data_free: 0
Auto_increment: 3
   Create_time: 2020-11-02 19:18:28
   Update_time: 2020-11-02 19:18:49
    Check_time: NULL
     Collation: utf8_general_ci
      Checksum: NULL
Create_options:
       Comment:
1 row in set (0.00 sec)
  • name: 表名
  • Engine: 表使用的存储引擎
  • Row_format: 行的格式, dynamic, Fixed, 和 compressed
    1. dynamic: 标识可变的, 一般包含可变的字段
    2. fixed: 固定varchar(x), 排序的时候, 会分配足够的空间, 对于空间不好
  • Rows: 表中的行数, Innodb 是估值(类似于基数 Cardinality), MyISAM 精确保存
  • Avg_row_length: 平均每行包含的字节数
  • Data_length: 表数据的大小
  • Max_data_length: 表数据的最大容量
  • Index_length: 索引的大小(以字节为单位)
  • Data_free: 对于 MyISAM 表, 表示已经分配但目前没有使用空间. 这部分空间包括了之前删除的行, 已经可以被 INSERT 利用的空间
  • Auto_increment: 下一个 Auto_increment 的值, 全局变量, 获取的时候需要上锁
  • Create_time: 创建表的时间 (测试的时候, 使用 alter 语句修改表, 这个时间也是会变(获取可以叫做 表的更新时间才对))
  • Update_time: 表数据的最后修改时间
  • Collation: 表的默认字符集和字符排序规则(校对集)
  • Checksum: 如果启用, 保存的是整个表实时校验和
  • Create_options: 创建表的其他选项
  • Comment: 注释信息

转换表的存储引擎

ALTER TABLE

导入导出

创建查询(CREATE 和 SELECT )

CREATE　TABLE InnoDB_table LIKE myisam_table; # 创建和 mysisam 一样的表
alter table innoDB_table engine=innodb; # 转换表的存储引擎
insert into innodb_table select * from myisam_table; # 插入数据

其他的..... 没有重点看... 就不记了

avatar
文章
阅读
粉丝