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

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