数据库系统 实验6

DB

实验要求

• 熟悉数据库管理系统中查询处理的基本流程，不同查询算子的功能；

• 掌握openGauss或MySQL数据库管理系统中EXPLAIN的相关语法；

• 针对具体查询场景画出查询树，分析查询开销，针对可能存在的性能问题提出解决方案。

实验过程记录

1. 在Dokcer环境中启动之前实验所创建的openGauss容器；

2. 通过PowerShell连接到数据库db2022；

3. sql执行以下语句，获取并解释该查询的执行计划；

   explain select name from instructor where dept_name = 'Biology';
   explain analyze select name from instructor where dept_name = 'Biology';
   explain performance select name from instructor where dept_name = 'Biology';

4. 执行以下语句，获取并解释该查询的执行计划；

   explain select * from takes where id= '79446';
   explain analyze select * from takes where id= '79446';
   explain performance select * from takes where id= '79446';

5. 执行以下语句，获取并解释该查询的执行计划；

   explain select distinct course.course_id, title from course, takes where course.course_id = takes.course_id and id = '13403';
   explain analyze select distinct course.course_id, title from course, takes where course.course_id = takes.course_id and id = '13403';
   explain performance select distinct course.course_id, title from course, takes where course.course_id = takes.course_id and id = '13403';

6. 执行以下语句，获取并解释该查询的执行计划；

   explain select id, sum(credits) from ( select distinct id, course.course_id, credits from course, takes where course.course_id = takes.course_id and grade <> 'F' ) group by id;
   explain analyze select id, sum(credits) from ( select distinct id, course.course_id, credits from course, takes where course.course_id = takes.course_id and grade <> 'F' ) group by id;
   explain performance select id, sum(credits) from ( select distinct id, course.course_id, credits from course, takes where course.course_id = takes.course_id and grade <> 'F' ) group by id;

课程项目查询分析

• 创建课程项目所使用的数据库环境，并导入全量的数据集；
• 针对课程项目任务5中所设计查询，使用EXPLAIN进行分析；
  
  • 画出查询计划树，并说明每个节点的功能和执行时间等信息；
  • 说明该执行计划是否是最优的；
  • 针对可能存在的性能问题，提出解决方案；

==每一个人负责3个查询语句，完成如下内容==

画出查询计划树，并说明每个节点的功能和执行时间等信息

• 画出查询树，分析流程，参考老师上课的PPT

说明该执行计划是否是最优的

• 思考其他的查询方案并对比分析
• 详细阐述理由

针对可能存在的性能问题，提出解决方案

可参考方法有：

• 先执行Filter；
• 对某属性建立索引，
• 采用类似于Zone Map的方式，对每个组别保存一些组别信息，这样能够加快访问速度

查看粉丝列表 （我）

SELECT follower_id, username FROM follow join user
WHERE follow.follower_id=user.user_id and follow.followee_id=[user_id]
ORDER BY follow_time DESC;

查看历史点赞（我）

select * from scenery_card
where card_id in
    (select card_id from like
    where user_id = [user_id])

推送关注用户的卡片（我）

select card_id, user_id, title, score, likes
from scenery_card
where user_id in
    (select followee_id from follow
    where follower_id = [user_id])
order by create_time desc
limit ([page_number]-1)*[page_size], [page_size];

查看关注列表（瑾瑜）

SELECT followee_id, username FROM follow join user
WHERE follow.followee_id=user.user_id and follow.follower_id=[user_id]
ORDER BY follow_time DESC;

景点类型搜索（瑾瑜）

select * from scenery where tag_id in (select tag_id from tag_table where tag like concat('%', [tag], '%'))
limit ([page_number]-1)*[page_size], [page_size];

匹配景点（瑾瑜）

select * from scenery
where province=[province] and city=[city] and district=[district]
    and (scenery_name like concat('%', [keyword], '%') or [keyword] like concat('%', scenery_name, '%'))
limit ([page_number]-1)*[page_size], [page_size];

查看我的收藏（晓博）

select * from scenery_card
where card_id in
    (select card_id from bookmark
    where user_id = [user_id])

景点搜索（晓博）

select * from scenery
where province=[province] and ([city] is null or city=[city] and district=[district]
    and (scenery_name like concat('%', [keyword], '%') or [keyword] like concat('%', scenery_name, '%'))
limit ([page_number]-1)*[page_size], [page_size];

推送地址附近的卡片（晓博）

with user_address(province, city, district) as
select (card_id, user_id, title, score, likes)
from scenery_card
where scenery_id in
    (select scenery_id
    from scenery natural join (select province, city, district from user
    where user_id = [user_id]) as user_address)
order by create_time desc
limit ([page_number]-1)*[page_size], [page_size];

存在的问题及解决方案

• MySQL只支持Nested Loop Join这一种join算法，而不支持Hash join和Sort Merge Join。

• MySQL的排序算法：

  1. 若排序内容能全部放入内存，则仅在内存中使用快速排序；

  2. 若排序内容不能全部放入内存，则分批次将排好序的内容放入文件，然后将多个文件进行归并排序

  3. 若排序中包含limit语句，则使用堆排序优化排序过程

实验小结

通过使用openGauss或MySQL数据库管理系统中EXPLAIN的相关语法，分析了数据库管理系统中查询处理的基本流程，了解了不同查询算子的功能；在对模型寻找并生成合适的数据之后，针对具体查询场景画出查询树，分析查询开销，针对可能存在的性能问题提出解决了方案。