MySQL8查询性能优化

MySQL8查询性能优化"

作者:[澳]杰斯帕·威斯堡·克罗(JesperWisborgKrogh)著史跃东杨欣殷海英译
ISBN:9787302583912
定价:¥158
字数:千字
页数:
出版时间:2021.08.01
开本:
版次:1-1
装帧:
出版社:清华大学出版社
简介

主要内容

● 监控性能,找出效果不佳的查询

● 选取要优化的查询,**限度地提高收益

● 使用EXPLAIN ANALYZE和Visual Explain等工具来分析查询

● 借助多种策略改进慢查询

● 正确使用索引和直方图,创建快速的执行计划

● 了解并分析锁,从而解决争用问题,提升系统吞吐量

前言

前    言

MySQL性能优化是一个非常大的主题,人们通常需要花费数年时间才能掌握。《MySQL 8查询性能优化》的篇幅就证明了这一点,即使只专注与查询相关的优化主题,篇幅显然就不小了。一般而言,没有什么简单方法可以轻松地提升性能,恰恰相反,要找到相关的解决方法,你不仅需要了解MySQL内部各部分之间的关系,还需要了解相关技术栈其他部分的内容。如果你觉得单单在性能优化方面就很难入门,那么第一步你就跨不过去。但是,请不要对性能优化感到失望,与其他技巧一样,也可以通过实践逐步成为性能优化高手。

撰写《MySQL 8查询性能优化》的目的在于,使你能在MySQL性能优化方面登堂入室,从而熟练掌握如何提升在MySQL实例上运行的那些查询的性能。如前所述,这没有什么简单秘方,最佳办法就是学习并了解性能优化过程中涉及的各个组件。这也是《MySQL 8查询性能优化》的主要内容,当然,我们也提供了如何找到相关信息,以及如何执行一些常见任务的示例。另外,《MySQL 8查询性能优化》的内容仅限于对MySQL本身的探讨,因此关于操作系统、文件系统以及硬件级别的内容,就相对有限了。

众所周知,MySQL以对各种存储引擎的支持而闻名。但除了对内部临时表相关的探讨外,《MySQL 8查询性能优化》只介绍InnoDB存储引擎。而对于MySQL的版本,则只考虑MySQL 8。也就是说,《MySQL 8查询性能优化》中的大部分讨论内容虽然也适用于旧版本的MySQL,但通常也只是为了说明MySQL 8中的新特性与旧版本的不同之处罢了。

《MySQL 8查询性能优化》面向的读者 

《MySQL 8查询性能优化》是为那些具有丰富的MySQL数据库使用经验,并希望将知识扩展到查询性能优化领域的开发人员和数据库管理员而编写的。当然,在阅读《MySQL 8查询性能优化》之前,你不需要具备性能优化的相关经验。

在撰写《MySQL 8查询性能优化》的过程中,作者尝试添加了尽可能多的示例代码及其输出结果。当然,有些示例很短,有些则很长。但无论哪种情况,作者都希望读者能够跟上并重现这些示例的结果。同时请记住,由于实际环境的差异(当然,这种差异和索引统计信息一样明确),示例结果可能会取决于在示例之前,相关的表和数据的获取方式。换句话说,即使读者完成了所有工作,得到的结果仍然可能与《MySQL 8查询性能优化》中的结果不同。尤其是涉及索引统计信息以及与计时等相关的数字时。

读者可扫封底的二维码,下载《MySQL 8查询性能优化》的示例代码。

《MySQL 8查询性能优化》结构

《MySQL 8查询性能优化》分为6部分,共计27章。在撰写《MySQL 8查询性能优化》时,作者试图让每章的内容都保持相对独立,以便读者将《MySQL 8查询性能优化》用作参考书。当然,这样做的缺点之一是有时会重复出现某些内容。例如第18章介绍了锁的理论方面的一些知识,以及如何对锁进行监控;而第22章则提供锁争用的一些示例。因此,第22章很自然会借鉴第18章中的部分信息,因此出现了一些内容上的重复。这是一个有意识的行为,作者希望各位读者在阅读《MySQL 8查询性能优化》的过程中可以减少翻页的次数,尽快找到所需内容。

