IT策士 10余年一线大厂经验专注 IT 思维、架构、职场进阶。我会在各个平台持续发布最新文章助你少走弯路。上篇我们学会了用 EXPLAIN 定位慢查询但知道问题只是第一步——如何解决才是关键。今天我们就用 Python 配合实战把生产中最常见的三大类 SQL 性能杀手逐个击破索引失效的陷阱、分页深翻的灾难、多表 JOIN 的优化。每个问题都配有错误示例、原理解析和优化方案看完就能直接用。1. 准备数据构建百万级测试环境importmysql.connectorimporttimeimportrandom connmysql.connector.connect(host127.0.0.1,port3306,userroot,passwordMyNewPass123!,databaseshop)cursorconn.cursor()# 用户表cursor.execute(DROP TABLE IF EXISTS users)cursor.execute( CREATE TABLEusers(idINT AUTO_INCREMENT PRIMARY KEY, phone VARCHAR(20)NOT NULL, name VARCHAR(50)NOT NULL, age INT, city VARCHAR(30), vip_level INT DEFAULT0, INDEX idx_phone(phone), INDEX idx_city(city), INDEX idx_age_city(age, city))ENGINEInnoDB DEFAULTCHARSETutf8mb4)# 订单表cursor.execute(DROP TABLE IF EXISTS orders)cursor.execute( CREATE TABLE orders(idINT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, order_no VARCHAR(30)NOT NULL, amount DECIMAL(10,2)NOT NULL, status VARCHAR(20)DEFAULTpending, created_date DATE NOT NULL, INDEX idx_user(user_id), INDEX idx_date(created_date), INDEX idx_user_date(user_id, created_date))ENGINEInnoDB DEFAULTCHARSETutf8mb4)# 批量插入数据print(⏳ 正在生成测试数据...)batch_size10000total200000cities[北京,上海,深圳,杭州,成都,武汉,南京,西安]foriinrange(0, total, batch_size): users_batch[]orders_batch[]forjinrange(batch_size): idxi j 1phonef138{idx:08d}[:11]namefuser_{idx}agerandom.randint(18,65)cityrandom.choice(cities)viprandom.randint(0,5)users_batch.append((phone,name,age,city,vip))order_nofORD{idx:010d}amountround(random.uniform(10,5000),2)statusrandom.choice([pending,completed,cancelled])datef2025-{random.randint(1,12):02d}-{random.randint(1,28):02d}orders_batch.append((idx%100001,order_no,amount,status,date))cursor.executemany(INSERT INTO users (phone, name, age, city, vip_level) VALUES (%s,%s,%s,%s,%s), users_batch)cursor.executemany(INSERT INTO orders (user_id, order_no, amount, status, created_date) VALUES (%s,%s,%s,%s,%s), orders_batch)conn.commit()print(f 已插入 {min(ibatch_size, total)}/{total} 条,end\r)print(\n✅ 测试数据准备完毕)2. 避坑一隐式类型转换2.1 问题重现phone列是VARCHAR类型并建有索引。但如果你用整数去查询索引就会失效。def compare_query(sql, label): cursor.execute(fEXPLAIN {sql})rowcursor.fetchone()print(f{label}: type{row[3]}, key{row[6]}, rows{row[9]})# 正确字符串查询 VARCHAR 列compare_query(SELECT * FROM users WHERE phone 13800000001,字符串查询)# 错误整数查询 VARCHAR 列compare_query(SELECT * FROM users WHERE phone 13800000001,整数查询 )预期输出字符串查询:typeref,keyidx_phone,rows1整数查询:typeALL,keyNone,rows200000同样的逻辑用 Python 测量实际耗时差距starttime.time()cursor.execute(SELECT * FROM users WHERE phone 13800000001)cursor.fetchall()print(f字符串查询耗时: {time.time() - start:.4f} 秒)starttime.time()cursor.execute(SELECT * FROM users WHERE phone 13800000001)cursor.fetchall()print(f整数查询耗时: {time.time() - start:.4f} 秒)2.2 原因与对策MySQL 在比较字符串和整数时会把字符串列的值逐个转换为数字再比较。这导致索引中存的是原始字符串无法直接匹配数字必须全表扫描逐行转换后比较对策永远保持查询参数的类型与列定义一致。Python 中尤其注意——用户输入的手机号、订单号等传给 SQL 时务必用字符串# 好的做法phoneinput(请输入手机号: )cursor.execute(SELECT * FROM users WHERE phone %s,(phone,))# 参数化自动处理类型# 坏的做法cursor.execute(fSELECT * FROM users WHERE phone {phone})# 数字会变成整数3. 避坑二函数作用于索引列3.1 问题重现# 错误WHERE 条件中对索引列使用函数compare_query(SELECT * FROM orders WHERE YEAR(created_date) 2025,函数包裹列)# 正确用范围查询替代函数compare_query(SELECT * FROM orders WHERE created_date 2025-01-01 AND created_date 2026-01-01,范围查询 )预期输出函数包裹列:typeALL,keyNone,rows200000范围查询:typerange,keyidx_date,rows1000003.2 常见“函数陷阱”清单# LEFT 函数 vs LIKEprint(\n LEFT vs LIKE)compare_query(SELECT * FROM users WHERE LEFT(phone, 3) 138,LEFT 函数)compare_query(SELECT * FROM users WHERE phone LIKE 138%,LIKE 前缀)预期输出 LEFT vs LIKE LEFT 函数:typeALL,keyNone,rows200000LIKE 前缀:typerange,keyidx_phone,rows50000LIKE 的索引规则LIKE abc%走索引前缀匹配LIKE %abc不走索引后缀匹配LIKE %abc%也不走索引中缀匹配。因为 BTree 只能从左向右匹配。4. 避坑三分页深翻优化4.1 问题重现传统LIMIT offset, count在偏移量巨大时MySQL 仍会扫描前面所有的行并丢弃。def test_pagination(offset):测试分页耗时 starttime.time()cursor.execute(fSELECT * FROM orders ORDER BY id LIMIT 10 OFFSET {offset})rowscursor.fetchall()elapsedtime.time()- start print(fOFFSET {offset:6}: {elapsed:.4f} 秒, 返回 {len(rows)} 行)returnelapsed print(\n 分页深翻性能对比)test_pagination(0)test_pagination(10000)test_pagination(50000)test_pagination(150000)预期输出 分页深翻性能对比 OFFSET0:0.0003秒, 返回10行 OFFSET10000:0.0152秒, 返回10行 OFFSET50000:0.0721秒, 返回10行 OFFSET150000:0.2103秒, 返回10行偏移量越大查询越慢。当数据达到千万级时后面的分页可能需要数秒。4.2 优化方案一延迟关联推荐核心思想先在索引上完成分页再通过主键关联回表取完整数据。# 传统深翻starttime.time()cursor.execute(SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 150000)traditional_timetime.time()- start# 延迟关联starttime.time()cursor.execute( SELECT o.* FROM orders o INNER JOIN(SELECTidFROM orders ORDER BYidLIMIT10OFFSET150000)AS tmp ON o.idtmp.id)deferred_timetime.time()- start print(f\n 延迟关联优化效果)print(f传统深翻: {traditional_time:.4f} 秒)print(f延迟关联: {deferred_time:.4f} 秒)print(f提升: {((traditional_time - deferred_time) / traditional_time * 100):.1f}%)原理子查询只扫描索引覆盖索引无需回表速度极快。然后只对 10 条记录做回表关联。4.3 优化方案二基于主键的游标分页如果你的分页逻辑允许比如“加载更多”而非“跳转到第 N 页”直接用上一页的最大主键作为起点# 传统方式OFFSETstarttime.time()cursor.execute(SELECT id, order_no FROM orders ORDER BY id LIMIT 10 OFFSET 100000)traditional_timetime.time()- start# 游标方式基于主键starttime.time()cursor.execute(SELECT id, order_no FROM orders WHERE id 100000 ORDER BY id LIMIT 10)cursor_timetime.time()- start print(f\n 游标分页 vs 传统分页)print(f传统 OFFSET 100000: {traditional_time:.4f} 秒)print(f游标 WHERE id100000: {cursor_time:.4f} 秒)游标分页能一直保持高性能因为每次只扫描 10 行不受已翻页数影响。缺点是只能逐页翻不能跳页。5. 多表 JOIN 优化5.1 确保驱动表尽可能小MySQL 优化器通常选择小表作为驱动表但如果顺序不对可以用STRAIGHT_JOIN强制指定。# 查看 JOIN 顺序cursor.execute( EXPLAIN SELECT u.name, o.order_no, o.amount FROMusersu JOIN orders o ON u.ido.user_id WHERE u.city杭州)print(\n JOIN 执行顺序)forrowincursor.fetchall(): print(f 表{row[2]}, type{row[3]}, key{row[6]}, rows{row[9]}, Extra{row[10]})优化法则在 JOIN 列user_id上必须有索引WHERE 条件应先在驱动表上过滤出少量行驱动表用过滤性好的条件缩小结果集5.2 用小结果集驱动大结果集# 不好先查所有 orders 再 JOIN userscursor.execute( EXPLAIN SELECT u.name, COUNT(*)FROM orders o JOINusersu ON o.user_idu.id WHERE u.city杭州GROUP BY u.id)print(\n 大表驱动小表不推荐)forrowincursor.fetchall(): print(f 表{row[2]}, type{row[3]}, key{row[6]}, rows{row[9]})# 好先过滤 users再 JOIN orderscursor.execute( EXPLAIN SELECT u.name, COUNT(*)FROMusersu JOIN orders o ON u.ido.user_id WHERE u.city杭州GROUP BY u.id)print(\n 小表驱动大表推荐)forrowincursor.fetchall(): print(f 表{row[2]}, type{row[3]}, key{row[6]}, rows{row[9]})5.3 为 JOIN 列建索引看一个有索引和无索引的天壤之别# 先故意删掉索引cursor.execute(DROP INDEX idx_user ON orders)# 无索引的 JOINstarttime.time()cursor.execute(SELECT u.name, o.order_no FROM users u JOIN orders o ON u.id o.user_id WHERE u.id 500 LIMIT 100)print(f无索引 JOIN 耗时: {time.time() - start:.4f} 秒)# 重建索引cursor.execute(CREATE INDEX idx_user ON orders(user_id))# 有索引的 JOINstarttime.time()cursor.execute(SELECT u.name, o.order_no FROM users u JOIN orders o ON u.id o.user_id WHERE u.id 500 LIMIT 100)print(f有索引 JOIN 耗时: {time.time() - start:.4f} 秒)预期输出数据量越大差距越明显无索引 JOIN 耗时:0.2100秒 有索引 JOIN 耗时:0.0012秒6. 用 Python 封装 SQL 优化检查器结合上篇的 EXPLAIN 分析器加上本篇的规则检测class SQLOptimizer: def __init__(self, conn): self.connconn self.cursorconn.cursor()def check_implicit_conversion(self, sql, params):检测隐式类型转换如果参数类型与列类型不匹配给出警告# 简单规则检查 LIKE、函数包裹等模式sql_uppersql.upper()warnings[]# 检测函数包裹索引列dangerous_patterns[(YEAR(,对日期列使用 YEAR() 会导致索引失效改用 BETWEEN),(LEFT(,对字符串列使用 LEFT() 会导致索引失效改用 LIKE prefix%),(CONCAT(,CONCAT 包裹列导致索引失效拆分为多个条件),]forpattern, msgindangerous_patterns:ifpatterninsql_upper: warnings.append(f⚠️ {msg})# 检测 LIKE 中缀/后缀ifLIKE %insql_upper: warnings.append(⚠️ LIKE %xxx 或 LIKE %xxx% 无法使用索引)returnwarnings def check_pagination(self, sql, params):检测深分页风险 sql_uppersql.upper()ifOFFSETinsql_upper:return[ 大偏移量分页建议改用游标分页或延迟关联]return[]# 使用optimizerSQLOptimizer(conn)warningsoptimizer.check_implicit_conversion(SELECT * FROM orders WHERE YEAR(created_date) 2025,[])forwinwarnings: print(w)warningsoptimizer.check_pagination(SELECT * FROM orders LIMIT 10 OFFSET 100000,[])forwinwarnings: print(w)7. 动手试试优化实战挑战基于users和orders表完成以下练习修复隐式转换写一条故意用整数查询phone列的 SQL然后用 EXPLAIN 证明它全表扫描。改成正确的写法对比 type 和 rows。优化分页对orders表执行LIMIT 10 OFFSET 190000测量耗时。然后改写为延迟关联和游标分页两种方式分别测量耗时并计算提升百分比。JOIN 优化查询“北京用户的所有订单”先写一版无 JOIN 索引的删掉idx_user再写一版有索引的用 EXPLAIN 对比。综合诊断把你写过的最慢的一条 SQL 放进第 16 篇的ExplainAnalyzer根据诊断报告进行优化直到健康评分 ≥ 8。8. 总结今天我们掌握了 SQL 优化最常见的三大类技法避免索引失效参数类型与列类型一致、WHERE 条件中不对索引列使用函数、LIKE 只用前缀匹配。分页深翻优化延迟关联先索引分页再回表、游标分页基于主键增量加载。多表 JOIN小表驱动大表、JOIN 列必建索引、用 STRAIGHT_JOIN 控制驱动表顺序。记住每一条优化都有可量化的效果。善用 EXPLAIN 和计时器让数据说话而不是凭感觉调优。下一篇我们将深入事务隔离级别与 MVCC理解 InnoDB 如何在并发与一致性之间取得平衡。下次见想了解更多还可以去各个平台搜索「IT策士」一起升级 IT 思维