MySQL技术内幕-InnoDB存储引擎 v2

• MySQL8.0 innodb架构图 来源于官方文档

第一章 MySQL体系结构

• 数据库：物理操作系统文件和其他形式文件类型的集合。存放于二级存储器中
• 实例：MySQL数据库由后台线程以及一个共享内存区组成。
• MySQL被设计成一个单进程多线程架构的数据库。

top  -H -p PID
ps -T -p PID

• mysql文件存储位置 show variables like ‘datadir’;
• 支持的存储引擎 show engines;
• innodb存储引擎占用表空间比MyISAM的大。Archive存储引擎占用空间更小。
• linux下UNIX域套接字连接MySQL服务端

mysql -uroot -S /var/lib/mysql/mysql.sock -p

第二章 InnoDB存储引擎

• 特点：行锁设计、支持MMCC、支持外键、提供一致性非锁定读，同时被设计用来最有效地利用以及使用内存和CPU。

InnoDB体系架构

• 后台线程：主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据。此外将已修改的数据文件刷新到磁盘文件，同时保证在数据库发生异常的情况下InnoDB能恢复到正常运行状态。

  • Master Thread：核心后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新，合并插入缓冲（Insert Buffer）、UNDO页的回收等。
  • I/O Thread：在InnoDB中大量使用AIO(Async I/O)来处理I/O请求，这样可以极大提高数据库的性能。而I/O Thread的主要工作就是负责这些I/O请求的回调处理。主要有4个I/O Thread，write、read、insert buffer和log I/O Thread。

  mysql> show variables like 'innodb_%io_threads';
  +-------------------------+-------+
  | Variable_name           | Value |
  +-------------------------+-------+
  | innodb_read_io_threads  | 4     |
  | innodb_write_io_threads | 4     |
  +-------------------------+-------+
  mysql> show engine innodb status\G
  ......
  FILE I/O
  --------
  I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
  I/O thread 1 state: waiting for completed aio requests (log thread)
  I/O thread 2 state: waiting for completed aio requests (read thread)
  I/O thread 3 state: waiting for completed aio requests (read thread)
  I/O thread 4 state: waiting for completed aio requests (read thread)
  I/O thread 5 state: waiting for completed aio requests (read thread)
  I/O thread 6 state: waiting for completed aio requests (write thread)
  I/O thread 7 state: waiting for completed aio requests (write thread)
  I/O thread 8 state: waiting for completed aio requests (write thread)
  I/O thread 9 state: waiting for completed aio requests (write thread)
  Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
  ibuf aio reads:, log i/o's:, sync i/o's:
  Pending flushes (fsync) log: 0; buffer pool: 0
  841 OS file reads, 3440 OS file writes, 2064 OS fsyncs
  0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
  

  • Purge Thread：事务提交之后，其所使用的undolog可能不再需要，因此需要PurgeThread来回收已经使用并分配的undo页。

  mysql> show variables like 'innodb_purge_threads';
  +----------------------+-------+
  | Variable_name        | Value |
  +----------------------+-------+
  | innodb_purge_threads | 4     |
  +----------------------+-------+
  

  • Page Cleaner Thread:刷新脏页，减轻原Master Thread的工作以及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。

• 内存

  • 缓冲池：InnoDB是基于磁盘存储的，并将其中的记录按照页的方式进行管理。由于CPU速度与磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。
    • 缓冲池读：首先从磁盘读到的页存放在缓冲池中，这个过程称为将页“FIX”在缓冲池中。下一次读相同的页，首先判断该页是否在缓冲池中，若在缓冲池中，称页命中，直接读取该页。否则，读取磁盘上的页
    • 写：首先修改缓冲池中的页，然后再以一定的频率刷新到磁盘上。页从缓冲池刷新回磁盘的操作并不是在每次页发生变更时触发，而是通过一种称为CheckPoint的机制刷新回磁盘。
    • 缓冲池缓存的数据页类型有：索引页，数据页，undo页，插入缓冲(insert buffer)，自适应哈希索引(adaptive hash index)，InnoDB存储的锁信息(lock info)，数据字典信息(data dictionary)等
    • 缓冲池配置大小，innodb_buffer_pool_size，一般为物理内存的70%-80%。可以从innodb status查看，也可以查information_schema.innodb_buffer_pool_stats表

    mysql> show variables like 'innodb_buffer_pool_size';
    +-------------------------+-----------+
    | Variable_name           | Value     |
    +-------------------------+-----------+
    | innodb_buffer_pool_size | 134217728 |
    +-------------------------+-----------+
    mysql> select pool_id,pool_size,free_buffers,database_pages from information_schema.innodb_buffer_pool_stats;
    +---------+-----------+--------------+----------------+
    | pool_id | pool_size | free_buffers | database_pages |
    +---------+-----------+--------------+----------------+
    |       0 |      8192 |         6939 |           1240 |
    +---------+-----------+--------------+----------------+
    1 row in set (0.00 sec)
    

    • 缓冲池可以配置多个：innodb_buffer_pool_instances，每个页根据哈希值平均分配到不同的缓冲池实例中。这样做的好处是减少数据库内部的资源竞争，增加数据库的并发处理能力。
      • 缓冲池通过LRU（最近最少使用）算法来进行管理。但是做了一些优化,读取到新的页，首先放入midpoint位置，距离尾端37%，由参数innodb_old_blocks_pct决定，midpoint后的列表称为old列表，之前的列表称为new列表。old列表的页需要等待innodb_old_blocks_time（ms）后才会加入new端。
      • Pages made young 239341807, not young 2439814636：表示old部分加入到new部分的次数。而因为innodb_old_blocks_time的设置而导致页没有从old部分转移到new部分的操作称为page not made young
      • Database pages：LRU列表中的页数量，和Free buffers之和不等于Buffer pool size大小，因为缓冲池中的页可能会分配给自适应哈希索引、Lock信息、Insert Buffer等页，而这部分页不需要LRU算法进行维护。
      • Modified db pages 14106：脏页数量，数据库会通过CHECKPOINT机制将脏页刷新回磁盘，而Flush列表中的页即为脏页列表。脏页既存在于LRU列表，也存在于Flush列表。LRU列表管理缓冲池中页的可用性，Flush列表用来管理将页刷回磁盘，二者互不影响。

    mysql> show variables like 'innodb_old_blocks_%';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | innodb_old_blocks_pct  | 37    |
    | innodb_old_blocks_time | 1000  |
    +------------------------+-------+
    -- 查询PAGES_MADE_YOUNG
    mysql> SELECT POOL_ID,HIT_RATE,PAGES_MADE_YOUNG,PAGES_NOT_MADE_YOUNG FROM `information_schema`.`INNODB_BUFFER_POOL_STATS`;
    -- 查询脏页名称 null表示该页属于系统表空间
    mysql> select table_name,space,page_number,page_type from information_schema.innodb_buffer_page_lru where oldest_modification>0;
    +------------+-------+-------------+-------------------+
    | table_name | space | page_number | page_type         |
    +------------+-------+-------------+-------------------+
    | NULL       |     0 |     1785896 | UNDO_LOG          |
    | NULL       |     0 |     1785895 | UNDO_LOG          |
    | NULL       |     0 |           0 | FILE_SPACE_HEADER |
    | NULL       |     0 |     1753088 | EXTENT_DESCRIPTOR |
    | NULL       |     0 |         197 | SYSTEM            |
    | NULL       |     0 |         219 | SYSTEM            |
    | NULL       |     0 |     1622016 | EXTENT_DESCRIPTOR |
    | NULL       |     0 |           6 | SYSTEM            |
    | NULL       |     0 |         785 | UNDO_LOG          |
    | NULL       |     0 |          48 | SYSTEM            |
    | NULL       |     0 |     1785916 | UNDO_LOG          |
    mysql> show engine innodb status\G
    ---BUFFER POOL 7
    Buffer pool size   196608
    Free buffers       1024
    Database pages     176905
    Old database pages 65282
    Modified db pages  14106
    Pending reads      0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 239341807, not young 2439814636
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 93481255, created 1123927, written 68416385
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 1 / 1000
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 176905, unzip_LRU len: 0
    I/O sum[5505]:cur[0], unzip sum[0]:cur[0]
    

    • 重做日志缓冲（redo log buffer）：InnoDB首先将重做日志信息先放入这个缓冲区，然后按一定频率将其刷新到重做日志文件。不需要设置很大，因为一般情况下每一秒钟会将重做日志刷新到磁盘。innodb_log_buffer_size参数控制。具体刷新条件如下：
      • Master Thread每一秒刷
      • 每个事务提交时会刷
      • 当重做日志缓冲池剩余空间小于1/2时刷
  • 额外的内存池：InnoDB对内存的管理是通过heap的方式进行的。在申请内存时除了buffer pool，也需要考虑这些额外的内存池

CheckPoint技术

• 当前事务数据库系统普遍采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。
• CheckPoint解决的问题：
  • 缩短数据库的恢复时间：当数据库发生宕机时，数据库不需要重做所有的日志，因为CheckPoint之前的页都已经刷新回磁盘，故只需要对CheckPoint后的重做日志进行恢复。
  • 缓冲池不够用时，将脏页刷新到磁盘：根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行CheckPoint，将脏页刷回磁盘
  • 重做日志不可用时，刷新脏页：重做日志是循环使用的，可以被重用的部分是指这些重做日志不需要，即当数据库宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。若此时重做日志还需要使用，那么必须强制产生CheckPoint，将缓冲池中的页至少刷新到当前重做日志的位置。
• 通过LSN（Log sequence number）来标记版本

LOG
---
Log sequence number 5103200757074
Log flushed up to   5103200756814
Last checkpoint at  5103200592522
Max checkpoint age    7782360
Checkpoint age target 7539162
Modified age          164552
Checkpoint age        164552
0 pending log writes, 0 pending chkp writes
19381381 log i/o's done, 4.15 log i/o's/second

• Sharp CheckPoint：发生在数据库关闭时将所有的脏页都刷新回磁盘。innodb_fast_shutdown=1，默认方式。
• 数据库运行时使用Fuzzy CheckPoint，即只刷新一部分脏页，而不是刷新所有脏页回磁盘。
  • Master Thread CheckPoint：每秒或每十秒从缓冲池的脏页列表中刷新一定比例的页回磁盘，异步
  • FLUSH_LRU_LIST CheckPoint：因为InnoDB需要保证LRU列表需要有差不多100多个空闲页可供使用。在MySQL5.6版本之后，通过参数innodb_lru_scan_depth来控制LRU列表中可用页的数量，默认为1024，检查被放在一个单独的Page Cleaner线程中进行。
  • Async/Sync Flush CheckPoint：指的是重做日志不可用时，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的。在MySQL5.6之后，采用单独Page Cleaner Thread来刷新，不会阻塞用户查询线程。
  • Dirty Page too much CheckPoint：脏页太多，强制进行CheckPoint，刷新一部分的在哪过夜到磁盘。由参数innodb_max_dirty_pages_pct控制百分比，默认75%。

Master Thread

mysql> show engine innodb status\G
Per second averages calculated from the last 21 seconds
-----------------
BACKGROUND THREAD
-------MySQL8.0----------
srv_master_thread loops: 69 srv_active, 0 srv_shutdown, 593077 srv_idle
srv_master_thread log flush and writes: 0

--------MySQL5.7---------
srv_master_thread loops: 1833639 1_second, 1833599 sleeps, 167935 10_second, 403465 background, 403460 flush
srv_master_thread log flush and writes: 4830842

• InnoDB 1.0.x版本之前
  • 每秒一次的操作
    • redo日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）
    • 合并插入缓冲（可能）：当前一秒IO次数小于5时，认为压力很小，可以执行合并插入缓冲的操作
    • 至多刷新100个缓冲池脏页到磁盘（可能）：如果脏页超过innodb_max_dirty_pages_pct,会刷回磁盘
    • 如果当前没有用户活动，则切换到background loop
  • 每10s一次
    • 刷新100个脏页到磁盘（可能的情况下）：在过去10s内。IO小于200次，刷新
    • 合并至多5个插入缓冲（总是）
    • 将日志缓冲刷新到磁盘（总是）
    • 删除无用的Undo页（总是）：执行full purge
    • 刷新100个或者10个脏页到磁盘（总是）：脏页比例超过70%，刷新100个，否则刷新10个
  • background loop：若当前没有用户活动或者数据库关闭，就会切换到这个循环，执行以下操作
    • 删除无用的Undo页（总是）
    • 合并20个插入缓冲（总是）
    • 没有空闲跳回到主循环（总是）
    • 不断刷新100个页直到符合条件（可能，跳转到flush loop中完成）
  • 若flush loop也没事干，会切换到suspend_loop，将Master Thread挂起，等待时间的发生
• InnoDB 1.2.x版本之前
  • innodb_io_capacity
    • 在合并插入缓冲时，合并插入缓冲的数量为innodb_io_capacity值的5%
    • 在从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity
  • 引入innodb_adaptive_flushing,当脏页比例低于innodb_max_dirty_pages_pct，也会适当的刷新脏页，通过判断重做日志（redo log）的速度来决定数量。
  • 引入innodb_purge_batch_size来控制每次full purge回收的Undo页的数量
• InnoDB 1.2.x版本
  • 如果服务器空闲，先进行10s一次操作
  • 将刷新脏页的操作，从Master Thread线程分离到一个单独的Page Cleaner Thread，从而减轻了Master Thread的工作，同时进一步提高了系统的并发性。

InnoDB关键特性

• 插入缓冲（Insert Buffer）：和数据页一样，也是物理页的一个组成部分。存在的原因是，数据记录是按聚簇索引顺序存放的（如果使用UUID，插入也是随机的），但是非聚簇索引页叶子节点的插入不是顺序的，需要离散访问非聚集索引页，这就引入了插入缓冲合并操作提高性能。
  • 对于非聚集索引的插入和更新操作，不是每一次直接插入索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，直接插入，若不在，则先放入一个Insert Buffer对象中，好似欺骗。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚簇索引插入的性能。
  • Insert Buffer使用的条件如下：
    • 索引是辅助索引（secondary index）
    • 索引不是唯一的：因为插入缓冲时，数据库并不去查找索引页来判断插入记录的唯一性。
  • Change Buffer：可以视为Insert Buffer的升级版，包括Insert Buffer、Delete Buffer、Purge Buffer。将一条记录进行UPDATE操作可能分为两个过程：
    • 将记录标记为已删除：对应于Delete Buffer
    • 真正将记录删除：对应于Purge Buffer
  • Change Buffer可以使用innodb_change_buffer_max_size参数来控制，默认25，表示使用的内存最大占用缓冲池内存的1/4

  -- seg size表示插入缓冲大小13777*16KB 
  -- free list len 空闲列表长度
  -- size 已经合并记录页的数量
  -- insert表示Insert Buffer，delete mark表示Delete Buffer，delete表示Purge Buffer
  Ibuf: size 1, free list len 13775, seg size 13777, 72498876 merges
  merged operations:
  insert 96870751, delete mark 30149375, delete 2657293
  discarded operations:
  insert 0, delete mark 0, delete 0
  

  • Insert Buffer实现：采用一颗全局B+树。存放在共享表空间中，也就是ibdata1中。Insert Buffer Bitmap用来标记每个辅助索引页的可用空间，这是为了保证Merge Insert Buffer页成功
• 两次写（double Write）:Insert Buffer带给InnoDB的是性能上的提升，而Double Write带给InnoDB的是数据的可靠性
  • 当发生写失效时，可以通过重做日志进行恢复。重做日志中记录是对页的物理操作，但是如果这个页本身已经发生了损坏，则对其进行重做是没有意义的。
  • 在应用重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重写，这就是double write。
  • double write由两部分组成，一部分是内存中的doublewrite buffer，大小为2MB，另一个是物理磁盘上共享表空间中连续的128个页，即2个区，大小为2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中doublewrite buffer，之后通过doublewrite buffer在分写两次，一次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。在这个过程中，因为doublewrite页是顺序的，这个过程也是循序写的，开销不大。在完成doublewrite页的写入后，再将doublewrite buffer中的页写入各个表空间中，此时写是离散的。

  MySQL [(none)]> show status like 'innodb_db%';
  +----------------------------+-----------+
  | Variable_name              | Value     |
  +----------------------------+-----------+
  | Innodb_dblwr_pages_written | 558784920 |  // 写入页数
  | Innodb_dblwr_writes        | 71333774  |  // 写入次数
  

  • 恢复：如果操作系统将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本，将其复制到表空间文件，再应用重做日志。
