Technology sharing differences between SQL and NoSQL databases: MySQL (VS) mongodb

Lao Wu and Xiao Xian 2021-05-04 15:44:33
technology sharing differences sql nosql



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 .



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



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 .

4、 Security
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 . 

7、 usage

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




本文为[Lao Wu and Xiao Xian]所创,转载请带上原文链接,感谢

  1. Help, Java how to get all the current processes of the system
  2. Has anyone ever used JMeter or written tests in httpUnit????
  3. Living in a mountain village in late spring
  4. Partridge day, spring of HKUST
  5. JavaScript异步编程4——Promise错误处理
  6. 海康摄像SDK开发笔记(一):海康威视网络摄像头SDK介绍与模块功能
  7. JavaScript asynchronous programming 4 -- promise error handling
  8. Haikang video SDK development notes (1): introduction and module functions of Hikvision webcam SDK
  9. JOP:用于FPGA的嵌入式实时系统中的Java优化处理器内核
  10. Spring Boot源码:使用MongoDB MongoTemplate公开REST在几分钟内实现CRUD功能
  11. Spring Boot应用程序事件教程 - reflectoring
  12. 带有Resilience4j断路器的Spring云网关 - rome
  13. 经验分享:Apache Kafka的缺点与陷阱 - Emil Koutanov
  14. 通过Spring Boot Webflux实现Reactor Kafka
  15. 从Java 8升级到Java 11应该注意的问题
  16. Jop: Java optimized processor core for FPGA embedded real time system
  17. Spring boot source code: use mongodb mongotemplate to open rest to realize crud function in a few minutes
  18. Spring boot application event tutorial - reflecting
  19. Spring cloud gateway with resilience4j circuit breaker - ROM
  20. Experience sharing: shortcomings and pitfalls of Apache Kafka - Emil koutanov
  21. Realization of reactor Kafka through spring boot Webflux
  22. RPC框架设计----Socket与I/0模型
  23. Problems in upgrading from Java 8 to Java 11
  24. RPC framework design -- socket and I / 0 model
  25. RPC框架设计----I/0模型
  26. RPC framework design: I / 0 model
  27. RPC框架设计----NIO编程缓冲区Buffer
  28. RPC框架设计----NIO编程缓冲区Buffer
  29. RPC framework design -- NiO programming buffer
  30. RPC framework design -- NiO programming buffer
  31. Java多线程基础
  32. Java multithreading Foundation
  33. 码农飞升记-00-Java发展历程
  34. Development history of coder-00-java
  35. 码农飞升记-00-Java发展历程
  36. Development history of coder-00-java
  37. Spring and Autumn Moon
  38. Node.js与Spring Boot比较? - Ryan Gleason
  39. Spring WebFlux的明显陷阱 - ŁukaszKyć
  40. Spring创始人Rod大叔对YAML的真实想法
  41. Compare node.js with spring boot- Ryan Gleason
  42. Obvious pitfalls of spring Webflux- Ł ukaszKy ć
  43. Spring founder uncle rod's real thoughts on yaml
  44. 码农飞升记-02-OracleJDK是什么?OracleJDK的版本怎么选择?
  45. What is manong feisheng-02-oracle JDK? How to choose the version of Oracle JDK?
  46. Spring tide surging Xinanjiang
  47. Linux内核软中断
  48. Linux kernel soft interrupt
  49. Linux内核软中断
  50. Linux kernel soft interrupt
  51. Java multithreading Foundation
  52. The construction of Maven private library nexus
  53. I / O stream in Java
  54. JDK 16:Java 16的新功能 - InfoWorld
  55. 在Java中本地进行线程间数据传输的三种方式和源码展示
  56. jdon导致cpu 99%最后tomcat死掉---banq给予回复
  57. 用领域事件模拟AOP注入
  58. JDK 16: new function of Java 16 - InfoWorld
  59. Cartoon: from JVM lock to redis distributed lock
  60. Spring 3.1 终于加入了Cache支持