Loading

数据库之索引总结

索引在数据库中可以说是相当重要的一块知识点了，也是面试经常被问的，这篇文章就总结一下索引相关的知识点，包括索引的底层实现原理，索引的分类，最左匹配原则等。

一、索引的概念

索引能快速找出在某一列中有一特定值的行。不使用索引，mysql必须从第一条记录开始查找，直到找到相关的行。如果表中查询的列有一个索引，mysql能快速到达某个位置去搜索数据文件，而不必查看所有数据。索引是对数据库中一列或多列的值进行排序的一种结构。

二、创建和删除索引

（1）在创建表指定索引列。

（2）使用alter table语句在存在的表上创建和删除索引

// 创建索引
alter table table_name add index(column_name);
// 删除索引
alter table table_name drop index index_name;

（3）使用create index语句在已存在的表上创建和删除索引

// 创建索引
create index index_name on table_name(column_name);
// 删除索引
drop index index_name on table_name;(内部被映射成alter table语句)

三、索引的一些基础知识点

（1）索引可以加快数据库的检索速度。
（2）索引会降低插入，删除，修改的速度。
（3）索引需要占用物理和数据空间。
（4）索引的最左匹配原则。
（5）索引的分类：聚集索引和非聚集索引。
（6）MySQL支持Hash索引和B+树索引。

四、索引为什么可以加快检索速度

这还得从MySQL的存储结构说起。MySQL的基本存储结构是页。（记录都存在页里面）
1.jpg

2.jpg

  • 各个数据页组成一个双向链表。

  • 每个数据页中的记录组成一个单向链表。

  • 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

  • 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写select * from user where username = 'CodeTiger’这样没有进行任何优化的sql语句，默认会这样做：
（1）定位到记录所在的页。
（2）需要遍历双向链表，找到所在的页。
（3）从所在的页中找到对应的记录。
（4）由于不是根据主键查询，只能遍历所在页的单链表。

很明显，在数据量很大的情况下这样检索会很慢。

那么索引究竟是怎么提高检索速度的呢？

其实就是将无序的数据转换成有序的（相对）。
3.jpg
要找到id为8的简要步骤：

4.jpg

很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过目录就可以很快地定位到对应的页上了！

其实底层结构就是B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。

五、索引降低增删改的速度

B+树是平衡树的一种。

平衡树：它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。

一棵普通的树，在极端的情况下， 会退化成单链表。
5.jpg

B+树是平衡树的一种，是不会退化成链表的，树的高度都是相对比较低的(基本符合矮矮胖胖(均衡)的结构)【这样一来我们检索的时间复杂度就是O(logn)】！从上面我们也可以看见，建立索引实际上就是建立一颗B+树。

综上，我们可以得到下面的知识点

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

六、Hash索引

除了B+树之外，还有一种常见的是哈希索引。

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

  • 本质上就是把键值换算成新的哈希值，根据这个哈希值来定位。

6.jpg

看起来Hash索引挺快的，但其实它有一些局限性

  • 无法利用索引完成排序。

  • 不支持最左匹配原则

  • 在有大量重复键值情况下，哈希索引的效率也是很低的----->哈希碰撞。

  • 不支持范围查询。

七、InnoDB支持Hash索引吗？

主流的还是使用B+树索引比较多，对于哈希索引，InnoDB是自适应哈希索引的（hash索引的创建由InnoDB存储引擎引擎自动优化创建，我们干预不了）！
7.jpg

八、聚集索引和非聚集索引

简单概括

  • 聚集索引就是以主键创建的索引。

  • 非聚集索引就是以非主键创建的索引。

区别

  • 聚集索引在叶子节点存放的表中的数据。

  • 非聚集索引在叶子节点中存放的是主键和索引列。

  • 使用非聚集索引查询数据时，拿到叶子上的主键再去查找相应的数据（回表）。

非聚集索引也叫做二级索引。非聚集索引在建立的时候也未必是单列的，可以多个列来创建索引。

  • 此时就涉及到了哪个列会走索引，哪个列不走索引的问题了(最左匹配原则–>后面有说)。

  • 创建多个单列(非聚集)索引的时候，会生成多个索引树(所以过多创建索引会占用磁盘空间)。

8.jpg

在创建多列索引的时候，也涉及到一种特殊的索引---->覆盖索引。

  • 我们前面知道了，如果不是聚集索引，叶子节点存储的是主键+列值
  • 最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢
  • 覆盖索引就是要查询出的列和索引是对应的，不做回表操作！

比如说：

  • 现在我创建了索引(username,age)，在查询数据的时候：select username , age from user where username = ‘CodeTiger’ and age = 20。
  • 很明显地知道，我们上边的查询是走索引的，并且，要查询出的列在叶子节点都存在！所以，就不用回表了~
  • 所以，能使用覆盖索引就尽量使用吧~

九、组合索引失效的情况（可以使用explain命令查看是否使用索引）

（1）使用or做连接字时，若or左右两边有一边不是组合索引中的字段，失效。

（2）最左匹配原则。

（3）模糊查询时，当%在前缀时，索引失效。当前缀没有%，后缀有%时，索引失效。

（4）如果列类型为字符串，则where查询时一定要用引号括起来，否则索引失效。

（5）当全表扫描速度比索引速度快时，MySQL会使用全表扫描，索引失效。

十、总结

  • 最左前缀匹配原则。这是非常重要、非常重要、非常重要（重要的事情说三遍）的原则，MySQL会一直向右匹配直到遇到范围查询（>,<,BETWEEN,LIKE）就停止匹配。

  • 尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。

  • 索引列不能参与计算，尽量保持列“干净”。比如，FROM_UNIXTIME(create_time) = ‘2016-06-06’ 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ： create_time = UNIX_TIMESTAMP(‘2016-06-06’)。

  • 尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。

  • 单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引。

参考资料

（1）zhuanlan.zhihu.com/p/23624390–简单理解索引
（2）blog.csdn.net/mysteryhaoh…-- MySQL学习之——索引(普通索引、唯一索引、全文索引、索引匹配原则、索引命中等)
（3）monkeysayhi.github.io/2018/03/06/…—浅谈MySQL的B树索引与索引优化

posted @ 2018-08-11 23:19  CodeTiger  阅读(41)  评论(0)  编辑  收藏  举报