MySQL管理工具MySQL Utilities — 优化重复冗余索引(36)

默北 MySQLMySQL管理工具MySQL Utilities — 优化重复冗余索引(36)已关闭评论8,539字数 3054阅读10分10秒阅读模式

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/

weinxin
我的微信
微信公众号
扫一扫关注运维生存时间公众号,获取最新技术文章~
默北
  • 本文由 发表于 29/03/2015 01:00:14
  • 转载请务必保留本文链接:https://www.ttlsa.com/mysql/optimize-mysql-duplicate-redundant-indexes/