索引:index是帮助Mysql高效获取数据的数据结构。默认为B+树。
- 主键索引:不能重复。一个字段设置为primary key默认为主键索引,不可以是null。
- 唯一索引:不能重复。一般id字段设置,可以是null。
- 单值索引:单列。一个表可以有多个单值索引。
- 复合索引:多列构成。(name age…)(相当于二级目录)
示例表【student】结构:
方法一:create 索引类型 索引名 on 表名(字段名)
create index ssex_index on student(ssex);
create unique index sno_index on student(sno);
create index name_age_index on student(name,age);
方法二:alter table 表名 add 索引类型 索引名(字段)
alter table student add index ssex_index(ssex);
alter table student add unique index sno_index(sno);
alter table student add index name_age_index(name,age);
drop index ssex_index from student;
show index from student;
- 索引本身很大。
因为它本身就是数据,需要占用存储空间(内存或硬盘)。 - 索引不是所有情况均适用:
- a.少量数据
- b.频繁更新的字段。因为索引字段的数据更新,索引本身也会更改b树结构。
- c.很少使用的字段。
- 索引提高查询的效率,但降低增删改的效率。
因为数据没有添加索引,数据直接进行增删改;添加了索引的数据每一次增删改,除了表数据变动,索引结构也会跟着变动,需要改两次数据。
- 提高了查询效率,也就是提高了IO的效率。
因为客户端连接到服务端是通过IO输入输出的,索引优化会降低了IO的使用率。 - 降低CPU的使用率。
比如order by age,如果没有索引sql会把所有的年龄数据全部拿出来进行对比排序;有了索引,索引本身是B+树,已经排序完成了,可以直接使用。
- 在where从句,group by 从句,order by 从句和on从句中出现的列。
- 索引的字段越小越好。
- 联合索引离散度大的列放在前面。(此时customer_id放在前面)
- 索引并非越多越好
通常情况下我们认为过多的索引只是影响数据库增删改的效率。并不是,也会影响到查询效率,因为mysql在查询数据的时候会进行分析使用哪一个索引进行查询,如果索引过多,那么分析会变慢,查询效率也会变低。
- 当一个索引包含所有查询的列,我们称之为覆盖索引。
也就是说select 查询的列如果都包含在同一个索引,那边查询效率很高。
比如select 后面只有id(主键索引),或者只查询建立了联合索引的name和age字段。
因为覆盖索引是可以直接通过索引中的数据来进行获取,不需要再次从表中获取数据了。
B+树也有三叉树
两个蓝色块根节点是17和35,如图的红色框就代表是17和35的指针,下面三个黄色块P1代表小于17的数据,P2代表17到35的数据,P3代表大于35的数据。
三层B+树存放百万条数据,且真实数据全部都是存放在叶子节点,上图中根节点和分支节点的蓝色数据只是为了对黄色块分成指针。
B+树中查询任意数据的次数都是相同的。(也就是B+树的高度,一般是3或者4层)