面试题：SQL_CALC_FOUND_ROWS与复杂查询优化及架构设计

查询优化思路及方案

分析查询语句：
- 使用 EXPLAIN 关键字分析SQL语句执行计划，查看索引使用情况、表连接顺序等。例如：EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM table1 JOIN table2 ON table1.id = table2.table1_id WHERE table1.column1 = 'value' AND table2.column2 > 10;，根据执行计划找出性能瓶颈，如是否全表扫描、不合理的连接类型等。
- 检查子查询，尽量将子查询改写为连接查询。例如，子查询 SELECT column1 FROM table1 WHERE column2 = (SELECT MAX(column2) FROM table1) 可改写为 SELECT column1 FROM table1 t1 JOIN (SELECT MAX(column2) AS max_column2 FROM table1) t2 ON t1.column2 = t2.max_column2，减少查询嵌套层级，提高查询效率。
优化条件过滤：
- 确保在 WHERE 子句中的条件字段上有合适的索引。如果是范围查询（如 >、<），索引可能无法完全覆盖，可考虑调整查询逻辑或使用覆盖索引。例如，对 WHERE column1 > 'value'，若 column1 没有索引，创建索引 CREATE INDEX idx_column1 ON table1(column1);
- 避免在条件中使用函数操作字段，如 WHERE UPPER(column1) = 'VALUE' 应改为 WHERE column1 = 'value'，因为函数操作会使索引失效。
合理使用 SQL_CALC_FOUND_ROWS：
- SQL_CALC_FOUND_ROWS 会增加额外开销，因为它需要计算符合条件的总行数。如果不是必须在同一查询中获取总记录数，可以考虑分开查询，先执行实际数据查询，再通过一个单独的 COUNT(*) 查询获取总记录数。例如：
```
SELECT * FROM table1 JOIN table2 ON table1.id = table2.table1_id WHERE table1.column1 = 'value' AND table2.column2 > 10;
SELECT FOUND_ROWS();
```
- 如果必须在同一查询中使用，确保查询本身已经尽可能优化，减少总行数计算的开销。

数据库架构设计思路及方案

索引优化：
- 分析查询场景，创建复合索引。例如，如果经常按 column1 和 column2 联合查询，可创建复合索引 CREATE INDEX idx_column1_column2 ON table1(column1, column2);，注意索引顺序要遵循最左前缀原则。
- 定期维护索引，如使用 ANALYZE TABLE 命令更新索引统计信息，使查询优化器能生成更准确的执行计划。例如：ANALYZE TABLE table1;
表结构优化：
- 考虑是否存在数据冗余，如有，可以进行范式化设计，减少数据冗余，提高数据一致性。但要注意过度范式化可能会增加表连接次数，所以需要权衡。例如，若有重复存储的地址信息，可以将地址信息提取到单独的表中。
- 对于大表，可以进行分区表设计。根据时间、地域等字段进行分区，如按日期对日志表进行分区，查询时只扫描相关分区，减少扫描数据量。例如，创建按日期分区的表：
```
CREATE TABLE log_table (
    id INT,
    log_date DATE,
    log_content TEXT
)
PARTITION BY RANGE (YEAR(log_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN (2023),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
```
读写分离：
- 如果读操作频繁，可以采用读写分离架构。主库负责写操作，从库负责读操作，将读压力分摊到多个从库上。例如，使用MySQL的主从复制机制搭建读写分离架构，通过中间件（如Mycat等）来实现读写请求的自动路由。

缓存机制：

引入缓存，如Redis。对于不经常变化的查询结果进行缓存，下次查询时先从缓存中获取数据，减少数据库压力。例如，在应用层使用Redis缓存查询结果：

import redis
import mysql.connector

r = redis.Redis(host='localhost', port=6379, db=0)
key = 'complex_query_result'
result = r.get(key)
if result:
    print(result.decode('utf - 8'))
else:
    cnx = mysql.connector.connect(user='user', password='password', host='127.0.0.1', database='database')
    cursor = cnx.cursor()
    query = "SELECT SQL_CALC_FOUND_ROWS * FROM table1 JOIN table2 ON table1.id = table2.table1_id WHERE table1.column1 = 'value' AND table2.column2 > 10"
    cursor.execute(query)
    result = cursor.fetchall()
    r.set(key, str(result))
    print(result)
    cursor.close()
    cnx.close()

星途面试题库

面试题：SQL_CALC_FOUND_ROWS与复杂查询优化及架构设计

知识考点

面试题答案

查询优化思路及方案

数据库架构设计思路及方案