高性能mysql 第六章_《高性能Mysql》读书笔记---第六章：查询性能优化

查询优化，索引优化，库表结构优化需要齐头并进。在获得Mysql查询经验的同时，也将学习如何为高效的查询设计表的索引，同样的，也可以学习到在优化表结构时会影响哪些类型的查询。

本章思路：查询设计的基本原则，这也是发现查询效率不高的时候首先考虑的因素

查询优化技巧

Mysql优化器内部的机制

Mysql是如何执行查询的

学习如何改变一个查询的执行计划

Mysql优化器在哪些方面还做的不够

探索查询优化的模式

一，为什么查询速度会慢

查询的性能目标是降低响应时间！

查询是一个任务，它有很多子任务构成，每个子任务都需要消耗一定的时间。若想优化查询，就要优化子任务，要么去掉子任务，要么减少子任务的执行次数，要么减少子任务的执行时间。

查询的生命周期：

从客户端，到服务器，然后在服务器上执行解析，生成执行计划，执行，然后将结构返回给客户端。

其中，执行是最重要的阶段，包括了大量为了检索数据到储存引擎的调用以及调用后的数据处理，包括排序，分组等。

在完成这些任务的时候，需要在不同的地方花费时间，包括网络IO，CPU计算，生成统计信息和执行计划 锁等待。

在调用底层数据引擎检索数据时，需要等待内存操作，CPU操作，和IO操作等待，这会产生大量的上下文切换和系统调用！

对于每一个消耗大量时间的查询操作，都能看到一些不必要的操作，某些操作被额外的执行了多次，某些操作执行太慢！

优化查询的目的就是减少和消除这些操作所花费的时间。

二，慢查询基础：优化数据访问

查询性能低下的原因是访问的数据太多。某些查询可能不可避免的筛选大量的数据，但这并不常见。大部分性能地下的查询都可以通过减少访问数据量的方式进行优化！

对于低效率的查询，使用下面的步骤来分析：确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能访问了太多的列。

确认Mysql服务器层是否在分析大量超过需要的数据行。

1,是否向数据库请求了不需要的数据

有些应用程序会请求超过实际需要的数据， 然后这些多余的数据会被应用程序丢弃。这会给Mysql服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU和内存资源。不应该做无用的事情！查询不需要的记录：就是不使用sql语句中自带的限制语句，而是使用应用程序中的编程语言中的限制语句，最丑了。

多表关联时返回全部列：多表关联时会从磁盘中读取全部列然后在内存中进行计算，非常低效

总是取出全部列：每次看到select *的时候都要用怀疑的眼光审视，取出全部列，会让优化器无法完成索引覆盖扫描这类优化。

重复查询相同的数据：当重复使用数据时，可以考虑将数据缓存起来，而不是每次都是去数据库取。

2,Mysql是否在扫描额外的记录

接下来看看查询为了返回结果是否扫描了过多的数据，对于Mysql，最简单的衡量查询开销的三个指标为：响应时间

扫描的行数

返回的行数

这三个指标会记录到Mysql的慢日志中。

------------------------------------------------------------------------------------------------------------

响应时间

响应时间是服务时间和排队时间的总和，服务时间就是真正执行查询的时间，排队时间是等待资源的时间，资源可能是IO操作或者锁。响应时间很难清楚将两种时间分开。

储存引擎的锁，高并发资源竞争，硬件响应等都会影响响应时间。

根据看到的响应时间，然后人工估计应该的响应时间，最后对这两者进行比较。可以使用"快速上限估计"法来估算查询的响应时间。大体做法：了解查询所要哪些索引以及执行计划是什么，

计算大概需要多少个顺序和随机IO

再用其乘以在具体硬件条件下一次IO的消耗时间。

然后把这些消耗都加起来

能估算出来的都是神人，可能以后的我也能估算出来。

-----------------------------------------------------------------------------------------

扫描的行数和返回的行数

最理想的情况是扫描的行数等于返回的行数，扫描的行数越接近返回的行数越好。

------------------------------------------------------------------------------------------------

扫描的行数和访问类型

Mysql有好几种方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才返回一行，而有些可能无须扫描就能返回正确的结果

explain语句中的type列反应了访问类型。访问类型从慢到快有：全表扫描，索引扫描，范围扫描，唯一索引查询，常数引用。

如果查询没有办法找到合适的访问类型，那最好的办法就是增加一个合适的索引。

一般Mysql能够使用三种方式应用WHERE条件，从好到坏依次为：在索引中使用WHERE条件来过滤不匹配的记录，这是在储存引擎中完成的

使用索引覆盖扫描(explain语句的extra列出现了Using index)来返回记录。直接从索引中过滤不需要的记录并返回命中的结果，这是在Mysql服务器层完成的，但无须再回表查询记录

从数据表中返回数据，然后过滤不满足的条件(explain语句的extra列出现了Using where)。这在Mysql服务器层完成的，Mysql需要从数据表读取记录然后过滤

这说明了好的索引多么重要！好索引可以让查询使用合适的访问类型，尽可能地只扫描需要的数据行。

