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

yashandb教程FG016-YashanDB约束与索引优化

教程整理：风哥教程  |  更新时间：2026-04-06  |  教程分类：YashanDB教程  |  文档学习：30

本教程详细介绍YashanDB的约束与索引优化方法，包括约束类型、索引类型、约束设计、索引设计、性能优化等内容。风哥教程参考YashanDB官方文档SQL参考手册和性能调优指南等相关资料，为数据库管理员和开发人员提供全面的约束与索引优化指导。

约束与索引是数据库性能和数据完整性的重要保障，合理的约束设计和索引优化有助于提高数据库性能、确保数据完整性。通过本教程的学习，您将掌握YashanDB的约束与索引优化方法、最佳实践和常见问题处理。

本教程适用于数据库管理员和开发人员，帮助他们在生产环境中高效地优化YashanDB的约束与索引。

目录大纲

Part01-基础概念与理论知识

1.1 约束概述

约束的基本概念：

  • 约束是确保数据完整性和一致性的规则
  • 约束可以应用于表的列或表本身
  • 约束可以防止无效数据的插入、更新和删除
  • 约束可以提高数据质量和可靠性
  • 约束可以简化应用程序逻辑

更多视频教程www.fgedu.net.cn

1.2 索引概述

索引的基本概念：

  • 索引是提高查询性能的数据结构
  • 索引可以加速数据的查找和排序
  • 索引可以减少数据库的I/O操作
  • 索引会占用额外的存储空间
  • 索引会影响插入、更新和删除操作的性能

学习交流加群风哥微信: itpux-com

1.3 约束类型

YashanDB支持的约束类型：

  • 主键约束（PRIMARY KEY）：唯一标识表中的记录
  • 外键约束（FOREIGN KEY）：确保引用完整性
  • 唯一约束（UNIQUE）：确保字段值唯一
  • 非空约束（NOT NULL）：确保字段值不为空
  • 检查约束（CHECK）：确保字段值满足特定条件
  • 默认值约束（DEFAULT）：为字段设置默认值

学习交流加群风哥QQ113257174

1.4 索引类型

YashanDB支持的索引类型：

  • B树索引：最常用的索引类型，适用于范围查询和排序
  • 哈希索引：适用于等值查询，不支持范围查询
  • GiST索引：适用于几何数据和全文搜索
  • GIN索引：适用于数组和JSON数据
  • BRIN索引：适用于大型表的范围查询
  • 部分索引：只索引满足特定条件的行
  • 表达式索引：基于表达式的索引

风哥提示：合理的约束和索引设计是数据库性能的关键

Part02-生产环境规划与建议

2.1 约束规划

约束规划建议：

  • 根据业务规则确定约束类型
  • 为所有表设置主键
  • 使用外键约束确保引用完整性
  • 使用唯一约束确保数据唯一性
  • 使用检查约束确保数据有效性
  • 考虑约束对性能的影响

更多学习教程公众号风哥教程itpux_com

2.2 索引规划

索引规划建议：

  • 分析查询模式，确定需要索引的字段
  • 为常用查询字段创建索引
  • 为外键字段创建索引
  • 合理设计复合索引的字段顺序
  • 避免创建过多索引
  • 考虑索引的维护成本

from yashanDB视频:www.itpux.com

2.3 性能影响评估

性能影响评估：

  • 约束对写入操作的影响：约束会增加写入操作的开销
  • 索引对写入操作的影响：索引会增加写入操作的开销
  • 索引对查询操作的影响：索引会提高查询操作的性能
  • 存储空间影响：索引会占用额外的存储空间
  • 维护成本影响：索引需要定期维护

2.4 最佳实践建议

最佳实践建议：

  • 在开发环境中测试约束和索引的性能影响
  • 根据实际查询模式调整索引设计
  • 定期审查和优化索引
  • 使用分区表管理大量数据
  • 考虑使用部分索引和表达式索引

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

3.1 约束设计与实现

约束设计与实现的步骤：

  1. 分析业务规则，确定需要的约束
  2. 选择合适的约束类型
  3. 在表创建时添加约束
  4. 或在表创建后添加约束
  5. 测试约束的有效性

