# SQL 优化

【专家视角】揭开SQL优化神秘面纱【适用于升职加薪】

武以快为尊。同理,快速高效工作,同样的工作时长,却创造更多企业价值,凸显个人价值,才能立于不败之地。 本章将从专家的视角,为你揭开SQL优化神秘面纱,解锁SQL优化的升职加薪技能。让你在工作中比别人技高一筹, 助你在工作中对SQL优化,慢查询优化能有独到的企业级解决方案,为你的高薪保驾护航。...

笔者总结,本章实际讲解:讲解慢查询如何配置、如何分析、怎么使用 explain 查看 SQL 执行计划。

# 优化 SQL 的一般步骤

  • 发现问题
  • 分析执行计划
  • 优化索引
  • 改写 SQL:如果索引优化解决不了,就考虑改写成另外的 SQL
  • 数据库垂直切分:如果上述都无法解决,那么久需要考虑分库分表了
  • 数据库水平切分

# 常见问题发现渠道

  • 用户主动上报应用性能问题:被动

    一般是用户发现某些功能发现问题或则缓慢

  • 分析慢查询日志发现存在问题的 SQL

    可以指定一定的实际阀值,超过该值的 SQL 将会被主动记录在慢查询日志中

  • 数据库实时监控长时间运行的 SQL

    比如数据库压力徒增,需要实时监控有问题的 SQL

# 配置慢查询日志

可以通过以下语句动态开关

set global slow_query_log = [ON | OFF]
1

可以通过以下语句指定慢查询日志的位置,如果不指定则默认会写在 data 目录下

set global slow_query_log_file = /sql_log/slowlog.log
1

通过如下语句设置条件,满足此条件则记录

-- 设置超过多少时间的查找则记录
-- 设置为 0 则记录有的查询
set global long_query_time = xxx.xxxx 秒 
-- 设置未使用索引的语句记录
set global log_queries_not_using_indexes = [No | OFF]
1
2
3
4
5

# 分析 MySQL 慢查询日志

当条件设置得比较宽泛时,会产生很多的慢查询 SQL,就需要借助工具来分析了:

  • mysqldumpslow [OPTS...] [LOGS...] :mysq 官网提供

    一般把日志文件作为参数

  • pt-query-digest [OPTIONS][FILES][DSN]:更加好用的一款工具,是 Percona toolkit 工具包中的

他们都有把多次出现的 SQL 抽象为一个 SQL 来分析,大大提高和简化了分析

# Percona toolkit 的使用

Percona toolkit,在下载页面中选择 percona-toolkit-3.2.0-1.el7.x86_64.rpm

在存 储库页面 中找到存储库的安装方式

[root@study local]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@study local]# yum install percona-toolkit-3.2.0-1.el7.x86_64.rpm 
# 上述下载的包如果安装不了,就只能在线安装了
[root@study local]# yum install percona-toolkit
1
2
3
4

# 配置 MySQL 慢查询日志

-- 先查询目前的时间
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
-- 我们本机开发呢,设置为 0 记录是由的 sql
-- 工作中,一般设置为 0.001 秒
mysql> set global long_query_time = 0;
-- 查询慢查询日志位置
mysql> show variables like 'slow_query_log_file';
-- 查询和打开慢查询日志
mysql> show variables like 'slow_query_log';
mysql> set global slow_query_log = on;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

配置好了之后,就随意执行几条 SQL 就可以在日志文件中看到了,下面例举一个 SQL 执行记录

/* ApplicationName=DataGrip 2020.1.2 */ select count(*) from imc_course;
# Time: 2020-04-29T15:35:20.396723Z
# User@Host: root[root] @ gateway [192.168.56.1]  Id:    10
# Query_time: 0.000164  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0

1. 查询语句,前面的注释是用工具查询自动生成的,表示了是来自哪个工具
2. 查询时间
3. 哪个用户,来自那台主机,线程 ID = 10
4. 查询耗时、锁定时间、
1
2
3
4
5
6
7
8
9

# mysqldumpslow

[root@study ~]# cd /usr/local/mysql/sql_log
[root@study sql_log]# mysqldumpslow slowlog.log 

Reading mysql slow query log from slowlog.log
Count: 1  Time=0.04s (0s)  Lock=0.01s (0s)  Rows=501.0 (501), root[root]@gateway
  /* ApplicationName=DataGrip N.N.N */ SELECT t.*
  FROM imc_db.imc_chapter t
  LIMIT N
...
有该条 sql 执行的次数、执行的时间、锁定时、返回的行数、哪个用户执行的
1
2
3
4
5
6
7
8
9
10

# pt-query-digest

[root@study sql_log]# pt-query-digest slowlog.log 
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 possibly with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
...
# 180ms user time, 30ms system time, 29.25M rss, 242.34M vsz
# Current date: Wed Apr 29 23:59:18 2020
# Hostname: study.centos.mrcode
# Files: slowlog.log
# Overall: 108 total, 18 unique, 0.14 QPS, 0.00x concurrency _____________
# Time range: 2020-04-29T15:31:41 to 2020-04-29T15:44:24
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           93ms    52us    47ms   858us     1ms     4ms   194us
# Lock time           11ms       0     6ms    99us       0   632us       0
# Rows sent            536       0     501    4.96    0.99   46.62       0
# Rows examine       2.68k       0   1.10k   25.42       0  156.98       0
# Query size         6.25k      13     856   59.28   72.65  132.77   36.69

# Profile
# Rank Query ID                         Response time Calls R/Call V/M   I
# ==== ================================ ============= ===== ====== ===== =
#    1 0x217EA386BFC0686D9D58C106714...  0.0467 50.3%     1 0.0467  0.00 SELECT imc_db.imc_chapter
#    2 0xE77769C62EF669AA7DD5F6760F2...  0.0117 12.6%     2 0.0058  0.00 SHOW VARIABLES
#    3 0xA86446692DE3CA32943798A0901...  0.0063  6.8%    30 0.0002  0.00 SHOW WARNINGS
#    4 0xB1D4F88B1115C3B5D8D05AAE016...  0.0055  6.0%     2 0.0028  0.00 SELECT imc_course
#    5 0x83C1E5898A01E697921459BF4F1...  0.0043  4.7%    10 0.0004  0.00 SELECT
#    6 0x7A2B64842DCEDE58C32EFD50D94...  0.0034  3.7%     9 0.0004  0.00 SELECT
#    7 0xDBF338083F09B28EB07A6B495E6...  0.0030  3.3%     3 0.0010  0.00 SELECT
#    8 0xA11944C87A6A5C16FB38455BF70...  0.0030  3.2%     6 0.0005  0.00 SELECT
#    9 0xE8390778DC20D4CC04FE01C5B31...  0.0018  1.9%    16 0.0001  0.00 ADMIN PING
#   10 0xE16545BB40117FC1F9F96162CEE...  0.0014  1.5%     6 0.0002  0.00 
#   11 0xCFA134076E32C4608D274F9D0D1...  0.0014  1.5%     6 0.0002  0.00 
# MISC 0xMISC  