在阅读过程中,《MySQL 8查询性能优化》的6部分将引导你逐步完成性能优化主题的相关探讨。我们先从一些基本的背景知识开始,然后给出面向问题的解决方案。第Ⅰ部分将探讨相关的方法论、基准以及测试数据。第Ⅱ部分重点介绍各种信息来源,如performance库等。第Ⅲ部分介绍《MySQL 8查询性能优化》将用到的各种工具,如MySQL shell。第Ⅳ部分则提供后面两部分将用到的理论知识。第Ⅴ部分侧重分析查询、事务以及锁。第Ⅵ部分则探讨如何通过配置、查询优化、复制以及缓存等技术来提升性能。某些情况下,有些内容的编排可能较特殊,例如,所有关于复制的内容都包含在单独一章中(即第26章)。

第Ⅰ部分  入门

第Ⅰ部分介绍MySQL查询性能优化的相关概念,包括一些高级注意事项等。其中一些并非MySQL所独有(不过也是在MySQL上下文中进行探讨的)。第Ⅰ部分包含4章。

第1章“MySQL性能优化”——该章涵盖MySQL性能优化的一些高级概念,例如考虑整个堆栈和查询生命周期的重要性等。

第2章“查询优化方法论”——以有效方式解决性能问题至关重要。该章介绍有效工作的方法论,并强调积极工作的重要性。

第3章“使用Sysbench进行基准测试”——通常,我们需要使用基准测试来检验更改效果。该章将简要介绍基准测试,并专门探讨Sysbench工具,也包括如何创建自定义基准测试等内容。

第4章 “测试数据”——列出《MySQL 8查询性能优化》主要使用的一些标准测试数据库。

第Ⅱ部分  信息来源

MySQL会通过一些信息来源提供有关性能的信息。在该部分,将介绍performance库、sys库、information库以及SHOW语句。虽然在该部分中使用这些信息来源的例子相对较少,但在《MySQL 8查询性能优化》其他部分,则广泛使用了这四个信息来源。如果你对这些信息来源还不太熟悉,我们强烈建议你详细阅读该部分中的各个章节。此外,该部分还包含慢查询日志的相关内容。第Ⅱ部分共包含5章。

第5章“performance库”——顾名思义,MySQL中与性能相关的信息的主要来源是performance库。该章介绍相关的术语、基本概念、组织方式以及配置信息。

第6章“sys库”——通过存储过程和函数中的预定义视图和工具,sys库提供了各种报告信息。该章对sys库各种可用的特性进行了概述。

第7章“information库”——如果想获得关于MySQL和数据库的元数据信息,就需要查看information库。该库还包含用于性能优化的重要信息,例如关于索引、索引统计以及直方图等信息。该章将概述sys库中可用的视图。

第8章“SHOW 语句”——这是获取信息的最古老方法,可通过它获得从执行查询到库的各级别信息。该章将SHOW语句与information库和performance库相关联,并在某种程度上更详细地介绍SHOW语句。而SHOW语句的某些内容,在information库和performance库中是没有对应内容的。

第9章“慢查询日志”——查找慢查询的传统方法,就是将其记录到慢查询日志中。该章将介绍如何配置慢查询日志,如何读取日志事件,以及如何使用mysqldump这一实用工具对事件进行聚合等。

第Ⅲ部分  工具

MySQL提供一些在执行日常任务以及特定任务时非常有用的工具。该部分涵盖与监控和简单查询执行相关的三种工具。《MySQL 8查询性能优化》将Oracle专用的MySQL监控解决方案作为监控示例。即使你当前正在使用其他监控解决方案,也建议你研究一下这个示例。因为这些不同的解决方案之间往往有一些重叠之处。此外,《MySQL 8查询性能优化》其余部分也广泛使用了这三种工具。该部分包含3章。

第10章“MySQL Enterprise Monitor”——监控是保证数据库稳定运行且性能良好的最重要内容之一。该章将介绍MySQL Enterprise Monitor(MEM),并说明如何安装试用版本,以及如何进行导航和使用图形化用户界面等。

