Mongodb vs. MySQL

In the data stored in the database, there is a special key value called the primary key , which is used to uniquely identify a record in the table. In other words, a table cannot have more than one primary key, and the primary key cannot be a null value.

There is a definition of the primary key for both MongoDB and MySQL.

For MongoDB, the primary key is called "_id", and MongoDB automatically generates a randomly assigned value for the data when it is generated, if the user does not actively assign it a primary key.

In MySQL, the designation of the primary key is defined by specifying PRIMARY key when MySQL inserts the data. When a primary key is not specified, the other tool, the index, is the equivalent of the function that replaces the primary key. Indexes can be empty or duplicate, and another index that does not allow duplication is called a unique index. If neither a primary key nor an index is specified, MySQL automatically creates one for the data.

1. Average insertion rate of the database:MongoDB does not specify _id Insert > MySQL do not specify primary key Insert > MySQL specify PRIMARY key Insert > MongoDB specify _id Insert .

2. MongoDB has a large difference in speed when specifying _id and not specifying _id inserts, while MySQL is much smaller.

Analysis:

1. When specifying a _id or primary key, both databases will process the index value at insert and look for the same key value in the database, which slows the insertion rate.

2. In MongoDB, the specified index insertion is much slower than not specified, because the _id value of each data in MongoDB is unique . When inserting data without specifying _ID, its _id is generated by the system's automatic calculation. MongoDB ensures that the generated _id are unique through computer eigenvalues, time, process IDs, and random numbers. when specifying _id inserts, MongoDB will need to check that this _id is not available for every piece of data, and when the number of data bars in the database is too many, the query overhead of this step slows down the entire database insertion speed.

3. MongoDB makes full use of system memory as a cache , which is a very good feature. The memory of our test machine is 64G, and when it is plugged in, MongoDB will try to persist the data to the hard disk after the memory is not written in the data. This is also the reason why MongoDB's efficiency is far ahead when not specifying _id insertion. However, when specifying _id inserts, when the amount of data in a large memory is not enough, MongoDB needs to read the disk information into memory to check the weight, so that its insertion efficiency is slow.

4. MySQL is a very stable database, no matter whether you specify a primary key or do not specify a primary key insertion, its efficiency is not too much.

Insert stability Analysis

Insert stability refers to the rate at which each insertion of a certain amount of data is inserted as the amount of data increases.

In this test, we set the scale of this indicator at 10w, that is, the data displayed in each insert 10w data, in this period of time each second can insert how many pieces of data .

Show four images first:

1. MONGODB specifies _id insert:

2. MongoDB does not specify _id insert:

3. mysql specifies primary key insertion:

4. mysql does not specify primary key insertion:

Summarize:

1. The overall insertion speed is similar to the previous statistic:MongoDB does not specify _id Insert > MySQL does not specify primary key Insert > MySQL specify PRIMARY key Insert > MongoDB specify _id Insert .

2. It can be seen that when the primary key is inserted into the data, MySQL and MongoDB at different levels of data, the data inserted every second every time there will be a fluctuation, in the chart shows a regular Burr phenomenon . When inserting data is not specified, in most cases the insertion rate is averaged, but as the data in the database increases, the efficiency of the insertion decreases instantaneously in a certain period of time and then becomes stable.

3. On the whole, the rate fluctuation of MongoDB is more serious than that of MySQL and the variance is larger.

4. When MongoDB is inserted in the specified _id, the insertion efficiency decreases significantly when the inserted data becomes much more. In the other three types of insert tests, the rate at which they were inserted, from start to finish, was fixed to a standard in most cases.

Analysis:

1. Glitch is because, when there is too much data to be inserted,MongoDB needs to write the data in memory into the hard disk, and MySQL needs to be re-divided into tables . These operations occur automatically every time the data in the database reaches a certain magnitude, so there is a noticeable glitch at intervals.

2. MongoDB is still a new thing after all, its stability has not been applied for many years of MySQL excellent.

3. MongoDB 's performance drop is severe when it is inserted in the specified _id .

1. When the size of the data read is small,MongoDB query speed is really a ride dust , throw away MySQL good far away.

2. When the data volume of the query is increasing, the query speed of MySQL is declining steadily, while the query speed of MongoDB is somewhat ups and downs.

Analysis:

1. If MySQL is not optimized for query, its query speed should not be compared with MongoDB. MongoDB can take full advantage of the system's memory resources , our test machine memory is 64GB, the larger the memory MongoDB query faster, after all, disk and memory I/O efficiency is not a magnitude.

2. The data of the query in this experiment is also randomly generated, so the probability that the data to be queried exists in the memory cache of MongoDB is very small. At query time, MongoDB needs to interact with the disk multiple times to find the data in memory, so its query rate depends on the number of times it interacts . There is a possibility that, although the number of data to be queried is large, this randomly generated data is taken out of the disk by MongoDB in less time. As a result, the average speed of its queries is faster. Thus, MongoDB's query speed fluctuation is also in a reasonable range.

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

Conclusion

1. Compared to MySQL, theMongoDB database is more suitable for those task models that read the job more heavily . MongoDB can take full advantage of the machine's memory resources. MongoDB's query efficiency can be much faster if the machine's memory resources are plentiful.

2. When inserting data with "_id", the efficiency of MongoDB insertion is not high. If you want to take full advantage of MongoDB performance, it is recommended to adopt the Insert method without "_id", and then index the related fields to query .

1. MongoDB is suitable for requirements models that are ambiguous in database format or that are frequently changed in database formats, and are very friendly to developers .

2. MongoDB official comes with a distributed file system that can be easily deployed on the server cluster . MongoDB has a shard concept, is convenient for the server shard use. The insertion performance of each additional shard,mongodb will also grow in close multiples, and the disk capacity can be easily expanded.

3. MongoDB also comes with support for the Map-reduce Computing Framework , which is also handy for data statistics.

MongoDB defects

1. transaction relations support is weak . This is also a common flaw for all NoSQL databases, but NoSQL is not designed for transactional relationships, and the application is still in demand.

2. There is some lack of stability , as can be seen from the above tests.

3. MongoDB on the one hand in the convenience of developers at the same time, on the other hand on the operation and maintenance personnel have put forward a considerable number of requirements. the industry does not have mature mongodb operation and maintenance experience, MONGODB data storage format is also very casual, and so on, and so on the test of operation and maintenance personnel.

Mongodb vs. MySQL