MySQL Getting started is simple: 5 index

1. Meaning and characteristics of the index

Index: Created on a table, is a structure that sorts the values of one or more columns in a database table.

Storage type: B-Tree (BTREE) index and hash index; InnoDB and MyISAM Support BTREE index, memory support BTREE index and hash index

Pros and Cons: advantages--Improve the speed of retrieving data; Cons: Creating and maintaining indexes takes time

2. Index classification

1) Normal index: No restrictions

2) Uniqueness Index: Use unique parameter (primary key is a special uniqueness index)

3) Full-text indexing: Use the fulltext parameter to create only fields on char varcher or text type

4) Single-column index: Create an index on a single field in a table, a uniqueness index, or a full-text index

5) Multi-column index: Create an index on multiple fields of a table; a uniqueness index or full-text index

6) Spatial index: Using spatial parameters, only the MyISAM storage engine supports spatial indexes, must be built on spatial data types, and must be non-empty

3. How to design an index

4. Create an index

Three ways: Create an index when creating a table, create an index on a table that already exists, and use the ALTER TABLE statement to create

4.1 Creating an index when creating a table

1) Create a normal index

indexing the ID field of a table CREATE TABLE INT  VARCHAR(a), sex BOOLEAN,INDEX(id));

2) Create a Uniqueness Index

CREATE TABLE INT UNIQUE  VARCHAR(),UNIQUEINDEXASC);

3) Create a full-text index

CREATE TABLE INT  VARCHAR(INDEX  index3_info (info)) ENGINE=MyISAM;

4) Create a single-column index

CREATE TABLE INT  VARCHAR(+),INDEX index4_st (subject (ten)));

5) Creating Multi-column indexes

CREATE TABLE INT  VARCHAR(4CHAR),INDEX  index5_ NS (name, sex));

6) Create a spatial index

CREATE TABLE INT  , Space  not NULL  INDEX index6_sp (space)) ENGINE=MyISAM;

4.2 Creating an index on a table that already exists

4.3 Creating an index with an ALTER TABLE statement

5. Deleting an index

MySQL Getting started is simple: 5 index