1. Introduction

I bought a bunch of classes on geek time, some of which I abandoned after reading a few chapters, and some of which I probably never opened at all. Recently, I had time to start the study of “MySQL Actual Combat 45”. During the study, I met a lot of knowledge points THAT I didn’t understand. By checking comments and referring to materials, I figured it out.

2. Optimize

MySQL optimization in a sentence: is to optimize disk IO, do not believe you can see the following content

2.1 buffer_pool_size

The buffer_pool_size parameter is used to set the buffer pool size. The larger the buffer pool, the more data pages it holds. Perform a query, query data corresponding to the data page in memory, you can directly return, do not need to perform IO operations, directly completed in memory, its sex can be imagined.

The official documentation for the buffer_pool_size setting suggests this:

On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.

2.2 redo log

Why not write redo logs directly to disk? The performance of sequential I/OS is better than random I/OS because the redo log is sequential AND the disk is random. Conclusion: The existence of redo logs uses sequential I/OS instead of random I/OS

2.3 change buffer

Write change buffer and redo log to prevent data pages from being read into memory and reduce random read I/OS. Conclusion Change buffer exists to reduce random READ IO

2.4 the index

If a statement is executed slowly, the first response is to add an index. Adding an index can search through the index tree, avoiding full table scanning and reducing I/O times. Conclusion: Indexes exist to reduce I/O times

2.4.1 Overwriting indexes

The secondary index can be used to obtain the query result, which is called using the overwrite index. The overwrite index avoids the search in the clustered index tree, which is to reduce the I/O times. Conclusion: Overwrite indexes exist to reduce I/O times

2.4.2 Index Push-down

Filtering conditions in the composite index are used to reduce the I/O times of returning to the table. Conclusion: Index push-downs exist to reduce I/O times

2.4.3 MRR

The MRR sorts the primary keys before returning to the table, changing random READ I/OS to sequential read I/OS. Conclusion: MRR exists to turn random read IO into sequential read IO

Conclusion 3.

All of the above means are optimized based on IO:

  • Reduce random readsIO
  • Order to writeIOSubstitution random writingIO
  • To reduce theIOThe number of
  • Random readIOTurn to sequential readingIO

Therefore, when optimizing MySQL, you can rely more on IO, and the results should not be too bad.