数字金字塔MySQL存储过程_千金良方——MySQL性能优化金字塔法则

基 础 篇

第1章　MYSQL初始化安装、简单安全加固3

1.1　背景3

1.2　初始化安装3

1.2.1　下载二进制安装文件3

1.2.2　创建MYSQL用户3

1.2.3　创建程序、数据存放目录4

1.2.4　解压缩二进制安装文件并设置目录权限4

1.2.5　软链接程序路径，并设置MYSQL命令环境变量5

1.2.6　配置MY.CNF文件参数5

1.2.7　初始化MYSQL6

1.2.8　启动MYSQL7

1.3　简单安全加固8

1.3.1　登录MYSQL8

1.3.2　删除非ROOT或非LOCALHOST的用户并修改ROOT密码9

1.3.3　删除TEST库，清理MYSQL.DB表10

1.4　创建用户、库、表、数据12

1.4.1　创建管理用户并授权12

1.4.2 创建库、表、程序账号13

1.4.3　插入数据14

1.5　MYSQL参数模板16

第2章　MYSQL常用的两种升级方法21

2.1　背景21

2.2　MYSQL 5.5.54的安装21

2.3　升级MYSQL 5.5.54到MYSQL 5.6.3521

2.3.1　使用MYSQL_UPGRADE直接升级数据字典库21

2.3.2　使用MYSQLDUMP逻辑备份数据30

2.4　升级注意事项38

第3章　MYSQL体系结构41

3.1　快速安装MYSQL41

3.2　数据目录结构43

3.3　MYSQL SERVER体系结构46

3.4　MYSQL中的存储引擎48

3.5　INNODB存储引擎体系结构48

3.6　INNODB存储引擎后台线程52

3.7　MYSQL前台线程54

第4章　PERFORMANCE_SCHEMA初相识56

4.1　什么是PERFORMANCE_SCHEMA56

4.2　PERFORMANCE_SCHEMA使用快速入门57

4.2.1　检查当前数据库版本是否支持57

4.2.2　启用PERFORMANCE_SCHEMA58

4.2.3　PERFORMANCE_SCHEMA表的分类59

4.2.4　PERFORMANCE_SCHEMA简单配置与使用62

第5章　PERFORMANCE_SCHEMA配置详解66

5.1　编译时配置66

5.2　启动时配置67

5.2.1　启动选项67

5.2.2　SYSTEM VARIABLES69

5.3　运行时配置71

5.3.1　PERFORMANCE_TIMERS表71

5.3.2　SETUP_TIMERS表72

5.3.3　SETUP_CONSUMERS表73

5.3.4　SETUP_INSTRUMENTS表76

5.3.5　SETUP_ACTORS表82

5.3.6　SETUP_OBJECTS表85

5.3.7　THREADS表87

第6章　PERFORMANCE_SCHEMA应用示例荟萃93

6.1　利用等待事件排查MYSQL性能问题93

6.2　锁问题排查97

6.2.1　找出谁持有全局读锁97

6.2.2　找出谁持有MDL锁100

6.2.3　找出谁持有表级锁104

6.2.4　找出谁持有行级锁107

6.3　查看最近的SQL语句执行信息112

6.3.1　查看最近的TOP SQL语句112

6.3.2　查看最近执行失败的SQL语句114

6.4　查看SQL语句执行阶段和进度信息117

6.4.1　查看SQL语句执行阶段信息117

6.4.2　查看SQL语句执行进度信息120

6.5　查看最近的事务执行信息121

6.6　查看多线程复制报错详情123

第7章　SYS系统库初相识126

7.1　SYS系统库使用基础环境126

7.2　SYS系统库初体验127

7.3　SYS系统库的进度报告功能129

第8章　SYS系统库配置表132

8.1　SYS_CONFIG表132

8.2　SYS_CONFIG_INSERT_SET_USER触发器136

8.3　SYS_CONFIG_UPDATE_SET_USER触发器136

第9章　SYS系统库应用示例荟萃138

9.1　查看慢SQL语句慢在哪里138

9.2　查看是否有事务锁等待140

9.3　查看是否有MDL锁等待141

9.4　查看INNODB缓冲池中的热点数据有哪些143

9.5　查看冗余索引144

9.6 查看未使用的索引145

9.7 查询表的增、删、改、查数据量和I/O耗时统计信息145

