InnoDB基础

常用命令

• 配置文件位置
  mysql --help | grep my.cnf
• 数据库所在路径
  my.cnf中的datadir参数, 或在mysql中, 通过show variables like 'datadir';
• show engines 显示mysql支持的引擎;
• 通过unix域套接字来连接mysql

// .sock文件路径show variables like 'socket';// -S /xxx  练级mysql -uxxx -S /tmp/mysql.sock

• 异步IO(AIO)
  SHOW ENGINE INNODB STATUS,
• 线程列表 show processlist
• 删除表
  DELETE FROM tbl [where]; 慢, 返回个数;
  TRUNCATE TABLE tbl; 快, 返回0;
• 查看表的行, 内存, 索引大小

SELECT table_name, table_rows, data_length FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';

• INSERT INTO tbl(x,x,x) SELECT x,x,x FROM ...
• 显示索引

show index from tbl;show create table tbl;

innodb相关介绍

• 主要面向OLTP应用; (MyISAM, 不支持事务, 表锁设计, 全文index, 适合OLAP应用), (Memory, HEAP存储引擎, 数据放于内存中, 适合于临时数据表, 速度快)
• MVCC实现高并发;
• 4中隔离级别, 默认REPEATALBE READ, 即事务中, 重复执行一个SQL其结果是一样的, 不会受其它SQL的影响;
• 采用聚集(clustered)方式, 主键按顺序存放, 没有制定主键, 则自动生成6字节的ROWID列作为主键;

体系架构

线程

• master 线程
• 4 Read 线程, 4 Write线程,
• 1 insert buffer线程, 1 log线程
• purge线程gmail
  回收无用的undo log页, innodb_purge_threads设置purge线程的数量, 独立的线程减轻mster线程的压力;

内存

缓冲池

• innodb_buffer_pool_size为缓冲池大小
• 包括数据页, 索引页, 插入缓冲, 自适应哈希索引, lock info等; 还有其它缓冲如redo log 缓冲;
• innodb_buffer_pool_instances可以设置缓冲池数量;
• information_schema表查看缓冲池情况

SELECT POOL_ID, POOL_SIZE, FREE_BUFFERS, DATABASE_PAGES FROM INNFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;

缓冲池管理算法LRU, Free List, Flush List

• 页默认大小16KB;
• LRU Latest Recent Used, 最近最少使用, 最频繁使用的在LRU前端, 最少使用的在链表尾部, 当缓冲池Free List没有空间时, 释放LRU的尾部页, 数据放入链表头;
• Midpoint机制
  
  1. Innodb对LRU算法做了优化, 通过innodb_old_blocks_pct设置midpoint点, 新的页不是放到尾部, 而是midpoint位置, midpoint前面为new列表, 后面为old列表, 理解为new为活跃数据, old为非活跃数据;
  2. 该算法是为了防止某些情况下, 新页数据被频繁刷出, 影响性能, 如扫描操作, 一般都是临时读取数据, 如果页放在链表头, 可能会将所有的热点数据刷出LRU链表;
• 减少innodb_old_blocks_pct可以减少热点数据被刷出的频率;
• pages made young
  页从old加入到new时, 即转变为热门数据;
  通过表查询:

SELECT POOL_ID, HIT_RATE, PAGES_MADE_YOUNG, PAGES_NOT_MADE_YOUNG FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;

Flush List

• LRU中修改过的页即脏页dirty page, 内存数据功能磁盘数据不一致, 这是需要通过checkpoint机制将脏页刷新回磁盘;
• Flush List即脏页列表, 脏页即存在与LRU与存在与Flush List
• 查看LRU中的脏页:

SELECT TABLE_NAME, SPACE, PAGE_NUMBER, PAGE_TYPE FROM INNODB_BUFFER_PAGE_LRU WHERE OLDEST_MODIFICATION>0;

重做日志缓冲

• redo log先放入这个缓冲, 再fsync到磁盘;
• innodb_log_buffer_size, 默认8MB;
• fsync时机:
  
  1. master线程每秒执行一次;
  2. COMMIT操作;
  3. innodb_log_buffer_size空间小于1/2;

checkpoint机制

将脏页刷新回磁盘.

• sharp checkpoint即服务器关机时将所有的脏页刷新回磁盘
• fuzzy checkpoint 运行时只刷新一部分脏页;
• fuzzy checkpoint时机:
  
  1. Master Tread Checkpoint
     master线程每1或10秒刷新回一部分脏页;
  2. FLUSH_LRU_LIST Checkpoint
     innodb_lru_scan_depth表示LRU列表中可用页的数量, 默认1024, 如果少于这个值, 则会启动checkpoint;
  3. Async/Sync Flush Checkpoint
     若redo_lsn-checkpoint_lsn达到总redo日志大小一定比例时, 就会启动;
  4. DIrty Page too much Checkpoint
     缓冲池中脏页数量占据75%时启动;

关键特性

插入缓冲(insert buffer / change buffer)

• innodb主键是行唯一标识符, 聚集索引的插入一般是顺序的, 不需要随机读取, 速度非常快;
• 如果表中有非唯一的辅助索引, 则此刻插入可能需要随机读取非聚集索引页, 所以这里用了insert buffer来解决这个问题;
• 对于非聚集索引或非唯一索引, 其插入或更新操作, 不是每次都直接插入到索引页中, 而是判断插入的非聚集索引页是否在缓冲池中, 在的话则直接插入, 没有则先放到一个insert buffer中. 然后再以一定的频率将insert buffer与辅助索引页的子节点的meige操作.
• change buffer是insert buffer的升级, 包括insert buffer, delete buffer, pruge buffer;

double write

• partial page write
  数据库在写入某页到表时发生宕机, 且只写了一半, 此时可能无法通过redo log来恢复, 这里就需要用到doble write来解决;

• doublewirte buffer与物理磁盘共享表空间128页空间(2个区extent)
  对缓冲池的脏页进行刷新, 不是直接写磁盘, 而是通过memcpy复制到内存的doublewrite buffer, 在分两次, 每次1MB(64个页)顺序的写入到共享表空间的物理磁盘上, 然后马上调用fsync进行磁盘同步;

• SHOW GLOBAL STATUS LIKE 'innodb_dblwr%';
  其中, innodb_dblwr_written实际写入的页数, innodb_dblwr_writes为写入的次数, 正常两者为64:1, 如果远小于64:1, 则说明写入压力不大;

启动, 关闭, 恢复

关闭

参数innodb_fast_shutdown:可以设为0,1,2;

• innodb_fast_shutdown=0
  关闭时, 完成所有的full purge 和 merge insert buffer, 并将所有的脏页刷新回磁盘; 数据量大时, 可能非常耗时, 可以先将参数设置为2, 在服务器下次启动时会执行恢复;
• innodb_fast_shutdown=1
  默认配置, 不完成full purge 和 merge insert buffer, 但要将所有的脏页刷新回磁盘;
• innodb_fast_shutdown=1
  不完成full purge 和 merge insert buffer, 也不刷新脏页回磁盘; 只将日志文件写回日志文件, 保证事务不会丢失, 下次启动时完成恢复;

恢复

参数innodb_force_recovery

• innodb_force_recovery=0, 默认为0, 启动时执行所有恢复;

  有时不需要服务器自己执行恢复, 因为很慢, 我们可以自定导数据到新表;

• innodb_force_recovery=1, SRV_FORCE_IGNORE_CORRUPT, 忽略检查的corrupt页;

