MySQL运维与监控之：`MySQL`的`Query Cache`：其在`MySQL 8.0`中被移除的原因。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

好的，我们开始今天的讲座，主题是MySQL运维与监控中的一个重要组成部分：Query Cache，以及它在MySQL 8.0中被移除的原因。

Query Cache 的概念与工作原理

在MySQL 5.x 和 7.x 版本中，Query Cache 是一个非常重要的性能优化手段。它的核心思想是将 SELECT 查询的结果缓存在内存中，当相同的查询再次发生时，直接从缓存中返回结果，避免了重复的解析、优化和执行过程，从而大大提高了查询速度。

简单来说，Query Cache 的工作流程如下：

接收查询： MySQL 服务器接收到一个 SELECT 查询请求。
检查 Query Cache： 服务器首先会计算查询语句的哈希值，然后在 Query Cache 中查找是否存在匹配的哈希值。
缓存命中： 如果找到匹配的哈希值，并且用户权限没有变化，则直接从 Query Cache 中返回之前缓存的结果集。
缓存未命中： 如果没有找到匹配的哈希值，或者用户权限发生了变化，则执行正常的查询处理流程，包括解析、优化、执行等。
缓存结果： 如果查询成功执行，并且查询结果满足缓存条件（例如，结果集大小在限制范围内），则将查询语句的哈希值和结果集一起存储到 Query Cache 中。

Query Cache 的配置

在MySQL 5.x 和 7.x 中，可以通过以下几个关键参数来配置 Query Cache：

query_cache_type: 控制 Query Cache 的开启和关闭。
- 0 或 OFF: 关闭 Query Cache。
- 1 或 ON: 开启 Query Cache，缓存所有符合条件的查询。
- 2 或 DEMAND: 开启 Query Cache，但只缓存显式指定 SQL_CACHE 关键字的查询。
query_cache_size: 指定 Query Cache 的总大小（以字节为单位）。
query_cache_limit: 指定可以缓存的单个查询结果集的最大大小（以字节为单位）。超过这个大小的结果集不会被缓存。
query_cache_min_res_unit: 指定 Query Cache 分配的最小内存块大小（以字节为单位）。较小的值可以减少内存碎片，但可能会增加管理开销。

例如，以下 SQL 语句可以用来配置 Query Cache：

SET GLOBAL query_cache_type = 1;  -- 开启 Query Cache
SET GLOBAL query_cache_size = 64 * 1024 * 1024;  -- 设置 Query Cache 大小为 64MB
SET GLOBAL query_cache_limit = 2 * 1024 * 1024;  -- 设置单个查询结果集最大大小为 2MB

Query Cache 的优点

Query Cache 的主要优点是：

提高查询速度： 对于重复执行的查询，尤其是读取频繁但更新较少的表，可以显著提高查询速度。
降低服务器负载： 减少了服务器的 CPU 和 IO 负载，提高了服务器的整体性能。

Query Cache 的缺点和问题

尽管 Query Cache 有其优点，但它也存在一些显著的缺点和问题，这些问题最终导致了它在 MySQL 8.0 中被移除：

缓存失效： 只要表中的任何数据发生变化（包括 INSERT、UPDATE、DELETE 等操作），所有与该表相关的 Query Cache 都会失效。这意味着即使只有很小的数据变化，也会导致大量的缓存失效，从而降低 Query Cache 的命中率。
锁竞争： Query Cache 使用全局锁来保证数据的一致性。当多个客户端同时访问 Query Cache 时，可能会发生锁竞争，导致性能下降。尤其是在高并发的场景下，锁竞争问题会变得更加严重。
内存碎片： Query Cache 使用动态内存分配来存储查询结果。频繁的缓存和失效会导致内存碎片，降低内存利用率。虽然可以通过调整 query_cache_min_res_unit 参数来减少内存碎片，但并不能完全解决问题。
复杂性： Query Cache 的实现比较复杂，维护成本较高。而且，Query Cache 的行为有时难以预测，容易导致性能问题。
对写密集型应用的负面影响： 对于写密集型的应用，Query Cache 的缓存失效非常频繁，反而会降低性能。因为每次写操作都会导致相关的缓存失效，而缓存的创建和失效本身也需要消耗资源。
对大结果集的处理效率低： 当查询返回大量数据时，Query Cache 的缓存和检索效率会显著下降。因为需要分配大量的内存来存储结果集，并且在检索时需要进行大量的数据拷贝。
权限问题： Query Cache 会缓存查询结果，这意味着即使用户的权限发生了变化，仍然可能从缓存中返回旧的结果。为了解决这个问题，MySQL 需要在每次查询时检查用户的权限，增加了额外的开销。

Query Cache 在 MySQL 8.0 中被移除的原因

综合以上缺点和问题，MySQL 团队决定在 MySQL 8.0 中移除 Query Cache。主要原因包括：

性能瓶颈： 在高并发的场景下，Query Cache 的锁竞争问题严重影响了性能。
维护成本高： Query Cache 的实现复杂，维护成本较高。
替代方案： 随着硬件的不断发展，以及 MySQL 内部优化器的改进，出现了一些更好的替代方案，例如优化查询语句、使用索引、调整数据库配置等。这些方案可以提供更稳定、更高效的性能提升，而不需要承担 Query Cache 的风险和复杂性。
与新特性不兼容： Query Cache 与 MySQL 8.0 中的一些新特性（例如，InnoDB 的 undo log 优化）存在冲突。

替代方案：优化查询和利用索引

既然 Query Cache 已经被移除，那么我们应该如何优化 MySQL 的查询性能呢？以下是一些常用的替代方案：

优化查询语句：
- *避免使用 `SELECT `：** 只选择需要的列，减少数据传输量。
- 使用 EXPLAIN 分析查询： 了解查询的执行计划，找出潜在的性能瓶颈。
- 避免在 WHERE 子句中使用函数或表达式： 这会导致索引失效。
- 尽量使用 JOIN 代替子查询： JOIN 通常比子查询更高效。
例如，假设有一个 orders 表，包含 order_id, customer_id, order_date, total_amount 等列。以下是一个低效的查询语句：
```
SELECT * FROM orders WHERE YEAR(order_date) = 2023;
```
这个查询语句使用了 YEAR() 函数，导致 order_date 列上的索引失效。可以将其改写为：
```
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';
```
这样就可以利用 order_date 列上的索引，提高查询速度。
利用索引：
- 为经常用于查询的列创建索引： 索引可以大大加快查询速度。
- 选择合适的索引类型： 不同的索引类型适用于不同的场景。例如，B-tree 索引适用于范围查询，Hash 索引适用于等值查询。
- 定期维护索引： 索引可能会因为数据的变化而变得碎片化，需要定期进行维护（例如，重建索引）。
例如，假设经常需要根据 customer_id 查询 orders 表。可以为 customer_id 列创建一个索引：
```
CREATE INDEX idx_customer_id ON orders (customer_id);
```
这样就可以大大加快根据 customer_id 查询订单的速度。
使用连接池： 连接池可以减少数据库连接的创建和销毁开销，提高服务器的响应速度。
使用缓存： 虽然 MySQL 自身的 Query Cache 被移除了，但仍然可以使用其他缓存方案，例如 Memcached、Redis 等。可以将查询结果缓存在这些外部缓存中，提高查询速度。但需要注意缓存的一致性问题。
优化数据库配置：
- 调整 innodb_buffer_pool_size 参数： 增加 InnoDB 的缓冲池大小，可以提高数据读取速度。
- 调整 innodb_log_file_size 参数： 增加 InnoDB 的日志文件大小，可以提高写入速度。
- 调整 innodb_flush_log_at_trx_commit 参数： 控制 InnoDB 的日志刷新策略，可以影响写入性能和数据安全性。
例如，以下 SQL 语句可以用来调整 innodb_buffer_pool_size 参数：
```
SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024;  -- 设置 InnoDB 缓冲池大小为 8GB
```
使用预编译语句： 预编译语句可以减少 SQL 语句的解析和优化开销。例如，在Java中使用PreparedStatement。
垂直分割和水平分割： 将表分割成更小的，更易于管理的部分，可以提高查询和写入速度。

示例代码：使用 Redis 缓存查询结果

以下是一个使用 Redis 缓存 MySQL 查询结果的示例代码（使用 Python）：

import redis
import mysql.connector

# Redis 配置
redis_host = 'localhost'
redis_port = 6379
redis_db = 0

# MySQL 配置
mysql_host = 'localhost'
mysql_user = 'root'
mysql_password = 'password'
mysql_database = 'testdb'

# 创建 Redis 连接
redis_client = redis.Redis(host=redis_host, port=redis_port, db=redis_db)

# 创建 MySQL 连接
mysql_conn = mysql.connector.connect(host=mysql_host, user=mysql_user, password=mysql_password, database=mysql_database)
mysql_cursor = mysql_conn.cursor()

def get_data_from_cache_or_db(sql):
    """
    从 Redis 缓存中获取数据，如果缓存未命中，则从 MySQL 数据库中获取数据，并将结果缓存到 Redis 中。
    """
    cache_key = f"query:{sql}"  # 使用 SQL 语句作为缓存键

    # 从 Redis 缓存中获取数据
    cached_data = redis_client.get(cache_key)

    if cached_data:
        print("从 Redis 缓存中获取数据")
        # 将缓存的字节数据解码为字符串，然后解析为 Python 对象 (假设缓存的是 JSON 字符串)
        import json
        return json.loads(cached_data.decode('utf-8'))
    else:
        print("从 MySQL 数据库中获取数据")

        # 从 MySQL 数据库中获取数据
        mysql_cursor.execute(sql)
        data = mysql_cursor.fetchall()

        # 将数据缓存到 Redis 中
        import json
        redis_client.set(cache_key, json.dumps(data), ex=3600)  # 设置缓存过期时间为 1 小时 (3600 秒)
        return data

# 示例查询
sql = "SELECT * FROM employees WHERE department = 'Sales'"

# 获取数据
data = get_data_from_cache_or_db(sql)

# 打印结果
print(data)

# 关闭连接
mysql_cursor.close()
mysql_conn.close()

在这个示例中，我们使用 SQL 语句作为 Redis 缓存的键。当从 Redis 中获取到缓存的数据时，需要将字节数据解码为字符串，然后解析为 Python 对象。当从 MySQL 数据库中获取到数据时，需要将数据序列化为 JSON 字符串，然后存储到 Redis 中。 ex=3600 设置缓存过期时间为 1 小时。

总结

Query Cache 曾经是 MySQL 性能优化的重要工具，但由于其固有的缺陷和性能瓶颈，最终被 MySQL 8.0 移除。现在，我们需要更多地依赖于优化查询语句、使用索引、调整数据库配置等方式来提高 MySQL 的查询性能。外部缓存如Redis也是一个不错的选择。

发表回复 取消回复

发表回复取消回复