mysql系列VIII-索引(5)-SQL性能分析

发表于 更新于 分类于 ， Changyan： 本文字数： 4.7k 阅读时长 ≈ 17 分钟

对数据库的性能分析来说，就是要知道一个SQL在数据库中执行的有多快，从进到数据库，到出数据库，需要多长时间。

Mysql系列文章

  1. mysql系列I-概述

  2. mysql系列II-sql语法

    ①. mysql系列II-sql语法-(1)-概述

    ②. mysql系列II-sql语法-(2)-DDL

    ③. mysql系列II-sql语法-(3)-DML

    ④. mysql系列II-sql语法-(4)-DQL

    ⑤. mysql系列II-sql语法-(5)-DCL

  3. mysql系列III-函数

  4. mysql系列IV-约束

  5. mysql系列V-多表查询

  6. mysql系列VI-事务

  7. mysql系列VII-存储引擎

  8. mysql系列VIII-索引

    ①. mysql系列VIII-索引(1)-概述

    ②. mysql系列VIII-索引(2)-结构

    ③. mysql系列VIII-索引(3)-分类

    ④. mysql系列VIII-索引(4)-语法

    ⑤. mysql系列VIII-索引(5)-SQL性能分析

    ⑥. mysql系列VIII-索引(6)-索引使用

    ⑦. mysql系列VIII-索引(7)-设计原则

  9. mysql系列IX-SQL优化

  10. mysql系列X-视图

  11. mysql系列XI-存储过程

  12. mysql系列XII-存储函数

  13. mysql系列XIII-触发器

  14. mysql系列XIV-锁

SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信
息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

1
2
3
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';

执行结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> SHOW GLOBAL STATUS LIKE 'Com_______';
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    4711
Current database: studydata

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 1241  |
| Com_delete    | 14    |
| Com_import    | 0     |
| Com_insert    | 1027  |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 54453 |
| Com_signal    | 0     |
| Com_update    | 1759  |
| Com_xa_end    | 0     |
+---------------+-------+
11 rows in set (0.05 sec)

Com_delete: 删除次数
Com_insert: 插入次数
Com_select: 查询次数
Com_update: 更新次数

我们可以在当前数据库再执行几次查询操作，然后再次查看执行频次，看看 Com_select 参数会不会变化。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SHOW GLOBAL STATUS LIKE 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 1241  |
| Com_delete    | 14    |
| Com_import    | 0     |
| Com_insert    | 1027  |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 54458 |
| Com_signal    | 0     |
| Com_update    | 1759  |
| Com_xa_end    | 0     |
+---------------+-------+
11 rows in set (0.00 sec)

通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以查询为主，那么就要考虑对数据库的索引进行优化了。

那么通过查询SQL的执行频次，我们就能够知道当前数据库到底是增删改为主，还是查询为主。 那假如说是以查询为主，我们又该如何定位针对于那些查询语句进行优化呢？ 次数我们可以借助于慢查询日志。
接下来，我们就来介绍一下MySQL中的慢查询日志。

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set, 1 warning (0.01 sec)

可以看到我这里的慢查询日志是我已经打开过的，如果是OFF状态，要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

1
2
3
4
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log。

1
systemctl restart mysqld

然后，再次查看开关情况，慢查询日志就已经打开了。

测试

我们这里制造1000w数据模拟这个场景

导入数据

使用load data local infile导入数据，执行报错如下：

1
Loading local data is disabled; this must be enabled on both the client and server sides

属于mysql导入数据报错：local_infile服务器变量指示能否使用load data local infile命令。该变量为OFF时，禁用客户端的load data local infile命令，该变量默认为ON， 报错消失。

查看变量状态：

1
2
3
4
5
6
7
mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF    |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

解决方案：windows为my.ini文件，linux为my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[client]

# 开启导入本地sql文件
local_infile = 1

[mysql]

# 开启导入本地sql文件
local_infile = 1

[mysqld]

# 开启导入本地sql文件
local_infile = 1

重启mysql服务

再次执行导入

1
2
mysql> load data local infile 'D:/tb_sku2.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
ERROR LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

解决方案

1
2
net stop mysql
net start mysql

使用mysql -u root -p --local-infile命令先登入MySQL

再次执行导入

已成功导入，本地测试用了5个文件，每个20w条，依次导入这5个，刚好制造好1000w数据

1
2
3
mysql> load data local infile 'D:/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
Query OK, 2000000 rows affected, 1540 warnings (45.88 sec)
Records: 2000000  Deleted: 0  Skipped: 0  Warnings: 1540

对比查询sql语句

1
2
3
4
5
6
7
8
9
10
11
12
mysql>select * from tb_user; -- 这条SQL执行效率比较高, 执行耗时 0.01sec
24 rows in set (0.01 sec)


-- 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时18.11 sec
mysql> select count(*) from tb_sku;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (18.11 sec)

检查慢查询日志

最终我们发现，在慢查询日志中，只会记录执行时间超多我们预设时间（2s）的SQL，执行较快的SQL是不会记录的。

