This is the 23rd day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

What are the differences between a normal index and a unique index in different business scenarios?

What’s the difference between the two

Select name from CUser where id_card = ‘XXXXXX’;

  • Create unique index for id_card field
  • Create a normal index

Impact of query statements on performance

Select id from T where k = 5 from B+ tree start at the root – search by layer to leaf node – inside the data page to locate records by dichotomy

  1. Plain indexing – finds the first record that meets the condition – continues to find the next record – until one that does not meet the condition is encountered
  2. Unique index – because indexes define uniqueness – the search stops after the first record that meets the condition is found

Performance difference – minimal

InnoDB – Data read and write by data page when a record needs to be read – by page, the whole is read into memory – InnoDB, the size of each data page is 16KB by default. Therefore, the engine reads and writes by page – when it finds a record with k = 5, the data page is in memory

  1. Normal indexing – the extra “find the next record” operation – requires only one pointer find and one evaluation

Performance impact of the update process

When updating a data page – the data page is in memory – directly updating the data page is not in memory – without affecting data consistency – InnoDB will cache these updates in the Change Buffer so that it does not need to read the data page from disk the next time a query needs to access the data page – Read the data page into memory – perform the operations related to the page in the change buffer – ensure that the data logic is correct

change buffer

Data that can be persisted — there’s a copy in memory, Operations are also written to disk and applied to the original data page – the process of obtaining the latest results is called merge – accessing this data page triggers the merge – the system and background threads merge periodically – during the normal shutdown of the database – merge Record the update operation in change Buffer – Reduce disk read – the execution speed of the statement will be significantly improved. Data read into memory – Need to occupy the buffer pool – This method can avoid occupying memory and improve the memory utilization

Change buffer uses memory in the buffer pool, so it cannot increase the size of change buffer wirelessly – this can be set dynamically with innodb_change_buffer_max_size

- The parameter is 50. -change The size of a buffer can occupy a maximum of 50% of the buffer poolCopy the code

When can you use change Buffer?

Unique index – All update operations – must determine whether the operation violates the uniqueness constraint – must read the data page into memory – no need to use change buffer ** Unique index updates cannot use change buffer – normal indexes can use **

What is InnoDB’s process for inserting a new record (4, 400) into this table?

  1. The target page for the record to update is in memory

    1. Unique index – find position between 3 and 5 – determine no conflict – insert value – statement end
    2. Plain index – find the position between 3 and 5 – insert this value – statement end

    The difference in the impact of a normal index versus a unique index on update statement performance is just a judgment call – a tiny amount of CPU time

  2. The target page for the record to update is not in memory

    1. Unique index – reads data page into memory – determines no conflicts – inserts this value – statement completes
    2. Normal index – Updates are recorded in the change Buffer, and statement execution ends

    Reading data from disk into memory involves random I/O access, which is one of the most expensive operations in the database. – The change buffer significantly improves update performance because it reduces random disk access!

Usage scenarios of change Buffer

Merge is the time when data is actually updated – the main purpose of the change buffer is to cache recorded changes – before a page is merged – the more changes recorded by the change buffer (the more times the page is updated) – the greater the benefit

Write more and read less – pages are less likely to be accessed immediately after being written – Change buffer works best – billing and logging systems

The query will be made immediately after the write – the update will be recorded in the change Buffer – but then because the data page will be accessed soon – the merge process will be triggered immediately – so that the number of random IO access will not be reduced – increasing the maintenance cost of the change buffer – change Buffer had the side effect

Index selection and practice

Query ability. – No difference

Update performance – Select common indexes as much as possible. Common indexes + Change Buffer – Optimizes the update of tables with large data volumes

Change buffer and redo log

EG: Insert into t (id, k) values(id1, k1), (id2, k2) InnoDB buffer pool InnoDB buffer pool InnoDB buffer pool Page2 is not in memory – the change buffer in memory records the message “insert a line to Page2” 3. Write the above two actions to the redo log and the transaction completes – the update is inexpensive – two memory writes – one disk writes 1. Read Page1 – returns data directly from memory – Does not need to update data from redo log. Read Page2 – Read Page2 from disk into memory – Apply the operation log in change buffer – generate a correct version and return the result. Redo log Saves I/O for random disk reads