With the development of Internet and mobile Internet, various agencies need to support far more than the previous data. In this stimulating demand, IT field there has been a lot of data processing techniques, one of which is NoSQL. Flexible data types, efficient processing capabilities, so has a place NoSQL data management system, such as the popular NoSQL databases MongoDB. However, in the Wix engineering practice, they found that a large number of scenarios is not required NoSQL, but more mature RDBMS benefits, such as MySQL. Watching below Wix project director Aviran Mordo share, translated by OneAPM engineer.
Developers select NoSQL databases are generally based on subjective assumptions, or "relational databases perform as well as NoSQL databases" This wrong idea. In addition, do database selection, developers also often overlooked on the operation and maintenance costs. In fact according to Wix practice found that in most cases do not have to choose NoSQL database, and if used properly, MySQL can also be a good NoSQL database.
When scalable system construction, a very important consideration is the use of technology is mature, proven technology means that the choice of an error can quickly recover. Of course, developers can also be used in the project the latest and most cattle NoSQL database, and this database can also run well in theory, but appeared in a production environment is a problem how long recovery will take? Technically existing knowledge and experience is essential to alleviate the problem, of course, this accumulation also includes Google search of content. In contrast, relational database has been in existence for over four decades, the industry maintains a relational database has accumulated a wealth of experience. Based on these considerations, usually choose to do in the new project technology selection when MySQL, instead of NoSQL databases, NoSQL unless there really is very, very obvious advantages, such as too much data is not suitable for use MySQL.
I must admit MySQL also has its own problems. For use in large-scale systems, then you may run into performance problems. To achieve optimal performance of MySQL, it summed up a few experience here, one of which is to avoid the database level transactions. Because the transaction requires a database using locks to achieve, which will affect database performance. Often using logic application-level locks replaced, thereby reducing the load and get a better performance.
For example, the invoice structure as an example. If an invoice has multiple line items in a single transaction will replace all line items written here should be written in non-progressive transaction case. After all rows of all writes to the database, there is also a first written record that contains links to all line items ID pointer. Thus, if all the rows in a row write fails, then the first record of this line would not exist, so that the whole transaction fails. While doing so may cause some garbage record, but in the storage medium so cheap today, which is obviously not a big problem, and these records can also do regular garbage removed.
Here are some MySQL mediation practices:
Do not use query joins only a primary key or index query.
Do not use auto-increment primary key lock because there is, instead, use the client to generate keys, such as GUIDs. Also, if you use the main primary backup, auto-increment key may also conflict, so you need to customize each instance key range.
No index fields or use JSON to delete all grouped into a single field.
In Wix, MySQL will often be stored as a key value, such as stored in a JSON object, so that does not change the database schema for the data structure model for expansion. In MySQL, using the primary key to read quickly, Wix will generated by this sub-millisecond reading speed, you can support the entire usage scenario. For these reasons, MySQL can be seen as consistent with a NoSQL database ACID principles. As for the size of the database, a MySQL instance to support hundreds of millions of pieces of data are no problem.
A distinct advantage of the relational database is not consider eventual consistency, and this is not natively supported in NoSQL database. This article is not to belittle NoSQL, because the relational database is also very much a restraint: strict data structure and size restrictions. Here just want to remind developers when selecting new technologies do not ignore the operation and maintenance costs.