9.8　查看MYSQL磁盘文件产生的磁盘流量与读写比例146

9.9　查看哪些语句使用了全表扫描147

9.10　查看哪些语句使用了文件排序148

9.11　查看哪些语句使用了临时表149

第10章　INFORMATION_SCHEMA初相识151

10.1　什么是INFORMATION_SCHEMA151

10.2　INFORMATION_SCHEMA组成对象153

10.2.1　SERVER层的统计信息字典表153

10.2.2　SERVER层的表级别对象字典表154

10.2.3　SERVER层的混杂信息字典表155

10.2.4　INNODB层的系统字典表156

10.2.5　INNODB层的锁、事务、统计信息字典表157

10.2.6　INNODB层的全文索引字典表158

10.2.7　INNODB层的压缩相关字典表160

第11章　INFORMATION_SCHEMA应用示例荟萃161

11.1　使用SERVER层的字典表查询相关的元数据信息161

11.1.1　查看数据库中是否使用了外键161

11.1.2　查看INNODB表空间文件信息162

11.1.3　查看索引的统计信息165

11.1.4　查看表的字段相关信息166

11.1.5　查看数据库中是否使用了存储程序167

11.1.6　查看数据库中的分区表信息168

11.1.7　查看数据库中的触发器169

11.1.8　查看数据库中的计划任务170

11.1.9　查看客户端会话的状态信息172

11.2　使用INNODB层的字典表查询相关的元数据信息173

11.2.1　查看索引列的信息173

11.2.2　查看表的字段相关信息175

11.2.3　查看事务锁等待信息176

11.2.4　查看INNODB缓冲池中的热点数据有哪些176

第12章　MYSQL系统库之权限系统表177

12.1　USER177

12.2　DB179

12.3　TABLES_PRIV180

12.4　COLUMNS_PRIV181

12.5　PROCS_PRIV182

12.6　PROXIES_PRIV182

第13章　MYSQL系统库之访问权限控制系统184

13.1　访问权限控制系统概述184

13.2　MYSQL提供了哪些权限185

13.3　MYSQL账号命名规则191

13.4　MYSQL账号访问控制两阶段192

13.4.1　第一阶段(账号和密码认证)192

13.4.2　第二阶段(权限检查)196

13.5　权限变更的影响197

13.6　MYSQL常见连接问题198

第14章　MYSQL系统库之统计信息表200

14.1　统计信息概述200

14.2　统计信息表详解203

14.2.1　INNODB_TABLE_STATS203

14.2.2　INNODB_INDEX_STATS204

第15章　MYSQL系统库之复制信息表206

15.1　复制信息表概述206

15.2　复制信息表详解207

15.2.1　SLAVE_MASTER_INFO207

15.2.2　SLAVE_RELAY_LOG_INFO209

15.2.3　SLAVE_WORKER_INFO211

15.2.4　GTID_EXECUTED215

15.2.5　NDB_BINLOG_INDEX217

第16章　MYSQL系统库之日志记录表218

16.1　日志信息概述218

16.2　日志表详解220

16.2.1　GENERAL_LOG220

16.2.2　SLOW_LOG223

第17章　MYSQL系统库应用示例荟萃228

17.1　查看用户不同作用域的权限228

17.1.1　查看全局权限228

17.1.2　查看库级别权限230

17.1.3　查看表级别权限232

17.1.4　查看字段级别权限234

17.2　查看统计信息236

17.2.1 查看表统计信息236

17.2.2　查看索引统计信息236

17.3　查看SQL日志信息237

第18章　复制技术的演进245

18.1　复制格式概述245

18.2　基于数据安全的复制技术演进246

18.2.1　异步复制246

18.2.2　半同步复制247

18.2.3　增强半同步复制249

18.2.4　组复制250

18.2.5　GTID复制253

18.3　基于复制效率的复制技术演进255

18.3.1　单线程复制255

18.3.2　DATABASE 并行复制256

18.3.3　LOGICAL_CLOCK 并行复制258

18.3.4　WRITESET并行复制260

第19章　事务概念基础263

19.1　4种事务隔离级别和3种异常现象263

19.1.1　标准的事务隔离级别263

19.1.2　调整事务隔离级别264

19.2　从事务提交谈起265

19.2.1　事务管理265

19.2.2　数据库中的文件266

