1. 首页
  2. 文章中心
  3. 笔记

Mysql调优大全梳理（涵盖90%需要调优的场景）前言1.Join语句优化2.Limit查询优化3.COUNT语句优化4.ORDER BY优化5.GROUP BY优化6.DISTINCT 优化Percona Toolkit

74 阅读 0 评论
我是靠谱客的博主 贪玩信封，最近开发中收集的这篇文章主要介绍Mysql调优大全梳理（涵盖90%需要调优的场景）前言1.Join语句优化2.Limit查询优化3.COUNT语句优化4.ORDER BY优化5.GROUP BY优化6.DISTINCT 优化Percona Toolkit，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

前言

学习这个文章需要具备一定的理论基础，不妨先来看一下我这篇文章《一文道尽数据库底层原理，探讨Mysql调优之道》

1.Join语句优化

Join语句相关算法

算法1：Nested-Loop Join（NLJ）

这种算法称为“嵌套循环Join”，大致是这样玩的：

TableJoin Type
t1range
t2ref
t3All
for each row in t1 matching range{
	for each row in t2 matching reference key {
		for each row in t3 {
			if row satisfies join conditions, send to client
		}
	}
}

如上，比如我们有三张表，大致流程是这样的，先查询出t1中符合条件数据，然后用一个for循环遍历，在循环里面查询出t2表中符合条件的数据，并使用reference key 去匹配，也就是join 时候的on字段，接着再查询出t3表中符合条件的数据，由于第三张表示全表扫描，所以就循环t3中所有数据做判断。

我们不难发现，这个算法是比较粗暴的，外层循环的结果集越多，内层循环的次数也就越多。

算法2：Block Nested-Loop Join（BNLJ）

翻译成中文，这种算法称为”块循环嵌套Join”，它的玩法是这样的：

我们还是拿上面算法1中的表为例，它的伪代码大致是这样的：

for each row in t1 matching range {
	for each row in t2 matching reference key {
		store used columns from t1, t2 in join buffer
		if buffer is full {
			for each row in t3 {
				for each t1, t2 combination in join buffer {
					if row satisfies join conditions, send to client
				}
			}
			empty join buffer
		}
	}
}
if buffer is not empty {
	for each row in t3 {
		for each t1, t2 combination in join buffer {
			if row satisfies join conditions, send to client
		}
	}
}

查询t1，t2和上面算法一样，但是在查询第三张表的时候就有区别了，它把t1以及t2表需要用到的字段，存到了一个叫 join buffer 的地方，这个join buffer叫做连接缓存，依次往join buffer里塞数据，当join buffer里满了的时候，再循环t3，然后用t3里的数据和join buffer里比较，如果匹配就返回给客户端。

我们比较一下这两种算法，我们看第一种，如果for each row in t2里有100个元素，那么就需要执行100次for each row in t3，那么如果使用BNLJ的话，mysql会把这100行数据缓存到join buffer，如果join buffer足够大，会把这100行数据都存放到缓存，那么只需要执行一次for each row in t3就可以了，大幅度减少了内存循环的表扫描次数。那么如果这100条数据不能一次存放到join，那么需要执行多少次for each row in t3 呢？ 这里有个计算公式：(S * C)/join_buffer_size + 1，其中S是缓存的t1或t2表的一行数据，C是缓存的行数，S*C就表示如果缓存所有数据需要多大空间，join_buffer_size是join buffer的大小，用这个公式就能得到需要扫描的次数了。

那么我们就可以知道，BNLJ主要是引入join buffer，从而减少内存扫描表的次数，进而提升性能的，但是使用join buffer是有条件的，只有符合某些条件才能使用join buffer：

  • 连接类型是ALL、index或range

  • 第一个nonconst table 不会分配join buffer，即使类型是ALL或者index

  • join buffer 只会缓存需要的字段，而非整行数据，即使用的是store used columns，而不是t1、t2所有字段。

  • 每个能被缓存的join 都会配分配一个join buffer，一个查询可能拥有多个join buffer

  • join buffer会在执行联接之前分配，在查询完成后释放。

