数据库索引

零、数据库索引介绍

稠密索引与稀疏索引

稠密索引：对于主文件中的每一个记录，都有一个索引项和他对应
稀疏索引：只有部分记录有索引项与之对应

稀疏索引定位记录

使用要求： 主文件必须按照对应索引字段值进行排序
查找时首先找离target最近的最大的字段值，然后顺序查找表

稠密索引定位记录

直接依据索引读取文件即可

索引字段有重复？

1. 建立多个索引项
2. 引入指针桶

主索引与辅助索引

主索引：

通常是对每一存储块有一个索引项，索引项的总数和存储表所占的存储块数目相同，存储表的每一存储块的第一条记录，又称为锚记录(anchor record), 或简称为块锚(block anchor)
主索引是根据索引字段值排序的有序文件，通常建立在有序文件的基于主码的排序字段上，是稀疏索引

辅助索引：

是定义在主文件的任一或多个非排序字段上的辅助存储结构
是稠密索引

聚簇索引与非聚簇索引

聚簇索引

索引中邻近的记录，在主文件中也是临近存储的
聚簇索引是可以决定记录存储位置的索引
主索引通常是聚簇索引

非聚簇索引

索引中邻近的记录，在主文件中不是邻近存储的
非聚簇索引不能决定记录存储位置，仅仅是为了查询
辅助索引通常是非聚簇索引

一、按照底层数据结构划分

（一）B树索引/B+树索引

这两种树形结构是目前最常用的。B树索引更加古老，现在更多使用B+树。

主要区别是

B树指向数据的指针既可以在叶子节点，也可以在非叶子节点，而B+树必须在叶子结点，且B+树的叶子节点中有指向下一个节点的指针
因此，同等条件下B+树的索引范围是比B树更大的。除此之外通过叶子结点之间的指针，B+树还增加了对范围查找的支持。这两者是他们最主要的区别。
此外在存储方面，B+树由于内部节点只指向节点，因此占空间更小，IO读写代价更低。由于B+树的所有指向文件的指针都在叶子节点，因此所有查找的路径长度都相当，导致每一个数据的查找效率相当。

使用方法

可以进行全键值、键值范围和键值前缀查询，也可以对结果进行order by排序。

左边前缀原则

1. 查询必须从索引左边的列开始
2. 查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配
3. 存储引擎不能使用索引中范围右边的列

举例：
假如person表上有一个组合索引（name， age， nationality）
那么这些是可以用索引的：
select * from person where name = "
select * from person where name = "and age ="
select * from person where name = "and age =" and nationality = "
这些则是不可以用索引的：
select * from person where age ="
select * from person where name = "and nationality ="

覆盖索引：
当使用覆盖索引的方式，select id,v_name,age from sys_user where age=10（where后面没有其他没有索引的字段条件），即使不是以v_name开头，也会使用联合索引，总结来说就是
select 后的字段有索引，where字段也有索引，则无关执行顺序，不需要最左边前缀法则

（二）Hash索引

使用哈希表的方式进行索引的建立,检索效率可能比树高很多

1. 散列计算是一个耗时很长的操作，相比树索引会更费时间
2. 不能使用hash索引排序
3. 只支持等值比较
4. 不支持键的部分匹配

静态散列索引的桶的数目不变，可能产生很大的溢出桶，降低查找效率

动态散列索引

桶的数目随键值增多，有两种方案：可扩展散列索引和线性散列索引

二、按照逻辑划分

（一）普通索引

是mysql中最基本的索引类型，唯一任务是加快系统对数据的访问速度
允许在定义索引的列中插入重复值和空值

定义

在table上的id字段上建立名为index_id的索引
create index index_id on table(id)

（二）唯一索引

唯一索引与普通索引类似，但目的不是为了提高访问速度，而是为了避免数据出现重复
唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列植的组合必须唯一

定义

create unique index index_id on table(id)

(三) 主键索引

专门为主键建立的索引
用primary key进行创建，不能使用create index语句创建

三、实际使用区分

（一）单列索引

质保函一个列的索引

（二）多列索引

也称为复合索引或组合索引。是将原表的多个列共同组合成一个索引。查询时需要满足左边前缀原则。

四、索引使用优化

建立索引的原则：

1. 较频繁的作为查询条件的字段应该创建索引
2. 唯一性太差的字段不应该创建索引
3. 增删改操作太多的字段不应该建立索引

组合索引

1. 有大量重复值、且经常有范围查询（between, >,< ，>=,< =）和order by、group by发生的列，可考虑建立群集索引；
2. 经常同时存取多列，且每列都含有重复值可考虑建立组合索引；
3. 组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。

使用原则：

1. 全值匹配，最优
2. 最佳左前缀（如果建立了联合索引，指的是从索引的最左前列开始并且不跳过索引中的列）
3. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
4. 索引不能使用索引中范围条件右边的列
   比如，table 上对age 和 pos 有联合索引
   则select * from table where age = 10 and pos = 'shanghai' 使用索引
   而select * from table where age > 10 and pos = 'shanghai' 不使用
5. 尽量使用覆盖索引，提高查询效率，避免使用select *
6. like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
7. 使用or导致索引字段失效