代码之家 › 专栏 › 技术社区 › foudfou

我应该为select在外键上创建索引吗?

postgresql database sql

foudfou · 技术社区 · 3 年前

在postgresql中,给定以下表格:

CREATE TABLE departments(
   id SERIAL NOT NULL PRIMARY KEY,
   department_name VARCHAR(255) NOT NULL
);

CREATE TABLE employees(
   id SERIAL NOT NULL PRIMARY KEY,
   employee_name VARCHAR(255) NOT NULL,
   department_id INT REFERENCES departments
);

是否在上创建索引 employees (department_id) 提高上的性能 SELECT 的,就像 SELECT * FROM employees WHERE department_id = 123 ,假设1千个部门和1万名员工?

虽然我可以回答外键索引的好处 DELETE 是的,我不确定这个推理是否适用于 选择 s

1 回复 | 直到 3 年前

foudfou 3 年前

至少如果我创建索引并生成一些随机数据:

CREATE INDEX ON employees (department_id);

INSERT INTO departments(department_name)
SELECT md5(random()::text) FROM generate_series(1, 1000);
INSERT INTO employees(employee_name, department_id)
SELECT md5(random()::text), floor(random() * 1000 + 1)::int FROM generate_series(1, 10000);

我可以看到索引被使用了:

> explain select * from employees where department_id = 100;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Bitmap Heap Scan on employees  (cost=4.35..32.11 rows=9 width=41)
   Recheck Cond: (department_id = 100)
   ->  Bitmap Index Scan on employees_department_id_idx  (cost=0.00..4.35 rows=9 width=0)
         Index Cond: (department_id = 100)
(4 rows)

> explain select * from employees join departments on employees.department_id = departments.id where department_id = 100;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Nested Loop  (cost=4.63..40.50 rows=9 width=78)
   ->  Index Scan using departments_pkey on departments  (cost=0.28..8.29 rows=1 width=37)
         Index Cond: (id = 100)
   ->  Bitmap Heap Scan on employees  (cost=4.35..32.11 rows=9 width=41)
         Recheck Cond: (department_id = 100)
         ->  Bitmap Index Scan on employees_department_id_idx  (cost=0.00..4.35 rows=9 width=0)
               Index Cond: (department_id = 100)
(7 rows)

因此,答案是: 对 ,我可能应该总是在外键上创建一个索引。

我还注意到,由于数据很少(两个表中都有<10行),因此不使用索引( Seq Scan ).

推荐文章

Aaron Green · 我的python程序无法识别数据库的存在,即使它在那里

1 年前

kaiselwyvrn · 我无法打开Visual Studio项目

1 年前

4SadMemeXD · 无法在Django中选择相关类别

2 年前

Agrim Singh · 用户数据。名称未显示用户名

2 年前

Peloucvo · 用数据库中的数据填充JTable

2 年前

Montaser Majid · 用于从多行中提取单行的SQL查询

2 年前

Max J. · 用整数作为键将dict写入csv

2 年前

YaddyVirus · NextJS API中where子句中的未知列

2 年前

David542 · 正确与否

2 年前

Trash at Coding · 为什么我的查询中的AND子句被忽略(非常简单的SQL查询)

2 年前