你可以通过join_buffer_size变量设置join buffer大小：

show variables like 'join_buffer_size'; //查看join buffer大小
set join_buffer_size = 1024 * 1024 * 50; //设置当前session的join buffer为50M
set global join_buffer_size = 1024 * 1024 * 50; //设置全局的join buffer为50M（一般不建议设置太大）

如何知道sql里有没有使用join buffer呢？看下图：

算法3：Batched Key Access Join（BKA）

这种算法翻译成中文叫做“批量键值访问”

  • MySQL 5.6引入

  • BKA的基石：Multi Range Read（MRR）

算法4：HASH JOIN

  • MySQL 8.0.18引入，用来替代BNLJ

  • join buffer缓存外部循环的hash表，内层循环遍历时到hash表匹配

  • 官方文档原理解析：https://dev.mysql.com/blog-archive/hash-join-in-mysql-8/

使用注意：

  • MySQL版本必须>= 8.0.18，而且有很多限制，比如不能作用于外连接，比如left join/right join等等。从8.0.20开始，限制少了很多，建议用8.0.20或更高版本。

  • 从MySQL8.0.18开始，hash join的join buffer是递增分配的，这意味着，你可以为将join_buffer_size设置的比较大。而在MySQL8.0.18中，如果你使用了外连接，外连接没法用hash join，此时join_buffer_size会按照你设置的值直接分配内存。因此join_buffer_size还是得谨慎设置。

  • 从8.0.20开始，BNLJ已经被删除，用hash join代替了BNLJ

有关HASH JOIN强烈建议大家阅读这个文章：https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html 里面列出了hash jion在mysql各个版本之间的区别。

驱动表和被驱动表

在正式探讨join 优化之前先来了解这两个概念。

  • 外层循环的表是驱动表，内层循环的表示被驱动表

我们依然拿上面算法1里的表来看：t1是t2的驱动表，t2是t1的被驱动表，t2是t3的驱动表，t3是t2的被驱动表。

JOIN调优原则

了解上面几个概念后，我们就看一下JOIN调优原则：

调优原则1：用小表驱动大表

也就是说用数据量小的表作为驱动表，数据量大的表作为被驱动表。

如何看哪张表是驱动表哪张表示被驱动表呢？

我们还是需要用explain：

这个id表示，id越大越先执行，如果id相同，则从上往下依次执行，所以对上图sql会先操作book(b) 表再执行第二行，操作userbookscore©表，所以book表是驱动表，userbookscore表示被驱动表。

我们再看一下关联三张表的操作：

这里结果有三行，先操作b表，再操作c表，最后再操作d表，那么b表就是c表的驱动表，而c表又是d表的驱动表。

上述就是第一个原则，用小表驱动大表。但是：

一般来说无需人工考虑，mysql的关联查询优化器会自动选择最优的执行顺序

如果优化器抽风，可以使用STRAIGHT_JOIN

调优原则2：如果有where条件，应当要能够使用索引，并尽可能减少外层循环的数据量

因为外层结果越大，内存扫描的次数越多。

调优原则3：join的字段尽量创建索引

小坑：当join字段的类型不同时，索引是无法使用的。

调优原则4：尽量减少扫描的行数（explain-rows）

尽量控制在百万以内（经验之谈，仅供参考），如果是百万以内性能还是可以接受的，如果百万以上性能就比较慢了。大家可以参考。

调优原则5：参与join的表不要太多

阿里编程规约建议不超过3张。

如果业务一定要关联很多张表才能查出结果，那么可以拆分成多个sql，用多个方法执行。

如果拆分成多个sql，那么sql就变得简单，分析起来就比较容易，执行效率也高，如果是复杂查询优化起来就比较麻烦。

所以大家不要以编写复杂sql为荣，因为复杂sql在现在这个年代不再是什么值得夸耀的事情了。

调优原则6：如果被驱动表的join字段用不了索引，且内存较为充足，可以考虑把join buffer设置的大一些

2.Limit查询优化

先看一下下图：

从上图中我们不难发现，offset值越大，查询效率就会越慢。

我们分析一下：