使用count(*)需要全表扫描，没有什么索引能够让这样的查询减少需要扫描的行数。

使用下面的技巧可以优化上面的查询：使用索引覆盖扫描，把所有用到的列都放到索引中，这样储存引擎无须回表获取对应行就可以返回结果了

改变库表结构，使用单独的汇总表，对与count()来说

重写这个复杂的查询，让Mysql优化器能够以更优化的方式执行这个查询

三，重构查询的方式

有时候有一种更优的查询方式来返回相同的结果集

1,一个复杂查询还是多个简单查询

在传统应用中，总是强调数据库层完成尽可能多的事情，因为网络延迟是很高的。

但对于Mysql并不使用，Mysql从设计上让链接和断开链接都是轻量级的操作，在返回一个小的查询结果时很高效，现代的网络也快的很多。

在条件都相同的条件下，使用尽可能少的查询当然是好的，但是有时候，将一个大查询分解为多个小查询往往效果会更好，应该好好衡量一下这样做是不是会减少工作量。

2,切分查询

切分查询是对大查询"分而治之"，将大查询分为多个小查询，每个查询功能完全一样，只完成一小部分。

删除旧的数据就是一个很好的"分而治之"的例子。定期的清楚大量数据时，如果一个大的语句一次性完成的话，则可能一次锁住很多数据，占满整个事物日志，耗尽系统资源，阻塞很多小的但重要的查询。

3,分解关联查询

将一条关联多个表的sql语句通过编程语言拆分成多个操作单个表的语句。

分解关联查询方式的优势：让缓存效率更高，应用程序可以缓存了单表的结果对象，然后下次直接使用这个缓存的结果，这样就跳过了一次查询。

将查询分解后，执行单个查询可以减少锁的竞争

在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展，为分布式做准备！

查询本身效率也会有提升，让Mysql按照ID顺训进行查询，比随机的关联要更高效。

可以减少冗余记录的查询

这样分解可以易于实现哈希索引！

四，查询执行的基础

Mysql是如何优化和执行查询的那？下面学Mysql执行一个查询的过程！

Mysql发送一个请求的时候，到底做了什么？客户端发送一条查询给服务器

服务器先查询查询缓存，若缓存命中，则立刻返回,否则进入下一阶段

服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划(可以理解为编译生成可执行文件)

Mysql根据优化器生成的执行计划，调用储存引擎的API执行查询

将结果返回给客户端

1,Mysql客户端/服务器通信协议

Mysql客户端和服务器之间的通信协议是"半双工"的

这种简单的协议限制了Mysql，一个明显的限制是没办法进行流量控制，一旦一端开始发送数据，另一端接受完整的消息才能响应他。

客户端会发送一个单独的数据包将查询传给服务器，当查询语句特别长的时候，可以使用max_allowed_packet限制。一旦客户端发送了请求，它能做的事情就只有等待结果了。

服务器响应的数据通常很多，由多个数据包组成，客户端不能中断服务器发送数据。

多数链接Mysql的库函数都可以将获得的结果集缓存在内存中，然后逐行获取数据。Mysql通常需要等待所有数据已经发送到客户端之后才释放这条查询所需要的资源。所以接收结果并缓存可以减轻服务器的压力，让查询早点结束。再点释放相应的资源。

---------------------------------------------------------------------------------------------------------

查询状态

对于一个Mysql链接，或者一个线程，任何时候都有一个状态，该状态表示了Mysql当前正在做什么。

在一个查询的生命周期中，状态会变化很多次，下面列出来官方手册的状态值：Sleep:线程正在等待客户端发送新的请求

Query：线程正在执行查询，或者正在将结果发送到客户端

Locked：在Mysql服务器层，该线程正在等待表锁。在储存引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个典型的状态，因为它没有行锁。

Analyzing and statistics:线程正在手机储存引擎的统计信息，并生成查询的执行计划。

Copying to tmp table [on disk]：线程正在执行查询，并且将结果集都复制到一个临时表中，这种状态要么是在执行GROUP BY操作，要么是文件排序操作，或者是UNION操作，[on disk]标记表示Mysql正在将一个内存临时表存放到磁盘上。

Sorting result：线程正在对结果集进行排序

2,查询缓存

在解析一个查询语句之前，要检查缓存是否命中。

这个检查是通过一个对大小写敏感的哈希查找实现的，查询和缓存中的查询即使一个字节不同，那也不会匹配缓存结果。

若缓存命中了，那么在返回查询结果之前Mysql会检查一次用户权限。

3,查询优化处理

查询生命周期的下一步是将SQL转换成一个执行计划，这个阶段包括多个子阶段：解析SQL，预处理，优化SQL执行计划。这个阶段的任何错误都会终止查询。

----------------------------------------------------------------------------------------------------------------

语法解析器和预处理

Mysql通过关键字将SQL语句进行解析，并生成一颗对应的"解析树"，Mysql解析器将使用Mysql的语法规则验证和解析查询。比如：它将验证是否使用了错误的关键字，或者使用关键字顺序是否正确，或者验证引号能否正确匹配。

