MySQL编程进阶之：`SQL_NO_CACHE`与`SQL_CALC_FOUND_ROWS`的性能陷阱与替代方案。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

大家好，我是老码农，今天咱们聊聊MySQL里两个挺有意思，但用不好容易掉坑里的家伙：SQL_NO_CACHE 和 SQL_CALC_FOUND_ROWS。

开场白：MySQL世界的二面性

MySQL这玩意儿，就像一把瑞士军刀，功能强大，但用不好也容易伤着自己。很多时候，我们觉得加个小玩意儿就能解决问题，但殊不知却挖了个大坑给自己跳。今天咱们就来扒一扒这两个“小玩意儿”的底裤，看看它们到底藏了些什么秘密。

第一部分：SQL_NO_CACHE：别迷信“关掉缓存就更快”

先来说说SQL_NO_CACHE。顾名思义，它的作用是告诉MySQL：“这条SQL别走缓存！直接给我跑一遍！” 乍一看，这好像是个性能优化的利器： “每次都从数据库拿最新鲜的数据，多好！” 但现实往往是残酷的。

1.1 SQL_NO_CACHE的真实面目

SQL_NO_CACHE其实并不是什么高科技，它只是绕过了MySQL的query cache。 Query cache这东西呢，会把执行过的SQL语句和结果缓存起来，下次再执行同样的SQL，直接从缓存里拿结果，速度嗖嗖的。

但是，query cache有个致命的缺点： 只要表里任何数据发生变化，所有和这张表相关的query cache都会失效！ 这就像一个定时炸弹，你不知道什么时候会爆炸，然后所有的查询都慢下来。

所以，MySQL 5.7之后，query cache就被官方deprecated了，到了MySQL 8.0，直接被移除了。这玩意儿实在是太鸡肋了，还不如不用。

1.2 为什么不建议使用SQL_NO_CACHE

绕过缓存，直接增加数据库压力： 每次都从数据库拿数据，数据库的CPU、IO压力会蹭蹭往上涨。
Query Cache本身就不靠谱： 都说了query cache不稳定，容易失效，用了还不如不用。
代码可读性差： 别人看到SQL_NO_CACHE，还得琢磨一下你是不是有什么特殊用意，增加了维护成本。

1.3 替代方案：优雅地处理缓存问题

既然SQL_NO_CACHE不好用，那我们应该怎么做呢？

拥抱InnoDB的Buffer Pool： InnoDB存储引擎有自己的Buffer Pool，它会把热点数据缓存到内存里，比query cache靠谱多了。你只需要确保你的SQL语句写得好，索引建得好，InnoDB自然会把数据缓存起来。
使用应用层缓存： 比如Redis、Memcached之类的。把热点数据缓存到应用层，可以大大减轻数据库的压力。这种方式更加灵活，可以根据业务需求定制缓存策略。

1.4 代码示例：应用层缓存

import redis

# 连接Redis
redis_client = redis.Redis(host='localhost', port=6379, db=0)

def get_user_name(user_id):
    """
    先从Redis缓存里找，找不到再去数据库查
    """
    cache_key = f"user_name:{user_id}"
    user_name = redis_client.get(cache_key)

    if user_name:
        print("从Redis缓存获取")
        return user_name.decode('utf-8')  # Redis里存的是bytes，要decode一下

    # 如果缓存里没有，就从数据库查
    # 假设你已经有了连接数据库的代码（比如用pymysql）
    import pymysql
    connection = pymysql.connect(host='localhost',
                                 user='your_user',
                                 password='your_password',
                                 database='your_database',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)

    try:
        with connection.cursor() as cursor:
            sql = "SELECT name FROM users WHERE id = %s"
            cursor.execute(sql, (user_id,))
            result = cursor.fetchone()
            if result:
                user_name = result['name']
                # 查到之后，把数据放到Redis里
                redis_client.set(cache_key, user_name, ex=3600)  # 设置过期时间为1小时
                print("从数据库获取并放入Redis缓存")
                return user_name
            else:
                return None
    finally:
        connection.close()

# 测试一下
user_id = 123
user_name = get_user_name(user_id)
if user_name:
    print(f"User name for ID {user_id}: {user_name}")
else:
    print(f"User not found with ID {user_id}")

这个例子演示了如何使用Redis作为应用层缓存。先从Redis里找数据，找不到再去数据库查，查到之后再放到Redis里。这样可以有效地减轻数据库的压力，提高查询速度。注意设置合理的缓存过期时间，避免数据过期。

第二部分：SQL_CALC_FOUND_ROWS：一个美丽的谎言

接下来，我们来看看SQL_CALC_FOUND_ROWS。这个家伙的作用是：在使用了LIMIT语句的同时，还能获取到符合查询条件的总记录数。

2.1 SQL_CALC_FOUND_ROWS的工作原理

当你执行类似这样的SQL语句：

SELECT SQL_CALC_FOUND_ROWS * FROM users WHERE age > 18 LIMIT 10;
SELECT FOUND_ROWS();

MySQL会做两件事：

执行SELECT SQL_CALC_FOUND_ROWS * FROM users WHERE age > 18 LIMIT 10;，返回前10条符合条件的数据。
顺便计算一下符合WHERE age > 18条件的总共有多少条数据。

