The transformation from SQL to NOSQL

NOSQL systems generally advertise a feature, that is, good performance. Why? After so many years of development in relational databases, various optimization work has been done very deeply. NOSQL systems generally use relational database technology. So what factors have bound the performance of relational databases? We can look at this problem from the perspective of system design.

1. Index support

At the beginning of the relational database service, I did not think that today's Internet applications have such high requirements on scalability. Therefore, the main consideration in the design is to simplify the work of users. The generation of SQL language facilitates the standardization of database interfaces, this forms a database company like Oracle and drives the development of the upstream and downstream industry chains. Relational databases support Indexing in standalone storage engines. For example, the Innodb Storage engine of Mysql must support indexing, while the standalone storage engine of NOSQL systems is pure, only primary key-based random reading and range query are supported. The NOSQL system provides indexing support at the system level. For example, a user table with the primary key user_id has many attributes, including the user name, photo ID (photo_id), and photo URL, if you need to create an index on photo_id in a nosql system, you can maintain a distributed table. The primary key of the table is a binary group. Relational databases need to support indexing at the level of a single storage engine, which greatly reduces the scalability of the system and makes the design of a single storage engine complicated.

2. Concurrent Transaction Processing

Relational databases have a set of theories about concurrent transaction processing. For example, the lock granularity is table level, page level or row level, multi-version concurrency control mechanism MVCC, transaction isolation level, and Deadlock Detection, rollback, and so on. However, most Internet applications feature fewer reads. For example, the ratio of read to write is 10: 1, and there is little need for complex transactions. Therefore, generally, the simpler copy-on-write technology can be used: Single-thread writing, multi-thread reading, and execution of copy-on-write during writing. Writing does not affect the read service. The assumption of a NOSQL system simplifies the system design, reduces overhead of many operations, and improves performance.

3. Dynamic or static data structures

The storage engine of apsaradb is always a disk B + tree. To improve performance, you may need insert buffer for aggregation and query cache for read, it is often necessary to implement a cache management mechanism similar to Linux page cache. The read and write operations in the database affect each other, and the write operation also flushes data to the disk from time to time, resulting in poor performance. In short, the data structure of the relational database storage engine is a universal dynamic update B + tree. However, in NOSQL systems, for example, Bigtable adopts the SSTable + MemTable data structure, and the data is first written to the memory MemTable, when it reaches a certain size or exceeds a certain period of time, it will dump to the disk to generate the SSTable file, which is read-only. If the data structure of the relational database storage engine is a dynamic B + tree, the SSTable is an ordered array sorted in order. Obviously, implementing an ordered data is much simpler and more efficient than implementing a dynamic B + tree that contains a complex concurrency control mechanism.

4. Join Operation

Relational databases need to support Join at the storage engine level, while NOSQL systems generally decide the Join implementation method based on applications. For example, there are two tables: User table and commodity table. Each user may have several commodities. The primary key of the User table is, the associated attributes of a user and a product are stored in the User table. The primary key of the product table is item_id. The product attributes include the product name, product URL, and so on. Assume that the application needs to query all the items of a user and display the details of the items. The common practice is to first query all the item_id of the specified user from the user table, then, you can query the details of each item_id in the commodity table, that is, execute a database Join operation. This will inevitably lead to a lot of random disk reads, and the random read locality caused by Join is not good, the cache effect is often limited. In NOSQL systems, we can often integrate user tables and product tables into a wide table, so that although the redundant storage of product details, but in exchange for efficient query.

The performance bottleneck of relational databases is often not in SQL statement parsing, but in the need to support complete SQL features. Internet companies are faced with high performance and scalability requirements for applications, and the DBA and development engineers are relatively high, so they can sacrifice some interface friendliness in exchange for better performance. Some designs of NOSQL systems, such as joining through wide tables, have also been done by Internet company DBAs and development engineers. NOSQL systems only enhance these constraints. In the long run, we can summarize a set of constraints and define an SQL subset. You only need to support this SQL subset to support Internet applications such as more than 90% without sacrificing scalability. I think that when NOSQL technology develops to this step, it will become more mature. This is what we finally want to do. When designing and using a NOSQL system, we can also transform our thinking as follows:

1) larger data volume.Many people encounter more than a certain number of records during the use of Mysql. For example, the database performance starts to decline at. This value usually requires a lot of tests. However, most NOSQL systems have good scalability and support a larger amount of data. Therefore, you can also use the space-to-time approach, such as using a wide table to implement Join.

2) Performance estimation is easier.Due to the complex concurrency control, the insert buffer and the read/write optimization mechanism similar to page cache make performance estimation relatively difficult. In many cases, you need to obtain the system performance through experience or tests. Then, due to the implementation of the storage engine and the concurrency control mechanism, the NOSQL system can roughly predict the system performance through the hardware performance indicators in the system design, and the performance estimation is more operable.