高性能MySQL学习——提高查询性能

提高查询性能

MySQL 查询优化器

SELECT 执行过程

如何提高 MySQL 的查询性能，首先你需要了解查询优化器处理 SQL 的全过程。以 SELECT 的 SQL 的执行过程为例，如下图所示。

1. 客户端发送一条 SELECT 查询给服务器；
2. 服务器先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段；
3. 服务器进行 SQL 解析、预处理、再由查询优化器生成对应的执行计划；
4. MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询；
5. 将结果返回给客户端，同时也会放入查询缓存中。

查询优化案例

MySQL 查询优化器不是完美的，MySQL 采用基于开销的优化器，以确定处理查询的最佳方式，也就是说执行查询之前，都会先选择一条自以为最优的方案。在很多情况下，MySQL 能够计算最佳的可能查询计划，但在某些情况下，MySQL 没有关于数据的足够信息，或者是提供太多的相关数据信息，它所采用的可能并非就是事实上的最优方案。这里举了两个例子来说明。

首先来看第一个案例，为什么 Range 执行效率差呢？我们先来看看 Range 类查询的执行计划流程。

1. 根据查询条件计算所有的 possible keys。
2. 计算全表扫描代价（cost_all）。
3. 计算最小的索引范围访问代价（这一步很关键，直接决定了 Range 的查询效率），它有三步：

• 对于每一个 possible keys（可选索引），调用 records_in_ranges 函数计算范围中的rows；
• List item
• 根据 rows，计算二级索引访问代价；
• 获取 cost 最小的二级索引访问代价（cost_range）。

4. 选择执行最小化访问代价的执行计划。如果 cost_all <= cost_range，则全表扫描，否则索引范围扫描。

对于这个例子，Range 使用了 records_in_range 函数估算每个值范围的 rows，结果依赖于possible_keys；possible_keys 越多，随机 IO 代价越大，Range 查询效率越差。所以，索引不是越多越好，相反，我们应该尽量减少 possible_keys，减少 records_in_range 从而减少 IO 的消耗。这里推荐两个工具，用 pt-index-usage 工具来删除冗余索引，用 pt-duplicate-key-checker 工具来删除重复索引。
再来看看这个例子，这样的一个表结构（优化后的），优化前有一个索引 idx_global_id。图中的这条 SQL 语句的 where 条件包括一个 sub_id 的等值查询和一个 global_id 的范围查询。执行一次需要 2.37 秒。从下一页的执行计划中，我们可以看到虽然查询优化器使用了唯一索引 uniq_subid_globalid，但是由于 idx_global_id 的干扰，实际只使用了前面的 4 个长度就 access，剩余 8 个长度都被 filter 了。