可以看到它是用的全表扫描，这种情况下我们就需要考虑进行优化，我们一起探讨一下limit的优化方式。

优化方式1：覆盖索引

这里我们使用了主键覆盖，可以看到性能提升了很多，分析一下：

可以看到，它使用了全索引扫描，全索引扫描还是要比全表扫描快很多的。

优化方式2：覆盖索引+Join

对于优化方式1，如果我们确实期望返回chapter表的所有字段，那么我们可以这样玩：

这样它会先查出来chapter有哪些chapterId值，之后再select * from chapter扫描这些chapter值。

优化方式3：覆盖索引+子查询

这种方法的思路是，先从chapter里取chapter最小的值，然后再从拿到的最小值开始取10条。

优化方式4：如果能获得起始主键值和结束主键值

这样直接可以用between替换limit

优化方式5：禁止传入过大的页码

你懂得

3.COUNT语句优化

COUNT语句总结

  • count(*) 和 count(1)性能是一样的，不存在性能差异。

  • count(*)会选择最小的非主键索引，如果不存在任何非主键索引，则会使用主键

  • count(*)不会排除为null的行，而count(字段)会排除

  • 对于不带任何查询条件的count(*)语句，MyISAM及InnoDB(MySQL>=8.0.13)，都做了优化

  • 如果没有特殊需求，尽量使用COUNT(*)

优化方法

我们先来进行一次查询：

这里说明一下，之所以用chapter表，是因为chapter表数据量很多，能更好的看出效果，可以看到上面查询花费了701毫秒，性能并不可观。

我们来看一下chapter表的存储引擎：

可以看到，它使用的是InnoDB。

再来看一下数据库版本：

我们用的是5.6.16<8.0.13，因此不能针对无条件count语句优化。

如果我们版本高于8.0.13的话，上面sql可能花费的时间就比较短了，因此，升级mysql数据库的版本，会很大程度上提升我们的查询效率。

那么，我们就探讨怎样优化这条count语句。

先用explain分析这条sql的执行计划：

可以看出，type是index，说明发生了全索引扫描，使用的索引是idx_authorId，key的长度是4.

针对这条sql，我们有以下方案可以优化：

方案1：把数据库引擎换成MyISAM

实际项目用的很少，一般不会修改数据库引擎。

方案2：创建汇总表 table[table_name, count]

比如chapter数据发生变化，就修改汇总表的数据，比如chapter新增一条数据，汇总表count+1，删除一条数据，汇总表count-1。你也可以通过触发器自动维护汇总表。这种方式的好处是：结果比较准确，也比较灵活，你可以根据你的需求设计汇总表。 缺点也很明显，就是增加了维护成本。

方案3：通过sql_calc_found_rows

在做完本条查询后，自动执行count。

select sql_calc_found_rows * from salaries limit 0,10;
select found_rows() as salary_count;

缺点：mysql8.0.17已经废弃这种用法，未来会被删除。

方案4：使用缓存

可以将select count(*) from chapter; 结果存放到缓存。

好处：性能快，结果比较准确。 缺点：引入额外组件，增加了架构复杂度。

方案5：information_schema.tables

你可以这样玩：

在结果里有个TABLE_ROWS，就可以拿到chapter这张表的总条数了，并且可以看到，只花费了56毫秒，性能很快。

好处：不需要操作chapter表，不论chapter表有多少数据，都可以迅速地返回结果。

缺点：这是一个估算值，并不是准确值，也就意味着，如果你想使用这个sql，就要求你的业务对这个count的结果要求不那么精确。

方案6：show table status where name = ‘chapter’;

这个和方案5类似，不需要操作你的chapter表，不过它也是一个估算值，并不是精确值。

方案7：explain

优缺点也同方案5和方案6。

4.ORDER BY优化

如果想优化ORDER BY语句，最好的做法是：利用索引避免排序。这是ORDER BY调优的首要原则。

我们先来看一下bookindex这张表：

这个表里有一个组合索引。

我们执行一下这个sql：

可以看到，发生了全表扫描，发现并没有利用索引避免排序。我们不妨加一个limit看看：

