面试题：Python优化MySQL复杂查询及性能调优

Question

在一个具有多个关联表的MySQL数据库系统中，存在'table1'、'table2'和'table3'三个表，'table1'与'table2'通过'id'字段关联，'table2'与'table3'通过'parent_id'字段关联。现在要使用Python执行一个复杂查询，从这三个表中获取满足一定条件的数据，条件为'table1'中的'category'字段值为'important'，'table3'中的'status'字段值为'active'。请写出使用Python（可使用任意主流数据库连接库）执行此查询的代码，并阐述如何通过索引优化、查询语句优化等手段提升查询性能，假设数据库规模较大。

Answer 1

Python代码实现

import mysql.connector

# 连接数据库
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

mycursor = mydb.cursor()

# 执行查询
query = """
SELECT table1.*, table2.*, table3.*
FROM table1
JOIN table2 ON table1.id = table2.id
JOIN table3 ON table2.parent_id = table3.parent_id
WHERE table1.category = 'important' AND table3.status = 'active'
"""

mycursor.execute(query)

result = mycursor.fetchall()

for row in result:
  print(row)

# 关闭连接
mycursor.close()
mydb.close()

性能优化手段

1. 索引优化：
   • 在 table1 表的 category 字段上创建索引：

CREATE INDEX idx_table1_category ON table1(category);

- 在 `table3` 表的 `status` 字段上创建索引：

CREATE INDEX idx_table3_status ON table3(status);

- 由于存在关联，在 `table2` 的 `id` 字段和 `parent_id` 字段上也应该确保有索引（通常在创建外键时会自动创建），如果没有则创建：

CREATE INDEX idx_table2_id ON table2(id);
CREATE INDEX idx_table2_parent_id ON table2(parent_id);

2. 查询语句优化：
   • 确保 JOIN 条件使用的字段类型一致，避免隐式类型转换导致索引失效。
   • 尽量使用 INNER JOIN 而不是 LEFT JOIN 或 RIGHT JOIN，如果查询语义允许，因为 INNER JOIN 执行效率更高。
   • 只选择需要的字段，而不是使用 *。例如，如果只需要 table1 的 name 字段，table2 的 description 字段和 table3 的 created_at 字段，可以这样写查询：

SELECT table1.name, table2.description, table3.created_at
FROM table1
JOIN table2 ON table1.id = table2.id
JOIN table3 ON table2.parent_id = table3.parent_id
WHERE table1.category = 'important' AND table3.status = 'active'

3. 数据库配置优化：
   • 适当增加 innodb_buffer_pool_size，让更多的数据可以缓存到内存中，减少磁盘 I/O。
   • 调整 innodb_log_file_size 等日志相关参数，优化写入性能。