MYSQL database index

Index overview

In MySQL, an index is composed of one or more columns in a data table. The purpose of creating an index is to optimize the query speed of the database. Where, the index created by the user points to the specific data location in the database. When users query the data in the database by index, they do not need to traverse all the data in all databases. In this way, the query efficiency is greatly improved.

MySQL index overview

1. Index is a structure that sorts the values of single or multiple columns in a database. The application of index can greatly improve the speed of query.
2. Users can not only improve the query speed, but also reduce the load of the server by index query data.
3. When applying MySQL database, users do not always need to apply index to optimize query when querying data.
4. On the whole, index can improve the query speed, but it will affect the user's operation of database insertion.

MySQL index classification

MySQL index includes general index, unique index, full-text index, single column index, multi column index and spatial index.

Create index

Index creation refers to the establishment of indexes in at least one column of a table in order to improve database performance. Among them, indexing can improve the access speed of the table. This section creates indexes in several different ways. It includes creating indexes when building a database, creating indexes in established data tables, and modifying data table structure to create indexes.

Create index when building data table

When building data table, index can be created directly, which is more direct, convenient and easy to use.

The basic syntax structure is as follows:

create table table_name(  
Attribute name data type [constraint],
Property name data type [constraint]
......
Property name data type
[UNIQUE | FULLTEXT | SPATIAL ]  INDEX }KEY
 [alias] (attribute name 1 [(length)] [ASC | DESC])
);

Example:

General index

create table t_dept(
no int not null primary key,
name varchar(20) null,
sex varchar(2) null,
info varchar(20) null,
index index_no(no)
)
show index from info; View index

unique index

create table t_dept(
no int not null primary key,
name varchar(20) null,
sex varchar(2) null,
info varchar(20) null,
unique index index_no(no)
);

Full-text index

create table t_dept(
no int not null primary key,
name varchar(20) null,
sex varchar(2) null,
info varchar(20) null,
fulltext index index_no(no);

Multi column index

create table t_dept(
no int not null primary key,
name varchar(20) null,
sex varchar(2) null,
info varchar(20) null,
key index_no_name(no,name)
);

Create an index in an established data table

In MySQL, you can not only create indexes when users create data tables, but also directly create indexes in one or several existing fields in the created tables.

The basic command structure is as follows:

CREATE [UNIQUE | FULLTEXT |SPATIAL ] INDEX index_name
ON table_name(attribute [(length)] [ ASC | DESC]);

Example:

General index

create index index_name
on t_dept(name);

unique index

create unique index index_name
on t_dept(name);

Full-text index

create fulltext index index_name
on t_dept(name);

Multi column index

create index index_name_no
on t_dept(name,no)

Modify data table structure to add index

Modify an index that already exists on a table. You can add indexes to a data table through the ALTER TABLE statement

The basic structure is as follows:

ALTER TABLE table_name ADD [ UNIQUE | FULLTEXT |SPATIAL ] INDEX index_name(Attribute name [(length)] [ASC | DESC]);

Example:

General index

alter table t_dept
add index index_name(name);

unique index

alter table t_dept
add unique index index_name(name);

Full-text index

alter table t_dept
add fulltext index_name(name);

Multi column index

alter table t_dept
add index index_name_no(name,no);

Delete index

Delete index

In MySQL, after the index is created, if the user no longer needs the index, the index of the specified table can be deleted. Because these indexes that have been established and are not often used, on the one hand, they may occupy system resources, on the other hand, they may lead to a decrease in update speed, which greatly affects the performance of data tables. Therefore, when the user does not need the index of the table, the specified index can be deleted manually. The DROP statement can be used to delete the index.

The basic commands are as follows:

DROP INDEX index_name ON table_name;

Tags: MySQL Database Attribute

Posted on Thu, 12 Mar 2020 02:08:04 -0700 by phpcodec