• 自适应哈希索引（Adaptive Hash Index）:InnoDB会监控对标上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引AHI。32733.05 hash searches/s, 2329.60 non-hash searches/s

  INSERT BUFFER AND ADAPTIVE HASH INDEX
  -------------------------------------
  Ibuf: size 1, free list len 13775, seg size 13777, 72504172 merges
  merged operations:
  insert 96875364, delete mark 30150187, delete 2657308
  discarded operations:
  insert 0, delete mark 0, delete 0
  Hash table size 6375023, node heap has 8693 buffer(s)
  Hash table size 6375023, node heap has 7233 buffer(s)
  Hash table size 6375023, node heap has 9478 buffer(s)
  Hash table size 6375023, node heap has 55587 buffer(s)
  Hash table size 6375023, node heap has 15621 buffer(s)
  Hash table size 6375023, node heap has 6443 buffer(s)
  Hash table size 6375023, node heap has 44123 buffer(s)
  Hash table size 6375023, node heap has 7376 buffer(s)
  32733.05 hash searches/s, 2329.60 non-hash searches/s
  

• 异步IO（Async IO）：用户可以在发出一个IO请求后立即再发出另一个IO请求，当全部IO请求发送完毕后，等待所有IO操作的完成，这就是AIO。
  • AIO的另一个优势是可以进行IO Merge操作，也就是将多个IO合并为一个
  • 在InnoDB中，read ahead（预读）方式的读取都是通过AIO完成，脏页的刷新，即磁盘的写入操作则全部由AIO完成。
• 刷新邻接页（Flush Neighbor Page）：当刷新一个脏页时，InnoDB会检测该页所在区（extent）的所有页，如果是脏页，那么一起刷新。
  • 需要考虑是不是把不怎么脏的页进行了写入，而该页又会很快成为脏页？
  • 该机制在机械硬盘上有着显著的优势，将多个IO合并成一个，但是对于固态硬盘，有着超高的IOPS，建议关闭。

  mysql> show variables like 'innodb_flush_neighbors';
  +------------------------+-------+
  | Variable_name          | Value |
  +------------------------+-------+
  | innodb_flush_neighbors | 0     |
  +------------------------+-------+
  

• 启动、关闭和恢复
  • 在关闭时，innodb_fast_shotdown影响着InnoDB行为
    • 0表示关闭时，InnoDB需要完后才能所有的full merge和merge insert buffer，并且将所有的脏页刷新回磁盘。这需要一段时间。如果在进行InnoDB升级时，必须将这个参数设为0，然后关闭
    • 1是默认值，表示不需要完成上述的full merge和merge insert buffer，但是在缓冲池中的一些数据脏页还是会刷新回磁盘。
    • 2表示不完成full merge和merge insert buffer，也不将缓冲池中的数据脏页写回磁盘，而是将日志都写入日志文件。
  • 在恢复时，参数innodb_force_recovery影响了整个InnoDB的状况。默认为0，代表当发生需要恢复时，进行所有的恢复操作，当不能进行有效恢复时，如数据页发生了corruption，MySQL可能会宕机（crash），并把错误写入错误日志中去。

第三章 文件

[root@test001 mysql]# ls
'#ib_16384_0.dblwr'   cp_audio             mysqlx.sock
'#ib_16384_1.dblwr'   ib_buffer_pool       mysqlx.sock.lock
'#innodb_temp'        ib_logfile0          performance_schema
 auto.cnf             ib_logfile1          private_key.pem
 binlog.000001        ibdata1              public_key.pem
 binlog.000002        ibtmp1               server-cert.pem
 binlog.index         mysql                server-key.pem
 ca-key.pem           mysql.ibd            sys
 ca.pem               mysql.sock           undo_001
 client-cert.pem      mysql.sock.lock      undo_002
 client-key.pem       mysql_upgrade_info

• 参数文件：告诉MySQL实例启动时在哪里可以找到数据库文件，并且制定某些初始化参数，这些参数定义了某种内存结构的大小等设置，还会介绍各种参数的类型
• 日志文件：用来记录MySQL实例对某种条件作出响应时写入的文件，如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等
• socket文件：当用UNIX域套接字方式进行连接时需要的文件
• pid文件：
• MySQL表结构文件：表结构定义文件
• 存储引擎文件：每个存储引擎都会有自己的文件来保存各种数据。这些存储引擎真正存储了记录和索引等数据。

参数文件

• my.cnf：mysql --help|grep my.cnf
• show variables
• 参数类型：动态参数，可运行时修改，静态参数：生命周期内不可修改

mysql> set read_buffer_size=524288;
mysql> select @@session.read_buffer_size;
+----------------------------+
| @@session.read_buffer_size |
+----------------------------+
|                     524288 |
+----------------------------+
mysql> select @@global.read_buffer_size;
+---------------------------+
| @@global.read_buffer_size |
+---------------------------+
|                    131072 |
+---------------------------+

mysql> set @@global.read_buffer_size=264800;
mysql> select @@global.read_buffer_size;
+---------------------------+
| @@global.read_buffer_size |
+---------------------------+
|                    262144 |
+---------------------------+

日志文件

• 错误日志：对MySQL的启动、运行、关闭过程进行了记录。有时用户可以直接在错误日志文件中得到优化的帮助，因为有些警告（warning）很好地说明了问题所在。而这时可以不需要查看数据库状态来得知。

mysql> show variables like 'log_error';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| log_error     | /var/log/mysql/mysqld.log |
+---------------+---------------------------+

• 慢查询日志

long_query_time 慢查询阈值
log_slow_queries 慢查询是否打开(该参数要被slow_query_log取代，做兼容性保留) set global log_slow_queries = ON;
log_queries_not_using_indexes：当SQL没有使用索引，是否会记录到慢查询日志
log_throttle_queries_not_using_indexes:允许每分钟记录到慢查询日志文件的日志条数，0为不限制
slow_query_log 慢日志是否打开
slow_query_log_file 慢日志文件

mysqldumpslow cptest003-slow.log :慢日志分析

• 查询日志：查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行信息
  • general_log

show variables like '%general%';
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| general_log      | OFF           |
| general_log_file | cptest003.log |
+------------------+---------------+

• 二进制文件：记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。然而，若操作本身并没有导致数据库发生变化，那么该操作可能也会写入二进制日志。
  • 恢复（recovery）：某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复
  • 复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（slave）与一台MySQL数据库（master或primary）进行实时同步
  • 审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

  show master status
  show binlog events in 'binlog.000002' limit 200 -- 查看日志
  

  • 参数
    • max_binlog_size：单个binlog最大大小
    • binlog_cache_size：缓冲，基于会话的，也就是说，当一个线程开始一个事务时，MySQL会自动分配一个大小为binlog_cache_size的缓存。
    • 二进制文件并不是在每次写的时候同步到磁盘。sync_binlog=[N]表示每写缓冲多少次就同步到磁盘
    • binlog_format:影响记录二进制日志的格式
      • STATEMENT：二进制日志文件记录的是日志的逻辑SQL语句
      • ROW：记录表的行更改情况。可以将InnoDB的事务隔离级别设置我READCOMMITTED，以获得更好的并发性。
      • MIXED：默认采用STATEMENT，在一些情况下会使用ROW格式
  • 查看binlog：mysqlbinlog --start-position=203 test.000004。如果是ROW格式，可以加-vv 、-v

其他

