MySQL Focus--index

1. About Indexes

# What is an index

An index is a table of contents that allows you to quickly locate the query data by looking at the index location in the catalog before finding it.

#索引的作用

Accelerate queries and constraints.

# Why the index query will become faster

Not creating an index will create an index table accordingly. The index table is made up of corresponding data and B-tree numbers:

                30

         10 40     5 15 35 66 1 6 11 19 21 39 55 100The observation of the data structure shows that the lower left of each number is larger than his lower right.

The number structure above is the index directory of B-tree numbers, and each corresponding number represents an index target. If the index column has 1024 data,

Then it generates 10 layers of such data. That is, searching for each index target is averaged 10 times. Columns that do not create an index are traversed when looking for

The entire data structure, that is, up to 1024 times. The rate of ascension is conceivable.

2. Index type

• Normal index
• Unique index
• Primary key Index
• Combined index
• ========= Split ===========
• Overwrite Index
• Full-Text Indexing
• Index Merge

Normal index

Function: Speed up search

Condition: None (can be empty, can be repeated)

To create a method:

1. CREATE table when creating

CREATE TABLE in1 (

Nid int not NULL auto_increment primary key,

Name varchar (+) is not NULL,

Email varchar (+) NOT NULL,

Extra text,

Index Ix_name (name))
Where Ix_name is the name of the index

2.table added after creation (must meet index criteria)

CREATE INDEX index_name on table_name (column_name)

Unique Indexes Unique

Function: Speed up search

Condition: cannot be duplicated, can be null.

To create a method:

1. CREATE table when creating

CREATE TABLE in1 (

Nid int not NULL auto_increment primary key,

Name varchar (+) is not NULL,

Email varchar (+) NOT NULL,

Extra text,

Unique Ix_name (name))
Where Ix_name is the name of the index

2.table added after creation (must meet index criteria)

Create unique index index_name on table_name (COLUMN_NAME);

Primary key Index PRIMARY key

The primary key is a special index, and the index table is created at the same time that the primary key is created.

Function: Speed up search

Condition: cannot be duplicated, cannot be null.

To create a method:

1. CREATE table when creating

CREATE TABLE in1 (

Nid int not NULL auto_increment primary key,

Name varchar (+) is not NULL,

Email varchar (+) NOT NULL,

　　　　　　)

Or

CREATE TABLE in1 (

Nid int not NULL auto_increment,

Name varchar (+) is not NULL,

Email varchar (+) NOT NULL,

Primary KEY (NID)

)

2.table added after creation (must meet index criteria)

ALTER TABLE TABLE_NAME ADD PRIMARY key (NID);

Delete primary key

ALTER TABLE name drop PRIMARY key;

ALTER TABLE name modify column name int, drop primary key;

Combined index

A composite index is also called a federated (normal, unique) index. The fact is that when you create an index, you become multiple columns.

Function: Speed up search

Condition: According to the form of creation

To create a method:

1. CREATE table when creating

CREATE TABLE in1 (

Nid int not NULL auto_increment primary key,

Name varchar (+) is not NULL,

Email varchar (+) NOT NULL,

Extra text,

Unique Ix_name (Name,nid) # The unique index of the Union, or the only one that refers to the two columns, is the only one that combines.

("CCC", 3) and ("CCC", 4) can exist.
Where Ix_name is the name of the index

2.table added after creation (must meet index criteria)

Create unique index index_name on table_name (COLUMN_NAME);

　　Attention:

The combined index follows the leftmost match. i.e. (NID,NAME,ADDR) in this composite index, if there is

SELECT * FROM table_name WHERE name = "FFF" and addr = "Seatle"

Such statements do not go through the index. and nid and Addr will go index.

Delete method (except primary key)

Drop index_name on table_name;

View Query

Show index from TABLE_NAME;

Attention

The length must be specified when the column type is blob and text. The two are too long to be needed.

Create INDEX Ix_extra on in1 (extra (32));

===================== segmentation (both concepts) =======================

Overwrite Index

When overriding an index, it refers to the fact that there is no second step in the index to fetch the data in the data table, directly in the Index table.

such as select NID from Table_name where NID >10;

Since NID is the primary key or index, the data that is taken is the value of the index. The program simply converts the value of the B-tree into data. There is no second step.

Index Merge

Index merging is applied to more than two columns as a condition restriction during the indexing process. Different from the combined index.

SELECT * FROM table_name where nid = and name = "CCC";

Nid is the primary key, and name is the normal index. Such an index form becomes an index merge.

So the combination still has the limit of the leftmost prefix, why is there still a composite index?

Because, there is the case that the leftmost of the combined index is the root. For example, user name and password, no one will be separated from the user name to index password.

　　Also, the combined index is better than the rate of index merging in the case of multiple columns of this index

Other

Conditional statements

Delimiter \

CREATE PROCEDURE proc_if ()

BEGIN

declare i int default 0;

If i = 1 then SELECT 1;

ELSEIF i = 2 then SELECT 2;

ELSE SELECT 7;

END IF;

End\

delimiter;

Looping statements

Execute SQL statements Dynamically

# The data passed in is entered into some SQL statements.

Execute SQL Dynamically

MySQL Focus--index