# Query 7: 0.01 QPS, 0.00x concurrency, ID 0xDBF338083F09B28EB07A6B495E60CF2A at byte 15710
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-04-29T15:31:41 to 2020-04-29T15:41:33
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2       3
# Exec time      3     3ms   222us     2ms     1ms     2ms   886us   515us
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       3       1       1       1       1       0       1
# Rows examine   0       0       0       0       0       0       0       0
# Query size    40   2.51k     856     856     856     856       0     856
# String:
# Databases    imc_db
# Hosts        gateway
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  ################################
#  10ms
# 100ms
#    1s
#  10s+
# EXPLAIN /*!50100 PARTITIONS*/
/* mysql-connector-java-8.0.15 (Revision: 79a4336f140499bd22dd07f02b708e163844e3d5) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout\G

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

上面的日志信息字段说明

# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2       3
# Exec time      3     3ms   222us     2ms     1ms     2ms   886us   515us
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       3       1       1       1       1       0       1
# Rows examine   0       0       0       0       0       0       0       0
# Query size    40   2.51k     856     856     856     856       0     856
横轴的表头是下面所有公用的:
 pct:占这份日志中总的百分比,这个需要配合 attribute 来看。
 比如 count:执行次数占日志中的总次数百 2%,总共执行了 3exec time:执行时间占总的 3%,总耗时 3ms、最小 222us、最大 2ms、平均耗时 1 ms、百分之 95 的耗时是 2ms
1
2
3
4
5
6
7
8
9
10
11
12

# 详细的字段含义

第一部分:总体统计结果

Overall:总共有多少条查询
Time range:查询执行的时间范围
unique:唯一查询数量,即对查询条件进行参数化以后,总共有多少个不同的查询
total:总计   min:最小   max:最大  avg:平均
95%:把所有值从小到大排列,位置位于 95% 的那个数,这个数一般最具有参考价值
median:中位数,把所有值从小到大排列,位置位于中间那个数
  
# 该工具执行日志分析的用户时间,系统时间,物理内存占用大小,虚拟内存占用大小
# 340ms user time, 140ms system time, 23.99M rss, 203.11M vsz
# 工具执行时间
# Current date: Fri Nov 25 02:37:18 2016
# 运行分析工具的主机名
# Hostname: localhost.localdomain
# 被分析的文件名
# Files: slow.log
# 语句总数量,唯一的语句数量,QPS,并发数
# Overall: 2 total, 2 unique, 0.01 QPS, 0.01x concurrency ________________
# 日志记录的时间范围
# Time range: 2016-11-22 06:06:18 to 06:11:40
# 属性                总计      最小     最大    平均     95%  标准    中等
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# 语句执行时间
# Exec time             3s   640ms      2s      1s      2s   999ms      1s
# 锁占用时间
# Lock time            1ms       0     1ms   723us     1ms     1ms   723us
# 发送到客户端的行数
# Rows sent              5       1       4    2.50       4    2.12    2.50
# select语句扫描行数
# Rows examine     186.17k       0 186.17k  93.09k 186.17k 131.64k  93.09k
# 查询的字符数
# Query size           455      15     440  227.50     440  300.52  227.50
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

第二部分:查询分组统计结果

Rank:所有语句的排名,默认按查询时间降序排列,通过--order-by指定
Query ID:语句的ID,(去掉多余空格和文本字符,计算hash值)
Response:总的响应时间
time:该查询在本次分析中总的时间占比
calls:执行次数,即本次分析总共有多少条这种类型的查询语句
R/Call:平均每次执行的响应时间
V/M:响应时间Variance-to-mean的比率
Item:查询对象

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0xF9A57DD5A41825CA  2.0529 76.2%     1 2.0529  0.00 SELECT
#    2 0x4194D8F83F4F9365  0.6401 23.8%     1 0.6401  0.00 SELECT wx_member_base
1
2
3
4
5
6
7
8
9
10
11
12
13
14

第三部分:每一种查询的详细统计结果

由下面查询的详细统计结果,最上面的表格列出了执行次数、最大、最小、平均、95%等各项目的统计。
ID:查询的ID号,和上图的Query ID对应
Databases:数据库名
Users:各个用户执行的次数(占比)
Query_time distribution :查询时间分布, 长短体现区间占比,本例中1s-10s之间查询数量是10s以上的两倍。
Tables:查询中涉及到的表
Explain:SQL语句

# Query 1: 0 QPS, 0x concurrency, ID 0xF9A57DD5A41825CA at byte 802 ______
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2016-11-22 06:11:40
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         50       1
# Exec time     76      2s      2s      2s      2s      2s       0      2s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent     20       1       1       1       1       1       0       1
# Rows examine   0       0       0       0       0       0       0       0
# Query size     3      15      15      15      15      15       0      15
# String:
# Databases    test
# Hosts        192.168.8.1
# Users        mysql
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# EXPLAIN /*!50100 PARTITIONS*/
select sleep(2)\G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# 实时获取需要优化的 SQL

通过是监控实时发现问题

前面通过慢查询日志来获取有问题的 SQL,这种方式需要一定的周期。想要快速的获取,就需要通过监控来获取

这里提供一种监控长时间允许的 SQL 的方式:

information_schema.PROCESSLIST:是一个视图,该视图中存储了当前正在执行的 SQL 信息

select id, user, host, db, command, time, state, info
from information_schema.PROCESSLIST
where time >= 60

-- time 是 sql 执行的时长,比如上面是大于 60 秒的执行 sql
1
2
3
4
5

# 获取 SQL 的执行计划

# 为什么要关注执行计划?

  • 了解 SQL 如何访问表中的数据

    是使用全表扫描、还是索引等方式来获取的

  • 了解 SQL 如何使用表中的索引

    是否使用到了正确的索引

  • 了解 SQL 锁使用的查询类型

    是否使用到了子查询、关联查询等信息