• 表结构文件，frm后缀的文件
• 存储引擎文件
  • 共享表空间文件ibdata1。innodb_data_file_path ：ibdata1:10M:autoextend，表示初始为10M，用完自动扩展
  • innodb_file_per_table打开后，用户可以将每个基于InnoDB存储引擎的表产生一个独立的表空间。命名规则为 表名.ibd
  • 这些单独的表空间仅存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的表空间
• 重做日志文件：redo log file，在数据库目录下的两个文件，ib_logfile0和ib_logfile1
  • 当实例故障时，重做日志文件就派上用场了
  • redo log大小：innodb_log_file_size
  • 每个日志文件组里的文件数：innodb_log_file_size
  • 文件组目录：innodb_log_group_home_dir
  • binlog和redo log的区别
    • binlog记录与MySQL数据库有关的日志记录，包括InnoDB、MyISAM、Heap等其他存储引擎的日志。而redo log只记录引擎本身的事务日志。
    • 记录的内容不同，binlog记录一个事务的具体操作内容，即该日志是逻辑日志。而redo log记录的是关于每个页（Page）的更改物理情况
    • 写入时间不同，binlog仅在提交前进行提交，即只写磁盘一次，不论这时事务多大。而在事务进行的过程中，去不断有重做日志条目被写到redo log file中。
  • 写入重做日志文件的操作不是直接写，而是先写入一个重做日志缓冲（redo log buffer）中，然后按照一定的条件顺序地写入日志文件。在重做日志缓冲往磁盘写入时，是按512字节，也就是一个扇区大小进行写入。因为扇区是写入的最小单位，因此可以保证写入是必定成功的，因此不需要有doublewrite。
  • 主线程每秒会将redo log buffer写入磁盘，不论事务是否已经提交。另一个触发磁盘写入的过程是由参数innodb_flush_log_at_trx_commit控制的。
    • 0代表提交事务时，不刷新redo log buffer，而是等待主线程刷新。数据库crash，可能会丢失数据
    • 1代表commit时将redo log buffer同步到磁盘，即伴有fsync的调用。
    • 2表示异步写入磁盘，即写到文件系统的缓存中，等待文件系统调度。数据库crash，但是操作系统没有crash，数据不丢失。

第四章 表

• 在InnoDB中，每张表都有个主键，如果在创建表时没有显式定义主键，则InnoDB会按照如下方式选择主键：
  • 首先判断表中是否有非空的唯一索引（Unique NOT NULL），如果有，则该列即为主键
  • 如果不符合上述条件，InnoDB会自动创建一个6字节大小的指针.
  • 当表中有多个唯一索引时，主键选择第一个定义为非空唯一索引为主键。
  • select *,_rowid from z; // _rowid只能查看单列的主键索引
• InnoDB所有数据都被存放在一个空间里，称之为表空间（tablespace）。表空间又由段（segment）、区（extent）、页（page）组成。
• 启用innodb_file_per_table参数，每张表空间内存放的只是数据、索引和插入缓冲Bitmap页，其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次缓冲（doublewrite buffer）等还是存放在原来的共享表空间ibdata1内。
• 表空间不会缩小：Innodb不会回收表空间，但是会自动判断空间是否被使用，没有使用则标记可供下次重复使用。
• 分析表空间

# ruby新版本安装 https://blog.csdn.net/loveshunyi/article/details/99583505
yum install ruby
gem install innodb_ruby
ruby -r rubygems -I lib bin/innodb_space -f /var/lib/mysql/ibdata1 space-page-type-summary

type                count       percent     description
INDEX               1556538     85.56       B+Tree index
ALLOCATED           145864      8.02        Freshly allocated
BLOB                96661       5.31        Uncompressed BLOB
UNDO_LOG            19326       1.06        Undo log
IBUF_FREE_LIST      246         0.01        Insert buffer free list
INODE               237         0.01        File segment inode
SYS                 168         0.01        System internal
IBUF_BITMAP         110         0.01        Insert buffer bitmap
XDES                110         0.01        Extent descriptor
FSP_HDR             2           0.00        File space header
TRX_SYS             2           0.00        Transaction system header

• 表空间由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB是索引组织的，数据段为B+树的叶子节点，索引段为B+树的非索引节点。
• 区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InnoDB一次从磁盘申请4~5个区。默认情况下，页大小为16KB，即一个区有64个连续页。
• 当采用独立空间表时，表默认大小为96KB，先使用最多32个碎片页来存储数据，在使用完这些页之后才是64个连续页的申请。这样做的目的是，对于一些小标，或者是undo这类的字段，可以在开始时申请较少的空间，节省磁盘容量。
• 页是InnoDB磁盘管理的最小单位，默认为16KB，从1.2.X开始，可以通过参数innodb_page_size将页的大小设置为4K，8K、16K。若设置完成，则所有页的大小都是innodb_page_size，不可以对其再次进行修改。除非通过mysqldump导入和导出操作来产生新的库。常见的页类型有：
  • 数据页 B-tree Node
  • undo页 undo Log Page
  • 系统页 System Page
  • 事务数据页 Transaction system Page
  • 插入缓冲位图页 Insert Buffer Bitmap
  • 插入缓冲空闲列表页 Insert Buffer Free List
  • 未压缩的二进制大对象页 Uncompressed BLOB Page
  • 压缩的二进制大对象页 compressed BLOB Page
