mysql,innodb内部实现

mysql存储结构

    1. B-tree 是平衡的多路查找树。
    1. 涉及到磁盘的查找需要设法减少磁盘 I/O 次数。
    1. 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字节;而在开启innodblargeprefix属性值的情况下,最大能支持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, informationschema下的,innodbtrx,innodblocks,innodblock_waits 等三张表
  • 一致性非锁定读 , 读取加x锁的记录时,不等待锁释放,直接读取快照, repeatable read 隔离级别下,读取最开始的快照,read commited模式下读取最新版本(锁释放后,读最新提交的数据)
  • 一致性锁定读
  • innodbautoinclock_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,强制写入到磁盘,避免停留在文件系统的写入缓冲
  • 通过修改配置可以改变重做日志刷新模式, innodbflushlogattrx_commit 默认为1,改为0不写入重做日志,而是等待一个时间周期(1s)后由master thread统一操作,设置为2表示提交时仅写入文件系统缓存
  • innodbflushlogattrx_commit 改为0或2时,对事务性能有明显的提升,但是在特定的条件下会牺牲数据的一致性,即写入到缓存而未刷新到硬盘
  • redo log 以512字节进行存储,以块(block)的方式进行保存, 称为重做块日志 redo log block
  • block 大小与磁盘扇区大小一致,保证写入的原子性,不需要doublewrite技术
  • innodb1.2之前,重做日志总大小要小于4G,innodb1.2开始限制提高到512G
  • 重做日志,格式 redologtype:重做日志类型 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对informationschema进行扩展,添加了两张数据字典表来查看undo信息 innodbtrxrollbacksegment,查看rollbacksetment,innodbtrxundo 记录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 等修改表结构,修改数据库的语句

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

  • 另外两个参数 handlercommit与handlerollback

  • 事务隔离级别

    • 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

分布式事务

  • 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

This article is my 19th oldest. It is 470 words long