The simplest implementation of database and data structure

Original: http://www.ruanyifeng.com/blog/2014/07/database_implementation.html

The simplest implementation of a database

Nanyi

Date: July 4, 2014

In all applications, the database can be the most complex.

MySQL's manual has more than 3,000 pages, and PostgreSQL's manual has more than 2000 pages, and Oracle's manuals are even thicker than they add.

But it's not hard to write a simple database of your own. Reddit There is a post, only hundreds of words, the principle is clear. Here's what I've organized based on this post.

Data is saved in text form.

The first step is to write the data you want to save to a text file. This text file is your database.

In order to facilitate reading, the data must be divided into records, each record length is specified as equal. For example, assuming that the length of each record is 800 bytes, the 5th record begins at 3200 bytes.

Most of the time, we don't know where a record is in the first place, only the value of the primary key (primary key) is known. At this point in order to read data, can be a comparison of records. However, this is inefficient, in practice, the database often uses B-tree (b-tree) format to store data.

Second, what is B-tree?

To understand a B-tree, you must start with a two-fork search tree.

Binary search tree is a kind of data structure with very high efficiency, it has three characters.

(1) Each node has a maximum of two sub-trees.

(2) The left dial hand tree is less than the parent node value, and the right subtree is greater than the parent node value.

(3) The target value is found in n nodes, usually only the log (n) comparison is required.

The structure of the binary lookup tree is not suitable for the database because its lookup efficiency is related to the number of layers. The more data is in the lower level, the more comparisons need to be made. In extreme cases, n data needs to be compared in n times to find a target value. For the database, every entry to a layer, to read the data from the hard disk, which is very deadly, because the hard disk read time is much larger than the data processing time, the database read the hard disk the less the better.

The B-tree is an improvement on the two-fork search tree. It is designed to focus the data as much as possible so that multiple data can be read at once, reducing the number of hard drive operations.

There are also three characteristics of B-trees.

(1) A node can hold multiple values. For example, a maximum of one node holds 4 values.

(2) No new layers are added unless the data is filled. In other words, the B-tree pursues the "layer" the less the better.

(3) The value in the child node, with the value in the parent node, has a strict size correspondence relationship. In general, if the parent node has a value, then there is a a+1 child node. For example, the parent node has two values (7 and 16), corresponding to three child nodes, the first child node is a value less than 7, the last child node is a value greater than 16, and the middle child node is the value between 7 and 16.

This data structure is very helpful in reducing the number of times the hard drive is read. Assuming that a node can hold 100 values, the 3-layer B-tree can hold 1 million data, and if you switch to a two-fork lookup tree, you need 20 layers! Assuming that the operating system reads one node at a time and the root node remains in memory, the B-tree looks for the target value in 1 million data and only needs to read the hard disk two times.

Third, index

The database is stored in the B-tree format and only resolves the problem of finding data by "primary key". If you want to find another field, you need to index it.

An index is a B-tree file that is a keyword in a field. Suppose you have an employee table that contains an employee number (primary key) and a name of two fields. You can index a name, which stores the name in the B-tree format, followed by its location in the database (that is, the first few records). When looking for a name, the corresponding first record is found in the index and then read from the table.

This index lookup method, called "Indexed Sequential access Method" (Indexed sequential access Methods), is abbreviated as ISAM. It already has many implementations (such as the C-isam Library and the D-isam library), so you can write your own simplest database by using the codebase.

Iv. Advanced Features

After you have deployed the most basic data access (including indexes), you can implement some advanced features.

(1) The SQL language is the universal operating language of the database, so a SQL parser is required to parse the SQL command into the corresponding ISAM operation.

(2) database connection (join) refers to the two tables of the database through the "foreign key", establish a connection relationship. You need to optimize this operation.

(3) database transaction (transaction) refers to a series of database operations in bulk, as long as one step is unsuccessful, the entire operation is unsuccessful. Therefore, an "action log" is required so that the operation is rolled back when it fails.

(4) Backup mechanism: Save a copy of the database.

(5) Remote operation: Allows the user to operate the database via TCP/IP protocol on different machines.

Finish

--------------------------------------------------------------------------------------------------------------- -----------------------------------------------------

How does you build a database? Self . Database

Its a great question, and deserves a long answer.

Most database servers is built in C, and store data using b-tree type constructs. In the old days there were a product called C-isam (C Library for an indexed sequential access method) which are a low level Library to help C programmers write data in b-tree format. So-need to know about btrees and understand what these is.

Most databases store data separate to indexes. Lets assume a record (or row) is an bytes long and you write 5 rows of the data to a file. If The row contains columns such as first name, last name, address etc. and you want to search for a specific record by LA St Name, you can open the file and sequentially search through each record but this is very slow. Instead you open a index file which just contains the LastName and the position of the record in the data file. Then, if you have the position open the data file, Lseek to that position and read the data. Because index data is very small it was much quicker to search through index files. Also as the index files is stored in btrees on it very quick to effectively does a quicksearch (divide and conquer) to find The record is looking for.

So, understand for one "table", you would have a data file with the data and one (or many) index files. The first index file could is for LastName, the next could is to search by SS number etc. When the user defines their query to get some data, they decide which index file to search through. If you can find all info on C-isam (there used to is an open source version (or cheap commercial) called D-isam) Understand this concept quite well.

Once You has stored data and has the index files, using an ISAM type approach allows-GET a record based on a value , or PUT a new record. However modern database servers all support SQL, so you need an SQL parser that translates the SQL statement into a Sequen Ce of related GETs. SQL may join 2 tables A optimizer is also needed to decide which table to read first (normally based on number of rows In each table and indexes available) and how to relate it to the next table. SQL can INSERT data so do need to parse this into PUT statements but it can also combine multiple inserts into Transactio NS so need a transaction manager to control this, and you'll need transaction logs to store wip/completed Transactio NS.

It is possible you'll need some backup/restore commands to backup your data files and index files and maybe also your TR Ansaction log files, and if you really want to go for it you could write some replication tools to read your transaction l OG and replicate the transactions to a backup database on a different server. Note If you want your client programs (for example a SQL UI like phpMyAdmin) to reside on separate machine than your data Base server you'll need to write a connection manager this sends the SQL requests over TCP/IP to your server and then authe Nticate it using some credentials, parse the request, run your GETS and send the data to the client.

So these database servers can is a lot of work, especially for one person. But can I create simple versions of these tools one at a time. Start with what to store data and indexes, and what to retrieve the data using an ISAM type interface.

There is books out There-look for older books on MySQL and msql, look for anything on Google re btrees and ISAM, look F or open source C libraries this already do ISAM. Get a good understanding on file IO to a Linux machine using C. Many commercial databases now dont even use the filesystem for their data files because of cacheing Issues-they write di rectly to Raw disk. You want to just write to files initially.

I hope this helps a little bit.

The simplest implementation of database and data structure