[MySQL 工具] pt-index-usage使用 --> 对查询中的索引进行分析

pt-index-usage¶

pt-index-usage从日志里面读取查询,并且分析它们是如何使用索引的.

概述：

使用

pt-index-usage [OPTIONS] [FILES]

pt-index-usage ：从日志里面读取查询,并且分析它们是如何使用索引的.

分析查询会在slow.log里面并且打印报告：

pt-index-usage /path/to/slow.log --host localhost

不打印出来，而且对于后来的分析把结果存入percona数据库里面：

pt-index-usage slow.log --no-report --save-results-database percona

风险：RISKS

Percona Toolkit 是成熟的产品，是在实际中被证明过的，而且良好测试过，但是所有的数据库工具都会对系统和数据库服务造成影响，在使用此工具之前，请注意：

• 阅读工具文档 Read the tool’s documentation
• 复审工具熟知的 Bugs Review the tool’s known “BUGS”
• 在非生产服务器测试工具 Test the tool on a non-production server
• 备份你的生产环节并且分析核查备份 Backup your production server and verify the backups

描述：DESCRIPTION

This tool connects to a MySQL database server, reads through a query log, and uses EXPLAIN to ask MySQL how it will use each query. When it is finished, it prints out a report on indexes that the queries didn’t use.

工具连接到MySQL数据库服务器，通读查询日志，使用EXPLAIN 命令去问MySQL如何执行每一个QUERY操作的。当这些完成，它会打印没有使用索引的报告出来。

The query log needs to be in MySQL’s slow query log format. If you need to input a different format, you can use pt-query-digest to translate the formats. If you don’t specify a filename, the tool reads from STDIN.

这个查询日志需要MySQL慢查询日志格式，如果你需要输入不同的格式，你能使用pt-query-digest将格式转换，如果你不指定特定的名字，工具将从STDIN读取。

The tool runs two stages. In the first stage, the tool takes inventory of all the tables and indexes in your database, so it can compare the existing indexes to those that were actually used by the queries in the log. In the second stage, it runs EXPLAIN on each query in the query log. It uses separate database connections to inventory the tables and run EXPLAIN, so it opens two connections to the database.

工具运行2个阶段，在第一个阶段，工具缓存库里面所有的表以及索引，因此它能已存在的索引与在日志里面真正被使用的查询进行比对。第二个阶段，他对查询日志的每一条查询执行EXPLAIN命令，它使用隔离的库连接去存储表并且执行explain，因此它开启了2个数据库连接。

If a query is not a SELECT, it tries to transform it to a roughly equivalent SELECT query so it can be EXPLAINed. This is not a perfect process, but it is good enough to be useful.

如果一个查询不是SELECT，它试图去将它转换成粗略对等的SELECt查询，来让这个query能被执行explained，这部不是最完美的，但是足够实用。

The tool skips the EXPLAIN step for queries that are exact duplicates of those seen before. It assumes that the same query will generate the same EXPLAIN plan as it did previously (usually a safe assumption, and generally good for performance), and simply increments the count of times that the indexes were used. However, queries that have the same fingerprint but different checksums will be re-EXPLAINed. Queries that have different literal constants can have different execution plans, and this is important to measure.

对于这个以前已经看过得准确的副本的查询，工具忽略了explain步骤。假定同样的查询可以产生同样的以前产生的explain计划(通常一个安全的假定，对于性能来说通常是好的)。并且简单的增长索引使用的时间数，尽管如此，查询有同样的指纹以及不同的校验将被重新执行explain。查询在不同的上下文环境里面能有不同的执行计划，并且对于估算来说是很重要的。

After EXPLAIN-ing the query, it is necessary to try to map aliases in the query back to the original table names. For example, consider the EXPLAIN plan for the following query:

在对查询执行完EXPLAIN之后，把别名映射回原表名是很有必要的，例如，考入如下的explain计划：

SELECT * FROM tbl1 AS foo;

The EXPLAIN output will show access to tablefoo, and that must be translated back totbl1. This process involves complex parsing. It is generally very accurate, but there is some chance that it might not work right. If you find cases where it fails, submit a bug report and a reproducible test case.

explain输入结果会显示对表foo的访问，并且必须转换回tb11，这个过程包含复杂的解析过程。通常是非常精准的，但是也有可能不会解析的很准确，如果你找到了失败的例子，请提交一个bug报告并且可再生的测试案例。

Queries that cannot be EXPLAINed will cause all subsequent queries with the same fingerprint to be blacklisted. This is to reduce the work they cause, and prevent them from continuing to print error messages. However, at least in this stage of the tool’s development, it is my opinion that it’s not a good idea to preemptively silence these, or prevent them from being EXPLAINed at all. I am looking for lots of feedback on how to improve things like the query parsing. So please submit your test cases based on the errors the tool prints!

不能被执行explain的查询会引起所有后续的有同样指纹的查询被列入黑名单。 这会减少他们引起的工作，并且阻止他们继续打印错误消息。尽管如此，至少工具部署的这个步骤，在我看来 保持沉默(这里怎么说？停滞在这里？) 不是一个好主意，或者 预防他们被执行 EXPLAINed，正在看大量的关于如何提高查询分区的性能，因此请提交在发生在工具上面打印出来的错误。

OUTPUT 输出

After it reads all the events in the log, the tool prints out DROP statements for every index that was not used. It skips indexes for tables that were never accessed by any queries in the log, to avoid false-positive results.

在读取完日志里面所有的事件之后，对于每一个没有用到的索引，工具打印出DROP语句。忽略了被日志里面任意查询访问的表索引，避免不必要的结果。

If you don’t specify--quiet, the tool also outputs warnings about statements that cannot be EXPLAINed and similar. These go to standard error.

如果你没有指定--quiet，工具也会输出不能被执行类似EXPLAINed的警告，这些都是标准错误。

Progress reports are enabled by default (see--progress). These also go to standard error.

默认会产生pregress报告(参见--progress)，这些也似标准错误。

OPTIONS 选项

--[待续中]--

原文地址：http://www.percona.com/doc/percona-toolkit/2.2/pt-index-usage.html#cmdoption-pt-index-usage--

不足之处，请大家多多指点。