Percona Toolkit使用测试（10）PT-INDEX-USAGE

pt-index-usage：从慢查询日志中读取查询并分析它们如何使用索引。
分析慢查询日志的语句并打印报告：


禁用报告，保存结果到数据库：

该工具连接到MySQL数据库服务器，读取查询日志，并使用EXPLAIN询问MySQL将如何使用每个查询。当它完成时，它打印出一个关于查询没有使用的索引的报告。

查询日志需要采用MySQL的慢查询日志格式。如果需要输入不同的格式，可以使用pt-query-digest转换格式。如果不指定文件名，工具将从STDIN中读取。（stdin是标准输入，一般指键盘输入到缓冲区里的东西。）
该工具运行两个阶段。在第一个阶段，该工具将对数据库中的所有表和索引进行编目，以便将现有索引与日志中的查询实际使用的索引进行比较。在第二阶段，它对查询日志中的每个查询运行EXPLAIN。它使用单独的数据库连接对表进行编目并运行EXPLAIN，因此它将打开两个数据库连接。

如果查询不是SELECT，则尝试将其转换为大致等效的SELECT查询，以便对其进行解释。
这并不是一个完美的过程，但是它已经足够有用了。

该工具如果遇到一模一样的查询，将会跳过explain步骤，它假设相同的查询拥有着相同的执行计划，并简单的增加使用索引的次数，但是具有相同指纹但是不同校验的查询将会被重新explain，具有不同文字常量的查询可能具有不同的查询计划

在解释了查询之后，有必要尝试将查询中的别名映射回原始表名。例如，考虑以下查询的EXPLAIN计划:

EXPLAIN输出将显示对表foo的访问，并且必须将其转换回tbl1。这个过程涉及复杂的解析。它通常是非常准确的，但也有可能不能正确工作

无法解释的查询将导致具有相同指纹的所有后续查询被列入黑名单。这是为了减少它们引起的工作，并防止它们继续打印错误消息。

