索引 定义及分类

索引
1、索引定义
用于快速找出某个列中拥有的特定值的行。
没索引，查整表（从第一条记录开始查找）
有索引，查索引文件

2、为什么要创建索引 
提高查询效率，经常、习惯、天天都要查这个字段时，为这个字段创建索引。

3、索引存储分类
BTREE/HASH

4、索引优缺点
优点：为查询提速
缺点：越来越大，消耗空间，要维护

5、索引的使用原则
避免过多的使用，经常查询的表的字段和不同值多的字段

索引类型
MyISAM/InnoDB存储引擎，BTREE
InnoDB存储引擎：带有事务。
MyISAM存储引擎：没有事务。

MEMORY/HEAP存储引擎，BTREE/HASH
单列索引、组合索引、全文索引、空间索引
1、单列索引
1、普通索引
为了查询速度快，字段的值可以重复，可以为null
格式：
create table 表名(
  字段名1 字段类型1,
  字段名2 字段类型2,
  ...
  字段名n 字段类型n,
  index/key 索引名(字段名(长度))
);

explain SQL查询
2、唯一索引
为了查询速度快，字段的值是唯一，可以为null

格式：
create table 表名(
  字段名1 字段类型1,
  字段名2 字段类型2,
  ...
  字段名n 字段类型n,
  unique index 索引名(字段名(长度))
);

3、主键索引
是一个特殊的唯一索引，不允许为null
格式：
create table 表名(
  字段名1 字段类型1,
  字段名2 字段类型2,
  ...
  字段名n 字段类型n,
  primary key 索引名(字段名(长度))
);

索引
普通索引：索引字段可重复，可为空
唯一索引：不可重复，可为空
主键索引：不可重复，不可为空

组合索引
多个字段索引，只有左边字段被使用时，索引才会被使用。

格式：
create table 表名(
  字段名1 字段类型1,
  字段名2 字段类型2,
  ...
  字段名n 字段类型n,
  index 索引名(字段名1(长度),字段名2(长度),...字段名n(长度))
);

sid,sname,age   只有查询sid,sname,age/ sid,sname/sid,age/sid才会触发组合索引。

第一步：创建组合索引，名字为index_sid_sname_age
create table stu1(
  sid int,
  sname varchar(20),
  age int,
  sex varchar(2),
  index index_sid_sname_age(sid,sname,age)
);

第二步：什么情况下才会使用组合索引
保证最左前缀，保证查询里面有sid这个字段。

explain select * from stu1 where age=1 and sname='job';  #不能调用组合索引
explain select * from stu1 where sid=1 and sname='job';  #能调用组合索引

2、全文索引
引擎：MyISAM 字段类型：char varchar text 使用函数：match()

格式：
create table 表名(
  字段名1 字段类型1,
  字段名2 字段类型2,
  ...
  字段名n 字段类型n,
  fulltext index 索引名(字段名(长度))
)ENGINE=MyISAM;

3、空间索引
用空间数据类型（点、线、面、几何类型）来建立空间索引。
引擎：MyISAM  列不能为空

格式：
create table 表名(
  字段名1 字段类型1,
  字段名2 字段类型2,
  ...
  字段名n 字段类型n,
  spatial index 索引名(字段名(长度))
)ENGINE = MyISAM;

4、索引操作
创建索引：创建好表后增加索引。

格式：
alter table 数据表名 add [unique|fulltext|spatial] [index|key] [索引名][字段名][asc|desc]

格式：
show index from 数据库表名;

格式一：
alter table 数据库表名 drop index 索引名;

格式二：
drop index 索引名 on 数据库表名;