小惠珠哦

这个屌丝很懒，什么也没留下！

热门标签

MySQL索引的性能优化

作者：小惠珠哦 | 2024-08-19 18:01:57

踩

MySQL索引的性能优化

1.数据库服务器的优化步骤

在数据库调优中，我们的目标就是响应时间更快，吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式

数据库服务器的优化步骤

当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。

整个流程划分成了观察（Show status）和行动（Action）两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）

首先在S1部分，需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动，有可能是周期性节点的原因，比如双十一、促销活动等。这样的话，可以通过A1这一步骤解决，也就是加缓存，或者更改缓存失效策略。

如果缓存策略没有解决，或者不是周期性波动的原因，就需要进一步分析查询延迟和卡顿的原因。接下来进入S2这一步，需要开启慢查询。慢查询可以帮我们定位执行慢的SQL语句。可以通过设置long_query_time参数定义“慢""的阈值，如果SQL执行时间超过了long query_time，则会认为是慢查询。当收集上来这些慢查询之后，就可以通过分析工具对慢查询日志进行分析。

在S3这一步骤中，我们就知道了执行慢的SQL，这样就可以针对性地用EXPLAIN查看对应SQL语句的执行计划，或者使用show profile查看SQL中每一个步骤的时间成本。这样就可以了解SQL查询慢是因为执行时间长，还是等待时间长。

如果是SQL等待时间长，就进入A2步骤。在这一步骤中，可以调优服务器的参数，比如适当增加数据库缓冲池等。如果是SQL执行时间长，就进入A3步骤，这一步中需要考虑是索引设计的问题?还是查询关联的数据表过多?还是因为数据表的字段设计问题导致了这一现象。然后在这些维度上进行对应的调整。
如果A2和A3都不能解决问题，需要考虑数据库自身的SQL查询性能是否已经达到了瓶颈，如果确认没有达到性能瓶颈，就需要重新检查，重复以上的步骤。如果已经达到了性能瓶颈，进入A4阶段，需要考虑增加服务器，采用读写分离的架构，或者考虑对数据库进行分库分表，比如垂直分库、垂直分表和水平分表等。

以上就是数据库调忧的流程思路。如果发现执行SQL时存在不规则延迟或卡顿的时候，就可以采用分析工具帮我们定位有问题的SQL，这三种分析工具可以理解是SQL调优的三个步骤:慢查询、EXPLAIN和SHOEW　PROFILING

小结：

2.查询系统性能参数

在MySQL中，`SHOW STATUS`是一个用于显示服务器状态的命令，它可以提供有关服务器运行时的大量信息，包括各种计数器和状态值。这些信息对于诊断问题、性能调优和了解服务器的运行情况非常有用。

使用`SHOW STATUS`时，可以指定不同的参数来过滤显示的信息：

`SHOW STATUS`：显示所有状态变量。

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

一些常用的性能参数如下：

Connections：连接MySQL服务器的次数。
Uptime：MySQL服务器的上线时间。
Slow_queries：慢查询的次数。
Innodb_rows_read：Select查询返回的行数
Innodb_rows_inserted：执行INSERT操作插入的行数
Innodb_rows_updated：执行UPDATE操作更新的行数
Innodb_rows_deleted：执行DELETE操作删除的行数
Com_select：查询操作的次数。
Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
Com_update：更新操作的次数。
Com_delete：删除操作的次数。

若查询MySQL服务器的连接次数，则可以执行如下语句：

SHOW STATUS LIKE 'Connections';

若查询服务器工作时间，则可以执行如下语句：

SHOW STATUS LIKE 'Uptime'；

若查询MySQL服务器的慢查询次数，则可以执行如下语句：

SHOW STATUS LIKE 'Slow_queries'；

3. 统计SQL的查询成本：last_query_cost

一条SQL查询语句在执行前需要确定查询执行计划，如果存在多种执行计划的话，MySQL会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划。

如果想要查看某条SQL语句的查询成本，可以在执行完这条SQL语句之后，通过查看当前会话中的last_query_cost变量值来得到当前查询的成本。它通常也是评价一个查询的执行效率的一个常用指标。这个查询成本对应的是SQL语句所需要读取的页的数量


CREATE TABLE `student_info` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`student_id` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`course_id` INT NOT NULL ,
`class_id` INT(11) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

如果想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：


SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id = 900001;
#运行结果（1 条记录，运行时间为 0.042s ）

然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：


SHOW STATUS LIKE 'last_query_cost';
/*
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
*/

如果要查询 id 在 900001 到 9000100 之间的学生记录呢？


SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id BETWEEN 900001 AND 900100;

运行结果（100 条记录，运行时间为 0.046s ）

然后再看下查询优化器的成本，这时我们大概需要进行 20 个页的查询


 SHOW STATUS LIKE 'last_query_cost';
 /*
+-----------------+-----------+
| Variable_name | Value       |
+-----------------+-----------+
| Last_query_cost | 21.134453 |
+-----------------+-----------+
*/

能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然页数量（last_query_cost）增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间。

使用场景：它对于比较开销是非常有用的，特别是有好几种查询方式可选的时候

SQL查询是一个动态的过程，从页加载的角度来看，可以得到以下两点结论:

位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。
批量决定效率。如果从磁盘中对单一页进行随机读，那么效率是很低的（差不多10ms)，而采用顺序读取的方式，批主对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

所以说，遇到I/O并不用担心，方法找对了，效率还是很高的。首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

4.定位执行慢的 SQL：慢查询日志

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上(不含10秒)的语句，认为是超出了最大忍耐时间值。

它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sql执行超过5秒钟，就算慢SQL，希望能收集超过5秒的sql，结合explain进行全面分析。

默认情况下，MySQL数据库没有开启慢查询日志，需要手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件。


show variables like 'slow_query_log';
/*
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
*/

4.1 开启慢查询日志参数

1.开启slow_query_log

在使用前，需要先看下慢查询是否已经开启，使用下面这条命令即可:


show variables like 'slow_query_log';
/*
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
*/

我们能看到slow_query_log=OFF，可以把慢查询日志打开，注意设置变量值的时候需要使用global，否则会报错:

set global slow_query_log='ON';

再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：


show variables like '%slow_query_log%';
 
+---------------------+-----------------------------------------+
| Variable_name       | Value                                   |
+---------------------+-----------------------------------------+
| slow_query_log      | ON                                      |
| slow_query_log_file | /var/lib/mysql/centos7-mysql-1-slow.log |
+---------------------+-----------------------------------------+
2 rows in set (0.00 sec)

能看到这时慢查询分析已经开启，同时文件保存在/var/lib/mysql/LSLNO1-slow.log 文件中

2. 修改long_query_time阈值


#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句
set global long_query_time = 1;
show global variables like '%long_query_time%';
 
set long_query_time=1;
show variables like '%long_query_time%';
/*
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
*/

持久化设置：使用set global语句所做的更改在数据库服务器重启之后将不再生效。要使更改持久化，你需要讲新的值添加到MySQL的配置文件中（my.ini或者my.cnf）。

如果不指定存储路径，慢查询日志将默认存储到MySQL数据库的数据文件夹下。如果不指定文件名，默认文件名为hostname-slow.log

4.2 关闭慢日志查询

关闭慢查询日志通常是为了减少数据库的性能开销，因为记录慢查询日志会占用额外的资源。

方式：临时性方式

使用SET语句来设置。
（1）停止MySQL慢查询日志功能，具体SQL语句如下


SET GLOBAL slow_query_log=off;
 
SHOW VARIABLES LIKE '%slow%';
/*
+---------------------------+--------------------------------+
| Variable_name             | Value                          |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF                            |
| log_slow_extra            | OFF                            |
| log_slow_slave_statements | OFF                            |
| slow_launch_time          | 2                              |
| slow_query_log            | OFF                            |
| slow_query_log_file       | /var/lib/mysql/LSLNO1-slow.log |
+---------------------------+--------------------------------+
*/
#以及
SHOW VARIABLES LIKE '%long_query_time%';
/*
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
*/

使用命令mysqladmin flush-logs 来重新生成查询日志文件，具体命令如下，执行完毕会在数据目录下重新生成慢查询日志文件

mysqladmin -uroot -p flush-logs slow


# 在打开的条件下，执行上面的语句，才可以看见重置的日志文件
SET GLOBAL slow_query_log=on;

提示:
慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须享先备份。

5. 查看 SQL 执行成本：SHOW PROFILE

这个是一个用来显示服务器性能相关统计信息的命令。使用show profile可以查看服务器在执行sql语句时的资源使用情况，例如cpu时间，块I/O操作，上下文切换等。对于性能调优非常有用。

Show Profile是MySQL提供的可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。


show variables like 'profiling';
/*
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
*/

通过设profiling='ON’来开启show profile :

set profiling = 'ON';

show profile使用演示：


use atguigudb;
 
select * from student where stuno =343455;
--
 
select * from student where name = 'vyituS';
--
 
