查询性能优化——高性能MySQL

原文链接：https://blog.csdn.net/zly9923218/article/details/51489794

优化数据访问

查询性能低下的最基本的原因是访问的数据太多，对于低效的查询，可以从下面两个步骤来分析：

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

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

一些典型的情况：

（1） 查询不需要的记录。这样的查询上应该加上LIMIT

（2） 多表关联时返回了全部列。应该只取需要的列。

（3） 总是取出全部的列：SELECT *

（4） 重复查询需要的数据。较好的解决方案是使用数据缓存。

确认MySQL只返回了需要的数据之后，接下来应该看看查询是否扫描了过多的数据，最简单的衡量查询开销的三个指标如下：

（1）响应时间

（2）扫描的行数

（3）返回的行数

响应时间=排队时间+服务时间

理性情况下的扫描的行数和返回的行数应该是相等的。

访问类型：MySQL有好几种方式可以查找并返回一行结果，Explain的type列反应了访问类型，访问类型有全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用等，这些速度从慢到块，扫描的行数从大到小。Using Where表示MySQL将通过Where条件来筛选存储引擎返回的数据。MySQL能够以三种方式应用Where条件，从好到坏依次是：

（1）、在索引中使用Where来过滤数据，这是在存储引擎层实现的

（2）、使用了索引覆盖扫描（Extra列中Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果，这是在MySQL服务器层实现的，但是无需回表查询记录。

（3）、从数据表中返回数据，然后过滤不满足条件的记录，这是在MySQL服务器层实现的，MySQL需要先从数据库中读取记录然后过滤。

如果一个查询需要扫描大量的数据但是只返回少数的行，那么通常可以尝试下面的技巧去优化：

（1）、使用索引覆盖扫描，即把所有需要的列都放到索引中，这样存储引擎无需回表获取对应行就可以返回结果了。

（2）、该表库表结构，使用单独的汇总表

（3）、重写整个复杂的查询，让MySQL优化器能够以最优化的方式执行整个查询。

重构查询的方式

确定一个复杂查询还是多个简单查询更加有效

切分查询：

将一个完整的查询分散到多次小查询中（例如通过Limit）

查询执行的基础

MySQL执行查询的过程：

（1）客户端发送一条查询给服务器

（2）服务器先检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果，否则进入下一个阶段。

（3）服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划

（4）将结果返回给客户端。

MySQL客户端、服务器端通信协议

MySQL客户端和服务器端的通信协议是“半双工”的，在任何一个时刻，要么是服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生

当使用多数连接Mysql的库函数从Mysql中获取数据的时候，其结果看起来都像是从MySQL服务器获取数据，实际上都是从这个库函数的缓存中获取数据。多数情况下，这没有什么问题，但是如果需要返回一个很大的数据集的时候，这样做并不好，因为库函数会花费很多时间和内存来存储所有的结果集，如果能够尽早处理这些结果集，就能大大减少内存的消耗，这种情况下可以不适用缓存来处理记录结果而是直接处理，这样做的缺点是，对于服务器来说，需要查询完成之后才能释放资源，所以在和客户端交互的过程中，服务器的资源都是被这个查询所占用的。

<?php
    $link= mysql_connect('', '', '');
    $result</span>= mysql_query(<span class="hljs-string">"SELECT * FROM table"</span>, <span class="hljs-variable">$link);
    while($row</span> = mysql_fetch_array( <span class="hljs-variable">$result ) ){
              //dosomething
    }
?>
   
   
1
2
3
4
5
6
7

这段代码看起来像是当你需要的时候，才循环从服务器端取出数据，而实际上，在上面的代码中，在调用mysql_query()的时候，PHP就已经将整个结果集缓存到内存中了，而while循环仅仅是从这个缓存中逐行读取数据。如果用Mysql_unbuffed_query代替mysql_query，则不会缓存结果。

查询状态

