Zhaoping: Project success depends on database schema design

Http://tech.it168.com/a2011/0416/1178/000001178961_all.shtml

"IT168 Information" high-rise valerian up, the success or failure of high-rise depends on whether there is a good foundation. The success or failure of a system depends on the merits and demerits of the architecture design. A good architecture avoids or reduces losses when external things make a company project fail, whereas a bad system architecture design can make the company more costly. How to design the system architecture? Please ask a multinational company database architect Zhaoping to share his experience.

▲ Zhaoping, a multinational company database architect

Architecture The most important thing is around the performance, cost, high availability of data three points, these three points are closely linked, inseparable, so we call the database architecture design "iron triangle." These three factors restrict each other, affect each other and are inseparable.

Iron Triangle

Performance-determining costs and high availability

Availability depends on cost and performance

Cost-determining performance and availability

Database Schema Design Roadmap

▲ Database Schema Design Roadmap

▲ Driving performance of Troika

First wagon--distribution data

Distribution data is based on certain conditions to divide the data into multiple pieces, and then put in different physical locations. The so-called physical location refers to geographic locations, physical hosts, and physical disks. Distributed data is currently the most efficient way to optimize performance. There are several ways to distribute data:

1, according to the National Division

2, according to the city division

3, according to the data Center (DC) Division

4, according to the data set division

Vertical split (Vertical shard)

▲ Vertical Split

Vertical splitting is also called row splitting, which is to separate the columns that make up a row into different tables that have different structures and have fewer columns in each table after splitting. Vertical splitting is the redistribution of columns. Vertical splitting is actually "business split."

▲ Vertical Split

The structure formed after the vertical split is complete: 1, a business corresponding to a database; 2, in fact, this is the vertical split (Vertical shard).

A structure that is formed after a vertical split has completed

When "Business two" becomes the focus and hotspot, the database must be split horizontally and what is split horizontally? Horizontal splitting is actually dividing a table into tables that have the same columns but less data. The principle is to clone new tables based on existing tables, which hold different data. It has the following characteristics:

1, the structure of each block is exactly the same

2, the table structure of each block and the original "Business II" database table structure is identical

3, the only difference is that each piece of data stored in different users

4, each block in the structure is actually the original database clone (clone)

5, each block is actually a complete database

Assuming that after a period of development, business two outstanding performance, the current database can not meet the needs of business two, but also need to split the business two database.

▲ the split of the "Business II" database

▲ Changes in user distribution

Splitting of the "Business II" database

User changes after splitting

In fact, the above is horizontal split (horizontal shard). The so-called horizontal splitting is actually dividing a table into tables that have the same columns but less data. The principle is to clone new tables based on existing tables, which hold different data. When the horizontal split is complete, the table S is split into three tables that have the same structure. Just these three tables hold different data.

　　Second carriage: "Read and write separation"

After the data is split, DB Set1 a set of database consists of 5 databases, for each such database, still not achieve the best performance, it is necessary to db22 to achieve read and write separation. The MySQL agent is a simple program between MySQL client and MySQL server that can be used to monitor, analyze, or transmit communication between them. The biggest advantage is: Read and write separation.

　　
▲ Read-Write separation for DB22

　　
The database before and after the read/write separation

　　
▲ Read and Write separated database

Third carriage: "Cache (Caching technology)"

When the separation of the business after the three changes, its performance has reached the bottleneck, the use of split technology can not break through the current limitations, then only consider the cache (caching technology).

Application of memcached

There are two major effects to be aware of when using memcached technology:

　　Query Impact: before querying, look for results in memcached. If found, returns it if it is not found, executes the query on the database server and returns the result to memcached

　　Insert Effect: inserts data into the database first, the database affected in memory becomes invalid

Using memcached Technology

▲memcached Post-application database

In addition to memcached, of course, other caching techniques can achieve the same effect. such as SOLIDDB and Oracle TimesTen can achieve this effect.

Zhaoping: Project success depends on database schema design