show profiles;
列出自从服务器启动以来或从上一次执行reset profiling命令以来所有已经记录查询的性能剖析信息
 
/*
+----------+------------+---------------------------------------------+
| Query_ID | Duration   | Query                                       |
+----------+------------+---------------------------------------------+
|        1 | 0.00174700 | show variables like 'profiling'             |
|        2 | 1.52700950 | select * from student where stuno =343455   |
|        3 | 1.20279475 | select * from student where name = 'vyituS' |
+----------+------------+---------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
*/
 
 
show profile;
用于显示最近执行的查询性能剖析信息，包括了查询周期中的各个阶段所
花费的时间详情，例如解析，优化，执行查询等阶段
 
/*
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000083 |
| Executing hook on transaction  | 0.000004 |
| starting                       | 0.000009 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000044 |
| init                           | 0.000004 |
| System lock                    | 0.000008 |
| optimizing                     | 0.000008 |
| statistics                     | 0.000019 |
| preparing                      | 0.000018 |
| executing                      | 1.202507 |
| end                            | 0.000024 |
| query end                      | 0.000005 |
| waiting for handler commit     | 0.000010 |
| closing tables                 | 0.000012 |
| freeing items                  | 0.000023 |
| cleaning up                    | 0.000013 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)
*/

作用范围：

（1）这个命令只是在本会话内起作用，即无法分析本会话外的语句。开启分析功能后，所有本会话中的语句都被分析（甚至包括执行错误的语句），除了SHOW PROFILE和SHOW PROFILES两句本身。
（2）profiling是会话级的，当会话结束，与之相关的profiling信息也会随之消失。
（3）profiling是针对进程(process)而非线程(threads),因此运行在服务器上的其他服务进程可能会影响分析结果.

show profile的常用查询参数:

ALL:显示所有的开销信息。
BLOCK IO:显示块IO开销。
CONTEXT SWITCHES:上下文切换开销。
CPU:显示CPU开销信息。
IPC:显示发送和接收开销信息
MEMORY:显示内存开销信息。
PAGE FAULTS:显示页面错误开销信息。
SOURCE:显示和Source_function，Source_file，Source_line相关的开销信息。
SWAPS:显示交换次数开销信息。

6. 分析查询语句：EXPLAIN

EXPLAIN是一个常用的sql命令，用于获取数据库执行的详细信息，它可以帮助开发者了解查询的执行计划，包括查询的步骤，数据访问路径，索引使用情况等，这有助于优化查询性能。

有什么用？

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行优化器查询

举例：


CREATE TABLE employees (
    id INT AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE,
    PRIMARY KEY (id)
);
 
并且我们想要查询所有在 '2000-01-01' 后雇佣的员工。我们可以用以下 SQL 查询来实现这个目标：
 
 
SELECT  FROM employees WHERE hire_date > '2000-01-01';

如果我们想知道这个查询是如何被执行的，可以使用 EXPLAIN 命令。下面是 EXPLAIN 的使用方法

EXPLAIN SELECT  FROM employees WHERE hire_date > '2000-01-01';

假设 employees 表上没有针对 hire_date 的索引，那么 EXPLAIN 的输出可能类似于下面这样：


+----+-------------+----------+----+------+---------------+---------+---------+-------+--------+----------+
| id | select_type | table    | type | possible_keys | key     | key_len | ref   | rows   | filtered | Extra   |
+----+-------------+----------+----+------+---------------+---------+---------+-------+--------+----------+
|  1 | SIMPLE      | employees| ALL | NULL          | NULL    | NULL    | NULL  | 100000 |   10.00 | Using where |
+----+-------------+----------+----+------+---------------+---------+---------+-------+--------+----------+

- id: 1 - 查询的选择序列号。
- select_type: SIMPLE - 这个查询是最简单的 SELECT 类型。
- table: employees - 被查询的表名。
- type: ALL - 表明这是一个全表扫描，因为没有合适的索引来加速查询。
- possible_keys: NULL - 没有可用的索引。
- key: NULL - 没有使用任何索引。
- key_len: NULL - 因为没有使用索引，所以这个值也是 NULL。
- ref: NULL - 没有使用索引或者常量来查找行。
- rows: 100000 - 预计需要检查的行数（假设表中有 100,000 行数据）。
- filtered: 10.00 - 过滤掉的行百分比（这里意味着大约 10% 的行将通过 WHERE 子句的过滤）。
- Extra: Using where - 表明 WHERE 子句中的条件是在表扫描过程中应用的。

