mysql慢sql相关
#慢查询日志配置
my.cnf文件
[mysqld]
slow_query_log=1
slow_query_log_file =/var/log/mysql/mysql-slow.log
long_query_time=0.11
log_queries_not_using_indexes=1
查看慢查询相关参数
show variables like 'slow_query%';
slow_query_log 慢查询开启状态
slow_query_log_file 慢查询⽇志存放的位置
long_query_time 查询超过多少秒才记录，默认为10秒
解释：这里设置long_query_time为2秒，意味着所有执行时间超过2秒的查询都会被记录到慢查询日志中。
log_queries_not_using_indexes设置为1表示即使查询执行时间没有超过long_query_time值，
但没有使用索引的查询也会被记录
使用命令临时修改MySQL慢查询
一、先查看开启状态
show variables like 'slow_query%';
二、将 slow_query_log 全局变量设置为“ON”/OFF状态
set global slow_query_log='ON';
三、修改完成后，查看开启状态
show variables like 'slow_query%';
四、设置慢查询⽇志存放的位置
set global slow_query_log_file='/opt/slow.log/';
修改慢查询时长配置
一、查询时长
show variables like 'long_query_time';
二、修改时长，查询超过1秒就记录
set global long_query_time=1;
注意：直接修改global 的long_query_time 之后在当前的的窗口中是没有效果的，
修改之后，在新打开的窗口中才会有效果或者先关闭数据库连接，
再重新连接，再次查询就可以看到实际上是修改了的。
如果想让本窗口也有效果 的话，不用加 global关键字。
安装pt-query-digest工具（percona-toolkit-3.5.0）
pt-query-digest 仓库地址：https://github.com/percona/percona-toolkit
在线获取二进制包
wget https://downloads.percona.com/downloads/percona-toolkit/3.5.0/binary/tarball/percona-toolkit-3.5.0_x86_64.tar.gz
解压
tar -zxvf ./percona-toolkit-3.5.0_x86_64.tar.gz
安装前置工作
perl模块依赖
yum install perl-ExtUtils-MakeMaker
yum install perl-DBD-MySQL
yum install perl-Digest-MD5
安装perl模块
perl Makefile.PL PREFIX=/usr/local/percona-toolkit
编译安装
make && make install
出现：安装成功
Installing ......
Installing ......
Installing ......
Appending installation info to /usr/local/percona-toolkit/lib64/perl5/perllocal.pod`
工具路径
/usr/local/percona-toolkit
写入系统命令
export PATH=$PATH:/usr/local/percona-toolkit/bin/
使用：
pt-query-digest mysql-slow.log
可将结果分为三部分：
第一部分：总体概要信息
该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小
# 270ms user time, 20ms system time, 23.05M rss, 187.48M vsz
分析结果生成的时间
# Current date: Tue Aug 20 18:04:10 2024
主机名
# Hostname: node1
分析的文件所在路径
# Files: mysql-slow.log
分析的 SQL 数量统计，本示例中总共存在的慢 SQL 有 546 条
total 为总语句数量。
unique 为唯一查询数量，即对查询条件进行参数化以后，统计的总共有多少个不同的查询
# Overall: 546 total, 34 unique, 0.00 QPS, 0.00x concurrency _____________
本次分析中所以慢 SQL 所在的时间段。
# Time range: 2024-08-14T08:12:40 to 2024-08-19T09:10:35
SQL 执行时间 总时间 最短时间 最长时间 平均时间 95%个数 标准偏差 中位数
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
语句执行时间
# Exec time 584ms 189us 16ms 1ms 1ms 1ms 925us
锁占用时间
# Lock time 2ms 0 53us 3us 6us 3us 3us
发送到客户端的行数
# Rows sent 8.67k 0 107 16.26 49.17 21.32 13.83
select语句扫描行数
# Rows examine 95.65k 0 3.33k 179.38 271.23 152.23 192.76
查询的字符数
# Query size 89.66k 17 570 168.15 329.68 84.23 143.84
参数说明如下：
Current date：分析结果生成的时间。
Hostname：主机名。
Files：分析的文件所在路径。
Overall：分析的 SQL 数量统计。
total 为总语句数量。
unique 为唯一查询数量，即对查询条件进行参数化以后，统计的总共有多少个不同的查询。
Time range：本次分析中所以慢 SQL 所在的时间段。
Exec time：SQL 执行时间。
total 总时间。
min 最短时间。
max 最长时间。
avg 平均时间。
95% 把所有耗时从小到大排列，位置最接近 95% 的那个数。
stddev 标准偏差。
median 中位数。
Lock time：锁占用的时间。
Row sent：发送到客户端的行数。
Row examine：SQL 语句扫描行数。
Bytes sent：发送到客户端的字节数。
Query size：查询的字符数。
第二部分：SQL 概要信息
# Profile
# Rank Query ID Response time Calls R/Call V/M
# ==== =================================== ============= ===== ====== ====
# 1 0x733019F33E3027F6693EB59382A27420 0.1041 17.8% 96 0.0011 0.00 SELECT INFORMATION_SCHEMA.PROFILING
# 2 0x9277183CCDDC5C9148701A8733C04373 0.0960 16.4% 96 0.0010 0.00 SELECT INFORMATION_SCHEMA.PROFILING
# 3 0xDEAAB9776B714C6CD80A051908127F82 0.0447 7.7% 27 0.0017 0.00 SELECT departments employees
# 4 0x3C2A84A184121E5898947CCF9160CAC9 0.0376 6.4% 6 0.0063 0.00 SELECT UNION information_schema.TABLES information_schema.COLUMNS information_schema.ROUTINES
# 5 0x85F052B3FEDEA672383CF734A6604AD4 0.0335 5.7% 27 0.0012 0.00 SELECT employees departments employees
# 6 0x775CCB275E806BD949B0FC33273C2545 0.0312 5.3% 26 0.0012 0.00 SELECT jobs employees
# 7 0x0D9BFC704518E46F131FAE6F3438C2FF 0.0297 5.1% 26 0.0011 0.00 SELECT employees
# 8 0x08FEC4FBC78D61B50EE315644E7EDF39 0.0265 4.5% 25 0.0011 0.00 SELECT employees
# 9 0x69F34EA58046CEA8D64D5673DE139023 0.0221 3.8% 26 0.0009 0.00 SELECT employees
# 10 0x3CF826411964B95A8ADDBAB46FE3C376 0.0217 3.7% 26 0.0008 0.00 SELECT employees
# 11 0x88FE408114B4AD1A20C6A15F1F4F8AC2 0.0196 3.4% 7 0.0028 0.00 SELECT information_schema.ROUTINES information_schema.PARAMETERS
# 12 0x98F29FD7449C46BD16775637401795F2 0.0187 3.2% 27 0.0007 0.00 SELECT employees
# 13 0x1E93CADB3E4EE37609B06B87E46E4890 0.0179 3.1% 26 0.0007 0.00 SELECT employees
# 14 0xCC580A58CB4307CD61D15F3D4165A1BB 0.0174 3.0% 16 0.0011 0.00 SELECT information_schema.SCHEMATA
# 15 0x94BAAC5E562A2C613E4E760D50C61E20 0.0171 2.9% 26 0.0007 0.00 SELECT employees
# 16 0x59EA8ACD36FCC7D4173C3C599372AC38 0.0164 2.8% 26 0.0006 0.00 SELECT employees
# 17 0x72B67E723ED57F385717E1E64FB70C95 0.0067 1.2% 1 0.0067 0.00 SELECT employees
# MISC 0xMISC 0.0232 4.0% 36 0.0006 0.0 <17 ITEMS>
参数说明如下：
Rank：SQL 在此次统计中的耗时排名。
Query ID：pt-query-digest 为此类 SQL 生成的唯一 ID，可以通过此 ID 找到本 SQL 对应的详细信息。
Response time：此类 SQL 总耗时时间。全部 Query 加起来就是所有慢 SQL 的总耗时时间。百分比数值是该类 SQL 耗时占此次统计慢 SQL 总耗时时间的百分比。全部 Query 加起来就是 100%。
calls：执行次数，即本次分析总共有多少条这种类型的 SQL 语句。
R/Call：平均每次执行的响应时间。
V/M：响应时间的方差与平均比。
最后：大致的 SQL 语句，体现 SQL 的类型。
第三部分：SQL 详细信息
# Query 2: 0.00 QPS, 0.00x concurrency, ID 0x9277183CCDDC5C9148701A8733C04373 at byte 77137
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-08-16T06:02:22 to 2024-08-19T09:10:29
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 17 96
# Exec time 16 96ms 511us 2ms 999us 1ms 177us 972us
# Lock time 2 52us 0 2us 0 1us 0 1us
# Rows sent 14 1.28k 4 15 13.65 14.52 3.20 14.52
# Rows examine 21 20.90k 56 325 222.96 284.79 57.68 234.30
# Query size 10 9.19k 98 98 98 98 0 98
# String:
# Databases atguigudb (75/78%), test1 (21/21%)
# Hosts 119.131.136.81 (65/67%), 113.111.4.57 (31/32%)
# Users root
# Query_time distribution
# 1us
# 10us
# 100us #########################################
# 1ms ################################################################
# 10ms
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `INFORMATION_SCHEMA` LIKE 'PROFILING'\G
# SHOW CREATE TABLE `INFORMATION_SCHEMA`.`PROFILING`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID\G
Query 2 是此次分析结果中出现的慢 SQL
列表参数可以参考第一部分，其他参数说明如下：
Databases：SQL 执行的数据库。
Users：SQL 执行的用户。
Query_time distribution：查询时长的分布。
如下是 SQL 语句的相关执行计划，但是对于非 select 语句通常不能再次执行来查看执行计划，
因此 pt-query-digest 会尝试将 SQL 转化为等效的 select 语句然后展示出来。
其它常用命令
mysql执行顺序参考
用Explain分析具体的sql语句
EXPLAIN SELECT * from xxx;
id: 选择标识符
select_type: 表示查询的类型
table: 输出结果集的表
partitions: 匹配的分区
type: 表示表的连接类型
possible_keys: 表示查询时，可能使⽤的索引
key: 表示实际使⽤的索引
key_len: 索引字段的长度
ref: 列与索引的比较
rows: 扫描出的行数(估算的行数)
filtered: 按表条件过滤的⾏百分比
Extra: 执行情况的描述和说明
Explain字段介绍
1、id
ID编号是select的序列号，SQL中有多少select语句就存在多少ID,相同编号执行顺序由上而下，不同编号，值越大优先级越高,如果ID为空，则最后执行
2、select_type
SIMPLE
简单查询，不包含子查询或者union
PRIMARY
复杂查询，是最外层的select；在查询中包含任何复杂的子部分，最外层select都会标记为 PRIMARY
SUBQUERY
在select后面或者where后面，是子查询中第一个select语句
DERIVED
派生(衍生)查询，在from 子句中的select语句，这个select语句会将结果集放到临时表中
UNION
在 union 中的第二个或之后的 select
DEPENDENT UNION
union 中的第二个或之后的select 语句，取决于外面的查询
DEPENDENT SUBQUERY
子查询中的第一个 select，取决于外面的查询
UNION RESULT
union的结果集
3、table
当前行访问的表，其中有具体的表名，别名或者临时表
<derivedN>
表示从派生表中查询，后面N表示id列编号，如下图：<derived3> 表示select_type是derived且id值是3
<unionN,M>
在union行中，选择id是N和M的行
<subqueryN>
同理，引用具体子查询行编号为N的结果
4、partitions
当前表是否使用分区，如果有用到分区，该字段就会显示具体使用的是什么分区
5、type
表示关联类型或访问类型，该值决定了mysql查找数据的大概范围
依次从最优到最差分别为:system >const > eq_ref > ref > fulltext > ref_or_null> index_merge > unique_subquery > index_subquery > range > index > ALL
其中是system、const、eq_ref、ref、range、index、ALL常用部分，这个字段在优化SQL时，经常使用
system
system是const的特例，表里只有一行记录(一般是系统表)时标记为system
const
查询条件是唯一索引或者主键,所以表最多有一个匹配行，读取1次，速度比较快，因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量
er_ref
主键或者唯一索引的索引所有部分被关联的时候，值是eq_ref，一般是常见于关联查询
ref
使用普通索引或者唯一索引的部分前缀（如：最左前缀匹配），可能会查询出多条记录
fulltext
全文索引
ref_or_null
这种连接类型类似于 ref，但 MySQL额外搜索NULL的数据,这种连接类型优化最常用于解析子查询
index_merge
此连接类型表示使用索引合并优化。在这种情况下，key列输出行中的列包含使用的索引列表,且key_len列包含使用的索引的最长键部分列表
unique_subquery
该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引
index_subquery
和unique_subquery类似，它取代了in子查询，但它适用于以下形式的子查询中的非唯一索引
range
范围扫描通常出现在<>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, IN() 等操作中。使用一个索引来检索给定范围的行。 如果查询范围结果集太大，那也会出现全表扫描的情况
index
扫描全索引，一般是扫描某个普通索引，只扫描索引树会有如下两种情况：
如果查询使用了覆盖索引，则只扫描索引树，在这样的情况下，Extra列将显示Using index
使用索引顺序来查找数据行来进行全表扫描。Extra列的结果则不会出现Uses index
当查询仅使用属于单个索引的列时，MySQL 可以使用此连接类型。
all
全表扫描，性能最差，扫描的是聚簇索引，遇到这样的一般通过索引来优化
6、possible_keys
当前查询能使用哪些索引，有时可能出现 possible_keys 有值，而 key列显示 NULL 的情况，mysql认为索引对此查询帮助不大，
选择了全表查询(索引的选择涉及到了成本计算)； 如果该列是 NULL，则没有相关的索引。
在这种情况下，可以通过检查 WHERE 子句看是否它引用某些列或适合索引的列来提高查询性能，若没，则可以创建一个合适的索引进行优化
7、key
当前SQL实际用到的索引,如果没有使用索引，则该列为NULL;若要强制使用索引或者忽略possible_keys列中的索引，
可以在查询中使用FORCE INDEX、USE INDEX 或者 IGNORE INDEX
8、key_len
代表了在索引里面使用的字节数，通过这个值可以算出具体使用了索引中哪些列
key列为NULL时，那key_len也NULL
字段允许为 NULL，需要1字节记录是否为 NULL
字符集字节长度utf8=3, gbk=2
key_len的计算规则
计算时，需要看当前字段是否可以为NULL,如果可以为空需要 + 1， 不可以则不用 + 1
char(n): n * 当前字符集字节长度 + 1(如果该字段可以为空则需要，不为空则不用加)
varchar(n)：n * 当前字符集字节长度 + 1(如果该字段可以为空则需要，不为空则不用加) + 2(2字节用来存储字符串变长)
tinyint:1字节
smallint:2字节
int:4字节
bigint:8字节
date:3字节
timestamp:4字节
datetime:8字节
举例：
explain select * from user_role where user_id=1
idx_user_role_id是一个联合索引（userID和roleID）,key_len值为4，
表示只用了联合索引中的userID这个前缀索引，如果整个联合索引都有用到，则key_len值为8
9、ref
表示将哪个字段或常量和key列所使用的字段进行比较，值有可能是常量或者某个字段。
如果ref是一个函数，则使用的值是函数的结果。要查看是哪个函数，则可以在EXPLAIN语句之后紧跟一个SHOW WARNING语句。
10、rows
mysql估计要读取并检测的行数，对于InnoDB，此数字是一个估计值，可能并不准确
11、filtered
该值是一个百分比， rows * filtered 可以估算与下表关联的行数，
如果 rows是 1000 并且 filtered是 50.00（50%），则与下表连接的行数为 1000 × 50% = 500
执行计划中filtered的计算方式和影响（https://www.jianshu.com/p/e70de1f7cdcf）
12、extra
Using index
使用了覆盖索引；查询的列在当前查询的索引树中全部包含，而不需要进行其他查找
Using where
不查询表的所有数据或者不是仅通过索引就可以获取所有需要的数据，这样的情况就会是Using where
Using index condition
先按条件过滤索引后找到所有符合索引条件的数据，然后用WHERE子句中的其他条件去过滤这些数据行
Using temporary
MYSQL需要创建一个临时表来存储结果，用于后续的处理，一般group by,union会出现这样的情况，这种情况一般是要进行优化的，可以使用索引来优化
Using filesort
在查询语句中包含 order by操作且无法使用索引完成排序操作时，当查询结果数据较少时会进行内存排序，反之会进行磁盘排序，可以使用索引来优化
一、慢查询可能原因：
SQL 没加索引
索引不生效
limit 深分页问题
单表数据量太大
join 或者子查询过多
in 元素过多
数据库在刷脏页
order by 文件排序
拿不到锁
delete + in 子查询不走索引
group by 使用临时表
系统硬件或者网络资源
二、要避免MySQL中的慢查询考虑措施：
使用索引： 确保数据库表上的列有适当的索引。索引可以帮助MySQL更快地定位和检索数据，从而提高查询性能。
优化查询： 编写高效的查询语句，避免不必要的联接和子查询，尽量减少数据检索的数量。可以使用EXPLAIN语句来分析查询执行计划，并找出潜在的性能问题。
适当使用缓存： 对于频繁执行但不经常变化的查询，可以考虑使用MySQL的查询缓存或应用程序级别的缓存来减少数据库负载。
优化服务器参数： 调整MySQL服务器的参数，以适应实际的工作负载和硬件资源。例如，调整缓冲区大小、连接数限制等参数。
分析慢查询日志： 启用MySQL的慢查询日志，并定期分析其中的内容，以识别和优化慢查询。
定期优化表： 对表进行定期的优化和碎片整理，以确保数据库表的性能保持在一个良好的水平。
升级硬件： 如果可能的话，升级数据库服务器的硬件配置，例如增加内存、更快的磁盘或者使用更强大的CPU，以提高整体性能。
使用合适的存储引擎： 根据应用的需求和特性，选择合适的存储引擎。例如，InnoDB通常适用于事务处理，MyISAM适用于读密集型的应用。
其他
