高并发下Mysql慢查询和执行计划分析

🕗 发布于 2025-01-20 08:28 mysql android 数据库

数据库环境准备(SQL脚本看附录）

#创建目录
mkdir -p /home/data/mysql/
#创建配置文件
touch /home/data/mysql/my.cnf

#部署
docker run \
    -p 3306:3306 \
    -e MYSQL_ROOT_PASSWORD=1234567890 \
    -v /home/data/mysql/conf:/etc/mysql/conf.d \
    -v /home/data/mysql/data:/var/lib/mysql:rw \
    -v /home/data/mysql/my.cnf:/etc/mysql/my.cnf \
    --name demo_mysql \
    --restart=always \
    -d mysql:8.0

Mysql慢查询日志介绍和配置实战

什么是MySQL慢查询日志

MySQL数据库自带的一个功能，用于记录执行时间超过指定阈值的SQL语句，以便于后续的性能优化工作
帮助开发和DBA发现哪些SQL语句需要优化，在哪些地方需要修改，以提高数据库的性能
默认情况下MySQL数据库是不开启慢查询日志的，long_query_time的默认值为10（即10秒，通常设置为1秒）
慢查询日志记录 SQL 语句相关信息
- 执行时间、执行次数、告警时间、执行的 SQL 语句、使用的索引、扫描的行数、等待锁的时间

# Time: 2023-05-29T07:16:22.801248Z
# User@Host: root[root] @  [119.130.128.200]  Id:   706
# Query_time: 2.000534  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1685344580;
select * from product

Time: 表示日志记录的时间。格式为 ISO8601 标准的 UTC 时间。
User@Host: 表示执行该SQL语句的用户和主机名。
Id: 表示线程 ID，是一个整数表示。
Query_time: 表示 SQL 语句的执行时间，以秒为单位。
Lock_time: 表示在执行 SQL 语句期间等待锁定资源的时间。如果 SQL 语句没有使用锁，则为 0。
Rows_sent: 表示 SQL 语句返回的行数。
Rows_examined: 表示查询过程中扫描的行数。如果 SQL 语句没有执行查询操作，则该值为 0。
timestamp: 表示该 SQL 语句的执行时间戳，以秒为单位。
sql语句: 实际执行的SQL语句。

通过分析慢查询日志，知道哪些 SQL 语句需要进行优化并进行相应的优化，以提高数据库的性能和效率

Mysql慢查询日志

查询状态

`show variables like 'slow%'`

在这里插入图片描述

slow_query_log属性是OFF，处于关闭状态，也可以通过 0和1进行配置，1表示开启，0表示关闭
- 开启慢查询日志 set global slow_query_log = on
- 慢查询配置临时生效，重启数据库则会丢失
- 配置持久化 vim /home/data/mysql/my.cnf

[mysqld]
slow_query_log = 1
long_query_time = 0.1
slow_query_log_file =/usr/local/mysql/mysql_slow.log

slow_query_log_file表示慢查询日志文件的存放路径
- set global slow_query_log_file = '自定义路径'
如何定义慢sql语句 show variables like '%long%'
- long_query_time属性，值为10.000000，表示的只记录查询时间在10s以上的语句
- 数据不多的情况，慢查询的临界值设置为0.02(方便测试)：set long_query_time=0.02

语句分析利器（Explain执行计划）

什么是Mysql的执行计划

EXPLAIN 是用于分析并优化查询语句性能的工具，执行计划会解析查询语句并生成执行计划
包括访问表和索引所需的策略、查询优化器的选择以及每个阶段的估计记录数等信息，深入SQL语句在执行过程中的各个细节

字段说明

id
- 对于 SELECT 语句，每个查询都会被分配一个唯一的ID
- 表示查询的标识符，数字越大越先执行
select_type
- 表示查询类型或者子查询类型，使用不同的 select_type 来帮助评估查询性能，并确定可以采取哪些优化方法
- 需要根据具体情况来进行相应的优化，例如尽量减少子查询的数量，避免使用不必要的 UNION 操作等等

类型	概述
SIMPLE	表示简单的 SELECT 查询，不包含子查询或 UNION 操作。
PRIMARY	表示外层查询的第一个 SELECT
UNION	表示 UNION 操作的第二个或后续的 SELECT 查询。
SUBQUERY	表示一个子查询，MySQL 会在子查询中先执行查询，比如where里面包括了子查询
DEPENDENT SUBQUERY	也表示一个子查询，但是外部 SQL 查询的结果会影响子查询的执行
DERIVED	表示派生表，MySQL 会在查询中创建一个新的临时表，这个临时表来自于 FROM 子句中的子查询
UNION RESULT	表示 UNION 操作的结果，MySQL 在创建结果集时使用临时表来存储数据

table
- 表示查询涉及到哪些表，对于子查询等复杂查询可能涉及多张表
partitions
- 表示查询操作涉及到的分区表的分区情况
type
- 表示 MySQL 在表中找到所需行的方式，常见的类型包括 ALL, index，range, ref, eq_ref, const, system, NULL
possible_key
- 表示 MySQL 可以使用哪些索引来优化查询
key
- 表示 MySQL 实际使用的索引，如果没有使用任何索引，则该值为 NULL
key_len
- 表示 MySQL 实际使用的索引的长度，该值与索引定义的长度有关
ref
- 表示 MySQL 使用哪个列或常量与索引列进行比较
rows
- 表示 MySQL 估计要扫描多少行才能找到所需记录，是一个估算值而不是确切值
filtered
- 查询条件过滤的效率，百分比形式表示， Filtered 越高，表示查询结果集中过滤数据所需要的开销越小，查询性能就越好
Extra
- 该字段包括一些额外的查询信息，包括使用何种排序方式、使用哪种 Join 操作等

执行计划关键字段

type
- 表示 MySQL 在表中找到所需行的方式，常见的类型包括 ALL, index，range, ref, eq_ref, const, system, NULL
- 下面表格是性能由 低 到 高，all是最差的（常规高频的sql推荐需要达到 range 级别以上，最好是ref以上）

Type	概述
all	全表扫描，MYSQL扫描全表来找到匹配的行
index	索引全扫描，MYSQL遍历整个索引来查找匹配的行；Extra 字段里面出现 Using index，则是覆盖索引，不用二次回表查询
range	索引范围扫描，常见于<、<=、>、>=、between、in等操作符；相对于index的全索引扫描，它有范围限制，因此要优于index
ref	使用非唯一性索引或者唯一索引的前缀扫描，返回匹配某个单独值的记录行；虽使用了索引但该索引列的值并不唯一,进行目标值附近的小范围扫描，不扫描全表
eq_ref	eq_ref 与 ref对比结果集只有一个，使用主键或者唯一索引进行查找,不用扫描更多行
const	最多只有一条匹配行，查询非常迅速，用到primary key 或者unique key，性能最高
system	表只有一行，基本不会出现，忽略
null	不访问数据库表，直接返回索引

possible_key
- 表示 MySQL 可以使用哪些索引来优化查询，一个或者多个
key
- 表示 MySQL 实际使用的索引，如果没有使用任何索引，则该值为 NULL
key_len
- 表示 MySQL 实际使用的索引的长度，该值与索引定义的长度有关
- 索引长度越短越好
Extra

类型	概述
Using index	选择使用了覆盖索引的特性，通过索引直接获取查询结果，而无需回表查询，提高了查询效率。
Using filesort	需要额外进行一个文件排序操作来实现 ORDER BY 操作，可能会严重影响查询性能。
Using temporary	在执行查询时需要借助临时表来保存中间结果集，这常发生在排序、分组、子查询和 UNION 查询之中。
Using where	条件查询，在查询过程中需要进行表级别的条件过滤，即使共享了某些索引，也需要进行全表扫描查找符合条件的行。不是仅仅通过索引就可以获取所有需要的数据,则会出现 Using where
Range checked for each record	通过索引比较操作来过滤部分行，直到找到符合条件的行，这种操作常出现在使用 INDEX 和 ORDER BY 操作时。
Using join buffer (Block Nested Loop)	在执行连接操作时需要额外申请 join buffer 来存储中间结果，这种操作常发生在连接操作中。
Using index condition	利用了查找索引数据的过程中额外发现的过滤条件进行了优化，无需回表查询或查表，可以直接通过索引结果来返回查询的结果
Using sort_union()`和` Using union()	通过 UNION ALL 或 UNION DISTINCT 操作来合并查询结果集，使用了一些优化策略来提高查询效率。

Mysql执行计划里面的字段 key_len（字节数）

表示 MySQL 实际使用的索引的长度，该值与索引定义的长度有关，索引长度越短越好，非精确统计
如何计算索引长度，联合索引是多个字段列的bytes相加
- 比如varchar(N)里的N是字符数，如果是utf8字符集时，1个字符占用3个字节
  - Mysql 5.0版本以上 varchar是存储字符，之前是字节数
- 后面加的2Bytes是用来表示字段长度的，可算出该索引占用多少bytes
- 字符串（utf8编码）
  - char(n): n字节长度
  - varchar(n) 长度是3n+2 ，需要2字节存储字符串长度
- 数值
  - tinyint 1字节
  - int 4字节
  - bigint 8字节
- 时间
  - date 3字节
  - timestamp 4字节
  - datetime 8字节
- 如果字段可以是null，需要1个字节记录是否可以为null，如果允许为null，则需要 +1 个字节存储
- 扩展
  - 不同的字符集一个字符占用不同的bytes，比如UTF-8字符集，一个字符占3个bytes
  - 在utf8字符集下
    - Myisam限制长度为1000bytes，对Myisam存储引擎创建索引的单列长度不能超过333（1000/3）个字符
    - InnoDB限制长度为767bytes，对InnoDB存储引擎创建索引的单列长度不能超过255（767/3）个字符
  - utf8mb4字符集的一个字符占用4个bytes
比如下图查询

附录（初始化数据）

创建数据库表

CREATE DATABASE `shop` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

CREATE TABLE `product` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL,
  `cover_img` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '封面图',
  `amount` decimal(10,2) DEFAULT NULL COMMENT '现价',
  `summary` varchar(2048) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '概要',
  `detail` longtext COLLATE utf8mb4_bin COMMENT '详情',
  `gmt_modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `gmt_create` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_title` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=201000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;

初始化数据(这里只有几条demo插入，本次测试product表数据为10W)

INSERT INTO `shop`.`product` (`id`, `title`, `cover_img`, `amount`, `summary`, `detail`, `gmt_modified`, `gmt_create`) VALUES (101000, '华为手机', 'https://img0.baidu.com/it/u=2191392668,814349101&fm=253&fmt=auto&app=138&f=JPEG?w=800&h=1399', 0.00, '这个是摘要0', '这个是详情0', '2025-01-19 11:28:02', '2025-01-19 11:28:02');
INSERT INTO `shop`.`product` (`id`, `title`, `cover_img`, `amount`, `summary`, `detail`, `gmt_modified`, `gmt_create`) VALUES (101001, '小米手机', 'https://img0.baidu.com/it/u=2191392668,814349101&fm=253&fmt=auto&app=138&f=JPEG?w=800&h=1399', 1.00, '这个是摘要1', '这个是详情1', '2025-01-19 11:28:02', '2025-01-19 11:28:02');
INSERT INTO `shop`.`product` (`id`, `title`, `cover_img`, `amount`, `summary`, `detail`, `gmt_modified`, `gmt_create`) VALUES (101002, '苹果手机', 'https://img0.baidu.com/it/u=2191392668,814349101&fm=253&fmt=auto&app=138&f=JPEG?w=800&h=1399', 2.00, '这个是摘要2', '这个是详情2', '2025-01-19 11:28:02', '2025-01-19 11:28:02');
INSERT INTO `shop`.`product` (`id`, `title`, `cover_img`, `amount`, `summary`, `detail`, `gmt_modified`, `gmt_create`) VALUES (101003, 'OPPO手机', 'https://img0.baidu.com/it/u=2191392668,814349101&fm=253&fmt=auto&app=138&f=JPEG?w=800&h=1399', 3.00, '这个是摘要3', '这个是详情3', '2025-01-19 11:28:02', '2025-01-19 11:28:02');
INSERT INTO `shop`.`product` (`id`, `title`, `cover_img`, `amount`, `summary`, `detail`, `gmt_modified`, `gmt_create`) VALUES (101004, 'vivo手机', 'https://img0.baidu.com/it/u=2191392668,814349101&fm=253&fmt=auto&app=138&f=JPEG?w=800&h=1399', 4.00, '这个是摘要4', '这个是详情4', '2025-01-19 11:28:02', '2025-01-19 11:28:02');
INSERT INTO `shop`.`product` (`id`, `title`, `cover_img`, `amount`, `summary`, `detail`, `gmt_modified`, `gmt_create`) VALUES (101005, 'springboot全家桶', 'https://img0.baidu.com/it/u=2191392668,814349101&fm=253&fmt=auto&app=138&f=JPEG?w=800&h=1399', 5.00, '这个是摘要5', '这个是详情5', '2025-01-19 11:28:02', '2025-01-19 11:28:02');
INSERT INTO `shop`.`product` (`id`, `title`, `cover_img`, `amount`, `summary`, `detail`, `gmt_modified`, `gmt_create`) VALUES (101006, '华为手机', 'https://img0.baidu.com/it/u=2191392668,814349101&fm=253&fmt=auto&app=138&f=JPEG?w=800&h=1399', 6.00, '这个是摘要0', '这个是详情0', '2025-01-19 11:28:02', '2025-01-19 11:28:02');
INSERT INTO `shop`.`product` (`id`, `title`, `cover_img`, `amount`, `summary`, `detail`, `gmt_modified`, `gmt_create`) VALUES (101007, '小米手机', 'https://img0.baidu.com/it/u=2191392668,814349101&fm=253&fmt=auto&app=138&f=JPEG?w=800&h=1399', 7.00, '这个是摘要1', '这个是详情1', '2025-01-19 11:28:02', '2025-01-19 11:28:02');

原文地址：https://blog.csdn.net/laow1314/article/details/145246062

免责声明：本站文章内容转载自网络资源，如侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：Spring篇解决因为Bean的循环依赖——理论篇
下一篇：SpringBoot+Vue3快速开发平台、自研工作流引擎

3.5 OpenAI Moderation：确保生成内容符合道德与安全标准
OpenAIModeration是一个自动化的内容审查系统，它利用机器学习模型来识别和标记可能含有不当内容的文本。这个工具的目标是帮助开发者、平台和服务确保其自动生成内容符合伦理标准，避免发布含有恶意
阅读更多2025-01-21
在 QNAP NAS中使用 Container Station 运行 Docker 的完整指南
qnap安装docker
阅读更多2025-01-21
麒麟系统中删除权限不够的文件方法
这将强制删除文件，不会将其移动到回收站，因此请谨慎使用。这将提示你输入管理员密码，输入正确后即可删除文件。希望这些方法能帮助你成功删除权限不够的文件。如果文件被系统占用或无法正常删除，可以使用。命令以
阅读更多2025-01-21
【达梦数据库】两地三中心环境总结
【代码】【达梦数据库】两地三中心环境总结。
阅读更多2025-01-21
每日一题-二分查找
二分查找
阅读更多2025-01-21
【cursor重构谷粒商城】03——谷粒商城技术架构选型存在哪些不足？
介绍谷粒商城项目的技术栈、架构、以及电商模式、微服务等基础概念。
阅读更多2025-01-21
如何用3个月零基础入门网络安全？_网络安全零基础怎么学习
我们知道计算机最早是在西方发明出来的，很多名词或者代码都是英文的，甚至现有的一些教程最初也是英文原版翻译过来的，而且一个漏洞被发现到翻译成中文一般需要一个星期的时间，在这个时间差上漏洞可能都修补了。”
阅读更多2025-01-21
SpringBoot注入配置文件application.properties中的信息
解决：自定义application.properties配置文件中的信息，注意严格遵循a=b的形式，不要加引号；在使用的时候通过Value注解注入。问题：将可能会变动的配置信息硬编码在代码中，在修改时
阅读更多2025-01-21
MySQL创建和操纵表
用逗号分隔；有的DBMS还要求指定表的位置。
阅读更多2025-01-21
数据结构与算法面试专题——引入及归并排序
数据结构与算法面试专题——引入及归并排序
阅读更多2025-01-21

高并发下Mysql慢查询和执行计划分析

目录

数据库环境准备(SQL脚本看附录）

Mysql慢查询日志介绍和配置实战

什么是MySQL慢查询日志

Mysql慢查询日志

语句分析利器（Explain执行计划）

什么是Mysql的执行计划

字段说明

执行计划关键字段

Mysql执行计划里面的字段 key_len（字节数）

附录（初始化数据）

创建数据库表

初始化数据(这里只有几条demo插入，本次测试product表数据为10W)

相关文章