诊断间歇性问题
解决间歇性错误案例
单条查询问题还是服务器问题？
三种办法：
- show global status
- 通过“尖刺”或者“凹陷”来发现问题，这种方法较为简单明了
- 示例
- 使用 show processlist
- 不停的捕获show processlist的输出，查看线程是否有大量处于不正常状态的特征
- 尾部加入 \G 可以垂直的显示结果
- 示例
- 使用慢查询日志
- 开启方式： 全局级别设置 Long_query_time 为 0，所有连接都采用新设置
- 替代方案： pt_query_digest 工具 +
- 理解发现的问题
- 建议先用前两种方法， show status 和 show processlist
诊断器
pt-map 工具使用
诊断案例
- 问题是什么： 清晰描述出错误，
- 为了解决问题做过什么操作
其他剖析工具
- 使用 user_statistics 表
- 使用 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 设计陷阱
- 过多的列
- 过多的关联
单个查询最好在12个表以内关联！！！！，但是实际上3个表以上的连表查询就已经不是十分建议了。
- 全能的枚举
缓存表和汇总表
- 有时候可以使用在同一张表当中保存冗余数据实现
- 要每一个小时对于网站的数据生成汇总表可以使用一下方式
- 每个小时生成一张汇总表
- 把前23个完整小时统计表的计数全部加起来
- 使用不严格的计数或者小范围的查询填满间隙的严格计数都要比计算所有行效率要高
可以使用影子表来作为替代方案
物化视图
Mysql 不支持原生的 物化视图，但是使用开源工具 flexviews 可以实现
加快alter table 速度
技巧：
1. 现在一台不提供服务的机器上执行alter table 操作，然后和提供服务的主库进行切换 2. 影子拷贝：用要求的表结构创建一张和原表无关的新表，然后使用删表和重命名操作交换两张表，
注意：
- 所有的Modify column 操作都会造成表创建
解决：
mysql > alter table sakila.fil > alter column rental_duration set default 5;
该操作只会修改 .frm 文件
只修改.frm 文件
下面的操作可能不会造成重新建表：
- 移除 一个列的 auto_increment 属性
- 增加、移除、或者更改一个enum 和 set 常量， 如果移除的是已经有的行数据用到其值的常量，查询会返回空字符串
技巧是为自己想要的表结构创建一个新的.frm 文件，然后用它替换掉已经存在的那张表的 .frm 文件
实例
快速创建myiSAM 索引
常用技巧： 禁用索引，载入数据， 重新启用索引
此办法对于唯一索引无效
Innodb 中的类似操作
- 先删除所有非唯一索引
- 增加新列
- 重新创建删掉的索引
操作步骤：
- 用需要的表结构创建一张表，但是不包括索引
- 载入数据表中以构建
.myd文件 - 按照需要的结构创建 另一张空表，这次要包含索引。这会创建需要的
.frm和.myi文件 - 获取读锁并刷新表
- 重命名第二种表 .frm 和 .myi 文件。让 Mysql 认为是 第一张表
- 释放读锁
- 使用 repair table 来重新创建表的索引。 会根据排序来构建所有的索引，包括唯一索引
总结
写在最后
第二篇读书笔记的内容对于mysql进行进一步的扩展，基准测试以及schema的描述相关内容，后续介绍了关于服务器性能剖析的相关内容。