第11章“MySQL Workbench”——MySQL通过MySQL Workbench为用户提供图形化界面。该章将介绍如何安装和使用这一工具。在《MySQL 8查询性能优化》中,MySQL Workbench对创建查询执行计划的可视化图形(称为Visual Explain,可视化解释)至关重要。

第12章“MySQL shell”——Oracle为MySQL推出的最新工具之一就是MySQL shell。它是第二代的命令行客户端,支持在SQL、Python以及JavaScript模式下执行代码。该章将使你快速了解这一工具,并介绍该工具对外部代码模块的支持,分析该工具的报告基础结构,以及如何创建自定义模块、报告和插件等。

第Ⅳ部分  方案考量与查询优化器

在该部分中,介绍内容的节奏稍有改变。重点从与方案、查询优化器和锁相关的主题,逐步转移到性能优化的主题上。该部分包含6章。

第13章“数据类型”——在关系数据库中,每列都有各自的数据类型。数据类型定义了各列能够存储的值,两个值进行比较时所遵循的规则,以及数据的存储方式等。该章介绍MySQL中可用的各种数据类型,并提供应该使用何种数据类型的指导信息。

第14章“索引”——索引用于查找数据。而良好的索引可以极大地提升查询的性能。该章介绍索引的概念、关于索引的注意事项、索引类型以及索引的特性等。此外,介绍了InnoDB如何使用索引,以及应该使用何种索引策略等。

第15章“索引统计信息”——当优化器需要确定索引的有用程度以及与索引值上的条件相匹配的行数时,就需要关于索引中的数据的相关信息,即索引统计信息。该章介绍索引统计信息在MySQL中的工作方式,以及如何对其进行配置、监控和更新。

第16章“直方图”——如果希望优化器知道给定列中值出现的频率,则需要创建直方图。这是MySQL 8中新添加的特性。该章将介绍如何使用直方图,其内部结构如何,以及如何查询直方图的元数据和统计信息等。

第17章“查询优化器”——在执行查询时,优化器会决定查询的执行方式。该章将介绍优化器要完成的任务、使用的联接算法、联接优化、优化器相关配置以及资源组等内容。

第18章“锁原理与监控”——最容易给人带来挫败感的问题之一就是锁争用。该章首先说明数据库为何需要锁、锁的访问级别以及锁的类型(粒度);然后介绍在无法获得锁时,如何减少锁争用,以及可在何处找到锁相关的信息。

第Ⅴ部分  查询分析

有了第Ⅳ部分的信息,现在就可对查询进行分析了。这包括如何查找查询,从而进行下一步的分析,然后使用EXPLAIN或performance库分析等。当你有两个或者两个以上的查询来争用相同的锁时,还需要考虑事务是如何工作的,并对锁争用问题进行调查。该部分包含4章。

第19章“查找待优化的查询”——无论是将其作为日常运维的一部分,还是在紧急状况下,你都需要查找那些需要分析和优化的查询。该章将介绍如何使用performance库、sys库、MySQL Workbench、监控解决方案以及慢查询日志等工具来查找那些值得研究的查询。

第20章“分析查询”——有了候选查询后,就需要分析为何执行速度慢或给系统造成如此大的影响。这里用到的主要工具是EXPLAIN,该命令可提供有关优化器选择的查询计划的相关信息。如何使用EXPLAIN生成和理解查询(包括示例)计划是该章的重点。也可使用优化器跟踪程序来获取有关优化器如何选择某一查询的更多信息。分析查询的另一种方法是使用performance库和sys库将查询分解为较小的部分。

第21章“事务”——InnoDB将所有执行操作视作事务,并且事务是一个极为重要的概念。正确使用事务可以确保原子性、一致性和隔离性。但是,事务也可能导致严重的性能和锁争用问题。该章探讨事务为何成为问题,以及如何分析事务。

第22章“诊断锁争用”——该章介绍了四种锁争用的场景(刷新锁、元数据锁、记录锁以及死锁),并探讨这四种锁的症状、形成原因、重建场景的方式、调查、解决方案以及预防方式等。

第Ⅵ部分  提升查询性能

现在,你已经找到有问题的查询,并对其进行分析,包括事务情况,以了解表现不佳的原因。但是,你应该如何改善查询呢?该部分介绍其他章节未涵盖的重要配置选项,以及如何改变查询计划、改变方案等,同时包含批量加载、复制以及缓存等相关知识。该部分包含5章。

