This is the sixth day of my participation in the August More text Challenge. For details, see:August is more challenging

Start with performance [Business code has been guaranteed not to write duplicate data】

The query process

1. Ordinary index - after finding the first record that meets the condition - need to find the next record - until the first record that does not meet the condition 1. Unique index - The uniqueness of the index definition - the first record that meets the criteria is found - the search stopsCopy the code

InnoDB reads and writes data on a page basis – instead of reading the record itself from disk, InnoDB reads it into memory as a page

Normal indexing – the “find and determine the next record” operation – requires only one pointer lookup and one calculation

The update process

Change buffer – persistent data [copy in memory, also write to disk]

When a page needs to be updated – the page is in memory – Direct update – The page is not in memory – If data consistency is not affected – The update operation is cached in the change buffer – when the next query needs to access the page – the page is read into memory – Execute the change buffer Operations related to this page in – to ensure that the data logic is correct

– innodb_change_buffer_max_size – innodb_change_buffer_max_size – innodb_change_buffer_max_size

merge
1. Operations in the change buffer are applied to the original data page [triggered when the data page is accessed] 1. 1. Merge background threads periodicallyCopy the code
advantages
1. Reduced disk read - statement execution speed is significantly improved. 1Copy the code

What conditions can I use a change buffer?

  1. Unique index – determine whether the operation violates the uniqueness constraint by reading the data page into memory – do not use change buffer

The change buffer is used in the common index

Process for inserting a new record (4, 400)

  1. The target page for the record to be updated is in memory –
    1. Unique index – Find position of 3 and 5 – Find no conflict – Insert value – end of statement
    2. Normal index – Find the position of 3 and 5 – Insert the value – end of statement execution

The difference in the performance of an update statement between a normal index and a unique index is just a judgment – a small amount of CPU time

  1. The target page for the record to be updated is not in memory –
    1. Unique index – read this record into memory – Find location 3 and 5
    2. Plain index – Records the update in the change buffer, and the statement is finished

Reading data from disk into memory involves access to random IO – one of the most expensive operations in the database change buffer reduces disk access – the improvement in update performance can be significant

Change Buffer usage scenario

More write, less read – More changes recorded in the change buffer – More benefits – Bill class, log class system common index + change buffer [make it as large as possible] => Select common index for update optimization of tables with large data volume. If all updates are immediately followed by a query with that record – turn off the change buffer

Correctness first to ensure that the correct business, business priorities – if meet with a lot of inserting data is slow, memory shot low – a + a screening approach is more “archival repository” setting the historical data stored in the file repository – as the archived data to ensure there is no unique key conflict – common indexes improve the efficiency of archive 】

Change buffer and redo log

WAL’s core mechanism for improving performance – minimizing random reads and writes

write

insert into t(id, k) values(id1, k1), (id2, k2); – The data page where K1 resides is in memory, and the data page where K2 resides is not

· Memory, redo log, data tablespace (T.IBD), system tablespace (IBdatA1)

  1. Page1 in memory – Updates memory directly
  2. Page2 Not in memory – Record this operation from the change buffer area of memory
  3. Log both actions in the redo log

The update statement is inexpensive to execute – two writes to memory and one writes to disk [two operations combined to write to disk once] [Sequential write]

Read requests

Select * from t where k in(k1, k2) select * from t where k in(k1, k2) select * from t where k in(k1, k2

  1. When reading Page1, it returns directly from memory

After WAL, is it necessary to read data from disk? Make sure you update the redo log before returning it. If Page2 is not updated, the redo log is updated, and the disk is not updated, the Page2 is not updated. If Page2 is not updated, the disk is not updated. Apply the operation log in the change buffer, generate a correct version and return the result – the data page will only be read if page2 needs to be read

The change buffer saves I/O consumption for random read to disks