MySQL笔记之查询优化

未分类

当我们的库表结构设计合理，索引优化高效，但如果查询语句一塌糊涂的话，前面的努力将不能在性能上带给我们丝毫的提升，可见查询优化也是至关重要。

在每个慢日志中耗时的查询计划中，很多都包含一些不必要的额外操作，某些操作被额外执行多次，某些操作执行的太慢等等问题，优化查询的目的就是减少甚至消除此类操作。

基础

查询性能低下的一个普遍原因就是访问的数据过多。大部分性能低下的查询都可以通过减少数据访问量来进行优化。

通过两步分析：

• 确认应用程序是否试图获取大量超过需要的数据，包括行和列
• 确认MySQL服务器层是否因为查询而在分析大量超过需要的数据

一些新手常见的错误是查询出大量的数据然后在应用层面再丢掉多余的数据，这会给MySQL服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU和内存。另外还有一种情况方式就是习惯性的使用 'SELECT *' ，取出全部的列，会使查询优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗。所以当不是必要的时候或者你很清楚这样做的影响时都要去思考你是不是真的需要返回全部的列。

那么如何确定MySQL是否在扫描额外的行呢？对于MySQL最简单的衡量查询开销的三个指标就是：响应时间，扫描行数，返回行数。这三个指标都会记录到MySQL的慢日志中。有一点需要注意的就是响应时间是服务时间和排队时间之和

响应时间 = 服务时间 + 排队时间
# 服务时间指数据库处理查询真正花费的时间。
# 排队时间指服务器因为等待某些资源而没有真正执行查询的时间

因此诸如存储引擎的锁、高并发资源竞争、硬件响应等诸多因素都会影响响应时间，所以响应时间并没有什么一致的规律。

在评估查询开销的时候，需要考虑查询成本，有些访问可能需要扫描很多行才能返回一行结果，有些访问方式也可能无需扫描就能返回结果，这时候就能明白索引对于查询优化是如此的重要了，没错，索引让MySQL以最高效、扫描行数最少的方式查找记录。

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

• 索引结构中使用WHERE条件来过滤不匹配的记录，由于这是在存储引擎层面完成的故效率最高
• 使用索引覆盖扫描来返回记录，直接从索引中过滤不需要的记录并返回。这种情况当EXPLAIN时在Extra列中将会出现 Using index ，这是在服务器层完成的，但是却无须查表
• 从数据表中返回数据，然后过滤不满足条件的记录。这种情况当EXPLAIN时在Extra列出现Using Where，这也是在服务器层完成的，但MySQL需要先从数据表读出记录然后过滤

查询的执行

当希望MySQL以更高的性能运行查询时，最好的办法就是弄清楚MySQL是如何优化和执行查询的。

1. client发送一条查询给server
2. server先检查缓存，如果命中缓存就直接返回，否则进入下一步
3. server经过解析、预处理、查询优化生成执行计划
4. 通过查询引擎调用存储引擎的API来执行查询

MySQL client与server之间的通信协议是半双工的，在任意一个时刻，要么server向client发送数据，要么client向server发送数据，这两个动作不能同时发生。