Comparative analysis between Mongodb and MySQL

The content of this article is about the comparative analysis between Mongodb and MySQL. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

In the data stored in the database, there is a special key value called primary key, which is used to uniquely identify a record in the table. In other words, a table cannot have multiple primary keys, and the primary key cannot be null. Whether it is MongoDB or MySQL, there is a definition of primary key .
For MongoDB, the primary key is called "_id". When generating data, if the user does not actively assign a primary key to it, MongoDB will automatically generate a randomly assigned value for it.

In MySQL, the designation of the primary key is defined by specifying PRIMARY KEY when MySQL inserts data. When the primary key is not specified, another tool - the index, is equivalent to replacing the function of the primary key. The index can be empty or have duplicates. There is another type of index that does not allow duplicates, called a unique index. If neither a primary key nor an index is specified, MySQL will automatically create one for the data.

Storage speed comparison

1. The average insertion rate of the database: MongoDB does not specify _id insertion> MySQL does not specify primary key insertion> MySQL specifies primary key insertion> MongoDB specifies _id for insertion.

2. MongoDB has a big difference in insertion speed between specifying _id and not specifying _id, but the difference in MySQL is much smaller.

Analysis:

1. When specifying _id or primary key, the two databases must process the index value when inserting, and find whether the same value exists in the database. key value, which will slow down the rate of insertion.

2. In MongoDB, specifying index insertion is much slower than not specifying it. This is because the _id value of each piece of data in MongoDB is unique. When data is inserted without specifying _id, its _id is automatically calculated and generated by the system. MongoDB uses computer characteristics, time, process ID and random numbers to ensure that the generated _id is unique. When inserting by specifying _id, MongoDB needs to check whether this _id is available every time it inserts a piece of data. When there are too many data items in the database, the query overhead of this step will slow down the insertion speed of the entire database.

3. MongoDB will fully use the system memory as cache, which is a very excellent feature. Our test machine has 64G of memory. When inserting, MongoDB will try its best to persist the data to the hard disk after the memory is almost full of data. This is also the reason why MongoDB is far more efficient when inserting without specifying _id. But when inserting by specifying _id, when the amount of data is too large to fit in the memory, MongoDB needs to read the information from the disk into the memory to check for duplication, which makes the insertion efficiency slower.

4. MySQL is indeed a very stable database, and its efficiency is not much different whether inserting with a primary key specified or without a primary key specified.

Insertion stability analysis

Insertion stability refers to the insertion rate when a certain amount of data is inserted as the amount of data increases.

In this test, we set the scale of this indicator at 100,000, that is, the displayed data is how many pieces of data can be inserted per second during this period when 100,000 pieces of data are inserted.

First present four pictures:

1. MongoDB specifies _id insertion:

##2. MongoDB does not specify _id insertion :

3. MySQL specifies PRIMARY KEY for insertion:

##4. MySQL does not specify PRIMARY KEY for insertion:

Summary:

1. The overall insertion speed is still similar to the statistics of the previous round: MongoDB does not specify _id for insertion> MySQL does not specify primary key for insertion> MySQL specifies primary key for insertion> MongoDB specifies _id for insertion.

2. It can be seen from the figure that when inserting data by specifying the primary key, when MySQL and MongoDB have different data orders of magnitude, the data inserted per second will fluctuate every once in a while. In the chart The display becomes regular glitches. When inserting data is not specified, the insertion rate is relatively average in most cases. However, as the data in the database increases, the insertion efficiency drops momentarily in a certain period of time, and then becomes stable again.

3. Overall, the rate fluctuation of MongoDB is more serious than that of MySQL, and the variance changes greatly.

4. When MongoDB inserts by specifying _id, when more data is inserted, the insertion efficiency drops significantly. In the other three insertion tests, the insertion rate is fixed at a standard most of the time from the beginning to the end.

Analysis:

1. The glitch phenomenon is because when too much data is inserted, MongoDB needs to write the data in the memory to the hard disk, and MySQL needs to re- Sub-table. These operations are performed automatically whenever the data in the database reaches a certain level, so there will be an obvious glitch every once in a while.

2. After all, MongoDB is still a new thing, and its stability is not as good as MySQL, which has been used for many years.

3. When MongoDB inserts specified _id, its performance drops significantly.

4. When the size of data read is not large, MongoDB's query speed is really unparalleled, far behind MySQL.

5. When the amount of queried data gradually increases, the query speed of MySQL decreases steadily, while the query speed of MongoDB fluctuates somewhat.

Analysis:

1. If MySQL has not been query optimized, its query speed should not be compared with MongoDB. MongoDB can make full use of the system's memory resources. Our test machine has 64GB of memory. The larger the memory, the faster MongoDB's query speed. After all, the I/O efficiency of disk and memory is not of the same order of magnitude.

2. The query data in this experiment is also randomly generated, so the probability that all the data to be queried is stored in MongoDB's memory cache is very small. When querying, MongoDB needs to interact with the data in memory and disk multiple times in order to find it, so its query rate depends on the number of interactions. There is a possibility that although the amount of data to be queried is larger, this randomly generated data is fetched from the disk by MongoDB in a smaller number of times. Therefore, the average query speed is faster. From this point of view, MongoDB's query speed fluctuations are also within a reasonable range.

3. There is no doubt about the stability of MySQL.

Conclusion

1. Compared with MySQL, MongoDB database is more suitable for task models with heavy reading operations. MongoDB can fully utilize the memory resources of the machine. If the machine has abundant memory resources, MongoDB's query efficiency will be much faster.

2. When inserting data with "_id", MongoDB's insertion efficiency is actually not high. If you want to make full use of MongoDB performance, it is recommended to insert without "_id" and then index the relevant fields for query.

3. MongoDB is suitable for those demand models where the specific data format of the database is unclear or the database data format changes frequently, and it is very friendly to developers.

4. MongoDB officially comes with a distributed file system, which can be easily deployed on a server cluster. There is a concept of Shard in MongoDB, which is convenient for server sharding. With each additional Shard, MongoDB's insertion performance will nearly double, and the disk capacity can be easily expanded.

5. MongoDB also comes with support for the map-reduce computing framework, which is also very convenient for data statistics.

Defects of MongoDB

1. Weak transaction relationship support. This is also a common flaw of all NoSQL databases, but NoSQL is not designed for transaction relationships, and specific applications are still in demand.

2. The stability is somewhat lacking, as can be seen from the above test.

3. On the one hand, MongoDB is convenient for developers, but on the other hand, it places considerable demands on operation and maintenance personnel. There is no mature MongoDB operation and maintenance experience in the industry, and the storage format of data in MongoDB is also very random. Issues such as these are a test for operation and maintenance personnel.

The above is the detailed content of Comparative analysis between Mongodb and MySQL. For more information, please follow other related articles on the PHP Chinese website!