MySQL系列：索引

目录

索引基础

什么是索引？
官方解释：索引(Index)是帮助MySQL高效获取数据的数据结构。

通俗理解：索引是一种特殊的文件（InnoDB 数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。

为什么使用数据索引能提高效率？

  • 数据索引的存储是有序的
  • 在有序的情况下，通过索引查询一个数据是无需遍历索引记录的
  • 极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N)

索引的优缺点
索引不是万能的，索引可以加快数据检索操作，但会使数据修改操作变慢。
索引的优点：

  • 减少服务器需要扫描的数据量
  • 避免排序和临时表
  • 将随机 I/O 变为 顺序 I/O

索引的缺点：

  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度
    因为更新表时，MySQL不仅要刷新数据，还要刷新索引文件
  • 建立索引会占用磁盘空间的索引文件
    一般这个问题不太严重，但如果在一个大表上创建了多种组合索引，索引文件的会膨胀很快
  • 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果
    对于非常小的表，大部分情况下简单的全表扫描更高效

数据页

在操作系统中，为了跟磁盘交互，内存也是分页的（一页大小4KB）。同样的在MySQL中为了提高吞吐率，数据也是分页的，不过MySQL的数据页大小是16KB（确切的说是InnoDB数据页大小），里面包含多条数据，它还有指向下一页的指针和指向上一页的指针。

数据库在查询到一条数据的时候会把页中相邻的数据也取出来，然后会把数据（OS页大小为4k，通常为其整数倍）直接加载的到内存中，下次要访问这一页的数据时，直接从内存中找，可以减少磁盘IO次数。

页的结构

名称 中文名 大小（单位：B） 描述
File Header 文件头部 38 描述当前页通用的状态信息
Page Header 页面头部 56 描述数据页特有的状态信息
Infimum + Supermum 最小记录和最大记录 26 插入的两条虚拟记录——即Infimum最小记录、Supremum最大记录
User Records 用户真实记录 不确定 实际存储的行记录内容
Free Space 空闲空间 不确定 页中尚未使用的空间
Page Directory 页目录 不确定 页目录中包含若干个槽，每个槽中会存储某个数据记录在该页的地址偏移量
File Trailer 文件尾部 8 校验当前页是否完整

在MySQL内存中，多个这样的数据结构作为节点构成一个双向链表。

表的实际数据是存放在数据页里的，然后表的索引其实也是存放在页里的，此时索引放在页里之后，就会有索引页。

MySQL中InnoDB采用的B+Tree索引结构中，每个节点就可以理解为是一个页，其中叶子节点也就是数据页，除了叶子节点以外的节点就是索引页。非叶子节点只存放了索引，只有叶子节点中存放了真实的数据。

B+Tree索引本身并不能直接找到具体的一条记录，只能知道该记录在哪个页上，数据库会把页载入到内存，再通过二分查找定位到具体的记录。

B+Tree的高度一般为2-4层，就可以满足千万级数据的存储。查找数据的时候，一次页的查找代表一次 I/O，那我们通过主键索引查询的时候，其实最多只需要2-4次 I/O 就可以了。

如何定位一个Record：

  1. 通过根节点开始遍历一个索引的B+Tree，通过各层非叶子节点达到底层的叶子节点的数据页（Page），这个Page内部存放的都是叶子节点;
  2. 在Page内部找到键则返回。如果当前Page里没有找到合适的键，这时借助Page页内部的next page指针，跳转到下一个page继续查找。

Page和B+Tree之间并没有一一对应的关系，Page只是作为一个Record的保存容器，它存在的目的是便于对磁盘空间进行批量管理。

MySQL索引都有哪些分类？

按数据结构分类可分为：B-Tree索引、Hash索引、空间数据索引、全文索引。
按物理存储分类可分为：聚簇索引、非聚簇索引（也叫二级索引、辅助索引）。
按字段特性分类可分为：主键索引、普通索引、前缀索引。
按字段个数分类可分为：单列索引、联合索引（也叫复合索引、组合索引）。

1、按数据结构分类

B-Tree索引

B-Tree（B树）通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，很适合查找范围数据。

  • 所有键值分布在整颗树中（索引值和具体data都在每个节点里）
  • 任何一个关键字出现且只出现在一个结点中
  • 搜索有可能在非叶子结点结束（最好情况O(1)就能找到数据）
  • 在关键字全集内做一次查找,性能逼近二分查找

