Analysis on 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 optimizations have been done very deeply. NOSQL systems generally use relational database technology. what factors have bound the performance of relational databases? We can look at this problem from the perspective of system design. NOSQL systems generally advertise a feature, that is, good performance. why? After so many years of development in relational databases, various optimizations have been done very deeply. NOSQL systems generally use relational database technology. 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, to create an index for photo_id in a NOSQL system, you can maintain a distributed table with the primary key

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 dynamically updated B + tree. However, in NOSQL systems, for example, Bigtable uses the SSTable + MemTable data structure, data is first written to the MemTable in the memory. when it reaches a certain size or exceeds a certain period of time, it will be dumped to the disk to generate an SSTable file. SSTable 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 item table. each user may have several items, and the primary key of the user table is

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. a 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. The implementation of the storage engine and concurrency control mechanism of the NOSQL system are relatively simple. the system performance can be roughly estimated by the hardware performance indicators in the system design. The performance estimation is more feasible.

The above is the specific analysis content of the transformation from SQL to NOSQL thinking. For more information, please follow the PHP Chinese network (www.php1.cn )!