@SovietPower 2022-05-01T23:03:37.000000Z 字数 20856 阅读 1109

数据库系统实验5 存储与索引

DB

数据库系统实验5 存储与索引

概念

Database (数据库)

是面向应用的数据的集合
应用与DBMS建立连接时需指定所使用的数据库

Tablespace (表空间)

用于定义数据库存储位置（哪个文件夹），一个数据库服务包含两个初始表空间
pg_default: 默认用于存储用户表、索引、临时表等(../data/base)
pg_global: 系统表(../data/global)

Schema (模式)

是对数据库中内容的逻辑划分，任何一个数据库实际命名为schema_name.object_name。
未指定schema_name时，系统按search_path顺序查找(show search_path)
如若search_path为"$user", public，则表示先找与当前用户名同名的schema，再找publicschema。
public 是数据库默认的模式名，创建的表默认使用该模式。建议为每个用户创建与用户名相同的模式名（初始时并没有）。
可通过指定模式名的方式访问数据库中不同模式下的数据对象（如果权限允许）

表空间管理命令

创建表空间
可指定owner，其拥有该表空间的所有权限。
要指定的相对位置是相对安装目录下的位置var/lib/opengauss/data。可指定最大大小。

CREATE TABLESPACE tablespace_name [ OWNER user_name ]
RELATIVE LOCATION 'directory' [ MAXSIZE 'space_size' ] [ with_option_clause ];

重命名表空间

ALTER TABL ESPACE tablespace_name RENAME TO new_tablespace_name;

修改表空间所有者

ALTER TABLESPACE tablespace_name OWNER TO new_owner;

修改表空间限额

ALTER TABL ESPACE tablespace_name RESIZE MAXSIZE { UNLIMITED I'space_size'};

删除表空间
删除前表空间中不能有任何数据库对象

DROP TABLESPACE [ IF EXISTS ] tablespace_name;

模式管理命令

用指定名称创建模式

CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ schema_element [...] ];

用用户名创建模式

CREATE SCHEMA AUTHORIZATION username [ schema_element [...] ];

修改模式的名称

ALTER SCHEMA schema_name RENAME TO new_name;

修改模式的所有者

ALTER SCHEMA schema_name OWNER TO new_owner;

删除模式
若该模式下有其它对象，CASCADE为全删掉；RESTRICT为不允许删除该模式。

DROP SCHEMA [IF EXISTS ] schema_name [, ...] [ CASCADE | RESTRICT ];

索引

CREATE [UNIQUE] INDEX [ CONCURRENTLY ] [ [schema_name.]index_name ] ON table_name [ USING method ]
({{ col_name | ( expression)} [COLLATE collation ][ opclass ][ ASC | DESC][ NULLS { FIRST | LAST}]}[,..])
[WITH ( {storage_parameter = value}[,..])]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ];

unique表示不允许该索引值有重复。CONCURRENTLY为并行创建。

method：

btree：类似于B+树
hash：hash索引
gin：倒排索引
gist：适用于几何和地理等多维数据
Psort：针对列存表进行局部排序索引
ubtree：多版本B-tree索引

DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] index name [,..]
[ CASCADE | RESTRICT ];

查看索引：

select * from pg_indexes where [schemaname='...' and ] tablename='...';

实验

首先运行之前创建的openGauss容器，连接数据库db2022。

PS H:\> docker exec -it opengauss3 bash
root@97375c3acbd1:/# su omm
omm@97375c3acbd1:/$ gsql -d db2022 -r

创建一个名为 myspace 的表空间

db2022=# create tablespace myspace RELATIVE LOCATION 'tablespace/myspace';
CREATE TABLESPACE

创建一个名为 myschema 的模式

同时在该模式下创建数据表 mytable 和视图 myview。

db2022=# create schema myschema authorization gxb
db2022-# create table mytable (id int, value int, primary key (id))
db2022-# create view myview as select * from public.takes;
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mytable_pkey" for table "mytable"
CREATE SCHEMA

练习在查询中使用模式

记录每条语句返回的结果并解释其完成的操作

db2022=# show search_path;
  search_path
----------------
 "$user", public
