Understanding and familiarity with database knowledge

---restore content starts---

Work in contact with the database has been a lot of, below to summarize, but also familiar with.

Type of database:

There are many kinds of database, there is a mature and stable MySQL database, there is a rising star of the MongoDB database, but also a new era darling Redis database. In addition, there are other databases, such as Sqlite,Oracle , and so on.

So the question is, how do you choose to face multiple types of databases?

Perhaps you prefer the MYSQL database because of your personal preference, so choose it. Maybe you looked up the data on the Internet and found out that others are recommending MongoDB, so choose it.

Both of these ideas are not properly chosen. anything that comes out of business to talk about architecture is nonsense . Therefore, decide which database to choose based on the scenario requirements of the project business . Each database has advantages and disadvantages, and selection criteria is the best choice, the most suitable.

My personal understanding is based on the following considerations:

1 Read and write speed

This method of storing data often determines the speed of reading and writing.

• MySQL is stored on the hard drive regardless of the data or index. To be swapped into memory when it is to be used. Ability to handle data that is far more than the total amount of memory.

• All of MongoDB 's data is actually stored on the hard disk, and all the data to be manipulated is mapped to an area of memory in a mmap way. MongoDB then modifies the data in this area to avoid fragmented hard disk operations.

• all Redis data is placed in memory. But it also supports data persistence to the hard disk.

We all know that disk read data is much less efficient than memory. So in general, the speed of reading and writing data for these three is:Redis > MongoDB > MySQL

2 whether transactional and complex queries are supported

• MySQL is a relational database that supports transactional operations and complex structured queries for join methods.
• MongoDB is a non-relational database that does not support transactional operations or join operations.
• Redis is also not supported .

Therefore, you should consider using MySQL for the following scenarios:
1) There are a lot of structured data in the business data, such as user account, address, etc. Because these data often require structured queries.
2) There are many transactional operations in the business and need to ensure strong consistency of transactions.

3 Business data volume growth rate

In a year or so, the growth in business data is not within the forecast range, and MongoDB is preferred.

Because MongoDB built a sharding, a lot of data sharding features, easy to scale, and better adapt to the growth of large data volume needs. and MySQL in this respect performance is inferior, MySQL single table data volume reaches 5-10 G when the performance degraded, need to do the horizontal and vertical splitting of the data, the library split to complete the expansion.

Redis is not used to store large amounts of data due to memory capacity limitations. Generally take it as a cache.

4 table structure is clear

If the database table interface is ambiguous in the business scenario, the data is growing. For example, the following scenarios, content management platform (such as BBS forum post scene), user social platform (such as posts in the bar and user comments), priority to use MANGDB.

Because MongoDB is a non-structured document database, extending the fields is easy and does not affect the original data.

At the end of the day, the database as a container for storing data should take a little more time to consider in the architecture selection.

Some of the SQL used in the work is posted below.

Query Library name according to the table name: MySQL

1 SELECT  from WHERE = ' t_vehicle ';

Query table name according to the field name:MySQL

1 SELECT DISTINCT  from WHERE  like ' %virtual_number% ';

• Meet the MANGODB database.
• Using tool: Robo 3T

It's the most intuitive way to get a picture of yourself.

One more: Support for SQL statements (should be tools)

For Mangodb, the functions used in the work are simply queries. Interested in can go to Baidu to understand.

Understanding and familiarity with database knowledge