This is the first day of my participation in the Gwen Challenge in November. Check out the details: the last Gwen Challenge in 2021

1, What is the index index

Definition: a data structure that helps MySQL improve query efficiency;

Advantages:

1. Greatly speed up data query;

Disadvantages:

1, the maintenance of index needs to consume database resources;

2. Indexes occupy disk space.

3. When adding, deleting, or modifying data to a table, the speed will be affected because the index needs to be maintained.

(So indexes are built on infrequently updated fields, commonly used search fields)

2. Index classification

1. Primary key index

When the primary key is set, the database will automatically create indexes. Innodb is a clustered index.

2. Single-value index

That is, an index contains only a single column, and a table can have multiple single-column indexes.

3. Unique index

Index column values must be unique, but null values (multiple NULL values can be allowed)

4. Composite indexes

That is, an index contains multiple columns

5, Full Text index (MySQL version before, only due to MYISAM engine)

The full-text index type is FULL TEXT, and supports full-text lookup of values on the columns that define the index, allowing the insertion of duplicate and null values in those index columns.

Full-text indexes can be created on CHAR, VARCHAR, or TEXT columns.

MYSQL only MYISAM storage engine supports full-text indexing.

3. Basic operation of index

Select * from index;

show index from t_user;
Copy the code

Drop index

dropThe index index nameonThe name of the tableCopy the code

1. Primary key index

A primary key index is automatically created when a table is created with a primary key.

2. Common indexes

Create table (s);

create table t_user(id varchar(20) primary key,name varchar(20),key(name)
Copy the code

Alter table create;

create index name_index on t_user(name);
Copy the code

3. Unique index

Created when the table is created

create table t_user(id varchar(20) primary key,name varchar(20),unique(name))
Copy the code

Create a table after it is created

create unique index on t_user
Copy the code

4. Composite indexes

Created when the table is created

CREATE TABLE tb_test2(
  id INT,
  `name` VARCHAR(200),
  age INT,
  KEY(`name`,age)
)
Copy the code

Create a table after it is created

create index name_age_index on t_user(name,age)
Copy the code

Use of composite indexes (interview questions)

name age bir

1. Left-most prefix rule;

2. In order to make better use of indexes, the mysql engine dynamically adjusts the query order during the query to make better use of indexes

Can the following query fields be used to match the index:

Name Bir Age Yes Name Age Bir Yes age Bir no Bir Age Name Yes age bir noCopy the code

4. Clustered index and non-clustered index

InnoDB data is stored directly in leaf nodes;

The MyISAM leaf node stores the disk address of the data;

Clustering index

You put the data store with the index, and you find the index and you find the data

Non-clustered index

Myisam uses key_buffer to cache the index in memory. When it needs to access data (through the index), myISam directly searches the index in memory, and then finds the corresponding data on disk through the index. This is why indexes are slow when they are not hit by the key buffer.

To clarify a concept: innodb, above the clustering index created index called secondary index, auxiliary index access data is always need a second search, the clustering index is auxiliary index, as a composite index, the prefix index, the only index, auxiliary index leaf node storage is no longer the physical location, but the primary key

When to use clustered and non-clustered indexes

Must a non-clustered index be queried back into the table? Not necessarily. This involves whether all the fields required by the query match the index. If all the fields match the index, then there is no need to perform the query back to the table.

Select age from employee where age < 20; select age from employee where age < 20; select age from employee where age < 20;