可以发现，type变成了index，也就是说这条sql是可以使用索引的，那么为什么一会是ALL一会是index呢？

这是因为一开始的sql是相当于把整个表做排序的，而mysql的优化器是基于成本做计算的，当它发现全表扫描的开销比使用索引的开销更小的话，那么就直接全表扫描了。

并且我们可以通过执行计划返回结果的Extra字段，判断是否能用索引避免排序。

如果是Using filesort说明不能用索引避免排序。

排序模式1：rowid排序（常规排序）

就目前来说，mysql实现了3种排序的模式，第一种是rowid排序，也叫常规排序，大致原理是这样的：

  1. 从表中获取满足WHERE条件的记录

  2. 对于每条记录，将记录的主键及排序键（id,order_column）取出放入sort buffer（由sort_buffer_size控制）

  3. 如果sort buffer能存放所有满足条件的（id,order_column），则进行排序；否则sort buffer满后，排序并写到临时文件

    • 排序算法：快速排序算法

  4. 如果排序中产生临时文件，需要利用归并排序算法，从而保证记录有序。

  5. 循环执行上述过程，直到所有满足条件的记录全部参与排序。

  6. 扫描排好序的（id,order_column)对，并利用id去取SELECT 需要返回的其他字段。

  7. 返回结果集

rowid排序特点

  • 看sort buffer是否能存放结果集里面的所有(id,order_column)，如果不满足，就会产生临时文件。

  • 一次排序需要两次IO，第一次IO是上述步骤2，第二次IO是步骤6。

对于步骤6，由于返回的结果是按照order_column排序的，这次的结果对主键id来说，是乱序的，会存在随机IO的问题，MySql内部针对这种情况做了一个优化，它会在利用主键获取数据之前，按照主键排序并放到缓存里面，这个缓存可以由read_rnd_buffer_size控制，再接着去取记录，从而把随机IO转换成顺序IO。

排序模式2：全字段排序（优化排序）

全字段排序是针对于rowid排序的一个优化，它和rowid排序的步骤大致是一样的，区别在于全字段排序是：

  • 直接取出SQL中需要的所有字段，放到sort buffer，而不是像rowid取出主键及排序键（id,order_column）放到sort buffer。

  • 由于sort buffer 包含了查询所需要的所有字段，因此在sort buffer中排序完成后可直接返回。

全字段排序 VS rowid排序

  • 好处：带来性能的提升，无需两次IO

  • 缺点：一行数据占用的空间一般比rowid排序多；如果sort buffer 比较小，容易导致临时文件。

算法如何选择？

什么场景下选择rowid排序，什么场景下选择全字段排序呢？

mysql提供了max_length_for_sort_data：当ORDER BY SQL 中出现字段的总长度小于该值，使用全字段排序，否则使用rowid排序。

排序模式3：打包字段排序

  • MySQL 5.7引入

  • 是全字段模式的优化，工作原理一样，但是将字段紧密地排列在一起，而不是使用固定长度空间。

举个例子：VARCHAR(255) “yes”：不打包255字节；打包：2+3字节。

也就是说如果一个字段类型是VARCHAR，长度是255，字段值是yes，如果使用全字段排序的话就会使用255个字节，如果使用打包字段排序的话，只需要2个字节存储这个字段的长度，加上三个字节存储字段“yes”本身就可以了。这样就可以节省大量的空间，从而让sort buffer存储更多的内容。

参数汇总

上面我们探讨了三个排序模式，这里我们汇总一下，方便复习：

变量作用
sort_buffer_size指定sort buffer的大小
max_length_for_sort_data当ORDER BY SQL中出现字段的总长度小于该值，使用全字段排序，否则使用rowid排序
read_rnd_buffer_size按照主键排序后存放的缓存区

如何调优ORDER BY

  • 利用索引，防止filesort的发生

  • 如果发生了filesort，并且没办法避免，想办法优化filesort

filesort调优

  • 调大sort_buffer_size，减少/避免临时文件、归并操作。

我们如何知道什么时候应该调整sort_buffer_size呢？大家只需要关注两个方面：

