剑指MySQL——架构、调优与运维电子书

内容简介

前言

第1章 Linux平台下MySQL的安装与使用

1.1 MySQL概述

1.1.1 MySQL简介

1.1.2 MySQL的发展历程

1.1.3 MySQL高手是如何练就的

1.2 Linux平台下MySQL的安装

1.2.1 MySQL的四大版本

1.2.2 下载MySQL指定版本

1.2.3 Linux平台下安装MySQL的三种方式

1.2.4 安装MySQL前的必要操作

1.2.5 CentOS 7下MySQL的安装过程

1.3 卸载MySQL

1.4 登录MySQL

1.4.1 首次登录

1.4.2 修改密码

1.4.3 设置远程登录

1.5 MySQL 8.0的密码强度评估

1.5.1 MySQL不同版本的密码设置

1.5.2 MySQL 8.0的密码安全策略

1.6 字符集的设置

1.6.1 查看字符集

1.6.2 查看校对规则

1.6.3 各级别的字符集和校对规则

1.6.4 MySQL 5.7和MySQL 8.0中数据库级别默认字符集的变化

1.7 SQL语句中的字母大小写规范

1.7.1 Windows和Linux平台下字母大小写规范的区别

1.7.2 Linux平台下字母大小写规范设置

1.8 sql_mode

1.8.1 sql_mode简介

1.8.2 宽松模式与严格模式对比

1.8.3 模式的查看和设置

1.9 小结

第2章 MySQL的目录结构

2.1 MySQL的主要目录

2.1.1 MySQL的数据目录

2.1.2 MySQL的数据库命令目录

2.2 数据库和文件系统的关系

2.2.1 查看系统数据库

2.2.2 数据库在文件系统中的表示

2.2.3 表在文件系统中的表示

2.2.4 视图在文件系统中的表示

2.2.5 其他文件

2.3 小结

第3章 用户与权限管理

3.1 权限表

3.1.1 user表

3.1.2 db表

3.1.3 tables_priv表和columns_priv表

3.1.4 procs_priv表

3.2 用户管理

3.2.1 登录MySQL服务器

3.2.2 创建用户

3.2.3 删除用户

3.2.4 修改用户信息

3.2.5 修改当前用户的密码

3.2.6 修改其他普通用户的密码

3.2.7 MySQL 8.0密码管理

3.3 权限管理

3.3.1 权限列表

3.3.2 授予权限的原则

3.3.3 授予权限的方式

3.3.4 查看权限

3.3.5 回收权限

3.4 访问控制

3.4.1 连接核实阶段

3.4.2 请求核实阶段

3.5 角色管理

3.5.1 角色概述

3.5.2 创建角色

3.5.3 给角色授权

3.5.4 查看角色拥有的权限

3.5.5 回收角色拥有的权限

3.5.6 删除角色

3.5.7 赋予用户角色

3.5.8 激活角色

3.5.9 撤销用户被赋予的角色

3.5.10 设置强制角色

3.6 配置文件的使用

3.6.1 配置文件的格式

3.6.2 启动命令与选项组

3.6.3 特定MySQL版本的专用选项组

3.6.4 同一配置文件中多个选项组的优先级

3.6.5 命令行和配置文件中启动选项的区别

3.7 系统变量

3.7.1 系统变量简介

3.7.2 查看系统变量

3.7.3 通过启动选项设置系统变量

3.7.4 在服务器端程序运行过程中设置系统变量

3.7.5 启动选项和系统变量之间的关系

3.8 小结

第4章 逻辑架构

4.1 逻辑架构剖析

4.1.1 MySQL的逻辑架构

4.1.2 客户端连接器

4.1.3 连接层

4.1.4 服务层

4.1.5 存储引擎层

4.1.6 存储层

4.2 SQL语句的执行流程

4.3 MySQL 5.7中的查询缓存设置

4.4 数据库缓冲池

4.4.1 缓冲池和查询缓存

4.4.2 查看缓冲池的大小

4.4.3 将数据加载到缓冲池中

4.4.4 缓冲池中的free链表

4.4.5 缓冲池中的LRU链表

4.4.6 缓冲池中的flush链表

4.5 小结

第5章 存储引擎

5.1 存储引擎的相关操作

