Location>code7788 >text

Normal and unique indexes

Popularity：282 ℃/2025-03-04 15:28:02
Normal index
Ordinary index is the most basic index type. It is an index structure created on a column or some columns in a database table. The purpose is to improve the efficiency of number query.
Unique index
The only index is to add uniqueness requirements on the basis of ordinary indexes
From a performance perspective, do you choose a unique index or a normal index? What is the basis for selection?
There is no difference in query capabilities. The main consideration is the impact on update performance, so try to use ordinary indexes.
 Because the change buffer cannot be used for the unique index update, the normal index can be used for the
change buffer
Essence: It is a memory cache structure, like a small warehouse that temporarily stores data modification information, specifically for update operations of non-primary key indexes

 Purpose: Reduce disk io operations. When updating non-primary key indexes, if the corresponding index is not in the memory buffer pool, put the modification information first in the change buffer, instead of immediately reading the index to modify it.
redolog
Essence: It is a log file used to record transactions' modification operations on data

 Content: Contains physical modification information to the data page

 Purpose: Ensure the persistence of transactions.  When a transaction is committed, the modification will be recorded in the redo log. If the system fails, the system restart can re-modify the data not persisted to the disk based on the redo log and restore it to the status at the time of transaction commit.
The difference between change buffer and redolog
Change buffer improves performance by reducing unnecessary disk io
 redo log optimizes write performance by sequentially writing to disk
 1. When we update data, if the data page is in memory, it will be updated directly.
 2. Otherwise, without affecting data consistency, innodb will store these update operations in the change buffer, so there is no need to read this data page from disk.
 3. The next time you query to access this data page, store the data page into memory, and then execute the insertion related to this page in the change buffer.
Update statement operation
1 In memory, update the memory directly
  2 It is not in memory, just in the change buffer area of ​​memory, record the information "I want to insert a row"
 3. Record these two actions into the redo log
 From the above, we can see:
 1. The cost of executing this update statement is very low, that is, I wrote 2 memory and 1 disk, or written sequentially
<Previous Next>