从上面的输出可以看出，由于没有针对 hire_date 字段的索引，查询需要进行全表扫描，这可能会很慢如果表很大。为了优化查询，我们可以添加一个索引：

ALTER TABLE employees ADD INDEX idx_hire_date (hire_date);

再次运行 EXPLAIN 命令，这次查询计划可能会变得更高效：


EXPLAIN SELECT  FROM employees WHERE hire_date > '2000-01-01';
 
 
+----+-------------+----------+--------+---------------+---------+---------+-------+------+----------+----------------+
| id | select_type | table    | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra          |
+----+-------------+----------+--------+---------------+---------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | employees| range  | idx_hire_date | idx_hire_date | 5      | NULL | 10000    | 100.00         | Using where; Using index |
+----+-------------+----------+--------+---------------+---------+---------+-------+------+----------+----------------+

这次的输出显示：

- type: range - 使用了索引范围扫描。
- possible_keys: idx_hire_date - 可用的索引。
- key: idx_hire_date - 使用的索引。
- key_len: 5 - 索引使用的字节数（日期类型的索引长度通常是 5 字节）。
- rows: 10000 - 预计需要检查的行数减少了很多。
- Extra: Using where; Using index - 表明 WHERE 子句的条件使用了索引，同时也表示查询结果可以直接从索引中获取而不需要访问实际的数据行。

合适的场景使用：

EXPLAIN 在多种情况下都非常有用，特别是在需要优化查询性能的情况下。以下是几种 EXPLAIN 特别有效的场景：

1. 复杂查询

对于包含多个表连接、子查询或联合操作的复杂查询，EXPLAIN 可以帮助你理解查询优化器是如何处理这些组件的。你可以查看每个表的连接类型、索引使用情况等，从而确定是否有可能进一步优化查询。

2. 性能瓶颈

如果你发现某个查询特别慢，使用 EXPLAIN 可以帮助识别问题所在。例如，如果 EXPLAIN 显示查询正在执行全表扫描（type: ALL），那么添加适当的索引可能会显著提高查询速度。

3. 索引评估

当你考虑添加新索引或调整现有索引时，EXPLAIN 是一个很好的工具来评估这些变化的效果。通过比较添加索引前后的 EXPLAIN 输出，你可以看到索引是否被利用以及查询性能的改进程度。

举例：

让我们来看一个具体的例子。假设我们有一个包含两个表的简单数据库：orders 和 customers。


CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
 
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT,
    name VARCHAR(50),
    address VARCHAR(100),
    PRIMARY KEY (customer_id)
);

假设我们需要找出所有在2020年下单的客户的名字和地址。我们可以编写如下的查询：


SELECT c.name, c.address
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN '2020-01-01' AND '2020-12-31';

现在，我们使用 EXPLAIN 来查看这个查询的执行计划：


EXPLAIN SELECT c.name, c.address
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN '2020-01-01' AND '2020-12-31';

如果没有针对 order_date 的索引，EXPLAIN 输出可能显示全表扫描或低效的连接类型。为了提高性能，我们可以为 orders 表添加一个索引：

ALTER TABLE orders ADD INDEX idx_order_date (order_date);

6.1 EXPLAIN的进一步使用

这里谈谈EXPLAIN的输出格式。EXPLAIN可以输出四种格式： 传统格式 ， JSON格式 ， TREE格式 以及可视化输出 。用户可以根据需要选择适用于自己的格式

1.传统格式

传统格式（Text Format）是 EXPLAIN 命令最常用的输出格式，它以文本形式显示查询计划。这种格式提供了查询中各个步骤的简要描述，并且通常包含有关操作类型、关联的表、使用的索引等信息。

示例：

假设有一个简单的 SQL 查询：

SELECT  FROM employees WHERE department = 'engineering';

我们可以使用 EXPLAIN 命令查看其执行计划：

EXPLAIN SELECT  FROM employees WHERE department = 'engineering';

假定数据库返回的输出类似于下面的内容：


+----+-------------+----------+--------+------------------+------------------+---------+------+---------+-------------+
| id | select_type | table    | type   | possible_keys    | key              | key_len | ref  | rows    | filtered    | Extra       |
+----+-------------+----------+--------+------------------+------------------+---------+------+---------+-------------+
|  1 | SIMPLE      | employees| range  | department       | department       | 100     | NULL | 1000    | 10.00       | Using where |
+----+-------------+----------+--------+------------------+------------------+---------+------+---------+-------------+

解释

