mysql---innodb 系列

MySQL 体系结构

1. Connectors指的是不同语言中与SQL的交互。
2. Management Serveices & Utilities： 系统管理和控制工具。
3. Connection Pool： 连接池。管理缓冲用户连接，线程处理等需要缓存的需求。
4. SQL Interface： SQL接口。接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface。
5. Parser：解析器。SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。主要功能：
将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的。

6. Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。用一个例子就可以理解： select uid,name from user where gender = 1;
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤。这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件联接起来生成最终查询结果。
7. Cache和Buffer： 查询缓存。
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。
8. Engine ：存储引擎。
存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。
Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）。现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的MyISAM,InnoDB,BDB。
默认下MySql是使用MyISAM引擎，它查询速度快，有较好的索引优化和数据压缩技术。但是它不支持事务。InnoDB支持事务，并且提供行级的锁定，应用也相当广泛。 
Mysql也支持自己定制存储引擎，甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。

 

InnoDB Architecture

 

 

 

线程

Master Thread

核心后台线程，主要负责将缓冲池中的数据异步刷新到磁盘上，保证数据的一致性，包括脏页的刷新，合并插入缓存（Insert BUffer），undo页回收等。

IO Thread

InnoDB存储引擎中大量使用AIO（Async IO）来处理写请求，可以极大的提高数据库性能。而IO Thread线程主要负责这些IO请求的回调（call back）处理。

IO Thread主要可以分为write，read以及insert buffer，在InnoDB1.0.x后可以使用innodb_read_io_threads和innodb_write_io_threads进行设置，IO Thread 0是insert buffer thread，IO Thread 1为log thread，根据参数 innodb_read_io_threads和innodb_write_io_threads 设置读写线程，并且读线程的ID总是小于写线程的ID。

（4读4写 一个insert buffer,一个log）

FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: doing file i/o (write thread) ev set

 

Purge Thread

事务被提交以后，其所使用的undolog可能就不再被需要了，因此需要使用Purge Thread 进行回收已经使用的undo页，在InnoDB1.1版本前，purge操作只能在InnoDB存储引擎中的Master Thread中进行，在InnoDB1.1以后purge操作可以在单独的线程中进行，来减轻master thread的工作压力，从而提高CPU的使用率和存储引擎的性能，用户可以在数据库的配置文件中添加如下命令来启动独立的purge thread：

 

Page Cleaner Thread

这个线程是在InnoDB1.2后产生的，其作用是将脏页刷新操作放入到单独的线程中来完成，同样这个动作之前是通过master thread实现的，这样做也是为了减轻master thread的工作压力和用户查询线程的阻塞，进一步提高InnoDB的性能。

1个lock锁监控线程；
1个error错误监控线程。

 

 

 

特性：插入缓冲

 

(1) Insert Buffer定义

InnoDB存储引擎开创性地设计了Insert Buffer，对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer对象中，好似欺骗。数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge（合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚集索引插入的性能。

(2) Insert Buffer使用条件

• 索引是辅助索引（secondary index）
• 索引不是唯一（unique）的

当满足以上两个条件时，InnoDB存储引擎会使用Insert Buffer，这样就能提高插入操作的性能了。不过考虑这样一种情况：应用程序进行大量的插入操作，这些都涉及了不唯一的非聚集索引，也就是使用了Insert Buffer。若此时MySQL数据库发生了宕机，这时势必有大量的Insert Buffer并没有合并到实际的非聚集索引中去。因此这时恢复可能需要很长的时间，在极端情况下甚至需要几个小时。

辅助索引不能是唯一的，因为在插入缓冲时，数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生，从而导致Insert Buffer失去了意义。

 

 

INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 9048, seg size 9050, 566898963 merges
merged operations:
insert 611723220, delete mark 227721429, delete 7001665
discarded operations:
insert 0, delete mark 0, delete 0
AHI PARTITION 1: Hash table size 12750011, node heap has 133 buffer(s)
AHI PARTITION 2: Hash table size 12750011, node heap has 846 buffer(s)
AHI PARTITION 3: Hash table size 12750011, node heap has 1642 buffer(s)
AHI PARTITION 4: Hash table size 12750011, node heap has 109 buffer(s)
AHI PARTITION 5: Hash table size 12750011, node heap has 253 buffer(s)
AHI PARTITION 6: Hash table size 12750011, node heap has 297 buffer(s)
AHI PARTITION 7: Hash table size 12750011, node heap has 12915 buffer(s)
AHI PARTITION 8: Hash table size 12750011, node heap has 45 buffer(s)
75.36 hash searches/s, 130.62 non-hash searches/s

 自适应hash_index

 

94.28 hash searches/s, 154.97 non-hash searches/s