创建索引 · k8s原理与实践

[TOC] # 创建索引本节主要介绍如何通过`CREATE INDEX`语句在非分区表上创建索引，分区表上索引的创建请参见[局部索引](https://open.oceanbase.com/docs/community/oceanbase-database/V3.1.0/local-index)章节。 OceanBase 数据库支持在非分区表和分区表上创建索引，索引可以是局部索引或全局索引，也可以是唯一索引或普通索引。如果是分区表的唯一索引，则唯一索引必须包含表分区的拆分键。 MySQL 模式下，创建索引的 SQL 语法格式如下： ~~~ CREATE [UNIQUE] INDEX index_name ON table_name ( column_list ) [LOCAL | GLOBAL] [ PARTITION BY column_list PARTITIONS N ] ; ~~~ MySQL 租户里，索引名称在表范围内不能重复，查看索引可以通过命令`SHOW INDEX`。在 MySQL 租户里，新增索引还有一种方法，其 SQL 语法格式如下： ~~~ ALTER TABLE table_name ADD INDEX|KEY index_name ( column_list ) ; ~~~ 该语句可以一次增加多个索引，索引关键字用`INDEX`或`KEY`都可以。示例：为非分区表创建普通索引。 ~~~ obclient> CREATE TABLE test (c1 int primary key, c2 VARCHAR(10)); Query OK, 0 rows affected (0.20 sec) obclient> CREATE INDEX idx ON test (c1, c2); Query OK, 0 rows affected (0.59 sec) 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 (0.00 sec) ~~~