Mysql index description

Mysql index description

I. What is an index?

An index is an independent storage unit in which the physical space of a field and field in the data table is recorded. Indexes are supported by algorithms, which can make queries very fast.

With the index, we can query data based on the index, which is very fast.

1. The index itself supports algorithms to quickly locate the keywords (fields) We want to find)

2. The index field corresponds directly to the physical address to help us quickly locate the information to be found.

You can set indexes for all fields in a data table.

2. Index types

1, four types:

(1) primary key parimary key

Auto_increment must be set for the primary key index. The value of the index column must not be null and must be unique.

(2) unique index

The value of the index Column cannot be repeated, but null values are allowed.

(3) Common index

The value of the index column can be repeated.

(4) Full Text index fulltext index

You can set this index for the Myisam data table.

2. Composite Index

An index is composed of two or more columns. It is called a composite index or a composite index.

3. Create an index

　　1. When creating a table

1) create a member table and create various indexes.

Create table member (
Id int not null auto_increment comment 'Primary key ',
Name char (10) not null default ''comment' name ',
Height tinyint not null default 0 comment 'height ',
Old tinyint not null default 0 comment 'age ',
School varchar (32) not null default ''comment 'school ',
Intro text comment 'Introduction ',
Primary key (id), // primary key index
Unique index nm (name), // unique index, index can also be set name, if not set name, default field name
Index (height), // normal index
Fulltext index (intro) // full-text index
) Engine = myisam charset = utf8;

2) add an index to an existing data table

Alter table member add primary key (id); // Note: After a primary key is set, the primary key field is set to auto-increment. (Alter table member modify id int not null auto_increment comment 'Primary key ';)

Alter table member add unique key nm (name );

Alter table member add index (height );

Alter table member add fulltext index (intro );

3) create a composite index (the index has no name, and the first field is taken out as the name by default)

Alter table member add unique key nm (name, height );

2. delete an index

Alter table Name drop primary key; // Delete the primary key index

Note: If this primary key field has the auto_increment attribute, delete it first. (Alter table name modify primary key int not null comment 'Primary key ')

Remove the auto_increment attribute of the data table field;

Alter table Name drop index name; // delete other indexes (unique, common, full text)

Eg: alter table member drop index nm;

Iv. explain to check whether the index is used

Specific Operation: explain to query SQL statements

This is the case where primary key indexes are not set: (execution speed and efficiency are low)

　　

After the primary key is added:

　　

5. Suitable index scenarios

1. where query conditions (the query condition fields set after where are suitable for indexing ).

2. Sort query (order by field)

Vi. indexing principles

1. Field Independence Principle

Select * from emp where empno = 1325467; // The empno condition is independent and indexes are used.

Select * from emp where empno + 2 = 1325467; // The empno condition is not independent. Only the independent condition field can use the index.

2. Left Principle

Fuzzy query, like &_

%: Associate multiple fuzzy content

_: Associate a fuzzy content.

Select * form table name where a like "beijing %"; // use the index

Select * from table name where a like "beijing _"; // use the index

Select * from table name where a like "% beijing %"; // No index is used.

Select * from table name where a like "% beijing"; // No index is used

3. Composite index (a, B)

Select * from table name where a like "beijing %"; // use the index

Select * from table name where B like "beijing %; // No index is used

Select * form table name where a like "beijing %" and B like "beijng %"; // use the index

4. or Principle

OR and the left and right association conditions must have an index to use the index.

Eg (index (a), index (B ))

Select * from table name where a = 1 or B = 1; // use the index

Select * from table name where a = 1 or c = 1; // No index is used