mysql索引学习总结

索引

索引主要是针对表中的列，增加索引的目的是加快查询速度，索引可以对单列或多列进行添加

1 普通索引

1.1 在创建表时添加索引

普通索引，在创建该类索引时，不给索引添加任何限制条件（唯一、非空等），可以在任意列上创建

格式：

create table tableName(
propname1 type1, propname2 type2,
INDEX|KEY indexName (propname1(length) ASC|DESC));

其中 INDEX|KEY 表示可以选择其中一个，索引一般使用index，indexName可以省略不写，length也可以省略不写；

ASC|DESC 表示可以选择一个，ASC表示对索引字段查询时会升序显示，DESC表示对索引字段查询时会降序显示，ASC|DESC也可以省略不写

【例】一张学生表，有id，姓名，班级，老师栏位，对姓名栏位增加索引

create table student(id int, name varchar(4),
class_name varchar(5), teacher varchar(4),
INDEX index_name(name);

1.2 在已有的表上添加索引

格式：

ALTER table tableName ADD INDEX|KEY indexName(porpname(length) ASC|DESC);

其中 INDEX|KEY 表示可以选择其中一个，索引一般使用index，indexName可以省略不写，length也可以省略不写；

ASC|DESC 表示可以选择一个，ASC表示对索引字段查询时会升序显示，DESC表示对索引字段查询时会降序显示，ASC|DESC也可以省略不写

2 唯一索引

对某一列进行设置唯一索引，该列需要为 UNIQUE 唯一属性

2.1 在创建表时添加索引

格式：

create table tableName(
propname1 type1, propname2 type2,
UNIQUE INDEX|KEY indexName (propname1(length) ASC|DESC));

与普通索引相比，只需要在INDEX前加上UNIQUE

2.2 在已有的表上添加索引

方式一格式：

ALTER table tableName ADD 
UNIQUE INDEX|KEY indexName(porpname(length) ASC|DESC);

方式二格式：

create UNIQUE INDEX|KEY indexName on tableName(
propname(length) ASC|DESC);

3 全文索引

全文索引主要是对字符类型（varchar/char/text）的字段进行建立基于分词的索引，查询条件中可以对添加索引的字段类似于模糊查询

3.1 在创建表时添加索引

创建格式：

create table tableName(
propname1 type1, propname2 type2,
FULLTEXT INDEX|KEY indexName (propname1(length));

与普通索引相比，只需要在INDEX前加上FULLTEXT

3.2 在已有的表上添加索引

创建格式：
方式1：

ALTER table tableName ADD 
FULLTEXT INDEX|KEY indexName(porpname(length));

方式二格式：

create FULLTEXT INDEX|KEY indexName on tableName(
propname(length));

3.3 使用索引

格式：

select * from tableName where MATCH(propname)AGAINST('关键字');

4 多列索引

多列索引就是对多列进行添加索引，只有当查询条件中使用了第一个索引字段时，多列索引才会被触发

4.1 在创建表时添加索引

创建格式：

create table tableName(
propname1 type1, propname2 type2,propname3, type3,
INDEX|KEY indexName (
propname1(length)ASC|DESC, 
propname2(length)ASC|DESC, 
propname3(length)ASC|DESC);

查询时，使用：select * from tableName where propname>0;
其中使用了第一个索引字段作为查询条件，多列索引会被触发

4.2 在已有的表上添加索引

创建格式：
方式1：

ALTER table tableName ADD 
INDEX|KEY indexName(
propname1(length)ASC|DESC, 
propname2(length)ASC|DESC, 
propname3(length)ASC|DESC);

方式二格式：

create INDEX|KEY indexName on tableName(
propname1(length)ASC|DESC, 
propname2(length)ASC|DESC, 
propname3(length)ASC|DESC);

5 查看索引执行情况

使用explain 查询语句
其中查询语句的条件中包含索引字段，会显示索引的执行情况

其中，
key：为使用的索引，若显示为null则表示没有索引
possible_keys：显示应用在表中的索引
key_len：为索引中使用的字节数

6 隐藏索引

可以对索引进行隐藏，从而不会触发索引，可以用来测试索引
格式：

ALTER table tableName INDEX indexName INVISIBLE; //隐藏索引
ALTER table tableName INDEX indexName VISIBLE; //取消隐藏

7 删除索引

索引有可能会降低表的更新速度，可以对索引进行删除
格式：

DROP INDEX indexName ON tableName;

如果需要对索引进行更新，可以先进行删除，再进行创建