怎么查找MySQL中的重复索引和无用索引，并且安全地drop index删除索引？

最新推荐文章于 2024-07-21 14:43:17 发布

Shuo_W

最新推荐文章于 2024-07-21 14:43:17 发布

阅读量917

点赞数

分类专栏： MySQL 索引文章标签： MySQL 无用索引

本文链接：https://blog.youkuaiyun.com/woson_wang/article/details/90904081

版权

MySQL 同时被 2 个专栏收录

14 篇文章

订阅专栏

索引

3 篇文章

订阅专栏

本文已链接到：
http://codercoder.cn/index.php/2019/09/mysql-drop-index-safely/

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

Shuo_W

关注关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
分享

复制链接

分享到 QQ

分享到新浪微博

扫一扫
举报

举报

专栏目录

检查并删除mysql中多余的索引

LUCKtou的博客

07-13

646

接手了一个无人管理的mysql数据库，发现有的表建了巨量索引（单表40个索引）；需要对整个数据库做一个索引使用情况的检测，并按照需求删除不用的索引，写了下面的sql用于排查和生成drop index的sql；注：mysql版本5.7.31 SELECT TAB.TABLE_SCHEMA, TAB.TABLE_NAME, IND.INDEX_NAME, TAB.INDEX_TYPE, TAB.NUM AS INDEX_NUM, #单表中索引总数 IND.COUNT_STAR AS

MySQL 处理重复数据

froginwe11的博客

02-14

348

重复数据指的是在数据库表中，存在多个完全相同的记录。这些记录可能在某些字段上完全一致，而在其他字段上有所不同。数据录入错误数据同步问题数据迁移过程中的错误数据库设计不合理MySQL数据库中处理重复数据是一个重要的任务。通过使用唯一索引、触发器、临时表等方法，可以有效地识别和删除重复数据。同时，定期检查、数据清洗和合理的数据库设计也是防止重复数据产生的重要手段。在实际应用中，应根据具体情况选择合适的方法和技巧。

参与评论您还未登录，请先登录后发表或查看评论

找出mysql中无用的索引

架构师的成长之路

07-13

401

