1. 什么是索引
索引是一个单独存储在磁盘上的数据库结构,它们包含着对数据表里所有记录的引用指针
在数据库中,索引用来加速对表的查询,索引技术通过使用快速路径访问的方法快速定位数据,减少了磁盘的I/O
可以把索引比作书的目录,每一张表都要有索引,就像每一本书都应该有目录一样。有了索引,数据库的检索速度可以大大提高
2. 索引的特点
索引是作用于表查询的一项技术,因此创建索引时,需要确保该索引是应用在SQL查询语句的条件,一般作为where子句的条件。
在表现形式上,索引表现为一张表,该表保存了主键与索引字段,并指向实体表的记录。索引独立于数据表存放,但不能独立存在,必须属于某个表。索引由数据库自动维护,数据表被删除时,该表上的索引自动被删除。
不过需要注意的是,虽然索引极大地提高了查询速度,同时却会降低更新表的速度,如对表进行insert、update和delete的速度会被降低。 因为更新表时,数据库不仅需要更新表数据,还要更新索
引文件,此外由于索引是独立于表存在的“表”,因此建立索引会占用一定的磁盘空间。
3. 索引的分类
普通索引
最基本的索引,它没有任何限制,用于加速查询
唯一索引(unique)
索引列的值必须唯一,但允许有空值。如果是组合索列值的组合必须唯一
主键索引(primary key)
是一种特殊的唯 一索引,一个表只能有一个主键,不允许有空值,主键索引一般是在表建立的同时自动创建
单列索引
指单个字段上创建索引
组合索引
指条个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合的规则
全文索引(fulltext)
该索引不直接与索引中的值相比较,而是查找文本中的关键字
空间索引(spatial)
MySQL 5.7.4实验室版本中,InnoDB存储引擎新增了对于几何数据空间索引的支持
4. 索引的设计原则
设计索引应遵循如下原则:
只建立适量的索引。 索引并非越多越好,过多的索引会降低表的修改速度以及占用额外的存储空间。
只在需要大量查询的表中建立大量索引。 应该避免对经常更新的表进行过多的索引,并且索引中的列要尽可能少。
不在数据量小的表建立索引。 数据量小的表没有必要使用索引
不在不同值少的列上建立索引。 如果表的某一列中大部分数据都是重复的,则没有必要建立索引
指定唯一索引。 当表的某一列数据被指定为unique是,可以建立唯一索引
在频繁操作的列上建立索引。 若想要建立索引,则应选择频繁排序(order by)或分组(group by)的列建立索引
5. 创建索引
5.1. 自动创建索引
当在表上定义primary key或者unique约束条件时,数据库会自动创建一个对应的索引,也就是创建主键索引和唯一索引。
5.2. 手动创建索引
5.2.1. 创建表时创建索引
在建表的同时创建索引的语法为:
create table [表名]( [属性名1] [数据类型1] [约束1], ... [属性名n] [数据类型n] [约束n], [unique | fulltext | spatial] index|key [索引名]( [属性名1] [(长度)] [asc | desc], ... [属性名n] [(长度)] [asc | desc] ) );
例如
建立普通索引
create table normal_table( id int(20) primary key, username varchar(20), email varchar(20), -- 在id列建立普通索引 index normal_index(id) );
建立唯一索引
create table unique_table( id int(20) primary key, username varchar(20), email varchar(20), -- 在email列建立唯一索引,并升序排列 unique index uniquel_index(email asc) );
建立全文索引
create table fulltext_table( id int(20) primary key, username varchar(20), email varchar(20), -- 在username列建立全文索引 fulltext index fulltext_index(username) );
建立单列索引
create table single_table( id int(20) primary key, username varchar(20), email varchar(20), -- 在username列建立索引,索引长度为9 index single_index(username(9)) );
建立多列索引
id int(20) primary key, username varchar(20), email varchar(20), -- 在username列建立索引,索引长度为9 index single_index(username(9)) );
5.2.2. 在已经存在的表上创建索引
5.2.2.1. alter语句创建索引
语法:
alter table [表名] add [unique|fulltext|spatial] [index|key] [索引名]( [属性名1] [(长度)] [asc | desc], ... [属性名n] [(长度)] [asc | desc] );
例如:
建立普通索引
-- 在normal_table表的id列建立名为normal_index的普通索引 alter table normal_table add index normal_index(id);
建立唯一索引
-- 在unique_table表的username列建立名为unique_index的唯一索引 alter table unique_table add unique index unique_index(username);
建立多列索引
-- 在multi_table表的username和email列建立名为multi_index的多列索引 alter table multi_table add index multi_index(username, email);
其他类型的索引同理
5.2.2.2. create语句创建索引
语法:
create unique [unique|fulltext|spatial] index [索引名] ON [表名]( [属性名1] [(长度)] [asc | desc], ... [属性名n] [(长度)] [asc | desc] );
例如
建立普通索引
-- 在normal_table表的id列建立名为normal_index的普通索引 create index normal_index on normal_table(id);
建立唯一索引
-- 在unique_table表的username列建立名为unique_index的唯一索引 create unique index unique_index on unique_table(username);
建立多列索引
-- 在multi_table表的username和email列建立名为multi_index的多列索引 create index multi_table on multi_table(username, email);
其他类型的索引同理
6. 查看索引
6.1. explain关键字查看索引
explain关键字用于查看和分析SQL索引使用情况
语法:
explain select * from [表名];
使用里explain语句后,会返回一张表:
例如:
-- 查看multi_table表中的所有使用情况 explain select * from multi_table;
结果为:
6.2. show index关键字查看索引
show index关键字用于查看某一表中的所有索引
语法:
show index from [表名];
使用里show index语句后,会返回一张表:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
其中对表中字段的说明可以参考这篇博客:MySQL SHOW INDEX 的语法解析
例如:
-- 查看multi_table表中的全部索引 show index from multi_table;
结果为:
7. 删除索引
7.1. alter关键字删除索引
语法:
alter table [表名] drop index [索引名];
例如
-- 从multi_table表中删除multi_index索引 alter table multi_table drop index multi_index;
7.2. drop关键字删除索引
语法:
drop index [索引名] on [表名];
例如
-- 从multi_table表中删除multi_index索引 drop index multi_index on multi_table;