• innodb_force_recovery=2, SRV_FORCE_NO_BACKGROUND,阻止master thread运行(会执行full purge, 导致crash);
• innodb_force_recovery=3, SRV_FORCE_NO_TRX_UNDO, 不进行事务回滚;
• innodb_force_recovery=4, SRV_FORCE_NO_IBUF_MERGE, 不进行插入缓冲的合并操作;
• innodb_force_recovery=5, SRV_FORCE_NO_UNDO_LOG_SCAN, 不查看撤销日志, 会将为提交的事务作为已提交;
• innodb_force_recovery=6, SRV_FORCE_NO_LOG_REDO, 不进行前滚操作;

慢查询

相关参数

• 开启: log_slow_queries
• 慢查询阈值时间(s): long_query_time
• 文件: slow_query_log_file

mysqldumpslow

• 执行时间最长的10条SQL语句

mysqldumpslow -s al -n 10 xxx.log

慢查询记录到表中

• 参数log_output默认为FILE, 可以配置为TABLE;
• 查询slow_log表:

SELECT * FROM mysql.slow_log;

分区

procedure

DELIMITER $$CREATE PROCEDURE()BEGINDECLARE dt DATE;DECLARE ds DATE;DECLARE de DATE;DECLARE dInt INT;SET ds = DATE(20170310);SET de = DATE(20170320);SET dt = ds;WHERE dt <=de DOSET dInt = YEAR(dt);Call trans_data('alph','dInt');  # 调用过程..SET dt = DATE_ADD(dt, INTERVAL | DAY);END WHILE;END; $$DELIMITER ; 

4种隔离级别

• READ_UNCOMMITTED
  可以读取到其它事务未提交的修改;
• READ_COMMITTED
  在同一事务中, 能读取事务中已经修改过的数据(隔离性Isolation没有满足), 但未提交前, 修改对其它事务不可见;
• REPEATABLE_READ
  只会读取到已提交的结果, 同一事务同一SQL语句查询的结果永远一致(满足Isolation特性);
• SERIALIZABLE
  与REPEATABLE类似, 如果autocommit=0, 则SELECT会自动转化为SELECT .... LOCK IN SHARE MODE;, 会对SELECT加上S锁, 造成阻塞;

锁

MyISAM

表级锁;

InnoDB

行级锁, 表级锁

• latch
  底层锁, 如mutex, rwlock, 轻量级, 时间短;
• lock
  事务级锁, 一般在COMMIT, ROLLBACK后释放, 有死锁机制;

innodb lock类型

• S Lock, 共享锁, 用于读;
• X Lock, 排它锁, 用于写;

表级锁(意向锁)

• IS Lock, 事务要获得一张表中某几行的共享锁;
• IX Lock, 事务要获得一张表中某几行的排它锁;

一致性锁定读

innodb默认隔离级别为REPEATABLE READ, 为一致性非锁定读, 所以某些情况下需要用户通过主动枷锁的方式来保证逻辑一致性, 实现一致性锁定读;

• 加X Lock

SELECT ... FOR UPDATE; 

• 加S Lock

SELECT ... LOCK IN SHARE MODE

幻读Phantom Problem

在同一事务下, 连续执行两次同样的SQL语句, 可能导致不同的结果. 第二个SQL可能返回与第一个SQL不存在的行;

这个特效使得其不满足I特性; mysql通过next-key lock来解决; 如数据中有id=1,2,5, 执行

SELECT * FROM t WHERE id>2 FOR UPDATE;

这里id>2只有5, 而由于next-key lock存在, 这里锁住的不仅仅时id=5, 而是id>2, 所以如果此时执行INSERT INTO t id=4是会被阻塞的;

锁的问题

1. 脏数据
   事务对缓冲池进行修改却还没有提交, 如果另一个事务读到了该未提交的数据, 即违背了隔离性I, 就是读到了脏数据;
2. 幻读(不可重复读)
   一个事务中两次相同的读取结果不一样, 其与脏读的区别是, 幻读读到是另一个已经提交了的事务的修改;
3. 更新丢失
   两个事务同时更新, 一个被另一个覆盖; 可以通过X Lock解决;

