表索引
索引是通过多路所搜树的方式存储数据的,每一个磁盘块代表一次IO操作读取的磁盘内容,可以看出采用树的形式查找数据比线性查找数据执行更少的IO操作。
创建索引的原则:
1.索引要创建在数据区分度高的字段,这样查找提升效率大。
2.加索引的字段占空间要尽可能小,因为叶子节点基本是固定的,如果在占空间大的字段加索引,索引字段会占用更多次判断,会增加树深度,增加查找IO。
聚集索引
聚集索引即主键(primary key),一张表只能由一个聚集索引,聚集索引是InnoDB储存引擎组织数据储存的依据,如果一张表内没有指定聚集索引,InnoDB会自动创建一个隐藏字段作为primary key,primary key带着unique和not null两种完整性约束。
聚集索引的叶子节点内存放的是整行数据。
-- 创建表时
create table t1(id int primary key);
create table t1(id int, primary key(id));
-- 创建表之后
alter table t1 add primary key(id);
alter table t1 drop primary key;
普通索引
普通索引叶子节点存的是索引字段的内容,还有该条数据对应的聚集索引。通过普通索引找到某条数据后,如果要select的就是该字段的内容,这种索引又被称为覆盖索引。如果要查找的是该条数据的其他字段,会通过回表操作依据聚集索引找到对应数据。
create table t1(id int,index index_name(id));-- 索引名字也可以不加,会以字段名命名索引
alter table t1 add index index_name(id);-- 除了聚集索引,其他索引都有名字
create index index_name on t1(id);
alter table t1 drop index index_name;
drop index index_name on t1;
唯一索引
唯一索引比普通索引加入了不可重复的约束。
create table t1(id int unique);
create table t1(id int, unique key uni_name(id);
alter table t1 add unique key uni_name(id);
alter table t1 drop index uni_name;-- 除了聚集索引,其他索引都有名字
两大索引类型
hash类型索引:查询单条快,范围查询慢
btree类型索引:b+树,层数越多,数据量指数级增长(InnoDB存储引擎默认支持它)