MySQL日常运维之toolkit：pt-index-usage和pt-duplicate-key-checker详解

./pt-index-usage --help

./pt-index-usage  mysqldata/mysqlslowlog/slowquery.log -h192.168.226.131 -uroot -p6yhn^YHN -decology

[mysql@mysql bin]$ ./pt-index-usage  mysqldata/mysqlslowlog/slowquery.log -h192.168.226.131 -uroot -p6yhn^YHN 
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.
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.
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.


ALTER TABLE `test1`.`user` DROP KEY `idx_1`; -- type:non-unique

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

[root@localhost][test1]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     1042305 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | idx_1    |            1 | id          | A         |     1042305 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

[mysql@mysql bin]$ ./pt-duplicate-key-checker -uroot -p6yhn^YHN
# ########################################################################
# test1.user                                                              
# ########################################################################
# idx_1 is a duplicate of PRIMARY
# Key definitions:
#   KEY `idx_1` (`id`)
#   PRIMARY KEY (`id`),
# Column types:
#         `id` int(11) not null auto_increment
# To remove this duplicate index, execute:
ALTER TABLE `test1`.`user` DROP INDEX `idx_1`;
# ########################################################################
# Summary of indexes                                                      
# ########################################################################
# Size Duplicate Indexes   4169220
# Total Duplicate Indexes  1
# Total Indexes            34