19.2.3　WAL日志先写266

19.3　MYSQL中的REDO日志267

19.3.1　REDO日志落盘时间点267

19.3.2　REDO日志格式267

19.3.3　相关参数及概念269

19.3.4　CHECKPOINT概念269

19.3.5　REDO日志的设置271

19.3.6　 REDO日志与BINLOG协调工作273

19.4　MVCC介绍274

19.4.1 MVCC原理274

19.4.2　具体代码276

第20章　INNODB锁280

20.1　INNODB锁概述280

20.1.1　INNODB锁分类280

20.1.2　行锁280

20.1.3　表锁281

20.1.4　锁模式对应的含义282

20.2　加锁验证282

20.2.1　REPEATABLE-READ隔离级别+表无显式主键和索引283

20.2.2　REPEATABLE-READ隔离级别+表有显式主键但无索引286

20.2.3　REPEATABLE-READ隔离级别+表无显式主键但有索引288

20.2.4　REPEATABLE-READ隔离级别+表有显示主键和索引291

20.2.5　READ-COMMITTED隔离级别+表无显式主键和索引293

20.2.6　READ-COMMITTED隔离级别+表有显式主键无索引295

20.2.7　READ-COMMITTED隔离级别+表无显式主键有索引295

20.2.8　READ-COMMITTED隔离级别+表有显式主键和索引296

第21章　SQL优化299

21.1　SQL优化基础概念299

21.2　MYSQL中的JOIN算法303

21.3　MYSQL中的优化特性305

第22章　MYSQL读写扩展308

22.1　分布式架构概述308

22.2　分库分表两种方式309

22.2.1　中间件方式309

22.2.2　客户端方式309

22.2.3　客户端方式与中间件方式不同310

22.2.4　分库与分表310

22.3　中间件工作方式及原理310

22.4　架构设计及业务场景312

22.4.1　读写扩展案例312

22.5　关于中间件的一些限制解释314

22.5.1　跨库查询、复杂的SQL语句支持314

22.5.2　分布式事务314

案 例 篇

第23章　性能测试指标和相关术语317

23.1　测试目标分类317

23.1.1　已知故障信息采集317

23.1.2　预估压力评测317

23.2　性能测试指标318

23.2.1　数据库性能通用“切口”318

23.2.2　测试通用术语319

23.2.3　示例320

23.3　本章小结321

第24章　历史问题诊断和现场故障分析322

24.1　故障分析和验证322

24.1.1　历史问题排查322

24.1.2　在线问题分析323

24.2　故障复现排查323

24.3　本章小结325

第25章　性能调优金字塔326

25.1　硬件和系统调优327

25.1.1　硬件优化327

25.1.2　系统优化327

25.2　MYSQL调优328

25.2.1　参数调优328

25.2.2　SQL/索引调优329

25.3　架构调优329

25.4　本章小结329

第26章　SQL语句执行慢真假难辨330

26.1　概述330

26.2　测试环境331

26.3　采集数据包331

26.3.1　采集应用服务器数据包331

26.3.2　采集数据库服务器数据包332

26.4　解析数据包333

26.4.1　使用WIRESHARK解析数据包333

26.4.2　使用TCPDUMP解析数据包335

26.5　本章小结337

第27章　如何避免三天两头换硬盘、内存、主板338

27.1　概述338

27.2　服务器标准化338

27.3　上线前烤机339

27.3.1　STRESS339

27.3.2　FIO341

27.3.3　数据库烤机341

27.4　本章小结341

第28章　每隔45天的MYSQL性能低谷342

28.1　存储知识小普及342

28.1.1　MYSQL存储系统要求342

28.1.2　存储系统软件层343

28.1.3　存储系统硬件层343

28.2　每隔45天的性能抖动344

28.3　影响I/O性能的三种RAID策略348

28.3.1　电池充放电348

28.3.2　巡读354

28.3.3　一致性检查356

28.4　本章小结358

第29章　MYSQL连接无法自动释放359

29.1　环境配置359

29.2　问题现象359

29.3　诊断分析359

29.4　解决方案362

29.5　本章小结362

第30章　查询MYSQL偶尔比较慢363

30.1　环境配置363

30.2　问题现象363

30.3　诊断分析363

30.4　选择VIP还是DNS访问MYSQL364

30.4.1　VIP之殇364

