MySQL查询性能分析

糖拌西红柿多放醋

于 2024-08-29 12:53:22 发布

阅读量503

点赞数 11

分类专栏： Linux及运维文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/qq_40690073/article/details/141297417

版权

Linux及运维专栏收录该内容

4 篇文章 0 订阅

订阅专栏

这里以Mysql为例，针对数据库SQL语句性能进行问题定位和分析

数据库状态排查

当数据库反应较慢时，可以查看Mysql正在运行的进行的线程可以通过以下两个命令（语句）查看:

show processlist

select * from information_schema.processlist where ……

可以通过查看processlist当前在执行的线程，确认存在问题的SQL或程序，show processlist为快捷查询，只看大概的情况，不便于分析；一般定位问题时，使用第二个SQL进行精准的筛选和分析

processlist

processlist表是INFORMATION_SCHEMA数据库的一部分，它提供了当前MySQL服务器上所有活动线程的快照。该表可以显示前100条进程信息，如果需要查看所有进程，可以使用SHOW FULL PROCESSLIST命令。

注：在MySQL 8.0及更高版本中，INFORMATION_SCHEMA中的PROCESSLIST表已被标记为弃用，并将在未来版本中被移除。推荐使用performance_schema实现的processlist 。performance_schema下的processlist视图提供了比INFORMATION_SCHEMA.PROCESSLIST更全面的信息，并且是非阻塞的，

语法：

SELECT * FROM information_schema.PROCESSLIST

输出：

   Id: 123
   User: root
   Host: localhost
     db: performance_schema
Command: Query
   Time: 0
  State: starting
   Info: show full processlist

processlist中返回的基本单位为线程，即：一条记录为一个线程，包含该线程的所属用户、客户端的IP端口、操作的数据库实例、线程的状态、时间等信息。

详细信息及内容：

字段	含义
id	就是这个线程的唯一标识，当我们发现这个线程有问题的时候，可以通过 kill 命令，加上这个Id值将这个线程杀掉。这个id是processlist表的主键
User	启动这个线程的用户
Host	记录了发送请求的客户端的 IP 和端口号
Db	数据库名称
Command	是指此刻该线程正在执行的命令。
Time	表示该线程处于当前状态的时间，默认单位为秒
State	线程的状态，和 Command 对应来看
Info	一般记录的是线程执行的语句。默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist

processlist性能排查思路

information_schema.PROCESSLIST反馈的信息中，用的最多的为Command和State、Time，根据这三者的结果综合分析进行问题定位；

Time过长

其中，慢查询的锁定多使用Time，Time表示该线程处于当前状态的时间，默认单位为秒，如果某一进程在Query命令时停留的Time过长，则该进程中的查询可能是慢查询。

例如：

--+
| Id | User  | Host       | db   | Command | Time | State | Info           |
+----+------+-----------+------+-------+---------+------+-------+-----------+
|  1 | dev   | 172.168.3.2| order| Query   |  300  | NULL  | SELECT ...    |
|  2 | user1 | 192.168.1  | NULL | Sleep   |  9000 |       |                |
+----+------+-----------+------+-------+---------+------+-------+-----------+

该例子中，线程1的一个Query停留时间竟然达到了300秒，则明显说明针对order数据库的某个程序在执行一个很复杂的SQL语句，相关SQL可以通过info来看，具体关于慢SQL的分析在后面会附上。

线程命令Command

线程比较常见的Command值：

命令	含义	特殊注意
Daemon	此线程在服务器内部，而不是服务客户端连接的线程。
Init DB	线程正在选择默认数据库。
Create DB	线程正在执行create-database操作。
Drop DB	线程正在执行drop-database操作。
Prepare	线程正在为语句生成执行计划。
Long Data	该线程在执行一个准备语句的结果中检索长数据。
Query	该线程正在执行一个语句。	正在执行查询，大多数性能问题出现在此处
Execute	线程正在执行一个准备好的语句。
Fetch	线程正在执行一个准备语句的结果。
Field List	线程正在检索表列的信息。
Delayed insert	线程是一个延迟插入处理程序。
Processlist	线程正在生成有关服务器线程的信息。
Refresh	线程是刷新表，日志或缓存，或重置状态变量或复制服务器信息。
Kill	线程正在杀死另一个线程。
Sleep	线程正在等待客户端向其发送新的语句。	最为常见的状态，即客户端与服务器保持连接，但未进行请求
Statistics	线程正在生成服务器状态信息。
Quit	线程正在终止。
Shutdown	线程正在关闭服务器。

