perconatoolkit之ptindexusage和ptduplicatekeychecker详解

pt-index-usage：从慢查询日志中读取查询并分析它们如何使用索引。 (用来查找不常使用索引)

./pt-index-usage --help

打印报告

 1 ./pt-index-usage  /mysqldata/mysqlslowlog/slowquery.log -h192.168.226.131 -uroot -p6yhn^YHN -decology
 2 
 3  
 5 [mysql@mysql bin]$ ./pt-index-usage  /mysqldata/mysqlslowlog/slowquery.log -h192.168.226.131 -uroot -p6yhn^YHN 
 7 DBD::mysql::db selectall_arrayref failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/' at line 1 [for Statement "EXPLAIN SELECT IFNULL(SUM(INDEX_LENGTH),0) from information_schema.TABLES where ENGINE='InnoDB' */"] at ./pt-index-usage line 4598, <> line 1.
 9 DBD::mysql::db selectall_arrayref failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/' at line 1 [for Statement "EXPLAIN SELECT IFNULL(SUM(DATA_LENGTH),0) from information_schema.TABLES where ENGINE='InnoDB' */"] at ./pt-index-usage line 4598, <> line 2.
11 DBD::mysql::db selectall_arrayref failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/' at line 1 [for Statement "EXPLAIN SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' */"] at ./pt-index-usage line 4598, <> line 4.
12 
15 ALTER TABLE `test1`.`user` DROP KEY `idx_1`; -- type:non-unique
16

2> pt-duplicate-key-checker检查数据库的重复索引

索引会更查询带来好处，但是过量的索引反而可能会使数据库的性能降低

1 ./pt-duplicate-key-checker --help

1> 表结构

 1 [root@localhost][test1]> show index from user;
 2 
 3 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 4 
 5 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 6 
 7 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 8 
 9 | user  |          0 | PRIMARY  |            1 | id          | A         |     1042305 |     NULL | NULL   |      | BTREE      |         |               |
10 
11 | user  |          1 | idx_1    |            1 | id          | A         |     1042305 |     NULL | NULL   |      | BTREE      |         |               |
12 
13 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
14 
15

字段id有两个索引，

 1 [mysql@mysql bin]$ ./pt-duplicate-key-checker -uroot -p6yhn^YHN
 2 
 3 # ########################################################################
 4 
 5 # test1.user                                                              
 6 
 7 # ########################################################################
 8 
 9  
10 
11 # idx_1 is a duplicate of PRIMARY
12 
13 # Key definitions:
14 
15 #   KEY `idx_1` (`id`)
16 
17 #   PRIMARY KEY (`id`),
18 
19 # Column types:
20 
21 #         `id` int(11) not null auto_increment
22 
23 # To remove this duplicate index, execute:
24 
25 ALTER TABLE `test1`.`user` DROP INDEX `idx_1`;
26 
27  
28 
29 # ########################################################################
30 
31 # Summary of indexes                                                      
32 
33 # ########################################################################
34 
35  
36 
37 # Size Duplicate Indexes   4169220
38 
39 # Total Duplicate Indexes  1
40 
41 # Total Indexes            34
42 
43