(1 row)
当前模式的search_path
db2022=# insert into mytable values(1,100);
ERROR:  relation "mytable" does not exist on gaussdb
LINE 1: insert into mytable values(1,100);
之前创建的表位于 myschema，但当前 search_path 不包含 myschema，故不指定 myschema 则找不到该表。
                    ^
db2022=# create table mytable (id int, value int, primary key (id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mytable_pkey" for table "mytable"
CREATE TABLE
创建表 public.mytable。
db2022=# insert into mytable values(1,100);
INSERT 0 1
search_path 包含 public，故可找到表 public.mytable。
db2022=# insert into myschema.mytable values(1,100);
INSERT 0 1
指定模式，故可找到表 myschema.mytable。
db2022=# SET search_path TO myschema,public;
SET
修改 search_path。
db2022=# insert into mytable values(2,200);
INSERT 0 1
因为 myschema 包含该表，所以向表 myschema.mytable 中插入。
db2022=# insert into myschema.mytable values(2,200);
ERROR:  duplicate key value violates unique constraint "mytable_pkey"
DETAIL:  Key (id)=(2) already exists.
重复插入。此时 myschema.mytable 就是 mytable。
db2022=# insert into public.mytable values(2,200);
INSERT 0 1
此时使用 public.mytable 需指定模式名 public。

观察索引对查询性能的影响

记录每条语句返回的结果并解释其完成的操作

db2022=# create table takes (like public.takes) tablespace myspace;
CREATE TABLE
创建 myschema.takes，结构同之前的takes，使用自定义表空间。
db2022=# insert into takes select * from public.takes;
INSERT 0 30094
导入之前的 takes 中的数据
db2022=# select * from pg_indexes where schemaname='public' and tablename='takes';
 schemaname | tablename | indexname  | tablespace |                                                     indexdef
------------+-----------+------------+------------+---------------------------------------------------------------------
----------------------------------------------
 public     | takes     | takes_pkey |            | CREATE UNIQUE INDEX takes_pkey ON takes USING btree (id, course_id,
sec_id, semester, year) TABLESPACE pg_default
(1 row)
之前的 takes 包含各字段上的 btree 索引
db2022=# select * from pg_indexes where schemaname='myschema' and tablename='takes';
 schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-----------+------------+----------
(0 rows)
新创建的 takes 不含索引
db2022=# \timing on
Timing is on.
显示每一次 sql 执行所花的时间
db2022=# select * from myschema.takes where id='79446' and course_id='748' and sec_id='1' and semester='Fall' and year=2
003;
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 79446 | 748       | 1      | Fall     | 2003 | A
(1 row)
Time: 99.561 ms
db2022=# select * from public.takes where id='79446' and course_id='748' and sec_id='1' and semester='Fall' and year=200
3;
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 79446 | 748       | 1      | Fall     | 2003 | A
(1 row)
Time: 45.003 ms
该单值查询中，有索引的表比无索引的表快1倍多

新建索引再次对比（在两个表中，后续的查询均比之前的查询快得多，可能是缓存了？但索引依旧有效）

db2022=# create unique index takes_pk on myschema.takes (id,course_id,sec_id,semester,year) tablespace myspace;
CREATE INDEX
Time: 411.531 ms
在新表中也建相同的索引。
db2022=# select * from myschema.takes where id='79446' and course_id='748' and sec_id='1' and semester='Fall' and year=2
003;
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 79446 | 748       | 1      | Fall     | 2003 | A
(1 row)
Time: 1.068 ms
db2022=# select * from public.takes where id='79446' and course_id='748' and sec_id='1' and semester='Fall' and year=200
3;
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 79446 | 748       | 1      | Fall     | 2003 | A
(1 row)
Time: 1.008 ms
此时两个表中查询时间基本一致。
db2022=# drop index myschema.takes_pk;
DROP INDEX
Time: 239.175 ms
删除索引
db2022=# select * from myschema.takes where id='79446' and course_id='748' and sec_id='1' and semester='Fall' and year=2003;
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 79446 | 748       | 1      | Fall     | 2003 | A
(1 row)
Time: 4.761 ms
db2022=# select * from public.takes where id='79446' and course_id='748' and sec_id='1' and semester='Fall' and year=2003;
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 79446 | 748       | 1      | Fall     | 2003 | A
(1 row)
Time: 0.885 ms
删除索引后，两表间查询时间又有了明显差距

