What is a mysql index? A brief discussion on mysql index

What this article brings to you is what is the mysql index? Let's briefly talk about mysql index, so that everyone can have a simple understanding of mysql index. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

1: What is an index

The index itself is an independent storage unit, in which a certain field of the data table is recorded. and the physical space corresponding to the field. There is algorithm support inside the index, which can make the query speed very fast. [Recommended related video tutorials: mysql tutorial]

With the index, we can perform data query based on the index as a condition, and the speed is very fast

1. The index itself has " algorithm " support, which can quickly locate the keywords (fields) we want to find

2. Index fields has a direct correspondence with physical address, helping us quickly locate the information we want to find

All fields in a data table can be indexed

Second, index type

1, four types:

(1) Primary key primary key

Must set auto_increment to the primary key index, index The value of the column cannot be null and must be unique

(2) Unique unique index

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

(3) Normal Index index

The value of the index column can be repeated.

(4) Full text index fulltext index

Myisam data table can set this index

2, composite index

The index is composed of two or more Composed of columns, it is called a composite index or joint index.

3. Create an index

1. When creating a table

1), when creating a member table, create various indexes.

create table member(
　　id  int not null auto_increment comment '主键',
　　name char(10) not null default '' comment '姓名',
　　height tinyint not null default 0 comment '身高',
　　old tinyint not null default 0 comment '年龄',
　　school varchar(32) not null default '' comment '学校',
　　intro text comment '简介',
　　primary key (id),  // 主键索引
　　unique index nm (name), //唯一索引,索引也可以设置名称，不设置名字的话，默认字段名
　　index (height),   //普通索引
　　fulltext index (intro) //全文索引
)engine = myisam charset = utf8;

2), add an index to the existing data table

//注：一般设置主键后，会把主键字段设置为自增。（alter table member  modify id  int not null auto_increment comment '主键';）
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), create a composite index (the index has no name, the first field is taken out as the name by default)

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

2, delete index　

##

alter table 表名 drop primary key;//删除主键索引

Note:

If the auto_increment attribute exists in this 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 of the data table field;

alter table 表名 drop  index 索引名称;  //删除其它索引（唯一，普通，全文）

Example:

alter table member drop index nm;

4. explain Check whether the index is used

Specific operation:

explain Query sql statement

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

After adding the primary key:

5. Suitable scenarios for indexing

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

2. Sorting query (order by field)

6. Index principles

1. Field independence Principle

select * from emp where empno = 1325467;//empno条件独立，使用索引
select * from emp where empno+2 = 1325467;//empno条件不独立，只有独立的条件字段才可以使用索引

2, left principle

Fuzzy query, like & _

　 %: Associate multiple fuzzy contents

　_: Associate a fuzzy content

Example:

select * form 表名 where a like "beijing%";//使用索引
select * from 表名 where a like "beijing_";//使用索引
select * from 表名 where a like "%beijing%”;//不使用索引
select * from 表名 where a like "%beijing";//不使用索引

3, compound index index (a,b)

select * from 表名 where a like "beijing%";//使用索引
select * from 表名 where b like "beijing%;//不使用索引
select * form 表名 where a like "beijing%" and b like "beijng%";//使用索引

4 , or principle

The association conditions around OR must all have indexes before the index can be used.

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

select * from 表名 where a = 1 or b = 1;//使用索引
select * from 表名 where a = 1 or c = 1;//没有使用索引

Summary: The above is the entire content of this article. I hope it can be helpful to everyone’s study. helped.

The above is the detailed content of What is a mysql index? A brief discussion on mysql index. For more information, please follow other related articles on the PHP Chinese website!