小知识，大挑战！本文正在参与“程序员必备小知识”创作活动

前言

​ 接续上文继续介绍：《高性能Mysql》学习笔记（一）。

mysql 时间线

基准测试

为什么需要基准测试

基准测试策略

• 基于mysql 单独测试 - 单组件式

• 对整个系统整体测试 - 集成式

  使用整个系统测试原因主要如下

​ 如果不需要关注整体应用，只关注msyql 性能

测试指标

• 吞吐量
• 响应时间或者延迟
• 并发性
• 可扩展性

应当避免以下情况再进行基准测试

获取系统性能和状态

获取基准测试结果

运行基准测试并分析结果

使用 shell , php, perl 都可以实现

结果分析图表

gnuplot > plot "qps-per-5-seconds" using 5 w lines title "qps"

基准测试工具

• apach AB
• http_load
• jmeter

单组件式测试工具

• mysqlslap

  • 包含在mysql5.1 的发行包当中，会自动生成查询schema的select 语句

• mysql benchmark suite (sql-bench)

  • 优点：单线程，测试服务器执行查询的速度。
  • 有大量预定义的测试
  • 缺点：单用户模式，测试数据集很小而且无法指定数据
  • 无法测试多cpu能力

• super mack

  • 用于 mysql 和 postgresql 基准测试工具

• database test suite

  • 类似工业标准的测试工具
  • dbt2 免费的toc-c oltp 测试工具

• percona's tpcc-mysql tool

  • mysql 高性能并发作者自己制作

• sysbench

  • 多线程系统压测工具
  • 支持lua 语言

msyql 的 benchmark() 函数

Mysql 内置，可以测试某些特定操作的执行速度

方便的测试某些特定操作性能，比如md5() 比 sha1() 函数快

基准测试案例：

​ 重点熟悉 sysbench 测试 ，因为和mysql 自身的设计最为贴合

服务器性能剖析

性能优化简介

​ 一句话概括：性能即响应时间

原则

1. 一定的工作负载之下尽可能的降低响应时间
2. 无法测量就无法有效优化

忌讳

1. 错误的时间启动和停止测量
2. 测量的是聚合后的信息，而不是目标活动本身

完成一项任务可以分成两部分

1. 执行时间：优化通过测量定位不同的子任务花费的时间，优化一些子任务，降低子任务的执行效率或者提升
2. 等待时间

如何判断测量是正确的？

测量到底有多么不准确，记住一点，使用的测量数据而不是实际数据，测量数据也有多种表现。很容易推导出错误的结论

性能剖析进行优化

​ 任务结束时间减去启动时间得到响应时间

性能剖析两种类型

1. 基于时间分析
   1. 某时候执行时间就是在等待
   2. 比如i/o或者查询等待时间过久
2. 基于等待分析

理解性能剖析

​ 将最重要的任务展示在前面，但是没有显示的信息也很重要

值得优化的查询：

1. 一些只占总响应时间比重很小的查询不值得优化
2. 如果优化成本大于收益，要停止优化！

异常情况：

​ 某些任务没有性能剖析输出也要优化，比如某些任务执行次数很少，但每次都很慢

未知的未知

​ 好的剖析工具尽可能显示“丢失的时间”

​ 丢失的时间： 任务的总时间和实际测量时间时间的差

被掩藏的细节：

​ 平均值不能完全相信和作为根据

应用程序的性能剖析：

​ 对于任何需要消耗时间的任务都可以进行性能分析

实用软件： New Relic

捕获查询到日志文件当中

• mysql 5.0 之前， 慢查询日志的响应时间是秒
• mysql 5.1 之后，慢查询被加强，可以做到微秒级别的查询

​ 慢查询日志是进度最高测量查询的日志，开销几乎可以忽略不计，但是会消耗大量磁盘空间，

percona server 慢查询日志

• 通过--processlist 选项不断查看 show full processlist 的输出
• 通过抓取 tcp 网络包，根据mysql 客户端 /服务端 通信协议进行剖析

建议： 在服务器上使用慢查询日志捕获所有的查询

​ 应该首先生成一个剖析报告，在进行慢查询

剖析报告

剖析单条查询

1. 使用 show profile

mysql 5.1 之后版本引入，默认是禁用的，但是可以通过服务器变量在连接中动态更改mysql> set profiling = 1

开启后会测量查询执行相关操作的状态

可能被 performance scheema 取代

该工具会讲剖析信息做成一张临时表

示例

1. 执行下列语句

2. 返回997行数据

3. 继续看待下面输出

4. 如果不使用上面方法，则使用下列方法

2. 使用 show status

​ 该命令返回了一些计数器，既有 服务器界别全局计数器，也有基于某个连接的会话级别计数器，show global status 可以查询服务器启动时候开计算查询次数的统计

全局计数器也会出现在show status

猜测操作代价或者消耗时间较多的，可以使用句柄计数器， 临时文件和表计算器

示例

3. 使用慢查询日志（重点）

通过 pt_query_digest 发现“坏查询”

# query 1 :0 ops, 0x concurrency, ID oxeexxxs at byte 3214 ___

使用下面方法查看详情

tail -c +3214 /path/to/query.log | head -n100

4. 使用performance Schema

​ mysql 5.5 之后新增还不支持查询级别的剖析信息

下面是显示系统等待主要原因的查询：

诊断间歇性问题