InnoDB使用的是B+Tree
B+Tree 的每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。

  • 叶子节点保存了完整的索引和数据，而非叶子节点只保存索引值，因此它的查询时间固定为 log(n)
  • 叶子节点中有指向下一个叶子节点的指针，叶子节点类似于一个单链表
  • 因为叶子节点保存了完整的数据以及有指针作为连接，B+Tree 可以增加了区间访问性，提高了范围查询，而B树的范围查询相对较差
  • B+Tree 更适合外部存储，因为它的非叶子节点不存储数据，只保存索引

B+Tree 叶子结点存储的是主键 KEY 或者具体数据。

假设 ID 为主键，以 user_name 为索引来分类讨论：

  • 主键KEY
    • 执行 select * from user_info where user_name = 'Bob' ,InnoDB 就会建立 user_name 索引B+树，节点里存的是 user_name 这个 KEY，叶子节点存储的数据的是主键 KEY
    • 拿到主键 KEY 后，InnoDB 才会去主键索引树里根据刚在 user_name 索引树找到的主键 KEY 查找到对应的数据
  • 具体数据
    • 执行 select * from user_info where id = 15 语句时，InnoDB 就会查询这颗主键 ID 索引B+树，找到对应的 user_name='Bob'

为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据？
因为 InnoDB 需要节省存储空间。一个表里可能有很多个索引，InnoDB 都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引数据文件就变得非常巨大（数据极度冗余了）。在牺牲较少查询的性能下节省了巨大的磁盘空间，这是非常有值得的。

为什么说 B+树比B树更适合实际应用中操作系统的文件索引和数据库索引？

  • B+Tree 的磁盘读写代价更低
    • B+Tree 的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说I/O读写次数也就降低了
  • B+Tree 的查询效率更加稳定
    • 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当

组合索引
组合索引是两个或更多个列上的索引。

对于组合索引，MySQL支持从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。

组合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不知道姓，电话簿将没有用处。

假设有如下一个表：

CREATE TABLE People (
  last_name varchar(50) not null,
  first_name varchar(50) not null,
  dob date not null,
  gender enum('m', 'f') not null,
  key(last_name, first_name, dob)
);

其组合索引包含表中每一行的last_name、first_name和dob列。其结构如下：

按索引的最左边前缀(leftmost prefix of the index)来进行查询：

  • 查询必须从索引的最左边的列开始，否则无法使用索引
    • 例如不能直接利用索引查找在某一天出生的人
  • 不能跳过某一索引列
    • 例如不能利用索引查找last name为Smith且出生于某一天的人
  • 存储引擎不能使用索引中范围条件右边的列
    • 查询语句为WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，该查询只会使用索引中的前两列，因为LIKE是范围查询

哈希索引

基于哈希表实现，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可。

哈希索引的优势：

  • 等值查询，Hash 索引的查询效率要远高于 B-Tree 索引
    • 前提是：没有大量重复键值，如果大量重复键值时，哈希索引的效率很低，因为存在所谓的哈希碰撞问题

哈希索引不适用的情况：

  • 不支持范围查询
  • 不支持索引完成排序
  • 不支持联合索引的最左前缀匹配规则

空间数据索引(R-Tree)

在涉及LBS的服务开发过程中，经常需要存储地理空间的位置并进行一定计算。

MyISAM支持空间索引，主要用于地理空间数据类型。

空间数据索引和 B-Treee 索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。

全文索引

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。

全文索引查找的是文本中的关键词，更类似于搜索引擎所做的事情，而不是简单的 WHERE 条件匹配。

全文索引通过建立倒排索引，可以极大的提升检索效率，解决判断字段是否包含的问题。
倒排索引（Inverted index）：也常被称为反向索引、置入档案或反向档案，是一种索引方法，被用来存储在全文搜索下某个单词在一个文档或者一组文档中的存储位置的映射。它是文档检索系统中最常用的数据结构。

2、按物理存储分类

MySQL索引按叶子节点存储的是否为完整表数据分为：聚簇索引、非聚簇索引（二级索引、辅助索引）。

聚簇索引(cluster index)

聚簇索引（Clustered indexes）是指索引和数据存储在一块。

在B+树索引数据结构里，叶子节点就是数据页自己本身，那么就可以称这颗B+树索引为聚簇索引。