5.1.1 查看存储引擎

5.1.2 设置系统默认的存储引擎

5.1.3 设置表的存储引擎

5.2 主要存储引擎介绍

5.2.1 InnoDB存储引擎：事务型存储引擎

5.2.2 MyISAM存储引擎：主要的非事务处理存储引擎

5.2.3 ARCHIVE存储引擎：数据存档

5.2.4 BLACKHOLE存储引擎：丢弃写操作

5.2.5 CSV存储引擎：存储CSV文件

5.2.6 MEMORY存储引擎：内存表

5.2.7 其他存储引擎

5.2.8 常用存储引擎对比

5.3 小结

第6章 InnoDB存储引擎中的数据存储结构

6.1 数据库的存储架构

6.2 InnoDB存储引擎中的行格式

6.2.1 查看和修改行格式

6.2.2 完整的记录信息

6.2.3 行溢出

6.2.4 REDUNDANT行格式

6.2.5 COMPACT行格式

6.2.6 DYNAMIC和COMPRESSED行格式

6.3 页的结构

6.3.1 页的组成部分

6.3.2 从页的角度看B+树是如何进行查询的

6.4 区和段

6.4.1 区

6.4.2 段

6.5 表空间

6.6 小结

第7章 索引

7.1 索引概述

7.2 合理选择索引的数据结构

7.2.1 二叉搜索树

7.2.2 AVL树

7.2.3 B树

7.2.4 B+树

7.2.5 哈希结构

7.3 索引分类

7.3.1 按功能逻辑分类

7.3.2 按物理实现方式分类

7.3.3 按索引字段个数分类

7.4 索引的创建与删除

7.4.1 创建索引

7.4.2 删除索引

7.5 InnoDB和MyISAM存储引擎中的索引方案

7.5.1 InnoDB存储引擎中的索引方案

7.5.2 MyISAM存储引擎中的索引方案

7.5.3 InnoDB和MyISAM存储引擎中的索引方案对比

7.6 MySQL 8.0的新特性

7.6.1 支持降序索引

7.6.2 支持隐藏索引

7.7 适合创建索引的场景

7.7.1 数据准备

7.7.2 查询操作的条件字段

7.7.3 分组和排序的字段

7.7.4 更新和删除操作的条件字段

7.7.5 去重的字段

7.7.6 多表连接查询

7.7.7 数据类型小的列

7.8 不适合创建索引的场景

7.8.1 WHERE条件用不到的字段

7.8.2 数据量小的表

7.8.3 有大量重复数据的字段

7.8.4 索引不要冗余或重复创建

7.8.5 其他不适合创建索引的场景

7.9 小结

第8章 性能分析工具的使用

8.1 数据库性能调优步骤

8.2 查看系统状态信息

8.3 查看SQL查询成本

8.4 定位执行时间长的SQL语句

8.4.1 开启慢查询日志

8.4.2 测试及分析

8.4.3 慢查询日志分析工具

8.4.4 关闭慢查询日志

8.4.5 删除慢查询日志

8.5 查看SQL语句的具体查询成本

8.6 分析查询语句：EXPLAIN

8.6.1 说明

8.6.2 EXPLAIN结果之id

8.6.3 EXPLAIN结果之select_type

8.6.4 EXPLAIN结果之table

8.6.5 EXPLAIN结果之partitions

8.6.6 EXPLAIN结果之type

8.6.7 EXPLAIN结果之possible_keys和key

8.6.8 EXPLAIN结果之key_len

8.6.9 EXPLAIN结果之ref

8.6.10 EXPLAIN结果之rows

8.6.11 EXPLAIN结果之filtered

8.6.12 EXPLAIN结果之Extra

8.6.13 JSON格式的执行计划

8.6.14 执行计划的扩展信息

8.7 小结

第9章 索引优化

9.1 数据准备

9.2 索引优化原则

9.2.1 全值匹配法则

9.2.2 最左前缀法则

9.2.3 优先考虑覆盖索引

9.3 索引失效的场景

9.3.1 查询条件中包含函数、计算导致索引失效

9.3.2 范围查询条件右边的列不能被使用导致索引失效

9.3.3 不等值查询导致索引失效

9.3.4 判空条件对索引的影响

9.3.5 LIKE以通配符“％”开头导致索引失效

