NoSQL architecture practice (I) supplemented by NoSQL _ MySQL

NoSQL architecture practice (I)-supplemented by NoSQL (1) using NoSQL as an image

It does not change the original architecture that uses MySQL as the storage, uses NoSQL as the auxiliary image storage, and uses the advantages of NoSQL to help improve performance.

Figure 1-NoSQL is an image (code completion mode)

// Sample pseudocode for writing data // data is the data object we want to store. title = "title"; data. name = "name"; data. time = "10:10:01"; data. from = "1"; id = DB. insert (data); // write data to the MySQL database NoSQL. add (id, data); // write data to the NoSQL database with the auto-increment id generated by writing data to MySQL as the primary key

If you have data consistency requirements, you can use the following method:

// Sample pseudocode for writing data // data is the data object bool status to be stored = false; DB. startTransaction (); // start transaction id = DB. insert (data); // write the MySQL database if (id> 0) {status = NoSQL. add (id, data); // write data to a NoSQL database with the auto-incrementing id generated by writing data to MySQL as the primary key} if (id> 0 & status = true) {DB. commit (); // submit the transaction} else {DB. rollback (); // rollback failed}

The above code may seem a little troublesome, but you only need to make a unified encapsulation on the DB class or ORM layer to achieve reuse. other code does not need to be modified.

This architecture adds an auxiliary NoSQL storage layer to the original MySQL-based database architecture, with a small amount of code and a low technical difficulty, but plays a significant role in scalability and performance. You only need to write the program into the MySQL database and the NoSQL database at the same time, so that MySQL and NoSQL have the same image data, in some places that can be queried based on the primary key, using Efficient NoSQL database queries saves MySQL queries and uses NoSQL high performance to resist these queries.

Figure 2-NoSQL image (synchronous mode)

Instead of program code, MySQL synchronizes data to NoSQL. this mode is a variant of the above, and is transparent to writing but more technically difficult. This mode is suitable for existing complex old systems. modifying the code is not easy to implement and may cause new problems. It is also suitable for synchronizing data to multiple types of storage.

The implementation of MySQL-NoSQL synchronization can be implemented using the MySQL UDF function and MySQL binlog parsing. It can be implemented using existing open-source projects, such:

• MySQL memcached UDFs: Operate The Memcached protocol from a UDF.
• Mysql-udf-http: an open-source mysql-UDF in China: operate the http protocol through udf.

With these two MySQL UDF libraries, we can use MySQL to transparently process Memcached or Http, as long as there is a NoSQL database compatible with Memcached or Http, then we can use MySQL to synchronize data. Combined with lib_mysqludf_json, the UDF and MySQL trigger functions can be used to automatically synchronize data.

(2) MySQL and NoSQL combinations

MySQL only stores small fields to be queried, and NoSQL stores all data.

Figure 3-MySQL and NoSQL combinations

// Sample pseudocode for writing data // data is the data object we want to store. title = "title"; data. name = "name"; data. time = "10:10:01"; data. from = "1"; bool status = false; DB. startTransaction (); // start transaction id = DB. insert ("insert into table (from) VALUES (data. from) "); // write data to the MySQL database. write only the field from that needs to be queried from if (id> 0) {status = NoSQL. add (id, data); // write data to a NoSQL database with the auto-incrementing id generated by writing data to MySQL as the primary key} if (id> 0 & status = true) {DB. commit (); // submit the transaction} else {DB. rollback (); // rollback failed}

Store small fields, such as numbers and times, to be queried in MySQL, and create corresponding indexes based on the query, large text fields are stored in NoSQL. During the query, we first query the primary key of the data from MySQL, and then directly retrieve the corresponding data from NoSQL.

This architecture mode integrates the functions of MySQL and NoSQL and performs their respective duties, so that MySQL is responsible for processing relational storage that is good at, and NoSQL is used as the storage of data. It has the following advantages:

• Saves MySQL I/O overhead. Because MySQL only stores small fields that need to be queried and is no longer responsible for storing large text fields, this saves the space overhead of MySQL storage and MySQL disk IO. We used this optimization to reduce a 40 GB MySQL table to several hundred MB.
• Improves the Cache hit rate of MySQl queries. We know that the query cache is invalid at the table level. Once the MySQL table is updated, it will become invalid. after this field separation, if the updated field is not stored in MySQL, this does not affect query cache. NoSQL caches are generally row-level, and only expire with the Cache of updated records.
• Improves MySQL master-slave synchronization efficiency. Because of the decrease in MySQL storage space, the synchronized data records are also reduced, and some data updates fall into NoSQL rather than MySQL, which also reduces the number of times MySQL data needs to be synchronized.
• Improves the speed of MySQL data backup and recovery. Because the data stored in the MySQL database is reduced, it is easy to see that the speed of data backup and recovery will be greatly improved.
• It is easier to expand than before. NoSQL is naturally easy to expand. After this optimization, MySQL Performance is also improved.

For example, mobile phone PhoenixNet is this architecture http://www.cnblogs.com/sunli/archive/2010/12/20/imcp.html