MySQL管理工具MySQL Utilities — 优化索引（36）
亿网MySQL允许用户创建重复或冗余的索引。重复索引是没有优势的，在某些情况下，冗余的索引可能是有益的。当然啦，这两者都是有缺点的。重复和冗余索引会减慢更新和插入操作的。因此，找到并删除它们是比较好的。
执行此任务的利器是mysqlindexcheck，自动检查并生产更改语句。
实例
表结构如下所示：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | CREATE TABLE ` test_db ` . ` indexcheck_test ` ( ` emp_id ` INT ( 11 ) NOT NULL , ` fiscal_number ` int ( 11 ) NOT NULL , ` name ` VARCHAR ( 50 ) NOT NULL , ` surname ` VARCHAR ( 50 ) NOT NULL , ` job_title ` VARCHAR ( 20 ) , ` hire_date ` DATE default NULL , ` birthday ` DATE default NULL , PRIMARY KEY ( ` emp_id ` ) , KEY ` idx_fnumber ` ( ` fiscal_number ` ) , UNIQUE KEY ` idx_unifnumber ` ( ` fiscal_number ` ) , UNIQUE KEY ` idx_uemp_id ` ( ` emp_id ` ) , KEY ` idx_full_name ` ( ` name ` , ` surname ` ) , KEY ` idx_full_name_dup ` ( ` name ` , ` surname ` ) , KEY ` idx_name ` ( ` name ` ) , KEY ` idx_surname ` ( ` surname ` ) , KEY ` idx_reverse_name ` ( ` surname ` , ` name ` ) , KEY `ì dx_id_name ` ( ` emp_id ` , ` name ` ) , KEY ` idx_id_hdate ` ( ` emp_id ` , ` hire_date ` ) , KEY ` idx_id_bday ` ( ` emp_id ` , ` birthday ` ) ) ENGINE = InnoDB DEFAULT CHARSET = utf8 |
索引分析：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | shell > mysqlindexcheck — server = test_user @ trustauth.cn : 13010 test_db . indexcheck_test # Source on trustauth.cn: … connected. # The following indexes are duplicates or redundant for table test_db.indexcheck_test: # CREATE INDEX ` idx_uemp_id ` ON ` test_db ` . ` indexcheck_test ` ( ` emp_id ` ) USING BTREE # may be redundant or duplicate of: ALTER TABLE ` test_db ` . ` indexcheck_test ` ADD PRIMARY KEY ( ` emp_id ` ) # CREATE INDEX ` idx_fnumber ` ON ` test_db ` . ` indexcheck_test ` ( ` fiscal_number ` ) USING BTREE # may be redundant or duplicate of: CREATE INDEX ` idx_unifnumber ` ON ` test_db ` . ` indexcheck_test ` ( ` fiscal_number ` ) USING BTREE # CREATE INDEX ` idx_full_name_dup ` ON ` test_db ` . ` indexcheck_test ` ( ` name ` , ` surname ` ) USING BTREE # may be redundant or duplicate of: CREATE INDEX ` idx_full_name ` ON ` test_db ` . ` indexcheck_test ` ( ` name ` , ` surname ` ) USING BTREE # CREATE INDEX ` idx_name ` ON ` test_db ` . ` indexcheck_test ` ( ` name ` ) USING BTREE # may be redundant or duplicate of: CREATE INDEX ` idx_full_name ` ON ` test_db ` . ` indexcheck_test ` ( ` name ` , ` surname ` ) USING BTREE # CREATE INDEX ` idx_surname ` ON ` test_db ` . ` indexcheck_test ` ( ` surname ` ) USING BTREE # may be redundant or duplicate of: CREATE INDEX ` idx_reverse_name ` ON ` test_db ` . ` indexcheck_test ` ( ` surname ` , ` name ` ) USING BTREE # ALTER TABLE ` test_db ` . ` indexcheck_test ` ADD PRIMARY KEY ( ` emp_id ` ) # may be redundant or duplicate of: CREATE INDEX `ì dx_id_name ` ON ` test_db ` . ` indexcheck_test ` ( ` emp_id ` , ` name ` ) USING BTREE # CREATE INDEX ` idx_id_hdate ` ON ` test_db ` . ` indexcheck_test ` ( ` emp_id ` , ` hire_date ` ) USING BTREE # may be redundant or duplicate of: CREATE INDEX `ì dx_id_name ` ON ` test_db ` . ` indexcheck_test ` ( ` emp_id ` , ` name ` ) USING BTREE # CREATE INDEX ` idx_id_bday ` ON ` test_db ` . ` indexcheck_test ` ( ` emp_id ` , ` birthday ` ) USING BTREE # may be redundant or duplicate of: CREATE INDEX `ì dx_id_name ` ON ` test_db ` . ` indexcheck_test ` ( ` emp_id ` , ` name ` ) USING BTREE # The following indexes for table test_db.indexcheck_test contain the clustered index and # might be redundant: # CREATE INDEX ` idx_uemp_id ` ON ` test_db ` . ` indexcheck_test ` ( ` emp_id ` ) USING BTREE # CREATE INDEX `ì dx_id_name ` ON ` test_db ` . ` indexcheck_test ` ( ` emp_id ` , ` name ` ) USING BTREE # CREATE INDEX ` idx_id_hdate ` ON ` test_db ` . ` indexcheck_test ` ( ` emp_id ` , ` hire_date ` ) USING BTREE # CREATE INDEX ` idx_id_bday ` ON ` test_db ` . ` indexcheck_test ` ( ` emp_id ` , ` birthday ` ) USING BTREE |
权限
需要对mysql数据库的SELECT权限，以及被检查数据库表的SELECT权限。
小技巧
可以使用-d选项来生成删除索引的SQL 语句。
–stats可以单独使用或与–best、–worst选项一起使用，来显示对索引的统计信息。
使用–show-indexes选项来显示每个表的索引。
文章转载来自：trustauth.cn