西北工业大学2019数据库实验课参考

实验十 一、优化上面的实时评估表。进行下列操作。 1. 利用设置实时评估数据库的说明，构建并填充这些表格。 表格构建填充在第一大题最后。 2.当存储表格到数据库的时候，分析表格的存储特征。 你将提交最正确的评估每个表格的元组数量以及用于存储每个表格的磁盘的页数。 提交收集这些信息的必要代码以及这些查询的输出。对于每个表格， 你以什么为块因子进行计算？ --创建表 --drop table customer_lot; --drop table customer; --drop table lot; create table customer( customer_id integer, customer_first_name varchar(100), customer_last_name varchar(100)); create table lot( lot_id integer, lot_description varchar(100), lot_size float, lot_district integer, lot_value float, lot_street_address varchar(100)); create table customer_lot( customer_id integer, lot_id integer) ; --导入数据 --数据目录为 'F:\\pgdata' copy lot from 'F:\\pgdata\\lot.out'; copy customer from 'F:\\pgdata\\customer.out'; copy customer_lot from 'F:\\pgdata\\customer_lot.out'; --查询 --table lot --input select relname,reltuples,relpages from pg_class where relname='lot'; --output 行 relname reltuples relpages 1 lot 249999 2688 --table customer --input select relname,reltuples,relpages from pg_class where relname='customer'; --output 行 relname reltuples relpages 1 customer 249999 1441 --table customer_lot --input select relname,reltuples,relpages from pg_class where relname='customer_lot'; --output 行 relname reltuples relpages 1 customer_lot 249999 1107 lot的块因子为：249999/2688=93 customer的块因子为：249999/1441=173 customer_lot的块因子为：249999/1107=226 3.分析上面给出的每个查询的运行时特征。 报告PostgreSQL的评估，用于磁盘访问的实际次数以及每次查询花费时间的总计。 提交收集这些数据的必要代码。 （1）给定一个大小范围，选出所有范围内的地段号lot_id。 该类查询的实例应该是select lot_id from lot where lot_size between 300 and 15000； --input explain analyze select lot_id from lot where lot_size between 300 and 15000; --output 行 QUERY PLAN text 1 Seq Scan on lot (cost=0.00..6437.98 rows=147272 width=4) (actual time=0.279..286.029 rows=147220 loops=1) 2 Filter: ((lot_size >= '300'::double precision) AND (lot_size <= '15000'::double precision)) 3 Rows Removed by Filter: 102779 4 Planning Time: 0.848 ms 5 Execution Time: 296.828 ms （2）给定一个值的范围，选出所有范围内的地段号lot_id。 该类查询的实例应该是select lot_id from lot where lot_value between 300 and 15000； --input explain analyze select lot_id from lot where lot_value between 300 and 15000; --output 行 QUERY PLAN text 1 Gather (cost=1000.00..6272.57 rows=3787 width=4) (actual time=0.745..211.929 rows=3733 loops=1) 2 Workers Planned: 1 3 Workers Launched: 1 4 -> Parallel Seq Scan on lot (cost=0.00..4893.87 rows=2228 width=4) (actual time=0.026..26.492 rows=1867 loops=2) 5 Filter: ((lot_value >= '300'::double precision) AND (lot_value <= '15000'::double precision)) 6 Rows Removed by Filter: 123133 7 Planning Time: 0.170 ms 8 Execution Time: 212.086 ms （3）选出一个指定用户的所有信息。 该类查询的实例应该是select * from customer where customer_id=12； --input explain analyze select * from customer where customer_id=12; --output 行 QUERY PLAN text 1 Gather (cost=1000.00..4279.33 rows=1 width=16) (actual time=0.564..117.202 rows=1 loops=1) 2 Workers Planned: 1 3 Workers Launched: 1 4 -> Parallel Seq Scan on customer (cost=0.00..3279.23 rows=1 width=16) (actual time=0.031..22.666 rows=1 loops=2) 5 Filter: (customer_id = 12) 6 Rows Removed by Filter: 124999 7 Planning Time: 0.503 ms 8 Execution Time: 117.219 ms （4）插入新的用户或地段数据。 该类查询的实例应该是insert into customer values (250001, 'Vince', 'Smith' ); --input explain analyze insert into customer values (250001, 'Vince', 'Smith' ); --output 行 QUERY PLAN text 1 Insert on customer (cost=0.00..0.01 rows=1 width=440) (actual time=0.410..0.410 rows=0 loops=1) 2 -> Result (cost=0.00..0.01 rows=1 width=440) (actual time=0.001..0.001 rows=1 loops=1) 3 Planning Time: 0.023 ms 4 Execution Time: 0.442 ms （5）删除一行用户或地段数据。 该类查询的实例应该是delete from customer where customer_id='250001'; --input explain analyze delete from customer where customer_id='250001'; --output 行 QUERY PLAN text 1 Delete on customer (cost=0.00..4565.99 rows=1 width=6) (actual time=23.335..23.335 rows=0 loops=1) 2 -> Seq Scan on customer (cost=0.00..4565.99 rows=1 width=6) (actual time=23.285..23.285 rows=1 loops=1) 3 Filter: (customer_id = 250001) 4 Rows Removed by Filter: 249999 5 Planning Time: 0.101 ms 6 Execution Time: 24.066 ms （6）更新一行用户或地段数据。 该类查询的实例应该是update customer set customer_first_name='Vinny' where customer_id='249001'; --input explain analyze update customer set customer_first_name='Vinny' where customer_id='249001'; --output 行 QUERY PLAN text 1 Update on customer (cost=0.00..4565.99 rows=1 width=234) (actual time=40.626..40.626 rows=0 loops=1) 2 -> Seq Scan on customer (cost=0.00..4565.99 rows=1 width=234) (actual time=40.371..40.498 rows=1 loops=1) 3 Filter: (customer_id = 249001) 4 Rows Removed by Filter: 249998 5 Planning Time: 0.075 ms 6 Execution Time: 40.670 ms （7）选出所有点段的平均地段大小。 该类查询的实例应该是select avg(lot_size) from lot; --input explain analyze select avg(lot_size) from lot; --output 行 QUERY PLAN text 1 Finalize Aggregate (cost=5526.34..5526.35 rows=1 width=8) (actual time=127.301..127.301 rows=1 loops=1) 2 -> Gather (cost=5526.23..5526.34 rows=1 width=32) (actual time=96.243..132.057 rows=2 loops=1) 3 Workers Planned: 1 4 Workers Launched: 1 5 -> Partial Aggregate (cost=4526.23..4526.24 rows=1 width=32) (actual time=47.794..47.794 rows=1 loops=2) 6 -> Parallel Seq Scan on lot (cost=0.00..4158.58 rows=147058 width=8) (actual time=0.008..19.772 rows=125000 loops=2) 7 Planning Time: 0.102 ms 8 Execution Time: 132.191 ms 4.对于上面每一个查询，如果可以，建议使用一个索引以提高查询性能。 要完整地回答这个问题，你必须描述出你编入索引的列以及你将使用的索引。 你必须用一个完整的解释来充分地为你的选择进行辩护。如果一个索引不适合这个查询，那么要清晰地解释原因。 注意，你可以只使用PostgreSQL支持的索引，例如, hash或b-tree。你也可以用聚类。 查询（1）和查询（2）的查询都是对于特定值的范围查询，默认使用的顺序扫描查找。即使建立b-tree索引也可能不会使用，因为查询范围过大。 查询（3）是根据给出条件查询相等的customer id。可以建立hash索引帮助查询。 查询（4）是一个插入操作，不需要建立索引。但是如果查询（2）建立了b-tree查询的话，查询（3）性能可能会下降。 查询（5）和查询（6）默认顺序扫描查找。通过customer id和lot id可以试用hash索引来提高性能。 查询（7）是一个聚合函数，对表lot执行顺序扫描。 以上索引，可以通过下列sql语句进行构建： create index customer_id_index on customer using hash(customer_id); create index lot_id_index on lot using hash(lot_id); create index lot_value_index on lot using btree(lot_value); create index lot_size_index on lot using btree(lot_size); lot_value索引可以使用lot_value_index聚合在lot上。 The lot_size索引可以使用lot_size_index聚合在lot上。 5.实现那些你之前提出的索引并且分析步骤3中运行的呢些查询的运行时性能。 提交一个表，显示出使用索引后，评估的和实际总的磁盘页的访问数以及查询运行时间。 基于运行时间，计算出由引表格导致的性能的增加或降低的百分比。 如果性能没有因为索引而得到提高，那么你必须解释原因。 你可以用下面的表格作为基础。 （1）给定一个大小范围，选出所有范围内的地段号lot_id。 该类查询的实例应该是select lot_id from lot where lot_size between 300 and 15000； --input explain analyze select lot_id from lot where lot_size between 300 and 15000