前言
最近发现有时候看完一本书，时间久了容易忘记，看书不总结思考效果大打折扣，故打算写这一系列文章，一是为了整理书中的要点，帮助自己消化理解；二是勉励自己多看书思考。文章中不会把书中内容讲解的非常详细，只是总结概括，适合已经阅读过该书的读者。
第2章：MySQL基准测试
基准测试是针对系统设计的一种压力测试
- 基准测试有两种主要的策略
- 针对整个系统的整体测试，集成式（full-stack)
- 单独测试MySQL，单组件式（single-component）
测试指标
- 吞吐量：单位时间内的事务处理数
- 响应时间或者延迟：测试任务所需的整体时间
- 并发性
- 可扩展性
基准测试常见错误
- 使用真实数据的子集而不是全集
- 使用错误的数据分布
- 使用不真实的分布参数
- 在多用户场景，只做单用户的测试
- 在单服务器上测试分布式应用
- 反复执行同一个查询
- 没有检查错误
基准测试工具
- MySQL Benchmark Suite（sql-bench）：自带
- sysbench：推荐使用，功能较多完善
第3章：服务器性能剖析
主要为了定位使性能低的位置，主要方法使专注测量服务器的时间花费在哪里
剖析MySQL查询
- 剖析整个数据服务器
- 通过慢查询日志记录查询：mysql设置，信息相对较少
- 通过pt-query-digest分析tcpdump结果：信息相对较多
- 对查询进行单独的剖析
- show profile
- set profiling = 1;
- show profiles; // 显示所有查询的时间
- show profile for query 1; // 显示查询1的详细时间开销
- show status
- 返回一些有用的计数器，如read、delete的次数等
- flush status; // 清除统计，重新开始
- 慢查询日志
- show profile
诊断间歇性问题
- show global status
- 以较高的频率反复执行该命令捕获数据，如Threads_connected、Threads_running的变化
- show processlist（主要查看线程状态）
- 以较高频率反复执行该命令，观察是否有大量线程处于不正常的状态或者不正常的特征
一般推荐上述两种方法，应为开销很低，而且可以通过简单的shell脚本或者反复执行的查询来交互式的收集数据，观察服务器的情况
第4章：Schema与数据类型优化
数据类型选择原则
- 更小的通常更好：执行更快，占用更少磁盘、内存、CPU缓存
- 简单就好：操作需要更少的CPU周期
尽量避免NULL：使得索引、索引统计和值的比较都更复杂
-
整数类型
- TINYINT：8位；SMALLINT：16位；MEDIUMINT：24位；INT：32位；BIGINT：64位
- 实数类型
- FLOAT：32位；DOUBLE：64位，浮点类型不精确
- DECIMAL：精确
- 字符串类型
- VARCHAR：需要使用1至2个额外字节记录字符串长度
- CHAR：固定长度，对于经常变更的数据更适用
- BLOB：采用二进制存储很大的数据
- TEXT：采用字符方式存储很大的数据
- 日期和时间类型
- DATETIME：1001年-9999年
- TIMESTAMP：1970年-2038年
范式和反范式
- 范式优点
- 更新操作比反范式快
- 很少重复数据，存储少
- 检索数据时很少需要GROUP BY之类的
- 范式缺点
- 通常需要关联
- 反范式
- 查询速度快，单独的表更有效的索引策略
第5章：创建高性能的索引
索引类型
-
B-Tree索引
- 适合：全键值、键值范围或键值前缀查找
- 限制
- 不是按索引最左列开始查找，无法使用
- 不能跳过索引中的列
- 若有范围查询，其右边所有列不能使用索引优化
-
哈希索引
- 只有精确匹配索引所有列才有效
- 限制
- 只包含哈希值和行指针，不存储字段值
- 不按照索引值顺序存储
- 不支持部分索引列匹配查找
- 不支持范围
- 哈希冲突很多的话，维护代价高
InnoDB引擎有一个特殊的功能叫做”自适应哈希索引”。当InnoDB某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引
索引的优点
- 大大减少了服务器需要扫描的数据量
- 帮助服务器避免排序和临时表
- 将随即IO变为顺序IO
高性能索引策略
- 独立的列
- 查询中的列不是独立的，即索引列是表达式的一部分，或者函数的参数，则不能使用索引
- 前缀索引
- 对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引
- 前缀索引使索引更小、更快，但无法使用GROUP BY和ORFER BY，也无法覆盖扫描
- 多列索引
- 索引合并策略，一定程度上可以使用表上的多个单列索引来定位指定的行
- 如过个and或者or条件
- 合适索引列顺序
- 原则：选择性最高的列放在索引最前列
- 聚簇索引
- 定义：在同一个地方保存了索引和数据行
- 优点：
- 把相关数据保存在一起
- 数据访问更快
- 使用覆盖索引扫描的查询可以直接使用页节点的主键值
- 缺点
- 如果不是IO密集型应用，聚簇没优势
- 插入速度严重依赖插入顺序
- 插入新行，或者主键更新时，面临页分裂问题
- 可能导致全表扫描变慢
- 覆盖索引
- 如果一个索引包含所有需要查询的字段的值，我们就称之为”覆盖索引”
- EXPLAIN的EXTRA列显示Using index
- 索引扫描排序
- 如果EXPLAIN的type列显示index，说明采用索引扫描排序
维护索引和表
- 找到损坏表：check table
- 修复损坏表：repaire table
- 更新索引统计信息：analyze table
- 减少索引和数据碎片：optimize table
第6章：查询性能优化
-
优化数据访问
- 查询不需要的记录
- 多表关联时返回全部列
- 总是取出全部列
- 重复查询相同的数据
-
是否扫描额外的记录，三个指标
- 响应时间
- 扫描的行数
- 返回的行数
-
重构查询方式
- 一个复杂查询还是多个简单查询
- 切分查询，如把一个大的delete切分成多个
- 分解关联查询，关联放在应用程序中
查询执行基础
优化特定类型的查询
- 优化count()查询
- count() where id > 5 转化为 count() - count(*) where id <= 5
- 使用近似值
- 优化关联查询
- 确保on或者using子句中的列有索引
- 确保group by或者order by只涉及一列，才能使用索引
- 优化子查询
- 尽可能使用关联查询代替
- 优化limit
- 尽可能使用索引覆盖扫描，再根据需要做一次关联操作，对于偏移量很大的时候，效率提高很多
- 优化union
- 手工将where、limit、order by等子句下推到各个union子查询，以便优化器充分利用这些条件优化
第7章：MySQL高级特性
分区表
-
使用场景
- 表非常大以至于无法全部都放在内存中
- 分区表的数据更容易维护
- 可以分布存储在不同物理设备上
- 备份、恢复独立分区
-
什么情况会出问题
- NULL会使分区过滤无效
- 分区列和索引列不匹配
- 选择分区的成本很高
- 打开并锁住所有底层表的成本可能很高
- 维护分区的成本可能很高
在MySQL内部存储代码
MySQL允许通过触发器、存储过程、函数的形式存储代码
- 优点
- 节省网络开销
- 代码重用
- 简化代码的维护和版本更新
- 提升安全性
- 缺点
- MySQL没有好的调试开发工具
- 存储代码效率相对较低
- 部署复杂性
- 服务器增加额外的压力
绑定变量
创建绑定变量SQL时，客户端向服务器发送了一个SQL语句原型，服务端收到这个SQL语句框架后，解析并存储这个SQL语句，返回客户端一个SQL语句处理句柄
- 优点
- 只需解析一次SQL
- 某些优化器只需执行一次
- 仅仅是参数，网络开销小，效率高
- 安全
- 缺点
- 会话级别
全文索引
希望通过关键字的匹配来进行查询过滤。全文索引可以支持各种字符内容的搜索，也支持自然语言搜索和布尔搜索。具体的，对数据表的某一条记录，MySQL会将需要索引的列全部拼接成一个字符串，然后进行索引。相比其他索引，当insert、update和delete操作进行时，全文索引的操作代价很大
优化：提供一个好的停用词表；忽略一些太短的单词
查询缓存
很多数据库产品，对于相同类型的SQL可以跳过SQL解析和执行计划生成阶段；MySQL还有另外一种不同的缓存，缓存完整的select查询结果，即跳过解析、优化和执行阶段。
- 查询缓存容易成为整个服务器的资源竞争单点
- 靠全局锁作保护
- 打开查询缓存对读写操作带来消耗
- 读查询前需先检查是否命中缓存
- 读查询，如果能被缓存，执行查询后需缓存
- 写出数据时，对应表的缓存设置失效
- 缓存碎片、内存不足、数据修改都会造成缓存失效