第23章“配置”——MySQL在执行查询时需要系统资源。该章将介绍配置这些资源的最佳实践,以及其他讨论中未曾介绍的一些重要配置选项。同时对InnoDB中的数据生命周期做了概述,将其作为探讨InnoDB配置的背景知识。

第24章“改变查询计划”——尽管优化器通常在找到最佳查询执行计划方面表现不错,但你依然需要不时为其提供帮助。例如,可能是因为没有索引,或现有索引无法使用等,导致全表扫描的出现。你可能还想提高索引的使用率,或者可能需要重写某个复杂条件甚至整个查询等。该章将仔细介绍这些内容,同时说明了如何使用SKIP LOCKED子句来实现队列系统。

第25章“DDL与批量数据加载”——在执行方案更改,或将大数据集加载到系统中时, MySQL需要完成大量工作。该章将探讨如何提升这类任务的性能,包括使用MySQL shell的并行数据加载特性等。该章也包含关于数据加载注意事项的内容,这通常也适用于数据修改操作。此外,我们也显示了顺序插入和随机插入之间的不同之处。在完成此探讨后,我们就知道选择主键对性能来说意味着什么。

第26章“复制”——在实例之间进行数据复制是MySQL的一项颇为流行的特性。从性能的角度看,复制包含两方面的内容:首先,你需要确保复制操作性能良好,此外,可通过复制来提升系统性能。该章探讨这两方面的内容,介绍用于监控复制的performance库表。

第27章“缓存”——提升查询性能的终极方法是根本不执行查询,或至少避免执行某部分查询。该章将探讨如何使用缓存表来降低查询的复杂性,以及如何使用Memcached、MySQL InnoDB Memcached插件和ProxySQL来避免查询的完全执行。

下载示例代码和彩色图片

《MySQL 8查询性能优化》正文中,有些地方会提到图中标记、箭头或区域的颜色。《MySQL 8查询性能优化》是黑白印刷,无法显示彩色。读者可在实际界面中查看确切的颜色。另外,可扫描封底二维码,下载示例代码和彩色图片。

目录

目    录

 

第Ⅰ部分  入门

第1章  MySQL性能优化   2

1.1  通盘考虑   2

1.2  监控   3

1.3  查询的生命周期   4

1.4  本章小结   5

第2章  查询优化方法论   6

2.1  综述   6

2.2  核实问题   7

2.3  确定原因   8

2.4  确定解决方案   8

2.5  实施解决方案   8

2.6  主动工作   10

2.7  本章小结   11

第3章  使用Sysbench进行基准测试   12

3.1  最佳实践   12

3.2  标准TPC基准测试   14

3.3  通用的基准测试工具   14

3.4  安装Sysbench   15

3.5  执行基准测试   20

3.6  创建自定义基准测试   25

3.6.1  自定义脚本概述   25

3.6.2  定义选项   27

3.6.3  run命令   27

3.6.4  prepare命令   29

3.6.5  cleanup命令   31

3.6.6  注册命令   31

3.7  本章小结   32

第4章  测试数据   34

4.1  下载示例数据库   34

4.2  world数据库   35

4.2.1  方案   35

4.2.2  安装   36

4.3  world_x数据库   36

4.3.1  方案   36

4.3.2  安装   36

4.4  sakila数据库   37

4.4.1  方案   37

4.4.2  安装   40

4.5  employees数据库   41

4.5.1  方案   41

4.5.2  安装   43

4.6  其他数据库   44

4.7  本章小结   44

第Ⅱ部分  信息来源

第5章  performance库   46

5.1  术语   46

5.2  线程   47

5.3  instrument   50

5.4  消费者   51

5.5  事件   53

5.5.1  事件类型   53

5.5.2  事件范围   53

5.5.3  事件嵌套   54

5.5.4  事件属性   55

5.6  Actor与对象   56

5.7  摘要   56

5.8  表类型   57

5.9  动态配置   58 

5.10  本章小结   59

第6章  sys库   61

6.1  sys库配置   62

6.2  格式化函数   64

