Federated index, prefix index, overwrite index in MySQL

Index

An index is a special kind of file that contains reference pointers to all the records in the data table. More generally, the database index is like a directory in front of a book, which can speed up the database query.

Federated Index

Also known as a composite index, indexed by two or more columns. It specifies that MySQL uses indexed fields from left to right, with certain requirements for the order of the fields. A query can use only a portion of the index, or, more accurately, the leftmost part (leftmost priority). such as index is key index (A,B,C). can support a | a,b| A,b,c 3 combinations to find, but B,c is not supported. The index is very effective when the leftmost field is a constant reference.

CREATE TABLE test(a INT, b INT, c INT, KEY(a, b, c));

• 1

Prefix index

For columns with longer values, such as BLOBs, TEXT, VARCHAR, prefix indexes must be established, and the first part of the value will be indexed. This can not only save space, but also improve query efficiency. However, you cannot use the prefix index for both ORDER by and GROUP by, and you cannot use the prefix index for overwrite scans.

ALTER TABLE table_name ADD KEY(column_name(prefix_length)); # 示例 ALTER TABLE city ADD KEY(cityname(7))

• 1
• 2
• 3
• 4

Overwrite Index

A bit similar to the federated Index, which is to fetch the data only by reading the index when querying T, without having to query the related table two times. The leaf nodes of such indexes also contain the data of their index.

Judging criteria: Using explain, can be judged by the output of the extra column, for an index overlay query, displayed as a using Index,mysql query optimizer will determine whether there is an index overwrite the query before executing the query.

Federated index, prefix index, overwrite index in MySQL