当前位置： 首页 > news >正文

MySQL 系列：第17篇 SQL优化实战技法

news 2026/6/25 12:19:11

IT策士 10余年一线大厂经验，专注 IT 思维、架构、职场进阶。我会在各个平台持续发布最新文章，助你少走弯路。

上篇我们学会了用 EXPLAIN 定位慢查询，但知道问题只是第一步——如何解决才是关键。今天我们就用 Python 配合实战，把生产中最常见的三大类 SQL 性能杀手逐个击破：索引失效的陷阱、分页深翻的灾难、多表 JOIN 的优化。每个问题都配有错误示例、原理解析和优化方案，看完就能直接用。

1. 准备数据：构建百万级测试环境

importmysql.connectorimporttimeimportrandom conn=mysql.connector.connect(host="127.0.0.1",port=3306,user="root",password="MyNewPass123!",database="shop")cursor=conn.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))ENGINE=InnoDB DEFAULTCHARSET=utf8mb4""")# 订单表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)DEFAULT'pending', created_date DATE NOT NULL, INDEX idx_user(user_id), INDEX idx_date(created_date), INDEX idx_user_date(user_id, created_date))ENGINE=InnoDB DEFAULTCHARSET=utf8mb4""")# 批量插入数据print("⏳ 正在生成测试数据...")batch_size=10000total=200000cities=["北京","上海","深圳","杭州","成都","武汉","南京","西安"]foriinrange(0, total, batch_size): users_batch=[]orders_batch=[]forjinrange(batch_size): idx=i + j +1phone=f"138{idx:08d}"[:11]name=f"user_{idx}"age=random.randint(18,65)city=random.choice(cities)vip=random.randint(0,5)users_batch.append((phone,name,age,city,vip))order_no=f"ORD{idx:010d}"amount=round(random.uniform(10,5000),2)status=random.choice(['pending','completed','cancelled'])date=f"2025-{random.randint(1,12):02d}-{random.randint(1,28):02d}"orders_batch.append((idx%10000+1,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(i+batch_size, total)}/{total} 条",end="\r")print("\n✅ 测试数据准备完毕")

2. 避坑一：隐式类型转换

2.1 问题重现

phone列是VARCHAR类型，并建有索引。但如果你用整数去查询，索引就会失效。

def compare_query(sql, label): cursor.execute(f"EXPLAIN {sql}")row=cursor.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","整数查询 ")

预期输出：

字符串查询:type=ref,key=idx_phone,rows=1整数查询:type=ALL,key=None,rows=200000

同样的逻辑，用 Python 测量实际耗时差距：

start=time.time()cursor.execute("SELECT * FROM users WHERE phone = '13800000001'")cursor.fetchall()print(f"字符串查询耗时: {time.time() - start:.4f} 秒")start=time.time()cursor.execute("SELECT * FROM users WHERE phone = 13800000001")cursor.fetchall()print(f"整数查询耗时: {time.time() - start:.4f} 秒")

2.2 原因与对策

MySQL 在比较字符串和整数时，会把字符串列的值逐个转换为数字再比较。这导致：

  1. 索引中存的是原始字符串，无法直接匹配数字

  2. 必须全表扫描，逐行转换后比较

对策：永远保持查询参数的类型与列定义一致。Python 中尤其注意——用户输入的手机号、订单号等，传给 SQL 时务必用字符串：

# 好的做法phone=input("请输入手机号: ")cursor.execute("SELECT * FROM users WHERE phone = %s",(phone,))# 参数化自动处理类型# 坏的做法cursor.execute(f"SELECT * 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'","范围查询 ")

预期输出：

函数包裹列:type=ALL,key=None,rows=200000范围查询:type=range,key=idx_date,rows=100000

3.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 函数:type=ALL,key=None,rows=200000LIKE 前缀:type=range,key=idx_phone,rows=50000

LIKE 的索引规则：LIKE 'abc%'走索引（前缀匹配），LIKE '%abc'不走索引（后缀匹配），LIKE '%abc%'也不走索引（中缀匹配）。因为 B+Tree 只能从左向右匹配。

4. 避坑三：分页深翻优化

4.1 问题重现

传统LIMIT offset, count在偏移量巨大时，MySQL 仍会扫描前面所有的行并丢弃。

