mysql 行级物理模式优化与评估

addison_lin

给定环境中，查询请求扫描多少行是合适的，每页应该存放多少行？每表存放多少字段？该有多大的字段？行该有多大？怎么评估响应时间？

原始数据：（下面三张图原为同一个记录）：

考虑在文件系统块大小为16K时，且innodb页为16K

rw_per = (inn_num_r+inn_num_w) / qps == 每查询需读取写入page”ios”
rw_bytes_per = ( inn_k_r + inn_k_w ) / qps == 每查询需要的bytes
rw_q_bytes = rw_bytes_per / rw_per == 每查询引用的数据量bytes
qrow = ( inn_row_r + inn_row_w ) / qps == 每qps 物理读取行数
p_rwk = rw_q_bytes / qrow == 每行长度如果rw_per大于1

，则平均每查询都会产生物理io ，而不是buffer hit , rw_per 越高则ibf_hit越低，io越高

行长度 :

行长度，部分决定了在io层，缓存层，result 层的开销(不会涉及到net层)在理想情况下

，对于一个表结构，当查询含大量与result无关数据列和行，ios明显增大，缓存命中降低

，特别是某些情况下，将使缓存将失效，增大大io操作，当io成为瓶颈，将产生大量上下文

，在profiling 中看到的上下文会变得很大，从而影响响应速度
根据出度公式 O( log( rowcount , page / line_size as pavg ) ) ，

在F[n]中，当rowcount 增加，对数值的增长较为平缓，当pavg增长，将导致log成直线增长

，驱动参数为pavg ，因为存在F[n]， O(log(F[n])) (= O(pavg) 由于innodb至多行大小为8190

，当溢出后，产生Uncompress blob页(在innodb plugin中得到了改进)，将导致更多的io

, 当 p_rwk > 8090 时，表结构存在设计问题(事实上，当度出越小，B-数过深，开销越大

，性能越低，因为它直接影响下钻及顺序访问操作 ) ， 应考虑采用精确的数据类型

，合理的纵向切割方法，降低单行数据所带来的O(log(F[n]))，用以降低由于下钻所需要的开销

行深度 ：
rw_rto_r == 读写比例中读次数
rw_rto_w == 读写比例中写次数
是否提取过多行 ？
假设用户体验需求为100ms
用户的体验，依赖于响应时间，一般的讲，要想有良好的用户体验，每查询过程响应时间是最重要的指标
影响响应时间的因素很多，如ibf 操作 ，磁盘搜索 ， 结果集 ， 网络通信 ， 服务器及数据库配置等等 。
在 网络通信 ， 服务器及数据库配置等因素排除(因为这些通常恒定)的情况下，ibf 查询速度很快

，开销主要集中在io中 ，满足100ms ，要求io开销要求小于 70 ， 响应时间主要由io得出

，在文件系统中，块的大小与行长度共同决定读取的io效率，mysql页为16K，也就是说当文件系统为16K时

，io性能依赖于磁盘参数 ，通常15000/rpms的磁盘理论能提供的响应时间为 4ms

，raid10的响应时间为 rw_rto_r * ( 4 / 2 ) + rw_rto_w * (4 * 2 ) * 0.7(假设写入缓冲merge为 0.3)

，在这里假设读写比例为 rw =100 , rw_ratio = 200 , r = 66 , w = 34

，可以计算出ios响应时间为：66*2 + 34 * 8 * 0.7 = 322.4 ms 能相信，这样的响应速度是不可忍受的

根据公式得出：

r = inn_num_r / qps == 每查询读取次数
w = inn_num_w / qps == 每查询写入次数
row_r = inn_row_r / qps == 每查询读取行数
row_w = inn_row_w / qps == 每查询写入行数
rk = inn_k_r / qps == 每查询读取字节数
wk = inn_k_w / qps == 每查询写入字节数
通过计算出读写次数，得出每个读写需要读取所需的行数，if p_rwk 时，得出每个r|w影响行数

，由于每个r|w行数的开销，通过降低扫描行数,能降低ios，有效提高系统吞吐量，将读取量控在：
1、算出出度：
rd_nu = rk / row_r == 每读取行占用字节数
wd_nu = wk / row_w
16384 / ( wk / row_w )
2、算出读写比例
r_rto = r / ( r + w )
w_rto = w / ( r + w )
3、算出最低容忍响应时间
r_rto_ru = 70 * r_rto
w_rto_ru = 70 * w_rto
4、获取rw所需要的响应时间所对应的行数
16384 * ( r_rto_ru / ( 1000 / ( 15000 / 60 ) / 2 ) ) / rd_nu == 获取读操作最多能操作多少块是能忍受的
16384 * ( w_rto_ru / ( 1000 / ( 15000 / 60 ) / 2 ) ) / wd_nu == 获取写操作最多能操作多少块是能忍受的
通过公式反推，响应缓慢时，应当检查是否扫描了过多的行，在可容忍的时间范围内

，可以通过配置行数及行长度，得到需要的响应时间

总结：设计数据库时，根据数据特性，进行有效的数据类型，字段数，行，表 ，及物理读写评估

，根据有行长度进行物理层优化，能有效的降低开销 ，提升响应，也间接获得了成本优势。

每天一点点

mylink199

好深奥,基本看不懂

凑字.exe