观察索引对更新性能的影响

记录每条语句返回的结果并解释其完成的操作。
可能是电脑问题，用时浮动比较大。

无索引更新：

db2022=# insert into myschema.takes values ('00000','000','0','Fall',2022,'Z');
INSERT 0 1
Time: 208.899 ms

对每个属性建立btree索引：
用时为无索引的3到4倍。

db2022=# create index takes_1 on myschema.takes (id) tablespace myspace;
CREATE INDEX
Time: 567.284 ms
db2022=# create index takes_2 on myschema.takes (course_id) tablespace myspace;
CREATE INDEX
Time: 347.021 ms
db2022=# create index takes_3 on myschema.takes (sec_id) tablespace myspace;
CREATE INDEX
Time: 383.941 ms
db2022=# create index takes_4 on myschema.takes (semester) tablespace myspace;
CREATE INDEX
Time: 453.346 ms
db2022=# create index takes_5 on myschema.takes (year) tablespace myspace;
CREATE INDEX
Time: 787.605 ms
db2022=# create index takes_6 on myschema.takes (grade) tablespace myspace;
CREATE INDEX
Time: 2343.505 ms
db2022=#  select * from pg_indexes where schemaname='myschema' and tablename='takes';
 schemaname | tablename | indexname | tablespace |                                 indexdef
------------+-----------+-----------+------------+----------------------------------------------------------------------
----
 myschema   | takes     | takes_1   | myspace    | CREATE INDEX takes_1 ON takes USING btree (id) TABLESPACE myspace
 myschema   | takes     | takes_2   | myspace    | CREATE INDEX takes_2 ON takes USING btree (course_id) TABLESPACE mysp
ace
 myschema   | takes     | takes_3   | myspace    | CREATE INDEX takes_3 ON takes USING btree (sec_id) TABLESPACE myspace
 myschema   | takes     | takes_4   | myspace    | CREATE INDEX takes_4 ON takes USING btree (semester) TABLESPACE myspa
ce
 myschema   | takes     | takes_5   | myspace    | CREATE INDEX takes_5 ON takes USING btree (year) TABLESPACE myspace
 myschema   | takes     | takes_6   | myspace    | CREATE INDEX takes_6 ON takes USING btree (grade) TABLESPACE myspace
(6 rows)
Time: 102.131 ms
db2022=# insert into myschema.takes values ('11111','111','1','Fall',2022,'Z');
INSERT 0 1
Time: 5397.037 ms
db2022=# delete myschema.takes where id='11111' and course_id='111';
DELETE 1
Time: 545.066 ms
db2022=# insert into myschema.takes values ('11111','111','1','Fall',2022,'Z');
INSERT 0 1
Time: 290.331 ms
删除之后重新插入会更快，可能也是缓存的问题？
db2022=# insert into myschema.takes values ('11111','1110','1','Fall',2022,'Z');
INSERT 0 1
Time: 829.721 ms
db2022=# insert into myschema.takes values ('11111','1120','1','Fall',2022,'Z');
INSERT 0 1
Time: 767.316 ms

对每个属性建立hash索引：
用时依旧为无索引的3到4倍。

db2022=# create index takes_7 on myschema.takes using hash (id) tablespace myspace;
CREATE INDEX
Time: 994.802 ms
db2022=# create index takes_8 on myschema.takes using hash (course_id) tablespace myspace;
CREATE INDEX
Time: 675.056 ms
db2022=# create index takes_9 on myschema.takes using hash (sec_id) tablespace myspace;
CREATE INDEX
Time: 624.564 ms
db2022=# create index takes_10 on myschema.takes using hash (semester) tablespace myspace;
CREATE INDEX
Time: 639.372 ms
db2022=# create index takes_11 on myschema.takes using hash (year) tablespace myspace;
CREATE INDEX
Time: 356.669 ms
db2022=# create index takes_12 on myschema.takes using hash (grade) tablespace myspace;
CREATE INDEX
Time: 3122.687 ms
db2022=# insert into myschema.takes values ('22222','222','2','Fall',2022,'Z');
INSERT 0 1
Time: 723.869 ms
db2022=# insert into myschema.takes values ('22221','227','2','Fall',2022,'Z');
INSERT 0 1
Time: 845.770 ms

