Mysql高级篇（中）——SQL性能分析

一、（了解）MySQL Query Optimizer 的主要功能和原理

MySQL Query Optimizer 是 MySQL 用来决定如何高效执行 SQL 查询的核心组件，它会基于查询的逻辑结构、表的统计信息、索引的使用等多种因素来选择最佳的执行计划。理解 Query Optimizer 的工作原理对于提高查询性能非常重要。

二、（了解）MySQL 常见瓶颈

MySQL 常见瓶颈 通常分为以下几个方面，涵盖了 硬件资源、数据库配置、查询设计、以及并发处理 等问题。识别和解决这些瓶颈可以显著提高 MySQL 性能。

总结来说，MySQL 常见瓶颈通常与硬件资源（CPU、内存、磁盘）、查询设计（索引、锁、事务）以及数据库配置相关。通过监控、分析执行计划、调整配置和优化查询，可以有效解决这些瓶颈并提升数据库性能。

三、关键字 EXPLAIN

1、是什么

EXPLAIN 关键字是 MySQL 中用于分析查询执行计划的工具，帮助开发者了解查询语句是如何被优化器执行的。通过 EXPLAIN，可以 获得关于表的访问顺序、索引使用情况、连接方式等详细信息，从而有针对性地优化查询。

2、基本语法

EXPLAIN + SQL 语句

• 执行 EXPLAIN 后，MySQL 会输出一张表，显示查询各个部分的执行计划。这个输出的结果可以帮助理解查询在数据库中的执行路径。

执行计划包含的信息：

信息	作用简述
id	标识 查询中每个步骤的顺序
select_type	标识 查询的类型，描述查询是简单查询、联合查询，还是子查询。
table	表示查询中 正在访问的表
partitions	显示查询涉及的表分区（如果有）
type	描述 MySQL 访问表的方式，反映 查询的效率
possible_keys	列出查询中 可能使用的索引
key	显示实际被优化器选择用于查询的索引
key_len	表示 MySQL 使用的索引长度（单位为字节）
ref	显示 哪个字段或常量与索引列进行比较
rows	优化器估计需要读取的行数
filtered	显示通过 WHERE 子句过滤后的行百分比（0-100%）
Extra	提供关于查询执行的额外信息，特别是某些性能影响因素的提示

3、EXPLAIN 执行信息详解

（1）id

在 MySQL 的 EXPLAIN 输出中，id 列用于表示查询的执行步骤和顺序。对于复杂的查询，尤其是涉及多个子查询或联合查询时，id 列能够帮助你理解 每个查询部分的执行流程及其依赖关系。

==通过 `id` 的递增顺序，可以清楚地了解`查询的执行流程`，尤其是在`多表连接、子查询或联合查询`的情况下，帮助确定 `MySQL` 在执行查询时的`实际步骤和顺序`。这对查询优化特别重要，可以`帮助你识别哪些部分的查询是性能瓶颈或需要重构`的。==

（2）select_type

select_type 是帮助理解复杂查询执行方式的重要信息，它 表明每个查询部分的类型，通过分析 select_type，可以识别出查询中的子查询、联合查询、依赖查询等类型，并结合索引和查询优化技术，提升查询性能

select_type值类型	简述
SIMPLE	没有子查询和联合（UNION）的简单查询
PRIMARY	查询中最外层的查询
SUBQUERY	子查询
DERIVED	派生表（即 FROM 子句中的子查询）
UNION	联合查询中的第二个或后续查询
UNION RESULT	用于保存联合查询的结果集
DEPENDENT SUBQUERY	依赖于外部查询的子查询
DEPENDENT UNION	依赖外部查询的联合查询
UNCACHEABLE SUBQUERY	不能被缓存的子查询
UNCACHEABLE UNION	不能被缓存的联合查询
MATERIALIZED	物化子查询,存储为临时表

示例参考：

EXPLAIN SELECT name FROM employees WHERE age IN (SELECT age FROM employees WHERE department_id = employees.department_id);

EXPLAIN
SELECT id FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE manager_id = employees.manager_id)
UNION
SELECT id FROM employees WHERE department_id = 3;

EXPLAIN SELECT * FROM employees WHERE id IN (SELECT id FROM (SELECT id FROM employees WHERE age > 30) AS temp);

（理解）无法缓存

无法缓存（Uncacheable） 是指某些查询结果无法被数据库缓存或重用，因此每次执行查询时，都会重新计算这些结果。 数据库通常会缓存一些查询的结果，以便在相同的查询再次执行时直接返回缓存的结果，避免重复计算。然而，当查询的某些部分是动态的、不可预测的或依赖外部因素时，数据库无法缓存这些结果，必须每次重新计算。

无法缓存的原因

1. 非确定性函数：使用了每次执行时都会返回不同结果的函数，比如 RAND()、NOW()、UUID() 等。这些函数的输出是不可预测的，无法缓存。
   
   
2. 依赖外部变量：查询依赖于外部输入（如用户输入的变量）或会话状态，使得每次执行时，输入可能不同。
   
   
3. 外部数据源：查询可能依赖外部数据源（如外部表或临时生成的数据），这些数据在每次执行

