Python操作MySQL全攻略：连接池、事务与性能优化-北京尧图网络

前阵子帮一个刚转 Python 后端的朋友梳理项目代码发现他在业务里每次请求都用 pymysql 重新创建一次数据库连接高峰期时 MySQL 日志里绝大多数连接状态都是 TIME_WAIT。这种写法在本地跑 demo 感觉不到问题放到线上连接数直接被打满增删改查本身倒是没写错问题全出在连接生命周期和资源复用上。于是我把 Python 操作 MySQL 的完整链路梳理了一遍覆盖增删改查、事务、连接池、性能优化这几个后端开发绕不开的核心点内容不挑框架示例统一用 PyMySQL但思路和坑在生产环境中都一样适用。1. 先理清三件事驱动选择、环境准备和基础连接1.1 PyMySQL 为什么是大多数人默认的答案我先说结论如果一个项目没有引入 SQLAlchemy直接用 Python 操作 MySQLPyMySQL 基本是默认选择。MySQL 官方提供过 mysql-connector-python 驱动但实测下来PyMySQL 胜在安装简单纯 Python 实现不需要编译原生扩展Windows 和 Linux 上都是直接pip install pymysql就能用。社区资料也多随便遇到一个报错粘贴到搜索引擎都能找到案例。另一个隐藏理由是 PyMySQL 实现了 DB-API 2.0 规范。这意味着游标、execute、fetchone、commit、rollback 这些 API 都是统一且可预测的以后如果项目切到 PostgreSQL只要把连接部分换成 psycopg2业务代码的改动成本极低。这个规范性的收益在项目迭代半年之后会越来越明显。当然纯 Python 驱动在执行复杂 SQL 时速度略慢于 C 扩展驱动但这个差距在面对真实网络耗时的时候几乎可以忽略。真正决定你数据层性能的不是驱动本身而是连接管理、SQL 写法、索引设计和事务控制这些才是这篇文章要展开的重点。1.2 驱动和 ORM 的分工什么时候直接上 SQLAlchemy一个常见误区是“用了 ORM 就不需要学原生 SQL”。我的建议是增删改查和事务这些概念先学会用原生 SQL 完整写一遍再考虑 ORM。否则一个项目下来你调用的orders.filter(...).join(...)底层到底发生了什么完全不清楚一旦线上遇到慢 SQL、死锁、连接耗尽排查起来寸步难行。如果你确实要上 SQLAlchemy也要清楚它只是一个数据访问层底层仍然需要调用 PyMySQL 之类的驱动。连接串里通常写成mysqlpymysql://user:passhost:3306/dbname这里的pymysql就点明了底层驱动是谁。所以这篇文章全部用 PyMySQL 做原生示例先把概念打通之后你再看 ORM 的源码和文档会轻松很多。1.3 建库建表和连接的基础代码先把最基础的环境准备说清楚。假设你已经装好了 Python 和 MySQL并且本地能启动 MySQL 服务。我们现在建一个用户信息表用于后面所有增删改查演示。CREATE DATABASE IF NOT EXISTS demo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE demo; CREATE TABLE user_profile ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(64) NOT NULL UNIQUE, email VARCHAR(128) NOT NULL, age INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB;建表这里有一个需要特别注意的点字符集要选utf8mb4不是utf8。MySQL 的utf8最多只能存 3 字节字符用户随便填一个 emoji 或者生僻字就会直接报错这是建表阶段就要避开的坑。排序规则用utf8mb4_unicode_ci足够覆盖大部分场景。连接的基础代码很简单用pymysql.connect拿到连接对象再调用cursor()创建游标。游标读出来默认是元组可以设置DictCursor让它返回字典调试时一眼能看懂字段名。import pymysql conn pymysql.connect( host127.0.0.1, port3306, userroot, passwordyour_password, databasedemo, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor, ) cursor conn.cursor() cursor.execute(SELECT 1) print(cursor.fetchone())跑通这段代码说明环境和驱动都没问题。但从这一步开始我希望你给自己定一条纪律不要在每个函数里随意创建连接连接统一交给后面的连接池管理否则代码里到处都是建立连接和关闭连接的样板代码出问题时连排查点都找不到。2. 增删改查的完整写法与一个必须养成的习惯2.1 CRUD 的四段式代码在 Python 里操作 MySQL 的增删改查本质上就是四个 SQL 动词加上 Python 的 execute 调用。但先给结论任何情况下都不要用字符串拼接 SQL。先看最简单的查询cursor.execute( SELECT id, username, email, age FROM user_profile WHERE age %s ORDER BY id DESC LIMIT 10, (18,) ) rows cursor.fetchall()对应插入cursor.execute( INSERT INTO user_profile (username, email, age) VALUES (%s, %s, %s), (alice, aliceexample.com, 25), ) conn.commit()这里有一件很多人第一次写都会踩坑的事执行 INSERT 语句之后必须显式调用conn.commit()数据才会真正写入。PyMySQL 默认开启了事务写操作不 commit当前连接断开时数据会自动回滚你在另一个客户端里查不到这条记录。这个“不 commit 就查不到”的体验几乎每个初学者都至少遇到过一次。更新和删除是同样的套路change SQL 语句然后 commit。注意 DELETE 语句执行后也要 commit否则其他连接仍然能看到已经被标记删除的数据。如果是大批量删除或者更新最好先 SELECT 确认一下范围尤其是生产环境先查出即将影响的行数再执行能避免不可恢复的误删。2.2 参数化查询背后的原理上面写法里没有用 f-string 拼接 SQL而是用%s占位符然后把参数作为 execute 的第二个参数传入。这个举动不是代码洁癖而是防 SQL 注入的关键屏障。如果写成这样user_input alice; DROP TABLE user_profile; -- sql fSELECT * FROM user_profile WHERE username {user_input} cursor.execute(sql)用户输入可以直接改变整个 SQL 的语义数据库里最关键的表被删除也只是一瞬间的事。参数化查询的理念是让 MySQL 服务端把参数当作一个“值”来处理而不是当作 SQL 片段去解析从机制上杜绝了这类注入问题。用生活化的类比拼接 SQL 就像让一个自动回复机器人直接念出用户发来的每个字用户发来“你是谁”它就回“你是谁”参数化查询则是把用户内容装进一个密封信封收件人只把内容当作数据来读取不会执行信封上写着的任何指令。所以不管前端有没有做输入校验后端这条参数化底线必须守住。2.3 更新、排序、limit 和几个高频细节更新操作里有一个容易忽略的细节如果 UPDATE 语句中使用了子查询MySQL 某些版本不允许直接对同一张表进行子查询更新。比如执行下面这条 SQL 会报错UPDATE user_profile SET age (SELECT MAX(age) FROM user_profile) WHERE id 1;原因是要更新的表同时出现在子查询中MySQL 为了避免不确定性会直接拒绝。处理方式是把子查询结果包一层临时表UPDATE user_profile SET age (SELECT m.max_age FROM (SELECT MAX(age) AS max_age FROM user_profile) m) WHERE id 1;排序和 limit 方面也有两个实操经验。分页查询不要用偏移量特别大的写法比如LIMIT 100000, 20MySQL 依然会扫描前 100000 行数据再丢弃数据量大时性能会断崖式下跌。更好的方案是记录上一页最后一条数据的 ID用WHERE id last_id ORDER BY id LIMIT 20这种“向后翻页”的方式。如果必须支持任意跳页再考虑用覆盖索引或者其他中间件方案。还有一个容易被忽略的坑是sort排序的字段如果没建索引数据量大时 MySQL 会使用 filesort在临时文件里排序速度很慢。ORDER BY 的字段最好能和 WHERE 条件一起命中联合索引这个在后面的性能优化章节再展开。3. 事务不是“自动的”提交时机、隔离级别与分布式事务边界3.1 一次扣库存为什么需要六行代码最常见的业务场景用户下单要扣库存、生成订单、记录流水。三个操作必须“要么都成功要么都失败”。如果你把它们拆成三条独立执行并且每一条都 commit前两条成功、第三条失败时库存和订单就永远不一致了后续对账和对账都救不回来。事务就是解决这个问题的。PyMySQL 默认autocommitFalse所以从你执行第一条写操作开始MySQL 就已经在一个隐式事务里了。你之前的 INSERT、UPDATE 在没有 commit 之前对其他连接是不可见的。最后执行conn.commit()整个事务才真正落盘如果中途发生异常执行conn.rollback()回滚到事务开始之前的状态。3.2 事务代码的写法与几个踩坑经验推荐的事务写法是 try/except 包住所有 SQL 操作异常时 rollback正常时 commitfinally 里关闭游标。不要在一个接口里让事务跨秒级执行更不要在事务进行中调用外部 HTTP 接口因为事务期间持续持有的行锁、间隙锁会让后续并发请求全部进入锁等待状态。线上经常看到的“数据库突然变慢”很多不是 SQL 本身慢而是事务把锁持有时间拉长了后面的请求全在排队。来看具体代码def transfer_funds(conn, from_account, to_account, amount): cursor conn.cursor() try: cursor.execute( UPDATE account SET balance balance - %s WHERE id %s, (amount, from_account) ) if cursor.rowcount 0: raise RuntimeError(转出账户不存在) cursor.execute( UPDATE account SET balance balance %s WHERE id %s, (amount, to_account) ) if cursor.rowcount 0: raise RuntimeError(转入账户不存在) conn.commit() except Exception: conn.rollback() raise finally: cursor.close()这里特意检查了rowcount这是实际经验中很容易漏掉的点。UPDATE 一个不存在的 id 时 MySQL 不会报错它只会影响 0 行如果你没有这个判断业务上就会表现为“提示转账成功但实际上转了个寂寞”。这类 bug 在单元测试里很难触发因为测试数据总是存在的真上了线才发现问题就晚了。另外一个坑是不要在事务里混用 SELECT 和写操作时忽略返回值。SELECT 的结果如果不 check可能会继续基于旧数据做更新导致覆盖已提交的修改。很多更新丢失问题根源就是“先读到旧值然后拿着旧值去覆盖”。3.3 隔离级别脏读、不可重复读、幻读的距离很多人分不清事务的四种隔离级别这里用一个核心逻辑来记隔离级别越严格数据一致性越好但并发能力越低。READ UNCOMMITTED可以读到其他事务未提交的数据也就是脏读。事务 A 改了数据但不 commit事务 B 就能读到如果事务 A 最终回滚事务 B 读到的就是一个从来没存在过的“假数据”。READ COMMITTED只能读到已提交的数据解决了脏读。但同一个事务里两次 SELECT 可能读到不同的已提交结果这就是不可重复读。REPEATABLE READMySQL 的默认隔离级别。同一事务中多次读取同一范围数据时结果一致理论上可能出现幻读也就是其他事务插入新行后你再次查询多了一行。不过 InnoDB 通过 MVCC 和间隙锁已经能规避大部分幻读场景所以 MySQL 的 RR 级别在实际使用中比理论上更安全。SERIALIZABLE全部串行化不会有任何并发问题但性能最差生产环境很少使用。实际开发中如果用默认的 REPEATABLE READ绝大多数业务都没问题。不要把隔离级别当成一个需要频繁折腾的东西它更多是排查问题时的背景知识。当你在一个事务里读两次数据发现结果不一样首先确认当前事务的隔离级别再推断是哪种并发读问题这样方向才不会跑偏。3.4 提到分布式事务先分清问题边界分布式事务是后端圈子里出现频率很高的词很多新手一听到“分布式事务”就紧张。其实你要先分清如果你的系统还是单库单服务那就只需要关心本地事务只有当调用多个服务、访问多个数据库、需要保证跨资源一致性时才需要考虑分布式事务也就是常见的 TCC、SAGA、基于消息的最终一致性等方案。在 Python 后端项目里最常见的演化路径是一开始一个服务一个数据库本地事务解决一切后来拆服务了每个服务有自己的库订单服务和库存服务各自独立提交才出现分布式一致性问题。我的建议是不要过早引入分布式事务框架先看业务能否用消息队列把链路改成最终一致如果不能再考虑 Seata 之类的方案。很多项目嘴里说的“分布式事务”其实只是在代码里串行执行了多个本地事务并没有真正的分布式资源竞争这种场景根本不需要上框架。4. 连接池为什么每次请求新建连接是一种“慢性事故”4.1 新建连接的真实代价文章开头提到朋友的项目每次请求都新建连接。很多人觉得“数据库连接不就是建立一个 TCP 连接吗”实际情况比想象中复杂得多TCP 三次握手至少一个 RTT 的开销MySQL 服务端认证、权限校验需要读取用户表和权限信息如果启用 SSL 加密传输还要进行 TLS 握手连接断开时还有四次挥手。也就是说一个连接从建立到销毁消耗的时间很可能比 SQL 本身还长。高并发下MySQL 默认的最大连接数通常只有几百一次请求一个连接如果建立连接的速度跟不上释放速度服务端连接数就会被打满后面的请求开始排队表现就是“数据库突然连不上了”。连接池的核心思想是复用预先创建一批连接放在池子里每次需要时从池子里取一个用完之后归还而不是销毁。这样反复握手和认证的开销被彻底去掉单个请求的数据库耗时能明显降下来。4.2 手写一个极简连接池先理解原理再上生产方案实际项目中我不建议手写连接池去替代成熟方案但为了理解原理可以先看一个简化实现的骨架import queue import pymysql class SimplePool: def __init__(self, size5, **conn_kwargs): self._queue queue.Queue(size) self._conn_kwargs conn_kwargs for _ in range(size): self._queue.put(pymysql.connect(**conn_kwargs)) def acquire(self): return self._queue.get() def release(self, conn): self._queue.put(conn)这个版本没有处理连接失效、超时、线程安全等问题但在思路上已经足够说明连接池的本质池子里存的是一个一个已经建好的连接对象业务只是借用和归还。理解了这一点再看 DBUtils 之类的库就容易明白它到底替你解决了哪些边界问题。4.3 DBUtils 的 PooledDB 是 Python 生态里的标准答案项目里直接使用 DBUtils 提供的 PooledDB 更靠谱。它把很多边界条件都考虑好了比如连接空闲超时后自动重建、取不到连接时是否阻塞等待等。from dbutils.pooled_db import PooledDB import pymysql pool PooledDB( creatorpymysql, maxconnections20, mincached2, maxcached10, maxshared0, blockingTrue, maxusageNone, setsession[], ping0, host127.0.0.1, port3306, userroot, passwordyour_password, databasedemo, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor, )拿到连接后的使用方式conn pool.connection() try: with conn.cursor() as cursor: cursor.execute(SELECT ...) rows cursor.fetchall() conn.commit() finally: conn.close()注意这里conn.close()并不是真的断开连接而是把连接归还到池子里所以一定不能漏。这个 close 语义和原生 pymysql 的 close 语义不一样PooledDB 做了重写很多人从新手阶段过渡过来时都会在这个细节上懵一阵。如果你的代码里finally漏写了 close等于从池子里借了连接不还池子很快就会耗尽。4.4 连接池参数怎么调先说原理再给建议下面是一份 PooledDB 核心参数对照表方便日常查阅参数作用建议maxconnections连接池最大连接数通常设为应用并发上限除以单请求 DB 次数再乘一个冗余系数mincached初始化时预先创建的空闲连接数2-5 即可不要过多maxcached空闲连接上限超过后多余连接会关闭与 mincached 配合保持适度maxshared允许共享的连接数PyMySQL 场景建议设为 0连接不能跨线程共享blocking连接耗尽时是否阻塞等待建议 True宁可等待也不要直接抛异常maxusage单个连接最大复用次数默认 None 不限制可设 1000-5000 防止连接状态老化ping取连接时是否检测连接存活调 0 表示不检测生产可调 1 或 2调优建议是池的 maxconnections 不要盲目设得很大它和 MySQL 的max_connections相关。一个粗略估算方法是应用正常并发 200每个请求访问数据库 2 次单次查询 10 毫秒一个连接大概能承载几个并发请求的周转池大小设在 40-50 就比较合理。设置得太大MySQL 后端线程数会飙升上下文切换反而消耗 CPU。5. 性能优化索引、批量写入与 EXPLAIN 定位慢查询5.1 索引是免费的加速但乱建会反噬数据库性能优化第一件事永远是看索引。把表想象成一本没有目录的书查找某一页只能从第一页翻到最后一页索引就是这本书的目录它让 MySQL 从“全表扫描”变成近似“二分查找”。建立索引要基于实际查询不能每个字段都建。一个经验是把 WHERE、JOIN、ORDER BY 里最常出现的字段拿来做联合索引并且遵守最左前缀原则。比如业务里经常用age和created_at查询可以创建联合索引(age, created_at)这样age单独查询时也能命中索引但created_at单独查询用不到。一个容易踩的坑是给区分度极低的字段建索引比如性别只有“男/女”两个值。区分度低时MySQL 优化器认为“一查一大半都是这个值”干脆走全表扫描索引反而增加了每次写入的成本。所以在设计索引前先算一下字段的区分度也就是COUNT(DISTINCT field)/COUNT(*)这个值太低的字段就不适合单独建索引。5.2 批量写入和分批提交的取舍逐条 INSERT 跑 1000 次就需要 1000 次网络往返性能非常差。正确姿势是用executemany一次性传入多条数据user_list [ (user1, e1example.com, 20), (user2, e2example.com, 22), (user3, e3example.com, 23), ] cursor.executemany( INSERT INTO user_profile (username, email, age) VALUES (%s, %s, %s), user_list, ) conn.commit()executemany底层会尽量把多条数据合并成一次批量 INSERT 发送给 MySQL减少了网络交互次数。但要注意单次提交的数据量也不是越大越好。上万条数据一次性提交有两个隐患一是 SQL 总长度超过max_allowed_packet限制直接报错二是事务时间太长锁持有时间太久阻塞其他请求。一般建议分批提交每批 500-1000 条批与批之间 commit 一次。这样如果中途失败只会回滚当前批次重试成本比全量回滚要低得多。5.3 用 EXPLAIN 找到真正的慢查询光看执行时间找慢 SQL 不够还要看执行计划。EXPLAIN 就是在一条 SQL 前面加EXPLAIN关键字MySQL 会输出这条 SQL 的执行计划不会真正去查数据。在结果里重点看这几列type从好到差依次是 system、const、eq_ref、ref、range、index、ALL。看到 ALL 说明是全表扫描基本就是优化信号。rows预估扫描行数数值越大越可疑。Extra如果出现 Using filesort说明排序没能用上索引在高频查询里这是大忌。举个例子EXPLAIN SELECT id, username FROM user_profile WHERE age 18 ORDER BY created_at DESC LIMIT 20;如果结果里 type 是 ALL或者 Extra 里有 Using filesort那就需要结合 WHERE 和 ORDER BY 字段建联合索引。示例表数据量小看不出差别但套到千万级的订单表上一次全表扫描就足以把数据库 CPU 打满。5.4 几个容易拖垮性能的习惯SELECT *尽量列出需要的列。MySQL 只需要读取和传输这些列IO 和内存都会省不少代码的可读性也更好。隐式类型转换比如username字段是字符串条件却写成WHERE username 123456MySQL 会把字符串列转成数字再做比较导致索引失效。我排查过几次线上慢 SQL最后定位到的原因都是这个。OR 条件OR 在多字段上容易让索引失效可以用 UNION 或者拆成多条 SQL 来优化具体要看执行计划。大事务里做远程调用事务期间持锁远程调用的网络延迟会被无限放大成数据库锁等待这个问题前面已经强调过但每次排查慢 SQL 时它都会冒出来值得反复提醒。写代码的时候每次多加一层“这条 SQL 在 1000 万行数据下会怎么执行”的意识就能避免掉大部分性能问题。真等线上报警再回来看代价往往是好几倍的。最后分享一个我平时写数据层代码的习惯组合PyMySQL 做访问层、DBUtils 做连接池、事务永远包在 try/except 里并显式 commit 或 rollback、每条 SQL 上线前跑一遍 EXPLAIN。这套组合已经在多个项目里验证过线上没出过大问题。实际排障时你会发现绝大多数数据库故障都不是 MySQL 本身不行而是连接管理、事务边界、SQL 写法这些细节出了问题。多花一点时间把这些基础打牢后面写业务代码会顺畅很多。

