The index in MySQL

an index base

1. Definition:

In MySQL, the index is also called "Key", which is a data structure used by the storage engine to quickly find records, which can improve query efficiency.

The most effective means of optimizing query performance is index optimization.

2, Working principle:

　　In MySQL, indexes are implemented at the storage engine level, not at the server level.

In MySQL, the storage engine uses the index in a similar way, its first finds the corresponding value in the index, finds the corresponding data row based on the matching index record, and finally returns the data result set to the client.

3. Index Type:

(1) General index:

Also called a normal index (index or key), you can have more than one regular index in a data table.

Typically there is no type that indicates an index, which refers to a regular index.

(2) Primary key index-primary key

The primary key, which provides a uniqueness constraint. There can be only one primary key in a table.

Fields marked for autogrow must be primary, but the primary key is not necessarily autogrow.

It is common to define a primary key on a field such as a number, whose data type is preferably numeric.

(3) Unique index-Unique key

Provides uniqueness constraints. You can have multiple unique indexes in a single table.

(4) Full text index

Can improve the query efficiency of full-text search, generally use sphinx substitution, but Sphinx does not support Chinese retrieval.

Coreseek is a full-text search engine that supports Chinese, also known as a sphinx with Chinese word segmentation capabilities.

In the actual project, Coreseek is used.

(5) Foreign key index-Foreign key

For short, foreign keys are automatically associated with the primary key of the corresponding other table.

The main role of foreign keys is to ensure consistency and integrity of records, but because the foreign key is not very efficient, it is not recommended to use foreign keys.

Note: Foreign keys are supported only for tables with the InnoDB storage engine. If you want to delete a record in the parent class, you must first delete the corresponding record in the child table, or you will get an error.

4, the method of indexing

There are mainly the following types:

(1) B-tree index:

If the type is not specifically specified, then the B-tree index is generally said. B-tree is stored sequentially for indexes, and therefore is suitable for finding range data.

Because the storage engine does not need a full table scan to get the data it needs, it can speed up access to the data.

Note: Different storage engines use B-tree indexes in different ways, and the performance varies.

For example: MyISAM uses prefix compression technology to make the index smaller, but InnoDB stores the index in the original data format.

Again, MyISAM refers to the rows that are indexed by the physical location of the data, while InnoDB references the rows that are indexed according to the primary key.

　　

Data source: MySQL index details

The index in MySQL