使用mysql-connector-python实现MySQL多线程操作线程安全的最高效方法

两种方案的效率对比 #

连接池方案的效率远高于全局锁方案，20个并发线程的场景下，吞吐量差距可以达到10倍以上：全局锁会完全串行化所有数据库操作，而连接池可以充分利用MySQL的并发处理能力，不会浪费硬件资源。 #

两种方案的优缺点 #

方案1：封装类加threading.Lock全局锁 #

优点

• 实现逻辑简单，不需要学习额外的连接池API，仅需在数据库操作的入口和出口加锁、解锁即可，不易出现逻辑漏洞
• 仅需维护单条数据库连接，异常重连、状态校验的逻辑实现成本低

缺点

• 所有数据库操作完全串行，20个线程无论读写都要排队等待锁释放，CPU和数据库的并发能力完全被浪费，只要单个查询耗时稍长，整个线程队列都会阻塞，吞吐量极低
• 自行实现锁逻辑容易出现粒度控制问题，要么锁范围过大进一步降低效率，要么锁范围过小引发竞态条件，甚至出现死锁、线程饥饿的问题
• 单连接一旦出现超时、异常断开，所有线程的数据库操作都会直接失败，容错性极差

方案2：mysql-connector-python原生连接池 #

优点

• 每个线程从连接池获取独立的连接，用完后归还，无需全局锁，所有数据库操作可以真正并行执行，能够充分利用MySQL的并发处理能力，20线程场景下的吞吐量比全局锁方案高一个量级以上
• 官方实现的连接池本身已经保证线程安全，不需要自行编写锁逻辑，规避了手动加锁带来的死锁、饥饿等问题
• 连接池自带连接存活检测、自动重连、连接数管控能力，单个连接异常不会影响其他线程的操作，容错性远高于单连接方案
• 符合Python数据库开发的通用规范，后续扩展成本极低，调整并发量、连接参数仅需修改连接池配置即可

缺点

• 实现逻辑比全局锁稍复杂，需要了解连接池的核心配置项（最大连接数、超时时间、回收策略等），配置不合理可能会出现连接占满、资源浪费的问题
• 多连接场景下如果涉及事务操作，需要自行保证每个线程的事务隔离，避免跨线程串事务的问题

更高效、更符合规范的实现方案 #

除了上述两种方案，还有两种优化方向可以进一步提升性能、降低维护成本：

1. 配合threading.local()线程本地存储绑定连接
   给每个线程绑定专属的数据库连接，避免每次操作都去连接池取还的开销，线程销毁时再统一归还连接，性能比每次取还连接还要高10%-20%，参考实现代码如下：

import threading
from mysql.connector import pooling

class MySQLClient:
    def __init__(self, pool_config):
        # 初始化连接池，最大连接数建议和线程数对齐，不要超过MySQL服务端max_connections配置
        self.pool = pooling.MySQLConnectionPool(**pool_config)
        self.local_storage = threading.local()
    
    def _get_current_conn(self):
        # 给当前线程绑定连接，避免重复取还
        if not hasattr(self.local_storage, "conn") or not self.local_storage.conn.is_connected():
            self.local_storage.conn = self.pool.get_connection()
        return self.local_storage.conn
    
    def exec_query(self, sql, params=None):
        conn = self._get_current_conn()
        cursor = conn.cursor(dictionary=True)
        try:
            cursor.execute(sql, params or ())
            if sql.strip().upper().startswith("SELECT"):
                return cursor.fetchall()
            conn.commit()
            return cursor.rowcount
        except Exception as e:
            conn.rollback()
            raise e
        finally:
            cursor.close()

2. 读写分离拆分连接池
   如果你的业务读请求占比远高于写请求（比如读占比80%以上），可以将读写请求拆分到不同的连接池，读请求走从库连接池，写请求走主库连接池，既可以进一步提升并发能力，也能避免大查询阻塞主库写入的问题。

注意事项 #

• 连接池的最大连接数不要超过MySQL服务端的max_connections默认配置（151），20个线程的场景下配置20-30即可，不要开过大浪费数据库资源
• 不要跨线程操作同一个数据库连接，每个连接仅允许持有它的线程使用，否则依然会出现线程安全问题
• 游标用完后要及时关闭，不需要长期持有连接的场景要及时将连接归还到连接池，避免连接泄露

内容的提问来源于stack exchange，提问作者koringakek5