对每个属性建立hash索引：
用时依旧为无索引的3到4倍。
前几次 insert 用时浮动极大，不知道原因。

db2022=# create index takes_13 on myschema.takes (id,course_id) tablespace myspace;
CREATE INDEX
Time: 2344.680 ms
db2022=# create index takes_14 on myschema.takes (course_id,sec_id) tablespace myspace;
CREATE INDEX
Time: 1309.072 ms
db2022=# create index takes_15 on myschema.takes (sec_id,semester) tablespace myspace;
CREATE INDEX
Time: 1117.506 ms
db2022=# create index takes_16 on myschema.takes (semester,year) tablespace myspace;
CREATE INDEX
Time: 1349.810 ms
db2022=# create index takes_17 on myschema.takes (year,grade) tablespace myspace;
CREATE INDEX
Time: 625.121 ms
db2022=# create index takes_18 on myschema.takes (grade,id) tablespace myspace;
CREATE INDEX
Time: 2183.103 ms
db2022=# insert into myschema.takes values ('33333','333','3','Fall',2022,'Z');
INSERT 0 1
Time: 4466.036 ms
db2022=# insert into myschema.takes values ('33444','355','4','Fall',2022,'Z');
INSERT 0 1
Time: 103.846 ms
db2022=# insert into myschema.takes values ('44444','555','4','Fall',2022,'Z');
INSERT 0 1
Time: 378.363 ms
db2022=# insert into myschema.takes values ('55555','555','4','Fall',2022,'Z');
INSERT 0 1
Time: 803.185 ms
db2022=# insert into myschema.takes values ('66666','666','1','Fall',2022,'Z');
INSERT 0 1
Time: 836.411 ms
db2022=# insert into myschema.takes values ('11166','116','3','Fall',2022,'Z');
INSERT 0 1
Time: 837.816 ms

删除本实验中所创建的模式和表空间

db2022=# drop schema myschema;
ERROR:  cannot drop schema myschema because other objects depend on it
DETAIL:  table mytable depends on schema myschema
view myview depends on schema myschema
table takes depends on schema myschema
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Time: 873.420 ms
db2022=# drop tablespace myspace;
ERROR:  tablespace "myspace" is not empty
Time: 2811.570 ms
db2022=# drop schema myschema CASCADE;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to table mytable
drop cascades to view myview
drop cascades to table takes
DROP SCHEMA
Time: 1311.816 ms
db2022=# drop tablespace myspace;
DROP TABLESPACE
Time: 3746.748 ms
db2022=# select count(*) from myschema.takes;
ERROR:  schema "myschema" does not exist
LINE 1: select count(*) from myschema.takes;
                             ^
Time: 0.941 ms

索引性能测试

测试维度

索引类型：无索引、唯一索引（btree或hash）、非唯一索引（btree或hash）
查询结果集：单条（等值查询），少量（<10）或大量（>1,000）（范围查询）
数据集规模：100、10,000、1,000,000

测试环境

db2022=# show server_version;
 server_version
----------------
 9.2.4
