--- title: "创建索引 | OceanBase 文档中心" description: 创建索引 您可以通过 CREATE INDEX 语句为表创建索引。 背景信息 OceanBase 数据库支持在非分区表和分区表上创建索引，索引可以是局部索引或全局索引，也可以是唯一索引或普通索引。如果是分区表的唯一局部索引，则唯一局部索引必须包含表分区的拆分键。 本文主要介绍如何在非分区表上创建索引，分区表上索引的创… --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 4.0.0 社区版 # 创建索引 更新时间：2026-06-12 08:25:02 [编辑](https://github.com/oceanbase/oceanbase-doc/edit/V4.0.0/zh-CN/300.developer-guide/200.create-and-manage-database-objects/800.create-and-manage-indexes/200.create-an-index.md) 您可以通过 `CREATE INDEX` 语句为表创建索引。 ## 背景信息 OceanBase 数据库支持在非分区表和分区表上创建索引，索引可以是局部索引或全局索引，也可以是唯一索引或普通索引。如果是分区表的唯一局部索引，则唯一局部索引必须包含表分区的拆分键。 本文主要介绍如何在非分区表上创建索引，分区表上索引的创建请参见 [分区表的索引](https://www.oceanbase.com/docs/community-observer-cn-10000000000901500)。 ## 语法 MySQL 模式下，创建索引的 SQL 语法格式如下： ### 语法 1 通过 `CREATE TABLE` 创建索引。 ```sql CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name (table_definition_list) [table_option_list] [partition_option] [AS] select; CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name LIKE table_name; table_definition_list: table_definition [, table_definition ...] table_definition: column_definition | [CONSTRAINT [constraint_name]] PRIMARY KEY index_desc | [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} [index_name] index_desc | {INDEX | KEY} [index_name] index_desc column_definition_list: column_definition [, column_definition ...] column_definition: column_name data_type [DEFAULT const_value] [AUTO_INCREMENT] [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment index_desc: (column_desc_list) [index_type] [index_option_list] column_desc_list: column_desc [, column_desc ...] column_desc: column_name [(length)] [ASC | DESC] index_type: USING BTREE index_option_list: index_option [ index_option ...] index_option: [GLOBAL | LOCAL] | block_size | compression | STORING(column_name_list) | comment table_option_list: table_option [ table_option ...] table_option: [DEFAULT] {CHARSET | CHARACTER SET} [=] charset_name | [DEFAULT] COLLATE [=] collation_name | primary_zone | replica_num | table_tablegroup | block_size | compression | AUTO_INCREMENT [=] INT_VALUE | comment | DUPLICATE_SCOPE [=] "none|zone|region|cluster" | LOCALITY [=] "locality description" | ROW_FORMAT [=] REDUNDANT|COMPACT|DYNAMIC|COMPRESSED|DEFAULT | PCTFREE [=] num partition_option: PARTITION BY HASH(expression) [subpartition_option] PARTITIONS partition_count | PARTITION BY KEY([column_name_list]) [subpartition_option] PARTITIONS partition_count | PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)} [subpartition_option] (range_partition_list) | PARTITION BY LIST {(expression) | COLUMNS (column_name_list)} [subpartition_option] PARTITIONS partition_count subpartition_option: SUBPARTITION BY HASH(expression) SUBPARTITIONS subpartition_count | SUBPARTITION BY KEY(column_name_list) SUBPARTITIONS subpartition_count | SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name_list)} (range_subpartition_list) | SUBPARTITION BY LIST(expression) range_partition_list: range_partition [, range_partition ...] range_partition: PARTITION partition_name VALUES LESS THAN {(expression_list) | MAXVALUE} range_subpartition_list: range_subpartition [, range_subpartition ...] range_subpartition: SUBPARTITION subpartition_name VALUES LESS THAN {(expression_list) | MAXVALUE} expression_list: expression [, expression ...] column_name_list: column_name [, column_name ...] partition_name_list: partition_name [, partition_name ...] partition_count | subpartition_count: INT_VALUE ``` ### 语法 2 通过 `CREATE INDEX` 创建索引。 ```sql CREATE [UNIQUE] INDEX index_name ON table_name ( column_list ) [LOCAL | GLOBAL] [ PARTITION BY column_list PARTITIONS N ]; ``` MySQL 租户里，索引名称在表范围内不能重复，查看索引可以通过命令 `SHOW INDEX`。 ### 语法 3 在 MySQL 租户里，新增索引还有一种方法，其 SQL 语法格式如下： ```sql ALTER TABLE table_name ADD INDEX|KEY index_name ( column_list ); ``` 该语句可以一次增加多个索引，索引关键字用 INDEX 或 KEY 都可以。 ## 示例 ### 示例 1 1. 创建 `t_idx1` 表。 ```sql obclient> CREATE TABLE t_idx1( id int, name varchar(18), date date, PRIMARY KEY (id), index idx (date) ); ``` 2. 查看 `t_idx1` 表的索引。 ```sql 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 1. 创建 `t_idx2` 表。 ```sql obclient> CREATE TABLE t_idx2 (id int primary key, name varchar(10)); Query OK, 0 rows affected ``` 2. 在 `t_idx2` 表添加索引 `idx`。 ```sql obclient> CREATE INDEX idx ON t_idx2 (id, name); Query OK, 0 rows affected ``` 3. 查看 `t_idx2` 表的索引。 ```sql obclient> SHOW INDEX FROM t_idx2; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+ | t_idx2 | 0 | PRIMARY | 1 | id | A | NULL | NULL | NULL | | BTREE | available | | YES | | t_idx2 | 1 | idx | 1 | id | A | NULL | NULL | NULL | | BTREE | available | | YES | | t_idx2 | 1 | idx | 2 | name | A | NULL | NULL | NULL | YES | BTREE | available | | YES | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+ 3 rows in set ``` ### 示例 3 1. 创建 `t_idx3` 表。 ```sql CREATE TABLE t_idx3( id int, name varchar(18), date date, PRIMARY KEY (id) ); ``` 2. 在 `t_idx3` 表添加索引 `idx`。 ```sql obclient> ALTER TABLE t_idx3 ADD INDEX idx ( date ) ; Query OK, 0 rows affected ``` 3. 查看 `t_idx3` 表的索引。 ```sql obclient> SHOW INDEX FROM t_idx3; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+ | t_idx3 | 0 | PRIMARY | 1 | id | A | NULL | NULL | NULL | | BTREE | available | | YES | | t_idx3 | 1 | idx | 1 | date | A | NULL | NULL | NULL | YES | BTREE | available | | YES | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+ 2 rows 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) 咨询热线