本文介绍了如何在分布式环境下实现高性能的连接查询优化算法，包括数据导入Mysql数据库、测试数据处理、查重和数据结构设计，重点是针对特定规模的数据集进行功能性和非功能性要求的实现。
一、实现要求
- 整体要求
基于指定的数据规模、数据集，实现一种分布式数据库领域的高性能连接查询优化算法，完成要求的性能测试，并进行效果演示。
- 功能性要求
1、在单台机器上启动参赛者程序，共启动4个实例。
2、4个实例各自加载50万零件数据和大约1500万行订单数据到内存中(每个实例加载不同的部分，记录加载时间)。
3、程序可以指定有效的零件品牌参数，非法的零件品牌报错。对于有效的零件品牌参数，完成如下要求的功能并计算时间：
(1)计算每个零件的销售总量、所有零件的销售总量并得出零件平均销售量；
(2)对于满足用户指定的零件品牌的零件，如果该零件销售总量小于(1)中的平均销售量的30%，则认定为非畅销零件。
注意：零件品牌的格式为Brand#MN，其中M、N都是在1-5中任意选择，如Brand#12。
(3)计算并输出该品牌的所有非畅销零件的销售总额。
- 非功能性要求
实例间通过IP+端口方式进行通信（模拟多个节点），不得采用共享内存方式进行通信。
- 演示要求
1、 启动4个实例后，在任意一个实例的控制台界面输入load表示开始加载数据，然后在各控制台界面显示各实例自己加载的2个表的各自的数据量和数据加载的时间；
2、 然后输入select，表示开始执行查询，Brand#MN总共有25种情况，程序自动依次从Brand#11开始到Brand#55结束，显示这25种情况下的查询结果和消耗的查询时间，最后显示出总的执行时间。
二、测试数据
测试数据生成下载地址
运行如下命令生成比例因子为10的part表和lineitem表的文本测试数据：
.\dbgen.exe -s 10 -T P
.\dbgen.exe -s 10 -T L
上述命令生成part.tbl和lineitem.tbl这两个文本文件，文本文件总大小约为8G左右，其中part.tbl包括200万行零件数据，lineitem.tbl包含接近6000万条订单数据。
注意：dists.dss需要和dbgen.exe在相同目录下
在linux操作系统中，dbgen.exe无法运行且没找到合适解决办法，因此在windows操作系统中生成测试数据，拷贝到linux系统中使用。
cd /dbgen
vim makefile.suite
生成后如下：
其中，只有箭头指向的两个文件才是我们所需要的。
- part表(零件数据)：
P_PARTKEY：零件号，唯一，整数类型
P_NAME：零件名称，唯一，字符串长度不超过55字节
P_MFGR：制造商名称，字符串长度不超过25字节
P_BRAND：零件品牌，字符串长度不超过10字节
P_TYPE：零件类型， 字符串长度不超过25字节
P_SIZE：零件尺寸，整数
P_CONTAINER：容器名称，字符串长度不超过10字节
P_RETAILPRICE：零件价格，浮点数
P_COMMENT：备注，字符串长度不超过23字节
其中的每一列数据用 | 符号隔开
- lineitem表
L_ORDERKEY：订单号，整数
L_PARTKEY：零件号，整数，引用part表d的P_PARTKEY
L_SUPPKEY：供应商号，整数
L_LINENUMBER：订单细目号，整数
L_QUANTITY：零件数量，整数
L_EXTENDEDPRICE：零件价格，浮点数
L_DISCOUNT：零件折扣，浮点数，本课题不考虑折扣
L_TAX：税率，浮点数，本课题不考虑税
L_RETURNFLAG：订单退回标志，字符串，1字节
L_LINESTATUS：订单状态，字符串，1字节
L_SHIPDATE：发货日期，日期类型
L_COMMITDATE：提交日期，日期类型
L_RECEIPTDATE：接收日期，日期类型
L_SHIPINSTRUCT：发货指令，字符串不超过25字节
L_SHIPMODE：发货方式，字符串不超过10字节
L_COMMENT：备注，字符串不超过44字节
三、两个.tbl测试文件导入Mysql数据库
- 开启mysql服务：
sudo service mysql start
- 登陆mysql：
mysql -u root -p #然后根据提示输入用户密码
- 创建数据库
create database tpch; #tpch是数据库的名字
查看创建成功没(查看所有建立的数据库)：
show databases;
进入创建的这个数据库：
use tpch;
- 创建表
part表的建表语句：
CREATE TABLE part ( P_PARTKEY INTEGER NOT NULL,
P_NAME VARCHAR(55) NOT NULL,
P_MFGR CHAR(25) NOT NULL,
P_BRAND CHAR(10) NOT NULL,
P_TYPE VARCHAR(25) NOT NULL,
P_SIZE INTEGER NOT NULL,
P_CONTAINER CHAR(10) NOT NULL,
P_RETAILPRICE DECIMAL(15,2) NOT NULL,
P_COMMENT VARCHAR(23) NOT NULL );
lineitem表的建表语句：
CREATE TABLE lineitem ( L_ORDERKEY INTEGER NOT NULL,
L_PARTKEY INTEGER NOT NULL,
L_SUPPKEY INTEGER NOT NULL,
L_LINENUMBER INTEGER NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL);
检查一下创建成功没有：
show tables;
- 将本地文件导入mysql
查看文件路径(part.tbl和lineitem.tbl)：
pwd
根据上述指令的结果，执行导入数据库命令，其中的文件路径就是刚刚的两个表的绝对路径：
load data local infile '/home/cherry/IOT-finall-work/part.tbl' into table part fields terminated by '|' lines terminated by '|\n';
会发现报错：
- 解决：
查看是否开启加载本地文件
show variables like 'local_infile';
看到果然是off，再开启全局本地文件设置：
set global local_infile=on;
再查看已经打开了。
再执行一遍load指令，如果发现还是解决不了，出现新问题(在为on的前提下)：
则exit退出mysql，通过如下命令重新登录：
mysql -u root -p --local-infile
然后选择tpch数据库，再执行一遍load指令，发现导入成功(200万行)，花费时间是15.21秒：
同样方法导入lineitem表：
在执行过程中，出现了根目录已满的情况，于是百度扩展根目录，发现自己的磁盘空间未分配的与根目录空间不连续，因此无法扩展。没办法删除ubuntu废了九牛二胡之力重新下载，给根目录分配了70G的空间，在回来执行一遍以上命令。
并且回来执行的时候，发现改load part表时候的指令不能用，只能导入一行，因此把lineitem.tbl每一行最后一个|字符删掉，然后执行：
load data local infile '/home/cherry/IOT-finall-work/lineitem.tbl' into table lineitem fields terminated by '|' lines terminated by '\n';
发现导入成功(约6000万行)，也没有warning，花费时间是：
四、查重，以便编写数据结构
P_PARTKEY、P_NAME唯一，不需要额外考虑；
P_MFGR：5；
P_BRAND：25；
P_TYPE：150；
P_SIZE：50；
P_RETAILPRICE：31681； // 也不用额外考虑，嵌入数据结构中
P_CONTAINER：40；
P_COMMENT：805936；// 也不用额外考虑，嵌入数据结构中
以此发现，part表中好几列的数据重复较高，因此可以将其存储到一组数组中，在结构体中，之拼接其在数组中的下标即可。
五、part数据结构
- 连接到数据库，存储重复数据
首先连接到数据库，将表中的重复数据存储下来，使用select distinct ***语句。linux下c++连接mysql数据库代码：
// 头文件
#include<mysql/mysql.h>
// 连接数据库
bool ConnectDataBase(MYSQL &mysql){
// 初始化文件句柄
mysql_init(&mysql);
// 设置字符编码格式
mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "utf8");
// 连接到数据库
MYSQL *ret = mysql_real_connect(&mysql, "localhost", "root", "mysql123", "tpch", 3306, NULL, 0);
if(ret == NULL){
cout << "数据库连接失败！原因：" << mysql_error(&mysql) << endl;
return false;
} else{
cout << "数据库连接成功！" << endl;
return true;
}
}
注意编译指令，要加上连接到库的 ‘-lmysqlclient’
g++ test.cpp -o test -lmysqlclient
运行结果：
说明运行成功！
下面连接到数据库，将高重复度不同数据存储下来：
// P_MFGR 提取之后存到string mfgr中，直接根据mfgr[size - 1]转换成数字后-1，拼接到elseinfo中
string part_mfgr[5] = {"Manufacturer#1", "Manufacturer#2", "Manufacturer#3", "Manufacturer#4", "Manufacturer#5"};
unordered_set<string> part_type; // 零件类型50个
set<float> part_size;
unordered_set<string> part_container;
// 记录数据库中两个表高覆盖率的列数据
bool DownloadData(MYSQL &mysql){
MYSQL_RES* res; // 查询结果集
MYSQL_ROW row; // 记录数组
int ret;
char sql_ptype[] = "select distinct P_TYPE from part;";
ret = mysql_real_query(&mysql, sql_ptype, (unsigned long)strlen(sql_ptype));
if(ret){
cout << "P_TYPE数据查询失败！失败原因:" << mysql_error(&mysql) << endl;
return false;
}
// 获取结果数据集
res = mysql_store_result(&mysql);
while(row = mysql_fetch_row(res))
part_type.emplace(row[0]);
// 释放查询结果
mysql_free_result(res);
char sql_psize[] = "select distinct P_SIZE from part;";
ret = mysql_real_query(&mysql, sql_psize, (unsigned long)strlen(sql_psize));
if(ret){
cout << "P_SIZE数据查询失败！失败原因:" << mysql_error(&mysql) << endl;
return false;
}
// 获取结果数据集
res = mysql_store_result(&mysql);
while(row = mysql_fetch_row(res))
part_size.emplace(stof(row[0]));
// 释放查询结果
mysql_free_result(res);
char sql_pcontainer[] = "select distinct P_CONTAINER from part;";
ret = mysql_real_query(&mysql, sql_pcontainer, (unsigned long)strlen(sql_pcontainer));
if(ret){
cout << "P_CONTAINER数据查询失败！失败原因:" << mysql_error(&mysql) << endl;
return false;
}
// 获取结果数据集
res = mysql_store_result(&mysql);
while(row = mysql_fetch_row(res))
part_container.emplace(row[0]);
// 释放查询结果
mysql_free_result(res);
return true;
}
- 读取part.tbl文件，数据存入结构体
// 读part文件数据，并解析
void Part::loadPartFileData(int serial){
downloadData();
ifstream file;
file.open("/home/cherry/IOT-finall-work/part.tbl");
if(!file.is_open()){
cout << "Error! 文件打开失败！" << endl;
exit(0);
}
string brand, type, container;
int size, index; // 重复数据下标
// 用以分割字符串
string line, word;
char delim = '|';
serial = (serial - 1) * 500000;
while(getline(file, line) && count<500000){
if(serial > 0){
// 实现空转
serial--;
continue;
}
part part_data;
// 1. 读入数据并解析
// P_PARTKEY / P_NAME / P_RETAILPRICE / P_COMMENT直接存入数组，其余的将下标单独存入P_ELSEINFO。
istringstream iss(line);
// 按照part表属性列的顺序逐个解析
if (getline(iss, word, delim))
part_data.P_PARTKEY = stoi(word);
if (getline(iss, word, delim))
part_data.P_NAME = word;
if (getline(iss, word, delim)) // P_MFGR
part_data.P_ELSEINFO += word[word.size()-1]; // 倒数第一个字符就是下标
if (getline(iss, word, delim))
brand = word;
if (getline(iss, word, delim))
type = word;
if (getline(iss, word, delim))
size = (short)stoi(word);
if (getline(iss, word, delim))
container = word;
if (getline(iss, word, delim))
P_RETAILPRICE.push_back(stof(word));
if (getline(iss, word, delim))
part_data.P_COMMENT = word;
// 2. 解析重复数据下标
// P_TYPE
vector<string>::iterator it = find(P_TYPE.begin(), P_TYPE.end(), type);
index = (int)distance(P_TYPE.begin(), it); // 在数组中的下标
// 宽度对齐3
if(index <= 9)
part_data.P_ELSEINFO += "00";
else if(index >=10 && index <= 99)
part_data.P_ELSEINFO += "0";
part_data.P_ELSEINFO += to_string(index);
// P_SIZE
vector<short>::iterator s_it = find(P_SIZE.begin(), P_SIZE.end(), size);
if(s_it == P_SIZE.end())
cout << "miss";
index = (int)distance(P_SIZE.begin(), s_it);
// 宽度对齐2
if(index <= 9)
part_data.P_ELSEINFO += "0";
part_data.P_ELSEINFO += to_string(index);
// P_CONTAINER
it = find(P_CONTAINER.begin(), P_CONTAINER.end(), container);
index = (int)distance(P_CONTAINER.begin(), it);
// 宽度对齐2
if(index <= 9)
part_data.P_ELSEINFO += "0";
part_data.P_ELSEINFO += to_string(index);
// 解析brand数组下标
index = ((brand[brand.size()-2] - '0') - 1) * 5 + (brand[brand.size()-1] - '0'); // 转换成1~25
P_BRAND[index].push_back(part_data);
count++;
}
file.close();
}
- 零件part的数据结构
Part.h
class Part{
private:
typedef struct {
int P_PARTKEY;
string P_NAME;
string P_COMMENT;
string P_ELSEINFO; // 存储其他为包含的信息所在数组的下标
/*
P_ELSEINFO[0] : P_MFGR 5个 1～5
P_ELSEINFO[1]~P_ELSEINFO[3] : P_TYPE 150个
P_ELSEINFO[4]~P_ELSEINFO[5] : P_SIZE 50个 自创62进制:0~9=0~9; A~Z=10~35(ascii码-55); a~z=36~61(ascii-61)
P_ELSEINFO[6]~P_ELSEINFO[7] : P_CONTAINER 40个 同上62进制
*/
}part;
// 零售信息
vector<float> P_RETAILPRICE;
// 高覆盖度数据
// P_MFGR 提取之后存到string mfgr中，直接根据mfgr[size-1]转换成数字后，拼接到elseinfo中，因此第一个位置留空
string P_MFGR[6] = {"", "Manufacturer#1", "Manufacturer#2", "Manufacturer#3", "Manufacturer#4", "Manufacturer#5"};
vector<string> P_TYPE;
vector<short> P_SIZE;
vector<string> P_CONTAINER;
MYSQL mysql; // 连接数据库指针
int count = 0; // 记录数据个数
void connectMysql(); // 连接数据库获取数据
public:
// 将数据按照Brand#11~Brand#55划分成25份，存储下标 (class中定义ector并初始化大小应按照如下方式)
vector<vector<part>> P_BRAND{vector<vector<part>>(BRANDSIZE+1)};
void downloadData(); // 加载重复数据
void printPTYPEData();
void printPSIZEData();
void printPCONTAINERData();
void loadPartFileData(int); // 加载part.tbl文件数据
void printPartData(); // 按brand分类打印part表数据
float getPartPrice(int partkey) {return P_RETAILPRICE[partkey-1];}
int getBrandNumber(string); // 判断输入的品牌名称是否有效，有效将MN转化成1~25，无效返回0
};
没写完懒得写了，完整代码在gitee。
扫一扫
5714
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
