MySQL 存储结构

  1. B-tree 是平衡的多路查找树。
  2. 涉及到磁盘的查找需要设法减少磁盘 I/O 次数。
  3. B-tree 就是为解决这个问题而引入的数据结构。
  • 区别于二叉树 b-tree 可以拥有很多个子节点（这个度量被称为「内结点出度」)
  • 我们可以在技术上使 B-tree 的结点大小为磁盘一个页的大小，并且在新建结点时直接申请一个页大小的空间，使得结点的物理存储位置也是在一个页里，这样就能实现存取一个结点只需一次磁盘 I/O
  • 在最坏情况下，B-tree 的一次检索最多需要 H（树的高度）次的磁盘 I/O
  • 实际上，为了取得更大的内结点出度，各个数据库一般会采用 B-tree 的变种如 B+-tree，B*-tree 来实现索引，比如 MySQL 的存储引擎 InnoDB 就采用 B+-tree 来实现聚簇索引

索引

  • 字符串索引，长度限制 innodb 存储引擎，默认前缀长度最大能支持 767 字节；而在开启 innodb_large_prefix 属性值的情况下，最大能支持 3072 字节
  • 前缀索引，后缀索引，手动 md5 哈希索引
  • innode 内置哈希
  • Cardinality 不重复值预估，除以记录总数的比例 尽量接近 1 索引的价值越大
  • 查询优化器 选择索引时会考虑这个值
  • oltp olap
  • Online Analytical Processing
  • Online transaction processing

索引细节

  • 单列索引币复合索引在每个数据页存的记录要多，所以查询优化器优先使用单列索引
  • 覆盖索引
  • 数据最小读取单位 (索引页？)
  • count(*) 操作，实际会读取辅助索引，避免读取聚合索引
  • 统计操作，覆盖索引的情况下，可以直接查询复合索引 (a,b) 中的 b
  • index hint 索引提示 use index 只是提示，force index 才是强制
  • multi-range read 优化 从辅助索引筛选完之后，将结果，已主键进行排序，再去读聚合索引下的记录行
  • index condition pushdown（IPC）优化，将 where 过滤条件推送到存储引擎，减少数据传输 (使用时会提示 using index condition)
  • innodb 全文索引 使用倒排索引实现，使用了 FTS Index Cache 缓存数据变更，批量更新到 Auxiliary Table 中 ( 这个表可以通过关键词定位到文档，单词位置)

锁

  • myisam 只支持表锁，sql server 2005 版之前只支持页锁，2005 开始支持行锁，但是实现方式与 innodb 不同，加锁会有资源开销，innodb 则与 oracle 的锁实现类似

  • lock 锁，与 latch 锁，lock 用于事务，latch 用于保证并发下的数据一致性（临界资源）

  • 查看 latch 锁 show engine innodb mutex;

  • 查看 lock 锁 show engine innodb status;

  • 共享锁 s Lock，允许事务读一行数据，共享锁可以叠加，称为锁兼容

  • 排他锁 x Lock，允许事务删除或者更新一行数据

  • 意向锁 (Intention Lock) ,对子级上锁，需要怼父级上意向锁，s 锁，对应 is,x 锁对应 ix

  • 查看锁的情况，show full processlist,show engine innodb status, information_schema 下的，innodb_trx,innodb_locks,innodb_lock_waits 等三张表

  • 如果没有合适的索引，则 innodb 会使用主键来进行锁定 (可能会造成表锁)

  • 索引含有唯一属性时，where id=1 类似的查询 Next-Key Lock 会降级为 Record Lock

锁的问题

  • 脏读 (read uncommited 级别下) 脏数据是事务对缓冲池中的行记录的修改，并且没有被提交 (commit),脏读就是读到了未提交数据

  • 不可重复读 (read commited 级别下) 在当前事务两次读取不一致，第二次读到了其他事务提交的数据

  • 丢失更新 一个事务的更新，被另外一个事务覆盖，数据库本身不会发生这个错误，程序缓存变量值再写入时可能发生

  • 阻塞 innodb 默认不会回滚阻塞超时引发的异常

  • 死锁 基础是等待一方超时，innodb 还采用 wait-for graph(等待图) 深度优先算法 采用递归实现 (innodb 1.2 之后采用非递归方式实现递归)

  • 发生死锁的因素 1.并发事务数量 2.每个事务操作的数量 3.操作数据的集合大小，集合越大越不容易冲突

  • innodb 一般情况出错，不会回滚事务，但是死锁除外，死锁时，innodb 会回滚其中一个事务，死锁报错 (1213)

