面试题：Python操作MySQL数据库时如何优化查询性能

Question

在Python中使用`pymysql`库操作MySQL数据库，假设有一张包含大量数据的用户表`users`，表结构为`(id INT PRIMARY KEY, name VARCHAR(50), age INT, email VARCHAR(100))`，现在要查询年龄大于30岁的用户信息，并且要对查询性能进行优化，你会采取哪些措施？请从SQL语句优化和Python代码层面分别阐述。

Answer 1

SQL语句优化

1. 添加索引：
   • 为age字段添加索引，在MySQL中可以使用以下语句创建索引：

   CREATE INDEX idx_age ON users(age);
   

   • 这样在执行SELECT id, name, age, email FROM users WHERE age > 30;查询时，数据库可以利用索引快速定位符合条件的数据，大大提高查询效率。
2. 避免使用函数操作：
   • 如果在WHERE子句中对age字段使用函数，例如SELECT... WHERE YEAR(CURRENT_DATE) - YEAR(birth_date) > 30;（假设原本存储的是出生日期字段），会导致索引失效，应尽量避免这种写法，直接使用存储的age字段进行比较。

Python代码层面优化

1. 合理使用连接池：
   • 使用连接池来管理数据库连接，避免频繁创建和销毁连接带来的开销。例如使用DBUtils库与pymysql结合，示例代码如下：

   from dbutils.pooled_db import PooledDB
   import pymysql
   
   pool = PooledDB(pymysql, 5, host='localhost', user='root', passwd='password', db='test', port=3306)
   conn = pool.connection()
   try:
       cursor = conn.cursor()
       cursor.execute("SELECT id, name, age, email FROM users WHERE age > 30")
       results = cursor.fetchall()
       for row in results:
           print(row)
   finally:
       conn.close()
   

2. 批量获取数据：
   • 避免一次获取大量数据，可使用fetchmany(size)方法，每次获取一定数量的数据，例如：

   import pymysql
   
   conn = pymysql.connect(host='localhost', user='root', passwd='password', db='test', port=3306)
   try:
       cursor = conn.cursor()
       cursor.execute("SELECT id, name, age, email FROM users WHERE age > 30")
       while True:
           rows = cursor.fetchmany(100)  # 每次获取100条数据
           if not rows:
               break
           for row in rows:
               print(row)
   finally:
       conn.close()
   

3. 关闭自动提交：
   • 关闭自动提交功能，在需要提交事务时手动提交，减少数据库I/O操作。示例如下：

   import pymysql
   
   conn = pymysql.connect(host='localhost', user='root', passwd='password', db='test', port=3306, autocommit=False)
   try:
       cursor = conn.cursor()
       cursor.execute("SELECT id, name, age, email FROM users WHERE age > 30")
       results = cursor.fetchall()
       for row in results:
           print(row)
       conn.commit()
   except Exception as e:
       conn.rollback()
       raise e
   finally:
       conn.close()