MYSQL IO 内部统计

MYSQL提供的两种存储引擎MYISAM和INNODB，下面分别根据两种计量io的方法说明

 

MYSQL IO

 

“此处排除由于排序等操作产生的IO”

 

Myisam ：

 

访问流程：

用户发起请求，mysql解析并构造解析树和优化树，随即调用hanlder接口，将请求分发给MyISAM，Myisam根据执行计划，在key buffer中探索key list ，如果keylist不在或不足，则将产生物理key read ， 并获取到key list ，写入到buffer中，然后再去数据文件中提取数据，由于MyISAM使用堆表，对于每行数据都可以认为是随机访问，但由于在key list中 MyISAM对其进行了排序优化，所以访问数据时，会产生很多连续的小片大量随机io访问，当用户请求写入数据时，将首先写入数据文件，随即更新key文件，最后将更新后的key写入buffer中，完成整个数据访问过程

 

1、使用key buffer时，判断key是否在key buffer中，则直接获取到key列表，key列表根据pos值进行排序后分段，然后定位到data中的pos值，拿到需要访问的数据，返回给客户端，因为轮询key列表段，所以会有多次数据访问，每轮询一个key list将导致Handler_read_key增大，产生物理io

2、全部或部分key不在buffer中，会从物理索引中提取一次key列表，表现为Key_reads增大，然后重复1的操作，在数据库status中值为：Key_reads , 它将产生至少一次key读取io及至少一次物理数据io

3、写入包含key的数据时，将先在数据文件中写入，然后写入key，在数据库status中值为：Key_writes 加上至少在数据文件中产生一个io

4、访问不到key时，将产生一个全表扫描，它将访问被筛选的每一个字段，并将其中的select值捕获，传递给MYSQL Hanlder层， Handler_read_rnd没有使用到索引，导致需要全表扫描的值

参数列表：

Key_reads 从硬盘读取键的数据块的次数，当key list不能在buffer中得到全部或部分时，将导致key io，他需要读取key list 然后装载到buffer中，并随即定位数据文件，至少产生两次物理io

Key_writes 向硬盘写入将键的数据块的物理写操作的次数，每写入一次key ， 必定先造成一次数据文件更新，并随即产生key更新

Key_read_requests 从key buffer中读取key list的次数，再在数据文件中移动指针得到数据，它至少产生一次物理io 请求（MyISAM为索引顺序访问，所以在这里也做了访问优化）

MyISAM总结：

MyISAM IO计量目前为止，不能捕获到精准的数值，但可以拿到模糊的值，因此下面的公式也代表了用户的io开销

 

计算公式

 

Key_reads  * 2 +  Key_writes * 2 + Key_read_requests

 

INNODB

 

访问流程：

用户发起请求，mysql解析并构造解析树和优化树，随即调用hanlder接口，将请求分发给Innodb，因为innodb由索引组织表构成，所以索引及数据都被倒挂在树下，所以对待索引和数据的方式是一致的，innodb根据优化器指示，在buffer中探寻index，并找出主键id，根据主键id在buffer中探寻数据，如果数据不存在于buffer，则需要到数据文件中将所在page装载进buffer，但对于blob，text，特大的varchar等对象，将会产生一个连接，不会直接装载，然后在内存中根据二分查找法查找数据，并将结果集返回给hanlder

1、  当innodb query一个数据时，将会判断数据是否在内存中，在很多情况下，数据的一部分位于磁盘上，但status值Innodb_data_reads将增加

2、  当用户进行数据更新或数据DDL时，将会对Innodb_data_writes，Innodb_dblwr_writes，Innodb_log_writes产生影响

参数列表:

Innodb_data_reads数据读请求量数量，包含 dic ，data，undo

Innodb_data_writes数据写请求量数量，包含 dic ，data，undo

Innodb_dblwr_writes 双写请求数

Innodb_log_writes 日志redo写请求数

INNODB总结：

Innodb写入数据时，将尽可能产生为顺序写，这点和MyISAM不同，由于数据被merge写入，会产生顺序IO，顺序IO被写入redo后，又经过merge刷写物理数据文件，所以提升了写入能力，但也带来了额外写和额外的刷写操作。由于使用了Buffer，可以提升读性能。

Innodb计算IO公式

 

Innodb_data_reads + Innodb_data_writes + Innodb_dblwr_writes + Innodb_log_writes

 

总结：

Instance io统计方法一共分两种:

Key_reads  * 2 +  Key_writes * 2 + Key_read_requests + Innodb_data_reads + Innodb_data_writes + Innodb_dblwr_writes + Innodb_log_writes

说明：以上为MySQL的内部IO统计，不涉及到具体物理IO统计，但能从另一面反映出数据库IO使用量，通过结合物理IO进行对比，可以对数据库进行进一步优化

转自：http://www.taobaodba.com/html/1203_1203.html