MySQL---Index

Index is a data structure in the database that is designed to help users quickly query data. Similar to a directory in a dictionary, you can find the contents of the dictionary based on the directory to locate the data, and then get directly.

stored in b-tree form :

1                     2  3                        4  5    5             6   7 1   6     100

Common indexes in MySQL are:

• Normal index
• Unique index
• Primary key Index
• Combined index

1. General Index

The normal index has only one function: Accelerated query

1 CREATE TABLE in1 (2      not NULL Auto_increment primary key, 3      not NULL, 4      not NULL, 5     Extra text,6    index Ix_name (name)7 )

CREATE TABLE + index

1 CREATE INDEX index_name on table_name (column_name)

Create an index

1 drop index_name on table_name;

Delete Index

1  from table_name;

View Index

Note: If you are creating an index with a blob and TEXT type, you must specify length.

1 CREATE index Ix_extra on in1 (extra (32));

View Code

2. Unique index

Unique index has two functions: Accelerated Query and UNIQUE constraint (can contain null)

1 CREATE TABLE in1 (2      not NULL Auto_increment primary key, 3      not NULL, 4      not NULL, 5     Extra text,6    unique ix_name (name)7 )

CREATE TABLE + unique index

1 create unique index index name on table name (column name);

Create a unique index

1 Drop unique index index name on table name;

To Delete a unique index

3. Primary KEY index

Primary key has two functions: Accelerated Query and UNIQUE constraint (non-nullable)

1 CREATE TABLE in1 (2Nid int notNULL Auto_increment primary key,3Name varchar (32) notNULL,4Email varchar (64) notNULL,5 extra text,6 index Ix_name (name)7 )8 9 ORTen  One CREATE TABLE in1 ( ANid int notnull auto_increment, -Name varchar (32) notNULL, -Email varchar (64) notNULL, the extra text, - primary KEY (NI1), - index Ix_name (name) -)

CREATE TABLE + Create PRIMARY key

1 ALTER TABLE name add primary key (column name);

Create a primary key

1 ALTER TABLE name drop PRIMARY key; 2 ALTER TABLE name  modify  column name int, drop primary key;

Delete primary Key

4. Combined Index

A composite index is the combination of n columns into one index

The application scenario is: Frequent simultaneous use of n columns for querying, such as: where N1 = ' Alex ' and N2 = 666.

1 CREATE TABLE in3 (2      not NULL Auto_increment primary key, 3      not NULL, 4      not NULL, 5     Extra Text6 )

Create a table

1 CREATE index ix_name_email on in3 (Name,email);

Create a composite index

After creating the combined index, the query (leftmost prefix):

• Name and email--Using the index
• Name--Using the index
• Email-Don't use index

Note: The performance of a composite index is better than multiple single-index merges for simultaneous search of n conditions.

Add

1. Overlay Index

 from TB where nid=1; # Look in the index first. # go to the data and find   from TB where nid<10; # go to the index first (you can get the data in the Index table only) #  The condition is applied on the index, and does not go to the data table operation, that is, overwrite the index . 

2. Merging Indexes (depending on business requirements)

MySQL---Index