In recent years, more and more NoSql products have continuously jumped out as the technical revolutionary: You see how fast your brother is, and your database is really a battle. Yes, NoSql is really amazing in high-performance scenarios. While our relational databases can only cry in the corner. Yes, they are really fast. But why are they so fast? With the halo of Lei Zhengfu
In recent years, more and more NoSql products have continuously jumped out as the technical revolutionary: You see how fast your brother is, and your database is really a battle. Yes, NoSql is really amazing in high-performance scenarios. While our relational databases can only cry in the corner. "Yes, they are really fast. But why are they so fast? With the halo of Lei Zhengfu
Over the past few years, more and more NoSql products have been jumping out as technical revolutions: "How fast are you looking at your brother? It's really a battle for relational databases ". Yes, NoSql is really amazing in high-performance scenarios. While our relational databases can only cry in the corner. "Yes, they are really fast ".
But why are they so fast? UsedLei Zhengfu's halo? We learned some basic knowledge from the Database Pilot execution process to see where our relational databases are slow? I only have a rough understanding of SqlServer, and I can only start with SqlServer. However, SQLServer is a very complex software. We will use a query execution process to give you a simple understanding of SQlServer's core introduction and running processes.
Since the Select statement is basically the same as the Update statement, but Update involves modifying the statement, let's start with an SQL statement.Update code Nong table set girlfriend = 1 where girlfriend = 0When the codenon wrote such an SQL statement, he easily pressed F5.A girlfriend is born.A statement is a Network Interface (SQL Server Network Interface) sent to the SQL Server in the form of a Data Stream (Tabular Data Stream) Protocol on the client ). It does not matter what the TDS protocol is. Because TDS is an exclusive protocol, it was first designed by Sysbase to interact with database services. You don't need to worry too much. SNI is a protocol layer used to establish a network connection between the server and the client. It is available only after SqlServer2005. It consists of a series of APIs used at the same time.
These are not the focus of our discussion. Let's continue. After SNI received the TDS package and unwrapped it, it found that the boy submitted the SQL statement again. The package is identified as an SQL command and sent to the command parser. Command Parser first checks syntax errors. If a syntax error is found, the error is sent back to the client through the protocol layer. If the syntax is verified, a query tree is generated.
After the query tree is generated, SqlServer performsCPU-consumingThe task is to generate a query plan based on the query tree. SqlServer does not simply translate a query tree into a query plan, but is constantly compared and balanced. A SQL Server may have many query plans. For exampleSelect Name from table where a=1If this table does not have any indexes, the query plan may be a table scan. If there is an index on columnPossibleYou may not need to apply the index on column. In short, SqlServer compares various solutions to find an execution plan with the minimum overhead. The process of finding the minimum execution plan also consumes CPU resources.
To make more effective use of resources, SqlServer caches query plan rows. Save the previously executed query plan in the memory for reuse. Here is an interesting performance optimization point. Because the memory is also very important for SqlServer. If a large number of query plans are cached, the performance of SQL Server is also affected. Therefore, CPU and memory are not cached. What should we do? We should make more efficient use of the query plan cache. For example. The statement we just executedUpdate code Nong table set girlfriend = 1 where girlfriend = 0Then executeUpdate code Nong table set girlfriend = 0 where girlfriend = 1For SqlServer, it treats this as two completely different statements.
Here is a definition: an ad hoc query is executed in the form of a separate SQL statement ). The SQL statements that we often spell in the program are the current queries.
If your system has a large number of existing queries, a large number of query plan caches will be generated. Therefore, we recommend that you use parameterization whenever possible when writing SQL in a program.Update code Nong table set girlfriend = 1 where girlfriend = @ Number of girlfriendsIt is both efficient and prevents SQL injection. Why not?
All components that have completed these tasks belong to the link reference section. Link query is the most complex component in SqlServer. Used to determine the optimal query execution plan. While most NoSql databases on the market do not have a relationship to introduce this part. Statement processing is relatively simple. This saves some expenses.
The storage engine is responsible for data access and management. In fact, I am not talking about the details of the bucket introduction. Let's only talk about the different parts of relational databases and NoSql. Like most NoSql databases, SqlServer relies heavily on memory. Memory is faster than disk. This is what everyone knows. SqlServer also has a mechanism to cache data in memory. Each time you query the request data, SqlServer first queries whether there are corresponding pages in the memory. The page here refers to a continuous 8 KB memory segment. The memory in this section maps the pages in the database file (8 KB for each page) directly to the memory. If a page is found to be required for query, the corresponding page content is directly packaged into a result and returned. If there is no SqlServer, go to the disk and find the corresponding page to load it into the memory. Return the pages in the memory. When the query ends, the pages in the memory are not recycled. It will be stored in the memory all the time. It is not until SqlServer finds that the operating system has insufficient memory that some memory pages that are not frequently used are returned to the Operating System (how intelligent ). In addition, SqlServer allocates some blank memory pages in advance. In this way, you do not need to allocate resources when you wait. With the above principle, you can understand why the value of the Page Life Expectancy (PLE) Performance calculator is better and better. The larger the Page Life Expectancy (PLE), the longer it takes for a Page to exist. That is to say, the smaller your memory pressure is.
However, what should I do if SqlServer is updated? We know that relational databases require more persistence than many NoSql products. How is the database guaranteed?Durability(Don't think too much) and try to improve performance?
SqlServer uses a technology called prewrite log. Simply put, aren't you asking me to work? I have not done the job yet. I will write down the job I want to do first. Then I will work again when I have time. What are the benefits of doing so?
First, we know that the random Write Performance of the disk is very low. On the contrary, the sequential write performance is much higher than the random write performance. If the data is written to the database file every time the user updates the data, a random read/write may be generated. This may affect performance. SqlServer writes logs first. Then, only the corresponding data pages in the memory are updated. Logs are generally written to the end of the file in sequence. In this way, a random read/write is converted into an sequential write and memory modification is added. Performance is naturally improved. By default, NoSql databases do not guarantee the write persistence. Some are regularly flushed to the hard disk, but there is no pre-written log, some are first let you return, the success is not successful, you can ask again.
So how does SqlServer ensure that data in the memory is written back to the hard disk? SqlServer and the NoSqL mentioned above are somewhat of a type. SqlServer has a Lazy Writer thread used to periodically check the value of the idle cache page. If the value is low, SqlServer will scan the normal data cache to expire the unused page for a long time. If he finds a dirty page that has not been used for a long time, he will also write a page that has not been written back to the hard disk-also called a dirty page to the disk, and mark him as an idle page in the memory.
If SqlServer suddenly loses power and the dirty pages in the memory cannot be written to the hard disk, can this problem be solved? SqlServer uses a mechanism called checkpoint.
The checkpoint process ensures that any dirty pages related to committed transactions can be written to the disk, and dirty pages related to uncommitted transactions are written to the disk to ensure efficiency. Unlike Lazy Writer, page check does not remove pages from the cache. He just flushed the dirty pages into the disk and identified the Page as Clean Page in the header. By default, on a busy Server, SQL Server initiates a checkpoint every minute and records it in the transaction log. If SqlServer is implemented or the database is restarted, the recovery process reads the transaction log to know that the work was done before the checkpoint. The work done before the checkpoint is ignored. Note: The trigger condition of CheckPoint is the size of logs generated during CheckPoint. Therefore, we have seen many dirty pages in the memory, but do not trigger CheckPoint.
When SQL Server is shut down due to abnormal reasons, that is, the database is shut down without taking the CheckPoint (as mentioned below). At this time, the data in the database may be inconsistent. Therefore, when the database is started again, it will scan logs to find the data that has not been submitted but written to persistent storage, or the data that has been submitted but not written to persistent storage, to ensure transaction consistency. SqlServer will try to ensure that the database recovery time is less than 1 minute, but at least 10 MB of data must be written to the log, sQLServer will trigger CheckPorint
At this point, you will understand why databases are slower than NoSql products. Of course, this is only one aspect I understand. There may be many areas where NoSql is optimized. Will database performance also have a qualitative leap if you simplify link introduction and remove prewrite logs? But is it all worthwhile?
In addition, if your data files and logs are stored on one disk, the advantage of this pre-Inbound log may be greatly reduced. The reason is something you must think about.
In fact, I just held my thigh with this NoSQL fire. This post does not cover most NoSql content. I don't know much about NoSql either. But now you don't know what NoSql means. NoSql is useful in some areas that do not have high transactional requirements. I plan to study Nosql after reading the work of teacher Podo on the hard disk.
.