sql---索引总结

索引:是为了提高数据查询的效率

常见模型:

1. 哈希表(以键值对key-value存储数据的结构)
   1. 适应场景:哈希表这种结构适用于只有等值查询的场景
   2. 思路:把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置
   3. 冲突的解决办法:链表
2. 有序数组
   1. 有序数组索引只适用于静态存储引擎
   2. 按顺序存储。查询用二分法就可以快速查询
3. 二叉搜索树
   1. 每个节点的左儿子小于父节点，父节点又小于右儿子
   2. 查询时间复杂度O(log(N))，更新时间复杂度O(log(N))
   3. 数据库存储大多不适用二叉树，因为树高过高，会适用N叉树

InnoDB 的索引模型:B+树(每一个表是好几棵B+树,B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数)

索引分为主键索引和非主键索引

• 主键索引:叶子节点存的是整行数据,在InnoDB里，主键索引也被称为聚簇索引（clustered index）。
• 非主键索引:叶子节点存的是主键索引的值在InnoDB里，非主键索引也被称为二级索引（secondary index）。

基于主键索引和普通索引的查询有什么区别？

　　如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树；

　　如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。(回到主键索引树搜索的过程，我们称为回表)

维护:

一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。

 select * from T where k between 3 and 5 

1. 在k索引树上找到k=3的记录，取得 ID = 300；

2. 再到ID索引树查到ID=300对应的R3；

3. 在k索引树取下一个值k=5，取得ID=500；

4. 再回到ID索引树查到ID=500对应的R4；

5. 在k索引树取下一个值k=6，不满足条件，循环结束。

这个查询过程读了k索引树的3条记录（步骤1、3和5），回表了两次（步骤2和4）

覆盖索引:减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

select ID from T where k between 3 and 5
--只需要查询ID值,而id值已经在k索引树上了,所以不需要回表

 

面试官:你们项目都是用关系型数据库吗?

回答: 是的,我们线上使用的是mysql

• 关系型:指基于“关系模型”的数据库,他的模型有1对多,多对多,多对1(msyql、oracle、sql server)
• 非关系型:指基于“非关系模型”的数据库,他的模型有列模型(Hbase)、键值对模型(redis)、文档类模型(mangoDb)

面试官:那你们都是怎么做优化的?

回答:我们在数据库创建了一些索引

面试官:那你能说说是什么索引吗?

回答:索引是一种数据结构,能够很好的帮助我们检索数据库中的数据

面试官:那索引具体采用的那种数据结构呢?

回答:索引分为hash索引和tree索引,我们用的是innoDb,默认是b+树索引

面试官:b+树和hash索引有什么优缺点,为什么用b+树?

回答:

　　hash索引底层是hash表,hash表是以key—value的形式存储的,所以多个数据在存储关系上没有任何顺序关系的,对于区间插件是无法通过索引查询的,就需要做全表扫描,而b+树是一种多路平衡树,他的节点是天然 有序的,(左节点小雨父节点,父节点小于右节点),所以对于范围查询的时候不需要做全表扫描

　　hash索引适合等值查询,但是无法进行范围查询,哈希索引没办法利用索引排序,哈希索引不支持多列联合索引的最左匹配原则,如果有大量重复键值的情况下,哈希索引的效率会很低,

面试官:b+树的叶子节点可以存储哪些东西?

回答:存储的是整行数据或者主键的值

面试官:有什么区别

回答:存储整行数据的是主键索引,又称聚簇索引,存储主键的值是非聚簇索引

面试官:聚簇索引和非聚簇索引的区别?

回答:聚簇索引比非聚簇索引索引更快,因为主键索引树节点的数据就是我们要查询的数据,而非聚簇索引,的叶子结点是主键的值,需要再次回表,根据主键的值再去查询一次

面试官:所有的非聚簇索引都需要回表吗?

回答:覆盖索引除外(select id from table where a ='1')

面试官:创建索引考虑的因素有哪些?

回答:我们一般对于查询概率比较高的字段,经常座位where条件的字段设置索引

面试官:联合索引多个字段的顺序是怎么选择的

回答:识别度高的字段放前边,因为联合索引遵循最左匹配原则,既最左优先,假如有个联合索引abc,那么他就相当于有a,ab,abc三个索引,这就是最左匹配原则

面试官:有什么手段可以知道有没有走索引查询

回答:可以通过exolain查看sql语句的执行计划,通过执行计划来分析索引使用情况

面试官:那什么情况下会发生明明创建了索引,但是执行的时候并没有通过索引呢

回答:咱们在执行sql语句的时候,优化器会去计划这条sql的执行最优方案,既使用哪个索引