- optimizer trace中num_initial_chunks_spilled_to_disk的值

如何这个值非常大，势必会产生大量的归并操作，这个时候可以调整sort_buffer_size的值。

- sort_merge_passes变量的值

它表示执行归并的次数，使用show status like ‘%sort_merge_passes%’，如果返回结果很大的话，说明是时候调整sort_buffer_size的时候了。

  • 调大read_rnd_buffer_size，让一次顺序IO返回的结果更多。

这个变量是按照主键排序之后存放到缓存区的大小，调大这个值可以让一次顺序IO返回的结果更多。

  • 设置合理的max_length_for_sort_data的值

这个参数如果设置太大，各种排序的sql都会利用全字段排序，可能就导致大量的内存占用，如果要写临时文件，又会占用大量的硬盘。而如果设置太小，又会导致各种排序sql都会使用rowid排序，从而产生两次IO，性能可能会差一些。

- 一般不建议随意调整
  • 调小max_sort_length(排序时最多取多少字节)

5.GROUP BY优化

就目前来说，MySQL有三种方式处理GROUP BY 语句，分别是：

  • 松散索引扫描（Loose Index Scan）

  • 紧凑索引扫描（Tight Index Scan）

  • 临时表（Temporary table）

松散索引扫描

  • 无需扫描满足条件的所有索引键即可返回结果

如果explain返回的结果Extra展示的是Using index for group-by，说明使用了松散索引扫描。

松散索引扫描使用条件

  • 查询作用在单张表上

  • GROUP BY指定的所有字段要符合最左前缀原则，且没有其他字段。

    • 比如有索引index(c1,c2,c3)，如果GROUP BY c1,c2则可以使用松散索引扫描；但GROUP BY c2,c3、GROUP BY c1,c2,c4则不能。

  • 如果存在聚合函数，只支持MIN()/MAX()，并且如果同时使用了MIN()和MAX()，则必须作用在同一个字段。聚合函数作用的字段必须在索引中，并且要紧跟GROUP BY 所指定的字段。

    • 比如有索引index(c1,c2,c3)，SELECT c1,c2,MIN(c3),MAX(c3) FROM t1 GROUP BY c1,c2可使用松散索引扫描。

  • 如果查询中存在除GROUP BY指定的列以外的其他部分，则必须以常量的形式出现。

    • 如SELECT c1,c3 FROM t1 GROUP BY c1,c2：不能使用

    • 如果想使用可以加一个WHERE条件：SELECT c1,c3 FROM t1 WHERE c3 = 3 GROUP BY c1,c2

  • 索引必须索引整个字段的值，不能是前缀索引。

    • 比如有字段c1 VARCHAR(20)，但如果该字段使用的是前缀索引index(c1(10))而不是index(c1)，无法使用松散索引扫描。

特定聚合函数用法能用上松散扫描的条件

  • AVG(DISTINCT)、SUM(DISTINCT)、COUNT(DISTINCT)，其中AVG(DISTINCT)、SUM(DISTINCT)可接受单个参数；而COUNT(DISTINCT)可接受多个参数

  • 查询中必须不存在GROUP BY或DISTINCT语句。

  • 满足前面所有使用松散索引扫描的条件。

假设index(c1,c2,c3)作用在表t1(c1,c2,c3,c4)上，下面这些SQL都能使用松散索引扫描：

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCE c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

紧凑索引扫描

紧凑索引扫描是相对于松散索引扫描来说的。

  • 需要扫描满足条件的所有索引键才能返回结果。

  • 性能一般比松散索引扫描差，但一般都可以接受。

临时表

  • 紧凑索引扫描也没有办法使用的话，MySQL将会读取需要的数据，并创建一个临时表，用临时表实现GROUP BY操作。

我们看这个sql，由于mergeId是没有索引的，我们通过explain分析后会发现，用的是全表扫描，但是Extra 显示了Using temporary表示使用了临时表。

GROUP BY调优思路

  • 如果GROUP BY使用了临时表，优化方案是为你的sql创建索引，想办法用上松散索引扫描或紧凑索引扫描。

