千金良方 MySQL性能优化金字塔法则

 

本书一共分为3篇：基础篇、案例篇和工具篇。“基础篇”从理论基础和基本原理层面介绍了MySQL的安装与配置、升级和体系结构，information_schema、sys_schema、performance_schema和mysql_schema，MySQL复制，MySQL事务，SQL语句优化及架构设计基础知识。“案例篇”从硬件和系统、MySQL架构等方面给出了性能优化的十几个案例，包括：性能测试的基本优化思路和最需要关注的性能指标解释、对日常SQL语句执行慢的基本定位、避免x86可用性的一般性方法、节能模式会怎样影响性能、I/O存储作为数据库最重要的依赖是如何影响数据库性能的、主备复制不一致可能有哪些原因、字符集不一致会造成哪些性能问题、在实际场景中锁的争用是怎样的。“工具篇”介绍了在MySQL性能优化过程中需要用到的各种工具，包括：dmidecode、top、dstat等硬件和系统排查工具；FIO、sysbench、HammerDB等压力测试工具；mysqldump、XtraBackup等备份工具；Percona、innotop、Prometheus等监控工具。

 

基础篇
第1章MYSQL初始化安装、简单安全加固
1.1背景
1.2初始化安装
1.2.1下载二进制安装文件
1.2.2创建MYSQL用户
1.2.3创建程序、数据存放目录
1.2.4解压缩二进制安装文件并设置目录权限
1.2.5软链接程序路径，并设置MYSQL命令环境变量
1.2.6配置MY.CNF文件参数
1.2.7初始化MYSQL
1.2.8启动MYSQL
1.3简单安全加固
1.3.1登录MYSQL
1.3.2删除非ROOT或非LOCALHOST的用户并修改ROOT密码
1.3.3删除TEST库，清理MYSQL.DB表
1.4创建用户、库、表、数据
1.4.1创建管理用户并授权
1.4.2创建库、表、程序账号
1.4.3插入数据
1.5MYSQL参数模板
第2章MYSQL常用的两种升级方法
2.1背景
2.2MYSQL5.5.54的安装
2.3升级MYSQL5.5.54到MYSQL5.6.35
2.3.1使用MYSQL_UPGRADE直接升级数据字典库
2.3.2使用MYSQLDUMP逻辑备份数据
2.4升级注意事项
第3章MYSQL体系结构
3.1快速安装MYSQL
3.2数据目录结构
3.3MYSQLSERVER体系结构
3.4MYSQL中的存储引擎
3.5INNODB存储引擎体系结构
3.6INNODB存储引擎后台线程
3.7MYSQL前台线程
第4章PERFORMANCE_SCHEMA初相识
4.1什么是PERFORMANCE_SCHEMA
4.2PERFORMANCE_SCHEMA使用快速入门
4.2.1检查当前数据库版本是否支持
4.2.2启用PERFORMANCE_SCHEMA
4.2.3PERFORMANCE_SCHEMA表的分类
4.2.4PERFORMANCE_SCHEMA简单配置与使用
第5章PERFORMANCE_SCHEMA配置详解
5.1编译时配置
5.2启动时配置
5.2.1启动选项
5.2.2SYSTEMVARIABLES
5.3运行时配置
5.3.1PERFORMANCE_TIMERS表
5.3.2SETUP_TIMERS表
5.3.3SETUP_CONSUMERS表
5.3.4SETUP_INSTRUMENTS表
5.3.5SETUP_ACTORS表
5.3.6SETUP_OBJECTS表
5.3.7THREADS表
第6章PERFORMANCE_SCHEMA应用示例荟萃
6.1利用等待事件排查MYSQL性能问题
6.2锁问题排查
6.2.1找出谁持有全局读锁
6.2.2找出谁持有MDL锁
6.2.3找出谁持有表级锁
6.2.4找出谁持有行级锁
6.3查看最近的SQL语句执行信息
6.3.1查看最近的TOPSQL语句
6.3.2查看最近执行失败的SQL语句
6.4查看SQL语句执行阶段和进度信息
6.4.1查看SQL语句执行阶段信息
6.4.2查看SQL语句执行进度信息
6.5查看最近的事务执行信息
6.6查看多线程复制报错详情
第7章SYS系统库初相识
7.1SYS系统库使用基础环境
7.2SYS系统库初体验
7.3SYS系统库的进度报告功能
第8章SYS系统库配置表
8.1SYS_CONFIG表
8.2SYS_CONFIG_INSERT_SET_USER触发器
8.3SYS_CONFIG_UPDATE_SET_USER触发器
第9章SYS系统库应用示例荟萃
9.1查看慢SQL语句慢在哪里
9.2查看是否有事务锁等待
9.3查看是否有MDL锁等待
9.4查看INNODB缓冲池中的热点数据有哪些
9.5查看冗余索引
9.6查看未使用的索引
9.7查询表的增、删、改、查数据量和I/O耗时统计信息
9.8查看MYSQL磁盘文件产生的磁盘流量与读写比例
9.9查看哪些语句使用了全表扫描
9.10查看哪些语句使用了文件排序
9.11查看哪些语句使用了临时表
第10章INFORMATION_SCHEMA初相识
10.1什么是INFORMATION_SCHEMA
10.2INFORMATION_SCHEMA组成对象
10.2.1SERVER层的统计信息字典表
10.2.2SERVER层的表级别对象字典表
10.2.3SERVER层的混杂信息字典表
10.2.4INNODB层的系统字典表
10.2.5INNODB层的锁、事务、统计信息字典表
10.2.6INNODB层的全文索引字典表
10.2.7INNODB层的压缩相关字典表
第11章INFORMATION_SCHEMA应用示例荟萃
11.1使用SERVER层的字典表查询相关的元数据信息
11.1.1查看数据库中是否使用了外键
11.1.2查看INNODB表空间文件信息
11.1.3查看索引的统计信息
11.1.4查看表的字段相关信息
11.1.5查看数据库中是否使用了存储程序
11.1.6查看数据库中的分区表信息
11.1.7查看数据库中的触发器
11.1.8查看数据库中的计划任务
11.1.9查看客户端会话的状态信息
11.2使用INNODB层的字典表查询相关的元数据信息
11.2.1查看索引列的信息
11.2.2查看表的字段相关信息
11.2.3查看事务锁等待信息
11.2.4查看INNODB缓冲池中的热点数据有哪些
第12章MYSQL系统库之权限系统表
12.1USER
12.2DB
12.3TABLES_PRIV
12.4COLUMNS_PRIV
12.5PROCS_PRIV
12.6PROXIES_PRIV
第13章MYSQL系统库之访问权限控制系统
13.1访问权限控制系统概述
13.2MYSQL提供了哪些权限
13.3MYSQL账号命名规则
13.4MYSQL账号访问控制两阶段
13.4.1第一阶段（账号和密码认证）
13.4.2第二阶段（权限检查）
13.5权限变更的影响
13.6MYSQL常见连接问题
第14章MYSQL系统库之统计信息表
14.1统计信息概述
14.2统计信息表详解
14.2.1INNODB_TABLE_STATS
14.2.2INNODB_INDEX_STATS
第15章MYSQL系统库之复制信息表
15.1复制信息表概述
15.2复制信息表详解
15.2.1SLAVE_MASTER_INFO
15.2.2SLAVE_RELAY_LOG_INFO
15.2.3SLAVE_WORKER_INFO
15.2.4GTID_EXECUTED
15.2.5NDB_BINLOG_INDEX
第16章MYSQL系统库之日志记录表
16.1日志信息概述
16.2日志表详解
16.2.1GENERAL_LOG
16.2.2SLOW_LOG
第17章MYSQL系统库应用示例荟萃
17.1查看用户不同作用域的权限
17.1.1查看全局权限
17.1.2查看库级别权限
17.1.3查看表级别权限
17.1.4查看字段级别权限
17.2查看统计信息
17.2.1查看表统计信息
17.2.2查看索引统计信息
17.3查看SQL日志信息
第18章复制技术的演进
18.1复制格式概述
18.2基于数据安全的复制技术演进
18.2.1异步复制
18.2.2半同步复制
18.2.3增强半同步复制
18.2.4组复制
18.2.5GTID复制
18.3基于复制效率的复制技术演进
18.3.1单线程复制
18.3.2DATABASE并行复制
18.3.3LOGICAL_CLOCK并行复制
18.3.4WRITESET并行复制
第19章事务概念基础
19.14种事务隔离级别和3种异常现象
19.1.1标准的事务隔离级别
19.1.2调整事务隔离级别
19.2从事务提交谈起
19.2.1事务管理
19.2.2数据库中的文件
19.2.3WAL日志先写
19.3MYSQL中的REDO日志
19.3.1REDO日志落盘时间点
19.3.2REDO日志格式
19.3.3相关参数及概念
19.3.4CHECKPOINT概念
19.3.5REDO日志的设置
19.3.6REDO日志与BINLOG协调工作
19.4MVCC介绍
19.4.1MVCC原理
19.4.2具体代码
第20章INNODB锁
20.1INNODB锁概述
20.1.1INNODB锁分类
20.1.2行锁
20.1.3表锁
20.1.4锁模式对应的含义
20.2加锁验证
20.2.1REPEATABLE-READ隔离级别+表无显式主键和索引
20.2.2REPEATABLE-READ隔离级别+表有显式主键但无索引
20.2.3REPEATABLE-READ隔离级别+表无显式主键但有索引
20.2.4REPEATABLE-READ隔离级别+表有显示主键和索引
20.2.5READ-COMMITTED隔离级别+表无显式主键和索引
20.2.6READ-COMMITTED隔离级别+表有显式主键无索引
20.2.7READ-COMMITTED隔离级别+表无显式主键有索引
20.2.8READ-COMMITTED隔离级别+表有显式主键和索引
第21章SQL优化
21.1SQL优化基础概念
21.2MYSQL中的JOIN算法
21.3MYSQL中的优化特性
第22章MYSQL读写扩展
22.1分布式架构概述
22.2分库分表两种方式
22.2.1中间件方式
22.2.2客户端方式
22.2.3客户端方式与中间件方式不同
22.2.4分库与分表
22.3中间件工作方式及原理
22.4架构设计及业务场景
22.4.1读写扩展案例
22.5关于中间件的一些解释
22.5.1跨库查询、复杂的SQL语句支持
22.5.2分布式事务
案例篇
第23章性能测试指标和相关术语
23.1测试目标分类
23.1.1已知故障信息采集
23.1.2预估压力评测
23.2性能测试指标
23.2.1数据库性能通用“切口”
23.2.2测试通用术语
23.2.3示例
23.3本章小结
第24章历史问题诊断和现场故障分析
24.1故障分析和验证
24.1.1历史问题排查
24.1.2在线问题分析
24.2故障复现排查
24.3本章小结
第25章性能调优金字塔
25.1硬件和系统调优
25.1.1硬件优化
25.1.2系统优化
25.2MYSQL调优
25.2.1参数调优
25.2.2SQL/索引调优
25.3架构调优
25.4本章小结
第26章SQL语句执行慢真假难辨
26.1概述
26.2测试环境
26.3采集数据包
26.3.1采集应用服务器数据包
26.3.2采集数据库服务器数据包
26.4解析数据包
26.4.1使用WIRESHARK解析数据包
26.4.2使用TCPDUMP解析数据包
26.5本章小结
第27章如何避免三天两头换硬盘、内存、主板
27.1概述
27.2服务器标准化
27.3上线前烤机
27.3.1STRESS
27.3.2FIO
27.3.3数据库烤机
27.4本章小结
第28章每隔45天的MYSQL性能低谷
28.1存储知识小普及
28.1.1MYSQL存储系统要求
28.1.2存储系统软件层
28.1.3存储系统硬件层
28.2每隔45天的性能抖动
28.3影响I/O性能的三种RAID策略
28.3.1电池充放电
28.3.2巡读
28.3.3一致性检查
28.4本章小结
第29章MYSQL连接无法自动释放
29.1环境配置
29.2问题现象
29.3诊断分析
29.4解决方案
29.5本章小结
第30章查询MYSQL偶尔比较慢
30.1环境配置
30.2问题现象
30.3诊断分析
30.4选择VIP还是DNS访问MYSQL
30.4.1VIP之殇
30.4.2DNS之痛
30.4.3VIP切换还是DNS切换
30.5CPU节能模式
30.6本章小结
第31章MYSQL最多只允许214个连接
31.1环境配置
31.2故障现象
31.3故障分析
31.4解决方案
31.5MYSQL优选连接数为214的源码解析
31.6LINUX资源
31.6.1资源拆分和组合
31.6.2进程资源
31.6.3MYSQL内部资源
31.7本章小结
第32章MYSQL挂起诊断思路
32.1环境配置
32.2问题现象
32.3诊断分析
32.4挂起时先做什么
32.5总结
第33章硬件和系统调优
33.1硬件和系统调优概览
33.1.1I/O：MYSQL80%的性能瓶颈所在
33.2CPU
33.3网络
33.4其他
33.5总结
第34章并发删除数据造成死锁
34.1现象描述
34.2如何阅读死锁日志
34.3表结构及操作步骤
34.4总结
第35章删除不存在的数据造成死锁
35.1现象描述
35.2问题分析
35.3问题扩展
35.4表结构及操作步骤
35.5总结
第36章插入意向锁死锁
36.1现象描述
36.2问题分析
36.3总结
第37章分页查询优化
37.1现象描述
37.2优化方案
第38章子查询优化——子查询转换为连接
38.1现象描述
38.2优化方案
第39章子查询优化——使用DELETE删除数据
39.1现象描述
39.2优化方案
工具篇
第40章硬件规格常用查看命令详解
40.1通用命令
40.1.1LSHW
40.1.2DMIDECODE
40.1.3DMESG
40.2CPU相关命令
40.3磁盘相关命令
40.3.1SMARTCTL
40.3.2LSSCSI
40.4网卡相关命令
40.4.1LSPCI
40.4.2ETHTOOL
40.5HCA卡相关命令
第41章系统负载常用查看命令详解
41.1TOP
41.1.1命令行选项
41.1.2交互式命令选项
41.1.3输出结果解读
41.2DSTAT
41.2.1命令行选项
41.2.2输出结果解读
41.3MPSTAT
41.3.1命令行选项
41.3.2输出结果解读
41.4SAR
41.4.1命令行选项
41.4.2输出结果解读
41.5VMSTAT
41.5.1命令行选项
41.5.2输出结果解读
41.6IOSTAT
41.6.1命令行选项
41.6.2输出结果解释
41.7FREE
41.7.1命令行选项
41.7.2输出结果解读
41.8IOTOP
41.8.1命令行选项
41.8.2交互式命令选项
41.8.3输出结果解读
41.9IFTOP
41.9.1命令行选项
41.9.2交互式命令选项
41.9.3输出结果解读
41.10IPERF
41.10.1命令行选项
41.10.2输出结果解读
第42章FIO存储性能压测
42.1安装FIO
42.2测试I/O性能
42.2.1随机写
42.2.2顺序写
42.2.3随机读
42.2.4顺序读
42.2.5混合随机读写
42.2.6FIO配置文件测试
42.3参数和结果详解
42.3.1关键参数解释
42.3.2配置文件
42.3.3结果解析
42.4FIO测试建议
42.5课外阅读
第43章HAMMERDB在线事务处理测试
43.1安装和配置HAMMERDB
43.1.1下载安装包
43.1.2安装HAMMERDB
43.1.3安装HAMMERDBGUI依赖包
43.2测试
43.3课外阅读
第44章SYSBENCH数据库压测工具
44.1安装SYSBENCH
44.1.1YUM安装
44.1.2RPM包安装
44.1.3编译安装
44.1.4验证安装是否成功
44.2测试案例
44.2.1造数
44.2.2数据库读写测试
44.2.3清理
44.3SYSBENCH参数详解
44.3.1SYSBENCH命令语法
44.3.2OPTIONS
44.3.3TESTNAME
44.3.4COMMAND
44.4数据库测试输出信息详解
44.4.1输出结果概述
44.4.2版本及关键测试参数输出
44.4.3中间统计结果输出
44.4.4整体统计结果输出
44.5课外阅读
第45章MYSQLADMIN和INNOTOP工具详解
45.1MYSQLADMIN
45.1.1命令行选项
45.1.2实战演示
45.2INNOTOP
45.2.1安装INNOTOP
45.2.2命令行选项
45.2.3交互式选项
45.2.4实战演示
第46章利用PROMETHEUS+GRAFANA搭建炫酷的MYSQL监控平台
46.1安装PROMETHEUS
46.1.1下载程序包
46.1.2解压缩程序包
46.1.3启动PROMETHEUS
46.2安装GRAFANA
46.2.1下载程序包
46.2.2解压缩程序包
46.2.3启动GRAFANA
46.2.4在GRAFANA中导入GRAFANA-DASHBOARDS
46.3监控节点部署
46.3.1添加主机监控
46.3.2添加MYSQL监控
46.3.3监控DASHBOARD切换
第47章PERCONATOOLKIT常用工具详解
47.1PT-QUERY-DIGEST
47.1.1命令行选项
47.1.2实战演示
47.2PT-IOPROFILE
47.2.1命令行选项
47.2.2实战演示
47.3PT-INDEX-USAGE
47.3.1命令行选项
47.3.2实战演示
47.4PT-DUPLICATE-KEY-CHECKER
47.4.1命令行选项
47.4.2实战演示
47.5PT-MYSQL-SUMMARY/PT-SUMMARY
47.5.1PT-MYSQL-SUMMARY
47.5.2PT-SUMMARY
47.6PT-PMP
47.6.1命令行选项
47.6.2实战演示
47.7PT-STALK
47.7.1命令行选项
47.7.2实战演示
47.8PT-SIFT
47.8.1命令行选项
47.8.2实战演示
第48章MYSQL主流备份工具之MYSQLDUMP详解
48.1简介
48.2原理
48.3命令行选项
48.3.1连接选项
48.3.2文件选项
48.3.3DDL选项
48.3.4调试选项
48.3.5帮助选项
48.3.6国际化选项——与字符集相关的选项
48.3.7复制选项
48.3.8输出格式化选项
48.3.9过滤选项
48.3.10性能选项
48.3.11事务选项
48.3.12组合选项
48.4实战演示
48.4.1接近备份与恢复
48.4.2增量备份与恢复
48.4.3搭建主从复制架构
48.4.4克隆从库
48.4.5指定库表备份与恢复
48.4.6纯文本备份与恢复
第49章MYSQL主流备份工具之XTRABACKUP详解
49.1简介
49.2原理
49.3命令行选项
49.4实战演示
49.4.1接近备份与恢复
49.4.2增量备份与恢复
49.4.3基于时间点的恢复
49.4.4搭建主从复制架构
49.4.5克隆从库
第50章MYSQL主流备份工具之MYDUMPER详解
50.1简介
50.2原理
50.3命令行选项
50.3.1MYDUMPER
50.3.2MYLOADER
50.4实战演示
50.4.1安装MYDUMPER
50.4.2备份与恢复
第51章MYSQL主流闪回工具详解
51.1闪回工具科普
51.2BINLOG2SQL
51.2.1安装和使用要求
51.2.2命令行选项
51.2.3实战演示
51.2.4使用与注意事项
51.3MYFLASH
51.3.1安装MYFLASH
51.3.2命令行选项
51.3.3实战演示