锁是的事务阻塞, 其阻塞时间为参数innodb_lock_wait_timeout

事务

• innodb的事务满足ACID特性: Automicity, Consistency, Isolation, Durability
• 事务是数据库从一种状态转变到另一种状态, 提交事务时, 要么所有的修改都已提交, 要么所有的都没保存;
• Isolation: 针对并发, 即一个事务在提交前其修改对其它事务是不可见的;
• Durability: 锁一旦提交, 其修改是永久的, 即使发生宕机, 也能恢复;

事务启动

• BEGIN
• START TRANSACTION
• SET AUTOCOMMIT=0

隐式提交SQL

• DML: ALTER TABLE, CREATE TABLE等;
• 管理语句: ANALYZE TABLE, OPTIMIZE TABLE等;

事务实现

隔离性Isolation

通过锁来实现

持久性Durability

• 重做日志分为: redo log buffer, redo log file, 重做日志需要先写到缓冲中, 在fsync到磁盘;
• Force log at commit
  当事务提交时, 必须将事务的所有日志写到重做日志, 进行持久化;
• undo log
  帮助事务回滚, 以及MVCC功能;

innnodb_flush_log_at_trx_commit

有三个值可选, 0, 1, 2. 默认为1;

• 0: commit提交时. 不执行fsync, 有master线程每个1s执行;
• 1: 默认, 事务提交时, 执行fsync;
• 2: commit时, 写入文件系统缓冲, 不执行fsync;

LSN(Log Sequence Number) 日志序列号

show engine innodb status里有如下值:

Log
Log Sequence Number 当前redo log buffer的LSN
Log Flushed up to 刷新到重做日志文件的LSN
Last Checkpoint at 磁盘的LSN

由于默认autocommit=1, 所以对于大量的insert操作, 应该放入一个事务中, 同意commit, 减少fsync次数;

事务类型

扁平事务Flat Transaction

• BEGIN WORK开始, COMMIT WORK 或 RollBack WORK结束;
• 保存点: SAVEPOINT, 可以回滚到的指定的保存点;
• ROLLBACK WORK: 2, 回滚到保存点2, 此时事务仍然活跃, 由Rollback work来结束;

链事务Chained Transaction

SAVEPOINT是易失的, 没有完成COMMIT, 当系统崩溃时, 数据会消失. 事务链, 就是阶段性的执行COMMIT操作, 其事务提交与开始一个新的事务是原子操作, 同时能够保留上一个事务的环境.

嵌套事务Nested Transaction

嵌套事务中, 事务的回滚操作会将子事务一同回滚. 子事务可以提交也可以回滚, 但提交操作只会在父事务提交时才会真正执行;

undo log

作用:

• ROLLBACK回滚
  undo log并不是将数据库物理的恢复, 而是针对某个事务执行其逻辑恢复. 因为并发, 执行回滚后, 数据库的物理数据与事务开始时会不一样;

• MVCC
  如果某一事务读取某一行时发现, 已被其它事务占用, 可以通过undo读取到其占用前的数据, 以实现MVCC;
  同理事务提交后, 并不能直接删除undo log, 因为可能还有其它的事务需要, 所以会将undo log放到一个链表中, 有purge线程执行最终的删除;
  show engine innodb status命令下的History List Length显示的是undo log 的数量;

insert undo log

这种类型的undo log对其它事务是隔离的, 因此可以直接删除, 不用等purge来删除;

update undo log

update, delete操作参数的undo log, 由于需要提供MVCC机制, 所以需要等purge操作;
delete操作也不会立刻删除, 而是放入undo log链表, 等purge来完成最终的删除;

purge线程

• 完成update, delete的最终操作, 只有等该行不被其它事务引用时才会执行;
• 当引擎压力大时, History List Length可能越来越长, 可以通过innodb_max_purge_lag来控制长度;
• innodb_max_purge_lag_delay, 控制每次的清理时长;