对于一个MySQL连接（一个线程），任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。可以用Show (full) processlist查询。

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

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

Locked: 在MySQL服务器层，该线程正在等待表锁，在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。

更多的信息可以查看MySQL的官方手册。

查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中这个查询缓存中的数据，这个检查是通过一个对大小写敏感的哈希查找实现的，查询和查询缓存中即使只有一个字节不同，也不会匹配缓存结果。

TODO： query cache的更多细节。

查询优化处理

语法解析器和预处理

MySQL通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”，MySQL解析器将使用MySQL语法规则进行验证和解析查询（语法分析），预处理器则会根据一些MySQL规则进一步检查解析树是否合法（语义分析），之后会验证权限。

查询优化器

优化器将查询转化为执行计划，优化器的作用是找到最好的执行计划。

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划的成本，并选择其中成本最小的一个。通过通过查询当前会话的last_query_cost的值来得知MySQL计算的当前查询的成本。优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘I/O

MySQL可以处理的优化类型：

（1） 重新定义关联表的顺序

（2） 将外连接转化为内连接

（3） 使用等价变化规则

（4） 优化COUNT(), MIN()和MAX() :例如要查找一个最小值，可以查询B-Tree索引的最左端的记录，如果要查询一个最大值，也只需要获取B-Tree索引的最后一条记录。

（5） 预估并转化为常数表达式

（6） 覆盖索引扫描：当索引中的列包含了所有查询中使用的列时，MySQL可以使用覆盖索引返回需要的数据，而无需查询对应的数据行。

（7） 子查询优化。

（8） 提前终止查询：当发现已经满足查询需求的时候，MySQL总是能够立刻终止查询，一个典型的例子就是当使用LIMIT子句的时候

（9） 等值传播：如果两个列通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另外一个列上。

（10） 列表IN的优化。在很多数据库系统中，IN()完全等价于多个OR条件的子句，因为这两者是完全等价的。在MySQL中，会对IN列表中的数据进行排序，然后通过二分查找的方式确定列表中的值是否满足条件，对于IN列表中有大量取值的时候，MySQL的处理速度将会更快。

MySQL中如何执行关联查询

当前MySQL关联执行的策略很简单：对任何关联都执行嵌套循环关联操作，现在一个表中循环取出单条数据，然后再嵌套到下一个表中寻找匹配的行，如此下去，直到找到所有表中匹配的行为止，然后根据各个表中匹配的行，返回查询中需要的各个列。

执行计划

对某个查询执行EXPLAIN EXTENDED后，再执行SHOW WARNINGS，就可以看到重构出的查询。

关联优化查询器

决定最佳的表连接的顺序。可以用SELECT STRAIGHT_JOIN强制按照查询的顺序进行表关联。

排序优化

无论如何，排序都是一个成本很高的操作，所以从性能角度考虑，应该尽量避免排序或者尽可能避免对大量数据进行排序。当不能使用索引生成排序结果的时候，MySQL需要进行排序，如果数据小则在内存中排序，如果数据量大则需要使用磁盘排序，MySQL将这个过程统一称为文件排序。

MySQL使用两种排序算法：旧版本使用“二次传输排序”，新版本使用“单次传输排序”

（1）两次传输排序：

读取行指针和需要排序的字段，对其进行排序，然后根据排序结果去读取所需要的数据行。这需要两次数据传输，第二次读取的时候，因为是读取的排序后的所有记录，这会产生大量的随机I/O，所以两次数据传输的成本非常高。不过这样做的优点是：排序的时候尽量存储较少的数据，可以再内存中容纳尽量多的行数进行排序

（2）单次传输排序：

先读取需要的所有列，然后根据给定列进行排序，最后直接返回排序结果，因为不需要从数据表中读取两次数据，对于I/O密集型的应用，这样的效率高了不少。相比两次数据传输排序，这个算法只需要一次顺序I/O读取所有的数据，而无需任何的随机I/O

查询执行引擎