• 行：InnoDB是面向列的（row-oriented），也就是说数据是按行来存储的，即把一条记录的所有属性（列）存储在一起。每个页存放的行记录也是硬性定义的，最多允许存放16KB/2-200行的记录(7992)。
• 行记录格式：Compact，设计目标是高效地存储数据。简单来说，一个页中存放的数据越多，其性能就越高。(行格式图示)[https://blog.csdn.net/shui2104/article/details/108103026]
  • 倒序的变长字段列表，null标志位，5字节记录度头，自动创建的索引，事务id，数据
  • 不管是CHAR还是VARCHAR，在compact格式下NULL值都不占用任何存储空间。
  • 长度字段最长为两字节，所以varchar最长为65535
  • 记录头最后两个字节表示下一条记录相对于本条记录的相对偏移。所以InnoDB在页内部是通过一条链表的结构串联各个行记录的。
  • 行溢出数据：MySQL定义一行长度为65535是指所有VARCHAR列的长度总和,超过则不能创建，需要使用text。InnoDB页为16KB，怎么存放65532个字节呢？在一般情况下，数据存放在B-tree node中。但是当发生行溢出时，数据存放在页类型为Uncompress BLOB页中。要保证数据存放在数据页中，需要保证每一个页存放两条数据，阈值长度为8098。 当使用text或者BLOB的数据类型，如果能保证一个数据页存放两条数据，也会采用数据页存放。

  mysql> create table test( a varchar(65532))charset=utf8;
  ERROR 1074 (42000): Column length too big for column 'a' (max = 21845); use BLOB or TEXT instead
  mysql> create table test( a varchar(65532))charset=utf8mb4;
  ERROR 1074 (42000): Column length too big for column 'a' (max = 16383); use BLOB or TEXT instead
  

• InnoDB数据页中的Page Directory中存放了记录的相对位置，但并不是每一条记录都有相对位置。B+树索引本身并不能找到具体的一条记录，能找到的只是记录所在的页。数据库把页载入到内存中，然后从Page Directory再进行二叉查找，找到一个粗略的结果，此时采用记录内的header的next_record来继续查找相关记录。
• 用户创建了一个唯一索引就创建了一个唯一的约束，但是约束和索引有所不同，约束是一个逻辑概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表者物理存储的方式。
• 分区：分区功能并不是在存储引擎层完成的，因此不是只有InnoDB存储引擎支持分区，常见的存储引擎MyISAM、NDB等都支持。
  • 分区过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立对象，可以独立处理，也可以作为一个更大对象的一部分进行处理。
  • 分区类型为水平分区（不同的行记录分散在不同的物理文件中）
  • 分区为局部索引，一个分区中即存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中，目前MySQL不支持全局分区(5.6)。
  • 创建分区时，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

  create table t1( 
    col1 int not null, 
    col2 int not null, 
    col3 int not null, 
    col4 int not null, 
    unique key (col1,col2))
  partition by hash(col3) partitions 4;
  ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
  

  • 分区类型
    • RANGE分区：行数据基于属于一个给定区间的列值被放入分区。
    • LIST分区：面向离散的值做分区
    • HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。
    • KEY分区：根据MySQL提供的hash函数来进行分区
    • COLUMNS分区，和上面四个分区组合使用，支持非数字，上面四个分区单独使用需要转化为数字
  • 子分区：对分区再进行分区
  • NULL值的分区处理
    • RANGE分区，如果向分区插入NULL值，则MySQL会将该条记录插入最左边的分区
    • LIST分区，则必须显示地指出那个分区中放入NULL值，否则会报错
    • HASH和KEY分区，对于NULL值，任意分区函数都会将NULL值的记录返回0.
  • 数据库应用分为两类：一类是OLTP（在线事务处理），如Blog、电子商务、网络游戏等；另一类是OLAP（在线分析处理），如数据仓库、数据集市等。
  • 采用分区表不一定能提升性能，假如对于一张大表1000w数据，没有采用分区表前为3层B+树，采用10个分区，每个分区B+树层级为2，那么如果扫描10个分区，就需要进行20次IO，而之前的表只需要2次IO，这样性能就会急剧下降。

第五章 索引和算法

• 常见的索引：B+树索引，全文索引和哈希索引
• B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到查找的数据。
• B+树索引是为磁盘或其他直接存取辅助设备设计的一种平衡树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。
• B+树图示
• 聚集索引：叶子节点存放具体数据。聚集索引的存储并不是物理上连续的，而是逻辑上连续的。
• 非聚集索引（辅助索引）：叶子节点存放索引值和索引相对应的记录位置，在InnoDB中是聚集索引键。
• 索引管理：Cardinality值非常关键，优化器会根据这个值来判断是否使用这个索引。但是这个值不是实时更新的，即并非每次索引的更新都会更新该值，因为这样代价太大了。

mysql> show index from wm_user_user\G
......
*************************** 3. row ***************************
        Table: wm_user_user     // 
   Non_unique: 1                // 是否为非唯一索引
     Key_name: name
 Seq_in_index: 1                // 索引中该列的位置，对于联合索引 a_c,c就为2
  Column_name: user_name
    Collation: A                // 列以什么方式存储在索引中。B+树的索引总是A
  Cardinality: 175359           // 索引中唯一值的数目的估计值
     Sub_part: NULL             // 是否是列的部分被索引。全列索引为NULL，name(100)部分索引则为100
       Packed: NULL
         Null:                  // 索引中是否存在null值
   Index_type: BTREE
      Comment:
Index_comment:

• InnoDB存储引擎实现Online DDL的原理是执行创建或者删除操作的同时，将INSERT、UPDATE、DELETE这类DML操作日志写入到一份缓存中。待完成索引创建后再重做应用到表上，以此达到数据的一致性。缓存innodb_online_alter_log_max_size默认大小为128M，如果更新的表比较大，而且有大量的写事务，当缓存空间不够存放日志时，会报错，调大缓存日志即可。

mysql> show variables like "innodb_online%";
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| innodb_online_alter_log_max_size | 134217728 |

• Cardinality的统计：默认InnoDB存储引擎对8个叶子节点(Leaf Page)进行采样
  • 取得B+树索引中的叶子节点数目。记为A
  • 随机取得B+树索引中的8个叶子节点。统计每个页不同记录的个数，即为P1，P2，…，P8
  • 根据采样信息给出Cardinality的预估值：(P1+P2+…+P8)*A/8

mysql> show variables like "innodb_stats%";
+--------------------------------------+-------------+
| Variable_name                        | Value       |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc             | ON          |
| innodb_stats_include_delete_marked   | OFF         |
| innodb_stats_method                  | nulls_equal |  // 所有null值为一个记录
| innodb_stats_on_metadata             | OFF         |  
| innodb_stats_persistent              | ON          |
| innodb_stats_persistent_sample_pages | 20          |  // 表示analyze table test，更新Cardinality值的每次采样数目
| innodb_stats_transient_sample_pages  | 8           |  // 表示系统采样的页数
+--------------------------------------+-------------+

• Cardinality在InnoDB引擎内部更新的策略：
  • 表中1/16的数据已发生变化
  • stat_modified_counter>2000000000。发生变化的次数
• 联合索引：不能跳过中间的字段，否则只能使用跳过字段前面的索引
• 覆盖索引（covering index，或称为索引覆盖）：即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。explain Extra列的Using index就是代表了优化器进行覆盖索引操作。
• 对于不能进行索引覆盖的查询，如果查询的数据量很小，则优化器一般会选择使用辅助索引，但是当访问的数据占整个表中数据的蛮大一部分（一般是20%）左右，优化器会选择通过聚集索引来查找数据。顺序读要远远快于离散读。
• 强制索引：FORCE INDEX(a_idx)

-- user_id是主键，采用强制索引或者索引提示之后不再使用索引查询，但是用到了索引覆盖。
mysql> explain select * from users USE INDEX(tel) WHERE user_id=100;
+------+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table        | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 175359 | Using where |
+------+-------------+--------------+------+---------------+------+---------+------+--------+-------------+

mysql> explain select * from users FORCE INDEX(tel) WHERE user_id=100;
+------+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table        | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 175359 | Using where |
+------+-------------+--------------+------+---------------+------+---------+------+--------+-------------+

• 索引提示：USE INDEX(b_idx)，一般情况下不需要使用索引提示，优化器在绝大多数情况下工作得都非常有效和正确。可能使用索引提示的情况
  • MySQL优化器错误的选择了某个索引，导致SQL语句运行的很慢。
  • 可以选择的所有很多，这时优化器选择执行计划时间的开销可能会大于SQL语句本身。
• Multi-Range Read（MRR）优化：目的就是为了减少磁盘的随机访问，并将随机访问转化为较为顺序的数据访问。
  • show variables like “optimizer_switch”; mrr=on,mrr_cost_based=on 。mrr_cost_based=on打开表示是否通过cost based的方式选择启用mrr。
  • 步骤
    • 将查询得到的辅助索引键值存放在一个缓存中，这时缓存中的数据是根据辅助索引键值排序的
    • 将缓存中的键值根据RowID进行排序
    • 根据RowID的排序来访问实际的数据文件。
  • 优点：
    • MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找
    • 减少缓冲池中页被替换的次数
    • 批量处理对键值的查询操作
• 索引下推（index condition pushdown）ICP：
  • 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。
  • 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。
  • 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
  • 根据explain解析结果可以看出Extra的值为Using index condition，表示已经使用了索引下推。
• 哈希索引：InnoDB使用哈希算法来对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方式。
• 对于缓存池页的哈希表来说，在缓冲池中的Page页都有一个chain指针，它指向相同哈希函数值的页。而对于除法散列，m的取值略大于2倍的缓冲池页数量的质数。
  • 每个表空间有一个space_id，用户所要查找的应该是某个表空间的某个连续16KB的页，即偏移量offset，关键字为K = (space_id<<20) + space_id +offset，通过除法散列找到对应槽。
• 自适应哈希索引：哈希索引只能用来搜索等值的查询。，下面包含自适应哈希大小，每秒哈希查询数和非哈希查询数。

mysql> show engine innodb status\G
Hash table size 3104531, node heap has 3105 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

• 全文检索：将存储在数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。
• 倒排索引：它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这通常利用关联数组实现，其拥有两种表现形式：
  • inverted file index，其表现形式为{单词，单词所在文档的ID}
  • full inverted index，其表现形式为{单词，(单词所在的文档的ID，在具体文档中的位置)}
• InnoDB从1.2.x开始支持全文检索的技术，其采用full inverted index的方式。
  • 使用6张辅助表，提高全文检索的并发性能，表为持久化表
  • 全文检索缓存（FTS Index Cache）：红黑树结构。当对数据进行插入时，先写入数据表，在写FTS缓存，但并不更新辅助表。当发生查询时，再将缓存和辅助表合并再查询。
• InnoDB全文索引使用限制：
  • 每张表只能有一个全文检索的索引
  • 由多列组合而成的全文检索的索引列必须使用相同的字符集与排序规则
  • 不支持没有单词界定符(delimiter)的语言，如中文、日语、韩语等。

第六章 锁

• 数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。
• 在数据库中，lock和latch都可以被称为“锁”。但是两者有不同的含义
  • latch一般是轻量级的锁，因为其要求锁定时间必须非常短。若持续时间长，则应用的性能会非常差。在InnoDB中，latch又可以分为mutex（互斥量）和rwlock（读写锁）。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。
  • lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事务commit或rollback后进行释放（不同事务隔离级别释放的时间可能不同）。此外lock是有死锁机制的。

  mysql> show engine innodb mutex;
  +--------+----------------------------+---------+
  | Type   | Name                       | Status  |
  +--------+----------------------------+---------+
  | InnoDB | rwlock: fil0fil.cc:3337    | waits=1 |
  | InnoDB | sum rwlock: buf0buf.cc:784 | waits=5 |
  +--------+----------------------------+---------+
  

• 行级锁的类型
  • 共享锁（S Lock）：允许事务读一行数据
  • 排他锁（X Lock）：允许事务删除或更新一行数据
• InnoDB支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持多粒度锁定，InnoDB支持意向锁。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。
  • InnoDB支持意向锁设计非常简练，其意向锁即为表级别的锁。
  • 意向共享锁（IS Lock），事务想要获得一张表中某几行的共享锁
  • 意向排他锁（IX Lock），事务想要获得一张表中某几行的排他锁
• 一致性非锁定读：是指InnoDB通过行多版本控制的方式来读取当前执行时间数据库中行的数据。
  • 如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会等待行上锁的释放，而是会去读取行的一个快照数据。
  • 快照数据：是指改行之前的一个版本的数据，该实现是通过undo段来完成。而undo用来在事务中回滚数据，因此快照数据本身没有额外的开销。此外读取快照数据是不用上锁的，因为没有事务需要对历史数据进行修改操作。
  • 一个行记录可能有不止一个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（MVCC）。
  • 在事务隔离级别READ COMMITTED和REPEATABLE READ（InnoDB默认）下，InnoDB使用非一致性读。然而对于快照数据的定义是不同的。在READ COMMITTED事务级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。而在REPEATABLE READ事务隔离级别下，对于快照数据，非一致性读总是读取事务开始的时的行数据版本。
• 一致性锁定读：对于已经执行了一致性读的，并不影响非一致性读
  • SELECT … FOR UPDATE：对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁。
  • SELECT … LOCK IN SHARE MODE：对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。
  • 对于非一致性读，当事务提交了，锁也就释放了，所以使用时，需要在事务中使用，BEGIN ，start transaction或者SET AUTOCOMMIT=0。
• 自增长和锁：基于互斥量对内存计数器累加的操作。在InnoDB中，自增长值的列必须是索引，同时必须是索引的第一个列。
• 外键和锁：对于外键值的插入和更新，首先会查询父表的记录，即SELECT父表。但是对于父表的SELECT操作，不是使用一致性非锁定读的方式，因为这样会发生数据不一致的问题，因此这时使用的是SELECT … LOCK IN SHARE MODE方式，即主动对父表加一个S锁。如果这时父表已经加X锁，子表的操作会阻塞。
• 锁的算法
  • Record Lock：单个行记录的锁
  • Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。目的是为了阻止多个事务将记录插入到同一范围内。
  • Next-Key Lock：Gap Lock+Record，锁定一个范围，并且锁定记录本身
• 采用Next-Key Lock的锁定技术成为Next-Key Locking。其设计的目的是为了解决幻读问题。
• 当查询的索引含有唯一属性时，InnoDB会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围。
• 幻读：是指在同一事务下，连续执行两次同样的SQL语句可能会导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。
• 脏读：指的是在不同的事务下，当前事务可以读到另外事务未提交的数据，

第七章 事务

• 事务会把数据从一种一致状态转换为另一种一致状态。
• 事务特性ACID：
  • A（Atomicity），原子性。指整个数据库事务是一个不可分割的工作单位。要么都做，要么都不做。只有使事务中所有的数据库操作都执行成功，才算事务成功。否则，已近执行的SQL必须撤销，数据库状态回到修改前的状态。
  • C（Consistency），一致性。指事务将数据库从一种状态转变为下一种一致的状态。在事务开始之前和事务结束之后，数据库的完整性约束没有被破坏。例如，在一个有唯一约束的姓名字段，如果事务对姓名修改，导致了姓名字段变为非唯一，这就破坏了事务的一致性要求，即事务将数据库从一种状态变为了一种不一致的状态。因此，事务是一致性的单位，如果事务中某个操作失败了，系统可以自动撤销事务——返回初始化状态。
  • I（Isolation），隔离性。隔离性还有其他称呼，如并发控制、可串行化、锁等。事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见，通常这使用锁来实现。
  • D（Durability），持久性。事务一旦提交，其结果就是永久性的。即使发生宕机等故障，数据库也能将数据恢复。需要注意的是，只能从事务本身的角度来保证结果的永久性。例如，在事务提交后，所有的变化都是永久的。即使数据库因为崩溃而需要恢复时，也能保证恢复后提交的数据不会丢失。但若不是数据库本身发生了故障，而是一些外部原因，如RAID卡损坏、自然灾害等原因导致数据库发生问题，那么所提交的数据可能都会丢失。因此持久性保证事务系统的高可靠性（High Reliability），而不是高可用性（High Availability）。对于高可用性的实现，事务本身并不能保证，需要一些系统共同配合完成。
• 事务分类
  • 扁平事务：在扁平事务中，所有的操作都在同一层次，其由BEGIN WORK开始，由COMMIT WORK或ROLLBACK WORK结束，其间的操作是原子的，要么都执行，要么都回滚。因此扁平事务是应用程序成为原子操作的基本组成模块。
  • 带有保存点的扁平事务：除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态。
    • 这是因为某些事务可能在执行过程中出现的错误并不会导致所有的操作都失效，放弃整个事务不合乎要求，开销也太大。
    • 保存点（SavePoint）用来通知系统应该记住事务当前的状态，以便当之后发生错误时，事务能回到保存点当时的状态。
    • 对于扁平的事务，其隐式地设置了一个保存点。然而在整个事务中，只有这一个保存点，因此，回滚只能回滚到事务开始时的状态。
    • 保存点用SAVE WORK函数来建立，通知系统记录当前的处理状态。当出现问题时，保存点能用作内部的重启动点，根据应用逻辑，决定是回到最近一个保存点还是其他更早的保存点。
  • 链事务：可视为保存点模式的一种变种。
    • 带有保存点的扁平事务，当发生系统崩溃时，所有的保存点都将消失，因为其保存点是易失的，而非持久化的。这意味着当进行恢复时，事务需要从开始处从新执行，而不能从最近的一个保存点继续执行。
    • 链事务的思想是：在提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始的事务。注意，提交事务操作和开始下一个事务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果，就好像在一个事务中进行一样
    • 链事务和带有保存点事务不同的是，带有保存点的扁平事务能回滚到任意正确的保存点。而链事务中的回滚仅限于当前事务，及只能恢复到最近一个保存点。对于锁的处理，两者也不相同。锁事务在执行COMMIT后即释放了当前事务所持有的锁，而带有保存点的扁平事务不影响迄今为止所持有的锁。
  • 嵌套事务：一个层次框架。由一个顶层事务控制着各个层次的事务。
  • 分布式事务：通常是一个在分布式环境下运行的扁平事务，因此需要根据所在位置访问网络中不同的节点。
  • 对于InnoDB来说，其支持扁平事务、带有保存点的事务、链事务、分布式事务。
• 事务的实现
  • 原子性、一致性、持久性通过数据库的redo log和undo log来完成。redo log称为重做日志，用来保证事务的原子性和持久性。undo log用来保证事务的一致性。
  • redo和undo的作用都可以视为是一种恢复操作，redo恢复提交事务修改的页操作，而undo回滚行记录到某个特定版本。因此两者记录的内容不同，redo通常是物理日志，记录的是页的物理修改操作。undo是逻辑操作，根据每行记录进行记录。
  • redo：重做日志用来实现事务的持久性。其由两部分组成：一是内存中的重做日志缓冲（redo log buffer），其是易失的；而是重做日志文件（redo log file），其是持久的。
    • InnoDB通过Force Log at Commit机制实现事务的持久性，即当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的COMMIT操作完成才算完成。这里的日志是指重做日志，在InnoDB中，由两部分组成，即redo log和undo log。redo log用来保证事务的持久性，undo log用来帮助事务回滚及MVCC的功能。redo log基本上都是顺序写的，在数据库运行时不需要对redo log的文件进行读取操作。而undo log是需要进行随机读写的。
    • 为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB都需要调用一次fsync操作。InnoDB也允许用户设置非持久化的情况发生，以此提高性能。参数innodb_flush_log_at_trx_commit
      • 0表示事务提交时不进行写入重做日志操作，这个操作仅在master thread中完成
      • 1，默认，事务提交时必须调用一次fsync操作
      • 2表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行fsync操作。数据库宕机，不会丢失数据，操作系统宕机，则可能丢失未从文件系统刷新到重做日志文件那部分事务。
    • 二进制日志只在事务提交完成后进行一次写入。而InnoDB的重做日志在事务进行中不断地被写入，这表现为日志并不是随事务提交的顺序进行写入的。
  • 重做日志都是以512字节进行存储的。这意味着重做日志缓存、重做日志文件都是以块（block）的方式进行保存的，称之为重做日志块（redo log block），每块的大小为512字节。
    • 由于重做日志块的大小和磁盘扇区大小一样，都是512字节，因此重做日志的写入可以保证原子性，不需要doublewrite技术。
    • 日志块由三部分组成，日志块头（12字节）、日志内容（492字节）、日志尾（8字节）
  • log buffer根据一定的规则将内存中的log block刷新到磁盘。这个规则具体是：
    • 事务提交时
    • 当log buffer中有一半的内存空间已经被使用时
    • log checkpoint时
    • 对于log block的写入追加（append）在redo log file的最后部分，当一个redo log file被写满时，会接着写入下一个redo log file，其使用方式为round-robin。
  • LSN:代表日志序列号，含义有
    • 重做日志的写入的总量
    • checkpoint的位置
    • 页的版本

  mysql> show engine innodb status\G
  LOG
  ---
  Log sequence number 5122679714160 // 当前LSN
  Log flushed up to   5122679714160 // 刷新到重做日志文件的LSN
  Last checkpoint at  5122679714160 // 刷新到磁盘的LSN
  Max checkpoint age    7782360
  Checkpoint age target 7539162
  Modified age          0
  Checkpoint age        0
  0 pending log writes, 0 pending chkp writes
  22279373 log i/o's done, 0.00 log i/o's/second
  

  • 恢复：InnoDB在启动时不管上次数据库运行时是否正常关闭，都会尝试进行恢复操作。因为重做日志记录的是物理日志，因此恢复的速度比逻辑日志，如二进制日志，要快得多。由于checkpoint表示已经刷新到磁盘页上的LSN，因此在恢复过程中仅需恢复checkpoint开始的日志部分。
• undo：事务的回滚
  • 对于数据库进行修改，InnoDB不但会产生redo，还会产生一定量的undo。
  • redo存放在重做日志文件中，与redo不同，undo存放在数据库内部的一个特殊段（segment）中，这个段称为undo段（undo segment）。undo 段位于共享表空间内。
  • undo是逻辑日志，只是将数据库逻辑地恢复到原来的样子。所有的修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。
  • 除了回滚操作，undo的另一个作用是MVCC，即在InnoDB中MVCC的实现是通过undo来完成。当用户读取一行记录，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。
  • undo log会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。
  • undo存储管理：128个rollback segment，每个回滚段中记录了1024个undo log segment，而在每个undo log segment上进行undo页的申请。
  • 参数innodb_undo_directory用于设置rollback segment文件所在的路径。这意味着rollback segment可以存放在共享表空间以外的位置，即可以设置独立表空间。
  • 当事务提交时，undo操作
    • 将undo log放入列表中，以供之后的purge操作
    • 判断undo log所在的页是否可以重用，若可以，分配给下个事务使用
  • 事务提交后不能马上删除undo log及undo log所在的页。这是因为可能还有其他事务需要通过undo log来得到行记录之前的版本。故事务提交时将undo log放入一个链表中，是否可以最终删除undo log及undo log所在页由purge线程来判断。
  • 当事务提交时，首先将undo log放入链表中，然后判断undo页的使用空间是否小于3/4，若是则表示该undo页可以被重用，之后新的undo log记录在当前undo log后。由于存放undo log的列表是以记录进行组织的，而undo页可能存放着不同事务的undo log，因此purge操作需要涉及磁盘的离散读取操作，这是一个比较缓慢的过程。

  TRANSACTIONS
  ------------
  Trx id counter 14559729364
  Purge done for trx's n:o < 14559729357 undo n:o < 0 state: running but idle
  History list length 295 
  

  • History list length 295：代表undo log的数量。purge操作会减少该值。然后由于undo log所在的页可以被重用，因此即使操作发生，History list length 的值也可以不为0。
  • undo log格式
    • insert undo log：指insert操作中产生的undo log。因为insert操作的记录，只对事务本身可见，对其他事务不可见（事务隔离性要求），故该undo log可以在事务提交后直接删除。不需要进行purge操作。
    • up date undo log：记录的是对delete和update操作产生的undo log。该undo log可能需要MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。
  • delete操作并不直接删除记录，而是将记录标记为已删除，也就是将记录的delete flag设置为1，记录还存在于B+树中。对辅助索引的记录也没有做任何处理，甚至没有删除undo log。而记录最终的删除是在purge操作中完成的。
  • update更新一个主键值，操作分两步完成。首先将原主键记录标记为已删除，之后插入一条新的记录。因此有两个undo log记录
  • purge：InnoDB先从History list中找undo log，然后再从undo page中找undo log的设计模式是为了避免大量的随机读取操作，从而提高purge的效率
  • 开启二进制日志之后的事务两阶段提交：
    • 1.当事务提交时InnoDB进行prepare操作
    • 2.MySQL数据库上层写入二进制文件
    • 3.InnoDB将日志写入重做日志文件
      • a.修改内存中事务对应的信息，并且将日志写入重做日志缓冲
      • b.调用fsync将确保日志都从重做日志缓冲写入磁盘。
    • 为了保证MySQL数据库上层二进制日志的写入顺序和InnoDB层的事务提交顺序一致，MySQL数据库内部使用了prepare_commit_mutex这个锁。但是在启用这个锁之后，步骤3中的步骤a不可以在其他事务执行步骤b时进行，从而导致group commit失效。
  • group commit：一次fsync可以刷新确保多个事务日志被写入文件。但是在InnoDB 1.2.x之前，事务的两阶段提交导致group commit失效。MySQL5.6 BLGC解决了这个问题，具体步骤如下
    • Flush阶段：将每个事务的二进制日志写入内存中
    • Sync阶段：将内存中的二进制日志刷新到磁盘，若队列中有多个事务，那么仅一次fsync操作就完成了二进制日志的写入，这就是BLGC（Binary Log Group Commit）
    • Commit阶段：leader根据顺序调用存储引擎层事务的提交，InnoDB本就支持group commit，因此修复了原先由于锁prepare_commit_mutex导致的group commit失效的问题
• 事务控制语句
  • START TRANSACTION|BEGIN：显示地开启一个事务
  • COMMIT：提交事务，并使得对数据库做的修改成为永久性的。
  • ROLLBACK：回滚
  • SAVEPOINT identifier：在事务中创建一个保存点，一个事务中可以有多个savepoint
  • RELEASE SAVEPOINT identifier：删除一个事务的保存点，当没有一个保存点执行这条语句时，就会抛出一个异常
  • ROLLBACK TO [SAVEPOINT] identifier：这个语句与SAVEPOINT一起使用。可以把事务回滚到标记点，而不回滚在此标记之前的任何工作。
  • SET TRANSACTION：设置事务隔离级别。
  • COMMIT和COMMIT WORK几乎等价，不同之处在于COMMIT WORK用来控制事务结束之后的行为是CHAIN还是RELEASE。参数completion_type控制：
    • 默认为0，表示没有操作
    • 1，COMMIT WORK等同于COMMIT AND CHAIN，表示马上开启一个相同级别的事务
    • 2，COMMIT WORK等同于COMMIT AND RELEASE，表示在事务提交后会自动断开与服务器的连接。
• 隐式提交的SQL语句：即执行完语句后，会有一个隐式的COMMIT操作。
  • DDL语句：ALTER，CREATE TABLE等
  • 修改MySQL架构的操作：CREATE USER,GRANT
  • 管理语句:ANALYZE TABLE
  • truncate table语句是DDL，因此虽然对整张表执行DELETE的结果是一样的，但它不能被回滚的。
• 事务统计：计算TPS的方法 (com_commit+com_rollback)/time。只有事务是显示提交的，才会计算到com_commit和com_rollback变量中。

mysql> show global statuscom_commit'\G
*************************** 1. row ***************************
Variable_name: Com_commit
        Value: 95

mysql> show global status like 'com_rollback'\G
*************************** 1. row ***************************
Variable_name: Com_rollback
        Value: 31

分布式事务

• 分布式事务php简单实现

// create table t(a int primary key);
function XAProcess($upId){
    $pdo1 = new PDO ("mysql:host=101.0.0.1;dbname=test;port=3306;charset=utf8mb4", "root", "123456", array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_TIMEOUT => 2,
    ));
    $pdo2 =  new PDO ("mysql:host=10.0.0.2;dbname=test;port=3306;charset=utf8mb4", "root", "123456", array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_TIMEOUT => 2,
    ));
    $xid = uniqid();
    $pdo1->query("XA START '{$xid}'");
    $pdo2->query("XA START '{$xid}'");
    try {
        $pdo1->exec("INSERT INTO t select {$upId}");
        $pdo2->exec("INSERT INTO t select {$upId}");
        $pdo1->query("XA END '{$xid}'");
        $pdo1->query("XA PREPARE '{$xid}'");
        $pdo2->query("XA END '{$xid}'");
        $pdo2->query("XA PREPARE '{$xid}'");

        $pdo1->query("XA COMMIT '{$xid}'");
        $pdo2->query("XA COMMIT '{$xid}'");
    } catch (Exception $e) {
        $pdo1->query("XA END '{$xid}'");
        $pdo1->query("XA PREPARE '{$xid}'");
        $pdo2->query("XA END '{$xid}'");
        $pdo2->query("XA PREPARE '{$xid}'");

        $pdo1->query("XA ROLLBACK '{$xid}'");
        $pdo2->query("XA ROLLBACK '{$xid}'");
        var_dump($e->getMessage());
    }
}

