「DB」MySQL学习笔记---索引

一、索引常见模型

1. 哈希表

  • 会发生哈希冲突，可以拉链法解决。
  • 优点：查询快；插入无序，所以新增比较快。
  • 缺点：因为不是有序的，所以哈希索引做区间查询的速度是很慢的。
  • 哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。

2. 有序数组

  • 有序数组在等值查询和范围查询场景中的性能就都非常优秀。
  • 优点：查询快，用二分法就可以快速得到，这个时间复杂度是 O(log(N))。
  • 缺点：更新慢，往中间插入一个记录就必须得挪动后面所有的记录，成本太高。
  • 有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据。

3. 搜索树

  • 每个节点的左儿子小于父节点，父节点又小于右儿子。
  • 查询时间复杂度：O(log(N))。
  • 为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))。
  • innoDB里是多叉树，B+树，⤵️⤵️⤵️
  • 以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。
  • 这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。
  • 考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

二、InnoDB 的索引模型

  • 在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。
  • 每一个索引在 InnoDB 里面对应一棵 B+ 树。
/*每一个索引在 InnoDB 里面对应一棵 B+ 树。
假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。这个表的建表语句是：*/
CREATE TABLE IF NOT EXISTS T (
	id INT PRIMARY KEY,
	k INT NOT NULL,
	name VARCHAR(16),
	INDEX(k))ENGINE=INNODB;
	
INSERT INTO T VALUES (100, 1, "aa"),(200, 2, "bb"),(300, 3, "cc"),(500, 5, "add"),(600, 6, "eee");
  • 从图中不难看出，根据叶子节点的内容，索引类型分为「主键索引」和「非主键索引」。
  • 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
  • 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

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

  • 如果语句是 select * from T where ID=500，即「主键查询方式」，则只需要搜索 ID 这棵 B+ 树；
  • 如果语句是 select * from T where k=5，即「普通索引查询方式」，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。
  • 也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

索引维护 & 主键自增

  • 如果主键不是自增，新增数据会导致向前插入或者页分裂的问题，影响性能。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。
  • 自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。
  • 显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

三、再了解一些！

💯 一个练习题

刚才创建的表 T 中，如果执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？

看看这条 SQL 查询语句的执行流程：

  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）。

索引覆盖

  • 查的东西在索引树上，不需要回表，就是覆盖索引。
  • 由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀原则

  • B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

  • 顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上。

  • 可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

在建立联合索引的时候，如何安排索引内的字段顺序？

  • 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
  • 必须维护多个索引的时候，再考虑空间。

索引下推

  • MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
  • 如果执行select * from tuser where name like '张%' and age=10 and ismale=1;
  • 上图是没有索引下推，会回表四次；
  • 有了索引下推，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。
posted @ 2020-03-15 12:35  antonGo  阅读(168)  评论(0)    收藏  举报