Mysql技术内幕

一些常识

随机IO和顺序IO

  1. 随机IO：在机械硬盘中，文件被放在物理存储介质种的不同的地方，读取时，磁头要不断的调整磁道的位置，以在不同位置上的读写数据
  2. 顺序IO：指的是本次 I/O 给出的初始扇区地址和上一次 I/O 的结束扇区地址是完全连续或者相隔不多的。在做连续 I/O 的时候，磁头几乎不用换道，或者换道的时间很短，效率高些

mysql的链接方式

  • TCP/IP
  • UNIX套接字

第二章 INNODB存储引擎

INNODB体系架构

后台线程

  • MASTER Thread 主要负责将缓存中的数据异步刷新到磁盘
  • IO Thread

共有4种类型IO线程，且共4个，从 INNODB 1.x 版本开始write 和 read 分别就有4个，linux下无法调整IO线程的数量，但在windos下可以调整，可以通过命令：SHOW ENGINE INNODB STATUS; 来观察INNODB 中的 IO Thread ：

  • write IO Thread
    1.x 后可以使用 innodb_write_io_threads 设置 read 线程的数量（只能在配置文件种修改）
  • read IO Thread
    1.x 后可以使用 innodb_read_io_threads 设置 read 线程的数量（只能在配置文件种修改）
  • insert buffer IO Thread
  • log IO Thread
  • Purge Thread

事务提交后undolog可能不再需要，Purge Thread用来回收已经使用并分配的undo页,可以使用以下配置来启动独立的 Purge Thread 否则改功能会使用 master 线程来实现

[mysqld]
innodb_purge_threads=1

在 Innodb 1.1 版本最多能设置1个 purge 线程否者启动时会受到错误提示，在Innodb 1.2以后的版本可以设置多个 purge 线程，可以以下sql查询purge的数量：

SHOW VARIABLES like 'innodb_purge_threads'
  • page cleaner Thread

由Innodb 1.2.x引入。 刷新脏页的操作独立出来

内存

缓冲池

mysql中对页的修改操作，首先是修改缓冲池中的页。再以一定的频率刷新到磁盘中，刷新缓存页到磁盘的操作，不是每次在修改时都发生，而是通过一种 Checkpoint 的机制刷新回磁盘

1.缓存池数量及大小配置

  • 缓冲池的大小通过参数**【innodb_buffer_pool_size】**来设置，同时使用： SHOW VARIABLES like ‘innodb_buffer_pool_size’ 观察缓冲池的大小
  • 缓冲池中主要缓存了：索引页、数据页、undo页、插入缓冲、自适应哈希索引、InnoDB存储的锁的信息、数据字典信息等
  • 从 InnoDB 1.0.X的版本版本开始，允许有多个缓冲池实例（缓存池的大小大于1G时）。每个页根据hash值平均分配到不同的缓冲池实例中，这样可以减少数据内部的资源竞争，增加数据库的并发，通过参数 【innodb_buffer_pool_instances】 修改缓冲池的数量，通过 SELECT POOL_ID, POOL_SIZE,FREE_BUFFERS, DATABASE_PAGES FROM information_schema.INNODB_BUFFER_POOL_STATS 查询缓冲池实例

2.缓存池内部配置（LRU LIST、Free List、Flush List）

LRU LIST 为使用中的缓存页列表，Free List为空闲的缓存页列表，当需要从缓存池分页时先从 Free List 中查找是否有可用的空闲页，有则删除并写入到LRU中

缓存池内部通常使用 LRU 算法（最近最少使用）来进行管理，即：使用最频繁的页在缓存列表最前面。新读取到的页放入LRU 的midpoint（队列长度5/8处，midpoint点前面的是new端，后面的是old端）位置

  • midpoint由参数：【innodb_old_blocks_pct】控制，使用sql：【 show VARIABLES like 'innodb_old_blocks_pct';】 查看当前的midpoint位置： 当前位置37%即差不多 5/8 的位置处
  • 为了防止新读取的页（这些页可能只会使用一次），将所需要的热点页，挤出了列表，可以通过 【innodb_old_blocks_time 】,设置新加入的页，过一段时间再放入热端