输出
在读取日志中的所有事件之后，该工具将为每个未使用的索引打印DROP语句。
它跳过日志中查询从未访问过的表的索引，以避免出现假阳性结果。
如果没有指定- -quiet，该工具还会输出关于无法EXPLAIN 或类似的语句的警告
默认情况下启用进度报告(参见 --progress）

选项
- -ask-pass
连接时询问密码
- -charset
字符集
- -config
配置文件
- -create-save-results-database
如果不存在 --save-results-database 数据库则创建
如果 --save-results-database已经存在，并且指定了此选项，则使用数据库，如果不存在必要的表，则创建它们。

- -[no]create-views
为 --save-results-database示例查询创建视图。
- -database
连接到的数据库
- -databases
只从这个逗号分隔的数据库列表中获取表和索引。
- -databases-regex
只从数据库中获取名称与此Perl正则表达式匹配的表和索引。
- -defaults-file
mysql 配置文件
- -drop
类型:散列;默认值:非唯一

建议只删除这些类型的未使用索引。

默认情况下pt-index-usage只建议删除未使用的辅助索引，而不是主索引或惟一索引。您可以指定该工具建议删除哪些类型的未使用索引:
primary, unique, non-unique,all
为每种类型打印一个单独的ALTER TABLE语句。如果你指定-drop all，这里有一个主键和一个非唯一索引,
每个索引的ALTER TABLE … DROP将被打印在单独的行上。
- -empty-save-results-tables
删除并重新创建 --save-results-database.数据库中所有已存在的表。这允许在当前运行之前删除以前运行的信息。
- -help
帮助并退出
- -host
连接主机名、ip
- -ignore-databases
忽略这个逗号分隔的数据库列表。
- -ignore-databases-regex
忽略名称与此Perl正则表达式匹配的数据库。
- -ignore-tables
忽略这个以逗号分隔的表名列表。
表名可以用数据库名限定。
- -ignore-tables-regex
忽略名称与Perl正则表达式匹配的表
- -password
连接密码
- -progress
类型:数组;默认值:time,30
向STDERR打印进度报告。该值是一个逗号分隔的列表，由两部分组成。第一部分可以是percentage, time, or iterations
第二部分指定了更新的打印频率，以百分比、秒或迭代次数为单位。
- -port
连接端口
- -quiet
不要打印任何警告。也禁用 --progress
- -[no]report
default: yes
根据–report-format.报告格式打印报告。
您可能希望通过指定“ --no-report”来禁用报表
- -report-format
类型:数组;默认值:drop_unused_indexes

现在只有一个报告:drop_unused_indexes。此报告打印用于删除任何未使用索引的SQL语句。
- -save-results-database
将结果保存到这个数据库中的表中。有关索引、查询、表及其用法的信息存储在指定数据库中的几个表中。如果表不存在，则自动创建它们。如果数据库不存在，可以使用—create-save-results-database自动创建它。在这种情况下，连接最初是在没有默认数据库的情况下创建的，然后在创建数据库之后，USE它。

pt-index-usage执行INSERT语句来保存结果。因此，如果在生产服务器上使用此功能，则应该小心。它可能会增加负载，或者在不希望将服务器写入的情况下造成麻烦，等等。

这是一个新特性。它可能会在未来的版本中发生变化。

运行之后，您可以查询usage表来回答关于索引使用情况的各种问题。这些表有以下CREATE TABLE定义:

下面是您可以针对这些表运行的一些查询，以回答您可能遇到的常见问题。

如果参数: ’ --[no]create-views '为真(默认情况下为真)，每个查询还将作为视图创建(MySQL v5.0或更新版本)。视图名称是MAGIC_view_前缀后面的字符串。
问题:哪些查询有时使用不同的索引，以及每个索引选择的时间比例是多少?
MAGIC_view_query_uses_several_indexes:
SELECT iu.query_id, CONCAT_WS(’.’, iu.db, iu.tbl, iu.idx) AS idx,
variations, iu.cnt, iu.cnt / total_cnt * 100 AS pct
FROM index_usage AS iu
INNER JOIN (
SELECT query_id, db, tbl, SUM(cnt) AS total_cnt,
COUNT() AS variations
FROM index_usage
GROUP BY query_id, db, tbl
HAVING COUNT() > 1
) AS qv USING(query_id, db, tbl);
问:哪些索引有很多备选项，即被选择来代替其他索引，以及用于哪些查询?
MAGIC_view_index_has_alternates:
SELECT CONCAT_WS(’.’, db, tbl, idx) AS idx_chosen,
GROUP_CONCAT(DISTINCT alt_idx) AS alternatives,
GROUP_CONCAT(DISTINCT query_id) AS queries, SUM(cnt) AS cnt
FROM index_alternatives
GROUP BY db, tbl, idx
HAVING COUNT(*) > 1;
问:哪些索引被认为是其他索引的替代，以及哪些查询的替代?
MAGIC_view_index_alternates:
SELECT CONCAT_WS(’.’, db, tbl, alt_idx) AS idx_considered,
GROUP_CONCAT(DISTINCT idx) AS alternative_to,
GROUP_CONCAT(DISTINCT query_id) AS queries, SUM(cnt) AS cnt
FROM index_alternatives
GROUP BY db, tbl, alt_idx
HAVING COUNT(*) > 1;
问:哪些索引 这些查询从来没有选择过，因此是多余的?
MAGIC_view_unused_index_alternates:
SELECT CONCAT_WS(’.’, i.db, i.tbl, i.idx) AS idx,
alt.alternative_to, alt.queries, alt.cnt
FROM indexes AS i
INNER JOIN (
SELECT db, tbl, alt_idx, GROUP_CONCAT(DISTINCT idx) AS alternative_to,
GROUP_CONCAT(DISTINCT query_id) AS queries, SUM(cnt) AS cnt
FROM index_alternatives
GROUP BY db, tbl, alt_idx
HAVING COUNT(*) > 1
) AS alt ON i.db = alt.db AND i.tbl = alt.tbl
AND i.idx = alt.alt_idx
WHERE i.cnt = 0;
问:给定一个表，有多少查询使用了哪些索引，有多少不同的指纹?
有替代品吗?哪些索引没有使用?您可以编辑以下查询的SELECT列表，以查看有问题的查询id。MAGIC_view_index_usage:
SELECT i.idx, iu.usage_cnt, iu.usage_total,
ia.alt_cnt, ia.alt_total
FROM indexes AS i
LEFT OUTER JOIN (
SELECT db, tbl, idx, COUNT() AS usage_cnt,
SUM(cnt) AS usage_total, GROUP_CONCAT(query_id) AS used_by
FROM index_usage
GROUP BY db, tbl, idx
) AS iu ON i.db=iu.db AND i.tbl=iu.tbl AND i.idx = iu.idx
LEFT OUTER JOIN (
SELECT db, tbl, idx, COUNT() AS alt_cnt,
SUM(cnt) AS alt_total,
GROUP_CONCAT(query_id) AS alt_queries
FROM index_alternatives
GROUP BY db, tbl, idx
) AS ia ON i.db=ia.db AND i.tbl=ia.tbl AND i.idx = ia.idx;
问题:给定表上的哪些索引对于至少一个查询是至关重要的(没有其他选择)?
MAGIC_view_required_indexes:
SELECT i.db, i.tbl, i.idx, no_alt.queries
FROM indexes AS i
INNER JOIN (
SELECT iu.db, iu.tbl, iu.idx,
GROUP_CONCAT(iu.query_id) AS queries
FROM index_usage AS iu
LEFT OUTER JOIN index_alternatives AS ia
USING(db, tbl, idx)
WHERE ia.db IS NULL
GROUP BY iu.db, iu.tbl, iu.idx
) AS no_alt ON no_alt.db = i.db AND no_alt.tbl = i.tbl
AND no_alt.idx = i.idx
ORDER BY i.db, i.tbl, i.idx, no_alt.queries;

- -set-vars
设置mysql变量
- -socket
连接socket文件
- -tables
只从这个以逗号分隔的表列表中获取索引。
- -tables-regex
只从名称与此Perl正则表达式匹配的表中获取索引。
- -user
连接用户
- -version
显示版本并退出
- -[no]version-check
检查工具、mysql版本