MySQL的索引类型及简单优化

2019独角兽企业重金招聘Python工程师标准>>>

普通索引：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')

普通索引

添加索引

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

添加索引

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

删除索引

DROP INDEX indexName ON tableName;
ALTER TABLE tableName DROP INDEX indexName;

唯一索引 

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

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

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

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

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

删除索引

DROP INDEX indexName ON tableName;
ALTER TABLE tableName DROP INDEX indexName;

主键索引

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

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

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

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

ALTER TABLE tableName DROP PRIMARY KEY;

组合索引 

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

indexName(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%'无效

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

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

DROP INDEX index_name ON tableName;
ALTER TABLE tableName DROP INDEX indexName;

全文索引 

全文索引适用于MyISAM引擎，且只能为char varchar text类型的列添加 查询时使用where match(`column name`) against("keywords" in boolean mode)

添加索引

CREATE FULLTEXT INDEX indexName ON tableName(`columnName`[(length)]);
ALTER TABLE tableName ADD FULLTEXT INDEX indexName(`columnName`[(length)]);

全文索引 like查询无法使用

select `id` from tableName where match(`content`) against("keywords" in boolean mode);

删除索引

DROP INDEX indexName ON tableName;
ALTER TABLE tableName DROP INDEX indexName;

索引优化

1、索引支持 < <= = => > between in 不支持 not in <>【索引是有序的，对于范围类的查询有效，对于非范围的只能遍历整张表，not in <>不到最后一条记录是没办法确认查询是否完成】

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

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

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

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

可以使用explain命令来查看mysql是如何执行sql语句的，看自己的某些索引是否起了作用

转载于:https://my.oschina.net/sallency/blog/464070