高性能MySQL--第六章 查询性能优化

1.查询速度

真正重要的是响应时间。查询需要在不同阶段耗费时间，包括网络、CPU计算、生成统计信息、执行计划、锁互相等待（互斥等待）等操作，向底层存储引擎检索数据的调用操作需要在内存操作、CPU操作和内存不足时会导致I/O操作耗时。上下文切换、系统调用耗时。

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

查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量来优化。

（1）确认应用程序是否在检索大量超过需要的数据。（太多行或太多列，给MySQL服务器带来额外的负担，并且增加来网络开销，也消耗来应用服务器的CPU和内存资源）（2）确认MySQL服务器层是否在分析大量超过需要的数据行。

2.1

分页查询，sql语句加LIMIT，防止返回大量不需要的数据。

多表关联时不要返回全部列，只选取需要的列。

使用缓存，将初次查询的数据缓存起来，需要的时候从缓存取出，避免多次重复查询。

2.2

衡量查询开销的3个指标：（1）响应时间（2）扫描的行数（3）返回的行数

这三个指标都会记录到MySQL慢查询日志中，检查慢查询日志是找出扫描行数过多的查询的好办法。

响应时间是两个部分之和：服务时间和排队时间。服务时间是数据库处理查询耗费的时间，排队时间是指服务器因为等待某些资源而没有真正执行查询的时间（可能是等I/O操作完成、等待行锁）。存储引擎的锁（表锁、行锁）、高并发资源竞争、硬件响应等因素都会影响响应时间。

EXPLAIN语句的TYPE列反映了访问类型。全表扫描、索引扫描、范围扫描、唯一索引查询、常数查询等，速度从慢到快，扫描的行数从大到小。如果查询没有办法找到合适的访问类型，最好增加一个合适的索引。索引让MySQL以最高效、扫描行数最少的方式找到需要的记录。

3.重构查询的方式

一个查询和多个查询：尽量写成一个查询返回多条数据，减少访问数据库的次数。但是现代网络速度已经很快，一个同意服务器上也能允许每秒超过10万的查询，MySQL内部每秒能够扫描内存中上百万行数据没所以可以将一个大查询分解为多个小查询。

切分查询：将大查询切分成小查询。例如定期清理大量数据，一个语句会导致一次锁住很多数据、占满整个事务日志】耗尽系统资源、阻塞很多小但很重要的查询。切分为多个小查询，一次删除10000个，可以尽可能减少MySQL复制的延迟，如果每次删除后暂停一下再删除，还可以将服务器上一次性的压力分散到一个很长的时间段里，降低对服务器的影响，减少删除时锁的持有时间。

分解关联查询：可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。（1）让缓存的效率更高。（2）执行单个查询可以减少锁的竞争。（3）在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。（4）提高查询本身的效率。（5）可以减少冗余记录的查询。（6）相当于实现了哈希关联，而非MySQL中的嵌套循环关联。

4.查询执行的基础

4.1MySQL客户端/服务端通信协议：半双工的，任一时刻，或者服务器向客户端发数据，或者客户端向服务器发数据，不能同时发送。

客户端用一个单独的数据包将查询传递给服务器。服务器响应给客户端的数据通常非常多，由多个数据包组成。服务端开始响应客户端请求时，客户端必须完整地接收整个返回结果，不能中途断开。多数连接MySQL的库函数都可以获得全部结果集并缓存到内存里，减少服务器的压力。

一个MySQL连接，或者说一个线程，任何时候都有一个状态，表示MySQL当前在做什么。SHOW FULL PROCESSLIST命令，返回的Command列表示当前状态。

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

Query线程正在执行查询或正在将结果返回给客户端。

Locked在MySQL服务器层，该线程正在等待表锁。

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

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

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

Sending data线程可能在多个状态之间传递数据，或正在生成结果集，或向客户端返回数据。

4.2查询缓存

4.3查询优化处理

4.3.1语法解析器和预处理。MySQL通过关键字将SQL语句进行解析，并生成一颗解析树。MySQL解析器使用MySQL语法规则验证和解析查询（关键字的使用是否正确）。预处理器则根据一些MySQL规则进一步检查解析树是否合法（检查数据表和列是否存在，解析名字和别名）。

4.3.2查询优化器。优化器将语法树转化为执行计划，选择最好的执行计划。MySQL使用基于成本的优化器，尝试预测一个查询使用某种执行计划时的成本，选择成本最小的。成本，如Last_query_cost的值。SHOW STATUS LIKE "Last_query_cost";Value值代表优化器认为需要做Value个数据页的随机查找才能完成上一条语句的查询。

重新定义关联表的顺序，表的关联并不总是按照查询中指定的顺序进行，决定关联顺序的是优化器。

将外连接转化成内连接。

使用等价变换规则，简化并规范表达式。

优化COUNT()  MIN()  MAX()，借助索引和列是否可为空。

可以预估并转化为常数表达式，当MySQL检测到一个表达式可以转化为常数时，会一直把该表达式作为常数处理。

覆盖索引扫描。

子查询优化，将子查询转化为效率更高的形式，减少多个查询多次对数据的访问。

提前终止查询，已满足查询要求则终止查询，如LIMIT。发现不成立的条件，立刻返回一个空结果。对特殊条件提前终止查询，如DISTINCT  NOT EXIST() LEFT JOIN。

等值传播，如果两个列的值通过等式关联，MySQL能把其中一列的WHERE条件传递到另一列上。

列表IN()，MySLQ将IN()其中的数据先进行排序，再通过二分查找确定列表中的值是否满足条件。

4.3.3执行计划，MySQL生成查询的一棵指令树，通过存储引擎执行完成这棵指令树并返回结果。

4.4查询执行引擎，MySQL的查询执行引擎根据执行计划完成整个查询，这里的执行计划是一个数据结构，而非字节码。

4.5返回结果给客户端

5MySQL查询优化的局限性

6查询优化器的提示（hint）

可以使用优化器的提示来控制最终的执行计划。

HIGH_PRIORITY ,  LOW_PRIORITY ,  DELAYED , STRAIGHT_JOIN , SQL_SMALL_RESULT , SQL_BIG_RESULT , SQL_BUFFER_RESULT , SQL_CACHE , SQL_NO_CACHE , SQL_CALL_FOUND_ROWS , FOR UPDATE , LOCK IN SHARE MODE , USE INDEX , IGNORE INDEX , FORCE INDEX , optimizer_search_depth , optimizer_prune_level , optimizer_switch 。

7优化特定类型的查询

7.1COUNT()查询，改变查询逻辑，使用SUM()，用EXPLAIN使用近似值，使用缓存，增加索引，增加汇总表

7.2关联查询：（1）确保ON或USING子句的列上有索引，通常只需要在关联顺序中的第二个表的相应列上创建索引。（2）确保任何GROUP BY 和 ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。

7.3子查询，尽可能使用关联查询来代替。

7.4GROUP BY 和 DISTINCT，使用索引优化。无法使用索引时，GROUP BY 使用临时表或文件排序来做分组。

7.5优化LIMIT分页，冗余表等。

7.6优化SQL_CALC_FOUND_ROWS

7.7优化UNION查询，除非确实需要服务器消除重复的行，否则一定要用UNION ALL（避免DISTINCT临时表的列）。

7.8静态查询分析

7.9使用用户自定义变量

优化原则：不做、少做、快速地做