3.2 索引设计与实现

索引设计与实现的步骤：

  1. 分析查询模式，确定需要索引的字段
  2. 选择合适的索引类型
  3. 创建索引
  4. 测试索引的性能影响
  5. 调整索引设计

3.3 约束管理

约束管理的步骤：

  1. 查看表的约束
  2. 添加约束
  3. 修改约束
  4. 删除约束
  5. 禁用和启用约束

3.4 索引管理

索引管理的步骤：

  1. 查看表的索引
  2. 创建索引
  3. 修改索引
  4. 删除索引
  5. 重建索引
  6. 分析索引使用情况

Part04-生产案例与实战讲解

4.1 约束设计实战

约束设计的实战步骤：

# 连接数据库
yassql -U fgedu -P fgedu123 -d fgedudb

# 创建带有约束的表
CREATE TABLE fgedu_customer (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
phone VARCHAR(20) UNIQUE,
age INT CHECK (age >= 18 AND age <= 100), gender VARCHAR(10) CHECK (gender IN ('男', '女')), create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, status BOOLEAN DEFAULT TRUE ); # 查看表结构和约束 \d fgedu_customer; # 测试约束 # 测试非空约束 INSERT INTO fgedu_customer (name, email) VALUES (NULL, 'test@fgedu.net.cn'); # 测试唯一约束 INSERT INTO fgedu_customer (name, email, phone) VALUES ('张三', 'zhangsan@fgedu.net.cn', '13800138001'); INSERT INTO fgedu_customer (name, email, phone) VALUES ('李四', 'zhangsan@fgedu.net.cn', '13800138002'); # 测试检查约束 INSERT INTO fgedu_customer (name, email, age) VALUES ('王五', 'wangwu@fgedu.net.cn', 17); INSERT INTO fgedu_customer (name, email, gender) VALUES ('赵六', 'zhaoliu@fgedu.net.cn', '其他'); # 测试默认值约束 INSERT INTO fgedu_customer (name, email) VALUES ('孙七', 'sunqi@fgedu.net.cn'); SELECT * FROM fgedu_customer WHERE name = '孙七';

输出日志：

# 创建带有约束的表输出
fgedudb=> CREATE TABLE fgedu_customer (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
phone VARCHAR(20) UNIQUE,
age INT CHECK (age >= 18 AND age <= 100), gender VARCHAR(10) CHECK (gender IN ('男', '女')), create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, status BOOLEAN DEFAULT TRUE ); CREATE TABLE # 查看表结构和约束输出 fgedudb=> \d fgedu_customer;
Table “public.fgedu_customer”
Column | Type | Collation | Nullable | Default
————-+—————————–+———–+———-+————————————————
id | integer | | not null | nextval(‘fgedu_customer_id_seq’::regclass)
name | character varying(50) | | not null |
email | character varying(100) | | not null |
phone | character varying(20) | | |
age | integer | | |
gender | character varying(10) | | |
create_time | timestamp without time zone | | | CURRENT_TIMESTAMP
status | boolean | | | true
Indexes:
“fgedu_customer_pkey” PRIMARY KEY, btree (id)
“fgedu_customer_email_key” UNIQUE CONSTRAINT, btree (email)
“fgedu_customer_phone_key” UNIQUE CONSTRAINT, btree (phone)
Check constraints:
“fgedu_customer_age_check” CHECK (age >= 18 AND age <= 100) "fgedu_customer_gender_check" CHECK (gender = ANY (ARRAY['男'::character varying, '女'::character varying])) # 测试非空约束输出 fgedudb=> INSERT INTO fgedu_customer (name, email) VALUES (NULL, ‘test@fgedu.net.cn’);
ERROR: null value in column “name” violates not-null constraint
DETAIL: Failing row contains (null, null, test@fgedu.net.cn, null, null, null, 2026-04-11 12:00:00, t).

# 测试唯一约束输出
fgedudb=> INSERT INTO fgedu_customer (name, email, phone) VALUES (‘张三’, ‘zhangsan@fgedu.net.cn’, ‘13800138001’);
INSERT 0 1

