介绍

在本文中，我们将了解在使用 Oracle、SQL Server、PostgreSQL 和 MySQL 时默认的数据库主索引、外键索引和唯一键索引策略是什么。

因此，如果你想知道在添加主键、外键或唯一键约束时这些顶级关系数据库中的一个是否会创建默认索引，那么本文将为你提供你一直在等待的答案.

数据库表

假设我们有以下post和post_comment形成一对多表关系的表：

父post表有两个键：

• id列上的主键约束
• slug列上的唯一键约束

子post_comment表也有两个键：

• id列上的主键约束
• post_id列上的外键约束

接下来，我们将看看排名前四的关系数据库系统各自选择的默认索引策略。

使用 Oracle 的默认数据库键索引

在 Oracle 上，post和post_comment表的创建方式如下：

CREATE TABLE post (
    id number(19,0) NOT NULL,
    slug varchar2(255 char),
    title varchar2(255 char),
    PRIMARY KEY (id)
)
 
CREATE TABLE post_comment (
    id number(19,0) NOT NULL,
    review varchar2(255 char),
    post_id number(19,0),
    PRIMARY KEY (id)
)
 
ALTER TABLE post
ADD CONSTRAINT UK_POST_SLUG
UNIQUE (slug)
 
ALTER TABLE post_comment
ADD CONSTRAINT FK_POST_COMMENT_POST_ID
FOREIGN KEY (post_id) REFERENCES post

要查看为这两个表创建了哪些数据库索引，我们可以使用以下 SQL 查询：

SELECT
    ind.table_name AS table_name,
    ind.index_name AS index_name,
    ind_col.column_name AS column_name,
    CASE
        WHEN ind.uniqueness = 'UNIQUE' THEN 1
        WHEN ind.uniqueness = 'NONUNIQUE' THEN 0
        END AS is_unique
FROM
    sys.all_indexes ind
INNER JOIN
    sys.all_ind_columns ind_col ON
        ind.owner = ind_col.index_owner AND
        ind.index_name = ind_col.index_name
WHERE
    lower(ind.table_name) IN ('post', 'post_comment')

运行上述查询时，我们将获得以下索引信息：

Oracle 为主键和唯一键列创建默认索引。

使用 Oracle 时，不会为外键列自动创建索引。

使用 SQL Server 的默认数据库键索引

使用 SQL Server 时，使用这些 DDL 语句创建表post和表：post_comment

CREATE TABLE post (
    id bigint NOT NULL,
    slug varchar(255),
    title varchar(255),
    PRIMARY KEY (id)
)
 
CREATE TABLE post_comment (
    id bigint not null,
    review varchar(255),
    post_id bigint,
    PRIMARY KEY (id)
)
 
ALTER TABLE post
ADD CONSTRAINT UK_POST_SLUG
UNIQUE (slug)
 
ALTER TABLE post_comment
ADD CONSTRAINT FK_POST_COMMENT_POST_ID
FOREIGN KEY (post_id) REFERENCES post

使用 SQL Server 时，你可以使用此 SQL 查询来检查哪些数据库索引与某些特定表相关联：

SELECT
    t.name AS table_name,
    ind.name AS index_name,
    col.name AS column_name,
    ind.is_unique AS is_unique
FROM
    sys.indexes ind
INNER JOIN
    sys.index_columns ic ON
        ind.object_id = ic.object_id AND
        ind.index_id = ic.index_id
INNER JOIN
    sys.columns col ON
        ic.object_id = col.object_id AND
        ic.column_id = col.column_id
INNER JOIN
    sys.tables t ON ind.object_id = t.object_id
WHERE
    t.name IN ('post', 'post_comment')

运行上述查询时，我们将获得以下索引信息：

SQL Server 为主键和唯一键列创建默认索引。

使用 SQL Server 时，不会为外键列自动创建索引。

使用 PostgreSQL 的默认数据库键索引

在 PostgreSQL 上，表post和post_comment表是这样创建的：

CREATE TABLE post (
    id int8 NOT NULL,
    slug varchar(255),
    title varchar(255), PRIMARY KEY (id)
)
 
CREATE TABLE post_comment (
    id int8 NOT NULL,
    review varchar(255),
    post_id int8,
    PRIMARY KEY (id)
)
 
ALTER TABLE IF EXISTS post
ADD CONSTRAINT UK_POST_SLUG
UNIQUE (slug)
 
ALTER TABLE IF EXISTS post_comment
ADD CONSTRAINT FK_POST_COMMENT_POST_ID
FOREIGN KEY (post_id) REFERENCES post

要验证 PostgreSQL 为这两个表创建的数据库索引，我们可以使用以下 SQL 查询：

SELECT
    c.relname AS table_name,
    i.relname AS index_name,
    a.attname AS column_name,
    ix.indisunique AS is_unique
FROM
    pg_class c
INNER JOIN
    pg_index ix ON c.oid = ix.indrelid
INNER JOIN
    pg_class i ON ix.indexrelid = i.oid
INNER JOIN
    pg_attribute a ON
        a.attrelid = c.oid AND
        a.attnum = ANY(ix.indkey)
WHERE
    c.relname IN ('post', 'post_comment')
ORDER BY
    c.relname

执行上述查询时，我们将得到如下索引结果集：

PostgreSQL 为主键和唯一键列创建默认索引。

使用 PostgreSQL 时，不会为外键列自动创建索引。

MySQL 的默认数据库键索引

使用 MySQL 时，使用这些 DDL 语句创建表post和表：post_comment

CREATE TABLE post (
    id bigint NOT NULL,
    slug varchar(255),
    title varchar(255),
    PRIMARY KEY (id)
)
 
CREATE TABLE post_comment (
    id bigint NOT NULL,
    review varchar(255),
    post_id bigint,
    PRIMARY KEY (id)
)
 
ALTER TABLE post ADD CONSTRAINT
UK_POST_SLUG
UNIQUE (slug)
 
ALTER TABLE post_comment
ADD CONSTRAINT FK_POST_COMMENT_POST_ID
FOREIGN KEY (post_id) REFERENCES post (id)

要查看 MySQL 默认创建的数据库索引，我们可以使用以下 SQL 查询：

SELECT
    TABLE_NAME as table_name,
    INDEX_NAME AS index_name,
    COLUMN_NAME as column_name,
    !NON_UNIQUE AS is_unique
FROM
    INFORMATION_SCHEMA.STATISTICS
WHERE
    TABLE_NAME IN ('post', 'post_comment')

执行上述查询时，我们将得到如下索引结果集：

MySQL 为所有主键、唯一键和外键列创建默认索引。

结论

在本文中，我们了解到 Oracle、SQL Server 和 PostgreSQL 为主键和唯一键列创建数据库索引，但不为外键列创建数据库索引。

因此，如果你有两个必须通过外键连接的大表，那么如果你在外键列上创建索引会更有效。否则，将改用表扫描。SQL 执行计划会告诉你数据库引擎使用了什么策略，因此你可以确定哪些外键列需要索引。

对于通过外键连接的小表，可能不需要在外键列上添加索引，因为数据库基于成本的优化器可能会进行表扫描。

MySQL 为所有主键、唯一键和外键列创建默认索引。