事务

  • ACID 原子性 (atomicity) 一致性 (consistency) 隔离性 (isolation) 持久性 (durability)

  • 事务的分类 扁平事务 (Flat Transactions) 带有保存点的扁平事务 (Flat Transactions with Savepoints) 链事务 (Chained Transactions) 嵌套事务 (Nested Transactions) 分布式事务 (Distributed Transactions)

  • 事务的隔离性由锁来实现

  • 事务的原子性，一致性，持久性 通过 redo log 和 undo log 来完成

  • Innodb 不支持嵌套事务，当执行一个 START TRANSACTION 指令时，会隐式的执行一个 commit 操作。

事务控制

  • innodb 默认是自动提交的 (auto commit)

  • begin/start transaction 显示的开启事务

  • 隐式提交的 sql 语句:alter 等修改表结构，修改数据库的语句

  • 事务操作的统计 com_commit 与 com_rollback (默认是自动提交 autocommit=1，不会记入这两字段) show global status like‘com_commit’

  • 另外两个参数 handler_commit 与 handle_rollback

  • 事务隔离级别

隔离级别Isolation Level脏读Dirty Read不可重复读NonRepeatable Read幻读Phantom Read
未提交读Read uncommitted可能可能可能
已提交读Read committed不可能可能可能
可重复读Repeatable read不可能不可能可能
可串行化Serializable不可能不可能不可能

未提交读 (Read Uncommitted)：

允许脏读，也就是可能读取到其他会话中未提交事务修改的数据。

已提交读 (Read Committed)：

只能读取到已经提交的数据。Oracle 等多数数据库默认都是该级别（不重复读）。

可重复读 (Repeated Read)：

可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB 默认级别。在 SQL 标准中，该隔离级别消除了不可重复读，但是还存在幻象读。

可串行化 (Serializable)：

完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞，innodb 在 repeatable read 隔离级别下就能达到 3 度的隔离，所以一般不需要 serializable。

幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

分布式事务

  • innodb 支持 XA 事务，通过 XA 事务来支持分布式事务的实现

  • XA 事务支持不同数据库之间的分布式事务

  • XA 事务由一个或多个资源管理器 (Resource Managers),一个事务管理器 (Transaction Manager) 以及一个应用程序 (Application Program) 组成

  • 资源管理器：提供访问事务资源的方法，通常就是数据库

  • 事务管理器：协调参与全局事务中的各个事务 (MySQL 服务器的客户端)

  • 应用程序：定义事务的边界，指定全局事务中的操作

  • Java 的 JTA(Java Transaction API) 可以很好的支持 MySQL 的分布式事务

  • MySQL 内部也存在另外一种内部 XA 事务，在存储引擎与插件直接，或者不同存储引擎之间

  • 最常见的内部 XA 事务是 binlog 与 innodb 存储引擎之间

  • 不好的事务习惯，在循环中提交事务 使用自动提交，使用自动回滚

