MySQL允许用户创建重复或冗余的索引。重复索引是没有优势的，在某些情况下，冗余的索引可能是有益的。当然啦，这两者都是有缺点的。重复和冗余索引会减慢更新和插入操作的。因此，找到并删除它们是比较好的。
执行此任务的利器是mysqlindexcheck，自动检查并生产更改语句。文章源自运维生存时间-https://www.ttlsa.com/mysql/optimize-mysql-duplicate-redundant-indexes/
实例
表结构如下所示：文章源自运维生存时间-https://www.ttlsa.com/mysql/optimize-mysql-duplicate-redundant-indexes/
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
索引分析：文章源自运维生存时间-https://www.ttlsa.com/mysql/optimize-mysql-duplicate-redundant-indexes/
shell> mysqlindexcheck --server=test_user@localhost:13010 test_db.indexcheck_test # Source on localhost: ... 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权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/optimize-mysql-duplicate-redundant-indexes/
小技巧
可以使用-d选项来生成删除索引的SQL 语句。文章源自运维生存时间-https://www.ttlsa.com/mysql/optimize-mysql-duplicate-redundant-indexes/
--stats可以单独使用或与--best、--worst选项一起使用，来显示对索引的统计信息。文章源自运维生存时间-https://www.ttlsa.com/mysql/optimize-mysql-duplicate-redundant-indexes/
使用--show-indexes选项来显示每个表的索引。文章源自运维生存时间-https://www.ttlsa.com/mysql/optimize-mysql-duplicate-redundant-indexes/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/optimize-mysql-duplicate-redundant-indexes/
评论