python建立数据库实现_python实现简易数据库之一—

最近没事做了一个数据库project，要求实现一个简单的数据库，能满足几个特定的查询，这里主要介绍一下我们的实现过程，代码放在过ithub，可参看这里。都说python的运行速度很慢，但因为时间比较急，工作量大，我们还是选择了高效实现的python。

一、基本要求

1、设计存储方式

测试的数据量大小为1.5GB，最大的表有6,001,215条记录。最大限度减少I/O次数，减少磁盘占有空间。

2、实现和优化group by，order by

对大表进行group by 聚集、排序，提高查询效率

3、实现和优化TOP k

高效率实现TOP k

4、对插入不作要求

二、总体设计

由于要求主要是对查询做优化，对插入删除不作考虑，完全可以采用列式数据库，发挥列式数据库的优势。

以下是主要框架（忽略了建表和插入数据的过程），图中蓝色箭头表示生成流，橘黄色箭头表示执行流。

整个过程其实是至上而下建立索引，从下往上执行查询，具体细节在详细设计中讲述。

三、详细设计

为了实现高效查询，我将表的各个列从表中抽取出来，单独存放，在查询时，只读取所需要的列，不需要读取原始记录，可以大大的减少I/O次数，但对于select * 这样的查询语句需要读取其他属性，因此我采用行式和列式结合的方式。

1、创建表，生成元数据

在插入记录之前需要建表，确定表的格式和完整性约束，如执行一下建表操作：

create table NATION (N_NATIONKEY int primary key,N_NAME varchar(25),

N_REGIONKEYint,N_COMMENT varchar(152),foreign key (N_REGIONKEY) references REGION(R_REGIONKEY));

将生成一个meta.table的元数据文件，该元数据文件第一行保存的是数据库的所有表名，以下的每一行为一个表的详细描述，格式如下：

[表名1]|...|[表名n]

[表名1]|[表1主键]|[表1第一个属性，约束]|[表1第二个属性，约束]|...|[[表1第N个属性，约束]]

测试数据共有8个表，REGION，NATION，LINEITEM，ORDERS，CUSTOMER，PARTSUPP，PART，SUPPLIER。示例如下（省略了一些表的描述）：

#meta.table:

REGION|R_REGIONKEY |R_REGIONKEY INT|R_NAME VARCHAR(25)|R_COMMENT VARCHAR(152)

建立元数据的作用，是在查询处理时可以知道某个表的某个属性在原记录文件中的列号，以及该属性属于什么类型，要知道对属性排序和比较时必须知道属性类型。因此原数据表meta.table的属性必须按原记录的顺序保存。该数据表常驻内存，以python的有序字典（map）在内存中存放：

{'NATION': OrderedDict([('N_NATIONKEY', 'INT'), ('N_NAME', 'VARCHAR(25)'), ('N_REGIONKEY', 'INT'), ('N_COMMENT', 'VARCHAR(152)'), ('primary', ['N_NATIONKEY'])]),'REGION': OrderedDict([('R_REGIONKEY', 'INT'), ('R_NAME', 'VARCHAR(25)'), ('R_COMMENT', 'VARCHAR(152)'), ('primary', ['R_REGIONKEY'])]),...

}

2、插入记录

元数据建立好后，可以进行插入数据，由于时间有限，插入数据时我没有进行完整性检查，假设插入的记录都是合法的，整个插入过程完成后，数据记录如下(REGION表)：

#REGION

0|AFRICA|lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to |

1|AMERICA|hs use ironic, even requests. s|

2|ASIA|ges. thinly even pinto beans ca|

3|EUROPE|ly final courts cajole furiously final excuse|

4|MIDDLE EAST|uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl|

属性之间用‘|’分割，在抽取属性列之前，记录文件不能压缩，我们将在生成列索引时压缩这个原始记录。

3、抽取属性列（同时建立记录行号与行地址的对应表）

将表中的每个属性单独抽取出来，格式为：

[属性值0]|[行号0]

[属性值1]|[行号1]

[属性值2]|[行号2]

抽取的列示例如下：

#REGION_R_NAME

上面的示例是REGION表中的R_NAME属性的列，后来发现行号可以不用保存，读取到数组中后，数组下标就是行号，这样可以节省一些空间，不过，这个属性列是按原始记录的顺序存放，不能实现按块读取，当记录数很多时，不能放入内存，因此这个属性列文件在下一步之后可以删掉。

抽取列的同时还要完成两个工作，第一个是压缩原始记录表，压缩后原始记录不再需要，读记录只需要读压缩记录即可；第二个就是建立行号到压缩后的行首地址的对应表，这样以后的操作都是按行号进行。在扫描原记录文件的每行时，写压缩文件保存压缩记录表，并记录压缩后的每一行的首地址（获得压缩后的地址在这里）。行号与行首地址的对应表格式如下：

[第0行首地址]

[第1行首地址]

[第2行首地址]

行号与行首地址的对应表示例如下：

#REGION

127

171

212

269

用行号代替行地址有，可以节省空间，单个表文件只要大于3*2^32B=12GB（乘以3是因为压缩比率约为3:1），字节地址就超过就超过long int能表示的范围，而行号可以表示更大的表；另一个好处，如果后续需要建立位图索引，用行号比行地址好，因为行号是连续的整数，而行地址是离散在整数空间中，如上面的示例行地址从0直接跳到了127，中间的一串整数都没有用到，那建立的位图索引将是相当稀疏的。