预处理器则根据一些Mysql规则进一步检查解析树是否合法，例如，这里将检查数据表和数据是否存在，还会解析名字和别名，看看他们是否有歧义。

然后预处理器会验证权限。

------------------------------------------------------------------------------------------------------------

查询优化器

现在语法树被认为是合法的了，这时需要优化器将其转化为执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就好找到其中最好的执行计划。

Mysql使用基于成本的优化器，它尝试预测一下查询使用某种计划时的成本，并选择其中最小的一个。

优化器根据一系列信息计算查找多少数据页才能完成查询，这些信息包括：每个表或索引的页面个数，索引的基数(索引中不同值的数量)，索引和数据行的长度，索引分布情况等。

很多原因会导致Mysql优化器选择错误的执行计划：统计信息不正确，Mysql依赖储存引擎提供的统计信息来评估成本，但有些信息是不正确的，比如InnoDB因为MVCC(多版本并发控制机制)架构，储存的数据表的行数并不精确！

执行计划中的成本估算不等同与实际执行的成本

从不考虑其他的并发执行查询

Mysql也不是任何时候都使用基于成本的优化

Mysql不会考虑不受其控制的操作的成本，比如用户自定义的函数

优化器可能无法估算所有可能的执行计划，所以可能错过最优的执行计划！

选择完执行计划后，查询优化器有两种优化策略：静态优化和动态优化。

静态优化直接对解析树进行分析，并完成优化。

动态优化则和查询的上下文有关，

Mysql的静态优化只会执行一次，但对查询动态优化则在每次执行时都需要重新评估，有时甚至在查询执行过程中也会重新优化。

Mysql能够处理的优化类型：重新定义关联表的顺序

将外连接转化为内连接

使用等价变换规则

优化COUNT() MIN() MAX():最大最小值可以直接取B-Tree中最左端最右端的值，count()会使用记录的值，而不是查询整个表

预估并转化为常数表达式

覆盖索引扫描

子查询优化

提前终止查询

等值传播

列表IN()的比较

----------------------------------------------------------------------------------------------------------------

数据和索引的统计信息

统计信息是由书u年引擎实现的，不同的储存引擎会储存不同的统计信息！服务器层没有任何统计信息。

Mysql查询优化器在生成查询的执行计划时，需要向储存引擎获取相应的统计信息。

储存引擎提供给优化器的统计信息有：每个表或每个索引有多少个页面，每个表的每个索引的基数是多少，数据行和索引长度，索引的分布信息等

--------------------------------------------------------------------------------------------------------------------

Mysql如何执行关联查询

在Mysql中，不仅仅是两个表匹配叫关联，其实单表查询，子查询都叫关联。

对于UNION查询，原理是Mysql先将一系列的单个查询放到一个临时表中，然后再重新读出临时表的数据来完成UNION查询。在Mysql概念中，每个查询都是一次关联，所以读取临时表的也是一次关联！

Mysql的关联执行策略很简单：Mysql对任何关联都执行嵌套循环关联操作！

Mysql对所有查询都执行嵌套循环关联操作，Mysql将右外链接转换为左外链接。

因为全外链接无法使用嵌套循环，所以Mysql不支持全外链接。

-------------------------------------------------------------------------------------------------------------------

执行计划

和其他关系数据库不同，Mysql不会生成查询字节码来执行查询。Mysql生成查询的一颗指令树，然后通过储存引擎执行完成这颗指令树并返回结果。最终的执行计划包含了重构查询的全部信息。

Mysql总是从一个表开始一直嵌套循环，回溯完成所有表关联。所以，Mysql的执行计划总是一颗左侧深度优先的树。

--------------------------------------------------------------------------------------------------------------------

关联查询优化器

关联查询优化是Mysql优化器的重要的一部分，它决定了多个表关联时的顺序。

在多表关联时，有多种不同的关联顺序，关联查询优化器通过评估不同顺序时的成本来选择一个代价最小的关联查询。

-----------------------------------------------------------------------------------------------------------------

排序优化

无论如何排序都是一个成本很高的操作。

通过索引排序效率很高，但当不能使用索引生成排序结果时，Mysql需要自己进行排序。若数据量小则在内存中进行，若数据量大则需要使用磁盘。Mysql将这个排序的过程叫做"文件排序".

排序过程：若排序的数据量小于"排序缓冲区"，Mysql使用内存进行"快速排序"操作，若内存不够排序，Mysql会将数据分块，对每个块使用"快速排序"，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并，最后返回排序结果。

两种排序算法：两次传输排序(旧版本)：先读取需要排序的字段进行排序，然后根据排序结果读取所需要的数据行。这需要进行两次数据传输，并且第二次是随机IO，所以成本很高，不过好处是在排序时使用的储存很少！

单次传输排序：与两次传输排序相反。

4,查询执行引擎

根据执行计划中的指令逐步执行，期间有很多需要调用储存引擎实现的接口来完成。查询中每个表都由一个句柄表示，很像文件描述符。储存引擎接口非常丰富，但是底层接口就有十几个。