Mysql资料索引--索引管理

索引管理

MySQL的索引分类

索引分类

1.普通索引index :加速查找
2.唯一索引
主键索引：primary key ：加速查找约束（不为空且唯一）
唯一索引：unique：加速查找约束 （唯一）
3.联合索引
-primary key(id,name):联合主键索引
-unique(id,name):联合唯一索引
-index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用

各索引应用场景

举个例子来说，比如你在为某商场做一个会员卡的系统。

这个系统有一个会员表有下列字段：会员编号 INT 会员姓名 VARCHAR(10) 会员身份证号码 VARCHAR(18) 会员电话 VARCHAR(10) 会员住址 VARCHAR(50) 会员备注信息 TEXT

那么这个会员编号，作为主键，使用 PRIMARY 会员姓名如果要建索引的话，那么就是普通的 INDEX 会员身份证号码如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

除此之外还有全文索引，即FULLTEXT 会员备注信息，如果需要建索引的话，可以选择全文搜索。用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。

其他的如空间索引SPATIAL，了解即可，几乎不用

索引类型

我们可以在创建上述索引的时候，为其指定索引类型，分两类

hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b 树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

不同的存储引擎支持的索引类型也不一样

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

操作索引

创建索引在创建表时就创建（需要注意的几点） create table s1( id int , #可以在这加primary key #id int index #不可以这样加索引，因为index只是索引，没有约束一说，不能像主键，还有唯一约束一样，在定义字段的时候加索引 name char(20), age int, email varchar(30), #primary key(id) #也可以在这加 index(id), #可以这样加 );

在创建表后再创建 create index name on s1(name); #添加普通索引 create unique index age on s1(age); 添加唯一索引 alter table s1 add primary key(id); #添加主键索引，也就是给id字段增加一个主键约束 create index name on s1(id,name); #添加普通联合索引

查看索引 mysql> show index from stu; 查看sut表的索引删除索引 drop index name on s1; #删除普通索引 drop index age on s1; #删除唯一索引，就和普通索引一样，不用在index前加unique来删，直接就可以删了 alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)

对student字段前8个字符建立索引，而不是对整个 create index index_name on student(name(8));

提示:按条件列查询数据时，联合索引是有前缀生效特性的index(a,b,c)仅 a,ab,abc 三个查询条件列可以走索引， b,bc,ac,c 等无法使用索引了尽量把最常用作为查询条件的列，放在第一位置

联合索引，并前10个字符 create index index_name_and_dept on student(name(10),dept(10));