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 工具可以检查每个表的索引。这样我们通过输出结果就能很方便的查看表的索引是否有重复的。