In today's market , There are various types of databases , Choosing the right database for your business is of great significance to the development and maintenance of your application . This article , Will share with you SQL and NoSQL The difference between languages , We will also compare the two types of databases , To help you choose the most suitable database for your business .
ONE —— What is? SQL and NoSQL
One 、 What is? SQL？
Structured query language （Structured Query Language） abbreviation SQL, Is a special purpose programming language , Is a database query and programming language , For data access and query 、 Update and manage relational database system . The language operates and retrieves data from structured data formats in the form of tables .
Two 、 What is? NoSQL？
NoSQL, A general term for a non-relational database , Provides a mechanism for storing and retrieving unstructured data . This type of database can handle a lot of data , And it has dynamic mode . therefore ,NoSQL The database has no specific query language , There are no or very few relationships , But data is stored in the form of collections and documents .
This is a Employee database , Among them is 2 A collection of , namely Employee and Projects. Now each of these collections has a document , These documents are basically data values . therefore , You can assume A set is Your watch , Documents are your fields in the table .
TWO ——SQL VS NoSQL
in the light of SQL and NoSQL The difference between , Will be based on different aspects of the comparison ：
One 、 Database type
SQL It's called Relational database , Because it defines structured data organization as rows and columns , Each table is related to other tables in the database .
On the other hand ,NoSQL It's called a non relational database , This is because data is stored as a collection , There is little or no relationship between them .
Two 、Schema framework
SQL Need to use structured data Predefined Architecture . therefore , At the beginning of use SQL Before extracting and manipulating data , You need to make sure that the data structure is predefined in the form of a table .
however ,NoSQL Have a dynamic architecture for unstructured data . therefore , If you use NoSQL database , There is no predefined Architecture , The complete architecture of the data depends entirely on how you want to store the data , Which fields do you want to store in documents and collections .
3、 ... and 、 Database categories
SQL The database is A table based database . So you can have n Two interrelated tables , And each table can have rows and columns , These rows and columns store data in each cell of the table .
and NoSQL Databases have the following types of databases ：
Document database – Pair each key with a complex data structure called a document . It can contain many different key value pairs , Even nested documents ;
Key value storage – They are the simplest NoSQL database . Every item in the database is stored as an attribute name or key and its value .
Graphic storage – They are used to store information about the network , Social relationships, for example . Graphics storage includes Neo4J and HyperGraphDB.
Wide column storage – Cassandra and HBase The equal width column storage is optimized for the query of large data sets , And store data columns together instead of rows .
therefore ,SQL Databases store data in the form of tables , and NoSQL Database pairs with key values , file , Graphic database or wide column storage to store data .
Four 、 Complex queries
And NoSQL comparison ,SQL More suitable for complex query environment , because SQL The architecture in the database is structured , And the data is stored in a tabular format . therefore , Even if you want to apply nested queries with many subqueries in an external query , It can also be done easily by using the appropriate table and column names .NoSQL The reason why databases are not suitable for complex queries is because NoSQL The database is not based on SQL And so on .
5、 ... and 、 Tiered data storage
And SQL Database comparison ,NoSQL Better for tiered storage .
This is because as the number of tables increases , The complexity of maintaining their relationship is also increasing . So in this case , You cannot associate a large number of tables with each other . But when you think about using NoSQL Database time , This kind of database is more suitable for hierarchical data storage , Because it follows something like JSON The key value pair storage mode of data .
6、 ... and 、 Extensibility
SQL The database is Vertically scalable Of . You can optimize the hardware （ For example, increase CPU,RAM,SSD etc. ） To balance the load of the data server .
On the other hand ,NoSQL The database is Horizontally scalable Of . You can perform load balancing by adding more servers to the cluster to handle large amounts of traffic .
7、 ... and 、 Language
SQL Databases have specific languages , It won't be different because of different databases .NoSQL The database has no specific language for querying , It changes with the database . stay NoSQL In the database , Queries are mainly focused on document collections , The language is called UnQL（ Unstructured query language ）.
8、 ... and 、 Online processing
In comparison SQL and NoSQL when , Based on this factor , SQL Databases are used for transactional applications . This is because SQL Provides the atomicity of data , Integrity and stability . You can also change NoSQL For transactional purposes , But in highly loaded and complex transactional applications , It's still not stable enough . therefore ,SQL It is mainly used for OLTP（ Online transaction processing ）, and NoSQL It is mainly used for OLAP（ On line analytical processing ）.
Nine 、 Basic attributes
SQL The database is based on ACID attribute （ Atomicity , Uniformity , Isolation and persistence ）, and NoSQL The database is based on Brewers CAP Theorem （ Uniformity , Availability and partition tolerance ）. First let me explain ACID attribute ：
Atomicity ： Atomicity is a complete or failed transaction , A transaction is a single logical operation of data . It means , If part of any transaction fails , The whole transaction will fail , And the state of the database will remain unchanged .
Uniformity ： Consistency ensures that data must conform to all validation rules . In short , You can say that your transaction will never leave the database without completing its state .
Isolation ： The main goal of isolation is concurrency control .
persistence ： Persistence means that if the transaction is committed , Then anything can happen between transactions , For example, power off , Crash or any kind of mistake .
talk about CAP Theorem ,Brewers CAP The theorem states that , The database can only implement at most two of the following three guarantees ： Uniformity , Availability and partition tolerance . here
Uniformity ： All nodes see the same data at the same time .
Usability ： Make sure that every request is successful or failed .
Zone tolerance ： Ensure that even if the message is lost or part of the system fails , Is the system still running .
NoSQL Can't provide consistency and high availability at the same time .
Ten 、 External support
since SQL Born more than 40 Since then , all SQL Excellent support from suppliers . however , For certain NoSQL database , There are limits , You still have to rely on community support to deploy large-scale NoSQL. This is because NoSQL On 2000 In the late's , People haven't done much about it yet
THREE——MySQL VS MongoDB
So far we have learned SQL and MySQL The difference between , Here's by sharing the most popular of the two databases MySQL and MongoDB To get to know them better .
One 、 What is? MySQL？
MySQL Is an open source relational database management system that can run on many platforms . It provides multi-user access 、 Support for many storage engines .
Here are MySQL The function of ：
Easier to manage – The software is very easy to download , And use the event scheduler to automatically schedule tasks .
Powerful transaction support – Have ACID（ Atomicity , Uniformity , Isolation, , Durability ） attribute , Distributed multi version support is also allowed .
Comprehensive application development – MySQL Have a plug-in library for embedding databases into any application . It also supports stored procedures for application development , trigger , function , View etc. .
High performance – Provides unique memory caching and table index partitioning for quick load Utilities .
Lower cost of ownership – This reduces costs and hardware spending .
Open source and 24 * 7 Support – The RDBMS Can be used on any platform , And for the open source and enterprise version 24 * 7 Support .
Secure data protection – MySQL Support strong mechanisms , To ensure that only authorized users can access the database .
High availability – MySQL Can run high speed master / From replication configuration , And provide cluster server .
Scalability and flexibility – Use MySQL, You can run deep embedded applications , And create a data warehouse that contains a lot of data .
Two 、 What is? MongoDB？MongoDB It's a non relational database , Used to store data in a document . This type of database stores relevant information together , For fast query processing .
MongoDB The functions are as follows ：
Indexes ： Index is created to improve search performance .
Copy ：MongoDB Distribute data on different computers .
Temporary query ： It passes for BSON Document indexing and using a unique query language to support temporary queries .
Modeless ： Because its modeless database is used C ++ Compiling , So it's very flexible .
Fragmentation ：MongoDB Use fragmentation to enable deployment with very large data sets and high throughput operations .
Now let's look at the difference between the two databases ：
1、 query language
MySQL Using structured query language （SQL）. The language is simple , Mainly by DDL,DML DCL and TCL Command composition , To retrieve and process data . On the other hand ,MongoDB Use unstructured query language . therefore , The query language is basically MongoDB query language . Please refer to the following figure. ：
2、 The flexibility of the model
MySQL With the flexibility of structured data architecture , Because you just need to clearly define the tables and columns . On the other hand ,MongoDB There are no restrictions on pattern design . You can refer directly to a few documents in the collection , And there's no relationship between these documents . however ,MongoDB The only problem is that you need to optimize the architecture based on how you access the data .
3、 Relationship processing
MySQL With the help of JOIN Statements support relationships , but MongoDB I won't support it JOIN sentence . however , It supports putting one document on another （ Also known as document embedding ） And multidimensional data types （ For example, an array of ） Inside .
MySQL Basically using a privilege based security model . This security model authenticates users , And promote user privileges on specific databases . On the other hand ,MongoDB Use role-based access control and a flexible set of privileges , These privileges provide security functions such as authorization and authentication .
5、 performance When considering large databases , And MongoDB comparison ,MySQL It's very slow . This is mainly due to MySQL Can't be used for massive and unstructured data . however ,MongoDB Ability to handle large unstructured data . therefore , It's better than thinking about large databases MySQL faster , Because it allows users to query in a way that reduces server load . Be careful ： There is no hard and fast rule , namely MongoDB Always provide faster speed for your data , It all depends on your data and infrastructure .
6、 make carbon copies
MySQL It supports master-slave replication and master-slave replication . On the other hand ,MongoDB Supports built-in replication , Slicing and automatic selection . therefore , With the help of MongoDB Automatic selection in , You can set up another database or secondary database , To take over automatically when the primary database fails .
You can refer to the figure below to see where to use MySQL and MongoDB：
|Best for data that contains tables and rows||Best for unstructured data|
|For small datasets||For large datasets|
|Updated frequently||High write load|
|Strong reliance on multi line trading||High availability in unstable environments|
|Modify a lot of records||Based on data location|
About SQL and NoSQL Database comparison is simple to share here ！
Master these concepts of contrast , It needs to be combined with real projects to get a deeper understanding of MySQL and MongoDB Different application scenarios and operation methods of database . The following course will take you into the application of real projects ！
《2021 year Web Front end development project package 》
20 Set the front end of the actual combat + Tool courses
Real enterprise projects , To improve your skills
High cost performance , Exclusive community , good service
Scan the code and buy immediately
Time limited discount
For more details
Code scanning consultation
WeChat ID ｜mixiu1573