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>