数据库中---索引

索引

Mysql数据库中数据的基本存储结构的分页存储（离散存储）。
数据页：
数据页就是分页存储中的物理地址，通过双向列表将其连接。
每一个数据页可以存放若干个记录；这些若干个记录有可以组成一个单向链表；数据页为存放在页内的记录生成一个页目录。
页表：
存储数据（一块连续的）逻辑地址 ；这个页表就是一个定位数据页的“数据页的目录“；又称为索引。
这有里两个目录：页表是定位数据页的一级目录；数据页中的页目录是定位记录位置的二级目录。

• 在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
• 当以其他列（非主键）作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。
• 例子：select * from user where username=‘王三’ 这样没有任何优化的sql语句，默认的查询顺序：
  1. 先定位记录所在的数据页：通过遍历双向链表，找到。
  2. 从数据页中定位要查找的记录：由于没有根据主键查询，只能从该数据页的第一条记录记录开始遍历单链表。
     这样，显然在数据量大的情况下，会查的很慢。
     

1. 索引为什么提高检索速度

本质：将无序的数据变成相对有序。
没用索引我们需要遍历双向链表来定位数据页，则通过“目录”就可以快速定位到数据页。索引的底层实现采用的是B+树实现；建立索引，实际就是在建立一棵B+树。

2.索引会降低增删改的速度

• B+树是一颗平衡树，如果我们对这颗树增删改的话，那肯定会破坏它的原有结构。
• 要维持平衡树，就必须做额外的工作。正因为这些额外的工作开销，导致索引会降低增删改的速度

3.哈希索引（时索引的一种底层实现方法）

索引的底层实现除了B+树还有一种是通过哈希算法实现。

• 本质：是将key值通过哈希函数变成对应的哈希值，根据哈希值进行定位。
  检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

哈希索引的局限

• 哈希索引没办法利用索引完成排序。
• 不支持范围查找。
  • 因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索和排序；
• 不支持最左匹配原则。
• 在有大量重复key值时，会出现哈希碰撞，从而降低哈希索引的效率。

哈希索引使用场景：
如果存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引。
eg:这种SQL
SELECT … FROM t WHERE C1 = ?; — 仅等值查询

Mysql中的InnoDB存储引擎对哈希索引的应用：自适应哈希索引，（哈希索引由InnoDB自动优化创建，我们干预不了）。

4.索引的分类（聚簇索引和非聚簇索引）

简单概括：

• 聚簇索引：以主键创建的索引（只适合搜索条件是主键值时才有作用）
  • 1.使用主键值的大小进行记录和页的排序。
    • 页内记录按照主键大小顺序排成一个单链表
    • 各个存放记录的页之间也是根据主键大小顺序排成一个双向链表
    • 当存在多级分页（多级索引）；存放目录页的目录也是根据主键大小顺序排成一个双向链表。
  • 2.B+树叶子节点存储的都是完整的用户数据。
  • 3.一张表中只能有一个聚簇索引（聚簇索引的唯一性：数据一旦存储，物理地址顺序就确定了；聚簇索引的顺序和数据的物理存放顺序一致）。但是该局促索引可以有多个列；eg:汉语字典就是一个典型的聚簇索引的实现，汉字的拼音和声调作为索引。
• 非聚簇索引：以非主键创建的索引（又称二级索引）
  - eg:
  学生表Student（sno,sname,ssex,sage,sdept）
  课程表course (cno,cname,cpno,credit)
  选课表sc(sno,cno,grade)
  选课表中的sno,和cno就可以作为非聚簇索引。
  聚簇索引和非聚簇索引的区别：
• 聚簇索引在叶子结点存储的是表中的数据。
• 非聚簇索引在叶子结点存储的是主键和索引列。
• 使用非聚簇索引查数据，拿到叶子结点主键再去查想要查的信息（该过程叫回表。

5.索引的最左匹配原则

• 在联合索引中，key可以有多个列组成，索引匹配只能用于相等情况，如果出现范围匹配（>,<,between,like），则不会往后匹配索引。
• 因此列的顺序也会决定索引的命中列数。
  eg: 有一个索引（a,b,c,d）,查询条件为 a=1 and b= 2 and c ❤️ and d=4,则命中的索引为a,b,c；无法命中d.

6. =,in,<等会自动优化顺序

不需要考虑这些运算符的顺序，Mysql会自动优化这些条件的顺序，以匹配尽可能多的索列。
eg: 如有索引(a, b, c, d)，查询条件c > 3 and b = 2 and a = 1 and d < 4与a = 1 and c > 3 and b = 2 and d < 4等顺序都是可以的，MySQL会自动优化为a = 1 and b = 2 and c > 3 and d < 4，依次命中a、b、c。

7.避免冗余索引

尽可能的扩展已有的索引，而减少创建新的索引；因为创建索引会增加系统损耗。eg:索引 name city 和索引name就是形同的目的，属于冗余索引。

8.Mysql添加索引的语法

//添加主键
alter table名字  add primary key 列名  
//添加普通索引
alter table名字  add index 列名