8、MySQL中的索引

1.什么是索引

用来提高数据库查询速度的东西。比如图书馆的检索系统。

2.索引的优缺点

• 优点：提高数据库的性能，查询速度提高（查询海量数据时，没有索引的话查询结果会等很久才显示）
• 缺点：查询速度的提高是以插入、更新、删除的速度为代价的。

3.索引的常见种类

• 主键索引（primary key）
• 唯一索引（unique key）
• 普通索引（index）
• 全文索引（fulltext）

4.创建索引

4.1创建主键索引

--方法1
--在创建表的时候，直接在指定列名后加primary key
create table 表名(filed1 datatype primary key,filed2 datatype);

--方法2
--创建表时，最后指定某列或者某几列为主键索引
create table 表名(filed1 datatype,filed2 datatype,primary key(filed1));

--方法3
--创建完表之后添加主键
alter table 表名 add primary key(filed1);

主键索引的特点：

• 一张表最多有一个主键索引
• 主键上的值不能为空，不能重复
• 主键索引列上的值类型几乎都是int

4.2创建唯一索引

--方法一
--在创建表时，直接指定unique属性
create table 表名(filed1 datatype unique,filed2 datatype);

--方法二
--创建表时，最后指定某列或者某几列为unique
create table 表名(filed1 datdatype,filed2 datatype ,unique(filed1));

--方法三
--表创建完成，追加唯一键
alter table 表名 add unique(filed1);

唯一索引的特点：

• 一张表可以有多个唯一索引
• 唯一索引列上的值可以为空，但不能重复。列约束条件加上NOT NULL等价于主键索引。

4.3创建普通索引

--方法1
--在表的定义最后，指定某列为索引
create table 表名(filed1 datdatype ,filed2 datatype ,index(filed1));

--方法2
--创建完表后添加某列为普通索引
alter table 表名 add index(filed1);

--方法3
--创建完表之后，创建一个索引名为idx_filed1的普通索引
create index idx_filed1 on 表名(filed1);

普通索引的特点

• 一张表可以有多个普通索引
• 若某列需要索引，但是该列有重复的值，就用普通索引

4.4全文索引的创建

--创建表最后用fulltext(字段名)添加全文索引
create table 表名(
    filed1 datatype,
    filed2 datatype,
    fulltext(filed1,filed2)
)engine = MyISAM;

全文索引的特点

• 有大段字段需要进行检索时使用全文索引
• 全文索引表的存储引擎必须是MyISAM
• 默认全文检索不支持中文，要对中文检索需要使用sphinx的中文版

5.查询索引

--方法一
show keys from 表名 \G;

--方法2
show index from 表名;

--方法3
desc 表名;

6.删除索引

--方法1 删除主键索引
alter table 表名 drop primary key;

--方法2 删除其他索引
alter table 表名 drop index 索引名;

--方法3
drop index name on 表名;

7.索引创建的原则

• 比较频繁作为查询条件的字段应该创建索引
• 唯一性太差的字段不适合创建索引，即使查询频繁
• 更新频繁的字段不适合创建索引
• 不会出现在where子句中的字段不该创建索引