• InnoDB提供了XA事务的支持，并通过XA事务来支持分布式事务的实现。
• 分布式事务指的是允许多个独立的事务资源参与到一个全局的事务中。在使用分布式事务时，InnoDB的事务隔离级别必须设置为SERIALIZABLE。
• XA允许不同数据库之间的分布式事务。
• XA事务由一个或多个资源管理器、一个事务管理器以及一个应用程序组成。
  • 资源管理器：提供访问事务资源的方法。通常一个数据库就是一个资源管理器。
  • 事务管理器：协调参与全局事务中的各个事务。需要和参与全局事务的所有资源管理器进行通信。
  • 应用程序：定义事务的边界，指定全局事务中的操作。
• 在MySQL数据库的分布式事务中，资源管理器就是MySQL数据库，事务管理器为连接MySQL服务器的客户端。
• 分布式事务使用两阶段提交（two-phase commit）的方式。在第一阶段，所有参与全局事务的节点都开始准备（PREPARE），告诉事务管理器它们准备好提交了。在第二阶段，事务管理器告诉资源管理器执行ROLLBACK还是COMMIT。如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。可见与本地事务不同的是，分布式事务需要多一次PREPARE操作，待收到所有节点的同意信息后，再进行COMMIT或是ROLLBACK操作。
• 内部XA事务：在存储引擎与插件之间，又或者在存储引擎与存储引擎之间，称之为内部XA事务。最为常见的内部XA事务存在于binlog与InnoDB存储引擎之间。
  • innodb prepare redo log
  • write binlog
  • innodb commit