6.3  视图   65

6.4  辅助函数与过程   66

6.5  本章小结   67

第7章  information库   68

7.1  何为information库   68

7.2  权限   69

7.3  视图   69

7.3.1  系统信息   69

7.3.2  方案信息   70

7.3.3  性能信息   74

7.3.4  权限信息   77

7.4  索引统计数据缓存   78

7.5  本章小结   79

第8章  SHOW语句   80

8.1  与information库的关系   81

8.2  与performance库的关系   82

8.3  引擎状态   83

8.4  复制与二进制日志   84

8.4.1  列出二进制日志   84

8.4.2  查看日志事件   84

8.4.3  显示连接的副本   87

8.5  其他语句   88

8.6  本章小结   90

第9章  慢查询日志   92

9.1  配置   93

9.2  日志事件   95

9.3  汇总   96

9.4  本章小结   98

第Ⅲ部分  工    具

第10章  MySQL Enterprise Monitor   100

10.1  概述   100

10.2  安装   102

10.2.1  下载   102

10.2.2  安装   104

10.3  启动和停止Service Manager   109

10.3.1  在Microsoft Windows中启动和停止Service Manager   109

10.3.2  在Linux中启动和停止Service Manager   110

10.4  添加MySQL实例   111

10.5  图形管理界面   113

10.5.1  通用导航   113

10.5.2  建议器   114

10.5.3  时序图   116

10.5.4  查询分析器   117

10.6  本章小结   118

第11章  MySQL Workbench   119

11.1  安装   120

11.1.1  Microsoft Windows   120

11.1.2  Enterprise Linux 7   124

11.1.3  Debian和Ubuntu   127

11.2  创建连接   129

11.3  使用MySQL Workbench   130

11.3.1  概要   130

11.3.2  配置   131

11.3.3  安全设置   133

11.3.4  重新格式化查询   133

11.4  EER图   134

11.5  本章小结   135

第12章  MySQL shell   136

12.1  概要   136

12.1.1  安装MySQL shell   137

12.1.2  调用MySQL shell   137

12.1.3  创建连接   137

12.1.4  语言模式   139

12.1.5  内建帮助   140

12.1.6  内建全局对象   141

12.2  提示符   141

12.2.1  内置提示符   141

12.2.2  自定义提示符   143

12.2.3  Powerline和Awesome字体   145

12.3  使用外部模块   146

12.4  报表基础架构   148

12.4.1  报表信息和帮助   148

12.4.2  执行报表   149

12.4.3  添加自己的报表   151

12.5  插件   155

12.6  本章小结   161

第Ⅳ部分  方案考量与查询优化器

第13章  数据类型   164

13.1  为何是数据类型   164

13.1.1  数据验证   165

13.1.2  文档   166

13.1.3  优化存储   166

13.1.4  性能   167

13.1.5  正确排序   167

13.2  MySQL的数据类型   167

13.2.1  数值类型   168

13.2.2  日期和时间类型   169

13.2.3  字符串与二进制类型   169

13.2.4  JSON数据类型   171

13.2.5  空间数据类型   172

13.2.6  混合数据类型   173

13.3  性能   174

13.4  应该选择何种数据类型   174

13.5  本章小结   176

第14章  索引   177

14.1  什么是索引   177

14.2  索引的概念   178

14.2.1  键与索引   178

14.2.2  唯一索引   178

14.2.3  主键   179

14.2.4  二级索引   180

14.2.5  簇聚索引   180

14.2.6  覆盖索引   180

14.3  索引的限制   181

14.4  SQL语法   181

14.4.1  创建带有索引的表   182

14.4.2  添加索引   182

14.4.3  移除索引   183

14.5  索引的缺点是什么?   184

14.5.1  存储   184

14.5.2  更新索引   185

14.5.3  优化器   185

14.6  索引类型   186

14.6.1  B-tree索引   186

14.6.2  全文索引   188

14.6.3  空间索引   189

14.6.4  多值索引   190

14.6.5  哈希索引   193

14.7  索引的特性   195

14.7.1  函数索引   196

14.7.2  前缀索引   196

14.7.3  不可见索引   197

14.7.4  降序索引   198

14.7.5  分区与索引   198