查询执行的最后一个阶段时将结果返回给客户端，即使客户端不需要返回结果，MySQL依然会返回一个这个查询的一些信息，如该查询影响到的行数，如果查询可以被缓存，那么MySQL在这个阶段也会将结果存放到查询缓存中。MySQL将结果集返回是一个增量、逐步返回的过程。

MySQL查询优化器的局限性

关联子查询、UNION的限制（无法将限制条件从外层下推到内层）、索引合并优化、等值传递、并行执行、哈希关联、松散索引扫描，最大值和最小值优化。

优化特定类型的查询

优化COUNT()的查询

COUNT可以统计行数和特定列的数量，统计列数量的时候，不会包含NULL。没有任何条件的COUNT(*)对于MyISAM引擎而言比较快（MYISAM会维护一个表行数的变量）

在一条查询中同时统计一个列不同值的数量：

SELECT SUM(IF(color='blue',1,0)) AS blue, SUM(IF(color='red',1,0))AS red FROM items.
   
   
1

也可以用COUNT而不是SUM()实现同样的目的：

SELECT count(color='blue' OR NULL) AS blue, count(color='red'OR NULL) AS red FROM items;
   
   
1

或者去掉IF表达式：

SELECT SUM(color='blue') AS blue, SUM(color='red') AS redFROM items.
   
   
1

优化关联查询

（1）、确保ON或者Using子句的列上有索引。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表中的相应列上添加索引。

（2）、确保任何的GROUP和ORDER BY中的表示式只设计其中一个表中的列，这样MySQL才有可能使用索引来优化这个过程。

优化子查询

尽可能使用关联查询替代，至少当前的MySQL版本是这样。

优化Group BY和DISTINCT

当无法使用索引的时候，MySQL使用两种策略完成分组：使用临时表或者文件排序

优化LIMIT分页

使用延迟关联优化LIMIT分页，避免偏移量较大的时候的性能低下

SELECT film_id, xxx FROM film ORDER BY title LIMIT 50, 5;
   
   
1

化为：

SELECT film_id FROM film JOIN (
    SELECT film_id FROM film ORDER BY title LIMIT 50,5
) AS lim USING(film_id);
   
   
1
2
3

如果预先知道了边界，也可以通过边界计算。

优化UNION操作

MySQL总是通过创建临时表的方式来执行UNION操作，经常需要通过手工将WHERE, LIMIT, ORDER BY等字句下推到UNION的各个子查询中，以便于优化器充分利用这些条件进行优化。除非确实需要服务器消除重复的行，否则一定需要ALL选项，如果没有ALL选项，MySQL会给临时表加上DISTINCT选项，会导致对整个临时表做唯一性检查，这样做的代价很高。实际上，即使有ALL选项，MySQL依然会使用临时表存储结果。

使用用户自定义变量

特点：

（1）、使用自定义变量的查询，无法使用查询缓存。

（2）、不能在使用常量或者标志符的地方使用自定义变量，例如表名、列名和Limit子句中。

（3）、用户自定义变量的生命周期是在一次连接中有效，所以不能用他们来做连接间的通信

（4）、如果使用数据池或者持久化连接，则可以实现一定程度的交互

（5）、5.0之前的版本中，自定义变量是大小写敏感的。

（6）、不能显式地生命自定义变量的类型。如果希望变量是整形，初始化0，如果希望是浮点型，初始化为0.0，如果希望是字符串，初始化为”,MySQL的自定义变量是一个动态类型。

（7）、MySQL优化器可能会在某些场景下将这些变量优化掉。

（8）、赋值的顺序和赋值的时间点并不总是固定的，这依赖于优化器的决定

（9）、赋值符号:=的优先级非常低

（10）、使用未定义变量不会产生任何语法错误。

.

案例学习（TODO）

（1）、使用MySQL构建一个队列

（2）、计算两点之间的距离

（3）、使用用户自定义函数。