Mysql > Mysql > Mysql > Mysql > Mysql > Mysql > Mysql > Mysql > Mysql

B+ tree execution process flow chart (similar to the principle of hop table multi-level index)

Several important properties

Record_type: indicates the record type. 0 indicates the common record, 1 indicates the index record, 2 indicates the minimum record, and 3 indicates the maximum record
Next_record: a property of the record header that represents the offset of the next address relative to this record (because addresses are not consecutive, the index is needed to find the location of the next data)
Values for each column: actual user data

The cost of indexing

Be sure to use indexes wisely.

Space cost (a large number of indexes can use a lot of memory)
Time cost (a large number of indexes need to be maintained, which can affect write performance)

(Each key is ordered)

All values match
Matches the column on the left
Match range values (try to put range conditions last, conditions after range conditions will fail)
Matches exactly one column and ranges exactly the other
Used for sorting (try to match index)
Used for grouping (try to be consistent with indexes)

Create indexes only for columns used for searching, sorting, or grouping
Create indexes for columns with large cardinality of columns
Index column types should be as small as possible
You can index only the prefixes of string values
An index applies only if the index column appears alone in a comparison expression
It is recommended that the primary key have the AUTO_INCREMENT attribute in order to minimize the number of page splits and record shifts in the cluster index.
Locate and delete duplicate and redundant indexes in a table
Try to use overwrite indexes for queries to avoid performance loss caused by back to the table.