MySQL 慢查询定位与 SQL 性能优化实战指南

文章目录

• 如何定位并解决慢查询？
• • 1. 开启/检查慢日志
  • 2. 分析日志
  • 3. 用explain分析执行计划
• SQL优化？
• • 一、基础优化
  • • 1. 避免select *
    • 2. 使用合适的where条件
    • 3. 合理使用索引
    • 4. 避免全表扫描
  • 二、JOIN优化（多表查询）
  • • 1. 大表驱动小表
    • 2. 确保JOIN字段都有索引
    • 3. 避免多层嵌套JOIN
  • 三、子查询 vsJOIN
  • 分页优化
• 如何创建、使用索引？
• • 索引介绍
  • 一、创建索引
  • • 1. 创建普通索引
    • 2. 创建唯一索引
    • 3. 创建复合索引
    • 4. 在建表时直接定义索引
    • 5. 添加主键（自动添加聚簇索引）

如何定位并解决慢查询？

1. 开启/检查慢日志

• 看一下是否开启慢日志

SHOWVARIABLESLIKE'slow_query_log';SHOWVARIABLESLIKE'long_query_time';SHOWVARIABLESLIKE'slow_query_log_file';

• 如果未开启，临时开启（生产环境建议永久配置）：

SETGLOBALslow_query_log=ON;SETGLOBALlong_query_time=1;

2. 分析日志

• mysqldumpslow（MySQL 自带）

  # 按执行次数排序前10条mysqldumpslow -s c -t10/var/log/mysql/slow.log# 按总耗时排序前10条mysqldumpslow -s t -t10/var/log/mysql/slow.log

3. 用explain分析执行计划

• 在SQL前面加explain

  EXPLAINSELECTid,order_noFROMordersWHEREuser_id=100ANDcreate_time>='2024-01-01'ORDERBYcreate_timeDESC;

  • 重点查看四个字段

字段	看什么
type	是否出现 ALL（全表扫描）
rows	扫描行数是否过大
key	是否使用到了正确索引
Extra	是否出现Using filesort或Using temporary

SQL优化？

一、基础优化

1. 避免select *

-- ❌ 不推荐SELECT*FROMusers;-- ✅ 推荐SELECTid,name,emailFROMusers;

2. 使用合适的where条件

• 尽量在where中使用索引字段
• 避免对字段进行函数操作或类型转换(导致索引失效)

  -- ❌ 索引失效SELECT*FROMordersWHEREYEAR(create_time)=2024;-- ✅ 使用范围查询，可走索引SELECT*FROMordersWHEREcreate_time>='2024-01-01'ANDcreate_time<'2025-01-01';

3. 合理使用索引

• 对经常用于where、join、order by、group by的列建立索引
• 比卖你过度索引(影响写入性能)
• 考虑使用复合索引（最左前缀原则）

4. 避免全表扫描

• 通过explain检查是否使用了索引

  EXPLAINSELECT*FROMproductsWHEREcategory_id=10;

二、JOIN优化（多表查询）

1. 大表驱动小表

• 在MySQL中，通常将小结果姐放在left，大表在right

2. 确保JOIN字段都有索引

• 两个表关联字段都应该有索引

3. 避免多层嵌套JOIN

• 复杂JOIN可拆分为多个简单查询

三、子查询 vsJOIN

• 子查询在某些数据库中效率较低，可以尝试改成JOIN

  -- ❌ 子查询（可能低效）SELECT*FROMusersWHEREidIN(SELECTuser_idFROMordersWHEREamount>100);-- ✅ 改写为JOINSELECTDISTINCTu.*FROMusers uJOINorders oONu.id=o.user_idWHEREo.amount>100;

分页优化

• 深分页（如LIMIT 100000,20）性能查，因为要跳过大量的数据
  • 优化方案：
    • 使用游标分页（基于上一页最后一条记录的ID或时间）：

  SELECT*FROMmessagesWHEREid>100000ORDERBYidLIMIT20;

如何创建、使用索引？

索引介绍

索引类型	说明
主键索引	聚簇索引，数据按主键物理存储，每一张表只能一个
唯一索引	不允许出现重复值
普通索引	最基本的索引，允许重复和null
全文索引	用于文本搜索
前缀索引	对字符串类的前N个字段创建索引，节省空间
覆盖索引	非独立类型，查询字段全部包含在索引中，无需回表

一、创建索引

1. 创建普通索引

-- 方法1：CREATE INDEX（推荐用于已有表）CREATEINDEXindex_nameONtable_name(column_name);-- 示例：在 users 表的 email 字段上创建索引CREATEINDEXidx_emailONusers(email);

2. 创建唯一索引

CREATEUNIQUEINDEXidx_usernameONusers(username);

3. 创建复合索引

• 符合索引使用时必须遵循最左前缀原则，查询时必须包含最左边的列才能生效

-- 按顺序：先按 category_id，再按 created_at 排序CREATEINDEXidx_category_createdONproducts(category_id,created_at);

4. 在建表时直接定义索引

CREATETABLEorders(idBIGINTPRIMARYKEYAUTO_INCREMENT,user_idINTNOTNULL,statusVARCHAR(20),created_atDATETIME,-- 主键自动创建聚簇索引（InnoDB）INDEXidx_user_status(user_id,status),-- 普通复合索引UNIQUEINDEXuk_order_no(order_no)-- 唯一索引);

5. 添加主键（自动添加聚簇索引）

ALTERTABLEtable_nameADDPRIMARYKEY(id);