Python操作MySQL全攻略：连接池、事务与性能优化

相关推荐

局域网远程桌面全攻略：从RDP到RustDesk的搭建与排错

Java Web毕设实战：Servlet+JSP+MySQL二手车交易系统

SpringBoot实现企业微信会话存档：跨平台证书加载与AES-SHA256签名实战

更多资讯

ROS1差速机器人导航全链路工程：SLAM建图、AMCL定位与move_base规划实战

Source SDK 2013 完整指南：在 Windows 和 Linux 上跑通游戏模组开发的两步构建

晶面生长动力学测定：破解晶体形貌控制的核心密码

RPA的ROI怎么算才准？成本拆解、收益量化与上线复盘的完整框架

ABAP CDS Association导航实践：从语法到性能优化

Java Stream流实战总结：高频操作、性能优化与避坑指南

今日更新

AWS无服务器应用开发指南：从Lambda到SAM的架构与实践

SpringBoot+Vue人事系统实战：解决HR数据断点与事务一致性

MATLAB手写紧束缚模型计算石墨烯能带结构

本周精选

AI驱动的自动化专利分析平台技术解析

LeetCode-Go 题解：1252. Cells with Odd Values in a Matrix（奇数值单元格计数）双解法剖析

在线答疑系统Java毕设实战：Spring Boot前后端分离与状态流转

本月热门

基于YOLO的焊缝缺陷检测：从数据标注到工业部署全流程实践

分布式服务的决策记录

创业产品的首版范围

读完文章，想聊聊您的网站？