SQL优化与执行计划详解

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT

FROM > WHERE > GROUP BY > HAVING > SELECT > DISTINCT > ORDER BY > LIMIT

SELECT DISTINCT player_id, player_name, count(*) as num  # 顺序5
FROM player JOIN team ON player.team_id = team.team_id   # 顺序1
WHERE height > 1.80                                      # 顺序2
GROUP BY player.team_id                                  # 顺序3
HAVING num > 2                                           # 顺序4
ORDER BY num DESC                                        # 顺序6
LIMIT 2                                                  # 顺序7

SQL优化与执行计划详解 一、SQL语句执行顺序 理解SQL语句的执行顺序是优化慢SQL的基础，SQL语句中各关键词的执行顺序如下： FROM & JOIN ：确定表之间的连接关系，得到初步数据 单表连接： from table1 join table2 on table1.id=table2.id 多表连接： from table1,table2 where table1.id=table2.id 不加关联条件会出现笛卡尔积 WHERE ：对符合条件的语句进行筛选 GROUP BY ：对数据进行分组（不筛选数据） HAVING ：对分组后的数据进行筛选 HAVING支持普通条件和聚合函数，WHERE只支持普通条件 WHERE+GROUP BY 与 GROUP BY+HAVING 效果几乎相同 SELECT ：选择要查询的字段或聚合函数 聚合函数会新增查询字段 重复字段名需要指明来源表 DISTINCT ：对结果去重 ORDER BY ：对结果排序 LIMIT ：分页查询（必须最后执行） SQL关键字顺序与执行顺序对比 关键字顺序 ： 执行顺序 ： 示例分析 ： 二、SQL执行计划分析 获取执行计划 在SQL语句前加上 EXPLAIN 关键词： MySQL 8.0支持对 SELECT/DELETE/INSERT/REPLACE/UPDATE 语句分析 MySQL 5.6前只支持 SELECT 语句 执行计划字段详解 1. id列 相同id：由上到下执行 不同id：由大到小执行 NULL：表示由UNION操作产生的结果集 2. select_ type列 | 值 | 含义 | |----|------| | SIMPLE | 简单查询（不包含子查询或UNION） | | PRIMARY | 包含子查询的最外层查询 | | SUBQUERY | SELECT列表中的子查询 | | DEPENDENT SUBQUERY | 依赖外部结果的子查询 | | UNION | UNION操作的第二个或之后的查询 | | DEPENDENT UNION | 作为子查询的UNION操作 | | UNION RESULT | UNION产生的结果集 | | DERIVED | FROM子句中的子查询（派生表） | 3. table列 显示表名或别名 <unionM,N> ：由ID为M、N的查询UNION产生的结果集 <subqueryN> ：由ID为N的子查询产生的临时表 4. type列（性能高→低） system ：表只有一行数据 const ：通过主键或唯一索引查询，只匹配一行 eq_ ref ：唯一索引查询，表中只有一条记录匹配 ref ：非唯一索引查询 ref_ or_ null ：类似ref，但包含NULL值查询 index_ merge ：使用了索引合并优化 range ：索引范围扫描（BETWEEN、>、 <等） index ：全索引扫描 ALL ：全表扫描（效率最低） 5. possible_ keys与key列 possible_ keys：可能使用的索引 key：实际使用的索引 覆盖索引：查询列都包含在索引中，无需回表 6. key_ len列 实际使用的索引字节数 联合索引中可判断使用了哪些列 7. ref列 显示哪些列或常量被用于索引查找 8. rows列 预估扫描行数 关联查询内嵌的次数 9. filtered列 返回结果行数占需读取行数的百分比 数值越高性能越好 10. Extra列 | 值 | 含义 | |----|------| | Distinct | 优化DISTINCT操作 | | Not exists | 使用NOT EXISTS优化查询 | | Using filesort | 使用文件排序（ORDER BY/GROUP BY） | | Using index | 使用覆盖索引 | | Using temporary | 使用临时表（排序/子查询/分组） | | Using where | 服务器层使用WHERE过滤 | | select tables optimized away | 直接通过索引获取数据 | 三、索引失效场景 最左前缀原则 ：联合索引必须从最左列开始使用 索引列计算 ：对索引列使用函数、计算或类型转换 IS NOT NULL ：索引列使用IS NOT NULL条件 LIKE通配 ：LIKE以%开头 OR连接 ：索引列用OR连接条件 隐式转换 ：字符串类型不加引号 四、优化实践问题 MySQL查询排序稳定性 ：了解排序是否稳定 force_ index使用 ：强制使用特定索引的方法 ORDER BY id失效 ：分析为何有时会导致索引失效 索引选择性 ：高选择性字段更适合建索引 覆盖索引优化 ：减少回表操作 五、总结 优化慢SQL需要深入理解： SQL语句执行顺序与执行计划 索引工作原理及失效场景 MySQL底层执行机制 各种查询类型的性能特点 通过分析执行计划，可以准确找出SQL性能瓶颈，针对性地进行优化。