千金良方——MySQL性能优化金字塔法则
| 作者 |
| 李春 |
| 丛书名 |
| 出版社 |
| 电子工业出版社 |
| ISBN |
| 9787121375200 |
| 简要 |
| 简介 |
| 内容简介书籍计算机书籍 本书一共分为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初始化安装、简单安全加固 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 初始化MYSQL 6 1.2.8 启动MYSQL 7 1.3 简单安全加固 8 1.3.1 登录MYSQL 8 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.35 21 2.3.1 使用MYSQL_UPGRADE直接升级数据字典库 21 2.3.2 使用MYSQLDUMP逻辑备份数据 30 2.4 升级注意事项 38 第3章 MYSQL体系结构 41 3.1 快速安装MYSQL 41 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_SCHEMA 56 4.2 PERFORMANCE_SCHEMA使用快速入门 57 4.2.1 检查当前数据库版本是否支持 57 4.2.2 启用PERFORMANCE_SCHEMA 58 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 VARIABLES 69 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_SCHEMA 151 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 USER 177 12.2 DB 179 12.3 TABLES_PRIV 180 12.4 COLUMNS_PRIV 181 12.5 PROCS_PRIV 182 12.6 PROXIES_PRIV 182 第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_STATS 203 14.2.2 INNODB_INDEX_STATS 204 第15章 MYSQL系统库之复制信息表 206 15.1 复制信息表概述 206 15.2 复制信息表详解 207 15.2.1 SLAVE_MASTER_INFO 207 15.2.2 SLAVE_RELAY_LOG_INFO 209 15.2.3 SLAVE_WORKER_INFO 211 15.2.4 GTID_EXECUTED 215 15.2.5 NDB_BINLOG_INDEX 217 第16章 MYSQL系统库之日志记录表 218 16.1 日志信息概述 218 16.2 日志表详解 220 16.2.1 GENERAL_LOG 220 16.2.2 SLOW_LOG 223 第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 STRESS 339 27.3.2 FIO 341 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访问MYSQL 364 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 CPU 384 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 LSHW 407 40.1.2 DMIDECODE 414 40.1.3 DMESG 419 40.2 CPU相关命令 420 40.3 磁盘相关命令 422 40.3.1 SMARTCTL 422 40.3.2 LSSCSI 424 40.4 网卡相关命令 426 40.4.1 LSPCI 426 40.4.2 ETHTOOL 428 40.5 HCA卡相关命令 430 第41章 系统负载常用查看命令详解 433 41.1 TOP 433 41.1.1 命令行选项 433 41.1.2 交互式命令选项 434 41.1.3 输出结果解读 434 41.2 DSTAT 436 41.2.1 命令行选项 436 41.2.2 输出结果解读 438 41.3 MPSTAT 442 41.3.1 命令行选项 443 41.3.2 输出结果解读 443 41.4 SAR 444 41.4.1 命令行选项 444 41.4.2 输出结果解读 446 41.5 VMSTAT 453 41.5.1 命令行选项 453 41.5.2 输出结果解读 453 41.6 IOSTAT 455 41.6.1 命令行选项 455 41.6.2 输出结果解释 456 41.7 FREE 458 41.7.1 命令行选项 459 41.7.2 输出结果解读 459 41.8 IOTOP 460 41.8.1 命令行选项 461 41.8.2 交互式命令选项 461 41.8.3 输出结果解读 461 41.9 IFTOP 463 41.9.1 命令行选项 463 41.9.2 交互式命令选项 463 41.9.3 输出结果解读 464 41.10 IPERF 465 41.10.1 命令行选项 466 41.10.2 输出结果解读 467 第42章 FIO存储性能压测 469 42.1 安装FIO 469 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 安装和配置HAMMERDB 477 43.1.1 下载安装包 477 43.1.2 安装HAMMERDB 477 43.1.3 安装HAMMERDB GUI依赖包 478 43.2 测试 481 43.3 课外阅读 492 第44章 SYSBENCH数据库压测工具 493 44.1 安装SYSBENCH 493 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 OPTIONS 496 44.3.3 TESTNAME 500 44.3.4 COMMAND 502 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 MYSQLADMIN 506 45.1.1 命令行选项 506 45.1.2 实战演示 511 45.2 INNOTOP 513 45.2.1 安装INNOTOP 513 45.2.2 命令行选项 514 45.2.3 交互式选项 515 45.2.4 实战演示 516 第46章 利用PROMETHEUS+GRAFANA 搭建炫酷的MYSQL监控平台 524 46.1 安装PROMETHEUS 525 46.1.1 下载程序包 525 46.1.2 解压缩程序包 526 46.1.3 启动PROMETHEUS 527 46.2 安装GRAFANA 529 46.2.1 下载程序包 529 46.2.2 解压缩程序包 530 46.2.3 启动GRAFANA 530 46.2.4 在GRAFANA中导入GRAFANA-DASHBOARDS 531 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-DIGEST 538 47.1.1 命令行选项 538 47.1.2 实战演示 550 47.2 PT-IOPROFILE 558 47.2.1 命令行选项 558 47.2.2 实战演示 559 47.3 PT-INDEX-USAGE 560 47.3.1 命令行选项 561 47.3.2 实战演示 563 47.4 PT-DUPLICATE-KEY-CHECKER 566 47.4.1 命令行选项 567 47.4.2 实战演示 568 47.5 PT-MYSQL-SUMMARY/PT-SUMMARY 571 47.5.1 PT-MYSQL-SUMMARY 571 47.5.2 PT-SUMMARY 578 47.6 PT-PMP 584 47.6.1 命令行选项 585 47.6.2 实战演示 585 47.7 PT-STALK 587 47.7.1 命令行选项 588 47.7.2 实战演示 591 47.8 PT-SIFT 594 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 MYDUMPER 664 50.3.2 MYLOADER 666 50.4 实战演示 667 50.4.1 安装MYDUMPER 667 50.4.2 备份与恢复 669 第51章 MYSQL主流闪回工具详解 675 51.1 闪回工具科普 675 51.2 BINLOG2SQL 676 51.2.1 安装和使用要求 676 51.2.2 命令行选项 677 51.2.3 实战演示 678 51.2.4 使用限制与注意事项 690 51.3 MYFLASH 690 51.3.1 安装MYFLASH 691 51.3.2 命令行选项 691 51.3.3 实战演示 692 |
有问题微信扫码联系