当前位置：网站首页>How does MySQL execute SQL statements

How does MySQL execute SQL statements

2022-07-18 22:14:00 【InfoQ】

Write it at the front

MySQL It is the relational database most used by major Internet companies . Because of its lightness 、 Open source features , extensive use . But we are using MySQL When , Often because I don't know how it works , Lead to all kinds of problems . The most remarkable is slow SQL.

MySQL working principle

slow SQL For developers , Is the most common problem in the process of use . The root is for MySQL in SQL I don't understand the plan implemented . So we have to find out MySQL Statement execution .

MySQL Executive schematic diagram

about MySQL The process of statement execution , What most people are familiar with is as shown in the above figure . Mainly MySQL Of Server Workflow of layer , And for Server Little is known about the interaction between layer and storage engine . Let me introduce in detail today

Interaction between actuator and storage engine ( With innoDB Engine as an example )

MySQL InnoDB The engine reads and writes data , Disks that are not directly operated , Instead, read the results into memory , To operate . This memory space is called InnoDB Buffer Pool.MySQL How to use this memory space Server End interacts with the storage engine , Explain the query statement and update statement respectively .

We know MySQL The data storage unit of is data page , The default size is 16K.

Query scenario

MySQL When doing data query , First query Buffer Pool, If there is no corresponding data page , Then query the data page of the disk , And load the relevant data pages into Buffer Pool in , And then in Buffer Pool Data screening in .

Update scene

MySQL When updating data , Or search for Buffer Pool Data pages in , If there is , Update memory directly . non-existent , Then read the data page from the disk to Buffer Pool, Update again Buffer Pool Data pages in . then ,MySQL Through asynchronous threads , Every once in a while Buffer Pool The data pages in are refreshed to disk in batches , This greatly reduces the number of disks IO operation , Saved a lot of time .

Some students may ask , The data is in Buffer Pool And a disk . In case , stay Buffer Pool Before synchronizing to disk , Database down , that Buffer Pool The data in is not lost ？MySQL To ensure the data persistence of committed transactions , Introduced redo log,redo log yes InnoDB Engine specific mechanism . With redo log, The update statement has been modified Buffer Pool After the data page in ,redo log Will record . This will ensure that after the database goes down , Still from redo log Recovering data in .

We know redo log Is a log file stored on disk ,MySQL To reduce the interaction with disk , To introduce Buffer Pool, Let the operation be carried out in memory . But then again redo log Perform disk operation , What is the meaning ？ This is because data pages are stored on disk , But the specific content of the operation is not affected MySQL control , Some data page operations belong to the category of random reading and writing . and redo log For disk operation, it is read and write in sequence , This greatly improves performance （ So is sequential reading kafka,rocketMQ One of the reasons for the high throughput ）.

summary

In fact, in the process of application development ,MySQL The implementation scheme to ensure the final consistency of data , We can also refer to the implementation .

原网站

版权声明
本文为[InfoQ]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/199/202207161912150422.html