高性能MySQL（第四版）读书笔记（自用）

仅记录个人知识盲区和个人认为有用的内容，从“Schema设计”这一章往后才开始仔细记录了笔记，因此之前的内容比较零散。由于阅读时间有限，跳过了部分章节（“备份与恢复”、“云上的MySQL”、“MySQL合规性”）

，？关于事务重试，spring框架如何处理
，？行写入和redo记录的先后顺序，是否redo先写入

性能指标

，团队建立 SLI（服务级别指标）可量化衡量标准，例如一年内停机时间，保证至少x秒以上数据的持久性等
，沟通建立（并可向客户宣传或承诺）SLO（服务级别目标），例如一年内99.5%的停机时间（可以计算出每周允许停机的时长）等，可以引导团队投入精力的方向

性能监控

，percona工具，performance_schema
，threads_connected, threads_running, cpus
，lock wait timeout错误发生的频率增增加，aborted connections激增
，？关于杀死进程临时解决问题的问题，事务重试等
，iostat工具监控io资源

performance_schema

，有大量插桩相关的统计数据表，建议开启（需重启），插桩则运行时按需开启
，插桩只能在启动后起作用，插桩启动前的统计数据丢失
，uservariable_by_thread

关于硬件和OS限制

，硬盘应选用自带备用电池，且可通过暴力测试（拔插头）验证备用电池足够将大量数据写入时硬盘缓冲区完整写入磁盘（因为MySQL的持久性依赖redo log确切写入了磁盘，而磁盘在写入缓冲区后就会向上层汇报写入完成）
，使用SSD来提高IO速率，但是顺序读取依然比随机读取快，原因主要是硬盘读取是以数据页为单位的（比如SSD的4K对齐）
，推荐启用至少RAID5磁盘阵列；条带化可以提高顺序读取速率（转换为并行读取），需要注意RAID驱动器本身的特征
，应减少内存交换（甚至于关闭？但是可能引起MySQL被kill，实际上和后面说不能限制MySQL的内存使用有矛盾），关闭操作系统的文件写入缓冲，使用直写模式(因为innodb自己有缓冲池，关闭系统文件缓冲可以减少mysql内存占用，减少oom_kill和内存交换概率)，将RAID磁盘驱动器的缓存更多分配到写入缓存和驱动器缓存(禁用读缓存和预读取缓存，因为会影响innodb缓冲区工作)

innodb如何刷新日志缓冲区

，写磁盘有写入和flush两个动作，flush有时可能会是操作系统的假同步，也可能是RAID驱动器的假同步，也可能是磁盘驱动的假同步
，日志缓冲区的三种写入策略，是通过控制提交时是否写入和刷新来区分的
，innodb_file_per_table控制每个表拥有自己的表空间，且每个表用多少个文件存储，建议设置为1，方便通过文件大小查看表占用空间

schema设计

，varchar内容增长时，innodb可能会分割数据页即B+树节点分裂，可通过Optimize Table 语句优化。varchar最大字符数量与字符集有关，innodb可能将比较长的保存为blob；char存储时会截断末尾的空格
，varchar在内存中和filesort的临时表（只要不通过索引排序，都叫filesort，如果内存不满足则使用文件系统，此时会将未压缩的数据行写入文件，可能导致磁盘占用激增）中，是固定长度存储的，在持久化时是变长的
，uuid可以用binary压缩，比字符串高性能（32字节变16字节空间和计算砍半），set和enum擅长处理多选和单选的情况

索引

