MySQL index principle and usage example analysis

This article uses examples to illustrate the principles and usage of MySQL indexes. Share with you for your reference, the details are as follows:

What is an index:

Indexes can help find data quickly
Basically , all indexes are required to be unique (some are not), so to some extent, the uniqueness of the data is also constrained.
An index is created on a data table object and consists of one or more fields. These fields form a "key" and are stored in a data structure (B-tree or hash table). [It can be classified into B-tree index (innodb\myisam engine) and hash index (memory engine) according to the data structure]
Since indexes are used to speed up data search, they are generally used on fields that are often needed for search (such as commonly used fields in where).
The index types supported by MySQL are: common index, unique index, full-text index, single-column index, multi-column index, and spatial index.

Replenish:

The difference between primary key and index: primary key is also a kind of index, and primary key is also a unique index, but the main function of index is to improve the search speed, while the main function of primary key is to identify the uniqueness of record (of course, it also facilitates search).

Create an index:

Normal index: index

A normal index is an index without the "unique" or "not empty" requirements.
grammar:
- create table table name (field data type, field data type, ... index|key [index name] (field [index length] [asc|desc]));
  - index|key means you can use the index keyword or the key keyword
  - The index name is optional. If it is not filled in, the index name is the field name.
  - Field is an indexed field, and there can be multiple fields ( multi-column index ).
  - The length of the index, optional (some engines set a maximum length, which is rarely encountered without in-depth study, so it is not described here)
  - asc|desc is optional and represents the sorting of the "keywords" in the index
- To add an index to an existing table structure: create index index name on table name (field [index length] [asc|desc]);
- To add an index to an existing table structure: alter table table name add index|key index name (field [index length] [asc|desc]);

Unique Index:

A unique index requires that the data in the field be unique when creating an index.
Create syntax:
- create table table name (field data type, field data type, ... unique index|key [index name] (field [index length] [asc|desc]));
  - [Parameter information refers to the common index]
- To add an index to an existing table structure: create unique index index name on table name (field [index length] [asc|desc]);
- To add an index to an existing table structure: alter table table name add unqiue index|key index name (field [index length] [asc|desc]);

Full text index: fulltext index

Full-text indexes are generally used on fields with data types of char, varchar, and text to facilitate searching for these longer data.
MyISAM storage engine supports full-text indexing
Create syntax:
- create table table name (field data type, field data type, ... fulltext index|key [index name] (field [index length] [asc|desc]));
  - [Parameter information refers to the common index]
- To add an index to an existing table structure: create fulltext index index name on table name (field [index length] [asc|desc]);
- To add an index to an existing table structure: alter table table name add fulltext index|key index name (field [index length] [asc|desc]);

Single column index:

When there is only one indexed field, it is a single column index.

Multi-column index:

When there are multiple indexed fields, it is a multi-column index.
Creation syntax: Just change the above (field [index length] [asc|desc]) to (field1 [index length] [asc|desc],field2 [index length] [asc|desc],field1 [index length] [asc|desc]).

View index:

You can view the index by viewing the table structure
You can view the index by viewing the table creation statement
You can use the explain statement to view the index. Explain is used to determine the time efficiency of MySQL statement execution.
- So you can use the explain select statement where plus the indexed field as a condition;
- The possible_key in the result is the index that may be used, and the index that key actually uses.
- for example:
```
explain select * from user_info where username="lilei"; 
```

To delete an index:

drop index index name on table name;
- for example:
```
drop index myindex on user_info;
```

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in: