数据库的索引

数据库的索引

一、索引的作用

加快数据检索的速度，索引需要额外的存储空间来保存索引项

二、索引的分类

1.按数据结构划分为++hash++索引和B+树索引
2.根据索引字段个数划分，假如我们有一张表，有三个属性，分别是 id，age 和 name 。假如在id上建立索引，那这就是单值索引；如果在 name 和 age 上建立索引，那这就是复合索引。
3.根据是否在主键上进行建立划分，每张表都有且仅有一个主键索引，不是主键索引的称之为辅助索引。
4.根据数据与索引的存储关联性划分，分为聚簇索引和非聚簇索引
5.其他分类，分为唯一索引和全文索引
索引分类详解

三、索引的优缺点

1.优点

• 大大加快了数据的检索速度
• 可以显著减少查询中分组和排序的时间
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
• 将随机I/O变为顺序I/O(B+树索引是有序的，会将相邻的数据都存储在一起)

2.缺点

• 占用额外空间
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
• 新增或删除数据时，需要维护索引表，导致新增、删除、修改数据速度减慢

四、索引适用的场景

• 用在经常需要搜索的列上，可以加快搜索的速度
• 在作为主键的列上，强制该列的唯一性和组织表中数据的排序结构
• 在经常用在连接的列上，主要是一些外键，可以加快连接的速度
• 在经常需要根据范围进行搜索的列上或经常需要排序的列上创建索引，因为索引已经排序，减少时间
• 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度

五、数据库为什么用 B+ 树做索引？如果是内存数据库用什么？

1.用B+树是为了减少I/O次数：

• 不可能把索引全部加载到内存里，只能逐一加载每个索引节点
• B+树的单个节点中包含的值个数越多，那么节点总数就会越少，I/O次数也越少
• B+树高度一般为2-4层，查找记录时最多只需要2-4次I/O
• 相反，二叉搜索树的高度高，所以需要的I/O次数更多

2.如果是内存数据库，不涉及磁盘I/O，可直接用二叉搜索树

六、联合索引与最左匹配原则

1.如果频繁地使用相同几个字段查询，就可以考虑建立这几个字段的联合索引来提高查询效率。例如对于联合索引test_col1_col2_col3，实际建立了(col1)、(col1,col2)、(col1,col2,col3)三个索引
2.最左匹配原则是指这些索引能被包含(col1)、(col1,col2)、(col1,col2,col3)的查询利用到，但是不能够被(col2)、(col2、col3)的等值查询利用到。减少了创建索引的开销。
3.联合索引查询相当于把多个索引视为一个列。叶节点中的每个行记录，也是按照这些索引列组合后的字典序进行比较的。字典序本身就符合最左匹配原则。
4.联合索引底层还是B+树。
5.联合索引的主要优势是减少结果集数量
6.联合索引属于：

• 覆盖索引：假设有表tab，有字段a、b、c均为int类型，a经常作为查询条件，可以作为索引列，而b和c则是经常作为查询内容出现，也即经常出现以下语句：
  select b,c from tab where a = …；那么我们就可以建立联合索引index(a,b,c)，这样索引树的叶子节点就会同时具有a、b、c三个字段的数据了，查询时就不需要频繁回表了，大大提高查询效率。

七、数据库查询offset的流程? 如果offset非常大会有什么问题？

1.offset的用法SELECT * FROM trom_data LIMIT 3 OFFSET 2limit所要取得记录数目 offset为参考点，表示从第2行（包括该行）取3行
2.查询offset的过程：

• 先通过辅助索引找到所有主键值
• 再逐条回表查询，根据主键值找到数据块的内容，重复offset+limit次
• 丢弃掉前面的offset条数据，只保留最后的limit条
  3.如果offset太大，会导致频繁通过主键索引访问I/O，造成性能下降
  4.优化：先查出偏移后的主键，再根据主键回表查询

# 性能低下的语句：
 select * from member where gender=1 limit 300000,1;
 # 可以优化为：
 select a.* from member as a inner 
   join (
     select id from member where gender=1 limit 300000,1
   ) as b 
   on a.id=b.id