Shulou(Shulou.com)06/01 Report--

Index of day04 MySQL database

I. Overview of the index:

An index is made up of one or more columns in a table, and the purpose of creating an index is to optimize the management of our database tables and improve the speed at which our queries use database tables.

II. Index

1. Classification of the index:

Indexes are divided into multiple indexes, and the specific indexes can be seen in the following figure

General index: an index that does not apply any restrictions and can be created in any type of database.

Unique index: use the unique parameter to set a unique index. Must be unique, the primary key is a special unique index.

Full-text index: use the fulltext parameter to set the full-text index of the index, which can only be created on fields of type char, varchar, or text. If you query a large amount of data, you can use the full-text index directly.

Single-column index: an index that corresponds to only one field. The index is applied only by ensuring that the index value corresponds to one field.

Multi-column index: creates an index on multiple fields in a table.

Spatial index: use the spatial parameter to set the index to spatial index. Can only be based on spatial data types. The fields of the index cannot be null values.

2. Create an index

Basic syntax:

Create table table name (attribute name 1 data type [constraint], attribute name 2 data type,. [unique | fulltext | spatial | index] key)

2.1. Create a general index

Create table table name (attribute name 1 data type [constraint], attribute name 2 data type,. Index (attribute name 1)

2.2. Create a unique index

Create table table name (attribute name 1 data type [constraint], attribute name 2 data type,. Unique index (attribute name 1)

2.3. Create a full-text index

Create table table name (attribute name 1 data type [constraint], attribute name 2 data type,. Fulltext key (attribute name 1)

3. Create an index in an existing database table

3.1. Create a general index

Create index index name on data table name (field name)

3.2. Create a unique index

Create unique index index name on data table name (field name)

3.3. Create a full-text index

Create fulltext index index name on data table name (field name)

3.4. Create a single-column index

Create index index name on data table name (field name (length))

3.5. Create a single-column index

Create index index name on datasheet name (field name 1, field name 2...)

3.6. Create a spatial index

Create spatial index index name on data table name (field name)

4. Add an index

4.1. Add a general index

Alter table table name add index index name (field name)

4.2. Add a unique index

Alter table table name add unique index index name (field name)

4.3. Add a full-text index

Alter table table name add fulltext index index name (field name)

4.4. Add a single-column index

Alter table table name add index index name (field name (length))

4.5. Add a multi-column index

Alter table table name add index index name (field name, field name 2...)

4.6. Add a spatial index

Alter table table name add spatial index index name (field name)

5. Delete the index

Drop index index name on database table name

III. Concluding remarks:

At this point, we have almost finished learning the index, these are some of the more important concepts of the index. We need to master it and make good use of it. Thank you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.