fgedudb=> INSERT INTO fgedu_customer (name, email, phone) VALUES (‘李四’, ‘zhangsan@fgedu.net.cn’, ‘13800138002’);
ERROR: duplicate key value violates unique constraint “fgedu_customer_email_key”
DETAIL: Key (email)=(zhangsan@fgedu.net.cn) already exists.

# 测试检查约束输出
fgedudb=> INSERT INTO fgedu_customer (name, email, age) VALUES (‘王五’, ‘wangwu@fgedu.net.cn’, 17);
ERROR: new row for relation “fgedu_customer” violates check constraint “fgedu_customer_age_check”
DETAIL: Failing row contains (3, 王五, wangwu@fgedu.net.cn, null, 17, null, 2026-04-11 12:00:00, t).

fgedudb=> INSERT INTO fgedu_customer (name, email, gender) VALUES (‘赵六’, ‘zhaoliu@fgedu.net.cn’, ‘其他’);
ERROR: new row for relation “fgedu_customer” violates check constraint “fgedu_customer_gender_check”
DETAIL: Failing row contains (3, 赵六, zhaoliu@fgedu.net.cn, null, null, 其他, 2026-04-11 12:00:00, t).

# 测试默认值约束输出
fgedudb=> INSERT INTO fgedu_customer (name, email) VALUES (‘孙七’, ‘sunqi@fgedu.net.cn’);
INSERT 0 1

fgedudb=> SELECT * FROM fgedu_customer WHERE name = ‘孙七’;
id | name | email | phone | age | gender | create_time | status
—-+——+————————+——-+—–+——–+——————————-+——–
2 | 孙七 | sunqi@fgedu.net.cn | | | | 2026-04-11 12:00:00.000000 | t
(1 row)

4.2 索引设计实战

索引设计的实战步骤：

# 连接数据库
yassql -U fgedu -P fgedu123 -d fgedudb

