I. What is an index

In a relational database, an index is a storage structure that separates and physically sorts the values ​​of one or more columns in a database table.A list of logical pointers to the data pages in the table that physically identify these values.The function of the index is equivalent to the directory of the book, which can quickly find the desired content according to the page number in the directory.

Index (index): like the directory of a book, used to speed up the search efficiency;

The role of index: speed up search efficiency. Slow down insertion and deletion, modification efficiency. (Need to adjust index results synchronously)

Second, common index types

1. Primary key index
The primary key is a unique index and must be specified as PRIMARY KEY (ie primary key)

    alter table table name add primary key (`field name`);

2. Unique index
All values ​​of the index column can only appear once (that is, must be unique), and the value can be null

    alter table table name add primary key (`field name`);

3. Ordinary index
The basic index type, the value can be null, there is no unique constraint

    alter table table name add index (`field name`);

4. Full-text index
The index of full-text index is FULLTEXT type.Full-text indexes can be created on columns of type varchar, char, and text.Can be created via ALTER TABLE or CREATE INDEX

    alter table table name add FULLTEXT(`field name`);

Three, under what circumstances does the index fail

1.Improper use of the like query will cause the index to fail
Wildcard % at the beginning will cause the index to fail
    select * from table name where field name like '%__';

2. The best left prefix rule
If there are multiple indexes in a table, the best left prefix rule should be followed, that is, the query starts from the leftmost front column of the index and does not skip the columns in the index.

3. The data types on both sides of the query condition are inconsistent