首页 > 数据库 >重复索引检查mysql pt-duplicate-key-checker

重复索引检查mysql pt-duplicate-key-checker

时间：2023-09-02 22:02:35浏览次数：57  
标签：pt col2 duplicate col1 checker sbtest KEY TABLE sbtest1

索引对于优化数据库中的查询执行时间至关重要，但是索引数量过多或冗余会对性能产生负面影响。虽然pt-duplicate-key-checker是识别MySQL中重复或冗余索引的首选工具，但它可能无法捕获所有重复。

测试环境，mysql5.7.27

建表结

mysql [localhost:5727] {root} (sbtest) > CREATE TABLE `sbtest1` (
    -> `id` int NOT NULL,
    -> `col1` varchar(1) DEFAULT NULL,
    -> `col2` varchar(2) DEFAULT NULL,
    -> `col3` varchar(3) DEFAULT NULL,
    -> `col4` varchar(4) DEFAULT NULL,
    -> PRIMARY KEY (`id`),
    -> UNIQUE KEY `id` (`id`),
    -> UNIQUE KEY `col1` (`col1`,`col2`),
    -> UNIQUE KEY `col2` (`col2`,`col1`),
    -> UNIQUE KEY `col1_2` (`col1`,`col2`),
    -> UNIQUE KEY `col1_3` (`col1`,`col2`,`col3`),
    -> UNIQUE KEY `col1_4` (`col1`),
    -> UNIQUE KEY `col1_5` (`col1`),
    -> KEY `idx1` (`col1`,`id`),
    -> KEY `idx2` (`col1`,`col2`),
    -> KEY `idx3` (`col2`,`col1`),
    -> KEY `idx4` (`col1`,`col2`,`col3`),
    -> KEY `idx5` (`col1`,`col2`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected, 3 warnings (0.02 sec)

运行pt-duplicate-key-checker工具，多余的索引会给出相应的解决方案

[root@testdb ~]# pt-duplicate-key-checker --user=root --password=msandbox  --port=5727 --socket=/tmp/mysql_sandbox5727.sock --databases sbtest --tables sbtest1
# ########################################################################
# sbtest.sbtest1
# ########################################################################
 
# Uniqueness of id ignored because PRIMARY is a duplicate constraint
# id is a duplicate of PRIMARY
# Key definitions:
#   UNIQUE KEY `id` (`id`),
#   PRIMARY KEY (`id`),
# Column types:
#         `id` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `id`;
 
# Uniqueness of col1_4 ignored because col1_5 is a duplicate constraint
# col1_4 is a duplicate of col1_5
# Key definitions:
#   UNIQUE KEY `col1_4` (`col1`),
#   UNIQUE KEY `col1_5` (`col1`),
# Column types:
#         `col1` varchar(1) default null
# To remove this duplicate index, execute:
ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `col1_4`;
 
# idx3 is a duplicate of col2
# Key definitions:
#   KEY `idx3` (`col2`,`col1`),
#   UNIQUE KEY `col2` (`col2`,`col1`),
# Column types:
#         `col2` varchar(2) default null
#         `col1` varchar(1) default null
# To remove this duplicate index, execute:
ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `idx3`;
 
# idx4 is a duplicate of col1_3
# Key definitions:
#   KEY `idx4` (`col1`,`col2`,`col3`),
#   UNIQUE KEY `col1_3` (`col1`,`col2`,`col3`),
# Column types:
#         `col1` varchar(1) default null
#         `col2` varchar(2) default null
#         `col3` varchar(3) default null
# To remove this duplicate index, execute:
ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `idx4`;
 
# Uniqueness of col1 ignored because col1_5 is a stronger constraint
# col1 is a left-prefix of col1_3
# Key definitions:
#   UNIQUE KEY `col1` (`col1`,`col2`),
#   UNIQUE KEY `col1_3` (`col1`,`col2`,`col3`),
# Column types:
#         `col1` varchar(1) default null
#         `col2` varchar(2) default null
#         `col3` varchar(3) default null
# To remove this duplicate index, execute:
ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `col1`;
 
# Uniqueness of col1_2 ignored because col1_5 is a stronger constraint
# col1_2 is a left-prefix of col1_3
# Key definitions:
#   UNIQUE KEY `col1_2` (`col1`,`col2`),
#   UNIQUE KEY `col1_3` (`col1`,`col2`,`col3`),
# Column types:
#         `col1` varchar(1) default null
#         `col2` varchar(2) default null
#         `col3` varchar(3) default null
# To remove this duplicate index, execute:
ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `col1_2`;
 
# idx2 is a left-prefix of col1_3
# Key definitions:
#   KEY `idx2` (`col1`,`col2`),
#   UNIQUE KEY `col1_3` (`col1`,`col2`,`col3`),
# Column types:
#         `col1` varchar(1) default null
#         `col2` varchar(2) default null
#         `col3` varchar(3) default null
# To remove this duplicate index, execute:
ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `idx2`;
 
# idx5 is a left-prefix of col1_3
# Key definitions:
#   KEY `idx5` (`col1`,`col2`)
#   UNIQUE KEY `col1_3` (`col1`,`col2`,`col3`),
# Column types:
#         `col1` varchar(1) default null
#         `col2` varchar(2) default null
#         `col3` varchar(3) default null
# To remove this duplicate index, execute:
ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `idx5`;
 
# Key idx1 ends with a prefix of the clustered index
# Key definitions:
#   KEY `idx1` (`col1`,`id`),
#   PRIMARY KEY (`id`),
# Column types:
#         `col1` varchar(1) default null
#         `id` int(11) not null
# To shorten this duplicate clustered index, execute:
ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `idx1`, ADD INDEX `idx1` (`col1`);
 
# ########################################################################
# Summary of indexes
# ########################################################################
 
# Size Duplicate Indexes   76
# Total Duplicate Indexes  9
# Total Indexes            13

再执行pt-duplicate-key-checker给出的解决方案

mysql [localhost:5727] {root} (sbtest) > ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `id`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost:5727] {root} (sbtest) > ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `col1_4`;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost:5727] {root} (sbtest) > ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `idx3`;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost:5727] {root} (sbtest) > ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `idx4`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost:5727] {root} (sbtest) > ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `col1`;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost:5727] {root} (sbtest) > ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `col1_2`;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost:5727] {root} (sbtest) > ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `idx2`;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost:5727] {root} (sbtest) > ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `idx5`;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost:5727] {root} (sbtest) > ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `idx1`, ADD INDEX `idx1` (`col1`);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