可以通过【show ENGINE INNODB STATUS;】来观察缓存池的使用情况

............
#---从最近8秒计算出的每秒平均值
Per second averages calculated from the last 8 seconds
..............
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 1098383360
Dictionary memory allocated 619500
#---缓存池总大小Buffer pool size 为 65536 即：65536 *16K
Buffer pool size   65536
#---Free buffers 表示为空闲页
Free buffers       65018
#---LRU 列表中页的数量
Database pages     512
Old database pages 0
#脏页，别修改需要写回磁盘的页
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
#---Pages made young 表示LRU列表中页移动到热端的次数,not young表示移出
Pages made young 0, not young 0
#---这2个操作每秒的频率
0.00 youngs/s, 0.00 non-youngs/s
Pages read 449, created 63, written 528
0.00 reads/s, 0.00 creates/s, 0.50 writes/s
#--Buffer pool hit rate 缓存池命中率，这里是100%，该值不应该小于95%，如小于考虑是否是由全局扫描引起的
#缓存污染
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
  • 查询lRU 列表中具体的页信息：
SELECT table_name,space,PAGE_NUMBER,PAGE_TYPE from INNODB_buffer_PAGE_LRU

3.重做（redo log ）日志

INNODB 会将重做信息放入redo log buffer中，然后按一定的频率刷入磁盘。应为每秒都会刷入磁盘所以改区域不需要很大，由参数**【innodb_log_buffer_size】**控制，默认为8M，足够大部分情况下的使用。以下情况会触发redo log写入磁盘：

  • MASTER thread 每一秒会将redo log buffer写入磁盘
  • 每个事务提交会将redo log buffer写入磁盘
  • redo log buffer 剩余空间小于1/2会写入磁盘

Checkpoint技术

InnoDB 修改数据时，先修改内存中的页，修改后的页与磁盘中的页不一致，也就是脏页。为了避免数据丢失，在事务中先写重做日志（redo log），在修改数据，发生意外通过重做日志恢复，也就是事务的持久性。innodb 触发Checkpoint的几种可能：

  • Master Thread Checkpoint
    主线程每秒或每10秒主动刷新一次
  • FLUSH_LRU_LIST Checkpoint
    为了保证LRU队列需要有差不多100个空闲的page供使用，如果不足会移除尾部的页，这些页如果是脏页，那么就需要Checkpoint，可以通过参数【innodb_lru_scan_depth】设置 保持的空闲页的数量。show VARIABLES like ‘innodb_lru_scan_depth’
  • Async/Sycn Flush Checkpoint
    重做日志文件不可用时(该文件时循环使用的，刷新磁盘后就会删除无用的redo log)，强制刷新回磁盘
  • Dirty Page Too much Checkpoint
    缓存中脏页太多，innodb主动刷新到磁盘，由参数【innodb_max_dirty_pages_pct】控制，脏页占比多少时主动触发Checkpoint。show VARIABLES like ‘innodb_max_dirty_pages_pct’

即使某个事务还没有提交，Innodb也会将日志刷新到磁盘，所以大事务提交也会很快

Master Thread 工作方式

Master Thread内部有多个循环组成：

  1. 主循环loop
    大部分的操作是在这个循环中，其中2大部分操作-----每一秒操作和每10秒操作每一秒操作包括如下：
  • 日志缓冲到磁盘（总是）
  • 合并插入缓冲（可能）
  • 至多刷新100个Innodb的缓冲到磁盘（可能）
  • 如果没有用户活动，切换到 backgroup 线程中

每10秒的操作如下：

  • 刷新100个脏页到磁盘（可能）
  • 合并插入之多5个插入缓冲（总是）
  • 将日志缓冲插入到磁盘（总是）
  • 删除无用的Undo页（总是）
  • 刷新100个或者10个脏页到磁盘（总是）
  1. 后台循环 backgroup loop
  2. 刷新循环 flush loop
  3. 暂停循环 suspend loop

Master Thread会根据数据库的状态切换到不同的循环中

InnoDb关键特性

插入缓冲