30.4.2　DNS之痛365

30.4.3　VIP切换还是DNS切换365

30.5　CPU节能模式365

30.6　本章小结366

第31章　MYSQL最多只允许214个连接367

31.1　环境配置367

31.2　故障现象367

31.3　故障分析367

31.4　解决方案370

31.5　MYSQL最大连接数为214的源码解析370

31.6　LINUX资源限制371

31.6.1　资源拆分和组合371

31.6.2　进程资源限制372

31.6.3　MYSQL内部资源限制374

31.7　本章小结374

第32章　MYSQL挂起诊断思路375

32.1　环境配置375

32.2　问题现象375

32.3　诊断分析376

32.4　挂起时先做什么377

32.5　总结377

第33章　硬件和系统调优378

33.1　硬件和系统调优概览378

33.1.1　I/O：MYSQL 80%的性能瓶颈所在378

33.2　CPU384

33.3　网络384

33.4　其他385

33.5　总结386

第34章 并发删除数据造成死锁387

34.1　现象描述387

34.2　如何阅读死锁日志388

34.3　表结构及操作步骤390

34.4　总结390

第35章 删除不存在的数据造成死锁391

35.1　现象描述391

35.2　问题分析392

35.3　问题扩展392

35.4　表结构及操作步骤393

35.5　总结393

第36章 插入意向锁死锁394

36.1　现象描述394

36.2　问题分析395

36.3　总结397

第37章 分页查询优化398

37.1　现象描述398

37.2　优化方案398

第38章 子查询优化——子查询转换为连接400

38.1　现象描述400

38.2　优化方案401

第39章 子查询优化——使用DELETE删除数据403

39.1　现象描述403

39.2　优化方案403

工 具 篇

第40章　硬件规格常用查看命令详解407

40.1　通用命令407

40.1.1　LSHW407

40.1.2　DMIDECODE414

40.1.3　DMESG419

40.2　CPU相关命令420

40.3　磁盘相关命令422

40.3.1　SMARTCTL422

40.3.2　LSSCSI424

40.4　网卡相关命令426

40.4.1　LSPCI426

40.4.2　ETHTOOL428

40.5　HCA卡相关命令430

第41章　系统负载常用查看命令详解433

41.1　TOP433

41.1.1　命令行选项433

41.1.2　交互式命令选项434

41.1.3　输出结果解读434

41.2　DSTAT436

41.2.1　命令行选项436

41.2.2　输出结果解读438

41.3　MPSTAT442

41.3.1　命令行选项443

41.3.2　输出结果解读443

41.4　SAR444

41.4.1　命令行选项444

41.4.2　输出结果解读446

41.5　VMSTAT453

41.5.1　命令行选项453

41.5.2　输出结果解读453

41.6　IOSTAT455

41.6.1　命令行选项455

41.6.2　输出结果解释456

41.7　FREE458

41.7.1　命令行选项459

41.7.2　输出结果解读459

41.8　IOTOP460

41.8.1 命令行选项461

41.8.2　交互式命令选项461

41.8.3　输出结果解读461

41.9　IFTOP463

41.9.1　命令行选项463

41.9.2　交互式命令选项463

41.9.3　输出结果解读464

41.10　IPERF465

41.10.1　命令行选项466

41.10.2　输出结果解读467

第42章　FIO存储性能压测469

42.1　安装FIO469

42.2　测试I/O性能470

42.2.1　随机写470

42.2.2　顺序写470

42.2.3　随机读471

42.2.4　顺序读471

42.2.5　混合随机读写471

42.2.6　FIO配置文件测试471

42.3　参数和结果详解472

42.3.1　关键参数解释472

42.3.2　配置文件473

42.3.3　结果解析475

42.4　FIO测试建议476

42.5　课外阅读476

第43章　HAMMERDB在线事务处理测试477

43.1　安装和配置HAMMERDB477

43.1.1　下载安装包477

43.1.2　安装HAMMERDB477

43.1.3　安装HAMMERDB GUI依赖包478

43.2　测试481

43.3　课外阅读492

第44章　SYSBENCH数据库压测工具493

44.1　安装SYSBENCH493

44.1.1　YUM安装493

44.1.2　RPM包安装493

44.1.3　编译安装494

44.1.4　验证安装是否成功494

44.2　测试案例494

44.2.1　造数495

