非官方测试版翻译

本页面由 PageTurner AI 翻译（测试版）。未经项目官方认可。 发现错误？ 报告问题 →

使用索引

索引是一种数据结构，它使得数据库无需扫描整张表就能定位到特定行。没有索引时，查询必须检查每一行以寻找匹配项。而有了索引，数据库可以直接跳转到相关行——从而显著减少需要检查的行数。

索引能加速读取操作，但会增加写入操作（插入、更新、删除）的开销，因为索引必须保持同步。因此，索引应谨慎创建，仅在需要时使用。

适合创建索引的列通常出现在 WHERE 子句、连接操作以及排序中。当使用多个列作为过滤条件时，创建复合索引可能更高效，但请注意列的顺序至关重要——应把选择性最高（即唯一值最多）的列放在最前面。

分析查询性能

大多数数据库允许你检查查询的执行方式，这被称为执行计划。理解执行计划能帮助你识别缺失的索引，并通过比较不同数据获取方式来优化查询结构。

CockroachDB

Use EXPLAIN ANALYZE (same syntax as PostgreSQL):

EXPLAIN ANALYZE SELECT * FROM "user" WHERE "firstName" = 'Timber';

Look for full scan (no index) vs scan with a specific index name.

Learn more

• Indexes
• EXPLAIN

Google Spanner

Use the Query Plan visualizer in the Google Cloud Console, or run a query in PLAN/PROFILE mode via the gcloud CLI:

gcloud spanner databases execute-sql DATABASE_ID \
    --instance=INSTANCE_ID \
    --query-mode=PROFILE \
    --sql="SELECT * FROM user WHERE firstName = 'Timber'"

Look for Table Scan (no index) vs Index Scan in the returned plan.

Learn more

• Secondary Indexes
• Query Execution Plans

MariaDB

Use EXPLAIN or ANALYZE to inspect the query execution plan:

ANALYZE SELECT * FROM user WHERE firstName = 'Timber';

Check the type column — ALL means a full table scan, while ref, range, or index indicate index usage. The key column shows which index was chosen.

Learn more

• Optimization and Indexes
• EXPLAIN
• ANALYZE Statement

MongoDB

Use the explain() method on a query to see the execution plan:

db.user.find({ firstName: "Timber" }).explain("executionStats")

Look for COLLSCAN (collection scan — no index) vs IXSCAN (index scan).

Learn more

• Indexes
• Explain Results

MS SQL Server

Use the execution plan to analyze queries. In SQL Server Management Studio, press Ctrl+M to include the actual execution plan, then run your query. Programmatically:

SET STATISTICS IO ON;
SELECT * FROM [user] WHERE firstName = 'Timber';
SET STATISTICS IO OFF;

Look for Table Scan (no index) vs Index Seek or Index Scan in the execution plan.

Learn more

• Indexes
• Execution Plans

MySQL

Use EXPLAIN to inspect the query execution plan:

EXPLAIN SELECT * FROM user WHERE firstName = 'Timber';

Check the type column — ALL means a full table scan, while ref, range, or index indicate index usage. The key column shows which index was chosen.

Aurora MySQL

This also applies to Amazon Aurora MySQL, which uses the same query engine and EXPLAIN syntax. See Aurora MySQL tuning for Aurora-specific guidance.

Learn more

• Optimization and Indexes
• EXPLAIN Output Format

Oracle

Use EXPLAIN PLAN to inspect the execution plan:

EXPLAIN PLAN FOR SELECT * FROM "user" WHERE "firstName" = 'Timber';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Look for TABLE ACCESS FULL (no index) vs INDEX RANGE SCAN or INDEX UNIQUE SCAN.

Learn more

• Indexes and Index-Organized Tables
• Generating and Displaying Execution Plans

PostgreSQL

Use EXPLAIN ANALYZE to see how PostgreSQL executes a query and whether it uses indexes:

EXPLAIN ANALYZE SELECT * FROM "user" WHERE "firstName" = 'Timber';

Look for Seq Scan (full table scan — no index used) vs Index Scan or Index Only Scan (index used).

Aurora PostgreSQL

This also applies to Amazon Aurora PostgreSQL, which uses the same query engine and EXPLAIN syntax. Aurora also offers query plan management for capturing and controlling execution plans.

Learn more

• Indexes
• EXPLAIN
• Using EXPLAIN

SAP HANA

Use EXPLAIN PLAN FOR to inspect the execution plan:

EXPLAIN PLAN FOR SELECT * FROM "user" WHERE "firstName" = 'Timber';
SELECT * FROM EXPLAIN_PLAN_TABLE;

Look for TABLE SCAN (no index) vs INDEX SCAN or INDEX SEEK in the operator column.

Learn more

• Indexes
• EXPLAIN PLAN

SQLite

Use EXPLAIN QUERY PLAN to see how SQLite resolves a query:

EXPLAIN QUERY PLAN SELECT * FROM user WHERE firstName = 'Timber';

Look for SCAN (no index) vs SEARCH (index used) in the output.

Learn more

• Query Planning
• EXPLAIN QUERY PLAN

在 TypeORM 中定义索引

TypeORM 支持使用 @Index 装饰器在表列上创建索引。

Basic index

import { Entity, PrimaryGeneratedColumn, Column, Index } from "typeorm"

@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    @Index()
    email: string

    @Column()
    firstName: string
}

Composite index

import { Entity, PrimaryGeneratedColumn, Column, Index } from "typeorm"

@Entity()
@Index(["lastName", "firstName"]) // most selective column first
export class User {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    firstName: string

    @Column()
    lastName: string
}

Join column

import {
    Entity,
    PrimaryGeneratedColumn,
    Column,
    ManyToOne,
    Index,
} from "typeorm"
import { User } from "./User"

@Entity()
export class Post {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    title: string

    @ManyToOne(() => User)
    @Index() // index foreign key columns to speed up joins
    author: User
}

要全面了解不同类型的索引（唯一索引、空间索引、全文索引、并发索引等），请参阅索引指南。

合理的索引通常是性能优化中最显著的改进手段——首先用上述执行计划工具分析最慢的查询，然后在最关键的位置添加索引。