6.DISTINCT 优化

DISTINCT和GROUP BY非常类似，你可以认为：

  • DISTINCT 是在GROUP BY操作之后，每组只取一条

  • 因此优化DISTINCT和GROUP BY优化思路一样。

尽量避免临时表，使用松散索引扫描或者紧凑索引扫描。

Percona Toolkit

Percona Toolkit是一款MySQL世界里非常实用的工具套件。

工具列表

  • pt-align ：对齐其他工具的输出

  • pt-archiver ：将数据归档到其他表或文件

  • pt-config-diff ：比较配置文件和变量

  • pt-deadlock-logger ：记录MySQL死锁

  • pt-diskstats ：交互式IO监控工具

  • pt-duplicate-key-checker ：找到重复的索引或外键

  • pt-fifo-split ：模拟分割文件并输出

  • pt-find ：查找表，并执行命令

  • pt-fingerprint ：将查询转换成fingerprint

  • pt-fk-error-logger ：记录外键错误信息

  • pt-heartbeat ：监控MySQL复制延迟

  • pt-index-usage ：通过日志分析查询，并分析查询如何使用索引

  • pt-ioprofile ：监控进程IO并打印IO活动表

  • pt-kill ：kill掉符合条件查询

  • pt-mext ：并行查询SHOW GLOBAL STATUS的样本信息

  • pt-mongodb-query-digest ：通过汇总来自MongoDB查询分析器（query profiler）的查询来报告查询使用情况统计信息

  • pt-mongodb-summary ：收集有关MongoDB集群的信息，它从多个来源收集信息从而提供集群的概要信息

  • pt-mysql-summary ：展示MySQL相关的概要信息

  • pt-online-schema-change ：在线修改表结构。无需锁表地ALTER表结构

  • pt-pg-summary ：收集有关PostgreSQL集群的信息

  • pt-pmp ：针对指定程序，聚合GDB的stack traces

  • pt-query-digest ：从日志、processlist以及tcpdump中分析MySQL查询

  • pt-secure-collect ：收集、清理、打包、加密数据

  • pt-show-grants ：规范化打印MySQL授权

  • pt-sift ：浏览由pt-stalk创建的文件

  • pt-slave-delay ：使MySQL从属服务器滞后于其Master

  • pt-slave-find ：查找和打印MySQL slave的复制层级树

  • pt-slave-restart ：监控MySQL slave，并在发生错误后重启

  • pt-stalk ：发生问题时收集有关MySQL的诊断数据

  • pt-summary ：展示系统概要信息

  • pt-table-checksum ：验证MySQL主从复制的一致性

  • pt-table-sync ：高效同步表数据

  • pt-table-usage ：分析查询是如何使用表的

  • pt-upgrade ：验证不同服务器上的查询结果是否相同

  • pt-variable-advisor ：分析MySQL变量，并对可能出现的问题提出建议

  • pt-visual-explain ：将explain的结果格式化成树形展示

安装

不同操作系统安装Percona Toolkit的方式是不一样的，如果你使用的是Windows可以直接放弃，因为它不支持Windows。

如果你使用的是Mac系统，直接使用这条命令就足够了：

brew install percona-toolkit

Percona Toolkit使用

pt-query-digest

这是一款分析工具，它可以分析日志、processlist以及tcpdump里的查询，从而帮助我们进行sql优化，就目前来说，很多企业用它来分析慢查询日志，这款工具非常使用，而且在业界也非常受欢迎。

官方文档：https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html

  • 作用

分析日志（包括binlog、General log、slowlog）、processlist以及tcpdump中的查询

  • 语法
pt-query-digest [OPTIONS] [FILES] [DSN]
  • 常用OPTIONS
--create-review-table  当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。
--create-history-table  当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
--filter  对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
--host  MySQL服务器地址
--user  mysql用户名
--password  mysql用户密码
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。
--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
--until 截止时间，配合—since可以分析一段时间内的慢查询。
  • 使用示例
# 展示slow.log中最慢的查询的报表
pt-query-digest slow.log

# 分析最近12小时内的查询
pt-query-digest --since=12h slow.log