- id: 1 - 这个查询块是主查询。
- select_type: SIMPLE - 这是一个简单的查询。
- table: employees - 查询涉及的表。
- type: range - 数据库将使用索引进行范围查找。
- possible_keys: department - 可能使用的索引是 department。
- key: department - 实际上使用了 department 索引。
- key_len: 100 - 使用的索引键的最大长度为 100 字节。
- ref: NULL - 没有使用引用。
- rows: 1000 - 数据库估计需要检查 1000 行。
- filtered: 10.00 - 大约只有 10% 的行满足条件。
- Extra: Using where - 查询使用了 WHERE 子句来过滤数据。

2.JSON格式

JSON 格式是一种结构化的数据格式，常用于网络传输和配置文件中。当使用 EXPLAIN 命令时，JSON 格式可以提供一种易于解析的数据结构，方便开发人员和自动化工具进一步处理查询计划信息。

JSON 格式的结构

JSON 格式的查询计划通常包含以下主要部分：

- query_block: 主查询块的信息，包括选择类型、涉及的表及其访问类型等。
- table: 关联表的信息，如表名、索引使用情况等。
- nested loop: 对于涉及子查询的情况，可能包含嵌套循环的信息。

示例

假设我们有如下 SQL 查询：

SELECT  FROM employees WHERE department = 'engineering';

我们可以使用 EXPLAIN 命令并指定 JSON 格式来查看其执行计划：

EXPLAIN FORMAT=JSON SELECT  FROM employees WHERE department = 'engineering';

输出示例

下面是该查询的一个可能的 JSON 格式输出：


{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "employees",
      "access_type": "range",
      "possible_keys": ["department"],
      "key": "department",
      "key_len": "100",
      "ref": null,
      "rows_examined": 1000,
      "rows_produced_per_join": 100,
      "filtered": "10.00",
      "Extra": "Using where"
    }
  }
}

解释

- query_block: 主查询块的信息。

  - select_id: 1 - 查询块的 ID。

  - table: 关联表的信息。

    - table_name: "employees" - 查询涉及的表。

    - access_type: "range" - 数据库将使用索引进行范围查找。

    - possible_keys: ["department"] - 可能使用的索引列表。

    - key: "department" - 实际使用的索引。

    - key_len: "100" - 使用的索引键的最大长度。

    - ref: null - 没有使用引用。

    - rows_examined: 1000 - 数据库估计需要检查的行数。

    - rows_produced_per_join: 100 - 每次连接产生的行数。

    - filtered: "10.00" - 大约只有 10% 的行满足条件。

    - Extra: "Using where" - 查询使用了 WHERE 子句来过滤数据

3.Tree格式

TREE 格式是 EXPLAIN 命令的一种输出格式，它以树状结构来表示查询计划。这种格式对于理解复杂的嵌套查询特别有用，因为它能够清晰地展示查询的层次结构。

TREE 格式的结构

TREE 格式的输出通常包含以下元素：

- 操作节点: 每个操作节点都代表查询计划中的一个操作，如表扫描、索引扫描、嵌套循环连接等。

- 属性: 每个操作节点下面列出与该操作相关的属性，如表名、索引、连接条件等。

- 子节点: 复杂的操作可能包含子节点，例如嵌套循环连接中的内部和外部表。


SELECT e.name, d.department_name 
FROM employees e 
JOIN departments d ON e.department_id = d.department_id 
WHERE e.salary > 50000;

我们可以使用 EXPLAIN 命令并指定 TREE 格式来查看其执行计划：


EXPLAIN (FORMAT TREE) SELECT e.name, d.department_name 
FROM employees e 
JOIN departments d ON e.department_id = d.department_id 
WHERE e.salary > 50000;

输出：


QUERY PLAN
└─ Nested Loop  (cost=0.00..10000.00 rows=100 width=100)
   ├─ Index Scan using idx_salary on employees e  (cost=0.00..5000.00 rows=100 width=100)
   │   ├─ Filter: (e.salary > 50000)
   └─ Index Scan using pk_departments on departments d  (cost=0.00..100.00 rows=1 width=100)
        ├─ Index Cond: (d.department_id = e.department_id)

- QUERY PLAN: 查询计划的根节点。

- Nested Loop: 嵌套循环连接操作，表示将两个表连接在一起。

  - Index Scan using idx_salary on employees e: 索引扫描操作，使用名为 idx_salary 的索引来访问 employees 表。

    - Filter: 过滤条件 (e.salary > 50000)。

  - Index Scan using pk_departments on departments d: 索引扫描操作，使用名为 pk_departments 的索引来访问 departments 表。

    - Index Cond: 索引条件 (d.department_id = e.department_id)。

本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】