9.3.6 数据类型转换导致索引失效

9.3.7 OR关键字导致索引失效

9.4 关联查询优化

9.4.1 数据准备

9.4.2 采用左连接：LEFT JOIN

9.4.3 采用内连接：INNER JOIN

9.4.4 JOIN语句的原理

9.5 排序优化

9.5.1 ORDER BY的索引规则

9.5.2 排序方式比较

9.5.3 影响排序效率的两个系统变量

9.6 索引下推

9.6.1 索引下推概述

9.6.2 索引下推案例

9.7 B+树索引的优化

9.7.1 自适应哈希索引

9.7.2 覆盖索引

9.8 其他索引优化场景

9.8.1 子查询优化

9.8.2 分页查询优化

9.8.3 索引提示

9.8.4 前缀索引

9.9 常用SQL编写建议

9.10 小结

第10章 数据库的设计规范

10.1 范式

10.1.1 范式简介

10.1.2 范式分类

10.1.3 数据表中的键

10.1.4 第一范式

10.1.5 第二范式

10.1.6 第三范式

10.1.7 巴斯-科德范式

10.1.8 第四范式

10.2 反范式化

10.2.1 反范式化概述

10.2.2 反范式化应用举例

10.2.3 反范式化的使用建议

10.3 ER模型

10.3.1 ER模型中的要素

10.3.2 ER模型中实体的属性

10.3.3 把ER模型转换为数据表

10.3.4 把实体转换为数据表

10.3.5 把多对多的关系转换为数据表

10.3.6 通过外键来表达一对多的关系

10.3.7 把属性转换为表中的字段

10.4 数据库对象的设计规范

10.4.1 库的设计规范

10.4.2 表的设计规范

10.4.3 索引的设计规范

10.5 PowerDesigner的使用

10.6 小结

第11章 数据库调优

11.1 数据库调优的措施

11.1.1 如何解决定位问题

11.1.2 数据库调优的维度和步骤

11.2 优化MySQL服务器

11.2.1 优化服务器硬件

11.2.2 优化MySQL服务的配置参数

11.3 优化数据库结构

11.3.1 拆分表：冷、热数据分离

11.3.2 增加中间表或冗余字段

11.3.3 优化字段的数据类型

11.3.4 优化插入记录的速度

11.3.5 使用非空约束

11.3.6 分析表、检查表和优化表

11.4 大表优化

11.4.1 读写分离

11.4.2 垂直拆分

11.4.3 水平拆分

11.5 其他调优策略

11.5.1 服务器语句超时处理

11.5.2 创建全局通用表空间

11.5.3 临时表性能优化

11.6 小结

第12章 数据库事务

12.1 事务概述

12.1.1 事务的ACID特性

12.1.2 事务的状态

12.2 事务的基本使用

12.2.1 数据准备

12.2.2 事务的基本语法

12.2.3 隐式提交数据库事务的情况

12.2.4 事务提交和回滚案例

12.2.5 不支持事务的表无法回滚事务

12.2.6 事务保存点

12.3 事务隔离级别

12.3.1 数据库并发问题

12.3.2 事务中的4种隔离级别

12.3.3 设置隔离级别

12.3.4 不同隔离级别下的并发情况

12.4 小结

第13章 redo日志和undo日志

13.1 为什么需要日志维护事务

13.2 redo日志

13.2.1 redo日志的优势

13.2.2 redo日志文件组

13.2.3 checkpoint

13.2.4 Log Sequence Number

13.2.5 redo日志缓存

13.2.6 redo日志刷盘策略

13.2.7 redo日志相关参数

13.3 undo日志

13.3.1 为什么需要undo日志

13.3.2 undo日志的作用

13.3.3 undo日志的存储机制

13.3.4 undo日志如何回滚事务

13.3.5 undo日志相关参数

13.4 小结

第14章 锁

14.1 锁概述

14.1.1 并发事务访问相同记录

14.1.2 锁的分类

14.2 共享锁和独占锁

14.3 表级锁

14.3.1 表级别的共享锁和独占锁

14.3.2 意向锁

14.3.3 自增锁

14.3.4 元数据锁

14.4 InnoDB存储引擎中的行级锁

14.4.1 记录锁