# 分析指定范围内的查询
pt-query-digest slow.log --since '2020-06-20 00:00:00' --until '2020-06-25 00:00:00'

# 把slow.log中查询保存到query_history表
pt-query-digest --user=root --password=root123 --review h=localhost,D=test,t=query_history --create-review-table slow.log

# 连上localhost，并读取processlist，输出到slowlog
pt-query-digest --processlist h=localhost --user=root --password=root123 --interval=0.01 --output slowlog

# 利用tcpdump获取MySQL协议数据，然后产生最慢查询的报表
# tcpdump使用说明：https://blog.csdn.net/chinaltx/article/details/87469933
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

# 分析binlog
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest  --type=binlog mysql-bin000093.sql

# 分析general log
pt-query-digest  --type=genlog  localhost.log
  • 结果可视化：

在Percona官方：https://www.percona.com/blog/2012/08/31/visualization-tools-for-pt-query-digest-tables/

  • Query Digest UI

  • Box Anemometer

  • 这两款工具已N多年不维护了，如果感兴趣也可以搭建玩一玩，不建议用于生产。

pt-index-usage

官方文档：https://www.percona.com/doc/percona-toolkit/3.0/pt-index-usage.html

  • 作用

通过日志文件分析查询，并分析查询如何使用索引

  • 原理

    • 清点数据库中所有表与索引，并将数据库中现有的索引和日志中的查询所使用的索引进行比较

    • 对日志中每个查询运行EXPLAIN（这一步使用单独的数据库连接清点表并执行EXPLAIN）

    • 对于无用的索引，展示删除的语句。

  • 语法

pt-index-usage [OPTIONS] [FILES]
  • 常用OPTIONS
--drop      打印建议删除的索引，取值primary、unique、non-unique、all。 默认值non-unique，只会打印未使用的二级索引。
--database   只分析指定数据库的索引，多个库用,分隔
--tables     只分析指定表的索引，多张表用,分隔
--progress    打印执行速度
--host        指定MySQL地址，也可用-h指定
--post        指定MySQL端口
--user        指定MySQL用户名，也可用-u指定
--password    指定MySQL密码，也可用-p指定
  • 使用示例
# 读取slow.log,并连上localhost,去分析有哪些索引是可以删除的
pt-index-usage slow.log --user=root --password=root123 --host=localhost --port=3306

#读取slow.log,并连上localhost，只分析bookdb库中，有哪些索引是可以删除的
pt-index-usage slow.log --user=root  --password=root123 --host=localhost  --databases=bookdb

  • 注意点：

    • 此工具使用MySQL资源比较多，因此在使用此工具的时候：

      • 如果有条件，尽量不要直接在生产环境执行，而应在有相同表结构的数据库环境执行；

      • 如果必须在生产环境执行，请避开高峰期，比如在凌晨低谷期执行

    • 此工具分析大文件比较慢，使用时需要注意这点，并做一定处理（比如把遗留的超大的慢查询日志先删除，而可以建立一个慢查询日志，并运行一段时间后用pt-index-usage分析）

    • 由于pt-index-usage只会扫描慢查询，而非所有查询，所以有可能某个索引在慢查询日志未使用，但其实还是使用了的（只是使用这个索引的SQL并非慢查询）。因此：

      • 正式删除之前，应先review下，确保可以删除该索引后再操作，避免发生问题。

pt-variable-advisor

官方文档：https://www.percona.com/doc/percona-toolkit/3.0/pt-variable-advisor.html

  • 作用

分析MySQL变量，并对可能出现的问题提出建议。

  • 原理

执行SHOW VARIABLES，并分析哪些变量的值设置不合理，给出建议。

  • 语法
pt-variable-advisor [OPTIONS] [DSN]
  • 常用OPTIONS
--source-of-variable    指定变量来源，可选mysql/none或者文件
--user                  指定MySQL用户名 可用-u指定
--password              指定MySQL密码，也可用-p指定

pt-online-schema-change

官方文档: https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html

从MySQL5.6开始，已支持online r功能，pt-online-schema-change越来越弱化了。

