MySQL的索引类型及简单优化

摘要  MySQL在应用层面上的四大索引：普通索引 index， 唯一索引 unique index， 主键索引 primary key， 组合索引 的基本理解，添加 删除

index  unique  index  primary  key  索引  mysql

• 

普通索引：index，单字段/多字段，可存在多个，列值or组合列值无限制

唯一索引：unique index，单字段/多字段，可存在多个，列值or组合列值不可重复，但可以为NULL的重复，因为NULL值的记录不会被添加到索引.....

主键索引：primary key，单字段/多字段，唯一性，列值or组合列值不可重复

组合索引：当普通/唯一/主键为多字段时，即为追寻左前缀匹配的组合索引，其他限制根据各自特性自行理解

InnoDB要5.6以上才可支持

全文索引：fulltext index，单字段/多字段，可存在多个，列值or组合列值无限制，但列属性必须为char，varchar，text类型，查询使用 where match(column_name) against('keywords')

普通索引

添加索引

?

1 2 3

CREATE  INDEX  index_name  ON  table_name(`column_name`); //可限定索引列长：`username`  varchar (25)可设定索引长度为index_name(`username`(10)); CREATE  INDEX  index_name  ON  table_name(`column_name`(10));

添加索引

?

1 2 3

ALTER  TABLE  table_name  ADD  INDEX  index_name(`column_name`); //可限定索引列长：`username`  varchar (25)可设定索引长度为index_name(`username`(10)); ALTER  TABLE  table_name  ADD  INDEX  index_name(`column_name`(10));

删除索引

?

1 2	DROP  INDEX  index_name  ON  table_name; ALTER  TABLE  table_name  DROP  INDEX  index_name;

唯一索引 

作为此索引的列不允许有重复的列值

注意：单字段则字段值不可重复，空字符串也不可以，但可以为NULL

          多字段组合模式则组合的值不可重复，但可以为NULL的组合

?

1 2 3

CREATE  UNIQUE  INDEX  index_name  ON  table_name(`column_name`); //可限定索引列长：`username`  varchar (25)可设定索引长度为index_name(`username`(10)); CREATE  UNIQUE  INDEX  index_name  ON  table_name(`column_name`(10));

?

1 2 3

ALTER  UNIQUE  TABLE  table_name  ADD  INDEX  index_name(`column_name`); //可限定索引列长：`username`  varchar (25)可设定索引长度为index_name(`username`(10)); ALTER  UNIQUE  TABLE  table_name  ADD  INDEX  index_name(`column_name`(10));

删除索引

?

1 2	DROP  INDEX  index_name  ON  table_name; ALTER  TABLE  table_name  DROP  INDEX  index_name;

主键索引

主键索引具有唯一性（注意，此唯一性是说整个表的primary key只能存在一个，但primary key 可以为多字段型），其他的普通索引 唯一索引 组合索引可以存在多个相同类型的索引

注意：单列值或组合列值不可重复，且不可以为NULL

?

1 2 3

ALTER  TABLE  table_name  ADD  PRIMARY  KEY ( 'column_name' ); //多字段主键索引 ALTER  TABLE  table_name  ADD  PRIMARY  KEY ( 'column_name_1' ,  'column_name_2' );

删除主键索引时要确保此键不为自增类型(auto_increment)

?

1	ALTER  TABLE  table_name  DROP  PRIMARY  KEY ;

组合索引 

组合索引为多个字段共同建立，追寻作左前缀模式:

index_name(name, sex, age)

可以使用

where name = 'name' 

where name = 'name' and sex = 'f' 

where name = 'name' and sex = 'f' and age >= 25

不可以使用

where sex = 'f'

where age >= 25

where sex = 'f' and age >= 25

最左前缀模式 like操作仅在 like 'xxxx%'模式下有效，'_xxx%', '%xxxx%'无效

?

1 2 3

CREATE  INDEX  index_name  ON  table_name(`column_name_1`, `column_name_2`, `column_name_3`); //可限定索引列长：`username`  varchar (25)可设定索引长度为index_name(`username`(10)); CREATE  INDEX  index_name  ON  table_name(`column_name_1`(10), `column_name_2`, `column_name_3`);

?

1 2 3

ALTER  TABLE  table_name  ADD  INDEX  index_name(`column_name_1`, `column_name_2`, `column_name_3); //可限定索引列长：`username`  varchar (25)可设定索引长度为index_name(`username`(10)); ALTER  TABLE  table_name  ADD  INDEX  index_name(`column_name_1`(10), `column_name_2`, `column_name_3);

?

1 2	DROP  INDEX  index_name  ON  table_name; ALTER  TABLE  table_name  DROP  INDEX  index_name;

全文索引 

添加索引

?

1	CREATE  FULLTEXT  INDEX  index_name  ON  table_name(`column_name`);

添加索引

?

1	ALTER  FULLTEXT  TABLE  table_name  ADD  INDEX  index_name(`column_name`);

删除索引

?

1 2	DROP  INDEX  index_name  ON  table_name; ALTER  TABLE  table_name  DROP  INDEX  index_name;

索引优化

1、索引支持 < <= = => > between in 不支持 not in <>

2、索引不会包含有NULL值的列

3、使用like操作时只有在第一位不为通配符时索引才会启动

4、使用短索引，varchar(50)则视情况而定取前几位即可

5、不要在列值上计算，where year(addtime) < 2015 优化为 where addtime < 2015-01-01