面试题：Python数据库查询结果优化

SQL语句优化

索引优化：
- 对连接条件涉及的列创建索引。例如在 JOIN 子句中的关联列上创建索引，这可以加快连接操作。如果查询语句为 SELECT * FROM table1 JOIN table2 ON table1.id = table2.table1_id，则在 table1.id 和 table2.table1_id 上创建索引。
- 对 WHERE 子句中的过滤条件列创建索引。如 SELECT * FROM table1 WHERE column1 = 'value'，在 column1 上创建索引可以快速定位满足条件的行。
查询改写：
- 尽量避免使用子查询，可以将子查询改写为 JOIN 操作。例如，原本子查询 SELECT * FROM table1 WHERE id IN (SELECT table1_id FROM table2)，可改写为 SELECT table1.* FROM table1 JOIN table2 ON table1.id = table2.table1_id。
- 使用 EXISTS 或 NOT EXISTS 代替 IN 或 NOT IN，在大数据集下，EXISTS 通常性能更好。例如 SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.table1_id)。
限制返回列：只选择需要的列，而不是使用 SELECT *。例如 SELECT column1, column2 FROM table1 JOIN table2 ON...，这样可以减少数据传输量和处理开销。

Python代码优化

分批获取数据：
- 使用 LIMIT 和 OFFSET 在SQL语句中分批获取数据。在Python中，通过循环每次设置不同的 OFFSET 值来逐步获取全部数据。例如：

import mysql.connector

cnx = mysql.connector.connect(user='user', password='password',
                              host='127.0.0.1',
                              database='database_name')
cursor = cnx.cursor()
batch_size = 1000
offset = 0
while True:
    query = f"SELECT * FROM table1 JOIN table2 ON table1.id = table2.table1_id LIMIT {batch_size} OFFSET {offset}"
    cursor.execute(query)
    results = cursor.fetchall()
    if not results:
        break
    # 处理每一批数据
    for row in results:
        print(row)
    offset += batch_size
cursor.close()
cnx.close()

使用生成器：
- 将查询结果以生成器的形式返回，避免一次性将所有数据加载到内存中。例如：

import mysql.connector


def get_query_results():
    cnx = mysql.connector.connect(user='user', password='password',
                                  host='127.0.0.1',
                                  database='database_name')
    cursor = cnx.cursor()
    query = "SELECT * FROM table1 JOIN table2 ON table1.id = table2.table1_id"
    cursor.execute(query)
    for row in cursor:
        yield row
    cursor.close()
    cnx.close()


for result in get_query_results():
    print(result)

优化数据处理逻辑：
- 在处理查询结果时，避免不必要的循环嵌套和复杂计算。如果需要对结果进行计算，尽量使用向量化操作（如使用 numpy 库进行数值计算）。例如，如果结果集是数值型数据且需要进行简单的数学运算，可以将结果转换为 numpy 数组进行操作。

import mysql.connector
import numpy as np

cnx = mysql.connector.connect(user='user', password='password',
                              host='127.0.0.1',
                              database='database_name')
cursor = cnx.cursor()
query = "SELECT column1, column2 FROM table1 JOIN table2 ON table1.id = table2.table1_id"
cursor.execute(query)
results = cursor.fetchall()
data = np.array(results)
# 对数据进行向量化计算，如计算两列之和
sum_column = data[:, 0] + data[:, 1]
cursor.close()
cnx.close()

数据库配置优化

调整缓冲区大小：
- 适当增大MySQL的 innodb_buffer_pool_size 参数，它用于缓存InnoDB表的数据和索引，增大该值可以减少磁盘I/O，提高查询性能。但要注意不要设置过大，以免占用过多系统内存导致系统性能下降。
查询缓存：
- 开启MySQL的查询缓存（虽然在高并发写入场景下可能存在问题）。通过设置 query_cache_type = 1 和 query_cache_size 来启用和设置缓存大小。查询缓存会缓存查询语句及其结果，如果相同的查询再次执行，可以直接从缓存中获取结果，提高查询速度。不过需要注意，表数据更新时，相关的查询缓存会失效。

星途面试题库

面试题：Python数据库查询结果优化

知识考点

面试题答案

SQL语句优化

Python代码优化

数据库配置优化