select t.TABLE_SCHEMA ,t.TABLE_NAME ,s.INDEX_NAME ,s.COLUMN_NAME ,s.SEQ_IN_INDEX ,( SELECT MAX(SEQ_IN_INDEX) FROM INFORMATION_SCHEMA.STATISTICS s2 WHERE s.TABLE_SCHEMA = s2...

查看mysql数据库表上的重复索引列

weixin_34262482的博客

10-14

170

有二种方法1. 借助pt-duplicate-key-checker，这是percona toolkit中的一个小工具pt-duplicate-key-checker --host=localhost --user=xxx--password=xxx--database=xxx以报表形式显示有重复索引的表，以及给出建议的删除语句2. 利用information_schema....

mysql 检查重复索引

awen20092009的博客

10-08

334

WITH X1 AS ( SELECT DISTINCT A.TABLE_SCHEMA, TABLE_NAME,index_name, GROUP_CONCAT(DISTINCT COLUMN_NAME ) C FROM INFORMATION_SCHEMA.STATISTICS A group by TABLE_NAME,index_name ), X2 AS ( SELECT C, TABLE_SCHEMA,TABLE_NAME,COUNT(0) COUNT1...

MySQL查找重复索引和冗余索引

likeflower950的博客

06-30

3284

索引的创建对数据库的查询性能有很好的优化，但是不恰当的索引使用也会给数据库带来性能上的影响。SQL索引创建需要注意的情况重复索引：一个字段上建立多个索引，因为见过在主键上建立唯一索引的这种二笔写法，深深地震惊了我。冗余索引：指多个索引的前缀列相同，或者在联合索引中包含了主键的索引。现在我们用SQL来查找一个数据库的重复索引和冗余索引SELECT a.TABLE_SCHEMA, a.TAB

MySQL索引的维护与优化——查找重复及冗余索引

weixin_33670713的博客

09-30

431

方法一：通过MySQL的information_schema数据库查找重复与冗余索引 SELECT a.table_schema AS '数据库', a.table_name AS '表名', a.index_name AS '索引1', b.index_name AS '索引2', a.column_name AS '重复列名' FROM information_schema.stat...

mysql索引能重复吗_mysql重复索引与冗余索引实例分析

weixin_32373123的博客

01-18

569

本文实例讲述了mysql重复索引与冗余索引。分享给大家供大家参考，具体如下：重复索引：表示一个列或者顺序相同的几个列上建立的多个索引。冗余索引：两个索引所覆盖的列重叠冗余索引在一些特殊的场景下使用到了索引覆盖，所以比较快。场景比如文章与标签表+——+——-+——+| id | artid | tag |+——+——-+——+| 1 | 1 | PHP || 2 | 1 | Linux || 3 |...

MySQL -- 索引

Afison的博客

06-04

1420

当表中的数据量过大时，SQL查询所花费的时间会很长，导致业务超时出错，此时就需要用索引来加速SQL查询。

MySQL高级-索引优化（超详细）

SIESTA030的博客

02-14

6846

性能分析 MySQL Query Optimizer Mysql中由专门负责优化SELECT语句的优化器，主要功能就是通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的，但**不一定是DBA觉得最优的，这部分最耗时间**）。当客户端向MySQL请求一条Query的时候，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MQO会对整条Query进行优化，处理掉一些常量表达式的预算，将其换算成常量值并对，Que

MySQL面试篇章——MySQL索引

itzzan的博客

07-21

1378

当表中的数据量到达几十万甚至上百万的时候，SQL查询所花费的时间会很长，导致业务超时出错，此时就需要用索引来加速SQL查询。由于索引也是需要存储成索引文件的，因此对索引的使用也会涉及到磁盘I/O操作。如果索引创建过多，使用不当，会造成SQL查询时，进行大量无用的磁盘I/O操作，降低了SQL的查询效率，适得其反，因此需要掌握良好的索引创建原则！

MySQL视图与索引

kissship的博客

07-09

2280

视图是数据库中的一种虚拟表，它由查询语句生成，根据特定的条件从一个或多个基本表中检索数据。视图可以根据需要选择性地显示表的某些列，并且可以根据查询语句的定义对数据进行过滤、排序、聚合等操作。视图并不实际存储数据，而是在访问时动态地从基本表中检索数据。通过创建视图，可以简化复杂的查询操作，并提供更方便、更易读的数据访问方式。视图还可以用于隐藏敏感的数据，保护隐私。视图的定义包括视图名称、所涉及的基本表、查询语句以及可选的列别名等。

MySQL删除重复和冗余的索引

wade1010的专栏

11-27

347

mysql

MySQL查看哪些索引未使用

weixin_33884611的博客

01-26

1765

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME FROM performance_schema.table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY ...

mysql 删除重复索引_MySQL-删除重复项

weixin_28927031的博客

01-27

227

我有一个带有唯一索引的条形码列的表.为了防止重复,在每个条形码的末尾已为数据加载了额外的字符(-xx),但是一旦我删除后缀,就会有很多重复.这是数据示例：itemnumber barcode17912 2-1418082 2-121870 2-1029219 2-8然后,我创建了两个临时表,分别是marty和manny,均带有itemnumber和...

mysql删除重复的数据库_MySQL数据库中删除重复记录的方法总结[推荐]

weixin_39902345的博客

01-18

104

MySQL删除重复的索引

weixin_30756499的博客

12-01

278

冗余和重复索引　　mysql允许在相同列上创建多个索引，无论是有意还是无意，mysql需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。重复索引是指的在相同的列上按照相同的顺序创建的相同类型的索引，应该避免这样创建重复所以，发现以后也应该立即删除。但，在相同的列上创建不同类型的索引来满足不同的查询需求是可以的。　　冗余索引和重复索引有一些不同，...

去掉mysql重复的索引

weixin_33834137的博客

06-17

140

CREATE TABLE t ( id INT NOT NULL PRIMARY KEY, a INT NOT NULL, INDEX(ID))ENGINE=InnoDB;一个经验不足的用户可能是想创建一个主键，然后再加上索引以供查询使用。事实上主键也就是索引了。所以完全没必要再添加INDEX(ID)了。（可以指定整个库，也可以指定单个表） [root@no...

mysql 删除重复索引pt-duplicate-key-checker

xxpxxpoo8的专栏

12-15

213

pt-duplicate-key-checker

mysql删除索引

最新发布

04-01

<think>好的，用户现在想了解如何在MySQL中删除索引。我需要先回顾之前的对话，确保回答的一致性。用户之前问过关于SQL优化的问题，涉及索引的使用，现在转向索引的删除，可能是在优化过程中调整或删除不必要的索引。首先，我需要确定用户对MySQL索引的基本了解程度。他们可能已经知道索引的作用，但需要具体操作步骤。用户可能遇到了性能问题，或者想清理无用索引，所以需要正确删除索引的方法。接下来，我需要详细说明删除索引的不同情况，比如删除普通索引、唯一索引、主键和复合索引。要区分不同的索引类型，因为删除语法可能不同。例如，删除主键需要使用ALTER TABLE语句，而普通索引可以用DROP INDEX。还要注意用户可能存在的误区，比如是否知道必须指定索引名，或者如何处理复合索引。需要提醒他们使用SHOW INDEX命令来查看现有索引，确保删除正确的索引，避免误操作。另外，用户可能关心删除索引的影响，比如对性能的影响。虽然问题中没有提到，但作为补充信息，可以简要提到删除索引可能提高写操作性能，但可能降低查询速度，需要权衡。最后，确保回答结构清晰，步骤明确，示例正确。使用代码块展示SQL语句，帮助用户直接复制使用。同时，强调注意事项，比如备份和确认索引名，避免生产环境中的问题。</think>### MySQL 删除索引的完整指南 --- #### 一、删除索引的核心语法 MySQL 删除索引的语法根据索引类型略有不同，以下是常见场景： ##### 1. **删除普通索引** ```sql -- 方式1: DROP INDEX 命令 DROP INDEX 索引名 ON 表名; -- 方式2: ALTER TABLE 命令 ALTER TABLE 表名 DROP INDEX 索引名; ``` ##### 2. **删除唯一索引** ```sql -- 语法与普通索引相同，但需注意唯一约束 DROP INDEX 索引名 ON 表名; ``` ##### 3. **删除主键索引** ```sql ALTER TABLE 表名 DROP PRIMARY KEY; ``` ##### 4. **删除复合索引** ```sql -- 直接删除索引名（无论索引是否复合） DROP INDEX 索引名 ON 表名; ``` --- #### 二、操作步骤详解（以 `users` 表为例） ##### 1. **确认索引信息** 删除前需明确索引名称和类型： ```sql SHOW INDEX FROM users; ``` 输出示例： | Table | Key_name | Column_name | Index_type | Non_unique | |--------|-------------|-------------|------------|------------| | users | PRIMARY | id | BTREE | 0 | | users | idx_email | email | BTREE | 1 | | users | idx_age | age | BTREE | 1 | ##### 2. **删除普通索引** 删除 `idx_email` 索引： ```sql DROP INDEX idx_email ON users; -- 或 ALTER TABLE users DROP INDEX idx_email; ``` ##### 3. **删除主键索引** ```sql ALTER TABLE users DROP PRIMARY KEY; ``` **注意**： - 若表无显式主键但存在隐式主键（如 InnoDB 的 `DB_ROW_ID`），此操作会报错。 - 删除主键前需确保有另一个唯一索引可替代（否则某些存储引擎会报错）。 ##### 4. **删除唯一索引** 假设存在唯一索引 `uniq_username`： ```sql DROP INDEX uniq_username ON users; ``` --- #### 三、关键注意事项 1. **索引名必须存在** 若索引不存在会报错：`ERROR 1091 (42000): Can't DROP 'xxx'; check that column/key exists` 2. **复合索引需整体删除** 复合索引（如 `INDEX (col1, col2)`）无法单独删除某一列，必须整体删除。 3. **主键的特殊性** - 主键索引名固定为 `PRIMARY`，不可自定义。 - 删除主键后，表的聚簇索引结构将发生变化（InnoDB 会重建表）。 4. **外键依赖** 若索引被外键约束引用，需先删除外键约束： ```sql -- 1. 查询外键约束名 SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '子表名' AND REFERENCED_TABLE_NAME = '父表名'; -- 2. 删除外键约束 ALTER TABLE 子表名 DROP FOREIGN KEY 外键名; ``` --- #### 四、常见问题解决 ##### 场景1：误删索引如何恢复？ **答案**：索引无法直接恢复，需手动重建： ```sql -- 重建普通索引 ALTER TABLE 表名 ADD INDEX 索引名 (字段); -- 重建唯一索引 ALTER TABLE 表名 ADD UNIQUE (字段); -- 重建主键 ALTER TABLE 表名 ADD PRIMARY KEY (字段); ``` ##### 场景2：删除索引后性能下降？ **分析**：删除索引可能影响查询性能（尤其是高频查询依赖该索引时）。 **解决方案**： - 通过慢查询日志定位问题 SQL。 - 使用 `EXPLAIN` 分析执行计划。 - 必要时重建索引。 --- #### 五、最佳实践建议 | 场景 | 操作建议 | |--------------------------|-------------------------------------------------------------------------| | **删除冗余索引** | 定期使用 `SHOW INDEX` 检查重复或长期未使用的索引 | | **大表删除索引** | 在业务低峰期操作，避免锁表影响业务（InnoDB 支持 Online DDL） | | **生产环境操作** | 先备份表结构（`SHOW CREATE TABLE`），或使用 `pt-online-schema-change` 工具 | --- #### 六、示例全流程演示假设需要删除 `orders` 表的 `idx_status` 索引： ```sql -- 1. 查看索引 SHOW INDEX FROM orders; -- 2. 删除索引 DROP INDEX idx_status ON orders; -- 3. 验证删除结果 SHOW INDEX FROM orders; -- idx_status 应消失 ``` --- ### 总结删除索引是 MySQL 表结构优化的常规操作，但需谨慎执行： 1. 确保索引确实冗余或无用。 2. 优先删除低选择性（重复值多）的索引。 3. 关注删除后的查询性能变化。