14.4.2 间隙锁

14.4.3 临键锁

14.4.4 插入意向锁

14.5 悲观锁和乐观锁

14.5.1 悲观锁

14.5.2 乐观锁

14.5.3 悲观锁和乐观锁的适用场景

14.6 隐式锁和显式锁

14.6.1 隐式锁

14.6.2 显式锁

14.7 死锁

14.8 锁监控

14.9 MySQL加锁案例解析

14.9.1 案例一：唯一索引等值查询

14.9.2 案例二：非唯一索引等值查询

14.9.3 案例三：主键索引范围查询

14.9.4 案例四：非唯一索引上存在等值的情况

14.9.5 案例五：LIMIT语句加锁

14.10 小结

第15章 多版本并发控制

15.1 什么是MVCC

15.1.1 快照读

15.1.2 当前读

15.2 版本链

15.3 ReadView

15.3.1 什么是ReadView

15.3.2 ReadView的设计思路

15.3.3 ReadView规则

15.3.4 MVCC整体操作流程

15.4 不同隔离级别下的ReadView

15.4.1 READ COMMITTED隔离级别下的ReadView

15.4.2 REPEATABLE READ隔离级别下的ReadView

15.5 小结

第16章 其他数据库日志

16.1 MySQL支持的日志

16.2 通用查询日志

16.2.1 开启/关闭通用查询日志功能

16.2.2 查看通用查询日志

16.2.3 删除（刷新）通用查询日志

16.3 错误日志

16.3.1 开启错误日志功能

16.3.2 查看错误日志

16.3.3 删除（刷新）错误日志

16.4 二进制日志

16.4.1 开启/关闭二进制日志功能

16.4.2 二进制日志参数设置

16.4.3 查看二进制日志

16.4.4 删除（刷新）二进制日志

16.4.5 使用二进制日志恢复数据

16.4.6 二进制日志的写入机制

16.4.7 两阶段提交

16.5 中继日志

16.6 数据定义语句日志

16.7 小结

第17章 主从复制

17.1 主从复制概述

17.2 主从复制的原理

17.3 主从复制架构的搭建

17.3.1 准备工作

17.3.2 修改配置文件

17.3.3 主机：创建账户并授权

17.3.4 从机：配置需要复制的主机信息

17.3.5 测试和停止主从复制

17.3.6 双主双从架构

17.3.7 二进制日志格式在主从复制架构中的优缺点

17.4 数据一致性

17.4.1 数据同步延迟问题

17.4.2 如何解决主从复制中数据不一致的问题

17.5 数据库中间件

17.6 小结

第18章 数据库备份与恢复

18.1 物理备份和逻辑备份的概念

18.2 逻辑备份之mysqldump命令

18.2.1 备份单个数据库

18.2.2 备份全部数据库

18.2.3 备份多个数据库

18.2.4 备份部分表

18.2.5 备份单表中的部分数据

18.2.6 排除某些表的备份

18.2.7 只备份表结构或只备份数据

18.2.8 备份中包含函数、存储过程或事件

18.2.9 以事务的形式备份

18.2.10 mysqldump命令的常用参数

18.3 逻辑恢复之mysql命令

18.3.1 从单库备份中恢复单库

18.3.2 全量备份恢复

18.3.3 从全量备份中恢复单库

18.3.4 从单库备份中恢复单表

18.4 MyISAM存储引擎类型的表的物理备份

18.5 MyISAM存储引擎类型的表的物理备份的恢复流程

18.6 InnoDB存储引擎类型的表的物理导出、导入

18.7 导出不同格式的文件

18.7.1 使用SELECT…INTO OUTFILE语句导出文件

18.7.2 使用mysqldump命令导出文件

18.7.3 使用mysql命令导出文件

18.8 导入文本文件

18.8.1 使用LOAD DATA INFILE语句导入文本文件

18.8.2 使用mysqlimport命令导入文本文件

18.9 数据迁移

18.9.1 数据迁移概述

18.9.2 迁移方案和注意事项

18.10 误删数据的预防方案和恢复方案

18.11 MySQL常用命令

18.11.1 mysql

18.11.2 mysqladmin

18.11.3 mysqlbinlog

18.11.4 mysqldump

18.11.5 mysqlshow

18.12 小结