innodb第二版阅读笔记

第一章体系机构和存储引擎

[root@pg1 sbin]# mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

按照这个顺序读取配置文件，以读取到的最后一个配置文件中的参数为准

• mysql是基于表的存储引擎，而不是数据库，这就可以针对表选择存储引擎
• innodb
  • 将每个表单独放到一个独立的idb文件中
  • 使用MVCC（多版本并发控制）实现并发
  • 默认隔离级别为repeatable
  • innodb采用cluster的方式，每张表的存储都是按照主键的顺序存放，如果没有指定主键，会为每一行生成 一个6字节的rowid作为主键
• myisam
  • 不支持事务、表锁
  • 支持全文索引，主要面向一些OLAP

第二章innodb存储引擎

一、体系架构

1. 后台线程

   1. master thread

      将缓冲池数据刷盘，undo回收

   2. IO thread

      分为read、write IO，负责这些IO请求的回调（call back）

      参数 innodb_read_io_threads、innodb_write_io_threads设置读写线程，读线程id总是小于写的

   3. purge thread

      回收事务提交后，使用的不需要的undolog

   4. page cleaner thread

      将刷脏页的操作放到单独的线程中完成，减轻mater thread的压力

2. 内存

   2.1. 缓冲池

   通过参数innodb_buffer_pool_size设置

    1、innodb_buffer_pool:
      data page
      index page
      insert buffer
      lock info
    2、redolog buffer
    3、innodb_additional_mem_pool_size
   

   可设置innodb_buffer_pool_instances大于1，得到多个缓冲池实例

   2.2. redolog buffer

​ 通过参数innodb_log_buffer_size控制，默认8mb

二、关于checkpoint

思考：什么时候触发ckp?

1.shutdown

2.LRU列表中若没有100个可用空闲页，就要将尾端的页移除，如果其中有脏页，就要ckp

3.脏页太多，有参数innodb_max_dirty_pages_pct默认为75，当缓冲池中脏页超过75%，就强制ckp

三、innodb的特点

3.1每秒/每10秒做

每秒做：

1、即使某个事务还没有提交，每秒依然会刷buffer 中的redo，这就保证大事务的commit时间很短

2、insert buffer合并插入缓冲不是每秒发生，如果前一秒IO小于5次，引擎认为此时IO压力很小，才做合并插入缓冲操作

3、判断：如果当前缓冲池中的脏页比例（buf_get_modified_ratio_pct）>innodb_max_dirty_pages_pct（默认90%），会将100个脏页写入磁盘中

每10秒做：

1、删除无用undo页

2、刷新100个或者10个脏页到磁盘

3、1.2.X新特性：原先最大只会刷新100个脏页，合并20个缓冲，如果写入密集，mater thread会很慢，如果使用ssd下面参数可以调大一点

​ 新增innodb_io_capaticy百分比控制

​ 合并插入缓冲时：数量为innodb_io_capaticy 的5%

​ 刷新脏页时：数量为innodb_io_capaticy

3.2关键特性

1、插入缓冲

• insert buffer

​ 要求索引是secondary index，索引不是唯一的

​ show engine innodb status看Ibuf插入缓冲信息；

​ 写密集的情况下，插入缓冲会占用过多innodb_buffer_pool,可以Ibuf_pool_size_per_max_size改为3，最大只能使用1/3的缓冲池内存

• change buffer

  innodb_change_buffer_max_size控制（默认25，即最多使用1/4的缓冲池内存空间）

  innodb_change_buffering 用来开启各种buffer

2、double write 两次写

​ 在写入失效发生前，先通过页的副本还原该页，再进行重做

​ 内存-》共享表空间-》数据文件

3、自适应哈希索引

4、异步IO

​ 全部IO请求发送完毕后，等待所有IO操作的完成

​ IO merge：将多个IO合并，以提高iops

5、刷新临近页

​ innodb_flush_neighbors SSD可以关闭，避免将不怎么脏的页写入

​ 传统机械硬盘开启，可以提高脏页刷新效率

innodb_fast_shutdown:

• 0 关闭时要完成所有的full purge 和merge insert buffer
• 1 默认，还是要将脏页刷回磁盘
• 2 不必将脏页都刷盘，但是日志都要写入日志文件，下次启动时要recovery

第三章文件

一、参数文件

二、日志文件

​ 2.1error log

mysql> show variables like '%log_error%';
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| binlog_error_action | ABORT_SERVER        |
| log_error           | /var/log/mysqld.log |
| log_error_verbosity | 3                   |
+---------------------+---------------------+
3 rows in set (0.00 sec)

​ 2.2binlog

​ 2.3slow query log

时间阈值：
long_query_time 默认10

没有使用索引的也记录到slow log：
mysql> show variables like '%log_quer%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

使用表查看mysql.slow_log,前提是修改log_output参数为table：
mysql> show variables like '%log_out%'
    -> ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

观察slow log 的逻辑读和物理读比例，优化SQL

也可设置slow_query_type