14.7.6  自生成索引   200

14.8  InnoDB与索引   200

14.8.1  簇聚索引   201

14.8.2  二级索引   201

14.8.3  建议   201

14.8.4  最佳用例   201

14.9  索引策略   202

14.9.1  何时添加或者移除索引?   202

14.9.2  主键的选择   203

14.9.3  添加二级索引   203

14.9.4  多列索引   204

14.9.5  覆盖索引   205

14.10  本章小结   205

第15章  索引统计信息   206

15.1  何为索引统计信息?   206

15.2  InnoDB与索引统计信息   207

15.2.1  统计信息是如何被收集的?   207

15.2.2  页采样   208

15.2.3  事务隔离级别   209

15.2.4  配置统计信息类型   209

15.3  持久索引统计信息   209

15.3.1  配置   210

15.3.2  索引统计信息表   211

15.4  临时索引统计信息   214

15.5  监控   215

15.5.1  information库中的STATISTICS视图   215

15.5.2  SHOW INDEX 语句   217

15.5.3  information库中的INNODB_TABLESTATS视图   219

15.5.4  information库中的TABLES视图及SHOW TABLE STATUS语句   220

15.6  更新统计信息   223

15.6.1  自动更新   223

15.6.2  ANALYZE TABLE语句   224

15.6.3  mysqlcheck程序   225

15.7  本章小结   227

第16章  直方图   229

16.1  何为直方图?   229

16.2  何时应该添加直方图信息?   230

16.3  直方图内部信息   231

16.3.1  bucket   231

16.3.2  累积频率   232

16.3.3  直方图类型   234

16.4  直方图的添加与维护   236

16.4.1  直方图的创建与更新   236

16.4.2  采样   237

16.4.3  删除直方图   238

16.5  查看直方图数据   238

16.6  直方图报告示例   239

16.6.1  列出所有直方图   240

16.6.2  列出一个直方图的所有信息   240

16.6.3  列出一个单值直方图的桶信息   241

16.6.4  列出一个等高直方图的桶信息   243

16.7  查询示例   244

16.8  本章小结   247

第17章  查询优化器   248

17.1  转换   249

17.2  基于成本的优化   249

17.2.1  基础:单表SELECT操作   250

17.2.2  表联接顺序   251

17.2.3  默认过滤效果   251

17.2.4  查询成本   252

17.3  联接算法   254

17.3.1  嵌套循环   254

17.3.2  块嵌套循环   257

17.3.3  哈希联接   260

17.4  联接优化   263

17.4.1  索引合并   263

17.4.2  多范围读(MRR)   269

17.4.3  批量key访问(BKA)   270

17.4.4  其他优化   271

17.5  配置优化器   274

17.5.1  引擎成本   275

17.5.2  服务器成本   275

17.5.3  优化器开关   277

17.5.4  优化器提示   278

17.5.5  索引提示   281

17.5.6  配置选项   282

17.6  资源组   282

17.6.1  获取资源组相关信息   283

17.6.2  管理资源组   283

17.6.3  分配资源组   285

17.6.4  性能考量   286

17.7  本章小结   287

第18章  锁原理与监控   288

18.1  为何会需要锁?   288

18.2  锁访问级别   289

18.3  锁粒度   289

18.3.1  用户级别锁   289

18.3.2  刷新锁   291

18.3.3  元数据锁   292

18.3.4  显式表锁   295

18.3.5  隐式表锁   295

18.3.6  记录锁   297

18.3.7  gap锁、next-key锁以及预测锁   299

18.3.8  插入意向锁   300

18.3.9  自增锁   302

18.3.10  备份锁   302

18.3.11  日志锁   304

18.4  获取锁失败   304

18.4.1  元数据锁和备份锁等待超时   305

18.4.2  InnoDB锁等待超时   305

18.4.3  死锁   306

18.5  减少锁相关的问题   309

18.5.1  事务大小与期限   309

18.5.2  索引   309

18.5.3  记录访问顺序   310

18.5.4  事务隔离级别   310

18.5.5  抢占锁   312

18.6  监控锁   313

18.6.1  performance库   313

18.6.2  sys库   314