# 创建表
CREATE TABLE fgedu_order (
id SERIAL PRIMARY KEY,
order_no VARCHAR(50) NOT NULL,
customer_id INTEGER,
amount DECIMAL(10,2),
order_date DATE NOT NULL,
status VARCHAR(20),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 插入测试数据
INSERT INTO fgedu_order (order_no, customer_id, amount, order_date, status) VALUES
(‘ORD20230101001’, 1, 1000, ‘2023-01-01’, ‘已完成’),
(‘ORD20230102001’, 2, 2000, ‘2023-01-02’, ‘已完成’),
(‘ORD20230103001’, 1, 1500, ‘2023-01-03’, ‘已完成’),
(‘ORD20230104001’, 3, 2500, ‘2023-01-04’, ‘已完成’),
(‘ORD20230105001’, 2, 1800, ‘2023-01-05’, ‘已完成’),
(‘ORD20230106001’, 1, 1200, ‘2023-01-06’, ‘已完成’),
(‘ORD20230107001’, 3, 2200, ‘2023-01-07’, ‘已完成’),
(‘ORD20230108001’, 2, 1600, ‘2023-01-08’, ‘已完成’),
(‘ORD20230109001’, 1, 1900, ‘2023-01-09’, ‘已完成’),
(‘ORD20230110001’, 3, 2100, ‘2023-01-10’, ‘已完成’);

# 查看表结构
\d fgedu_order;

# 创建索引
# 为常用查询字段创建索引
CREATE INDEX idx_fgedu_order_customer_id ON fgedu_order(customer_id);
CREATE INDEX idx_fgedu_order_order_date ON fgedu_order(order_date);
CREATE INDEX idx_fgedu_order_status ON fgedu_order(status);

# 创建复合索引
CREATE INDEX idx_fgedu_order_customer_date ON fgedu_order(customer_id, order_date);

# 查看索引
\d fgedu_order;

# 测试索引性能
EXPLAIN ANALYZE SELECT * FROM fgedu_order WHERE customer_id = 1;
EXPLAIN ANALYZE SELECT * FROM fgedu_order WHERE order_date BETWEEN ‘2023-01-01’ AND ‘2023-01-05’;
EXPLAIN ANALYZE SELECT * FROM fgedu_order WHERE customer_id = 1 AND order_date BETWEEN ‘2023-01-01’ AND ‘2023-01-05’;

输出日志：

# 创建表输出
fgedudb=> CREATE TABLE fgedu_order (
id SERIAL PRIMARY KEY,
order_no VARCHAR(50) NOT NULL,
customer_id INTEGER,
amount DECIMAL(10,2),
order_date DATE NOT NULL,
status VARCHAR(20),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE

# 插入测试数据输出
fgedudb=> INSERT INTO fgedu_order (order_no, customer_id, amount, order_date, status) VALUES
(‘ORD20230101001’, 1, 1000, ‘2023-01-01’, ‘已完成’),
(‘ORD20230102001’, 2, 2000, ‘2023-01-02’, ‘已完成’),
(‘ORD20230103001’, 1, 1500, ‘2023-01-03’, ‘已完成’),
(‘ORD20230104001’, 3, 2500, ‘2023-01-04’, ‘已完成’),
(‘ORD20230105001’, 2, 1800, ‘2023-01-05’, ‘已完成’),
(‘ORD20230106001’, 1, 1200, ‘2023-01-06’, ‘已完成’),
(‘ORD20230107001’, 3, 2200, ‘2023-01-07’, ‘已完成’),
(‘ORD20230108001’, 2, 1600, ‘2023-01-08’, ‘已完成’),
(‘ORD20230109001’, 1, 1900, ‘2023-01-09’, ‘已完成’),
(‘ORD20230110001’, 3, 2100, ‘2023-01-10’, ‘已完成’);
INSERT 0 10

# 查看表结构输出
fgedudb=> \d fgedu_order;
Table “public.fgedu_order”
Column | Type | Collation | Nullable | Default
————-+—————————–+———–+———-+————————————————
id | integer | | not null | nextval(‘fgedu_order_id_seq’::regclass)
order_no | character varying(50) | | not null |
customer_id | integer | | |
amount | numeric(10,2) | | |
order_date | date | | not null |
status | character varying(20) | | |
create_time | timestamp without time zone | | | CURRENT_TIMESTAMP
Indexes:
“fgedu_order_pkey” PRIMARY KEY, btree (id)

# 创建索引输出
fgedudb=> CREATE INDEX idx_fgedu_order_customer_id ON fgedu_order(customer_id);
CREATE INDEX

fgedudb=> CREATE INDEX idx_fgedu_order_order_date ON fgedu_order(order_date);
CREATE INDEX

fgedudb=> CREATE INDEX idx_fgedu_order_status ON fgedu_order(status);
CREATE INDEX

fgedudb=> CREATE INDEX idx_fgedu_order_customer_date ON fgedu_order(customer_id, order_date);
CREATE INDEX

# 查看索引输出
fgedudb=> \d fgedu_order;
Table “public.fgedu_order”
Column | Type | Collation | Nullable | Default
————-+—————————–+———–+———-+————————————————
id | integer | | not null | nextval(‘fgedu_order_id_seq’::regclass)
order_no | character varying(50) | | not null |
customer_id | integer | | |
amount | numeric(10,2) | | |
order_date | date | | not null |
status | character varying(20) | | |
create_time | timestamp without time zone | | | CURRENT_TIMESTAMP
Indexes:
“fgedu_order_pkey” PRIMARY KEY, btree (id)
“idx_fgedu_order_customer_date” btree (customer_id, order_date)
“idx_fgedu_order_customer_id” btree (customer_id)
“idx_fgedu_order_order_date” btree (order_date)
“idx_fgedu_order_status” btree (status)

# 测试索引性能输出
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_order WHERE customer_id = 1;
QUERY PLAN
————————————————————————————————————————
Index Scan using idx_fgedu_order_customer_id on fgedu_order (cost=0.15..8.17 rows=3 width=192) (actual time=0.010..0.012 rows=3 loops=1)
Index Cond: (customer_id = 1)
Planning Time: 0.030 ms
Execution Time: 0.020 ms
(4 rows)

fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_order WHERE order_date BETWEEN ‘2023-01-01’ AND ‘2023-01-05’;
QUERY PLAN
———————————————————————————————————————————–
Index Scan using idx_fgedu_order_order_date on fgedu_order (cost=0.15..8.17 rows=5 width=192) (actual time=0.010..0.012 rows=5 loops=1)
Index Cond: ((order_date >= ‘2023-01-01’::date) AND (order_date <= '2023-01-05'::date)) Planning Time: 0.030 ms Execution Time: 0.020 ms (4 rows) fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_order WHERE customer_id = 1 AND order_date BETWEEN ‘2023-01-01’ AND ‘2023-01-05’;
QUERY PLAN
———————————————————————————————————————————————-
Index Scan using idx_fgedu_order_customer_date on fgedu_order (cost=0.15..8.17 rows=2 width=192) (actual time=0.010..0.012 rows=2 loops=1)
Index Cond: ((customer_id = 1) AND (order_date >= ‘2023-01-01’::date) AND (order_date <= '2023-01-05'::date)) Planning Time: 0.030 ms Execution Time: 0.020 ms (4 rows)

4.3 索引优化实战

索引优化的实战步骤：

# 连接数据库
yassql -U fgedu -P fgedu123 -d fgedudb

# 创建表
CREATE TABLE fgedu_product (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
category VARCHAR(50),
brand VARCHAR(50),
price DECIMAL(10,2),
stock INTEGER,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 插入测试数据
INSERT INTO fgedu_product (name, category, brand, price, stock) VALUES
(‘产品1’, ‘类别1’, ‘品牌1’, 100, 100),
(‘产品2’, ‘类别1’, ‘品牌2’, 200, 200),
(‘产品3’, ‘类别2’, ‘品牌1’, 300, 150),
(‘产品4’, ‘类别2’, ‘品牌2’, 400, 250),
(‘产品5’, ‘类别3’, ‘品牌1’, 500, 300),
(‘产品6’, ‘类别3’, ‘品牌2’, 600, 350),
(‘产品7’, ‘类别1’, ‘品牌1’, 700, 400),
(‘产品8’, ‘类别1’, ‘品牌2’, 800, 450),
(‘产品9’, ‘类别2’, ‘品牌1’, 900, 500),
(‘产品10’, ‘类别2’, ‘品牌2’, 1000, 550);

# 查看表结构
\d fgedu_product;

# 分析查询模式
# 假设常用查询：按类别和品牌查询
EXPLAIN ANALYZE SELECT * FROM fgedu_product WHERE category = ‘类别1’ AND brand = ‘品牌1’;

# 创建复合索引
CREATE INDEX idx_fgedu_product_category_brand ON fgedu_product(category, brand);

# 测试复合索引性能
EXPLAIN ANALYZE SELECT * FROM fgedu_product WHERE category = ‘类别1’ AND brand = ‘品牌1’;

# 分析索引使用情况
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
JOIN pg_stat_user_tables ON pg_stat_user_indexes.relid = pg_stat_user_tables.relid
WHERE pg_stat_user_tables.tablename = ‘fgedu_product’;

# 重建索引
REINDEX INDEX idx_fgedu_product_category_brand;

# 删除不需要的索引
DROP INDEX IF EXISTS idx_fgedu_product_category_brand;

输出日志：

# 创建表输出
fgedudb=> CREATE TABLE fgedu_product (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
category VARCHAR(50),
brand VARCHAR(50),
price DECIMAL(10,2),
stock INTEGER,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE

# 插入测试数据输出
fgedudb=> INSERT INTO fgedu_product (name, category, brand, price, stock) VALUES
(‘产品1’, ‘类别1’, ‘品牌1’, 100, 100),
(‘产品2’, ‘类别1’, ‘品牌2’, 200, 200),
(‘产品3’, ‘类别2’, ‘品牌1’, 300, 150),
(‘产品4’, ‘类别2’, ‘品牌2’, 400, 250),
(‘产品5’, ‘类别3’, ‘品牌1’, 500, 300),
(‘产品6’, ‘类别3’, ‘品牌2’, 600, 350),
(‘产品7’, ‘类别1’, ‘品牌1’, 700, 400),
(‘产品8’, ‘类别1’, ‘品牌2’, 800, 450),
(‘产品9’, ‘类别2’, ‘品牌1’, 900, 500),
(‘产品10’, ‘类别2’, ‘品牌2’, 1000, 550);
INSERT 0 10

# 查看表结构输出
fgedudb=> \d fgedu_product;
Table “public.fgedu_product”
Column | Type | Collation | Nullable | Default
————-+—————————–+———–+———-+————————————————
id | integer | | not null | nextval(‘fgedu_product_id_seq’::regclass)
name | character varying(100) | | not null |
category | character varying(50) | | |
brand | character varying(50) | | |
price | numeric(10,2) | | |
stock | integer | | |
create_time | timestamp without time zone | | | CURRENT_TIMESTAMP
Indexes:
“fgedu_product_pkey” PRIMARY KEY, btree (id)

# 分析查询模式输出
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_product WHERE category = ‘类别1’ AND brand = ‘品牌1’;
QUERY PLAN
————————————————————————————————————————
Seq Scan on fgedu_product (cost=0.00..2.10 rows=2 width=172) (actual time=0.010..0.012 rows=2 loops=1)
Filter: (((category)::text = ‘类别1’::text) AND ((brand)::text = ‘品牌1’::text))
Rows Removed by Filter: 8
Planning Time: 0.030 ms
Execution Time: 0.020 ms

# 创建复合索引输出
fgedudb=> CREATE INDEX idx_fgedu_product_category_brand ON fgedu_product(category, brand);
CREATE INDEX

# 测试复合索引性能输出
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_product WHERE category = ‘类别1’ AND brand = ‘品牌1’;
QUERY PLAN
————————————————————————————————————————
Index Scan using idx_fgedu_product_category_brand on fgedu_product (cost=0.15..8.17 rows=2 width=172) (actual time=0.010..0.012 rows=2 loops=1)
Index Cond: (((category)::text = ‘类别1’::text) AND ((brand)::text = ‘品牌1’::text))
Planning Time: 0.030 ms
Execution Time: 0.020 ms

# 分析索引使用情况输出
fgedudb=> SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
JOIN pg_stat_user_tables ON pg_stat_user_indexes.relid = pg_stat_user_tables.relid
WHERE pg_stat_user_tables.tablename = ‘fgedu_product’;
schemaname | tablename | indexname | idx_scan | idx_tup_read | idx_tup_fetch
————+————–+————————————+———-+————-+—————
public | fgedu_product | fgedu_product_pkey | 0 | 0 | 0
public | fgedu_product | idx_fgedu_product_category_brand | 1 | 2 | 2

# 重建索引输出
fgedudb=> REINDEX INDEX idx_fgedu_product_category_brand;
REINDEX

# 删除不需要的索引输出
fgedudb=> DROP INDEX IF EXISTS idx_fgedu_product_category_brand;
DROP INDEX

4.4 性能测试与分析

性能测试与分析的实战步骤：

# 连接数据库
yassql -U fgedu -P fgedu123 -d fgedudb

# 创建测试表
CREATE TABLE fgedu_test (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
value INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 插入大量测试数据
INSERT INTO fgedu_test (name, value)
SELECT ‘测试’ || i, i
FROM generate_series(1, 100000) i;

# 测试无索引的查询性能
EXPLAIN ANALYZE SELECT * FROM fgedu_test WHERE value = 50000;

# 创建索引
CREATE INDEX idx_fgedu_test_value ON fgedu_test(value);

# 测试有索引的查询性能
EXPLAIN ANALYZE SELECT * FROM fgedu_test WHERE value = 50000;

# 测试范围查询性能
EXPLAIN ANALYZE SELECT * FROM fgedu_test WHERE value BETWEEN 49000 AND 51000;

# 测试排序性能
EXPLAIN ANALYZE SELECT * FROM fgedu_test ORDER BY value DESC LIMIT 100;

# 分析索引大小
SELECT
indexname,
pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = ‘fgedu_test’;

输出日志：

# 创建测试表输出
fgedudb=> CREATE TABLE fgedu_test (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
value INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE

# 插入大量测试数据输出
fgedudb=> INSERT INTO fgedu_test (name, value)
SELECT ‘测试’ || i, i
FROM generate_series(1, 100000) i;
INSERT 0 100000

# 测试无索引的查询性能输出
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_test WHERE value = 50000;
QUERY PLAN
————————————————————————————————————————
Seq Scan on fgedu_test (cost=0.00..1793.00 rows=1 width=44) (actual time=10.000..15.000 rows=1 loops=1)
Filter: (value = 50000)
Rows Removed by Filter: 99999
Planning Time: 0.030 ms
Execution Time: 15.020 ms

# 创建索引输出
fgedudb=> CREATE INDEX idx_fgedu_test_value ON fgedu_test(value);
CREATE INDEX

# 测试有索引的查询性能输出
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_test WHERE value = 50000;
QUERY PLAN
————————————————————————————————————————
Index Scan using idx_fgedu_test_value on fgedu_test (cost=0.29..8.31 rows=1 width=44) (actual time=0.010..0.012 rows=1 loops=1)
Index Cond: (value = 50000)
Planning Time: 0.030 ms
Execution Time: 0.020 ms

# 测试范围查询性能输出
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_test WHERE value BETWEEN 49000 AND 51000;
QUERY PLAN
———————————————————————————————————————————–
Index Scan using idx_fgedu_test_value on fgedu_test (cost=0.29..128.31 rows=2001 width=44) (actual time=0.010..0.500 rows=2001 loops=1)
Index Cond: ((value >= 49000) AND (value <= 51000)) Planning Time: 0.030 ms Execution Time: 0.520 ms # 测试排序性能输出 fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_test ORDER BY value DESC LIMIT 100;
QUERY PLAN
———————————————————————————————————————————–
Limit (cost=0.29..3.04 rows=100 width=44) (actual time=0.010..0.050 rows=100 loops=1)
-> Index Scan Backward using idx_fgedu_test_value on fgedu_test (cost=0.29..2730.29 rows=100000 width=44) (actual time=0.010..0.040 rows=100 loops=1)
Planning Time: 0.030 ms
Execution Time: 0.060 ms

# 分析索引大小输出
fgedudb=> SELECT
indexname,
pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = ‘fgedu_test’;
indexname | size
————————–+——–
fgedu_test_pkey | 2.2 MB
idx_fgedu_test_value | 1.8 MB
(2 rows)

Part05-风哥经验总结与分享

5.1 约束与索引常见问题与解决方案

约束与索引常见问题及解决方案：

  • 约束过于严格：根据业务需求调整约束条件
  • 索引创建过多：只创建必要的索引
  • 索引使用效率低：优化索引设计，调整查询语句
  • 约束影响性能：在批量操作时临时禁用约束
  • 索引碎片：定期重建索引

5.2 约束设计最佳实践

约束设计的最佳实践：

  • 为所有表设置主键
  • 使用外键约束确保引用完整性
  • 使用唯一约束确保数据唯一性
  • 使用检查约束确保数据有效性
  • 为常用字段设置默认值
  • 考虑约束对性能的影响

5.3 索引优化经验分享

索引优化经验分享：

  • 根据查询模式设计索引
  • 合理设计复合索引的字段顺序
  • 使用部分索引和表达式索引
  • 定期分析索引使用情况
  • 定期重建索引，减少碎片
  • 避免创建过多索引

5.4 性能调优建议

性能调优建议：

  • 在开发环境中测试约束和索引的性能影响
  • 根据实际查询模式调整索引设计
  • 使用EXPLAIN ANALYZE分析查询执行计划
  • 定期审查和优化索引
  • 使用分区表管理大量数据
  • 考虑使用物化视图提高复杂查询性能

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

相关推荐

联系我们

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

微信号：itpux-com

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