In-depth analysis of MySQL database indexes

What is an index?

An index is a data structure used by the storage engine to quickly find records. An index is similar to a directory in a book. We can quickly find the content we are interested in based on the directory. Index is the Directory of the storage engine. If there is no index storage engine, you must traverse the entire database table to query qualified records. Index creation and optimization should be the most effective way to improve query performance.

Index type

Indexes are implemented at the storage engine layer of MYSQL, rather than at the service layer. Therefore, the indexes of each storage engine are not necessarily the same, and not all storage engines support all index types. Even if multiple storage engines support the same type of index, the underlying implementation may be different.

B-Tree index

B-Tree is the default index type of MyISAM and InnoDB engines. You can also use USING BTTREE to display the specified index. B-Tree is a multi-cross balancing Tree. The B-Tree structure can significantly reduce the intermediate process encountered when locating records, thus accelerating access. It is generally used for database indexing and has a high overall efficiency.

Application scenarios of B-Tree indexes

Equivalent matching

Can Be Used for =! = <> In not in <=> query statement optimization

Range matching

It can be used to optimize range query statements such as >=< = bteween and.

Match the leftmost prefix

For post-fuzzy match queries such as name like bai %, the index created on the name field can be used to optimize the query, however, no index can be used for prefix fuzzy match queries such as name like % bai.

Overwrite index

Covering indexes means that all fields to be queried already exist in the index, so you do not need to query data any more. This query is highly efficient.
Select id where id> 100

Sort

B-Tree indexes are sorted, so MySQL can be used for ORDER BY and GROUP BY operations.

HASH)

Hash indexes are implemented based on Hash tables. Only the Memory engine supports Hash indexes and can be located at a time using Hash indexes. Therefore, the query efficiency of Hash indexes is much higher than that of B-Tree indexes. However, there are many restrictions on hash indexes:

Only queries that precisely match all columns of the index are valid, because the hash index uses the field values of all columns of the index to calculate the hash value,
Only query by equivalent comparison is supported and cannot be used for range query.

The hash index only contains the hash value he of the index field and the pointer to the data. Therefore, you cannot use the value in the index to avoid reading rows.
The hash index data is not stored in sequence and cannot be used for sorting.

Full text index (FULLTEXT)

Full-text index is a way to quickly match documents by creating inverted indexes.

SPATIAL index (SPATIAL)

Clustered index & non-clustered index

Clustered index

Clustered index is not a separate index type, but a data storage method. Innode clustered index stores the PRIMARY kEY and data rows in the same file, A table can have only one clustered index.

InnoDB requires that the table must have a primary key (which is not available in MyISAM). If it is not explicitly specified, MySQL uses a unique and non-null index column as the primary key. If this column does not exist, mySQL automatically generates an implicit field for the InnoDB table as the primary key.

The leaf node of INNODB's common Index (secondary index) stores the primary key value. Therefore, you must first query the leaf node of the general index (secondary index) to find the corresponding primary key value, then, the corresponding data is queried in the clustered index based on the primary key value.

InnoDB aggregates primary keys and data so that insertion and query in the primary key order is highly efficient, however, it takes a lot of time to update the primary key field or insert data without the primary key order, therefore, primary key selection is very important (using the auto increment field or the sequentially increasing field generated by the application is much better than the unordered UUID)
Secondary indexes store the value of the primary key, so the value of the primary key should not be too large.

Non-clustered index

Non-clustered index indexes and data are stored in different files. For a table in the MyISAM engine, there are three types of files: FRM (table structure) and MYD (data, that is, each row in the database) and MYI (index ).

When MySQl uses an index to query data, it first finds the data storage location pointer in the MYI file and then reads the data in the MYD file.

In MyISAM, the PRIMARY key index and other indexes have no difference in structure. The PRIMARY key index is a unique non-null index named PRIMARY.

Index operations

Create

You can CREATE an INDEX when executing the create table statement, or use the create index or alter table statement to add an INDEX to the TABLE.

CREATE TABLE
Create table table_name (
Column_name data_type,
......
[UNIQUE | FULLTEXT | SPATIAL] {INDEX | KEY} index_name [USING {BTREE | HASH}] (col_name [(length)] [ASC | DESC]...)
);
ALTER TABLE
Alter table table_name ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [USING {BTREE | HASH}] (col_name [(length)] [ASC | DESC]...)
Alter table table_name add primary key (col_name [(length)] [ASC | DESC]...)
CREATE INDEX
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [USING {BTREE | HASH}] ON tbl_name (col_name [(length)] [ASC | DESC],...)

Delete

Drop index index_name ON talbe_name
Alter table table_name drop index index_name

View

Show index from table_name

Efficient Index policy

Although it can be tempting to create an indexes for every possible column used in a query, unnecessary indexes waste space and waste time for MySQL to determine which indexes to use. indexes also add to the cost of inserts, updates, and deletes because each index must be updated. you must find the right balance to achieve fast queries using the optimal set of indexes.