Mysql管理必备工具Maatkit详解之四（mk-duplicate-key-checker）

2009年05月19日　作者： 大头刚　

mk-duplicate-key-checker - 检查MYSQL重复索引,并给出删除语句。安装方法可以参考。

在oracle里，不允许在同一个列上创建多个索引，如果试图创建就会报错如下：

CREATE INDEX sg1 ON uid_tmp(user_id);
CREATE INDEX sg1 ON uid_tmp(user_id)
                            *
ERROR AT line 1:
ORA-01408: such column list already indexed

而在mysql上，却是允许的，不知道为何要这样，总之是可以的。

mysql> create index sg1 on t1(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> create index sg2 on t1(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         | 
| t1    |          1 | sg1      |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         | 
| t1    |          1 | sg2      |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         | 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

在ID这个列上，居然有3个索引，显然严重浪费资源。如果你的数据库有很多表，一个一个表去查看显然是一件很费力，很机械的事情，那么你可以用mk-duplicate-key-checker工具来帮你，使用很简单：

[root@mysql2 u01]# mk-duplicate-key-checker -u sg -p 
# ########################################################################
# test.t1                                                                 
# ########################################################################
 
# sg1 is a duplicate of PRIMARY
# Column types:
#	  `id` int(11) NOT NULL
# To remove this duplicate key, execute:
ALTER TABLE `test`.`t1` DROP KEY `sg1`;
 
# sg2 is a duplicate of PRIMARY
# Column types:
#	  `id` int(11) NOT NULL
# To remove this duplicate key, execute:
ALTER TABLE `test`.`t1` DROP KEY `sg2`;
 
# ########################################################################
# Summary of keys                                                         
# ########################################################################
 
# Size Duplicate Keys   256
# Total Duplicate Keys  2
# Total Keys            293

给出了存在重复索引的表和列，以及删除的语法。
OK,还有其他的一些参数，可以看下帮助文件。

mk-duplicate-key-checker --help

mk-duplicate-key-checker examines MySQL tables for duplicate or redundant
indexes and foreign keys.  Connection options are read from MySQL option files.
For more details, please use the --help option, or try 'perldoc
/usr/bin/mk-duplicate-key-checker' for complete documentation.

Usage: /usr/bin/mk-duplicate-key-checker 

Options:
  --all-structs       Compare indexes with different structs (BTREE, HASH, etc)
  --ask-pass          Prompt for a password when connecting to MySQL
  --charset       -A  Default character set
  --[no]clustered     PK columns appended to secondary key is duplicate (default
                      yes)
  --config            Read this comma-separated list of config files; if
                      specified, this must be the first option on the command
                      line
  --databases     -d  Check only this comma-separated list of databases
  --defaults-file -F  Only read mysql options from the given file
  --engines       -e  Do only tables whose storage engine is in this
                      comma-separated list
  --help              Show help and exit
  --host          -h  Connect to host
  --ignore-databases  Ignore this comma-separated list of databases
  --ignore-engines    Ignore this comma-separated list of storage engines
  --ignore-order      Ignore index order so KEY(a,b) duplicates KEY(b,a)
  --ignore-tables     Ignore this comma-separated list of tables
  --key-types         Check for duplicate f=foreign keys, k=keys or fk=both
                      (default fk)
  --password      -p  Password to use when connecting
  --port          -P  Port number to use for connection
  --set-vars          Set these MySQL variables (default wait_timeout=10000)
  --socket        -S  Socket file to use for connection
  --[no]sql           Print DROP KEY statement for each duplicate key (default
                      yes)
  --[no]summary       Print summary of indexes at end of output (default yes)
  --tables        -t  Check only this comma-separated list of tables
  --user          -u  User for login if not current user
  --verbose       -v  Output all keys and/or foreign keys found, not just
                      redundant ones
  --version           Show version and exit

Options and values after processing arguments:
  --all-structs       FALSE
  --ask-pass          FALSE
  --charset           (No value)
  --clustered         TRUE
  --config            /etc/maatkit/maatkit.conf,/etc/maatkit/mk-duplicate-key-checker.conf,
                         /root/.maatkit.conf,/root/.mk-duplicate-key-checker.conf
  --databases         (No value)
  --defaults-file     (No value)
  --engines           (No value)
  --help              TRUE
  --host              (No value)
  --ignore-databases
  --ignore-engines
  --ignore-order      FALSE
  --ignore-tables
  --key-types         fk
  --password          (No value)
  --port              (No value)
  --set-vars          wait_timeout=10000
  --socket            (No value)
  --sql               TRUE
  --summary           TRUE
  --tables            (No value)
  --user              (No value)
  --verbose           FALSE
  --version           FALSE