Shulou(Shulou.com)06/01 Report--

The following is a detailed knowledge of mysql indexing. The secret of the text is that it is relevant to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading the detailed knowledge of mysql index.

One: what is an index

The index itself is a separate storage unit in which there is a physical space for recording a field and a field in the data table. There is algorithm support inside the index, which can make the query speed very fast. [recommended for related video tutorials: mysql tutorial]

With the index, we query the data according to the index, and the speed is very fast.

1. The index itself has "algorithm" support, which can quickly locate the keywords (fields) we are looking for.

2. The index field corresponds directly to the physical address, which helps us to quickly locate the information we are looking for.

All fields of a data table can be indexed.

Second, index type

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, but unique.

(2) unique unique index

The value of the index column cannot be duplicated, but null values are allowed

(3) General index index

The value of the index column can be repeated.

(4) full-text index fulltext index

The index can be set for the Myisam data table

2, composite index

An index consists of two or more columns, which is called a composite index or a federated index.

Third, 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 The index can also set the name If the name is not set, the default field name is index (height), / / general index fulltext index (intro) / / full-text index) engine = myisam charset = utf8

2) to add an index to an existing data table

/ Note: generally, after setting the primary key, the primary key field will be set to self-increasing. (alter table member modify id int not null auto_increment comment 'primary key';) alter table member add primary key (id); alter table member add unique key nm (name); alter table member add index (height); alter table member add fulltext index (intro)

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

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

2, delete the index

Alter table table name drop primary key;// delete primary key index

Note:

If the auto_increment attribute exists in the primary key field, it needs to be deleted first. (alter table table name modify primary key int not null comment 'primary key')

Remove the auto_increment attribute from the datasheet field

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

Example:

Alter table member drop index nm

4. Explain to check whether the index is used

Specific operation: explain query sql statement

This is the case where the primary key index is not set: (execution speed, low efficiency)

After adding the primary key:

Fifth, index the suitable scene

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

2. Sort query (order by field)

VI. Principles of indexing

1. The principle of field independence

Select * from emp where empno = 1325467 Spacer empno condition is independent, using index select * from emp where empno+2 = 1325467 Spacemno condition is not independent, only independent conditional fields can use index.

2, left principle

Fuzzy query, like & _

%: associate multiple obscure content

_: associate a vague content

Example:

Select * form table name where a like "beijing%"; / use index select * from table name where a like "beijing_"; / use index select * from table name where a like "% beijing%"; / / do not use index select * from table name where a like "% beijing"; / / do not use index

3, composite index index (aformab)

Select * from table name where a like "beijing%"; / / use index select * from table name where b like "beijing%;// does not use index select * form table name where a like" beijing% "and b like" beijng% "; / / use index

4the principle of "or"

All the association conditions around OR must have an index before you can use the index.

Example: (index (a), index (b))

Select * from table name where a = 1 or b = 1 select / use index select * from table name where a = 1 or c = 1 Bandpool / no index is used

Is there anything you don't understand about the detailed knowledge of the above mysql index? Or if you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.