def test_pagination(offset):"""测试分页耗时""" start=time.time()cursor.execute(f"SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET {offset}")rows=cursor.fetchall()elapsed=time.time()- start print(f"OFFSET {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 优化方案一：延迟关联（推荐）

核心思想：先在索引上完成分页，再通过主键关联回表取完整数据。

# 传统深翻start=time.time()cursor.execute("SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 150000")traditional_time=time.time()- start# 延迟关联start=time.time()cursor.execute(""" SELECT o.* FROM orders o INNER JOIN(SELECTidFROM orders ORDER BYidLIMIT10OFFSET150000)AS tmp ON o.id=tmp.id""")deferred_time=time.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 页”），直接用上一页的最大主键作为起点：

# 传统方式：OFFSETstart=time.time()cursor.execute("SELECT id, order_no FROM orders ORDER BY id LIMIT 10 OFFSET 100000")traditional_time=time.time()- start# 游标方式：基于主键start=time.time()cursor.execute("SELECT id, order_no FROM orders WHERE id > 100000 ORDER BY id LIMIT 10")cursor_time=time.time()- start print(f"\n📊 游标分页 vs 传统分页：")print(f"传统 OFFSET 100000: {traditional_time:.4f} 秒")print(f"游标 WHERE id>100000: {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.id=o.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_id=u.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.id=o.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")# 无索引的 JOINstart=time.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)")# 有索引的 JOINstart=time.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.conn=conn self.cursor=conn.cursor()def check_implicit_conversion(self, sql, params):"""检测隐式类型转换：如果参数类型与列类型不匹配，给出警告"""# 简单规则：检查 LIKE、函数包裹等模式sql_upper=sql.upper()warnings=[]# 检测函数包裹索引列dangerous_patterns=[("YEAR(","对日期列使用 YEAR() 会导致索引失效，改用 BETWEEN"),("LEFT(","对字符串列使用 LEFT() 会导致索引失效，改用 LIKE 'prefix%'"),("CONCAT(","CONCAT 包裹列导致索引失效，拆分为多个条件"),]forpattern, msgindangerous_patterns:ifpatterninsql_upper: warnings.append(f"⚠️ {msg}")# 检测 LIKE 中缀/后缀if"LIKE '%"insql_upper: warnings.append("⚠️ LIKE '%xxx' 或 LIKE '%xxx%' 无法使用索引")returnwarnings def check_pagination(self, sql, params):"""检测深分页风险""" sql_upper=sql.upper()if"OFFSET"insql_upper:return["💡 大偏移量分页建议改用游标分页或延迟关联"]return[]# 使用optimizer=SQLOptimizer(conn)warnings=optimizer.check_implicit_conversion("SELECT * FROM orders WHERE YEAR(created_date) = 2025",[])forwinwarnings: print(w)warnings=optimizer.check_pagination("SELECT * FROM orders LIMIT 10 OFFSET 100000",[])forwinwarnings: print(w)

7. 动手试试：优化实战挑战

基于users和orders表，完成以下练习：

  1. 修复隐式转换：写一条故意用整数查询phone列的 SQL，然后用 EXPLAIN 证明它全表扫描。改成正确的写法，对比 type 和 rows。

  2. 优化分页：对orders表执行LIMIT 10 OFFSET 190000，测量耗时。然后改写为延迟关联和游标分页两种方式，分别测量耗时并计算提升百分比。

  3. JOIN 优化：查询“北京用户的所有订单”，先写一版无 JOIN 索引的（删掉idx_user），再写一版有索引的，用 EXPLAIN 对比。

  4. 综合诊断：把你写过的最慢的一条 SQL 放进第 16 篇的ExplainAnalyzer，根据诊断报告进行优化，直到健康评分 ≥ 8。

8. 总结

今天我们掌握了 SQL 优化最常见的三大类技法：

  • 避免索引失效：参数类型与列类型一致、WHERE 条件中不对索引列使用函数、LIKE 只用前缀匹配。

  • 分页深翻优化：延迟关联（先索引分页再回表）、游标分页（基于主键增量加载）。

  • 多表 JOIN：小表驱动大表、JOIN 列必建索引、用 STRAIGHT_JOIN 控制驱动表顺序。

记住：每一条优化都有可量化的效果。善用 EXPLAIN 和计时器，让数据说话，而不是凭感觉调优。

下一篇我们将深入事务隔离级别与 MVCC，理解 InnoDB 如何在并发与一致性之间取得平衡。下次见！

想了解更多还可以去各个平台搜索「IT策士」，一起升级 IT 思维 ！

http://www.jsqmd.com/news/1074769/

相关文章：

  • Embedchain实战指南：分钟级搭建企业级语义检索系统
  • 混沌序列与小波变换在遥感图像加密中的层次化编码实践
  • Embedchain：企业级RAG工程化实践与私有知识库落地指南
  • Redis 的存取速度为什么这么快
  • 汽车网络安全工程实践：从ISO/SAE 21434标准到TARA与安全设计落地
  • 每天节省30分钟！淘宝淘金币自动化脚本终极指南：一键完成所有日常任务
  • Boss直聘批量投递工具：3步让你每天多投50份简历
  • 美光 CEO 放了一句狠话：别低估造内存，这玩意比你想的难得多
  • 30分钟构建与部署OpenEMS：从零搭建开源能源管理系统
  • 数之和：从“暴力相亲”到“提前登记”——算法里的时间-空间交易哲学（哈希表）
  • 长肥网络下的单流拥塞控制对比：BBR vs KCC
  • Redis容器重启循环问题排查与数据持久化完整指南
  • Anthropic新架构层蒸发：LLM封装层为何正在归零
  • 完整指南：如何用DroneSecurity工具快速解密DJI无人机通信数据
  • AI动态简报之技术前沿篇（2026.06.24）
  • 《笨蛋美人她天生凤命》小说|下载|txt
  • 从规则引擎到大模型：NLP范式迁移与提示即架构实践
  • 人机协作新范式：2026年不容错过的专业AI论文软件
  • 化学机器学习实战：从分子特征到可部署API的七步炼金术
  • qBittorrent搜索插件终极指南：一键解锁20+种子网站资源
  • OpenCore Legacy Patcher技术深度解析：老Mac兼容性突破与性能优化终极方案
  • 3步完成Rhino到Blender的无缝转换：import_3dm插件完全指南
  • 基于Qwen3-14B与OpenClaw的AI驱动接口自动化测试实践
  • 毕业论文存在哪里安全不易丢失？2026超稳存储平台实测分享
  • 跨国出差网络自动切换方案的工程实践
  • 印度AI工程实战：多模态取舍、KAN应用与LLM生产部署
  • AI工程实战：三阶段视频生成、JAX高性能优化与LLM落地失败避坑指南
  • 同一 WiFi 下 SSH 连不上：Ping 通但 22 端口超时的排查实录
  • 如何彻底移除Microsoft Edge：EdgeRemover工具完整指南
  • TD学习实战指南：从原理到工业级部署的12条铁律