mysql对于非聚集索引的插入，先去判断我要插入的索引页是否已经在内存中了，如果不在，我暂时不着急先把索引页加载到内存中，而是把它放到了一个Insert Buffer对象中，如说现在Insert Buffer中有1，99，2，100，合并之前可能要4次插入，合并之后1，2可能是一个页的，99，100可能是一个页的，这样就减少到了2次插入。效率就这样提升了

两次写

一个页16kb写入一半失败了会造成物理数据页不完整，而 redo log 日志记录的物理操作，比如物理页的文件指针偏移80然后写入“aaa”，如果物理页不完整就不能通过 redo log还原,那么就必须保证，物理磁盘中一个页至少有1个（共享表空间，数据文件）是完整的，可以通过完整的那个页+redo log 恢复数据

内存中的脏页写入磁盘时，会写入2份，如果写时系统中断，就可以保证同一个页在磁盘中，至少有1个是完整的页

两次写需要额外添加两个部分：

  • 内存中的两次写缓冲（doublewrite buffer），大小为2MB
  • 磁盘上共享表空间中连续的128页，大小也为2MB

其原理是这样的：

  • 当刷新缓冲池脏页时，并不直接写到数据文件中，而是先拷贝至内存中的两次写缓冲区。
  • 接着从两次写缓冲区分两次写入磁盘共享表空间中，每次写入1MB
  • 待第2步完成后，再将两次写缓冲区写入数据文件

自适应hash索引

注意 hash 索引是有锁的，5.6使用的是1把锁，5.7每个分区使用不同的锁。

自适应hash索引是一种键值对的存储结构，存储的是热点页所在的记录。InnoDB存储引擎会自动根据访问的频率和模式 来为某些页建立哈希索引。称之为：Adaptive Hash index(AHI)

生成AHI有一个要求，即对这个页的连续访问模式必须是一样的，例如(a,b)的联合索引页，其访问模式可以是以下情况

  • where a=xxx
  • where a=xxx and b=xxx

访问模式一样指的是查询条件一样，若交替进行上述两种查询，那么innodb存储引擎不会对该页构造AHI，此外AHI还有如下的要求：

  • 以该模式访问了100次
  • 页通过该模式访问了N次，其中N=页中记录 * 1/16

可以通过参数【innodb_adaptive_hash_index】开启或关闭，可以通过 show engine innodb status; 观察

INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
#一共8个页，存储在buffer pool中的。上面的2表示存储了二个buffer。
Hash table size 276707, node heap has 2 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 2 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 2 buffer(s)
# 走hash 索引的数量，没有hash的数量
1.57 hash searches/s, 16.52 non-hash searches/s

异步IO

使用AIO的方式读取数据页，可以通过【innodb_use_native_aio】参数控制 AIO 的开启，且AIO可以合并IO请求比请求（8，6）（8，7）（8，8）可以合并成（8，8），linux 下默认关闭的。官网的测试是开启后性能提升70%

刷新邻接页

在对某个脏页刷新时，会检测同一个区的所有页，看是否有脏页，如果有一起刷出可以将多个IO合并成1个IO，在机械磁盘上可以显著的提升效率。带来的问题

  • 可能将不怎么脏的页写入了，写入后不久又变成脏页了
  • 固态硬盘的IO比较快，是否需要这个功能

可以使用参数【innodb_flush_neighbors】控制是否启用，建议在机械磁盘上使用这个功能。

启动与关闭

通过一些设置，影响mysql innodb 关闭或启动时的操作流程

  • innodb_fast_shutdown 关闭时做什么操作
  • 0表示关闭时将所有的 full purge 和 merge insert buffer ,并将所有的脏页刷新回磁盘。可能需要一定的时间，看内存中的数据而定
  • 1默认值，会刷新一部分脏页回磁盘
  • 2什么都不干，而是把日志写入日志文件，启动时通过日志文件恢复
  • innodb_force_recovery

第三章 文件

参数文件

通过：mysql --help | grep my.conf 查询配置文件地址