聚簇索引的每个叶子节点存储了一行完整的表数据，叶子节点间按id列递增连接，可以方便地进行顺序检索。

InnoDB表要求必须有聚簇索引，默认在主键字段上建立聚簇索引，在没有主键字段的情况下，表的第一个非空的唯一索引将被建立为聚簇索引，在前两者都没有的情况下，InnoDB将自动生成一个隐式的自增id列，并在此列上建立聚簇索引。

MyISAM为存储引擎的表不存在聚簇索引
MyISAM表中的主键索引和非主键索引的结构是一样的，索引的叶子节点不存储表数据，存放的是表数据的地址。所以，MyISAM表可以没有主键。

非聚簇索引（二级索引）

二级索引的数据和存储数据是分离的，它的叶子节点并不存储一行完整的表数据，而是存储了聚簇索引所在列的值。

聚簇和非聚簇对比

回表查询

由于二级索引的叶子节点不存储完整的表数据，索引当通过二级索引查询到聚簇索引列值后，还需要回到聚簇索引也就是表数据本身进一步获取数据。

回表查询需要额外的 B+Tree 搜索过程，必然增大查询耗时。
需要注意的是，通过二级索引查询时，回表不是必须的过程，当SELECT的所有字段在单个二级索引中都能够找到时，就不需要回表，MySQL称此时的二级索引为覆盖索引或触发了索引覆盖。

索引下推

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

  • 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件
  • 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器

准备一张用户表(user)，其中主要几个字段有：id、name、age、ismale。建立联合索引（name，age）。
假设有这么个需求，查询表中“名字第一个字是张，性别男，年龄为10岁的所有记录”。

mysq> select * from tuser where name like '张 %' and age=10 and ismale=1;

没有索引下推：

根据最左前缀原则会忽略 age 这个字段，直接通过 name 进行查询，在(name,age)这课树上查找到了4个结果，需要回表4次。
通过索引下推优化：

InnoDB 并没有忽略 age 这个字段，而是在索引内部就判断了age是否等于10，对于不等于10的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了2个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表2次。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数，从而提升整体性能。

3、按字段特性分类

MySQL索引按字段特性分类可分为：主键索引、普通索引、前缀索引。

  • 主键索引
    • 建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，通常在创建表时一起创建
    • 唯一索引：建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突
  • 普通索引
    • 建立在普通字段上的索引被称为普通索引
  • 前缀索引
    • 前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引

4、按索引字段个数分类

MySQL索引按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。

  • 单列索引
    • 建立在单个列上的索引被称为单列索引
  • 联合索引（复合索引、组合索引）
    • 建立在多个列上的索引被称为联合索引，又叫复合索引、组合索引

使用索引的策略

什么时候索引会失效？

  1. 违反最左前缀原则
  2. 使用反向查询（!=, <>,NOT LIKE）
    MySQL在使用反向查询（!=, <>, NOT LIKE）的时候无法使用索引，会导致全表扫描，覆盖索引除外。
  3. LIKE以通配符开头
    但是使用通配符结尾就没有问题。
  4. 对索引列做任何操作
    对索引列做了其他操作，例如数值计算、使用函数、（手动或自动）类型转换等操作，会导致索引失效。
  5. OR连接
    使用OR连接的查询语句，如果OR之前的条件列是索引列，但是OR之后的条件列不是索引列，则不会使用索引。

索引创建（使用）原则

  1. 不为离散度高的列创建索引
  2. 只为用于搜索、排序或分组的列创建索引
  3. 用好联合索引
    不要为联合索引的第一个索引列单独创建索引；
    建立联合索引的时候，一定要把最常用的列放在最左边。
  4. 对过长的字段，建立前缀索引
  5. 频繁更新的值，不要作为主键或索引
  6. 随机无序的值，不建议作为索引
    例如身份证、UUID等。

什么情况下应不建或少建索引

索引并不是万能的，在以下几个场景的时候，我们应该尽量不建或者说少建索引：

  • 表记录太少
  • 经常插入、删除、修改的表
  • 数据重复且分布平均的表字段
    • 假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度
  • 经常和主字段一块查询但主字段索引值比较多的表字段
posted @ 2022-03-20 23:53  当康  阅读(574)  评论(0)  编辑  收藏  举报