mysql

mysql,innodb内部实现

Posted by onceme on Monday, October 1, 2018

TOC

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 只支持表锁，sq 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 等三张表
  • 一致性非锁定读 , 读取加x锁的记录时，不等待锁释放，直接读取快照， repeatable read 隔离级别下，读取最开始的快照，read commited模式下读取最新版本(锁释放后，读最新提交的数据)
  • 一致性锁定读
  • innodb_autoinc_lock_mod ，自增列，锁定模式，0:锁定插入，1:固定长度的插入时使用互斥量，去增加内存计数，不固定长度时，使用锁定插入 2:强制使用内存计数
  • 行锁的3中算法， Record Lock:单个航记录上的锁 Gap Lock:间隙锁，锁定一个范围，但不包含记录本身 Next-Key Lock:Gap Lock+Record Lock 锁定一个范围，并且锁定记录本身， 另外还有 previous-key locking
  • 如果没有合适的索引，则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操作。

    日志

  • 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两种类型

  • 事务控制语句

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

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

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

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

  • 另外两个参数 handler_commit与handle_rollback

  • 事务隔离级别

    • read uncommited
    • read commited
    • repeatable read
    • serializable 会对select语句自动加共享锁，所以就不支持一致性的非锁定读

隔离级别 脏读（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事务支持不同数据库之间的分布式事务,e.g mysql oracle
  • XA事务由一个或多个资源管理器(Resource Managers),一个事务管理器(Transaction Manager)以及一个应用程序(Application Program)组成
  • 资源管理器:提供访问事务资源的方法，通常就是数据库
  • 事务管理器:协调参与全局事务中的各个事务 (MySQL服务器的客户端)
  • 应用程序:定义事务的边界，指定全局事务中的操作
  • Java的JTA(Java Transaction API)可以很好的支持MySQL的分布式事务
  • MySQL内部也存在另外一种内部XA事务，在存储引擎与插件直接，或者不同存储引擎之间

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

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

备份与恢复

  • 备份方式 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
comments powered by Disqus
FEATURED TAGS
bigdata golang nosql php python redis
FRIENDS
Ï