18.6.3  状态计数器与InnoDB指标   314

18.6.4  InnoDB锁监控与死锁日志   315

18.7  本章小结   318

第Ⅴ部分  查询分析

第19章  查找待优化的查询   320

19.1  performance库   321

19.1.1  语句事件表   321

19.1.2  prepared语句的汇总   325

19.1.3  表的I/O汇总   327

19.1.4  文件 I/O汇总信息   332

19.1.5  错误汇总表   334

19.2  sys库   335

19.2.1  语句视图   335

19.2.2  表I/O视图   337

19.2.3  文件I/O视图   338

19.2.4  语句性能分析器   340

19.3  MySQL Workbench   343

19.3.1  性能报告   344

19.3.2  客户端连接报告   346

19.4  MySQL Enterprise Monitor   346

19.4.1  查询分析器   346

19.4.2  时间序列图   349

19.4.3  即席查询报告   350

19.5  慢查询日志   352

19.6  本章小结   352

第20章  分析查询   354

20.1  EXPLAIN用法   355

20.1.1  显式查询的用法   355

20.1.2  EXPLAIN ANALYZE   355

20.1.3  连接的用法   356

20.2  EXPLAIN格式   357

20.2.1  传统格式   358

20.2.2  JSON格式   359

20.2.3  树状格式   362

20.2.4  Visual Explain   364

20.3  EXPLAIN输出   368

20.3.1  EXPLAIN字段   368

20.3.2  选择类型   371

20.3.3  访问类型   372

20.3.4  Extra信息   376

20.4  EXPLAIN示例   377

20.4.1  单表,全表扫描   378

20.4.2  单表,索引访问   379

20.4.3  两张表和覆盖索引   380

20.4.4  多列索引   381

20.4.5  两张表并带有子查询和排序   382

20.5  优化器跟踪   384

20.6  performance库事件分析   387

20.6.1  检查存储过程   387

20.6.2  分析阶段事件   391

20.6.3  使用sys.ps_trace_thread( )过程进行分析   393

20.6.4  使用ps_trace_statement_digest( )过程进行分析   396

20.7  本章小结   400

第21章  事务   401

21.1  事务的影响   401

21.1.1  锁   402

21.1.2  undo日志   402

21.2  INNODB_TRX   403

21.3  InnoDB监视器   406

21.4  INNODB_METRICS和sys.metrics   407

21.5  performance库事务   410

21.5.1  事务事件及其语句   410

21.5.2  事务汇总表   416

21.6  本章小结   417

第22章  诊断锁争用   418

22.1  刷新锁   419

22.1.1  症状   419

22.1.2  原因   419

22.1.3  构建   420

22.1.4  调研   420

22.1.5  解决方案   423

22.1.6  预防   423

22.2  元数据锁和方案锁   424

22.2.1  症状   424

22.2.2  原因   424

22.2.3  构建   424

22.2.4  调研   425

22.2.5  解决方案   431

22.2.6  预防   431

22.3  记录锁   432

22.3.1  症状   432

22.3.2  原因   434

22.3.3  构建   435

22.3.4  调研   435

22.3.5  解决方案   436

22.3.6  预防   437

22.4  死锁   437

22.4.1  症状   437

22.4.2  原因   438

22.4.3  构建   438

22.4.4  调研   439

22.4.5  解决方案   444

22.4.6  预防   444

22.5  本章小结   445

第Ⅵ部分  提升查询性能

第23章  配置   448

23.1  最佳实践   448

23.2  InnoDB综述   451

23.3  InnoDB缓冲池   452

23.3.1  缓冲池大小   453

23.3.2  缓冲池实例   455

23.3.3  转储缓冲池   455

23.3.4  旧块子列表   455

23.3.5  刷新页   457

23.4  重做日志   458

23.4.1  日志缓冲区   459

23.4.2  日志文件   459

23.5  并行查询执行   461

23.6  查询缓冲区   462

23.7  内部临时表   463

23.8  本章小结   465

第24章  改变查询计划   466

24.1  测试数据   466

24.2  出现过多全表扫描的症状   467

24.3  错误查询   468

24.4  未使用索引   470

24.4.1  不在多列索引的靠左位置   471