• 事务使用的误区
  • 在循环中提交事务：每一次提交都要写一次重写日志，可以把多个事务并为一个。
  • 使用自动提交：问题如上。开发人员需要了解自动提交的问题。
  • 使用自动回滚：如在存储过程中发生了错误会自动进行回滚。这时需要指明发生了什么错误。
  • 长事务：可以拆分为多个短事务处理

第八章 备份与恢复

• 备份方法分类
  • 热备（Hot Backup）：在数据库运行中直接备份，对正在运行的数据库没有任何影响。在线备份
  • 冷备（Cold Backup）：在数据库停止的情况下备份。一般只需要复制相关的数据库物理文件即可。离线备份
  • 温备（Warm Backup）：在数据库运行中备份，但是会对数据库操作有所影响，如加一个全局读锁以保证备份数据的一致性
• 备份文件的内容分类
  • 逻辑备份：指备份的文件是可读的，一般是文本文件。内容一般是一条条SQL语句，或者是表内实际数据组成。
  • 裸文件备份：复制数据库的物理文件，即可以在数据库运行中进行复制（如ibbackup、xtrabackup这类工具），也可以是在数据库停止运行时直接的数据文件复制。
• 备份数据库的内容分类：
  • 完全备份：对数据库进行一个完整的备份
  • 增量备份：在上次完全备份的基础上，对于更改的数据进行备份。MySQL无官方工具支持。原理：扫描当前每页最后检查点的LSN，如果大于之前全备时的LSN，则备份该页，否则不用备份，这大大加快了备份的速度和恢复的时间，同时这也是xtrabackup工具增量备份的原理。
  • 日志备份：对MySQL二进制日志的备份，通过对一个完全备份进行二进制重做（replay）来完成数据库的point-in-time的恢复工作。