然后，你再执行SELECT FOUND_ROWS();，就能拿到总记录数了。

2.2 为什么不建议使用SQL_CALC_FOUND_ROWS

性能损耗巨大： 为了计算总记录数，MySQL需要扫描整个表或者使用索引，这会消耗大量的CPU和IO资源。尤其是在大表上，这个损耗会非常明显。
影响LIMIT查询的速度： SQL_CALC_FOUND_ROWS会拖慢LIMIT查询的速度，因为MySQL需要做额外的工作。
并发问题： 在并发环境下，FOUND_ROWS()的结果可能会不准确，因为在你的查询和执行FOUND_ROWS()之间，可能有其他线程修改了数据。

2.3 替代方案：更高效地获取总记录数

既然SQL_CALC_FOUND_ROWS这么坑，那我们应该怎么做呢？有很多种方法，具体选择哪种取决于你的业务需求。

*单独执行`COUNT()查询：** 这是最简单也是最常用的方法。把SELECT SQL_CALC_FOUND_ROWS * FROM users WHERE age > 18 LIMIT 10;`拆分成两条SQL语句：
```
SELECT COUNT(*) FROM users WHERE age > 18;
SELECT * FROM users WHERE age > 18 LIMIT 10;
```
虽然需要执行两次SQL，但是通常情况下，COUNT(*)查询会比SQL_CALC_FOUND_ROWS快得多，因为它只需要扫描索引，不需要返回所有的数据。
使用应用层缓存： 如果总记录数不经常变化，可以把它缓存到应用层。这样可以避免每次都去数据库查询。
估算总记录数： 如果对总记录数的精度要求不高，可以使用一些近似算法来估算。比如HyperLogLog。
使用滚动分页： 如果只需要知道是否有下一页，可以使用滚动分页。每次查询多一条数据，如果返回的数据条数等于LIMIT + 1，就说明还有下一页。

*2.4 代码示例：单独执行`COUNT()`查询**

import pymysql

def get_users_with_pagination(page_num, page_size, age_greater_than):
    """
    分页查询用户数据，并获取总记录数
    """
    try:
        connection = pymysql.connect(host='localhost',
                                     user='your_user',
                                     password='your_password',
                                     database='your_database',
                                     charset='utf8mb4',
                                     cursorclass=pymysql.cursors.DictCursor)

        with connection.cursor() as cursor:
            # 先查总记录数
            count_sql = "SELECT COUNT(*) as total FROM users WHERE age > %s"
            cursor.execute(count_sql, (age_greater_than,))
            total_count = cursor.fetchone()['total']

            # 再查分页数据
            offset = (page_num - 1) * page_size
            data_sql = "SELECT * FROM users WHERE age > %s LIMIT %s, %s"
            cursor.execute(data_sql, (age_greater_than, offset, page_size))
            users = cursor.fetchall()

            return users, total_count

    finally:
        connection.close()

# 测试一下
page_num = 1
page_size = 10
age_greater_than = 18
users, total_count = get_users_with_pagination(page_num, page_size, age_greater_than)

print(f"Total users with age > {age_greater_than}: {total_count}")
for user in users:
    print(user)

这个例子演示了如何使用单独的COUNT(*)查询来获取总记录数。这种方法通常比SQL_CALC_FOUND_ROWS快得多。

第三部分：总结与建议

功能	`SQL_NO_CACHE`	`SQL_CALC_FOUND_ROWS`
作用	绕过Query Cache，强制从数据库读取数据	在使用`LIMIT`的同时，获取符合查询条件的总记录数
性能问题	增加数据库压力，Query Cache本身就不稳定	性能损耗巨大，影响`LIMIT`查询速度，并发问题
建议	避免使用，拥抱InnoDB的Buffer Pool或应用层缓存	避免使用，单独执行`COUNT(*)`查询，使用应用层缓存，估算总记录数，或使用滚动分页
使用场景（不推荐）	除非你非常清楚自己在做什么，否则不要用	除非你非常清楚自己在做什么，而且对性能要求不高，否则不要用

总的来说，SQL_NO_CACHE 和 SQL_CALC_FOUND_ROWS 都是MySQL里的一些“历史遗留问题”。它们在某些情况下可能有用，但是在大多数情况下，它们都会带来性能问题。所以，尽量避免使用它们，选择更高效的替代方案。

一些建议：

不要迷信“一招鲜吃遍天”： MySQL优化是一个复杂的问题，没有万能的解决方案。要根据具体的业务场景选择合适的优化方法。
多做测试： 在生产环境上线之前，一定要进行充分的测试，确保你的优化方案不会带来新的问题。
关注MySQL的版本： MySQL一直在不断发展，新的版本可能会引入新的特性和优化。及时升级MySQL版本，可以享受到更好的性能和安全性。
学习MySQL的原理： 只有深入理解MySQL的原理，才能写出高效的SQL语句，并做出正确的优化决策。

好了，今天的分享就到这里。希望大家以后在使用MySQL的时候，能够避开这些坑，写出更高效、更稳定的代码。谢谢大家！

发表回复 取消回复

发表回复取消回复