24.4.2  数据类型不匹配   473

24.4.3  函数依赖   476

24.5  改善索引的使用情况   478

24.5.1  添加覆盖索引   479

24.5.2  错误索引   480

24.5.3  重写复杂索引条件   487

24.6  重写复杂查询   488

24.6.1  公共表表达式(CTE)   489

24.6.2  窗口函数   493

24.6.3  使用联接来重写子查询   494

24.6.4  将查询拆分为多个部分   495

24.7  队列系统:SKIP LOCKED   496

24.8  多个OR或者IN条件   498

24.9  本章小结   502

第25章  DDL与批量数据加载   504

25.1  方案更改   505

25.1.1  算法   505

25.1.2  其他考量   506

25.1.3  删除或者截断表   506

25.2  数据加载的一般性考量   507

25.3  以主键顺序插入   516

25.3.1  自增长主键   516

25.3.2  插入已有数据   517

25.3.3  UUID主键   518

25.4  InnoDB缓冲池与二级索引   519

25.5  配置   521

25.6  事务与加载方式   522

25.7  MySQL shell并行数据加载   522

25.8  本章小结   524

第26章  复制   526

26.1  复制概述   527

26.2  监控   528

26.2.1  连接表   529

26.2.2  applier表   531

26.2.3  日志状态   533

26.2.4  组复制表   534

26.3  连接   534

26.3.1  复制事件   534

26.3.2  网络   535

26.3.3  维护源信息   536

26.3.4  写入中继日志   536

26.4  applier线程   536

26.4.1  并行applier   537

26.4.2  主键   538

26.4.3  放宽数据安全   538

26.4.4  复制过滤器   539

26.5  将工作负载卸载到副本   540

26.5.1  读操作的横向扩展   540

26.5.2  任务分离   540

26.6  本章小结   541

第27章  缓存   542

27.1  缓存,无处不在   542

27.2  MySQL中的缓存   543

27.2.1  缓存表   543

27.2.2  直方图统计信息   545

27.3  Memcached   546

27.3.1  独立服务器模式下的Memcached   547

27.3.2  MySQL InnoDB Memcached插件   549

27.4  ProxySQL   552

27.5  缓存技巧   558

27.6  本章小结   559

作者简介

编辑推荐

性能不佳的查询将影响用户的体验,导致业务收入下降;本书将帮助你在日常工作中更好地识别、分析和改进此类查询。本书详述涉及多个步骤的处理过程,包括监控查询执行时间、识别需要优化的查询、分析当前的性能表现以及进行优化等,还介绍相关的数据源和工具,帮助你更快地提交结果,降低系统

开销。

《MySQL 8查询性能优化》描述多种可提升查询性能的策略,讲述如何使用传统的EXPLAIN命令以及新的EXPLAIN ANALYZE工具来分析查询、如何使用Visual Explain功能来获得执行计划的可视化视图、如何用直方图获得关于“桶”数据的分布信息。此外,将介绍锁以及解决锁问题的相关知识;讨论MySQL优化器的工作原理,包括新的哈希联接算法,以及在必要时改变优化器行为来缩短查询的执行时间。通过本书,你将掌握必备技术,能用合适工具提高用户满意度,从公司的计算资源中获取更大价值。

作者寄语

自2006年以来,Jesper Wisborg Krogh先后以SQL开发人员和数据库管理员的身份参与到MySQL数据库工作中,并且作为MySQL技术支持团队的一员,工作了8年之久。他曾在MySQL Connect和Oracle OpenWorld上多次发表演讲。除了出版相关书籍外,他也会定期撰写一些以MySQL为主题的博客文章,并为Oracle知识库撰写了约800份文档。此外,Jesper Wisborg Krogh也为MySQL中的sys库,以及MySQL 5.6等相关的OCP认证考试做出了许多贡献。

在2006年转向MySQL及软件开发之前,Jesper Wisborg Krogh获得计算化学的博士学位。他现在居住在澳大利亚的悉尼,平时喜欢在户外散步、旅行以及阅读等。其研究领域涉及MySQL集群、MySQL Enterprise Backup(MEB)、性能优化,以及performance库和sys库等。

电子资料

www.luweidong.cn

下一个