• MySQL的数据库复制原理就是异步实时地将二进制日志重做传送并应用到从数据库。
• 逻辑备份，mysqldump
  • 使用–single-transaction选项来保证备份的一致性
  • –master-data[=value]：通过该参数产生的备份转存文件主要用来建立一个replication。value为1，转存文件中记录CHANGE MASTER语句。当value为2，CHANGE MASTER被写出SQL注释

  mysqldump -uroot -p --single-transaction --databases test>test_backup.sql
  

• SELECT … INTO OUTFILE

SELECT * INTO OUTFILE '/home/work/a.txt' from a;

• 逻辑备份恢复
  • mysql -uroot -p <test_backup.sql
  • mysql> source /home/work/test_backup.sql
  • mysql> load data infile ‘/home/work/test_backup.sql’ into table test;
  • 并发导入两张表：mysqlimport --user-threads=2 test /home/work/a.txt /home/work/b.txt
• 二进制日志备份与恢复：mysqlbinlog
• 复制的工作原理
  • 主服务器master把数据更改记录到二进制日志binlog中
  • 从服务器slave把主服务器的二进制日志复制到自己的中继日志（relay log）中。
  • 从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。

性能测试

find / -name 'oltp*.lua'
sysbench /usr/local/Cellar/sysbench/1.0.20/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-password=123456 --mysql-host=127.0.0.1 --tables=5 --table_size=1000000 --threads=10 --time=30 --report-interval=3  prepare
sysbench /usr/local/Cellar/sysbench/1.0.20/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-password=123456 --mysql-host=127.0.0.1 --tables=5 --table_size=1000000 --threads=10 --time=30 --report-interval=3  run
sysbench /usr/local/Cellar/sysbench/1.0.20/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-password=123456 --mysql-host=127.0.0.1 --tables=5 --table_size=1000000 --threads=10 --time=30 --report-interval=3  cleanup
#--threads:表示线程数
#--time:表示执行时间
#--report-interval:表示间隔多少秒输出测试信息