面试官：sql优化你的思路

引言

mysql优化是我们经常面临的问题，也是面试必问之一；为什么面试官这么喜欢问sql优化，主要有两点，一是基础知识的掌握；二是项目的熟悉程度。
因此sql优化也分两部分：一是项目背景下修改代码逻辑；二是sql语句的优化；
本文本次主要介绍第二部分sql语句的优化。
那么sql优化也分为两部分。首先，我们要了解sql的执行循序和执行计划，通过分析执行计划我们才能更好地进行sql的优化

正文

1.1. mysql的执行顺序

在MySQL中，SQL查询的执行顺序与其书写顺序不同。了解执行顺序有助于优化查询性能。以下是MySQL查询的典型执行顺序：

1. FROM

• 确定数据源，进行表的连接操作（JOIN）。

2. ON

• 对连接操作中的ON条件进行过滤。

3. JOIN

• 执行表连接操作。

4. WHERE

• 过滤不符合条件的记录。

5. GROUP BY

• 对结果进行分组。

6. HAVING

• 过滤分组后的结果。

7. SELECT

• 选择需要的列。

8. DISTINCT

• 去除重复的记录。

9. ORDER BY

• 对结果进行排序。

10. LIMIT

- 限制返回的记录数。

说明

• FROM 和 JOIN: 从指定的表中获取数据，并根据JOIN条件进行表连接。
• WHERE: 在数据分组前进行行过滤，通常能减少数据量。
• GROUP BY: 在过滤后进行分组操作。
• HAVING: 在分组后进行过滤，适用于聚合函数。
• SELECT: 提取所需的列。
• DISTINCT: 去重操作在SELECT之后进行。
• ORDER BY: 排序在SELECT之后执行，可能会影响性能。
• LIMIT: 最后应用，减少返回的数据量。

通过理解这些步骤，可以更好地优化查询，确保高效的数据检索。

mysql的执行计划

在MySQL中，使用 EXPLAIN 语句可以查看查询的执行计划，帮助识别性能瓶颈。以下是 EXPLAIN 输出的各个字段及其含义：

1. id

• 含义: 查询中每个SELECT子句的标识符。
• 说明: 数字越大，优先级越高。相同的 id 表示可以并行执行，不同的 id 表示按顺序执行。

2. select_type

• 含义: SELECT的类型。
• 常见值:
  • SIMPLE: 简单SELECT查询，不包含子查询或UNION。
  • PRIMARY: 最外层的SELECT。
  • SUBQUERY: 子查询中的SELECT。
  • DERIVED: 派生表（子查询中的FROM子句）。
  • UNION: UNION中的第二个或后续的SELECT。
  • UNION RESULT: 从UNION中获取结果的SELECT。

3. table

• 含义: 查询的表。
• 说明: 显示当前行正在访问的表名。

4. type

• 含义: 连接类型，表示查询使用的访问方法。
• 常见值（从好到坏）:
  • system: 表只有一行（等于系统表）。
  • const: 表最多有一个匹配行。
  • eq_ref: 对于每个来自前一个表的行组合，从表中读取一行。
  • ref: 使用非唯一索引扫描。
  • range: 使用索引范围扫描。
  • index: 全索引扫描。
  • ALL: 全表扫描。

5. possible_keys

• 含义: 查询中可能使用的索引。
• 说明: 显示查询中可能用到的索引列表。

6. key

• 含义: 实际使用的索引。
• 说明: 显示查询优化器选择的索引。

7. key_len

• 含义: 使用的索引长度。
• 说明: 索引中使用的字节数。越短越好。

8. ref

• 含义: 索引的比较列。
• 说明: 显示索引与哪些列或常量进行比较。

9. rows

• 含义: 估计需要读取的行数。
• 说明: 估算值，表示MySQL认为需要检查的行数。

10. filtered

• 含义: 返回结果的行占读取行的百分比。
• 说明: 表示经过WHERE条件过滤后的行比例。

11. Extra

• 含义: 额外的信息。
• 常见值:
  • Using where: 使用WHERE过滤。
  • Using index: 使用覆盖索引（只访问索引，不访问表）。
  • Using temporary: 使用临时表。
  • Using filesort: 使用文件排序（通常是排序操作）。

分析和优化建议

1. 优先使用索引: 确保查询中的条件列上有适当的索引。
2. 避免全表扫描: 通过优化查询条件和索引避免 ALL 类型的扫描。
3. 使用覆盖索引: 如果可能，使用覆盖索引来减少回表操作。
4. 优化连接顺序: 确保连接顺序合理，减少不必要的行扫描。
5. 避免使用临时表和文件排序: 通过优化查询结构来避免 Using temporary 和 Using filesort。

通过仔细分析 EXPLAIN 的输出，可以识别出sql执行使用索引情况，从而进行sql语句及索引的调整。