有关Online DDL，可详见 [__Online DDL Operations__](https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html)

有关online DDL和pt-online-schema-change之间的对比详见《[__MySQL ONLINE DDL 和PT-ONLINE-SCHEMA-CHANGE对比__](http://blog.itpub.net/27067062/viewspace-2147452/)》
  • 作用

在线修改表结构，无需锁表地ALTER表结构

  • 原理

    • 创建一张一模一样的表，表名一般是_new后缀

    • 在新表上执行更改表的操作。

    • 在原表上加三个触发器，分别对应DELETE/UPDATE/INSERT操作，并将原表中要执行的语句也在新表中执行。

    • 将原表的数据拷贝到新表中

    • 使用原子的RENAME TABLE操作同时重命名原始表和新表，完成此操作后，删除原始表。

  • 语法

pt-online-schema-change [OPTIONS] DSN
  • 常用OPTIONS
–-dry-run	  创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，与--print配合查看执行细节
--execute	如果指定该选项，则会修改表结构，否则只会做一些安全检查
--charset   指定编码
--alter     修改表结构的语句（其实就是你的alert table语句，去掉alert table后剩下的部分），多条语句使用,分隔。该选项有一些限制，详见：https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html#cmdoption-pt-online-schema-change-alter
--no-version-check    是否检查版本
--alter-foreign-keys-method 处理带有外键约束的表，以保证他们可以引用到正确的表。取值：auto（自动选择最佳策略）、rebuild_constraints（适用于删除和重新添加引用新表的外键约束）、drop_swap（禁用外键检查，然后再重命名新表之前将其删除）、none（无）

最后

以上就是贪玩信封为你收集整理的Mysql调优大全梳理（涵盖90%需要调优的场景）前言1.Join语句优化2.Limit查询优化3.COUNT语句优化4.ORDER BY优化5.GROUP BY优化6.DISTINCT 优化Percona Toolkit的全部内容，希望文章能够帮你解决Mysql调优大全梳理（涵盖90%需要调优的场景）前言1.Join语句优化2.Limit查询优化3.COUNT语句优化4.ORDER BY优化5.GROUP BY优化6.DISTINCT 优化Percona Toolkit所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(49)

相关文章

c语言while和if的嵌套,分之和循环语句 （if 、for 、while）
c语言while和if的嵌套,分之和循环语句 （if 、for 、while）
c语言循环流程图复杂及盒式结构图,C语言中for循环嵌套的流程图怎么画呢？
c语言循环流程图复杂及盒式结构图,C语言中for循环嵌套的流程图怎么画呢？
php中for循环流程图,PHP for循环语句
php中for循环流程图,PHP for循环语句
利用MATLAB计算无限级数和泰勒级数1 无限级数2 泰勒级数
利用MATLAB计算无限级数和泰勒级数1 无限级数2 泰勒级数
Mysql调优大全梳理（涵盖90%需要调优的场景）前言1.Join语句优化2.Limit查询优化3.COUNT语句优化4.ORDER BY优化5.GROUP BY优化6.DISTINCT 优化Percona Toolkit
Mysql调优大全梳理（涵盖90%需要调优的场景）前言1.Join语句优化2.Limit查询优化3.COUNT语句优化4.ORDER BY优化5.GROUP BY优化6.DISTINCT 优化Percona Toolkit
MATLAB循环结构1、for语句 
MATLAB循环结构1、for语句 
【无人驾驶文献】无人驾驶、自动驾驶综述论文推荐Overall
【无人驾驶文献】无人驾驶、自动驾驶综述论文推荐Overall
[Simulink] 基于模型的测试与验证学习笔记_Step 3: Detecting Desig ErrorsSimulink Design Verifier对模型做死逻辑检测问题分析模型除以零的检测自动设计错误检测总结
[Simulink] 基于模型的测试与验证学习笔记_Step 3: Detecting Desig ErrorsSimulink Design Verifier对模型做死逻辑检测问题分析模型除以零的检测自动设计错误检测总结

评论列表共有 0 条评论

立即
投稿
返回
顶部