2. 首页 > 国产数据库教程 > GaussDB教程 > 正文

GaussDB教程FG012-GaussDB表设计与索引优化

教程整理：风哥教程  |  更新时间：2026-02-01  |  教程分类：GaussDB教程  |  文档学习：20

本教程详细介绍GaussDB数据库的表设计原则与索引优化方法，包括表结构设计、数据类型选择、索引类型、索引创建与管理等内容。风哥教程参考GaussDB官方文档GaussDB8开发者手册、GaussDB8性能调优指南等。

通过本教程的学习，您将掌握GaussDB数据库的表设计最佳实践，了解如何创建高效的索引，提高数据库查询性能。

本教程包含丰富的实战操作，帮助您在生产环境中快速应用所学知识。

目录大纲

Part01-基础概念与理论知识

1.1. GaussDB表设计原则

GaussDB表设计应遵循以下原则：

  • 标准化原则：遵循数据库设计范式，减少数据冗余
  • 性能原则：考虑查询性能，合理设计表结构
  • 可扩展性原则：考虑未来数据增长，设计可扩展的表结构
  • 数据类型合理性：选择合适的数据类型，减少存储空间
  • 约束完整性：合理使用约束，保证数据完整性

1.2. GaussDB数据类型选择

GaussDB支持多种数据类型，包括：

  • 数值类型：integer, bigint, decimal, real, double precision
  • 字符类型：varchar, char, text
  • 日期时间类型：date, time, timestamp, interval
  • 布尔类型：boolean
  • 二进制类型：bytea
  • JSON类型：json, jsonb

1.3. GaussDB索引类型

GaussDB支持多种索引类型：

  • B-tree索引：默认索引类型，适用于等值查询和范围查询
  • Hash索引：适用于等值查询，不支持范围查询
  • GIN索引：适用于复合值类型，如数组、JSON
  • GiST索引：适用于空间数据和全文搜索
  • SP-GiST索引：适用于非平衡数据结构
  • BRIN索引：适用于大数据量的范围查询

1.4. GaussDB索引原理

索引的基本原理是通过构建数据结构（如B-tree），加速数据的查找速度。索引可以大大提高查询性能，但也会增加写入操作的开销，因此需要合理创建和管理索引。

Part02-生产环境规划与建议

2.1. 表结构规划

生产环境表结构规划建议：

  • 根据业务需求设计表结构，避免过度设计
  • 合理选择数据类型，减少存储空间
  • 为常用查询字段创建索引
  • 考虑表的分区策略，提高查询性能
  • 设置合理的约束，保证数据完整性

2.2. 索引策略规划

索引策略规划建议：

  • 为常用查询的WHERE条件字段创建索引
  • 为连接查询的关联字段创建索引
  • 为排序和分组字段创建索引
  • 避免创建过多索引，影响写入性能
  • 定期维护索引，保持索引效率

2.3. 分区表规划

分区表规划建议：

  • 对于大数据量表，考虑使用分区表
  • 根据业务特点选择分区键，如时间、地域等
  • 合理设置分区大小，避免分区过多或过少
  • 定期管理分区，如合并、拆分、删除等

Part03-生产环境项目实施方案

3.1. 表创建与管理

表创建与管理包括创建表、修改表结构、删除表等操作，是数据库管理的基础工作。

3.2. 索引创建与管理

索引创建与管理包括创建索引、修改索引、删除索引等操作，是提高查询性能的重要手段。

3.3. 表分区实施

表分区实施包括创建分区表、管理分区等操作，是处理大数据量的有效方法。

Part04-生产案例与实战讲解

4.1. GaussDB数据库表设计实战

创建表并设置约束：

— 连接到数据库
$ psql -h 192.168.1.101 -p 5432 -U fgedu -d fgedudb

