掘金 后端 ( ) • 2024-03-04 11:28
【MySQL篇】谁能告诉我索引到底咋存储的🤬？我能，快来！
索引 🤬

一、为什么要用索引

我们在使用SQL进行快乐查询的时候，如果单表数据量过大，100w、1000w条，我们会发现查询就贼慢(未建立索引)，往往需要的查询时间2秒左右及往上，在当今互联网应用程序的时代，这样的查询效率肯定是不满足业务上的需求的，SQL慢就等于接口慢，接口慢用户体验就不好，用户就会流失。

为了满足我们在海量数据中查询某条、多条记录的场景时，我们就要使用到索引来进行查询的加速 🚀！

慢查询示例

数据量：11009180条 索引：仅创建主键索引，其余字段未创建索引

执行以下SQL

SELECT * FROM study_mysql_01.employees a WHERE a.group_name = 'Whale Hi：48'

耗时：1.373s

结果：1条

二、什么是索引

我们可以把索引看做是一个字典，字典就是通过目录来定位到页码，然后翻到对应的页数来快速定位查询的文字，字典存放目录的地方就是一个索引，目录组成的方式就是指定好的数据结构。索引也是如此，也是通过某种数据结构来快速定位到具体的数据。

索引存放的位置

  • InnoDB引擎：索引和数据都存储在*.ibd文件中，i=index，d=data，索引和数据。
  • MyISAM引擎：索引和数据分开，索引存在*.MYI，数据存在*.MYD

三、有索引和没有索引的区别

不存在索引，查询数据时，就会一行一行的寻找和比对数据，直至找到符合条件的数据结束，容易造成全表扫描，从而影响查询性能。

如果我要进行查询：id=3，name=3的数据，那么就要进行3次IO，每次IO读取4kb的数据，因为IO操作是非常损耗性能的，如果是全表扫描的话，则会更加的慢，整体耗时相加就造成了慢查询。

image-20220905151335484

如果查询的字段有索引，则可以直接通过查找索引列来，定位到数据地址，通过地址就可以直接获取到整行数据。

问题：查找索引列和扫描表有什么区别呢？不都是查找，为什么所以的效率会更高呢？

因为所以使用了特定的数据结构进行存储，通过数据结构自身的优势就可以进行快速查找！

四、索引的分类

在MySQL中索引共有5种：

  • 主键索引(聚簇索引)：指定主键后自动生成，也就是直接使用主键查询数据，性能是最高的。

  • 普通索引：

    • # 创建普通索引
create index idx_name on employees(emp_name);

  • 唯一索引：表中的每一行的某一列中的数据是唯一的，比普通索引的性能高。

    • # 创建唯一索引
create unique index idx_name on employees(emp_name);

  • 组合索引：多个字段联合查询时，可以创建这个索引。一次为多个字段创建索引。一个联合索引最好不要超过5个字段，否则可能就是结构的问题。

    • 需要遵循最左前缀法则，否则索引将无效。

    • # 创建组合索引
create index idx_name on employees(emp_name,emp_group);

  • 全文索引：类似于搜索引擎，在全文(不同的字段、不同的表)中检索某个关键词、字。

    • 建议使用ElasticSearch、Solr等专业的搜索引擎。

五、MySQL索引

MySQL使用了B+Tree进行构建索引，之所以不使用BTree是因为两种数的结构不同

  • BTree会在每个节点中存储key和数据，而B+Tree只会在叶子节点中存储数据，非叶子节点只存储key和指针，这样可以缩小节点的体积，一次性加载更多到内存中，供查询使用。
  • BTree的叶子节点是独立的，而B+Tree的叶子节点通过指针相连，在MySQL这种关系型数据库中，数据之间可能存在某种关系，所以B+Tree可以支持范围查询。

每一个节点都被称之为页，每个页的大小均为16KB。只有叶子节点才会存储数据的。非叶子节点只会存储索引key和指针。

img

B+Tree的特点

  • 非叶子节点只存储索引的键，也就是索引列的数据，这样存储的索引数据多，树的层数更低，对于查询利用率更高。
  • 叶子节点存储索引和数据的键值对，通过叶子节点的指针相连，就能快速定位。
  • 叶子节点和其它叶子节点之间又提供了指针，这样可以提高数据的区间访问性能。
    • 如果我查找了name为customer1的数据，MySQL首次进行索引比较后会缓存customer1叶子节点的指针信息，我再次查找customer2的时候，我就不需要进行非叶子节点的比较，直接可以通过customer1的指针获取到customer2的叶子节点，此时耗费的时间几乎为0；
  • 叶子节点是从小到大，从左到右排列的。

3层B+Tree最多存储：

  • 第一层：如果主键是int类型的话，int占用4个字节，指针占用6个字节，一对就是10个字节。16KB可以存储16*1024/10 = 1638对。

  • 第二层：

    • 两层B+Tree：1638也就是可以管理1638个页指针，假设每行的数据大小为1kb，那么每页就可以存储16条数据，所以第二页就是：1638 * (16/1) = 26208条数据
    • 三层B+Tree：三层的话相当于第二层存储的东西和第一层是一样的，所以可以存储：1638 * 1638 = 2683044

  • 第三层就是：假设每行的数据大小为1kb，那么每页就可以存储16条数据，所以就是：2683044 * 16 = 42928704条

因此得到结论，3层B+Tree对于主键是int类型的，可以存储4千200万左右的数据。如果是bigint的话，则主键占用8个字节，也就是：

  • 第一层：16*1024 / (8+6) = 1170对
  • 第二层：1170*1170 = 1368900对
  • 第三层：1368900 * 16 = 21902400条

这样就证实了一种说法，既：MySQL单表不建议超过2000w数据。一旦超过2000w那么B+Tree就会超过3层。查询效率就会慢，因为要再多一次的磁盘IO。

六、页的构成

页分为两种：叶子节点的页、非叶子节点的页

非叶子节点的页

image-20221108145217238

只存储key(索引)、页的指针。

叶子节点的页，

在这里插入图片描述

主要存储了：

  • 页头信息
  • 页目录信息
  • 页中最大记录和最小记录
  • 用户数据信息
  • 校验是否完整
相关内容
MySQL索引（六）索引优化补充，分页查询、多表查询、统计查询
【MySQL系列】一条SQL，我怎么知道它有没使用到索引？
MySQL索引（五）索引优化分析工具
MySQL索引（四）常见的索引优化手段
你管这叫数据库索引？
在PostgreSQL中管理索引
JAVA都这么卷了，MySQL索引你还不了解吗？一文带你熟悉MySQL索引。
无索引的join关联的表，你如何优化？
Mysql篇——索引
数据库管理系统基础-第二篇：索引