日志文件*

  • 错误日志
    通过：show VARIABLES like ‘log_error’ 可以查询到文件地址。数据库不能启动时可以通过日志进行分析
  • 二进制日志
    记录了所有写的操作sql。用于复制，恢复，审计。【show VARIABLES like ‘datadir’】查看数据库所在目录
  1. log_bin=binlog 启用日志
  2. binlog_format=mixed 日志模式
  3. max_binlog_size 单个日志的最大值
  4. binlog_chache_size 日志缓存大小（session级别不易过大，默认32k），事务提交时会写入到磁盘。如果太小，缓存不够时会写入到临时文件，可以通过【binlog_chache_ues】缓存使用次数，【binlog_chache_disk_ues】临时文件的使用次数判断大小是否合适
  5. sync_binlog 设置日志何时写入到文件 【0】由文件系统自己控制它的缓存的刷新,【1】每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。【>1】每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去
  6. innodb_support_xa 如果使用了主从同步，需要开启此参数保证极端情况下binlog日志与数据库存储文件之间的同步
  • 慢查询日志
    通过配置参数：【long_query_time】设置慢查询的时间
    通过配置参数：【slow_query_log】设置启用慢查询（5.7版本）
    通过配置参数：【show_query_log_file】配置慢查询日志的存放路径，默认存在数据目录下。
    通过配置参数：【log_queries_not_using_indexes】记录未使用索引的sql，需要设置最多记录多少条否则海 量的sql产生
    通过配置参数：【log_output】配置日志输出到哪里【FILE/TABLE】。默认FILE，5.1开始可以输出到 mysql.slow_log表中
  • 查询日志
    通过参数【general_log】设置开启还是关闭，记录客户端所有请求的sql。包括错误的。使用凡是和慢查询日志一直

socket 文件

pid 文件

mysql实例进程文件

mysql表结构文件

存储引擎文件

  • 表空间文件
  • 重做日志文件

第四章 表

4.1组织索引表

在innodb中表都是按主键顺序进行存放的。这种存储方式称之为 组织索引表 ，在innodb中，每个表都有主键，如果没有主动声明。会按一下顺序处理：

  • 判断表中是否有非空的唯一主键。如果有该列为组件（如果有多个，选择第一个）
  • 隐式的创建一个6kb大小的主键

4.2Innodb 逻辑存储结构

innodb 中所有数据都被逻辑的存放在一个空间中即表空间，表空间又由：段、区、页(块)组成。

  1. 表空间
    默认情况下会创建一个共享表空间。所有数据都存放在这个表空间。如果启用了 【innodb_file_per_table】则每张表的数据单独存放在一个表空间内。单独的表空间只存放：数据、索引、插入缓冲页。其他的信息如：回滚信息、插入缓冲索引页、系统事务信息、二次写缓冲等都放在共享表空间中。表空间大小会自动增长，其中的无用的页虽然会回收但不会压缩这些空间，只会标记页为可用页。
  2. 段
    正常情况下，我们检索都是在叶子节点的双向链表进行的，也就是说我们会把区进行区分，如果不区分把叶子节点和非叶子结点混在一起，那么效果就会打大折扣，所以对于一个索引B+树来说，我们区别对待叶子节点的区和非叶子节点的区，并把存放叶子节点的区的集合称为一个段把存放非叶子节点区的集合也称为一个段，所以一个索引会生成两个段：叶子节点段和非叶子节点段。
  3. 区
    区是由连续得页组成得。对于16KB的页来说，连续的64个页（连续的）就是一个区，也就是说一个区的大小为1MB
  • Innodb 1.0.x开始引入压缩页 KEY_BLOCK_SIZE 设置为 2k、4k、8k,因此每个区对应得页得数量就是 512、256、128个
  • Innodb 1.2.x开始引入 innodb_page_size 设置为 4k、8k，但是页种得数据不是压缩区对应得页得数量就是 256、128个

不管页得大小怎么变区得大小是不变得总是为1M。

  1. 页
    页是Innodb引擎最小得存储单位。可以通过。参数设置页的大小，设置后不可以对页的大小进行再次修改除非，删除数据库重新创建
  2. 行
    Innodb是面向 列的，也就是数据是按行来存储的。

4.3 行记录格式

可以通过 【 show variables like"innodb_file_format"】 查看行如何存储的

  • Barracuda
  • Compact

第五章 索引和算法