— 创建用户表
fgedudb=> CREATE TABLE fgedu_users (
user_id serial PRIMARY KEY,
username varchar(50) UNIQUE NOT NULL,
email varchar(100) UNIQUE NOT NULL,
password_hash varchar(100) NOT NULL,
created_at timestamp DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE

— 创建订单表
fgedudb=> CREATE TABLE fgedu_orders (
order_id serial PRIMARY KEY,
user_id integer REFERENCES fgedu_users(user_id),
order_date timestamp DEFAULT CURRENT_TIMESTAMP,
total_amount decimal(10,2) NOT NULL,
status varchar(20) DEFAULT ‘pending’
);
CREATE TABLE

— 创建订单详情表
fgedudb=> CREATE TABLE fgedu_order_items (
item_id serial PRIMARY KEY,
order_id integer REFERENCES fgedu_orders(order_id),
product_id integer NOT NULL,
quantity integer NOT NULL,
unit_price decimal(10,2) NOT NULL,
subtotal decimal(10,2) NOT NULL
); 风哥提示：
CREATE TABLE

4.2. GaussDB数据库索引创建与优化实战

创建索引：

— 为订单表的user_id和order_date创建索引
fgedudb=> CREATE INDEX idx_orders_user_id ON fgedu_orders(user_id);
CREATE INDEX
fgedudb=> CREATE INDEX idx_orders_order_date ON fgedu_orders(order_date); 学习交流加群风哥微信: itpux-com
CREATE INDEX

— 为订单详情表的order_id创建索引
fgedudb=> CREATE INDEX idx_order_items_order_id ON fgedu_order_items(order_id);
CREATE INDEX

— 查看索引信息
fgedudb=> \d fgedu_orders
Table “public.fgedu_orders”
Column | Type | Collation | Nullable | Default
————-+—————————–+———–+———-+————————————
order_id | integer | | not null | nextval(‘fgedu_orders_order_id_seq’::regclass)
user_id | integer | | |
order_date | timestamp without time zone | | | CURRENT_TIMESTAMP
total_amount | numeric(10,2) | | not null |
status | character varying(20) | | | ‘pending’
Indexes:
“fgedu_orders_pkey” PRIMARY KEY, btree (order_id)
“idx_orders_order_date” btree (order_date)
“idx_orders_user_id” btree (user_id)
Foreign-key constraints:
“fgedu_orders_user_id_fkey” FOREIGN KEY (user_id) REFERENCES fgedu_users(user_id)

测试索引性能：

— 插入测试数据
fgedudb=> INSERT INTO fgedu_users (username, email, password_hash)
SELECT ‘user’ || i, ‘user’ || i || ‘@example.com’, ‘hash’ || i
FROM generate_series(1, 100000) i;
INSERT 0 100000 学习交流加群风哥QQ113257174

— 插入订单数据
fgedudb=> INSERT INTO fgedu_orders (user_id, total_amount, status)
SELECT (i % 100000) + 1, random() * 1000, ‘completed’
FROM generate_series(1, 500000) i;
INSERT 0 500000

— 测试无索引查询
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE user_id = 50000;
QUERY PLAN
—————————————————————————————————————-
Gather (cost=1000.00..9718.50 rows=5 width=44) (actual time=0.158..29.345 rows=5 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on fgedu_orders (cost=0.00..8718.00 rows=2 width=44) (actual time=13.760..25.428 rows=5 loops=3)
Filter: (user_id = 50000)
Rows Removed by Filter: 166662
Planning Time: 0.104 ms
Execution Time: 29.372 ms
(7 rows)

— 测试有索引查询
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE user_id = 50000;
QUERY PLAN
—————————————————————————————————————-
Index Scan using idx_orders_user_id on fgedu_orders (cost=0.43..12.45 rows=5 width=44) (actual time=0.022..0.035 rows=5 loops=1)
Index Cond: (user_id = 50000)
Planning Time: 0.107 ms
Execution Time: 0.051 ms
(4 rows) 更多视频教程www.fgedu.net.cn

4.3. GaussDB数据库表分区实战

创建分区表：

— 创建按月份分区的销售表
fgedudb=> CREATE TABLE fgedu_sales (
sale_id serial PRIMARY KEY,
sale_date date NOT NULL,
product_id integer NOT NULL,
quantity integer NOT NULL,
amount decimal(10,2) NOT NULL
) PARTITION BY RANGE (sale_date);
CREATE TABLE

— 创建分区
fgedudb=> CREATE TABLE fgedu_sales_202401 PARTITION OF fgedu_sales
FOR VALUES FROM (‘2024-01-01’) TO (‘2024-02-01’);
CREATE TABLE

fgedudb=> CREATE TABLE fgedu_sales_202402 PARTITION OF fgedu_sales
FOR VALUES FROM (‘2024-02-01’) TO (‘2024-03-01’);
CREATE TABLE

fgedudb=> CREATE TABLE fgedu_sales_202403 PARTITION OF fgedu_sales
FOR VALUES FROM (‘2024-03-01’) TO (‘2024-04-01’);
CREATE TABLE

— 插入测试数据
fgedudb=> INSERT INTO fgedu_sales (sale_date, product_id, quantity, amount) 更多学习教程公众号风哥教程itpux_com
SELECT ‘2024-01-‘ || (random() * 30 + 1)::integer,
(random() * 1000 + 1)::integer,
(random() * 100 + 1)::integer,
random() * 1000
FROM generate_series(1, 100000);
INSERT 0 100000

— 测试分区查询性能
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_sales WHERE sale_date BETWEEN ‘2024-01-15’ AND ‘2024-01-31’;
QUERY PLAN
—————————————————————————————————————-
Seq Scan on fgedu_sales_202401 fgedu_sales (cost=0.00..1833.00 rows=50000 width=28) (actual time=0.014..13.245 rows=50869 loops=1)
Filter: ((sale_date >= ‘2024-01-15’::date) AND (sale_date <= '2024-01-31'::date)) Rows Removed by Filter: 15673 Planning Time: 0.112 ms Execution Time: 16.478 ms (5 rows)

4.4. GaussDB数据库查询性能优化实战

优化查询语句：

— 优化前：全表扫描
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE status = ‘completed’;
QUERY PLAN
—————————————————————————————————————-
Gather (cost=1000.00..11718.50 rows=250000 width=44) (actual time=0.153..78.345 rows=500000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on fgedu_orders (cost=0.00..9218.50 rows=104167 width=44) (actual time=0.032..45.678 rows=166667 loops=3) from DB视频:www.itpux.com
Filter: (status = ‘completed’::character varying)
Rows Removed by Filter: 0
Planning Time: 0.104 ms
Execution Time: 85.672 ms
(7 rows)

— 为status字段创建索引
fgedudb=> CREATE INDEX idx_orders_status ON fgedu_orders(status);
CREATE INDEX

— 优化后：索引扫描
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE status = ‘completed’;
QUERY PLAN
—————————————————————————————————————-
Bitmap Heap Scan on fgedu_orders (cost=432.00..7432.00 rows=250000 width=44) (actual time=0.234..45.678 rows=500000 loops=1)
Recheck Cond: (status = ‘completed’::character varying)
Heap Blocks: exact=12345
-> Bitmap Index Scan on idx_orders_status (cost=0.00..379.50 rows=250000 width=0) (actual time=0.156..0.156 rows=500000 loops=1)
Index Cond: (status = ‘completed’::character varying)
Planning Time: 0.123 ms
Execution Time: 52.345 ms
(6 rows)

Part05-风哥经验总结与分享

5.1. GaussDB数据库表设计最佳实践

1. 合理设计表结构：遵循数据库设计范式，减少数据冗余

2. 选择合适的数据类型：根据数据特点选择最适合的数据类型

3. 设置合理的约束：使用主键、外键、唯一约束等保证数据完整性

4. 考虑表的大小：对于大数据量表，使用分区表提高性能

5. 定期维护表：执行VACUUM和ANALYZE操作，保持表的性能

5.2. GaussDB数据库索引优化技巧

1. 只为必要的字段创建索引：避免创建过多索引影响写入性能

2. 选择合适的索引类型：根据查询模式选择适合的索引类型

3. 合理设置索引列顺序：将选择性高的列放在前面

4. 定期重建索引：对于频繁更新的表，定期重建索引

5. 使用部分索引：对于特定条件的查询，使用部分索引提高效率

5.3. GaussDB数据库性能调优建议

1. 优化SQL语句：避免全表扫描，使用索引覆盖查询

2. 合理使用连接查询：避免复杂的多表连接

3. 使用物化视图：对于复杂查询，使用物化视图提高性能

4. 调整数据库参数：根据硬件配置和业务需求调整参数

5. 监控数据库性能：定期监控数据库性能，及时发现问题

表设计和索引优化是数据库性能调优的基础，需要根据实际业务场景进行合理规划和实施。

from GaussDB视频:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息