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)
是指某些查询结果无法被数据库缓存或重用
,因此每次执行查询时,都会重新计算这些结果。 数据库通常会缓存一些查询的结果,以便在相同的查询再次执行时直接返回缓存的结果,避免重复计算。然而,当查询的某些部分是动态的、不可预测的或依赖外部因素时,数据库无法缓存这些结果,必须每次重新计算。
无法缓存的原因
非确定性函数
:使用了每次执行时都会返回不同结果的函数,比如RAND()、NOW()、UUID()
等。这些函数的输出是不可预测的,无法缓存。
依赖外部变量
:查询依赖于外部输入(如用户输入的变量)或会话状态,使得每次执行时,输入可能不同。
外部数据源
:查询可能依赖外部数据源(如外部表或临时生成的数据),这些数据在每次执行
(理解)物化子查询
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