测试主流数据库允许同一列(column)上创建重复索引？

by

之前在《有哪些技术可以减少PostgreSQL/openGauss数据库的存储空间？》记录过，在PG系的数据库上是支持同一列上创建多个索引，这种既浪费存储又增加了更新列时的额外的写代价，日常巡检需要即使发现并清理，下面再测试oracle,mysql（goldendb等），Gaussdb(opengauss系)，Kingbase（Postgresql系），oceanbase,达梦，崖山的情况。

oracle

$sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 16 08:10:58 2026
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create table tidx(id int);

Table created.

SQL>  create index idx1 on tidx(id);

Index created.

SQL> create index idx2 on tidx(id);
create index idx2 on tidx(id)
                          *
ERROR at line 1:
ORA-01408: such column list already indexed

Note: oracle 不允许，但是19c可以在同一列创建不同类型的索引如btree index,bitmap index

MySQL

mysql> use anbob
Database changed
mysql> create table tidx(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> create index idx1 on tidx(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx2 on tidx(id);
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show indexes from tidx;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tidx  |          1 | idx1     |            1 | id          | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tidx  |          1 | idx2     |            1 | id          | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Note: mysql 也允许，以MySQL为内核的GoldenDB, GreatDB,TDSQL等相同。

GaussDB

$ gsql
gsql ((GaussDB Kernel 505.2.0 build 82d715e8) compiled at 2024-09-20 00:15:22 commit 9967 last mr 19883 release)
NOTICE : The password has been expired, please change the password.
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

gauss=# create table tidx(id int);
CREATE TABLE
gauss=# create index idx1 on tidx(id);
CREATE INDEX
gauss=# create index idx2 on tidx(id);
CREATE INDEX

Note: GaussDB允许，同时基于opengauss的海量VBe等同样存在。

Kingbase

$ ksql -U system -d test
ksql (V8.0)
Type "help" for help.

test=# create table tidx(id int);
CREATE TABLE
test=# create index idx1 on tidx(id);
CREATE INDEX
test=# create index idx2 on tidx(id);
CREATE INDEX
test=#

Note: kingbase允许，同时基于postgresql的highgoDB, 海山等同样存在。

Oceanbase

$ sh connorcl.sh
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 276731
Server version: OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient(SYS@orcl)[SYS]> create table tidx(id int);
Query OK, 0 rows affected (2.866 sec)

obclient(SYS@orcl)[SYS]> create index idx1 on tidx(id);
Query OK, 0 rows affected (2.049 sec)

obclient(SYS@orcl)[SYS]> create index idx2 on tidx(id);
ORA-01408: such column list already indexed

Note: oceanbase不允许。

达梦

SQL> create table tidx(id int);
executed successfully
used time: 5.887(ms). Execute id is 58904.

SQL>  create index idx1 on tidx(id);
executed successfully
used time: 11.608(ms). Execute id is 58905.

SQL> create index idx2 on tidx(id);
create index idx2 on tidx(id);
[-3236]:Error in line: 1
such column list already indexed.
used time: 0.973(ms). Execute id is 0.

Note: 达梦不允许 .

Yashan数据库

$ yasql / as sysdba
YashanDB SQL Enterprise Edition Release 23.5.1.100 x86_64

Connected to:
YashanDB Server Enterprise Edition Release 23.5.1.100 x86_64 - Linux

SQL> create table tidx(id int);

Succeed.

SQL> create index idx1 on tidx(id);

Succeed.

SQL> create index idx2 on tidx(id);

YAS-02043 columns have been indexed

Note: 崖山数据库同样不允许。

Leave a Comment