解决间歇性错误案例

单条查询问题还是服务器问题？

三种办法：

• show global status
  • 通过“尖刺”或者“凹陷”来发现问题，这种方法较为简单明了
  • 示例

• 使用 show processlist

  • 不停的捕获show processlist的输出，查看线程是否有大量处于不正常状态的特征
  • 尾部加入 \G 可以垂直的显示结果
  • 示例
    • 

• 使用慢查询日志

  • 开启方式： 全局级别设置 Long_query_time 为 0，所有连接都采用新设置
  • 替代方案： pt_query_digest 工具 +

• 理解发现的问题
  • 建议先用前两种方法， show status 和 show processlist

诊断器

pt-map 工具使用

​

诊断案例

1. 问题是什么： 清晰描述出错误，
2. 为了解决问题做过什么操作

其他剖析工具

1. 使用 user_statistics 表

   

2. 使用 strace

总结

Schema 与数据类型优化

选择优化的数据类型

• 更小通常更好
  • 尽量使用正确存储数据的最小数据类型
• 简单就好
  • 使用内建date 类型代替日期字符串
  • 使用整型存储Ip地址
• 尽量避免Null
• 通常将列指定为not null(尽量)

整数类型和实数类型

​ 使用小数点和不使用的区别

字符串类型

• varchar 和 char 的区别
• blob 和 text的区别
• blob 和 text 的使用技巧
• 使用枚举代替字符串类型

日期和时间类型

timestamp 使用的存储空间只需要 datatime 的一半

但是时间存储范围却要小

• datetime : 使用 8个字节；默认以一种无歧义的格式排序显示； 范围 1001 - 9999
• timestamp ： 使用4个字节； 日期时间戳， 1970年开始，使用from unix ()函数对于日期进行转换；默认为Not null;并且无值的时候自动根据当前时间插入日期

位数据类型

bit:

• 最大存储64为，使用（位数）指定多少位数据存储
• 存储方式根据引擎制定
• mysql 当做字符串处理
• 尽量避免使用
• 替代方法： char(0) 保存 null 或者 长度为零 的字符串

set:

• 需要多个true/false时候可以使用

• 在整数列上按位操作

  • 使用一个8位的tinyint 进行包装替代set

• 应用：保存访问权限控制表

  • 

• 使用整数可以参考下面例子

  

  选择标识符

  • 使用整型最佳
  • 字符串类型：
    • 避免使用md5(),sha1(), uuid()产生的字符串会导致insert 以及 slelect 变慢
    • 使用uuid()应该移除 '-' 值，更好做法使用 unhext()函数转换uuid 为16位的数字，并且存储在binary(16)当中

  特殊数据类型

  对于ip地址，最好使用无符号整数存储，而不是varchar(15)

  inet_aton() 和 inet_ntoa() 函数可以转换

mysql schema 设计陷阱

1. 过多的列
2. 过多的关联

单个查询最好在12个表以内关联！！！！，但是实际上3个表以上的连表查询就已经不是十分建议了。

3. 全能的枚举

缓存表和汇总表

1. 有时候可以使用在同一张表当中保存冗余数据实现
2. 要每一个小时对于网站的数据生成汇总表可以使用一下方式
   1. 每个小时生成一张汇总表
   2. 把前23个完整小时统计表的计数全部加起来
3. 使用不严格的计数或者小范围的查询填满间隙的严格计数都要比计算所有行效率要高

​ 可以使用影子表来作为替代方案

物化视图

Mysql 不支持原生的 物化视图，但是使用开源工具 flexviews 可以实现

加快alter table 速度

技巧：

1. 现在一台不提供服务的机器上执行alter table 操作，然后和提供服务的主库进行切换
2. 影子拷贝：用要求的表结构创建一张和原表无关的新表，然后使用删表和重命名操作交换两张表，

注意：

1. 所有的Modify column 操作都会造成表创建

解决：

mysql > alter table sakila.fil
	  > alter column rental_duration set default 5;

该操作只会修改 .frm 文件

只修改.frm 文件

下面的操作可能不会造成重新建表：

1. 移除 一个列的 auto_increment 属性
2. 增加、移除、或者更改一个enum 和 set 常量， 如果移除的是已经有的行数据用到其值的常量，查询会返回空字符串

技巧是为自己想要的表结构创建一个新的.frm 文件，然后用它替换掉已经存在的那张表的 .frm 文件

实例

快速创建myiSAM 索引

​ 常用技巧： 禁用索引，载入数据， 重新启用索引

​ 此办法对于唯一索引无效

Innodb 中的类似操作

1. 先删除所有非唯一索引
2. 增加新列
3. 重新创建删掉的索引

操作步骤：

• 用需要的表结构创建一张表，但是不包括索引
• 载入数据表中以构建 .myd 文件
• 按照需要的结构创建 另一张空表，这次要包含索引。这会创建需要的 .frm 和.myi文件
• 获取读锁并刷新表
• 重命名第二种表 .frm 和 .myi 文件。让 Mysql 认为是 第一张表
• 释放读锁
• 使用 repair table 来重新创建表的索引。 会根据排序来构建所有的索引，包括唯一索引

总结

写在最后

​ 第二篇读书笔记的内容对于mysql进行进一步的扩展，基准测试以及schema的描述相关内容，后续介绍了关于服务器性能剖析的相关内容。