《高性能MySQL》学习笔记四

在上一篇博客中，主要学习了如何高性能索引方面的知识，今天学习数据库中的查询性能优化。一个好的查询语句，对数据库高性能同样重要。

查询性能优化

为什么查询速度会慢？
一个查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中在“执行”阶段包含了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组。
查询速度慢的原因在于：某些不必要的额外操作，某些操作被额外地重复很多次，某些操作执行得太慢。
优化查询的目的就是减少和消除这些操作所花费的时间。

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

查询性能低下最基本的原因是访问的数据太多。所以对于低效的查询可以从下面两个方面来分析：
1.确认应用程序是否在检索大量超过需要的数据。
2.确认MySQL服务器层是否在分析大量超过需要的数据行。
查询是否只取所需
低效的查询表现在如下方面：
a.查询了不需要的记录；
b.多表关联时返回全部列；
c.总是取出所有的列；
d.重复查询相同的数据。
注：在查询语句中慎用select * 语句，我们对数据库的数据应该只取所需。
MySQL是否在扫描额外的记录
在确定查询只返回需要的数据之后，那么查询为了返回结果是否扫描了过多的数据，有以下三个指标衡量查询的开销：

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

通过查询慢日志可以找到这三个指标的记录
索引对扫描的行数有着很大的影响，当发现查询需要扫描大量的数据但只返回少数的行时，可以使用索引覆盖扫描，避免回表查询。

重构查询的方式

选择一个复杂的查询还是多个简单的查询？
书中作者的观点：在MySQL内部每秒能够扫描内存中上百行的数据，相比之下MySQL响应数据给客服端就慢的多了。其他条件都相同的时候，使用尽可能少的查询是更好的。但是并不否认将一个大的查询分解为多个小的查询。
将查询分解的方法：
切分查询：顾名思义，就是将一个大的查询切分为许多小的查询，每个小查询功能完全一样，返回一部分结果，我们只需重复执行小查询就行。（应用：在清除大量的数据时，如果一个大的语句可能一次性要耗费许多资源，阻塞其他查询，这时我们可以将其切分为多个小的查询，即每个查询只删除适量的查询，多次进行）
分解关联查询
可将单条的多表关联查询分解为多条查询，对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。（将在数据库中做的关联查询，转移到了应用层）
优点：
a.让缓存的效率更高。对单表查询的结果，应用程序可以很方便的缓存，分解语句之后，我们可以高效的利用缓存来进行查询。
b.将查询分解之后，执行单个查询可以减少锁的竞争。
c.在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
d.查询本身的效率也会得到提升。按照ID顺序查询比随机的关联要更加的高效。（使用in()的方式代替关联查询的join…on…）
e.减少冗余记录的查询。在应用层做关联查询，对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复的访问某一部分的数据。

查询执行的基础

MySQL是如何优化和执行查询的？

1.客户端发送一条查询给服务器；
2.服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段；
3.服务器进行SQL解析、预处理、在由查询优化器生成对应的执行计划；
4.MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5.将结果返回给客服端，同时也会放入查询缓存中。
MySQL客户端/服务器通信协议
MySQL客户端和服务器之间的通信协议是“半双工”的，这意味着在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时进行。这种通信方式造成了许多限制。
在多数连接MySQL的库函数（调用MySQL数据库的函数方法）默认一般是获得全部结果集并缓存到内存里。
当然有时候这种全部缓存的方法并不好，一个很大的结果集在缓存时会占有大量的时间和内存。不使用缓存，从服务器中获取数据，然后直接处理，不过这样会加大服务器的压力，服务器只有在查询完成后才能释放资源。
查询缓存（query cache）
MySQL会通过一个对大小写敏感的哈希查找实现对查询缓存中数据的查找，是一种精确的匹配查找。
查询优化处理
将SQL转化为一个执行任务包括“解析SQL、预处理、优化执行计划”。
语法解析器：MySQL通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”，MySQL解析器将使用MySQL语法规则验证和解析查询（关键字的顺序是否正确，验证引号是否能前后正确匹配）。
预处理器：预处理器会进一步的检查解析是否合法（检查数据表和数据列是否存在，解析名字、别名看它们是否有歧义）。
查询优化器
查询优化器是基于成本的优化器，它会尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最下的一个（可通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本）

上述语句预测了此count（*）操作大概需要做1.8个数据页的随机查找才能完成。
MySQL能够处理的优化类型：
1.重新定义关联表的顺序；
2.将外连接转化为内连接；
3.使用等价变化规则；可以合并和减少一些比较，还可以移除一些恒成立和恒不成立的判断。
4.优化count()、min()和max()；索引和列是否可为空通常可以帮助MySQL优化这类的表达式，如查找最小值，只需找到索引树最左边的第一条记录。
5.预估并转化为常数表达式；当MySQL检测到一个表达式可以转化为常数时，就会一直把该表达式作为常数进行优化处理。
6.覆盖索引扫描；当扫描的索引列包含所有查询中需要的使用的列时，MySQL就可以直接使用索引返回需要的数据。
7.子查询优化；
8.提前终止查询；如使用limit子句查找限制数量的数据。
9.等值传播；如果两个列的值通过等式关联，那么MySQL能够将其中一个列的where条件传递到另一个列上。
10.列表in()的比较；MySQL对in()列表进行优化，先对列表中的值进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件。
MySQL的排序优化：
MySQL除了利用索引进行排序外，还有一种排序方法叫文件排序（filesort）。
当需要排序的数据量小于“排序缓冲区”时，MySQL使用内存进行“快速排序”，当内存不够时，会将数据块进行分块，每个独立的块使用“快速排序”，并将各个块的排序结果放在磁盘上，然后对结果进行合并。
MySQL的两种排序算法：
两次传输排序（旧版本使用）：读取行指针和需要排序的字段（第一次），对其进行排序，然后根据排序结果读取所需要的数据行（第二次）。优点是：排序缓冲区能够容纳更多的数据行进行排序；缺点是：第二次读取会产生大量的随机I/O。
单词传输排序（新版本使用）：先读取查询所需的所有列，然后再根据给定列进行排序，最后直接返回排序结果。

MySQL不允许在同一个表同时进行查询和更新，所以我们在写这类语句时，先处理某个操作，然后关联此操作产生的临时表，相当于先运行子查询，后完成整个操作。

优化特定类型的查询

优化COUNT（）查询
count（*）：统计行数，比统计一般的列值个数要快很多。
简单的优化：通过修改条件语句，减少扫描的次数。（始终记住，计算count(*)是很快的，比计算所有带条件的统计都要快）
使用近似值：即count()结果可以用一个优化器估算出来的值代替。
优化关联查询
1.确保ON或者USING子句中的列上有索引，一般索引建立在最后个关联表上的相应列上。
2.确保任何时候的GROUP BY 和 ORDER BY 中的表达式只涉及到一个表上的列，这样MySQL才有可能使用索引来优化这个过程。
优化子查询
尽可能使用关联查询代替子查询。