（理解）物化子查询

SELECT e.name, e.salary
FROM employees e
WHERE e.department_id IN (
    SELECT department_id
    FROM departments
    WHERE manager_id > 100
) 
AND e.department_id IN (
    SELECT department_id
    FROM departments
    WHERE manager_id > 100
);

在这个查询中，子查询 SELECT department_id FROM departments WHERE manager_id > 100 被多次使用。通常，数据库会在每次遇到该子查询时重新执行一次。但是如果数据库能够将该子查询的结果“物化”，即存储在一个临时表中，那么它只需要执行一次子查询，并且可以在后续查询中直接使用物化的结果，从而提高查询效率。

（3）table

table 列显示了查询中正在处理的表或派生表的名称

（4）partitions

CREATE TABLE orders (
  id INT,
  order_date DATE,
  customer_id INT,
  amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2019 VALUES LESS THAN (2020),
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022)
);

-- 查询该表中 2020 年的订单：
EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2020-01-01' AND '2020-12-31';

（5）type

EXPLAIN 命令的输出信息包含了一列 type，这列用于 显示数据库在执行查询时所使用的访问方法（或称访问类型）。type 是优化查询性能的重要指标，它展示了查询如何访问表中的数据，以及查询是否使用了索引、全表扫描等。通过分析 type 列，我们可以了解查询的效率，并决定是否需要进行优化。

type常见值	序号（1~7对应的type列的值表示效率从低到高）
ALL	1
index	2
range	3
ref	4
eq_ref	5
const/system	6
NULL	7

每种类型的访问方式代表 数据库如何检索数据，越靠前的值表示性能越差，越靠后的值表示性能越好

示例详解：

• 假设有两个表 users 和 orders， 结构如下：
  表 users 记录用户信息，表 orders 记录订单信息，其中 orders.user_id 是外键，关联到 users.id。
  

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    INDEX (user_id)
);

（6）possible_keys

possible_keys，它列出了在执行查询时可能使用的索引; 它与key 的关系: key表示查询执行时实际使用的索引

• 定义： possible_keys 是指 MySQL 在 优化查询时认为可能适用的索引。如果 possible_keys 为空（NULL），表示没有索引被认为是合适的，意味着优化器将进行全表扫描。
  
  
• 影响因素： possible_keys 受查询的 WHERE 条件、JOIN 条件、ORDER BY、GROUP BY 等影响。

（7）key

key，它显示了数据库优化器在查询执行过程中实际选择使用的索引，而不仅仅是可能使用的（这由 possible_keys 字段列出）。

• 定义： key 表示数据库在查询执行过程中实际使用的索引。这个值能够帮助你了解数据库引擎是否正确地使用了索引，以提升查询性能。
  
  
• 与 possible_keys 的关系： possible_keys 列出所有可能的索引，而 key 则表示查询执行时实际使用的索引。如果 key 为空，表示查询没有使用索引，通常会导致全表扫描。

（8）key_len

key_len，MySQL 在生成查询计划时，使用的索引字段的最大可能字节数。 它反映了 MySQL 根据索引结构、字段类型以及查询条件所估算的索引长度。 需要注意的是，key_len 并不表示实际执行时的动态使用长度，而是静态估算值。

key_len 示例分析: 假设有一个 employees 表结构如下：

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2),
    INDEX idx_name_dept (first_name, department_id)
);

（9）ref

ref 列主要用于描述表与表之间是如何通过索引关联的， 即在执行查询时，某个表中的列是如何与另一个表中的索引列进行比较的。

ref 列显示的是查询中的列或常量如何与索引字段匹配，通常会在多表连接（JOIN）查询中体现。它的值可以是常量、列名、NULL 等，用于指示索引匹配的方式。

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10, 2),
  INDEX (user_id)
);

（10）rows

rows 列 表示 MySQL 在执行查询时，预估需要扫描的记录行数。这一列的数据基于统计信息，并 不代表最终的实际行数，而是 MySQL 用来估算查询成本的依据。

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department_id INT,
  INDEX (department_id)
);

（11）filtered

filtered 列表示 查询结果中的行在每个步骤中剩余的比例， 即经过 WHERE 子句过滤后，剩下的行数占总扫描行数的百分比。这个值可以帮助判断过滤条件的有效性，值越接近 100%，说明条件过滤效果差，越接近 0%，说明条件过滤得非常严格。

filtered 列的值是一个百分比（0 到 100），它的计算基于 MySQL 对表中数据的统计信息和查询的过滤条件，并不表示实际扫描或返回的行数，而是预估的比例。

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department_id INT,
  salary DECIMAL(10, 2),
  INDEX (department_id)
);

（12）Extra

Extra 是 EXPLAIN 输出中的一个字段，它提供了有关查询执行过程中额外的详细信息。 Extra 字段中的信息可以揭示 MySQL 在查询执行时做出的特殊操作或优化决策，这些信息有助于理解查询的性能表现，并能帮助找出潜在的优化点。

常见的 Extra 字段输出及其含义

理解 Using index condition