60.MySQL-Utilities工具包之-mysqlindexcheck

mysqlindexcheck  
1.检查数据库test的冗余或者重复的索引。
[root@mysql1 ~]# mysqlindexcheck --server=root:rootroot@192.168.1.51:3306:/mysql/mysql3306.sock --show-drops --show-indexes --stats  --report-indexes -vvv test
WARNING: Using a password on the command line interface can be insecure.
# Source on 192.168.1.51: ... connected.
# Checking indexes...
# Getting indexes for test.test1
# Showing indexes from test.test1:
#
+-----------+--------+----------+--------+---------+----------------+----------+
| database  | table  | name     | type   | unique  | accepts nulls  | columns  |
+-----------+--------+----------+--------+---------+----------------+----------+
| test      | test1  | PRIMARY  | BTREE  | True    | False          | id       |
+-----------+--------+----------+--------+---------+----------------+----------+
#
# Table test.test1 has no duplicate nor redundant indexes.
# WARNING: Not enough data to calculate best/worst indexes.
#
# Getting indexes for test.test2
# Showing indexes from test.test2:
#
+-----------+--------+----------+--------+---------+----------------+----------+
| database  | table  | name     | type   | unique  | accepts nulls  | columns  |
+-----------+--------+----------+--------+---------+----------------+----------+
| test      | test2  | PRIMARY  | BTREE  | True    | False          | id       |
+-----------+--------+----------+--------+---------+----------------+----------+
#
# Table test.test2 has no duplicate nor redundant indexes.
# WARNING: Not enough data to calculate best/worst indexes.
#
# Getting indexes for test.test3
# Showing indexes from test.test3:
#
+-----------+--------+----------+--------+---------+----------------+----------+
| database  | table  | name     | type   | unique  | accepts nulls  | columns  |
+-----------+--------+----------+--------+---------+----------------+----------+
| test      | test3  | PRIMARY  | BTREE  | True    | False          | id       |
+-----------+--------+----------+--------+---------+----------------+----------+
#
# Table test.test3 has no duplicate nor redundant indexes.
# WARNING: Not enough data to calculate best/worst indexes.
#
# ...done.

2.查看某个表test.test1 的索引是否存在冗余索引
[root@mysql1 ~]# mysqlindexcheck --server=root:rootroot@192.168.1.51:3306:/mysql/mysql3306.sock --show-drops --show-indexes --stats  --report-indexes -vvv test.test1
WARNING: Using a password on the command line interface can be insecure.
# Source on 192.168.1.51: ... connected.
# Checking indexes...
# Getting indexes for test.test1
# Showing indexes from test.test1:
#
+-----------+--------+----------+--------+---------+----------------+----------+
| database  | table  | name     | type   | unique  | accepts nulls  | columns  |
+-----------+--------+----------+--------+---------+----------------+----------+
| test      | test1  | PRIMARY  | BTREE  | True    | False          | id       |
+-----------+--------+----------+--------+---------+----------------+----------+
#
# Table test.test1 has no duplicate nor redundant indexes.
# WARNING: Not enough data to calculate best/worst indexes.
#
# ...done.
3.总结

mysqlindexcheck 工具可以检查每个表的索引。这样我们通过输出结果就能很方便的查看表的索引是否有重复的。