数据管理技术-Lab-7

数据管理技术-Lab7实验报告

TASK 1 索引

环境MYSQL

1. 准备一个包含400万条记录的表A，包含三个数字型字段：1）自增长的字段id； 2）可能的取值有500万个的字段sparse； 3）可能的取值有10个的字段dense。
2. 基于A复制出三个表（B、C、D）。
3. A表建立主键id，没有其它索引； B表，没有主键，在sparse、dense字段上建立一个多列索引；C表，建立主键id，和dense字段上的索引；D表没有任何索引和主键。

A表即为上一次实验创建的表tablea。

建表以及数据复制如下代码：

drop table if exists tableB;
create table tableB(
    id int,
    sparse int null ,
    dense int null ,
    index index_mult_columns(sparse, dense)
);

drop table if exists tableC;
create table tableC(
    id int primary key ,
    sparse int null ,
    dense int null ,
    index index_column(dense)
);

drop table if exists tableD;
create table tableD(
    id int ,
    sparse int null ,
    dense int null
);

insert into tableB select * from tableA;
insert into tableC select * from tableA;
insert into tableD select * from tableA;

Q1: 查询id字段大于2000000小于3000000的记录条数

查询语句：

select count(*) from tableA where id > 2000000 and id < 3000000;
select count(*) from tableB where id > 2000000 and id < 3000000;
select count(*) from tableC where id > 2000000 and id < 3000000;
select count(*) from tableD where id > 2000000 and id < 3000000;

结果：

分析：因为表A C拥有主键id，因此查询速度快，其与两表查询速度接近。

Q2:查询dense段大于5的记录条数

查询语句：

select count(*) from tableA where dense > 5;
select count(*) from tableB where dense > 5;
select count(*) from tableC where dense > 5;
select count(*) from tableD where dense > 5;

结果：

分析：tableC拥有dense的索引，查询较快，tableA因为有主键，自动创建聚集索引，因此查询A表时查询引擎会更快地定位和检索满足条件的数据。而表B因为是多列索引，其排序方式与单列索引不同，因此查询速度较慢。

Q3:查询sparse字段小于150000的记录条数

查询语句：

select count(*) from tableA where sparse < 150000;
select count(*) from tableB where sparse < 150000;
select count(*) from tableC where sparse < 150000;
select count(*) from tableD where sparse < 150000;

结果：

分析：表A原理同Q2，表B因为具有sparse的索引因此查询较快，表C由于索引对表的排序而使其查询较无索引快一些。

Q4: 查询sparse字段小于150000，dense段大于5的记录条数

查询语句：

select count(*) from tableA where sparse < 150000 and dense > 5;
select count(*) from tableB where sparse < 150000 and dense > 5;
select count(*) from tableC where sparse < 150000 and dense > 5;
select count(*) from tableD where sparse < 150000 and dense > 5;

结果：

分析：表A原理同Q2，表B由于具有多列索引因此查询快，表C由于索引不足以覆盖查询，因此查询返回的数据比索引的数据更多，需要访问更多的磁盘块，因此查询很慢。

Task 2 触发器

环境MYSQL

1. 建表： fruits（fid， fname， price）， sells（fid，cid， sellTime， quantity），customer （cid， cname， level)，在fruits表和customer 表插入至少一条数据。

2. 写个sells表触发器，当插入新的用户购买记录时，检查该用户购买的总价值（每种水果价格 * 销售量的和 ）超过1万元就设置customer表的level为VIP，超过2万元设置为SVIP，低于1万元则置为normal。

3. 若是删除或修改sells表记录，也重新计算并重置客户的level值。

4. 通过DML语句进行触发器效果验证, 如插入/更新/删除交易记录，查看用户等级变化。

TIPS: 使用存储过程将重复流程简化

1.建表

create table fruits(
  fid int primary key ,
  fname varchar(20) ,
  price int
);

create table customer(
  cid int primary key ,
  cname varchar(20) ,
  level varchar(20)
);

create table sells(
  fid int ,
  cid int ,
  sellTime date ,
  quantity int
);

fruits中插入数据：

customer中插入数据:

2.创建触发器

create trigger add_sell
after insert on sells
for each row
begin
    declare total_money int ;
    select sum(sells.quantity * fruits.price) into total_money
    from sells inner join fruits on sells.fid = fruits.fid
    where cid = NEW.cid;
    if total_money < 10000 then
        update customer set level = 'normal' where cid = NEW.cid;
    else if total_money >= 10000 and total_money < 20000 then
        update customer set level = 'VIP' where cid = NEW.cid;
    else
        update customer set level = 'SVIP' where cid = NEW.cid;
    end if;
    end if;
end;

create trigger delete_sell
    after delete on sells
    for each row
begin
    declare total_money int ;
    select sum(sells.quantity * fruits.price) into total_money
    from sells inner join fruits on sells.fid = fruits.fid
    where cid = OLD.cid;
    if total_money < 10000 then
        update customer set level = 'normal' where cid = OLD.cid;
    else if total_money >= 10000 and total_money < 20000 then
        update customer set level = 'VIP' where cid = OLD.cid;
    else
        update customer set level = 'SVIP' where cid = OLD.cid;
    end if;
    end if;
end;

create trigger update_sell
    after update on sells
    for each row
begin
    declare total_money int ;
    select sum(sells.quantity * fruits.price) into total_money
    from sells inner join fruits on sells.fid = fruits.fid
    where cid = NEW.cid;
    if total_money < 10000 then
        update customer set level = 'normal' where cid = NEW.cid;
    else if total_money >= 10000 and total_money < 20000 then
        update customer set level = 'VIP' where cid = NEW.cid;
    else
        update customer set level = 'SVIP' where cid = NEW.cid;
    end if;
    end if;
end;

3.DML语句验证

insert into sells values (1, 1, '2021-01-02', 10);

结果如下

insert into sells values (1, 1, '2021-01-02', 1000);

结果如下

再执行上述语句一次，结果如下

执行删除语句，删除购买数量为1000的数据

delete from sells where quantity = 1000;

结果如下

执行修改语句，将购买数量10改为1000,

update sells set quantity = 1000 where quantity = 10;

结果如下