MySQL Big Data sub-library and sub-table PHP solution!

When the MySQL data volume is too large, it will face pressure decomposition, then the sub-database table is a good solution, and now we have to talk about how MySQL library sub-table is ideal, and then use PHP how to invoke.

1, master-slave replication, read/write separation

To modify the data for the main library, the query is used from the library. A master multi-slave, to reduce the database read pressure.

2, sub-database sub-table

According to the physical business to Sub-Library, sub-table. For example, depending on the activity of the data, depending on the UID of the user.

3,mysql different storage engine differences

The InnoDB is used for applications with high data integrity/write performance requirements. MyISAM is suitable for querying applications.


table is a good way to distribute the pressure of the database.

Table, the most straightforward meaning, is to divide a table structure into multiple tables, and then, can be in the same library, can also be placed in different libraries.

Of course, the first thing you need to know is how to divide the table. Individuals think that the number of single-table records to reach the million to tens the use of the table.

1, classification of the sub-table

1> Longitudinal sub-table

The content that would have been in the same table could have been artificially divided into multiple tables. (So-called originally, refers to the third paradigm according to the relational database requirements, is supposed to be in the same table.) ）

Sub-table reason: Separate according to the activity of the data (because different active data, processing method is different)

Case:

For a blog system, the article title, author, classification, creation time, etc., is the change frequency slow, the number of queries, and preferably have good real-time data, we call it cold data. and the number of blog views, replies, and so on, similar statistics, or other high-frequency changes in the data, we call it active data. Therefore, in the design of database structure, we should consider the sub-table, first of all, the processing of vertical table.

This is followed by a longitudinal sub-table:

First, the use of the storage engine is different, cold data using MyISAM can have better query data. Active data, can use InnoDB, can have better update speed.

Second, the cold data is more from the library configuration, because more operations when queried, so as to speed up the query. For thermal data, it is possible to handle the horizontal table of the main library relatively.

In fact, for some special active data, you can also consider using Memcache, Redis

such as the cache, and so accumulated to a certain amount to update the database. or MongoDB type of NoSQL database, here is just an example, not to say this first.

2> Horizontal sub-table

Literally, it can be seen that the large table structure, horizontal cutting for the same structure of the different tables, such as user Information table, user_1,user_2 and so on. The table structure is exactly the same, however, according to some specific rules to partition the table, such as according to the user ID to take the module partition.

Sub-table reason: According to the size of the data volume to divide, to ensure that the capacity of the single table is not too large, so as to ensure the single-table query processing capacity.

Case: With the above example, the blog system. When the amount of blog is very large, you should take the horizontal segmentation to reduce the pressure of each single table, to improve performance. For example, a blog cold data table, if divided into 100 tables, when there are 1 million users in the browsing, if it is single table, will make 1 million requests, and now after the table, it is likely that each table for 10,000 data requests (because, can not be absolute average, just assume), so the pressure is much lower.


1, background: A Address Book application, the design of the user volume of 200 million, the statistics of each user's address book is about 30, the entire data volume of 6 billion, using MySQL database (billion-level database design)
Plan divided into: 1000 tables, 100 libraries

2, sub-database sub-table code

Private Function Getdbno ($email)
{
$m = MD5 ($email);
$n = Hexdec (substr ($m, 0, 16));
$tableNo = Fmod ($n, 1000);
$dbNo = $tableNo% 100;
Return Array ($dbNo, $tableNo);
}

3. Connection access code for mates

Require_once ' db/config.php ';

Class Db_adapter
{
Const MASTER = 0;
Const SLAVE = 1;

private static $instances = Array ();

Private $conf = Array ();

Private $conns = Array ();

Private $conn = NULL;
private $stmt = NULL;
Public function __construct ($conf)
{
$this->conf = $conf;
}

Public function Execute ($sql, $params)
{
$cmd = substr (Strtolower (Trim ($sql)), 0, 6);
if ($cmd = = ' select ') {
$conn = $this->getconn (self::slave);
} else {
$conn = $this->getconn (self::master);
}

$conn->prepare ($sql);
$stmt = $conn->execute ($params);

$this->conn = $conn;
$this->stmt = $stmt;
}

Public function Fetch ()
{
return $this->stmt->fetch ();
}

Public Function Fetchall ()
{
return $this->stmt->fetchall ();
}

Public Function Lastinsertid ($name = NULL)
{
return $this->conn->lastinsertid ($name);
}

Public Function RowCount ()
{
return $this->stmt->rowcount ();
}

Private Function Getconn ($type)
{
if ($type = = Self::slave && isset ($this->conf[self::slave])) {
$id = 0;
} else {
$id = 1;
}

if (!isset ($this->conns[$id])) {
$conf = $this->conf[$id];
$this->conns[$id] = new PDO (
$conf [' DSN '], $conf [' User '], $conf [' Pass '],
Self::d boptions);
}

return $this->conns[$id];
}

public static function getinstance ($dbName, $dbNo = 0)
{
$key = $dbName. ‘_’ . $dbNo;
if (!isset (self:: $instances [$key])) {
$conf = Db_config::getconfig ($dbName, $dbNo); Connection Configuration Parameters
Self:: $instances [$key] = new self ($conf);
}

Return self:: $instances [$key];
}
}

4, "potential problems"

if those users in a table have more than one address book contact, such as 1000 for each person, the table may appear oversized, you need to differentiate the table as a child table, and there is no configuration center to handle the situation.
(If this happens, continue with the hash in the connection parameter).

MySQL Big Data sub-library and sub-table PHP solution!