Selection of relational and non-relational databases and alternative methods (mysql replaces mo

Selection of relational databases and non-relational databases, as well as alternative methods (mysql replaces mongodb) the non-relational data (mongodb) Project I used because it involves a large amount of historical data increases ), however, I encountered many problems in the actual process, such as development and management. As a result, I thought about replacing non-

Selection of relational databases and non-relational databases, as well as alternative methods (mysql replaces mongodb) the non-relational data (mongodb) Project I used because it involves a large amount of historical data increases ), however, I encountered many problems in the actual process, such as development and management. As a result, I thought about replacing non-

Selection of relational and non-relational databases and alternative methods (replacing mongodb with mysql)

My project uses non-relational data (mongodb) because it involves a large amount of historical data. However, many problems have been encountered in the actual process, including development issues, there are also management aspects, So I thought about the idea of replacing non-relational database mongodb with relational database mysql. Www.2cto.com

It has not been implemented yet, because there will be great changes to the project. I hope you can take a look at my ideas. It is best to give some comments. Thank you.

The core of the solution is to divide the current database into two databases, one current transaction database and one historical data query database.

Www.2cto.com

--------------------------- The detailed solution is as follows ----------------------------------

Create two mysql databases, the current business database is bussDB, and the data center database is datacenterDB.

How the two databases operate:

1. The table structures of the datacenterDB database and the bussDB database are basically the same. BussDB inventory placement ongoing transactions, datacenterDB inventory placement ongoing transactions and all historical transactions.

2. The business background management system and business foreground website are directly connected to the bussDB library for normal business operations.

3. The Historical query and analysis statistics system connects to the datacenterDB database for query statistics.

4. The bussDB database is optimized for database storage and modeling based on the database suitable for oltp (transaction type.

5. The datacenterDB database is optimized for database storage and Modeling Based on databases suitable for olap (analyticdb and data warehouse). technologies such as table partitions and clusters can be used.

6. Every night, information in the bussDB database is automatically synchronized to the datacenterDB database through the ETL Tool (Database Synchronization tool.

7. The setup program automatically clears the transaction information 180 days after the transaction is completed. After the transaction is cleared, the query about the transaction is queried in the datacenterDB database.

Why are there two databases:

1. There are some differences between a suitable transaction database and a suitable analysis database modeling. bussDB is a transaction type, while datacenterDB is a query analysis type.

2. The bussDB database must ensure rapid response without affecting the speed due to the increasing history data. Therefore, it is necessary to regularly clear the data and data transfer.

3. The bussDB database is a transaction-type database and must ensure transaction integrity. Therefore, it is not suitable for cluster creation and Vertical Scaling (high-performance servers) should be adopted ).

4. The datacenterDB database is a data warehouse database that requires cluster and horizontal scaling (low-cost PC as a cluster ).

Why do we use relational databases instead of relational databases.

1. When the bussDB database is designed in the preceding method, the performance overhead of the addition, deletion, modification, and query operations for relational databases and non-relational databases is basically the same, because the data volume of all tables is very small, the number is smaller than a million, because relational databases only need to set indexes if they have less than 10 million data records.

2. When the performance is consistent, non-relational databases cannot support dynamic connection query applications, that is, join Operations in SQL, or join operations are less efficient than relational databases, in addition, group and count/sum/avg operations are not supported. A large number of join operations exist in the business system, for example, complex applications such as report printing, bank payment reconciliation, employees, organizations, roles, transactions, accounts, and dictionaries all involve a large number of associations, therefore, non-relational databases are not as flexible as relational databases and provide high performance in terms of processing, and the code compilation is less readable and robust.

3. Relational Database Management tools such as sqlyong are richer and more complete than non-relational database management tools. For DBA maintenance of databases, there are a wide range of methods and materials such as batch update, import, export, backup, and optimization of relational databases. for developers, relational databases are also relatively low.

4. the development history and Stability of relational databases exceed those of non-relational databases.

5. Since the performance is consistent, why not use relational databases.

6. The datacenterDB database must be a relational database, because a large number of statistical applications of datacenterDB are based on the statistical functions in SQL, such as querying the sex ratio and degree distribution of transaction services, SQL operations, such as join, group by xxx, sum (), count (), and avg (), are not supported by relational databases.