面试题：MySQL 8.0 废弃查询缓存后，有哪些替代方案及如何实现优化

1. 索引优化

技术细节：
- 创建合适的索引，如单列索引、复合索引。索引可以加速数据的查找，通过B - Tree等数据结构，快速定位到满足查询条件的数据行。例如，对于查询SELECT * FROM users WHERE age = 30;，如果在age列上创建索引，数据库可以直接定位到age为30的记录，而无需全表扫描。
- 避免冗余和低效索引，冗余索引指的是多个索引功能类似，例如在(col1, col2)上有复合索引，再单独为col1创建索引就可能是冗余的。低效索引可能是索引列选择不当，导致无法有效利用索引进行查询优化。
适用场景：
- 适用于经常基于某些列进行查询过滤的场景。比如电商系统中，经常根据商品类别、价格范围等条件查询商品信息，在这些列上创建索引可以显著提升查询性能。
- 对于小表，由于全表扫描的成本较低，索引优化效果可能不明显，而对于大表，索引优化的作用更为突出。
选择与实施：
- 根据业务查询语句，分析经常用于WHERE、JOIN等条件的列，为这些列创建索引。
- 使用EXPLAIN关键字分析查询语句的执行计划，查看索引是否被有效使用。如果未被使用，需要调整索引策略。例如，EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2023 - 01 - 01' AND '2023 - 12 - 31';，通过分析执行计划可以判断是否在order_date列上的索引生效。

2. 物化视图

技术细节：
- 物化视图是一种实际存储数据的数据库对象，它预先计算并存储查询结果。例如，在MySQL中可以通过创建物化视图来缓存复杂查询的结果。假设一个复杂的多表关联查询SELECT a.col1, b.col2 FROM table1 a JOIN table2 b ON a.id = b.id WHERE a.status = 'active';，可以创建物化视图存储这个查询结果。
- 当基表数据发生变化时，需要考虑物化视图的刷新机制。有两种常见的刷新方式，手动刷新和自动刷新。手动刷新需要数据库管理员手动执行刷新命令，而自动刷新可以设置为基于时间间隔或者基于基表数据变化触发。
适用场景：
- 适用于查询结果相对稳定，基表数据更新频率较低的场景。比如报表数据，每天生成一次报表，在这期间基表数据变化不大，使用物化视图可以快速获取报表数据。
- 对于复杂的多表关联查询或者聚合查询，物化视图可以大大减少查询的计算量，提升查询性能。
选择与实施：
- 评估业务查询的复杂性和基表数据的更新频率。如果查询复杂且数据更新不频繁，适合创建物化视图。
- 在MySQL中创建物化视图，可以使用CREATE MATERIALIZED VIEW语句，例如CREATE MATERIALIZED VIEW mv_example AS SELECT a.col1, b.col2 FROM table1 a JOIN table2 b ON a.id = b.id WHERE a.status = 'active';，并根据实际需求设置刷新机制。

3. 缓存中间层（如Redis）

技术细节：
- Redis是一种内存数据库，具有极高的读写性能。将MySQL查询结果缓存到Redis中，当相同查询再次到来时，直接从Redis中获取数据，避免了对MySQL的查询。例如，使用Python的redis - py库连接Redis，代码如下：

import redis
import mysql.connector

r = redis.Redis(host='localhost', port=6379, db = 0)
def get_user_data(user_id):
    cache_key = f'user:{user_id}'
    data = r.get(cache_key)
    if data:
        return data.decode('utf - 8')
    else:
        cnx = mysql.connector.connect(user='user', password='password', host='127.0.0.1', database='test')
        cursor = cnx.cursor()
        query = "SELECT * FROM users WHERE id = %s"
        cursor.execute(query, (user_id,))
        result = cursor.fetchone()
        cnx.close()
        if result:
            r.set(cache_key, str(result))
            return str(result)
        return None

- 需要处理缓存的一致性问题，即当MySQL数据发生变化时，要及时更新或删除Redis中的缓存数据。可以通过数据库的触发器或者应用层逻辑来实现。

适用场景：
- 适用于读多写少的业务场景，如新闻网站、博客等，大量用户读取文章内容，而文章更新频率较低。
- 对于一些对实时性要求不是特别高的数据，使用缓存中间层可以有效减轻数据库压力，提升系统整体性能。
选择与实施：
- 根据业务读写比例和对数据实时性的要求来判断是否使用缓存中间层。如果读操作远多于写操作且允许一定时间的数据延迟，适合引入Redis缓存。
- 在应用层集成Redis，根据业务查询构建合适的缓存键值对，并合理设置缓存过期时间，同时处理好缓存一致性问题。

4. 查询重写与优化

技术细节：
- 对复杂的SQL查询进行重写，使其执行效率更高。例如，将子查询转换为连接查询。假设原查询为SELECT * FROM orders WHERE order_id IN (SELECT order_id FROM order_items WHERE product_id = 123);，可以重写为SELECT o.* FROM orders o JOIN order_items oi ON o.order_id = oi.order_id WHERE oi.product_id = 123;。
- 利用MySQL的查询提示（Query Hints），如STRAIGHT_JOIN提示，强制MySQL按照指定的表连接顺序执行查询。例如SELECT /*+ STRAIGHT_JOIN */ a.col1, b.col2 FROM table1 a JOIN table2 b ON a.id = b.id;，这种方式可以根据实际数据量和表结构特点优化查询执行计划。
适用场景：
- 适用于所有场景，尤其是复杂查询场景。通过对查询语句的优化，可以在不引入额外组件的情况下提升查询性能。
- 当数据库架构和数据量发生变化时，对原有的查询语句进行重写和优化，以适应新的情况。
选择与实施：
- 深入分析业务查询语句，结合MySQL的执行计划和优化规则，对复杂查询进行重写。
- 测试不同的查询重写方式和查询提示，通过性能测试工具（如sysbench）对比优化前后的查询性能，选择最优方案进行实施。

星途面试题库

面试题：MySQL 8.0 废弃查询缓存后，有哪些替代方案及如何实现优化

知识考点

面试题答案

1. 索引优化

2. 物化视图

3. 缓存中间层（如Redis）

4. 查询重写与优化