44.2.2　数据库读写测试495

44.2.3　清理496

44.3　SYSBENCH 参数详解496

44.3.1　SYSBENCH命令语法496

44.3.2　OPTIONS496

44.3.3　TESTNAME500

44.3.4　COMMAND502

44.4　数据库测试输出信息详解502

44.4.1　输出结果概述503

44.4.2　版本及关键测试参数输出504

44.4.3　中间统计结果输出504

44.4.4　整体统计结果输出504

44.5　课外阅读505

第45章　MYSQLADMIN和INNOTOP工具详解506

45.1　MYSQLADMIN506

45.1.1　命令行选项506

45.1.2　实战演示511

45.2　INNOTOP513

45.2.1　安装INNOTOP513

45.2.2　命令行选项514

45.2.3　交互式选项515

45.2.4　实战演示516

第46章　利用PROMETHEUS+GRAFANA 搭建炫酷的MYSQL监控平台524

46.1　安装PROMETHEUS525

46.1.1　下载程序包525

46.1.2　解压缩程序包526

46.1.3　启动PROMETHEUS527

46.2　安装GRAFANA529

46.2.1　下载程序包529

46.2.2　解压缩程序包530

46.2.3　启动GRAFANA530

46.2.4　在GRAFANA中导入GRAFANA-DASHBOARDS531

46.3　监控节点部署534

46.3.1　添加主机监控534

46.3.2　添加MYSQL监控535

46.3.3　监控DASHBOARD切换536

第47章　PERCONA TOOLKIT常用工具详解538

47.1　PT-QUERY-DIGEST538

47.1.1　命令行选项538

47.1.2　实战演示550

47.2　PT-IOPROFILE558

47.2.1　命令行选项558

47.2.2　实战演示559

47.3　PT-INDEX-USAGE560

47.3.1　命令行选项561

47.3.2　实战演示563

47.4　PT-DUPLICATE-KEY-CHECKER566

47.4.1　命令行选项567

47.4.2　实战演示568

47.5　PT-MYSQL-SUMMARY/PT-SUMMARY571

47.5.1　PT-MYSQL-SUMMARY571

47.5.2　PT-SUMMARY578

47.6　PT-PMP584

47.6.1　命令行选项585

47.6.2　实战演示585

47.7　PT-STALK587

47.7.1　命令行选项588

47.7.2　实战演示591

47.8　PT-SIFT594

47.8.1　命令行选项595

47.8.2　实战演示595

第48章　MYSQL主流备份工具之MYSQLDUMP详解598

48.1　简介598

48.2　原理599

48.3　命令行选项601

48.3.1　连接选项601

48.3.2　文件选项602

48.3.3　DDL选项603

48.3.4　调试选项604

48.3.5　帮助选项604

48.3.6　国际化选项——与字符集相关的选项604

48.3.7　复制选项605

48.3.8　输出格式化选项607

48.3.9　过滤选项609

48.3.10 性能选项612

48.3.11　事务选项612

48.3.12　组合选项614

48.4　实战演示614

48.4.1　完全备份与恢复614

48.4.2　增量备份与恢复616

48.4.3　搭建主从复制架构616

48.4.4　克隆从库618

48.4.5　指定库表备份与恢复619

48.4.6　纯文本备份与恢复621

第49章　MYSQL主流备份工具之XTRABACKUP详解624

49.1　简介624

49.2　原理624

49.3　命令行选项633

49.4　实战演示644

49.4.1　完全备份与恢复644

49.4.2　增量备份与恢复645

49.4.3　基于时间点的恢复649

49.4.4　搭建主从复制架构657

49.4.5　克隆从库659

第50章　MYSQL主流备份工具之MYDUMPER详解662

50.1　简介662

50.2　原理662

50.3　命令行选项664

50.3.1　MYDUMPER664

50.3.2　MYLOADER666

50.4　实战演示667

50.4.1　安装MYDUMPER667

50.4.2　备份与恢复669

第51章　MYSQL主流闪回工具详解675

51.1　闪回工具科普675

51.2　BINLOG2SQL676

51.2.1　安装和使用要求676

51.2.2　命令行选项677

51.2.3　实战演示678

51.2.4　使用限制与注意事项690

51.3　MYFLASH690

51.3.1　安装MYFLASH691

51.3.2　命令行选项691

51.3.3　实战演示692