Database index (ii) clustered/nonclustered indexes, indexes, and locks

Clustered index (InnoDB, using b+tree as the index structure )

B-tree indexes and data rows are saved in a structure and stored on leaf pages in the order of primary keys ;

Primary key index: leaf node storage (primary key data: All remaining column data)

Secondary index (non-clustered index): leaf node Storage (indexed column data: primary key data)

non-leaf nodes are stored only Indexed Columns

Advantages:

can save the relevant data together, such as the aggregation of e-mail messages based on user ID, only need to read a few data pages to get an ID user's all mail;

data access is faster, and indexes and data are saved in the same b-tree ;

Queries using the overlay index Scan can use the primary key values directly in the page node;

Disadvantages:

The insertion speed is heavily dependent on the insertion order, and inserting in the order of the primary key is the fastest way to load the data into the InnoDB table;

Inserting a new row may face the problem of page splitting, which causes the table to occupy more disk space;

The two-level index requires two lookups, and the storage engine finds the leaf node of the two-level index to obtain the corresponding primary key value, according to which the corresponding row is found in the clustered index

Primary key:

If the table does not have any data to be aggregated (such as the above mail user ID), then you can define a surrogate key as the primary key, using the auto_increment self-increment column;

Nonclustered Indexes ( MyISAM using b+tree as the index structure )

by Data Insertion Order stored on disk , access to data requires a system call;

PRIMARY KEY index / Level two index: leaf node storage (indexed column data: line number of data on disk)

Contrast:

InnoDB provides transaction support transactions, foreign keys and other functions;MyISAM not supported.

InnoDB supports row-level locks;MyISAM only supports table-level locks

InnoDB requires a primary key;MyISAM allows tables without any indexes and primary keys to exist, and the index is the address of the save row.

Overwrite Index

An index that contains (or overrides) the values of all fields that need to be queried

Overwrite index to store the value of the indexed column, only the B-tree index can be used to overwrite the index (not hash index, full-text index, etc.)

Advantages:
1. the MyISAM storage engine stores only the index in memory, overwriting the index does not require a system call;

2. InnoDB Storage Engine clustered index mechanism, two-level primary key if the query can be overwritten, you can avoid the primary key index of two queries;

Indexes and Locks

An index can cause a query to lock fewer rows,InnoDB locks only when the row is accessed, and the index reduces the number of rows accessed by InnoDB, thereby reducing the amount of locks;

< Span style= "font-family: Song body; font-size:14px" > However, only if Innodb innodb Retrieves data and returns it to the server layer, mysql where statements are filtered, and innodb These rows have been locked until the server layer filter is complete and the lock is released;

< Span style= "font-family: Song body; font-size:14px" > example: select actor_id from Sakila.actor where actor_id < 5 ( ) and actor_id <> 1 &NBSP; ( filter for Update;

< Span style= "font-family: Song body; font-size:14px" > execute explain command, display type range mysql The execution plan selected for the query is an indexed range query, that is, only the is executed at the storage engine level. actor_id < 5 condition, query result: 2,3,4 1,2,3,4

even if you use an index, you may lock some rows that you do not need, but if you do not use an index lookup , MySQL will scan all tables and lock all rows.

Database index (ii) clustered/nonclustered indexes, indexes, and locks