MySQL Utilities (mysqlindexcheck)

by

MySQL Utilities (mysqlindexcheck)
Reference: http://thinkit.co.jp/story/2014/02/10/4814
mysqlindexcheck check for duplicate or redundant indexes
不要なIndexを確認して、削除してくれるスクリプトも準備してくれる。

mysqluc> mysqlindexcheck --help
MySQL Utilities mysqlindexcheck.exe version 1.4.3 (part of MySQL Workbench Distribution 6.0.0)
License type: GPLv2
Usage: mysqlindexcheck.exe --server=user:pass@host:port:socket db1.table1 db2 db3.table2

mysqlindexcheck - check for duplicate or redundant indexes

Options:
  --version             show program's version number and exit
  --help                display a help message and exit
  --license             display program's license and exit
  --server=SERVER       connection information for the server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>].
  -d, --show-drops      display DROP statements for dropping indexes
  -i, --show-indexes    display indexes for each table
  -s, --skip            skip tables that do not exist
  -f FORMAT, --format=FORMAT
                        display the list of indexes per table in either sql,
                        grid (default), tab, csv, or vertical format
  --stats               show index performance statistics
  --best=BEST           limit index statistics to the best N indexes
  --worst=WORST         limit index statistics to the worst N indexes
  -r, --report-indexes  reports if a table has neither UNIQUE indexes nor a
                        PRIMARY key
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug

mysqluc>

■既存テーブルの状況確認

mysql> show create table language\G
*************************** 1. row ***************************
       Table: language
Create Table: CREATE TABLE `language` (
  `language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`language_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

■意図的に不要なIndexを作成してみる

mysql> create index index_1 on test.language(language_id) using btree;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

■Indexが出来ている事を確認

mysql> show create table language\G
*************************** 1. row ***************************
       Table: language
Create Table: CREATE TABLE `language` (
  `language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`language_id`),
  KEY `index_1` (`language_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

■mysqlindexcheckで確認してみる

mysqluc> mysqlindexcheck --server=root:password@localhost:3306 -d test.language;
# Source on localhost: ... connected.
# The following index is a duplicate or redundant for table test.language;:
#
CREATE INDEX `index_1` ON `test`.`language` (`language_id`) USING BTREE
#     may be redundant or duplicate of:
ALTER TABLE `test`.`language` ADD PRIMARY KEY (`language_id`)
#
# DROP statement:
#
ALTER TABLE `test`.`language` DROP INDEX `index_1`;
#
# The following index for table test.language; contains the clustered index and might be redundant:
#
CREATE INDEX `index_1` ON `test`.`language` (`language_id`) USING BTREE
#
# DROP/ADD statement:
#
ALTER TABLE `test`.`language` DROP INDEX `index_1`;
#

mysqluc>

■不要なインデックスがある事が確認出来たので、不要なインデックスを削除する。

mysql> ALTER TABLE `test`.`language` DROP INDEX `index_1`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table language\G
*************************** 1. row ***************************
       Table: language
Create Table: CREATE TABLE `language` (
  `language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`language_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

■再度、mysqlindexcheckにて確認すると不要なインデックスが無くなっている事が確認出来た。

mysqluc> mysqlindexcheck --server=root:password@localhost:3306 -d test.language;
# Source on localhost: ... connected.

mysqluc>

余談：Indexの追加、削除はもちろんAlter tableでもCreate Index, Drop IndexでもOKです。

mysql> create index index_1 on test.language(language_id) using btree;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table language\G
*************************** 1. row ***************************
       Table: language
Create Table: CREATE TABLE `language` (
  `language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`language_id`),
  KEY `index_1` (`language_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> drop index index_1 on language;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table language\G
*************************** 1. row ***************************
       Table: language
Create Table: CREATE TABLE `language` (
  `language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`language_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>