Operation of MYSQL Index

A: why

Because the data is stored in a database table, the index is created on a database table object, consisting of keys generated by one field or multiple fields in the table, stored in a data structure (b-tree or hash table) that can quickly and efficiently find fields associated with a key value through MySQL. Depending on the storage type of the index, you can divide the index into

B-Tree index (BTREE)

Hashed index (hash)

Note: The InnoDB and MyISAM storage engines support the Btree type index, and the memory storage engine supports hash type indexes, which are indexed by default.

II: what

MySQL supports 6 kinds of indexes: Normal index unique index full-text index single index multi-column index spatial index

Three: how

Creating and viewing Indexes

Create indexes when creating tables

CREATE TABLE table_name (

Property name Data type,

...

Property name Data type,

Index|key "Index Name" (Property name "(length)" "asc| DESC ") #普通索引

Unique Index|key "Index name" (Property name 1 "(length)" "(asc| DESC) "#唯一索引

Fulltext Index|key "index name" (Property name 1 "(length)" "(asc| DESC) "#全文索引

)；

CREATE TABLE T_dept (

Deptno int,

Dname varchar (20),

...

Index Index_deptno (DEPTNO)

Unique index Index_deptno (deptno)

Fulltext Index Index_loc (Loc)

);

Show CREATE table T_dept \g;

Explain

SELECT * from t_dept where deptno=1\g;

Create a normal index on a table that already exists

CREATE Index Index name

Create unique index index name

On table name (property name "(length)" "asc| DESC ")

///...

CREATE INDEX Index_deptno

Create UNIQUE index Index_deptno

On t_dept (DEPTNO);

///...

To create a normal index from an SQL statement ALTER TABLE

ALTER TABLE table_name

ADD index| KEY Index Name (property name "(length)" "(asc| DESC) ";

ADD UNIQUE index| KEY Index Name (property name "(length)" "(asc| DESC) "

///...

ALTER TABLE t_dept

Add index Index_deptno (DEPTNO);

Add unique index Index_deptno (DEPTNO);

///...

Create and view multi-column Indexes

#1:

CREATE TABLE T_dept (

Deptno int,

Dname varchar (20),

Loc varchar (40),

Key Index_dname_loc (Dname,loc)

);

#2:

CREATE INDEX Index_dname_loc

On t_dept (DNAME,LOC);

#3:

ALTER TABLE t_dept

Add index Index_dname_loc (dname,loc);

Delete Index

Drop INDEX Index_name

On table_name

Operation of MYSQL Index