在windows中，我们可以通过my.ini中slow_query_log_file="DESKTOP-TPUK7BF-slow.log"配置，查看日志名称，

然后进入数据库Data保存目录找到这个文件打开即可看到慢日志记录

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
C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe, Version: 8.0.29 (MySQL Community Server - GPL). started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
# Time: 2022-08-02T01:43:17.055439Z
# User@Host: root[root] @ localhost [::1]  Id:     9
# Query_time: 38.558903  Lock_time: 0.000002 Rows_sent: 0  Rows_examined: 0
use studydata;
SET timestamp=1659404558;
load data local infile 'D:/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

# Time: 2022-08-02T01:44:35.017395Z
# User@Host: root[root] @ localhost [::1]  Id:     9
# Query_time: 45.880232  Lock_time: 0.000002 Rows_sent: 0  Rows_examined: 0
SET timestamp=1659404629;
load data local infile 'D:/tb_sku2.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

# Time: 2022-08-02T01:47:25.934166Z
# User@Host: root[root] @ localhost [::1]  Id:     9
# Query_time: 43.595911  Lock_time: 0.000002 Rows_sent: 0  Rows_examined: 0
SET timestamp=1659404802;
load data local infile 'D:/tb_sku3.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

# Time: 2022-08-02T01:50:15.889409Z
# User@Host: root[root] @ localhost [::1]  Id:     9
# Query_time: 44.240190  Lock_time: 0.000004 Rows_sent: 0  Rows_examined: 0
SET timestamp=1659404971;
load data local infile 'D:/tb_sku4.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

# Time: 2022-08-02T01:51:40.856591Z
# User@Host: root[root] @ localhost [::1]  Id:     9
# Query_time: 43.998160  Lock_time: 0.000002 Rows_sent: 0  Rows_examined: 0
SET timestamp=1659405056;
load data local infile 'D:/tb_sku5.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

# Time: 2022-08-02T01:55:23.098781Z
# User@Host: root[root] @ localhost [::1]  Id:     9
# Query_time: 18.112484  Lock_time: 0.000002 Rows_sent: 1  Rows_examined: 0
SET timestamp=1659405304;
select count(*) from tb_sku;

那这样，通过慢查询日志，就可以定位出执行效率比较低的SQL，从而有针对性的进行优化。

profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling
参数，能够看到当前MySQL是否支持profile操作：

1
SELECT @@have_profiling;

查询结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

可以看到，当前MySQL是支持 profile操作的，但是开关是关闭的。可以通过set语句在session/global级别开启profiling：

1
SET profiling = 1;

再次查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

开关已经打开了，接下来，我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了。 我们直接执行如下的SQL语句：

1
2
3
4
select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';
select count(*) from tb_sku;

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：

1
2
3
4
5
6
-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

查看每一条SQL的耗时情况:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show profiles;
+----------+-------------+-------------------------------------------+
| Query_ID | Duration    | Query                                     |
+----------+-------------+-------------------------------------------+
|        1 |  0.00013000 | SELECT @@have_profiling                   |
|        2 |  0.00013575 | SELECT @@profiling                        |
|        3 |  0.00773350 | select * from tb_user                     |
|        4 |  0.00026225 | select * from tb_user where id = 1        |
|        5 |  0.00048050 | select * from tb_user where name = '白起' |
|        6 | 18.17035025 | select count(*) from tb_sku               |
+----------+-------------+-------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

查看指定SQL各个阶段的耗时情况 :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> show profile for query 6;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000055 |
| Executing hook on transaction  |  0.000003 |
| starting                       |  0.000005 |
| checking permissions           |  0.000018 |
| Opening tables                 |  0.001312 |
| init                           |  0.000005 |
| System lock                    |  0.000005 |
| optimizing                     |  0.000005 |
| statistics                     |  0.000009 |
| preparing                      |  0.000012 |
| executing                      | 18.168639 |
| end                            |  0.000012 |
| query end                      |  0.000004 |
| waiting for handler commit     |  0.000011 |
| closing tables                 |  0.000019 |
| freeing items                  |  0.000081 |
| logging slow query             |  0.000134 |
| cleaning up                    |  0.000023 |
+--------------------------------+-----------+
18 rows in set, 1 warning (0.00 sec)

explain

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

语法:

1
2
-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

如：

1
2
3
4
5
6
7
mysql> explain select * from tb_user where id = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Explain 执行计划中各个字段的含义:

字段含义
idselect查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。
select_type表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
table表名
partitions指分区信息。数据库优化有分库、分表、分区；这里的分区值表的分区信息。
type表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。
possible_keys显示可能应用在这张表上的索引，一个或多个。
key实际使用的索引，如果为NULL，则没有使用索引。
key_len表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。
ref显示索引的哪一列被使用了
rowsMySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。
Extra额外信息

id

通过id值可以知道sql语句执行顺序：

id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id有相同有不同。

id相同

id 相同，执行顺序由上至下执行

id有不同

如果是子查询，id的序号会递增，id值越大优先级越高，越被执行。所以上例id=3先被执行，type=ALL做的是一个全表扫描