日志

  • redo log 重做日志 用来保证事务的原子性和持久性，redo log 有单独的文件保存

  • redo log 记录物理修改，某个表空间，某个页，某条记录的值

  • redo log 分为两部分 内存中的 redo log buffer 重做日志文件 redo log file

  • 事务提交时，必须将重做日志持久化，才算完成，即每次提交 commit，写入重做日志到磁盘后都会调用 fsync，强制写入到磁盘，避免停留在文件系统的写入缓冲

  • 通过修改配置可以改变重做日志刷新模式，innodb_flush_log_at_trx_commit 默认为 1，改为 0 不写入重做日志，而是等待一个时间周期 (1s) 后由 master thread 统一操作，设置为 2 表示提交时仅写入文件系统缓存

  • innodb_flush_log_at_trx_commit 改为 0 或 2 时，对事务性能有明显的提升，但是在特定的条件下会牺牲数据的一致性，即写入到缓存而未刷新到硬盘

  • redo log 以 512 字节进行存储，以块 (block) 的方式进行保存，称为重做块日志 redo log block

  • block 大小与磁盘扇区大小一致，保证写入的原子性，不需要 doublewrite 技术

  • innodb1.2 之前，重做日志总大小要小于 4G,innodb1.2 开始限制提高到 512G

  • 重做日志，格式 redo_log_type:重做日志类型 space:表空间 id page_no:也的偏移量

  • LSN(Log Sequence Number) 日志序列号 含义:1.重做日志写入的字节总量 2.checkpoint 的位置 3:页的版本

  • show engine innodb status 可以查看 lsn 的情况

  • Log sequence number 当前的 LSN Log flushed up to 表示刷新到重做日志文件的 LSN Pages flushed up to Last checkpoint at 刷新到磁盘的 LSN

  • 生成环境这几个的值可能不同

  • undo log 用来保证事务的一致性，undo log 默认存放在共享表空间中的 undo 段中 (undo segment)，innodb1.2 开始可以修改配置，存放在单独的文件中

  • undo log 记录逻辑修改，回滚时反向操作

  • mvcc 就是通过 undo log 来实现

  • innodb1.1 之前 只有一个 rollback segment，每个回滚段记录了 1024 个 undo log segment，所以 innodb1.1 之前只支持并发 1024 个事务

  • innodb1.1 开始支持最大 128 个 rollback segment 所以支持同时在线事务的数量为 128*1024

  • 事务提交后不能马上删除 undo log，因为可能还有其他事务需要读取事务提交前的行记录版本，由单独的 pure 线程来判断是否需要最终删除 undolog

  • undo 页可以重用

  • History list length 代表 undo log 的数量，purge 会减少这个数量

  • innodb 还不能直接查看 undo 信息。innosql 对 information_schema 进行扩展，添加了两张数据字典表来查看 undo 信息 innodb_trx_rollback_segment，查看 rollbacksetment,innodb_trx_undo 记录 undo log

  • relay log relay log 很多方面都跟 binary log 差不多，区别是：从服务器 I/O 线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后 SQL 线程会读取 relay-log 日志的内容并应用到从服务器。

  • group commit 一次 fsync 确保多个事务日志被写入文件

  • innodb1.2 之前开启二进制日志后，group commit 会失效，因为开启二进制日志后，为了保证存储引擎层的事务与二进制日志的一致性，必须每个步骤都使用 fsync，使用 prepare_commit_mutex 保证顺序 ** 1) 当事务提交时，Innodb 存储引擎进行 prepare 操作 ** 2) MySQL 数据库上层写入二进制日志 (fsync 由 sync_binlog 控制) ** 3) Innodb 粗糙你引擎层将日志写入重做日志问的 a) 修改内存中事务对应的信息，并且将日志写入重做日志缓冲 b) 调用 fsync 将确保日志都从重做日志缓冲写入磁盘 (fsync 由 innodb_flush_log_at_trx_commit 参数控制)

  • MySQL5.6 实现了 BLGC(Binary Log Group Commit) 使得数据库层与 innodb 存储引擎层都实现了 group cimmit ,移除了 prepare_commit_mutex 锁，提高了性能

  • 二进制日志 (binlog) ,用来进行 POINT-IN-TIME(PIT) 的恢复，以及主从复制 (Replication) 环境的建立

  • 重做日志由 innodb 产生，二进制日志则是在 MySQL 数据库层产生，对任何存储引擎都会产生二进制日志

  • 二进制日志是逻辑日志，记录的是对应的 sql 语句，而重做日志是物理格式日志

  • 二进制日志是事务提交后一次写入，而重做日志是事务每次操作都写入

  • 重做日志是幂等的，二进制日志不是

  • binlog 分为 statement 与 row 两种类型

备份与恢复

  • 备份方式 mysqldump,ibbackup,replication,第三方工具:xtrabackup,LVM快照备份

  • 热备 (Hot Backup) 数据库运行中直接无影响备份 冷备 (Cold Backup) 数据库停机时备份，(复制物理文件) 温备 (Warm Backup) 数据库运行中有影响备份，(e.g 加全局读锁)

  • 逻辑备份，裸文件备份

  • 完全备份，增量备份，MySQL 本身没有增量备份，通过二进制日志来完成增量备份 (效率很低)，可以使用 xtrabackup 工具 日志备份 二进制日志文件

性能调优

  • 选择 64 位 CPU,64 位 MySQL

  • 内存，在达到 MySQL 数据本身大小前，内存与性能，线性增加

  • 机械硬盘与固态硬盘，不同特性对性能的影响，机械硬盘，随机读性能差，固态硬盘随机读性能好，但是覆盖更新性能有局限，根据不同硬件情况来调整参数与程序设计

  • 合理的设置 RAID，有的 RAID 卡支持写入缓存，可以很好的提高性能，同时注意需要内置 UPS 电源才能避免数据丢失

  • 部分文件系统支持文件快照

  • 不同操作系统对 MySQL 有不同的影响

  • 选择合适的基准测试工具 sysbench ,mysql-tpcc