If used properly, MySQL can also be transformed into NoSQL

With the development of the Internet and mobile Internet, various organizations need to support data that far exceeds the previous ones. Under the stimulus of this demand, a large number of data processing technologies have emerged in the IT field.

With the development of the Internet and mobile Internet, various organizations need to support data that far exceeds the previous ones. Under the stimulus of this demand, a large number of data processing technologies have emerged in the IT field.

[Editor's note] with the development of the Internet and mobile Internet, various organizations need to support data that is far greater than before. Under the stimulus of this demand, a large number of data processing technologies have emerged in the IT field, one of which is NoSQL. With flexible data types and efficient processing capabilities, NoSQL has occupied a place in the data management system, such as the Popular NoSQL database MongoDB. However, in Wix engineering practices, they found that NoSQL is not required in many scenarios, but mature RDBMS is more efficient, such as MySQL. Let's take a look at the sharing of Aviran Mordo, Wix Engineering director, translated by OneAPM engineering engineer.

The following is the translation

Developers generally choose NoSQL databases based on subjective assumptions, or the incorrect idea of "relational database performance is inferior to NoSQL database. In addition, when selecting databases, developers often ignore the O & M overhead. In fact, based on Wix's practice, we found that in most cases we do not have to choose a NoSQL database, and if used properly, MySQL can also be an excellent NoSQL database.

When building a scalable system, it is important to consider whether the technology used is mature. Choosing a mature technology means that the system can be quickly recovered when an error occurs. Of course, developers can also use the latest and most popular NoSQL database in the project, and this database can run well theoretically. However, how long does it take to recover a problem in the production environment? The accumulation of existing technical knowledge and experience is essential for mitigating problems. Of course, this accumulation also includes what Google can search. In contrast, relational databases have existed for more than 40 years, and the industry has accumulated a lot of experience in maintaining relational databases. Based on these considerations, MySQL is often used for technical selection in new projects, rather than NoSQL databases, unless NoSQL really has very obvious advantages. For example, MySQL is not suitable for large data volumes.

You must acknowledge that MySQL has its own problems. Performance problems may occur when used in large-scale systems. To optimize MySQL performance, we have summarized several experiences here, one of which is to avoid database-level transactions. Because transactions need to be implemented by the database using locks, which will affect the database performance. Generally, logical application-level locks are used to replace them, reducing load and improving performance.

For example, the invoice structure is used as an example. If an invoice has multiple row projects, instead of writing all row projects in a single transaction, it should be written row by row in non-transaction situations. After all rows are written to the database, a first record is written, which contains a pointer to the project IDs of all rows. In this way, if one row of Data fails to be written in all rows, the first record of the row will not exist and the transaction will fail. Although this may cause some junk records, it is obviously not a big problem today when the storage media is so cheap, and these junk records can also be deleted regularly.

The following describes some MySQL practices:

In Wix, MySQL is often used as key-value storage. For example, you can store JSON objects in a column to expand the data structure mode without changing the database mode. In MySQL, the primary key reading speed is also very fast. Wix obtains the read Speed in sub-seconds through this method, and can fully support the entire application scenario. For these reasons, MySQL can be regarded as an ACID-compliant NoSQL database. As for the database size, it is no problem that a MySQL instance supports hundreds of millions of data records.

A clear advantage of relational databases is that they do not have to consider eventual consistency, which is not supported by native databases. This article does not describe NoSQL, because there are many restrictions on relational databases: strict data structures and size restrictions. I just want to remind developers not to ignore the O & M cost when selecting a new technology.

Original article: MySQL is a Great NoSQL Database (Editor/Zhong Hao)

This article permanently updates the link address: