MySQL数据库----索引

1. 说明

MySQL可以为字段创建索引，InnoDB使用BTREE索引，提高查询效率。维护索引需要消耗资源，经常变动的字段，索引会造成时间上的浪费，降低插入效率。按限制条件，索引可以分为普通索引、唯一索引、全文索引、单列索引、多列索引。

2. 普通索引

创建索引时不加其他限制条件就是普通索引，普通索引可以创建在任何字段上。

#创建普通索引
create table 表名(列名 列定义, index 索引名(列名 ASC/DESC));

#用例
create table world.city(id int, name varchar(32), index id_index(id desc));

• ASC/DESC为可选项，定义索引升序排列或降序排列

3. 唯一索引

设置唯一索引的字段要求值具有唯一性，同时设置unique约束的字段也会自动设置唯一索引

#创建唯一索引
create table 表名(列名 列定义, unique index 索引名(列名 asc/desc));

create table world.city(id int, name varchar(32), unique index id_index(id desc));

4. 全文索引

对字符串类型可以设置全文索引， MySQL会对字符串做分词索引，可以快速查询数据量较大的字符串类型。

#全文索引
create table 表名(列名 列定义, fulltext index 索引名(列名 asc/desc));

#用例
create table world.city(id int, name varchar(32), fulltext index name_index(name desc));

• 全文索引建立在分词的基础上，比如英文以空格作为分词符，中文，作为分词符，并不是以每个字符分词

5. 多列索引

创建索引时也可以关联多个字段，称为多列索引

#多列索引
create table 表名(列名1 列定义1, 列名2 列定义2, index 索引名(列名1, 列名2) );

#用例
create table world.city(id int, name varchar(32), index id_name_index(id, name));

• 查询时必须带上设置多列索引的第一个字段，否则不会使用索引

6. 索引的操作

可以在已创建的表上修改索引

6.1 追加索引

追加索引有两种语法。

#增加索引-方法一
alter table 表名 add index 索引名(列名 asc/desc);

#用例
alter table world.city add index id_index(id desc);

#增加索引-方法二
create index 索引名 on 表名(列名 asc/desc); 

#用例
create index id_index on world.city(id desc);

6.2 删除索引

#删除索引
alter table 表名 drop index 索引名;

#用例
alter table world.city drop index id_index;

#删除所有-方法二
drop index 索引名 on 表名;

#用例
drop index id_index on world.city; 

6.3 隐藏索引

隐藏索引可以使索引暂时失效，而不用直接删除索引

#隐藏索引
alter table 表名 alter index 索引名 invisible;

#取消隐藏
alter table 表名 alter index 索引名 visible;

6.4 查看索引

#查看创建的索引
show create table 表名;

#查看查询时是否使用索引
explain select * from 表名 where 条件;