• 0 不记录
• 1 根据时间记录
• 2 根据逻辑IO次数记录
• 3 根据时间和逻辑IO记录到slow log

​ 2.4log

第四章表

表空间--》段--》区--》页（块）

段：数据段、索引段、回滚段

区：1MB，一次从磁盘申请4-5个区，默认页16K，即一个区中一共有64个连续的页

innodb的文件格式：

• antelope：compact、redundant

• barracuda：compressed、dynamic

  • 对于BLOB采用的是完全行溢出
  • compressed 会以zlib算法压缩

4.6约束

information_schema.TABLE_CONSTRAINTS

通过设置参数sql_mode来审核输入的参数

mysql不支持check约束，可以通过ENUM类型+严格的sql_mode来约束

对于外键，innodb会默认在对应列加上一个索引。oracle则需要手动去做。

但是mysql的外键是即时检查的，导入速度会有影响，可以在导入过程中忽视外键的检查：

​ set foreign_key_checks=0;

​ load data...

​ set foreign_key_checks=1;

不支持物化视图，可以通过触发器实现

第六章 锁

innodb的锁类型：共享锁和排他锁（行锁），更细粒度的意向锁（表级别）

使用innodb_trx,innodb_locks,innodb_lock_waits监控当前事务并分析可能存在的锁问题

锁的算法：

-	record lock 行锁
-	GAP lock 
-	next_key lock / previous-key lock

例子：

表t(a,b) 有值：

insert into t select 1,1;

insert into t select 3,1;

insert into t select 5,3;

insert into t select 7,6;

insert into t select 10,8;

begin;

select *  from t where a = 5 for update;

a 是主键，对a持排他锁，锁定的仅仅是5这个值

select * from t where b = 3 for update;

b是辅助索引，此时用的是next_key lock，锁定b的一个范围（1,3），同时还有gap 锁将锁定下一个范围（3,6）

若此时执行：

​

	select * from t where a=5 lock in share mode 

​		insert into t select 4,2;

​		insert into t select 6,5;

第一句不能执行，因为a=5这列已经有X锁了

第二句不能执行，因为b 的范围（1,3）已经被锁，2不能插入

第三句也不能执行，因为gap锁，b的范围（3,6）已经被锁，5不能插入

GAP lock的作用：阻止多个事务将记录插入到同一个范围内，出现幻读（前后读到的数据不一致）

但是如果使用read commited的事务隔离级别，就不会有这两种锁，只有行锁了。

锁问题：

​ 脏读：读到了未提交的数据（一般不会发生）

​ 不可重复读：读到了另外一个事务修改的数据（RR避免了）

​ 丢失更新：情景为两个终端更新数据，应该让事务操作串行化，对读取的第一步就加上 for update排他锁

第七章事务

事务的ACID特性：

原子性：redo log

一致性：undo log

隔离性：read commit 不满足隔离性

持久性：redo log

• redo log 顺序写，binlog是事务提交后才写入
• log block 每块512字节（redo log 头12字节，尾8字节，所以每个重做日志块实际可以存储492字节）

redo log buffer刷盘的策略：

1、事务提交

2、log buffer 中有一半内存空间被使用

3、log checkpoit

LSN : 日志序列号

1、redo log 写入的总量

​ 比如当前LSN为1000，有个事务T1写入100个字节的redo，LSN变为1100

2、ckp的位置

​

3、页的版本

​ 页的头部有个FIL_PAGE_LSN，记录该页的LSN，表示最后刷新时LSN的大小

`show  engine innodb status\G; 查看LSN的情况`

undo segment位于共享表空间中

rollback实际是对操作的回放，如insert-->delete，表空间大小并不会收缩

另一个作用是MVCC，读取之前的行版本信息

undo也会生成redo，因为undo也需要持久性的保护

insert undo log 在事务提交后就可以直接删除

update undo log 是delete和update的log，提交后放入连表等待purge线程删除

数据字典：innodb_trx_rollback_segment、innodb_trx_undo

purge: 实际删除的操作

innodb_purge_batch_size用来设置每次purge的undo数量

innodb_max_purge_lag 用来控制history list的长度

唤醒的条件是有事务有提交或者回滚

group commit

事务提交时有两个阶段的动作：

1、修改内存中数据，日志写进redo log buffer

2、调用fsync刷盘

为了提高刷盘效率，第二步将多个事务一起commit

但是如果开启了binlog，两个日志的一致性只能通过两阶段事务来保证：

​ 1、prepare redolog

​ 2、binlog写进内存

​ 3、内存中binlog刷盘（BLGC）

​ 4、redolog group commit

推荐使用binlog格式为ROW，是因为STATEMENT记录的是SQL语句，主上为先增后删，SATEMENT记录的是先删后增，会导致主从不一致。

存储过程使用注意：

不要在块里显示使用commit，innodb是自动提交的，两次commit反而提交了两次redolog

建议：

start transaction；

where xxx do

insert into xx select ...;

set s= s+1;

end while;

commit;

但是oracle中考虑到没有足够undo导致snap too old，会建议显式commit，即时释放事务