(1 row)
db2022=# select version();
          version
 (openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:04 commit 0 last mr   on x86_64-unknown-linux-gnu, compil
ed by g++ (GCC) 7.3.0, 64-bit
(1 row)

数据集
使用：prereq包含100条数据，takes包含约3w条数据，mytakes包含约100w条数据。
100条数据的不测了，比较麻烦，结论基本就是随数据集规模增长，索引查询优势会更大。
为方便删除index，使用新建的与takes相同的表takes1。

下面是mytakes的生成方式，会包含大量id或course_id相同的元组。

db2022=# create table mytakes (like takes);
CREATE TABLE
Time: 5100.183 ms
db2022=# insert into mytakes select * from takes;
INSERT 0 30094
Time: 1527.305 ms
db2022=# create table takes1 (like takes);
CREATE TABLE
Time: 1974.941 ms
db2022=# insert into takes1 select * from takes;
INSERT 0 30094
Time: 466.372 ms
db2022=# update takes1 set course_id = course_id||'x';
UPDATE 30094
Time: 334.094 ms
db2022=# insert into mytakes select * from takes1;
INSERT 0 30094
Time: 2277.785 ms
db2022=# delete takes1;
DELETE 30094
Time: 12980.146 ms
db2022=# insert into takes1 select * from takes;
INSERT 0 30094
Time: 11817.119 ms
db2022=# update takes1 set course_id = course_id||'y';
UPDATE 30094
Time: 13041.597 ms
db2022=# insert into mytakes select * from takes1;
INSERT 0 30094
Time: 15906.420 ms
db2022=# delete takes1
db2022-# ;
DELETE 30094
Time: 18279.577 ms
db2022=# insert into takes1 select * from mytakes;
INSERT 0 90282
db2022=# update takes1 set sec_id=sec_id||'a';
UPDATE 90282
db2022=# insert into mytakes select * from takes1;
INSERT 0 90282
Time: 36853.872 ms
db2022=# update takes1 set sec_id=sec_id||'b';
UPDATE 90282
Time: 654.162 ms
db2022=# insert into mytakes select * from takes1;
INSERT 0 90282
Time: 532.678 ms
db2022=# delete takes1;
DELETE 90282
Time: 3595.527 ms
db2022=# select count(*) from mytakes;
 count
--------
 270846
(1 row)
Time: 50.027 ms
db2022=# insert into takes1 select * from mytakes;
INSERT 0 270846
Time: 6825.950 ms
db2022=# update takes1 set sec_id=sec_id||'c';
UPDATE 270846
db2022=# insert into mytakes select * from takes1;
INSERT 0 270846
db2022=# update takes1 set sec_id=sec_id||'d';
UPDATE 270846
db2022=# insert into mytakes select * from takes1;
INSERT 0 270846
db2022=# update takes1 set sec_id=sec_id||'e';
UPDATE 270846
db2022=# insert into mytakes select * from takes1;
INSERT 0 270846
db2022=# select count(*) from mytakes;
  count
---------
 1083384
(1 row)

无索引

3w条数据测试等值查询

db2022=# select * from pg_indexes where tablename='takes1';
 schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-----------+------------+----------
(0 rows)
Time: 2.366 ms
db2022=# select * from takes1 where id='28361' and course_id='960' and sec_id='1';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 28361 | 960       | 1      | Fall     | 2009 | C+
(1 row)
Time: 65.290 ms
db2022=# select * from takes1 where id='259' and course_id='692' and sec_id='1';
 id  | course_id | sec_id | semester | year | grade
-----+-----------+--------+----------+------+-------
 259 | 692       | 1      | Spring   | 2010 | B-
(1 row)
Time: 67.276 ms

3w条数据测试范围查询

db2022=# select * from takes1 where course_id='476';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 44551 | 476       | 1      | Fall     | 2010 | C-
 51008 | 476       | 1      | Fall     | 2010 | B+
...
Time: 1076.589 ms
db2022=# select * from takes1 where course_id='949';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 49450 | 949       | 1      | Fall     | 2007 | A-
 54728 | 949       | 1      | Fall     | 2007 | C
...
Time: 887.121 ms

100w条数据测试等值查询

db2022=# select * from mytakes where id='37339' and course_id='237y' and sec_id='1abc';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 37339 | 237y      | 1abc   | Spring   | 2008 | B
(1 row)
Time: 192.314 ms
db2022=# select * from mytakes where id='37339' and course_id='237y' and sec_id='1abcd';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 37339 | 237y      | 1abcd  | Spring   | 2008 | B
(1 row)
Time: 193.495 ms

100w条数据测试范围查询

db2022=# select * from mytakes where course_id='486y';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 123   | 486y      | 1      | Fall     | 2009 | C
 827   | 486y      | 1      | Fall     | 2009 | B
 86573 | 486y      | 1      | Fall     | 2009 | B
...
Time: 1230.652 ms
db2022=# select * from mytakes where course_id='704y';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 55940 | 704y      | 1      | Spring   | 2008 | B-
 17128 | 704y      | 1      | Spring   | 2008 | C+
 60366 | 704y      | 1      | Spring   | 2008 | B
...
Time: 850.035 ms

唯一btree索引

3w条数据测试等值查询

db2022=# create unique index takes1_pk on takes1 (id,course_id,sec_id,semester,year);
CREATE INDEX
Time: 446.259 ms
db2022=# select * from pg_indexes where tablename='takes1';
 schemaname | tablename | indexname | tablespace |                                                     indexdef
------------+-----------+-----------+------------+----------------------------------------------------------------------
---------------------------------------------
 public     | takes1    | takes1_pk |            | CREATE UNIQUE INDEX takes1_pk ON takes1 USING btree (id, course_id, s
ec_id, semester, year) TABLESPACE pg_default
(1 row)
Time: 12.272 ms
db2022=# select * from takes where id='28361' and course_id='960' and sec_id='1';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 28361 | 960       | 1      | Fall     | 2009 | C+
(1 row)
Time: 1.225 ms
db2022=# select * from takes where id='259' and course_id='692' and sec_id='1';
 id  | course_id | sec_id | semester | year | grade
-----+-----------+--------+----------+------+-------
 259 | 692       | 1      | Spring   | 2010 | B-
(1 row)
Time: 1.095 ms

3w条数据测试范围查询

db2022=# select * from takes where course_id='476';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 44551 | 476       | 1      | Fall     | 2010 | C-
 51008 | 476       | 1      | Fall     | 2010 | B+
...
Time: 290.466 ms
db2022=# select * from takes where course_id='949';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 49450 | 949       | 1      | Fall     | 2007 | A-
 54728 | 949       | 1      | Fall     | 2007 | C
...
Time: 200.423 ms

100w条数据测试等值查询

db2022=# select * from pg_indexes where tablename='mytakes';
 schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-----------+------------+----------
(0 rows)
Time: 3.418 ms
db2022=# create unique index mytakes_pk on mytakes (id,course_id,sec_id,semester,year);
CREATE INDEX
Time: 11262.171 ms
db2022=# select * from mytakes where id='37339' and course_id='237y' and sec_id='1abc';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 37339 | 237y      | 1abc   | Spring   | 2008 | B
(1 row)
Time: 1.326 ms
db2022=# select * from mytakes where id='37339' and course_id='237y' and sec_id='1abcd';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 37339 | 237y      | 1abcd  | Spring   | 2008 | B
(1 row)
Time: 0.888 ms

100w条数据测试范围查询

db2022=# select * from mytakes where course_id='486y';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 123   | 486y      | 1      | Fall     | 2009 | C
 827   | 486y      | 1      | Fall     | 2009 | B
 86573 | 486y      | 1      | Fall     | 2009 | B
...
Time: 1238.876 ms
db2022=# select * from mytakes where course_id='704y';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 55940 | 704y      | 1      | Spring   | 2008 | B-
 17128 | 704y      | 1      | Spring   | 2008 | C+
 60366 | 704y      | 1      | Spring   | 2008 | B
...
Time: 779.980 ms

非唯一btree索引

3w条数据测试等值查询

db2022=# drop index takes1_pk;
DROP INDEX
Time: 824.120 ms
db2022=# create index takes1_pk on takes1 (id,course_id,sec_id,semester,year);
CREATE INDEX
Time: 1577.301 ms
db2022=# select * from pg_indexes where tablename='takes1';
 schemaname | tablename | indexname | tablespace |                                                  indexdef
------------+-----------+-----------+------------+----------------------------------------------------------------------
--------------------------------------
 public     | takes1    | takes1_pk |            | CREATE INDEX takes1_pk ON takes1 USING btree (id, course_id, sec_id,
semester, year) TABLESPACE pg_default
(1 row)
Time: 4.266 ms
db2022=# select * from takes where id='28361' and course_id='960' and sec_id='1';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 28361 | 960       | 1      | Fall     | 2009 | C+
(1 row)
Time: 0.942 ms
db2022=# select * from takes where id='259' and course_id='692' and sec_id='1';
 id  | course_id | sec_id | semester | year | grade
-----+-----------+--------+----------+------+-------
 259 | 692       | 1      | Spring   | 2010 | B-
(1 row)
Time: 0.969 ms

3w条数据测试范围查询

db2022=# select * from takes where course_id='476';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 44551 | 476       | 1      | Fall     | 2010 | C-
 51008 | 476       | 1      | Fall     | 2010 | B+
...
Time: 321.893 ms
db2022=# select * from takes where course_id='949';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 49450 | 949       | 1      | Fall     | 2007 | A-
 54728 | 949       | 1      | Fall     | 2007 | C
...
Time: 351.266 ms

100w条数据测试等值查询

db2022=# drop index mytakes_pk;
DROP INDEX
Time: 526.671 ms
db2022=# select * from pg_indexes where tablename='mytakes';
 schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-----------+------------+----------
(0 rows)
Time: 2.022 ms
db2022=# create index mytakes_pk on mytakes (id,course_id,sec_id,semester,year);
CREATE INDEX
Time: 15270.766 ms
db2022=# select * from mytakes where id='37339' and course_id='237y' and sec_id='1abc';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 37339 | 237y      | 1abc   | Spring   | 2008 | B
(1 row)
Time: 1.112 ms
db2022=# select * frommytakes where id='37339' and course_id='237y' and sec_id='1abcd';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 37339 | 237y      | 1abcd  | Spring   | 2008 | B
(1 row)
Time: 0.926 ms

100w条数据测试范围查询

db2022=# select * from mytakes where course_id='486y';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 123   | 486y      | 1      | Fall     | 2009 | C
 827   | 486y      | 1      | Fall     | 2009 | B
 86573 | 486y      | 1      | Fall     | 2009 | B
...
Time: 999.292 ms
db2022=# select * from mytakes where course_id='704y';
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 55940 | 704y      | 1      | Spring   | 2008 | B-
 17128 | 704y      | 1      | Spring   | 2008 | C+
 60366 | 704y      | 1      | Spring   | 2008 | B
...
Time: 774.836 ms

总结

范围查询的用时与结果有关，取两次平均值。
因为结果比较简单，就不做图了。

3w数据集

查询方式	无索引	唯一索引 (无索引比例)	非唯一索引 (无索引比例)
等值查询	66	1.16 (1.76%)	0.95 (1.44%)
范围查询	981	245 (25.0%)	336 (34.25%)

100w数据集

查询方式	无索引	唯一索引 (无索引比例)	非唯一索引 (无索引比例)
等值查询	192	1.11 (0.58%)	1.02 (0.53%)
范围查询	1040	1010 (97.1%)	887 (85.3%)

结论
索引可大幅提高查询效率，但影响更新效率，且当结果集很大时，与无索引查询的差距会变小。
唯一索引和非唯一索引没有明显差别。

读取性能：
唯一索引：查到目标值后，即可停止查询，返回结果。
普通索引：查到目标值后，继续查找直至与目标值不相等时，停止查询，返回结果。
对比：每次查询，都将磁盘中的一个数据页读取到内存中，故即使唯一索引只查询一个值，也要读取整个数据页，所以两者读取性能相差不大。

更新性能：
唯一索引：将数据页读入内存（因为要判断有没有冲突，故不能直接用 change buffer），更新数据。
普通索引：将更新的数据缓存到 change buffer 中，更新结束，后续执行vmerge。
对比：除了更新完后要立刻查询的操作，普通索引因为不需要读入内存，性能更优.
故一般更推荐用普通索引。

数据库系统 实验5 存储与索引

概念

表空间管理命令

模式管理命令

索引

实验

创建一个名为 myspace 的表空间

创建一个名为 myschema 的模式

练习在查询中使用模式

观察索引对查询性能的影响

观察索引对更新性能的影响

删除本实验中所创建的模式和表空间

索引性能测试

无索引

唯一btree索引

非唯一btree索引

总结

内容目录

数据库系统实验5 存储与索引