---
title: "删除索引 | OceanBase 文档中心"
description: 删除索引 当索引过多时，维护开销会增大，您可以根据需要删除不必要的索引。 语法 MySQL 模式下，删除索引的语法格式如下： 语法 1 DROP INDEX index_name idx table_name; 参数解释 参数 描述 indexname 指定索引名称。 tblname 指定表名。 语法 2 ALTER…
image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 3.2.3 企业版 LTS

# 删除索引

更新时间：2023-07-18 17:25:58

当索引过多时，维护开销会增大，您可以根据需要删除不必要的索引。

## 语法

MySQL 模式下，删除索引的语法格式如下：

### 语法 1

```unknow
DROP INDEX index_name idx table_name;

```

#### 参数解释

| 参数 | 描述 |
| --- | --- |
| indexname | 指定索引名称。 |
| tblname | 指定表名。 |

### 语法 2

```unknow
ALTER TABLE table_name DROP key|index index_name ;

```

## 示例

### 示例 1

1. 查看 `test` 表的索引。

   ```sql
   obclient> SHOW INDEX FROM test;
   +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
   +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | test  |          0 | PRIMARY  |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
   | test  |          1 | idx      |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
   | test  |          1 | idx      |            2 | c2          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
   +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   3 rows in set

   ```
 2. 删除 `test` 表的索引 `idx` 。

   ```sql
   obclient> DROP INDEX idx on test;
   Query OK, 0 rows affected

   ```
 3. 查询 `test` 表的索引。

   ```sql
   obclient> SHOW INDEX FROM test;
   +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
   +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | test  |          0 | PRIMARY  |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
   +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   1 row in set

   ```

### 示例 2

1. 查看 `t_idx1` 表的索引。

   ```unknow
   obclient> SHOW INDEX FROM t_idx1;
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | t_idx1 |          0 | PRIMARY  |            1 | id          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
   | t_idx1 |          1 | idx      |            1 | date        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   2 rows in set

   ```
 2. 删除 `t_idx1` 表的索引。

   ```unknow
   obclient> ALTER TABLE t_idx1 DROP INDEX idx ;
   Query OK, 0 rows affected

   ```
 3. 再次查看 `t_idx1` 表的索引。

   ```unknow
   obclient> SHOW INDEX FROM t_idx1;
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | t_idx1 |          0 | PRIMARY  |            1 | id          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   1 row in set

   ```

 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线