id有相同，有不同的

id 如果相同，可以认为是一组，从上至下顺序执行 在所有组中，id值越大，优先级越高，越先执行

select_type

根据此字段可以看到当前查询类型，select_type常用值：

  • SIMPLE简单的select查询，查询中不包含子查询或者
  • UNION PRIMARY查询中若包含任何复杂的子部分，最外层查询标记
  • SUBQUERY在SELECT或WHERE列表中包含了子查询
  • DERIVED在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表
  • UNION若第二个SELECT出现在UNION之后，则被标记为UNION,若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
  • UNION RESULT：从UNION表获取结果的SELECT

table

显示这一行数据是关于哪一个表查询。

derived 临时表

type

  • 表示访问类型，访问类型理论上如下表，排序越靠前性能越好：

    1
    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

  • 但我们工作中常接触到：ALL,index,rang,ref,eq_ref,const,system,NULL

    1
    2
    显示查询使用类型从最好到最差依次：
    system>const>eq_ref>ref>range>index>ALL

  • 一般来说，你的SQL语句至少得保证达到range级别，最好达到ref。

  • 各类型解释：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    system :表只有一行（等于系统表）这是const类型的特列，平时不会出现，这个也可以忽略不计

    const: 表示通过索引一次就找到了，const用于比较primary key或unique索引，因为只匹配一行数据，所以很快如将主键放置于where列表中，MySQL就能将该查询转换为一个常量。

    eq_ref : 唯一性索引扫描，对于每个索引建，表中只有一条记录与之匹配，常见于主键或唯一索引的扫描

    ref : 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问。它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。(工作中能达到这个就很牛逼了)

    range : 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就在你的where语句中出现between,<,>,in等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
        
    index： Full Index Scan,index与ALL区别为index类型只遍历索引树，这通常比ALL快，因为索引文件通常比数据文件小，（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读取）

    all : Full Table Scan,将遍历全表以找到匹配的行。

possible_keys 和 key

用于判断是否使用索引，是否索引失效，你的sql是使用哪个索引。

1
2
3
4
possible_keys  显示可能应用在这张表中的索引，一个或多个
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key 实际使用的索引，如果为NULL 则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中,优先使用普通索引，然后使用主键索引

当然一般情况下都是出现理论索引，再看有没有实际用到索引，但个别情况下也会出现理论上没有索引，实际上使用覆盖索引：

key_len

表示索引中使用的字节数，可通过该列计算查询中使用索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得到，不是通过表内检索出的。

同样查询结果，精度越小，key_len越小，查询效果越好。

ref

显示索引的哪一列被使用了，如果可能的话，是一个常量，哪些列被用于查找索引列上的值。

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

如图，可以看到建立索引和没有建立索引所扫描行数区别。

Extra

额外信息，它的显示十分重要。

Using filesort （九死一生）

通过如下改进性能：

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MySQL中无法利用索引完成的排序操作成为“文件排序”。(出现这个就代表SQL不好)

Using temporay (十死无生)

新建了内部临时表，保存中间结果，MySQL在对查询结果排序使用临时表，常见于order by 和分组查询 group by，因创建临时表大大降低了性能。

优化改进：

Using index

表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现using where,表明索引被用来执行索引键值查找。如果没有同时出现using where ，表示索引用来读取数据而非执行查找动作。

Using where
  • 使用了where过滤
Using join buffer
  • 使用了连接缓存
impossible where

  • where子句值总是false,不能用来获取任何元组

  • select查询错乱了。

    1
    2
    -- 当你执行name即等于July又等于z3导致SQL混乱：
    explain select * from staffs where name='July' and name='z3';
select tables optimized away
  • 在没有GROUPBY 子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
dictinct
  • 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

Mysql系列文章

  1. mysql系列I-概述

  2. mysql系列II-sql语法

    ①. mysql系列II-sql语法-(1)-概述

    ②. mysql系列II-sql语法-(2)-DDL

    ③. mysql系列II-sql语法-(3)-DML

    ④. mysql系列II-sql语法-(4)-DQL

    ⑤. mysql系列II-sql语法-(5)-DCL

  3. mysql系列III-函数

  4. mysql系列IV-约束

  5. mysql系列V-多表查询

  6. mysql系列VI-事务

  7. mysql系列VII-存储引擎

  8. mysql系列VIII-索引

    ①. mysql系列VIII-索引(1)-概述

    ②. mysql系列VIII-索引(2)-结构

    ③. mysql系列VIII-索引(3)-分类

    ④. mysql系列VIII-索引(4)-语法

    ⑤. mysql系列VIII-索引(5)-SQL性能分析

    ⑥. mysql系列VIII-索引(6)-索引使用

    ⑦. mysql系列VIII-索引(7)-设计原则

  9. mysql系列IX-SQL优化

  10. mysql系列X-视图

  11. mysql系列XI-存储过程

  12. mysql系列XII-存储函数

  13. mysql系列XIII-触发器

  14. mysql系列XIV-锁