# 如何获取执行计划(EXPLAIN)

可通过 EXPLAIN 来获取到 SQL的执行计划

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {
    FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

-- explainable_stmt:要分析的静态 SQL 语句
-- FOR CONNECTION connection_id:可以分析正在执行的 SQL 语句,这里的 connection_id 就是上面 information_schema.PROCESSLIST 中的 id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# 执行计划内容分析 explain

下面使用查询学习人数大于 3000 的查询 SQL,来了解下 explain 相关信息

explain
select course_id, title, study_cnt
from imc_course
where study_cnt > 3000
1
2
3
4
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE imc_course NULL ALL NULL NULL NULL NULL 100 33.33 Using where

# id 列

id 会有两种值:

  • 数值:查询中的 SQL 数据对数据库对象操作的顺序
  • NULL:这一行数据是由另外两个查询进行 union 后产生的

执行顺序:

  • 当 ID 相同时由上到下执行。
  • 当 ID 不同时,由大到小执行

第一个 SQL:由于只有一行执行计划,下面来看一个复杂一点的执行计划

-- 查询课程的分类名称、难度等级、课程名称、学习人数
explain
select course_id, class_name, level_name, title, study_cnt
from imc_course a
         join imc_class b on a.class_id = b.class_id
         join imc_level c on a.level_id = c.level_id
where study_cnt > 3000
1
2
3
4
5
6
7
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE c NULL ALL PRIMARY NULL NULL NULL 4 100 NULL
1 SIMPLE a NULL ALL NULL NULL NULL NULL 100 3.33 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE b NULL eq_ref PRIMARY PRIMARY 2 imc_db.a.class_id 1 100 NULL

id 相同的,由上到下来分析计划,第一行的 table = c:指以该表为主表作为循环嵌套来查询的,虽然我们用 a 来作为主表查询的,实际上执行是以 c 为主表

第二个 SQL:再来看下 id 不同的计划分析

explain
select a.course_id, a.title
from imc_course a
where a.course_id not in (select b.course_id from imc_chapter b)
1
2
3
4
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY a NULL index NULL udx_title 62 NULL 100 100 Using where; Using index
2 DEPENDENT SUBQUERY b NULL index_subquery udx_couseid udx_couseid 4 func 13 100 Using index

第三个 SQL:带 union 查询的计划

explain
select course_id, class_name, level_name, title, study_cnt
from imc_course a
         join imc_class b on a.class_id = b.class_id
         join imc_level c on a.level_id = c.level_id
where study_cnt > 3000
union
select course_id, class_name, level_name, title, study_cnt
from imc_course a
         join imc_class b on a.class_id = b.class_id
         join imc_level c on a.level_id = c.level_id
where class_name = 'MySQL'
1
2
3
4
5
6
7
8
9
10
11
12
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY c NULL ALL PRIMARY NULL NULL NULL 4 100 NULL
1 PRIMARY a NULL ALL NULL NULL NULL NULL 100 3.33 Using where; Using join buffer (Block Nested Loop)
1 PRIMARY b NULL eq_ref PRIMARY PRIMARY 2 imc_db.a.class_id 1 100 NULL
2 UNION b NULL ALL PRIMARY NULL NULL NULL 13 10 Using where
2 UNION a NULL ALL NULL NULL NULL NULL 100 10 Using where; Using join buffer (Block Nested Loop)
2 UNION c NULL eq_ref PRIMARY PRIMARY 2 imc_db.a.level_id 1 100 NULL
NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary

# select_type 列

有以下几种类型:

  • SIMPLE:不包含子查询或是 UNION 操作的查询
  • PRIMARY:查询中如果包含任何子查询,那么最外层的查询则被标记为 PRIMARY
  • SUBQUERY:SELECT 列表中的子查询
  • DEPENDENT SUBQUERY:依赖外部结果的子查询
  • UNION:union 操作的第二个或是之后的查询的值为 union
  • DEPENDENT UNION:当 UNION 作为子查询时,第二或是第二个后的查询的 select_type 值
  • UNION RESULT:UNION 产生的结果集
  • DERIVED:出现在 FROM 子句中的子查询

刚刚第一个 SQL 中,使用了 join 查询,但是是 SIMPLE 的类型、第二个 SQL 中,id=1 的计划就讲 a 表标记为了 PRIMARy

# table 列

表示执行计划表中的数据是由哪个表输出的

  • 表名:指明从哪个表中获取数据,有原始表名,或则别名
  • <unionM,N>:表示由 ID 为 M.N 查询 union 产生的结果集
  • <derived N>/<subquery N>:由 ID 为 N 的查询产生的结果集

# partitions 列

只有在查询分区表的时候,才会显示分区表的 ID

# type 列

通常通过 type 可以知道查询使用的连接类型。

TIP

在 MySQL 中,并不是只有通过 join 产生的关联查询才叫关联查询。

就算只查询一个表,也会认为是一个连接查询

type 按照性能从高到低排列如下:

  • system (性能最高)

    这是 const 连接类型的一个特列,当查询的表只有一行时使用

  • const

    表中有且只有一个匹配的行时使用,如对住建或是唯一索引的查询,这是效率最高的连接方式

  • eq_ref

    唯一索引或主键引查找,对于每个索引键,表中只有一条记录与之匹配;

    是以前面表返回的数据行为基础,对于每一行数据都会从本表中读取一行数据

  • ref

    非唯一索引查找、返回匹配某个单独值的所有行

  • ref_or_null

    类似于 ref 类型的查询,但是附加了对 NULL 值列的查询

  • index_merge

    该连接类型表示使用了索引合并优化方法;

    mysql 5.6 之前,一般只支持一个索引查询,后来支持索引合并之后,可以支持多个索引查询

  • range

    索引范围扫描,常见于 between、>< 这样的查询条件

  • index

    FULL index Scan 全索引扫描,同 ALL 的区别是,遍历的是索引树

  • ALL (性能最底)

    FULL TABLE Scan 权标扫表这是效率最差的连接方式

# possible_keys 列

指出查询中可能会用到的索引

# key 列

possible_keys 列出的是可能使用到的索引,key 则是表示实际使用到的索引

  • 如果为 NULL,则表示该表中没有使用到索引
  • 如果出现的值,没有存在 possible_keys 中,查询可能使用到的是覆盖索引

# key_len 列

实际使用索引的最大长度。

**注意:**比如在一个联合索引中,如果有 3 列且总字节长度是 100 字节,key_len 可能少于 100 字节的,比如只有 30 个字节,这就说明在查询中没有到联合索引中的所有列,而是只使用到了联合索引中的部分列。

注意: 这一列的字节计算方式是以表中定义列的字节方式,而不是数据的实际长度

# ref 列

指出哪些列或常量被用于索引查找;

下面通过一个简单的语句来说明下现在所知道的列信息

explain
select *
from imc_user a
where user_id = 1
1
2
3
4
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE a NULL const PRIMARY PRIMARY 4 const 1 100 NULL

只出现了一行:

  • 是 SIMPLE 类
  • 从 a 表中输出的数据
  • 不是查询的分区表
  • type=const:是常量,user_id = 1 我们用了一个具体的数值 1
  • 有可能使用到的索引有:PRIMARY
  • 实际使用到的索引:PRIMARY (主键索引)
  • 索引的长度是 4 ,这个表定义的 id 就是占用 4 字节
  • 使用的是常量过滤

# rows 列

有两方面的含义:

  • 根据统计信息预估的扫描的行数

  • 在关联查询中,也表示内嵌循环的次数

    每获取一个值,就要对目标表进行一次查找,循环越多,性能就越差

# filtered 列

与 rows 有一定的关系,表示返回结果的行数占需要读取行数(rows)的百分比。

也是一个预估值,不太准确,但是可以在一定程度上可以预估 mysql 的查询成本。

该值越高,效率也越高

# Extra 列

包含了不适合在其他列中显示的一些额外信息。常见的值有如下:

  • Distinct

    优化 distinct 操作,在找到第一匹配的元组后即停止找同样值的动作

  • Not exists

    使用 not exists 来优化查询

  • Using filesort

    使用文件来进行排序,通常会出现在 order by 或 group by 查询中

  • Using index

    使用了覆盖索引进行查询;

    覆盖索引:查询中使用到的信息是完全可以通过索引信息来获取的,而不用去从表中的数据进行访问

  • Using temporary

    MySQL 需要使用临时表来处理查询,常见于排序、子查询、和分组查询

  • Using where

    需要在 MySQL 服务器层使用 Where 条件来过滤数据

  • select tables optimizedaway

    直接通过索引来获得数据,不用访问标

TIP

当该列的值出现了 Using temporary 时,就需要重点关注了,通常来说性能不会太好

# explain 列含义汇总整理

  • id

    • 数值:查询中的 SQL 数据对数据库对象操作的顺序
    • NULL:这一行数据是由另外两个查询进行 union 后产生的

    当 ID 相同时由上到下执行,当 ID 不同时,由大到小执行

  • select_type : 查询类型

    • SIMPLE:不包含子查询或是 UNION 操作的查询
    • PRIMARY:查询中如果包含任何子查询,那么最外层的查询则被标记为 PRIMARY
    • SUBQUERY:SELECT 列表中的子查询
    • DEPENDENT SUBQUERY:依赖外部结果的子查询
    • UNION:union 操作的第二个或是之后的查询的值为 union
    • DEPENDENT UNION:当 UNION 作为子查询时,第二或是第二个后的查询的 select_type 值
    • UNION RESULT:UNION 产生的结果集
    • DERIVED:出现在 FROM 子句中的子查询
  • table :表示执行计划表中的数据是由哪个表输出的

    • 表名:指明从哪个表中获取数据,有原始表名,或则别名
    • <unionM,N>:表示由 ID 为 M.N 查询 union 产生的结果集
    • <derived N>/<subquery N>:由 ID 为 N 的查询产生的结果集
  • partitions:只有在查询分区表的时候,才会显示分区表的 ID

  • type:通常通过 type 可以知道查询使用的连接类型。

    type 按照性能从高到低排列如下:

    • system (性能最高)

      这是 const 连接类型的一个特列,当查询的表只有一行时使用

    • const

      表中有且只有一个匹配的行时使用,如对住建或是唯一索引的查询,这是效率最高的连接方式

    • eq_ref

      唯一索引或主键引查找,对于每个索引键,表中只有一条记录与之匹配;

      是以前面表返回的数据行为基础,对于每一行数据都会从本表中读取一行数据

    • ref

      非唯一索引查找、返回匹配某个单独值的所有行

    • ref_or_null

      类似于 ref 类型的查询,但是附加了对 NULL 值列的查询

    • index_merge

      该连接类型表示使用了索引合并优化方法;

      mysql 5.6 之前,一般只支持一个索引查询,后来支持索引合并之后,可以支持多个索引查询

    • range

      索引范围扫描,常见于 between、>< 这样的查询条件

    • index

      FULL index Scan 全索引扫描,同 ALL 的区别是,遍历的是索引树

    • ALL (性能最底)

      FULL TABLE Scan 权标扫表这是效率最差的连接方式

  • possible_keys:指出查询中可能会用到的索引

  • key:表示实际使用到的索引

    • 如果为 NULL,则表示该表中没有使用到索引
    • 如果出现的值,没有存在 possible_keys 中,查询可能使用到的是覆盖索引
  • key_len:实际使用索引的最大长度

    这一列的字节计算方式是以表中定义列的字节方式,而不是数据的实际长度

  • ref:指出哪些列或常量被用于索引查找

  • rows:

    有两方面的含义:

    • 根据统计信息预估的扫描的行数

    • 在关联查询中,也表示内嵌循环的次数

      每获取一个值,就要对目标表进行一次查找,循环越多,性能就越差

  • filtered:表示返回结果的行数占需要读取行数(rows)的百分比。

  • Extra:包含了不适合在其他列中显示的一些额外信息

    常见的信息有:

    • Distinct

    优化 distinct 操作,在找到第一匹配的元组后即停止找同样值的动作

    • Not exists

      使用 not exists 来优化查询

    • Using filesort

      使用文件来进行排序,通常会出现在 order by 或 group by 查询中

    • Using index

      使用了覆盖索引进行查询;

      覆盖索引:查询中使用到的信息是完全可以通过索引信息来获取的,而不用去从表中的数据进行访问

    • Using temporary

      MySQL 需要使用临时表来处理查询,常见于排序、子查询、和分组查询

    • Using where

      需要在 MySQL 服务器层使用 Where 条件来过滤数据

    • select tables optimizedaway

      直接通过索引来获得数据,不用访问标