MySQL优化器与执行计划之：`MySQL`的`Query Cache`：其在`MySQL 8.0`中被移除的原因和影响。

好的，我们开始今天的讲座。

MySQL 优化器与执行计划之：MySQL 的 Query Cache (已移除)

大家好，今天我们来聊聊 MySQL 优化器和执行计划中的一个重要组成部分，也是一个已经成为历史的功能：Query Cache。 重点是为什么它在 MySQL 8.0 中被移除，以及移除前后的影响。

1. 什么是 Query Cache？

Query Cache，顾名思义，是 MySQL 用于缓存查询结果的机制。当 MySQL 服务器接收到一个 SELECT 查询时，它会首先检查 Query Cache 中是否存在该查询对应的结果。如果存在（也就是所谓的 "Cache Hit"），服务器会直接从 Cache 中返回结果，而无需执行实际的查询操作。 这样可以极大地提高查询效率，特别是对于那些频繁执行且数据变化不大的查询。

2. Query Cache 的工作原理

Query Cache 的工作原理可以简单概括为以下几个步骤：

1. 接收查询： MySQL 服务器接收到一个 SELECT 查询请求。
2. 查询 Cache： 服务器计算查询语句的哈希值（包括查询语句的文本、数据库、协议版本等），并在 Query Cache 中查找是否存在该哈希值对应的缓存结果。
3. Cache Hit 或 Miss：
   • Cache Hit： 如果找到匹配的缓存结果，服务器会检查缓存结果是否仍然有效。如果有效，则直接从 Cache 中返回结果。
   • Cache Miss： 如果没有找到匹配的缓存结果，或者缓存结果已失效，则服务器会执行实际的查询操作。
4. 执行查询： 服务器执行实际的查询操作，从存储引擎中获取数据。
5. 缓存结果： 如果查询能够被缓存（需要满足一些条件，比如查询中不能包含用户自定义变量、临时表等），服务器会将查询结果和对应的哈希值存储到 Query Cache 中。
6. 返回结果： 服务器将查询结果返回给客户端。

3. Query Cache 的配置参数

在 MySQL 5.x 和 7.x 版本中，可以通过以下几个关键的系统变量来配置 Query Cache：

系统变量	描述
query_cache_type	控制 Query Cache 的启用状态。可以设置为 ON (启用)，OFF (禁用)，或者 DEMAND (只有使用 SQL_CACHE 指示符的查询才会被缓存)。
query_cache_size	指定 Query Cache 的总大小，单位为字节。
query_cache_limit	指定可以缓存的单个查询结果的最大大小，单位为字节。如果查询结果超过这个限制，则不会被缓存。
query_cache_min_res_unit	指定 Query Cache 中分配的最小内存块大小，单位为字节。这个参数影响着 Cache 的碎片化程度。
query_cache_wlock_invalidate	决定当表被 WRITE 锁定时，是否使该表相关的 Query Cache 失效。

例如，要启用 Query Cache 并设置其大小为 64MB，可以使用以下 SQL 语句：

SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 67108864; -- 64MB

4. Query Cache 的优点

Query Cache 的主要优点在于：

• 提高查询性能： 对于频繁执行的查询，如果命中 Cache，可以避免实际的查询操作，从而大大提高查询速度。
• 降低服务器负载： 减少了对存储引擎的访问，降低了 CPU 和 I/O 负载。

5. Query Cache 的缺点

尽管 Query Cache 有其优点，但它也存在一些严重的缺点，这些缺点最终导致了它在 MySQL 8.0 中被移除。

• 细粒度锁： Query Cache 使用全局锁进行并发控制。 即使是读取缓存，也需要获取锁。在并发较高的场景下，这会成为性能瓶颈。 任何对表的写操作都会使所有与该表相关的缓存失效，这也会导致大量的锁竞争。
• 维护成本高： Query Cache 的维护成本很高。 每次有数据更新时，都需要使相关的缓存失效。 这需要服务器维护一个复杂的依赖关系，并且会消耗大量的 CPU 资源。 特别是对于高并发的写入操作，这会成为一个严重的性能问题。
• 碎片化： Query Cache 容易产生碎片。 由于缓存的查询结果大小不一，并且缓存的失效和插入是随机的，因此 Query Cache 中会产生大量的碎片。 这会导致内存利用率降低，并且会影响 Cache 的查找效率。
• 不适用于所有查询： Query Cache 并不适用于所有类型的查询。 例如，包含用户自定义变量、临时表、或者使用 NOW() 等函数的查询是无法被缓存的。 因此，Query Cache 的命中率可能并不高。
• 复杂性： Query Cache 的行为比较复杂，容易引起混淆。 例如，即使查询语句完全相同，如果查询所使用的数据库不同，或者客户端的协议版本不同，也会导致 Cache Miss。 这使得 Query Cache 的行为难以预测，并且容易导致性能问题。

6. Query Cache 被移除的原因

综合以上缺点，MySQL 团队最终决定在 MySQL 8.0 中移除 Query Cache。 主要原因如下：

• 性能瓶颈： 在高并发场景下，Query Cache 的全局锁成为一个严重的性能瓶颈。
• 维护成本高： Query Cache 的维护成本很高，特别是对于高并发的写入操作。
• 效果不明显： 现代应用程序通常使用更高级的缓存技术，例如应用层缓存或者 Redis 等，Query Cache 的作用已经越来越小。
• 复杂性： Query Cache 的行为比较复杂，容易引起混淆。

7. Query Cache 移除的影响

Query Cache 被移除后，对应用程序的影响主要体现在以下几个方面：

