MySql查询优化

茕祇 2021-03-15 我要评论

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

查询性能低下最基本的原因是访问的数据太多。

确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。

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

向数据库请求了过多的数据

查询不需要的记录

一个常见的错误是常常会误以为MySQL会只返回需要的数据，实际上MySQL却是先返回全部结果集再进行计算。

比如查询100条结果,但在获取前十条后关闭结果集,正确的做法应该是加上limit

多表关联时返回全部列

如select * from SC,C,我们应该只返回我们需要的列,但这个也要根据代码情况来判断

重复查询相同的数据

MySql是否在扫描额外的记录

指标

响应时间
扫描的行数
返回的行数

响应时间

响应时间是两个部分之和：服务时间和排队时间。

服务时间是指数据库处理这个查询真正花了多长时间。

排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——可能是等I/O操作完成，也可能是等待行锁

一般最常见和重要的等待是I/O和锁等待

扫描的行数和返回的行数

扫描的行数对返回的行数的比率通常很小，一般在1:1和10:1之间，不过有时候这个值也可能非常非常大。

扫描的行数和访问类型

访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等

一般MySQL能够使用如下三种方式应用WHERE 条件，从好到坏依次为

重构查询的方式

目标应该是找到一个更优的方法获得实际需要的结果——而不一定总是需要从MySQL获取一模一样的结果集

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

切分查询

将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

分解关联查询

简单地，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。

好处

让缓存效率更高,连表查询只要一个表发生变化就不能命中缓存,拆分后,其他表却可以利用缓存
执行单个查询可以减少锁的竞争
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展
查询本身效率也可能会有所提升。这个例子中，使用IN() 代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。
可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。

查询执行的基础

客户端发送一条查询给服务器。
服务器先检查查询缓存，
如果命中了缓存，则立刻返回存储在缓存中的结果。
否则进入下一阶段。服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
将结果返回给客户端。

MySQL客户端服务器协议

这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生

客户端用一个单独的数据包将查询传给服务器

一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据,所以才需要用limit

MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放相应的资源。

查询状态

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

在一个查询的生命周期中，状态会变化很多次

SHOW FULL PROCESSLIST

Sleep

线程正在等待客户端发送新的请求。

Query

线程正在执行查询或者正在将结果发送给客户端。

Locked

在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态，但在其他没有行锁的引擎中也经常会出现

Analyzing and statistics

线程正在收集存储引擎的统计信息，并生成查询的执行计划。

Copying to tmp table [on disk]

线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做GROUP BY 操作，要么是文件排序操作，或者是UNION操作。如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上。

The thread is

线程正在对结果集进行排序。

Sending data

这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

查询缓存

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

查询优化处理

包括多个子阶段：解析SQL、预处理、优化SQL执行计划

语法解析器和预处理

MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。例如，它将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者它还会验证引号是否能前后正确匹配。

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

下一步预处理器会验证权限。

查询优化器

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

查询优化的类型

重新定义关联表的顺序
将外连接转化成内连接
使用等价变换规则MySQL可以使用一些等价变换来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断
优化COUNT()、MIN()和MAX(),利用索引转换为常数
预估并转化为常数表达式当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理
覆盖索引扫描
子查询优化
及时终止查询(limit)
等值传播(若id在两个表中都有,则条件只需要写一个)
将IN ()先排序,再二分查找,而不是直接转换为or

数据的索引和统计信息

MySQL架构由多个层次组成。在服务器层有查询优化器，却没有保存数据和索引的统计信息。统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息（也可以按照不同的格式存储统计信息）。

优化器根据这些信息来选择一个最优的执行计划

MySQL如何执行关联查询

MySQL认为任何一个查询都是一次“关联”——并不仅仅是一个查询需要到两个表匹配才叫关联，所以在MySQL中，每一个查询，每一个片段（包括子查询，甚至基于单表的SELECT）都可能是关联。
对于UNION查询

先将一系列的单表查询结果放在一个临时表中
再从临时表中读取数据(由于MySQL中每一次查询都算关联,所以这也算作一次关联)

当前MySQL关联执行策略:嵌套循环关联操作

即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。
然后根据各个表匹配的行，返回查询中需要的各个列。
MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个联表无法找到更多的行以后，MySQL返回到上一层次关联表，看是否能够找到更多的匹配记录，依此类推迭代执行。

执行计划

MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息

关联查询优化器

通过优化关联查询的表的顺序,使成本最小,当表的数量较小(小于Optimize时)逐一遍历,过大时则采用贪心算法

排序优化

如果数据量小,在内存中进行快速排序
如果数据量大,分块进行快速排序后再合并
两次传输合并,读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。
单次传输合并,先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果

查询执行引擎

MySQL在优化阶段就为每个表创建了一个handler 实例，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息，等等。

返回结果给客户端

返回结果的相关信息

将结果加入到缓存

MySQL查询优化器的局限

关联子查询

UNION的限制

有时，MySQL无法将限制条件从外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上

索引传递优化

最大值和最小值优化

总是会进行全表扫描

一个曲线的优化办法是移除MIN()，然后使用LIMIT来将查询重写如下：

有时候为了获得更高的性能，我们不得不放弃一些原则。

松散索引扫描

MySQL并不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引,通常，MySQL的索引扫描需要先定义一个起点和终点，即使需要的数据只是这段索引中很少数的几个，MySQL仍需要扫描这段索引中每一个条目。

不能在同时在一个表上做查找和更新

可以通过使用生成表的形式来绕过上面的限制，因为MySQL只会把这个表当作一个临时表来处理。实际上，这执行了两个查询：一个是子查询中的SELECT语句，另一个是多表关联UPDATE ，只是关联的表是一个临时表。

查询优化器的提示

主要还是看官方文档

优化特定类型的查询

优化COUNT()查询

COUNT()查询的作用

它可以统计某个列值的数量，也可以统计行数。

在统计列值时要求列值是非空的（不统计NULL ）。如果在COUNT() 的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数

最简单的就是当我们使用COUNT（*）的时候，这种情况下通配符*并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数

简单优化

MyISAM 用一个变量保存了整个表的行数

InnoDB要全表扫描

到

使用近似值

可以减少约束条件得到大概的数量

更复杂的优化

快速，精确和实现简单”，三者永远只能满足其二，必须舍掉其中一个。

通过索引覆盖,汇总表或外部缓存表

优化关联查询

确保ON 或者USING 子句中的列上有索引。

在创建索引的时候就要考虑到关联的顺序。当表A 和表B 用列c 关联的时候，如果优化器的关联顺序是B、A ，那么就不需要在B表的对应列上建上索引。没有用到的索引只会带来额外的负担。

一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。

确保任何的GROUP BY 和ORDER BY 中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。