检查表结构：

mysql [localhost:5727] {root} (sbtest) > show create table `sbtest`.`sbtest1`\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL,
  `col1` varchar(1) DEFAULT NULL,
  `col2` varchar(2) DEFAULT NULL,
  `col3` varchar(3) DEFAULT NULL,
  `col4` varchar(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col2` (`col2`,`col1`),
  UNIQUE KEY `col1_3` (`col1`,`col2`,`col3`),
  UNIQUE KEY `col1_5` (`col1`),
  KEY `idx1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

 检查发现没有多余的索引。 

再对表加一个唯一索引

mysql [localhost:5727] {root} (sbtest) > alter table sbtest1 add UNIQUE KEY col2_1(col1,col2);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql [localhost:5727] {root} (sbtest) > alter table sbtest1 drop index col1_3;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql [localhost:5727] {root} (sbtest) > show create table `sbtest`.`sbtest1`\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL,
  `col1` varchar(1) DEFAULT NULL,
  `col2` varchar(2) DEFAULT NULL,
  `col3` varchar(3) DEFAULT NULL,
  `col4` varchar(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col2` (`col2`,`col1`),
  UNIQUE KEY `col1_5` (`col1`),
  UNIQUE KEY `col2_1` (`col1`,`col2`),
  KEY `idx1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

这里人为可以看出索引col2和 col2_1是重复的，再用 pt-duplicate-key-checker工具检查看看看能不能查出来

[root@testdb ~]# pt-duplicate-key-checker --user=root --password=msandbox  --port=5727 --socket=/tmp/mysql_sandbox5727.sock --databases sbtest --tables sbtest1
# ########################################################################
# sbtest.sbtest1
# ########################################################################
 
# idx1 is a duplicate of col1_5
# Key definitions:
#   KEY `idx1` (`col1`)
#   UNIQUE KEY `col1_5` (`col1`),
# Column types:
#         `col1` varchar(1) default null
# To remove this duplicate index, execute:
ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `idx1`;
 
# ########################################################################
# Summary of indexes
# ########################################################################
 
# Size Duplicate Indexes   4
# Total Duplicate Indexes  1
# Total Indexes            5

检查发出是索引IDX1有重复，并没有检查出col2和col2_1重复。当这种重复索引检查不出时，只能通过自定义SQL来检查。

mysql [localhost:5727] {root} (sbtest) > SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME, group_concat(INDEX_NAME) duplic8_UK, COLUMN_NAMES FROM
    ->  (SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME SEPARATOR ',') AS COLUMN_NAMES
    ->  FROM information_schema.STATISTICS WHERE NON_UNIQUE = 0 AND INDEX_NAME!='PRIMARY' AND INDEX_TYPE = 'BTREE'
    ->  GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) X group by TABLE_SCHEMA, TABLE_NAME, COLUMN_NAMES having count(*)> 1;
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | duplic8_UK  | COLUMN_NAMES |
+--------------+------------+-------------+--------------+
| sbtest       | sbtest1    | col2,col2_1 | col1,col2    |
+--------------+------------+-------------+--------------+
1 row in set (0.09 sec)

 从自定义的SQL脚本可以看到，索引col2和col2_1是重复的。

 当pt工具检查索引不全时，可以用自定义SQL脚本检查。

标签：pt,col2,duplicate,col1,checker,sbtest,KEY,TABLE,sbtest1
From： https://blog.51cto.com/u_13482808/7335026

相关文章

  • 无涯教程-JavaScript - LOGINV函数
    LOGINV函数替代Excel2010中的LOGNORM.INV函数。描述该函数返回x的对数正态累积分布函数的逆函数,其中ln(x)的分布通常带有参数mean和standard_dev。如果p=LOGNORMDIST(x,...),则LOGINV(p,...)=x使用对数正态分布来分析对数转换的数据。语法LOGINV(probability,mean,s......
  • [Typescript] DistributiveOmit
    OmitonUniontypetypeUnion=|{a:"a";user?:string;}|{b:"b";user?:string;};typeX=Omit<Union,"user">;//Xis{} UsingDistributiveOmit:typeDistributiveOmit<......
  • ?ChatGPT指南：六个最佳提示词使用技巧
    大家好，我是星辰编程理财。很高兴能够与大家分享关于ChatGPT的最佳实践。作为一个热爱编程的技术爱好者，我曾经历了在学习和使用ChatGPT时遇到的一些挑战和困惑。然而，通过不断的探索和实践，我逐渐领悟到一些宝贵的经验和技巧。在这篇文章中，我想和大家聊聊指令的重要性。我还记得刚开始......
  • 无涯教程-JavaScript - HYPGEOMDIST函数
    HYPGEOMDIST函数替代Excel2010中的HYPGEOM.DIST函数。描述该函数返回超几何分布。HYPGEOMDIST返回给定样本数量,给定样本数量,总体成功率和总体数量的概率。将HYPGEOMDIST用于具有有限总体的问题,其中每个观察输出都是成功或失败,并且给定大小的每个子集的选择可能性均等。......
  • 学习JavaScript的路径
    学习JavaScript的路径可以按照以下步骤进行：了解基本概念：首先学习JavaScript的基本概念，包括变量、数据类型、运算符、数组、对象、循环和条件语句等。可以通过阅读相关的教材、在线课程或者参考W3Schools和MDN文档等来学习。学习控制DOM元素：学习如何使用JavaScript控制DOM元素，包......
  • 文心一言 VS 讯飞星火 VS chatgpt （85）-- 算法导论8.2 2题
    二、用go语言，试证明COUNTING-SORT是稳定的。文心一言：要证明COUNTING-SORT是稳定的，我们需要证明在排序过程中，具有相同值的元素在排序后仍保持其原始的相对顺序。COUNTING-SORT是一种基于计数的排序算法，其核心思想是利用计数数组记录待排序元素的数量。下面是COUNTING-SORT的......
  • 无涯教程-JavaScript - GAMMAINV函数
    GAMMAINV函数取代了Excel2010中的GAMMA.INV函数。描述该函数返回伽马累积分布的倒数。如果p=GAMMADIST(x,...),则GAMMAINV(p,...)=x您可以使用此函数来研究变量的分布可能偏斜的变量。语法GAMMAINV(probability,alpha,beta)争论Argument描述Required/OptionalP......
  • 文心一言 VS 讯飞星火 VS chatgpt （85）-- 算法导论8.2 2题
    二、用go语言，试证明COUNTING-SORT是稳定的。文心一言：要证明COUNTING-SORT是稳定的，我们需要证明在排序过程中，具有相同值的元素在排序后仍保持其原始的相对顺序。COUNTING-SORT是一种基于计数的排序算法，其核心思想是利用计数数组记录待排序元素的数量。下面是COUNTING-SORT......
  • 无涯教程-JavaScript - GAMMADIST函数
    GAMMADIST函数取代了Excel2010中的GAMMA.DIST函数。描述该函数返回伽马分布。您可以使用此功能来研究可能具有偏斜分布的变量。伽马分布通常用于排队分析。语法GAMMADIST(x,alpha,beta,cumulative)争论Argument描述Required/OptionalXThevalueatwhichyouwantt......
  • Lnton 羚通视频分析算法平台【OpenCV-Python】教程： Depth Map from Stereo Images 立
    立体图像的深度图（DepthMap）是表示图像中每个像素点距离相机的深度或距离信息的二维灰度图。它提供了场景中物体的三维结构和距离信息，对于三维重建、增强现实、虚拟现实等应用具有重要意义。深度图可以通过以下方式获取：1.双目视觉方法：使用两个相机（左右摄像机）同时拍摄同一场景，通过计......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99