行首地址在查询中不会用到，只有在最后读取原始记录时才需要转换为行号，因此可以将它进行压缩，我们用gzib压缩，gzib为我们提供一个透明的文件压缩，所谓透明，就是像读写普通文件一样，gzib自动在缓冲区进行压缩和解压。

python写压缩文件主要代码如下：

importgzip

condenseFile= gzip.open(os.path.join(path,fileName+".gz"),'wb',compresslevel = 4)#以二进制写，压缩等级为4，值越大压缩率越高，但时间越长

block = '...'condenseFile.write(block)

condenseFile.flush()

condenseFile.close()

读压缩文件：

path = os.path.join(DATABASE,"line2loc")

with gzip.open(os.path.join(path,fileName),'rb') as transFile:

locations= transFile.read().split("\n")#也可以只读以行 transFile.readline()

transFile.close()

4、属性列压缩并建立分块索引

上一步我们得到的属性列只是简单从表中抽取出来，这样的属性列有很多冗余，比如一个表有10000行，某个属性只有10个取值，那在属性列中就需要保存保存10000行，我们可以按属性值进行分组，记录出现该属性值得行号，格式如下：

[属性值0]|[出现该值的行0]|[出现该值的行1]|...|[出现该值的行n]

[属性值1]|[出现该值的行0]|[出现该值的行1]|...|[出现该值的行n]

接下来按属性值排序，这样得到的属性列就有序了，排序的过程中需要用到外部排序。排序后的结果，示例如下：

#PARTSUPP_PS_SUPPLYCOST

1.0|81868|307973|409984|490169|620444|632371

1.01|25328|36386|172687|243808|287934|558840|774633|775920

1.02|108457|137974|175055|206681|246824|297497|374608

1.03|38563|117772|175895|289935|381497|486960|630290|644984|723651|726647

1.04|284511|314284|327411|392035|639283|721325|754065|783577.

.6.5|193020|436686|746401

6.51|46883|59908|129012|189045|398695|437094|455012|458310|490801|598787

6.52|54123|129198|145810|223578|336148|377020|377755|379426|430717|442844|500296|549401

6.53|32341|54384|149844|208256|437181|528380

6.54|7164|41427|377948|417213|432345|625698|652283|757838

上面的示例截取自PARTSUPP表的PS_SUPPLYCOST。排序之后，我们就可以对它进行分块读取，为了节省空间和减少I/O次数，我们对这个属性表进行分块压缩，并在块上建立索引，我们把属性表称为一级索引，这个在块上的索引称为二级索引。实现时，我们以32KB为一块，不过实际操作时我们的块大于等于32KB，我们依次将各行添加到一个字符串string中，每添加一行我们都会检查string的是否大于等于32*1024B，如果小于32KB，就继续添加一行；直到大于等于32KB，将string写压缩文件，同时记录压缩后的大小，保存该块的首地址和块大小，然后清空string，开始记录下一个块。实现的主要代码如下：

1 scwf = open(os.path.join(scddir,fileName),"w")2 wf = gzip.open(os.path.join(sortdir,fileName+".gz"),'wb',compresslevel = 4)#压缩属性表

3 block = ""

4 newblock = True#新块标志

5 for k in li:#li存放的是有序的属性值和行号表

6 if newblock ==True:7 blockattr = str(k[0])#块首属性值

8 newblock =False9 line = str(k[0])+"|"

10 for loc in k[1]:11 line += loc+"|"

12 block += line+"\n"

13 if len(block) >BLOCKSIZE:14 startloc =endloc15 wf.write(block)16 endloc =wf.tell()17 size = endloc -startloc18 scwf.write(blockattr+SPLITTAG+str(startloc)+SPLITTAG+str(size)+'\n')#保存块头的属性值，块首地址和块大小

19 block = ""#块清空

20 newblock = True#新块，下次循环记住新块首部属性值

压缩后生成二级索引，二级索引示例如下：

1.0|0|32812

6.52|32812|32810

12.03|65622|32800

17.46|98422|32835

22.92|131257|32787

28.39|164044|32771

33.77|196815|32794

39.29|229609|32810

44.7|262419|32843

这两级索引的示意图如下：

上面的示例中，第一行表示该属性值1.0-6.52为一块，块内的属性值在[1.0,6.52)之间，块的起始地址为0，块大小为32812B，二级索引也是有序的，因此建立二级索引后，我们可以在二级和一级索引上都进行折半查找，查询速度很快。

整个测试数据压缩后的一级索引列表：

对原始记录表进行压缩后，不能指定抽取属性列建立索引，只能同时对一个表的所有属性建立索引，这在实际应用中有很大缺陷，因为有一些属性根本就不会再查询条件中使用，建立的索引浪费了磁盘空间，也延长了建立索引的时间。虽然设计了压缩原始记录表，但最后我们实现没有压缩原始记录表，行到地址的对应表存的是原始记录的行首部地址。原始记录文件不会删除，这样可以指定表和属性建立索引。

到此，自顶向下的存储和索引一级建立好了，下一篇将介绍SQL语句解析和查询处理。

python建立数据库实现_python实现简易数据库之一——存储和索引建立