MySQL 查询性能优化

查询执行过程

如图所示，一条查询语句的执行过程大致如下：

客户端和服务器连接后发送一条查询语句给服务器。
服务器首先查询缓存，如果命中缓存就立刻返回存储在缓存中的结果，否则进入下一步。
经过解析器对查询语句分析、预处理器预处理后，优化器会生成执行计划及索引选择。
服务器根据生成的执行计划，调用存储引擎的 API 来执行查询。
结果返回。

MySQL 解析器将使用 MySQL 语法规则验证和解析查询。预处理器则根据一些 MySQL 规则进一步检查解析树是否合法，例如检查数据表和数据列是否存在，之后会验证权限。查询优化器的作用是根据存储引擎提供的统计信息找出一个最优的执行计划。

查询性能优化

MySQL 中可以通过Explain语句的结果来分析和优化查询语句。查询性能的常用方式为优化数据访问和重构查询。

优化数据访问

减少请求的数据量

只返回必要的列：最好不要使用SELECT *语句。
只返回必要的行：使用LIMIT语句来限制返回的数据。
缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

MySQL 中的缓存参数：

query_cache_type：为 ON 时开启，为 OFF 关闭，为 DEMAND 时则只有查询语句中有 sql cache 时才使用缓存；
query_cache_size: 缓存的内存空间；
query_cache_min_res_unit: 分配内存块的最小单；
query_cache_limit：缓存可使用的内存最大值；

减少服务器端扫描的行数

最有效的方式是使用索引来覆盖查询。

重构查询

切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
查询分解后，执行单个查询可以减少锁竞争；

优化 Limit 分页

在使用 Limit 语句的时候，如果偏移量过大，例如LIMIT 10000, 20，那么 MySQL 就要查询 10200 条数据，然后丢弃前 10000 条数据而只返回最后的 20 条记录，这导致 MySQL 耗费大量随机 I/O 在前 10000 条不被使用的数据上，代价非常高。要优化这个问题有两种技巧：延迟关联和书签。

延迟关联

延迟关联不查询所有的列，只需要根据一次关联操作返回所需要的列。例如下面的这条查询语句：

1	SELECT * FROM tableName ORDER BY id LIMIT 500000,2;

没有使用覆盖索引的情况下，需要回表查询 500002 条记录且只返回最后 2 条。如果我们使用覆盖索引只查询 ID 列：

1	SELECT id FROM tableName ORDER BY id LIMIT 500000,2;

因为索引已经覆盖了查询的需求，所以查询的时间会大大缩短。那如果要查询所有的列，则可以使用关联操作改写：

-- 使用子查询
SELECT * FROM tableName
WHERE id >= (SELECT id FROM tableName ORDER BY id LIMIT 500000 , 1)
LIMIT 2;

-- 使用 JOIN 分页的方式
SELECT *
FROM tableName AS t1
JOIN (SELECT id FROM tableName ORDER BY id LIMIT 500000, 1) AS t2
WHERE t1.id > t2.id ORDER BY t1.id LIMIT 2;

可以发现，子查询中能使用覆盖索引，因此在索引结构中就能获取到需要访问的记录而无需回表，之后再根据关联列回表查询需要的所有列。

书签

LIMIT和OFFSET的问题，其实是OFFSET的问题，它会导致 MySQL 扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。例如：

endNum = (i + 1)*500;
select id,content from test_table 
where id > 
(select id from test_table order by id asc limit endNum,1)  
limit 500

与原查询语句对比，原语句需要跨越大量数据块并取出，优化后基本通过直接根据索引字段定位再取出相应内容，效率自然提升。