• 性能变化： 对于那些依赖 Query Cache 的应用程序，可能会出现性能下降。 但是，对于那些在高并发场景下使用 Query Cache 的应用程序，性能可能会有所提升，因为避免了全局锁的竞争。
• 配置变化： 相关的配置参数（例如 query_cache_type、query_cache_size 等）已经被移除，不能再使用。
• 应用程序代码变化： 如果应用程序中有使用 SQL_CACHE 指示符，需要将其移除，否则会报错。

8. 替代方案

Query Cache 被移除后，可以使用以下几种替代方案来提高查询性能：

• 应用层缓存： 在应用程序中使用缓存，例如使用 Redis 或 Memcached。 这种方式可以更加灵活地控制缓存的粒度，并且可以避免 MySQL 的全局锁竞争。
• 连接池： 使用连接池可以减少数据库连接的创建和销毁开销，从而提高查询性能。
• 优化 SQL 查询： 通过优化 SQL 查询语句，例如添加索引、避免全表扫描等，可以提高查询效率。
• 使用更快的硬件： 使用更快的 CPU、内存和存储设备可以提高数据库的整体性能。
• 查询重写插件： 某些第三方插件，例如 ProxySQL，可以实现类似 Query Cache 的功能，但具有更高的性能和更好的可配置性。

9. 示例

为了更直观地理解 Query Cache 的作用和影响，我们来看一个简单的示例。

假设我们有一个名为 users 的表，其中包含 id、name 和 age 三个字段。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    age INT
);

INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Charlie', 35);

现在，我们执行以下查询语句：

SELECT * FROM users WHERE age > 28;

在启用 Query Cache 的情况下，第一次执行该查询时，MySQL 会执行实际的查询操作，并将结果缓存到 Query Cache 中。 后续再次执行该查询时，如果 Cache 命中，则会直接从 Cache 中返回结果，而无需执行实际的查询操作。

为了验证 Query Cache 的作用，我们可以使用 SHOW STATUS 命令来查看 Query Cache 的状态信息。

SHOW STATUS LIKE 'Qcache%';

该命令会显示以下一些关键的指标：

变量名	描述
Qcache_free_blocks	Query Cache 中空闲的内存块数量。
Qcache_free_memory	Query Cache 中空闲的内存大小，单位为字节。
Qcache_hits	Query Cache 命中的次数。
Qcache_inserts	插入到 Query Cache 中的查询结果数量。
Qcache_lowmem_prunes	由于内存不足而从 Query Cache 中删除的查询结果数量。
Qcache_not_cached	由于各种原因而没有被缓存的查询数量，例如查询中包含用户自定义变量、临时表等。
Qcache_queries_in_cache	Query Cache 中缓存的查询数量。
Qcache_total_blocks	Query Cache 中总的内存块数量。

通过观察这些指标，我们可以了解 Query Cache 的使用情况，例如 Cache 命中率、内存利用率等。

10. 代码示例：模拟应用层缓存

以下是一个简单的 Python 代码示例，演示了如何使用应用层缓存来提高查询性能。

import mysql.connector
import time

# 缓存字典
cache = {}

def get_data_from_db(query):
    """从数据库中获取数据"""
    try:
        mydb = mysql.connector.connect(
            host="localhost",
            user="yourusername",
            password="yourpassword",
            database="yourdatabase"
        )
        mycursor = mydb.cursor()
        mycursor.execute(query)
        result = mycursor.fetchall()
        mydb.close()
        return result
    except Exception as e:
        print(f"数据库错误: {e}")
        return None

def get_data_with_cache(query):
    """从缓存或数据库中获取数据"""
    if query in cache:
        print("从缓存中获取数据")
        return cache[query]
    else:
        print("从数据库中获取数据")
        data = get_data_from_db(query)
        if data:
            cache[query] = data  # 将数据添加到缓存
        return data

# 示例用法
query = "SELECT * FROM users WHERE age > 28;"

# 第一次查询（从数据库）
start_time = time.time()
result1 = get_data_with_cache(query)
end_time = time.time()
print(f"第一次查询结果: {result1}")
print(f"第一次查询耗时: {end_time - start_time:.4f}秒")

# 第二次查询（从缓存）
start_time = time.time()
result2 = get_data_with_cache(query)
end_time = time.time()
print(f"第二次查询结果: {result2}")
print(f"第二次查询耗时: {end_time - start_time:.4f}秒")

在这个示例中，我们使用一个字典 cache 来模拟应用层缓存。 当执行查询时，首先检查缓存中是否存在该查询的结果。 如果存在，则直接从缓存中返回结果。 否则，从数据库中获取数据，并将结果添加到缓存中。

11. 实践建议

• 理解 Query Cache 的局限性： 在 MySQL 5.x 和 7.x 中使用 Query Cache 时，要充分理解其局限性，避免在高并发场景下使用。
• 监控 Query Cache 的状态： 使用 SHOW STATUS 命令来监控 Query Cache 的状态，例如 Cache 命中率、内存利用率等。
• 选择合适的替代方案： 在 MySQL 8.0 中，要选择合适的替代方案来提高查询性能，例如应用层缓存、连接池、优化 SQL 查询等。
• 性能测试： 在应用任何优化方案之前，一定要进行充分的性能测试，以确保优化方案能够真正提高性能。

MySQL 8.0 的变化：告别 Query Cache，拥抱新策略

MySQL 8.0 移除 Query Cache 是一个重要的变化。 虽然在某些情况下可能会导致性能下降，但从长远来看，这是一个正确的决定。 由于 Query Cache 存在许多固有的问题，移除它可以简化 MySQL 的架构，并且为未来的优化提供了更大的空间。 开发者应该拥抱新的优化策略，例如应用层缓存和 SQL 查询优化，以提高查询性能。