，主键一般建议自增，以便顺序INSERT时io更少和页分裂更少；但是在高并发的场景，顺序写入会导致间隙竞争和auto_increment竞争，autoinc锁竞争可以看mysql版本是否支持调整innodb_autoinc_lock_mode参数
，如果可能出现了大量页分裂的情况，应使用optimize table重新整理存储结构
，同时使用多个索引共同查询采用了"索引合并"策略，会消耗额外的缓存内存，在并发场景下严重影响性能
，回表查询的效率低下，原因是顺序读取二级索引时每个记录都需要在聚簇索引上查询一次，因为二级索引的顺序和聚簇索引无关，因此本质上回表查询是随机io，其问题在于单行访问需要读取整个4k数据页，机械硬盘则再加上寻道时间
，order by要用索引进行排序，索引的顺序必须和语句中指定的完全一致，包括升降序，降序可以使用反转串和相反数的技巧，可能sql要相应调整？如果在where语句中限定了前导字段，则orderby可以不包含前导字段
，explain中出现的filesort指的是使用缓存进行排序，不一定涉及io
，可以使用pt-query-digest等工具找出性能最差的查询，再考虑集中精力优化
，存在三种碎片，行碎片(影响任何查询)，行间碎片(影响顺序读取，范围查询等)，剩余空间碎片(比如4k对齐的数据页没有被数据填满)，通过optimize table或alter table no-op整理，或导出再导入数据

Replica 复制 主从 读写分离

，延迟复制用于误操作恢复
，在复制节点上启用super_read_only，避免任何意外写入，如果节点用于故障切换，最好有自动脚本处理这些配置变更
，启用GTID事务复制特性，注意正确配置服务器id；应启用基于行的bin log以确保数据正确同步，基于语句的同步需要注意数据顺序等不一致导致的严重后果。复制过滤器可用于过滤特定的库变更，但是要注意是基于当前库而不是表的实际库名的，因此存在隐患
，关于复制延迟，可通过Seconds_behind_sources值监控，但是只能在有正在同步的语句时才有值(如果网络断开则无法得知)，同时会随长事务的提交而剧烈波动；应该采用心跳包时间差值的方式，Percona Toolkit包含相关脚本
，扩展读采用复制节点，扩展写采用数据分片，复制延迟可开启多线程复制；还可以临时降低持久性设置，在延迟恢复后调整回来

skipped p231~257 备份与恢复

扩展mysql

，建议将读密集和写密集的业务分离，各自扩展，否则面临更多复杂性

读扩展

，将用于备份的实例和用于读的实例分离，用于报表查询的实例分离
，使用自动管理的读池，将提供旧数据的节点踢出，赶上进度后再重新加入，但是要注意定义所有节点都被踢出后的备用方案
，负载均衡算法：随机，轮询，最少连接，最快响应，哈希，权重(即按性能优先)
，(读扩展也可通过分片解决，适合无法忍受延迟的情况)

写扩展

，分析数据写入特征，如果可以接受延迟，应该使用队列(设计异步交互)

按功能分片，按业务功能划分实例，SOA，微服务架构(领域驱动设计)

按数据分片

，注意关联数据一起分片，避免高频查询交叉访问
，可以构造字段关系图，尝试切断尽量少的线条得到尽量大的独立子图，切断的线条可以引导发现被忽视的分片候选键
，对于多种角度访问(一方面从用户，一方面从商品下的评论，一方面从商家)，可以将数据解藕，分别采用不同的分片键(各自保存业务上关联强的数据，类似于DDD的战术设计，可能会存在一定的数据冗余)，书中的例子是书籍评论网站，将用户评论保存在用户模型中，书籍模型中只保存评论标题和ID；(可以预见这种方式如果想展示评论概览或者收起版本，需要额外保存一份概览供批量查询，而单个评论展开则请求另外数据库实例)
，跨分片查询，可以采用缓存，比如跨分片查询后保存到汇总表(可以单独保存)，好的分片设计中，这种查询应该尽量少(而且可能实时性要求更低)
，vitess分片系统(重量级)：优先更小的实例，复制和自动写故障切换，半同步复制持久化
，proxySQL(轻量级)：负载均衡，按用户分片(配置不同用户凭证和实例组，按功能分片)，按schema命名规则分流(实现功能数据分片或按数据分片)

p279