线程状态State

主要指Command执行的状态，配合Command排查问题，这里列出出现问题的常见状态

状态值	含义
logging slow query	线程正在向慢查询日志写入语句。
Copying to tmp table	服务器正在复制磁盘到内存的临时表，是直接在磁盘创建的临时表而并非从内存转到磁盘的临时表。
Sending data	线程正在读取和处理SELECT语句的行，并将数据发送到客户端。由于在此状态期间发生的操作往往执行大量的磁盘访问（读取），所以在给定查询的整个生命周期内通常是最长的运行状态。
Waiting for commit lock	FLUSH TABLES WITH READ LOCK正在等待提交锁。
Waiting for global read lock	FLUSH TABLES WITH READ LOCK正在等待全局读锁定或read_only正在设置全局系统变量。
Writing to net	服务器正在将数据包写入网络，如果一个线程长时间在执行并且一直处于Writing to net状态，那么一直在发送数据包到网络，可以试着调整max_allowed_packet大小。另外，这可能会导致其他线程大量阻塞。
Sorting for group	线程正在做一个满足一个GROUP BY。
Sorting for order	线程正在做一个满足一个ORDER BY。
Sorting index	线程是排序索引页，以便在MyISAM表优化操作期间更有效地访问。
statistics	服务器正在计算统计信息以开发查询执行计划。如果一个线程长时间处于这种状态，服务器可能是磁盘绑定的，执行其他工作

慢查询语句分析

跟踪慢查询

开启MySQL慢查询记录一般有两种方式，通过Mysql配置开启和使用命令改动

使用MySQL开启

找到MySQL的配置文件（通常是my.cnf或my.ini），并添加或修改以下配置项：

[mysqld]
#打开慢查询记录
slow_query_log = 1
#慢查询记录日志路径
slow_query_log_file = /var/mysql/log/slowlog.log
#查询超过2秒即为慢查询
long_query_time = 2
#记录是否使用了索引
log_queries_not_using_indexes = 1

修改配置后，重启Mysql服务即可。

临时开启

临时开启则是使用SQL在正在运行中的MySQL服务实例上临时开启，随着实例生命周期结束或者服务重启则会关闭。具体操作如下：

查看慢查询是否打开

SHOW VARIABLES LIKE 'slow_query_log';

开启慢查询日志记录

若服务未开启，则可以手动开启慢查询记录

-- 打开慢查询记录日志
SET GLOBAL slow_query_log = 'ON';
-- 设置慢查询的定义，eg:执行超过2秒的SQL
SET GLOBAL long_query_time = 2;

指定慢查询日志位置

你可以通过设置slow_query_log_file变量来指定慢查询日志的存储文件。如果未指定，MySQL会自动创建一个文件，通常名为host_name-slow.log

-- 指定一个mysql服务有权限写入的目录来记录慢查询
SET GLOBAL slow_query_log_file = '/var/mysql/log/slowlog.log';
-- 同时设置记录中SQL是否使用索引
SET GLOBAL log_queries_not_using_indexes = 'ON';

注意：开启MySQL慢查询日志确实会对性能产生一定影响，因为MySQL需要额外记录那些执行时间超过指定阈值的查询。这种记录会增加CPU的使用率，并且由于需要写入磁盘，也可能会增加I/O负担。在实际应用中，建议在业务低峰期开启慢查询日志，或者在测试环境中先行测试其影响，以确保不会对生产环境造成不良影响。同时，应该根据实际业务情况调整long_query_time的值，以及考虑是否需要记录所有未使用索引的查询。

Explain解析语句

确定了查询慢的SQL后，则可以针对存在查询问题的SQL进行分析，在Mysql中使用explain关键字对语句进行性能分析：

EXPLAIN
SELECT
    t1.category_name AS one,
    t2.category_name AS two
FROM
    tb_category AS t1
    INNER JOIN tb_category AS t2 ON t1.category_id = t2.category_parent_id

结果解释

id	查询序号
select_type	查询类型
table	表名
partitions	匹配的分区
type	join类型
prossible_keys	可能会选择的索引
key	实际选择的索引
key_len	索引的长度
ref	与索引作比较的列
rows	要检索的行数(估算值)
filtered	查询条件过滤的行数的百分比
Extra	额外信息

这里主要关注select type和type

select_type

select_type的内容如下：

select_type	类型说明
SIMPLE	简单SELECT(不使用UNION或子查询)
PRIMARY	最外层的SELECT
UNION	UNION中第二个或之后的SELECT语句
DEPENDENT UNION	UNION中第二个或之后的SELECT语句取决于外面的查询
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个SELECT
DEPENDENT SUBQUERY	子查询中的第一个SELECT, 取决于外面的查询
DERIVED	衍生表(FROM子句中的子查询)
MATERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	结果集无法缓存的子查询，必须重新评估外部查询的每一行
UNCACHEABLE UNION	UNION中第二个或之后的SELECT，属于无法缓存的子查询

type

type的值包含：

除此之外，type还可能存在：

NULL：MySQL 不需要访问表或索引来找到所需的行，因为所需的行已经通过前面的表的访问方法被“推送”到当前的表中。
index_merge：MySQL 将使用索引合并优化来执行查询，这意味着它将使用多个索引来找到行。
fulltext：将使用全文本索引来查找匹配的行。
subquery：子查询将返回一个结果集，外部查询将为每个结果集的行执行操作。
unique_subquery：这个连接类型通常用于 IN 子查询，当子查询返回的是唯一值时，例如使用了 PRIMARY KEY 或 UNIQUE 索引。如果子查询满足唯一性，MySQL 会将其视为常数，从而提高查询效率。
index_subquery：与 unique_subquery 类似，但是用于非唯一索引。当子查询的结果集不是唯一的，MySQL 会使用索引来搜索这些可能的值。
derived：MySQL 将对一个派生表（即 FROM 子句中的子查询）执行操作。
materialized：MySQL 将将子查询的结果集物化到一个临时表中，然后对临时表执行操作。

从性能最好到最差的连接类型依次为：

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

在分析查询性能时，目标是尽可能使用 system、const、eq_ref 和 ref 这些更高效的访问类型，避免使用 ALL 和 index，因为它们通常涉及到全表或全索引扫描，性能较差。

一个案例

我有三个表，账号表、人员表、组织机构表，其中账号表中使用user_id关联人员表，人员表中org_id关联组织机构表，表结构如下：

CREATE TABLE accounts (
    account_id VARCHAR(255),
    user_id VARCHAR(255),
    account_name VARCHAR(255)
);

CREATE TABLE employees (
    employee_id VARCHAR(255),
    user_id VARCHAR(255),
    org_id VARCHAR(255),
    employee_name VARCHAR(255)
);

CREATE TABLE organizations (
    org_id VARCHAR(255),
    org_name VARCHAR(255),
    parent_id VARCHAR(255)
);

我现在想要查询所有二级部门下一共有哪些账号，SQL为：

SELECT
    o.org_name,
    GROUP_CONCAT(a.account_name SEPARATOR ', ') AS account_names
FROM
    organizations o
LEFT JOIN employees e ON o.org_id = e.org_id
LEFT JOIN accounts a ON e.user_id = a.user_id
GROUP BY
    o.org_name;

实际过程中发现这个SQL的查询效率极低，使用explain进行分析

explain
SELECT
    o.org_name,
    GROUP_CONCAT(a.account_name SEPARATOR ', ') AS account_names
FROM
    organizations o
LEFT JOIN employees e ON o.org_id = e.org_id
LEFT JOIN accounts a ON e.user_id = a.user_id
GROUP BY
    o.org_name;

输出为：

+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | o     | ALL  | NULL          | NULL | NULL    | NULL | 10000 |        |
|  1 | SIMPLE      | e     | ALL  | NULL          | NULL | NULL    | NULL | 50000 |        |
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL | 80000 |        |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+

type：对于所有表，访问类型都是 ALL，表示进行全表扫描。
possible_keys：所有表的这一列都是 NULL，表示没有可用的索引。
key：所有表的这一列都是 NULL，表示没有使用索引。
rows：这个数字是估计需要检查的行数，对于没有索引的查询，这个数字可能会非常大，导致查询速度很慢。

针对这个SQL来讲，数据库必须扫描整个表来找到匹配的行，这会随着数据量的增加而显著降低查询性能。如果 accounts 表有8万条数据，employees 表有5万条数据，organizations 表有1万条数据，那么每次连接操作都可能需要扫描这些表的几乎所有行，这会导致查询速度非常慢。

对此就需要考虑对三个表进行相应的索引设计了。