技术博客
首页 所有文章 标签 分类 关于
技术博客

目录

MySQL索引详解

 收录于 Mysql
   约 3444 字   预计阅读 7 分钟 
https://bing.ee123.net/img/rand?artid=146206063

MySQL索引详解

1.从磁盘说起

计算机中的很多数据都是要以文件的形式存储在磁盘上的，一个磁盘硬件内部有很多盘片，如下所示：

一整个磁盘的物理结构：

https://i-blog.csdnimg.cn/direct/65781a1ce4c445c3af1969558cb46c56.png

盘片的物理结构：

https://i-blog.csdnimg.cn/direct/9293adf2d4cc45f4acdbc122f4c2986e.png

一个盘片又被分成了很多的扇区，这一个个的扇区就是磁盘进行IO的基本单位，大小为512字节（现在随着技术的发展，可能更大）。而对于操作系统来说，磁盘IO的单位为512字节还是太小了，所以操作系统和磁盘IO的基本单位是4KB大小的数据块。

在磁盘的物理结构中，主要起存储作用的就是这个盘片，一个文件可能被存储在磁盘的一个或多个扇区上。

2.MySQL、OS、磁盘之间的结构关系

MySQL是应用软件，操作系统是系统软件、磁盘是硬件。他们三者之间的结构我们可以这样来表示：

https://i-blog.csdnimg.cn/direct/b157ab3f4e5c49fdac3ef37e4d0ac7d1.png

操作系统会开辟一个文件缓冲区来和外部进行IO，同样，MySQL内部也会开辟一块空间来和外部进行IO，这块大空间叫做bufferpool，MySQL和操作系统之间进行IO的基本单位是16kb，我们可以间接认为MySQL和磁盘IO的基本单位就是16kb， 这16kb大小的数据单元在MySQL里面叫做 page。

我们可以使用 show global status like ‘innodb_page_size’ 命令来查看这个page的大小：

https://i-blog.csdnimg.cn/direct/eebcc15b6b3e4ccca0785a7f7b71162b.png

  • 这里查出来的单位是字节，16384byte/1024=16kb。

3.理解索引

注意，后面的内容都是以 innodb 存储引擎为例讲解的。

什么是page

所谓的page在物理上其实就是一个个的大小为16kb的数据块，但又不仅仅是数据块那么简单。page是MySQL和操作系统进行数据IO的基本单位，在MySQL中，可能存在很多的page，MySQL为了管理好这些page，就要为其创建数据结构，如下所示：

https://i-blog.csdnimg.cn/direct/5969e8f8683e4bee9a4a35749a9fd59a.png

所以，page不仅仅是一个物理块，其内部还被写入了一些管理信息。

page的内部结构

MySQL是以表的形式存储数据的，表中一条条的数据被称为一条条记录；而page是MySQL进行IO的基本单位，所以，一张表的信息可能存储在一个page或者多个page之中，每个page之间用指针关联起来，如下图所示：

https://i-blog.csdnimg.cn/direct/c85052285761474b9ed96cac7f757ec7.png

在一个page内部，为了更快的查找数据，MySQL会在page内部构建一个 目录 。在查找数据的时候，MySQL会先遍历目录，确定数据在哪一个分段，然后去遍历对应的分段，从而找到对应的数据。在遍历目录的时候，目录中的数据量远远少于数据记录中的记录，并且，一旦查找到数据所在的分段，就能够排除其他所有分段的数据， 于是能够大大提高查找数据的效率 。

多个page的情况（索引）

如果一张表的数据特别大，那么这张表中的数据就会存储在多个page中，此时，多个page的结构是什么样子的呢？

如果一张表中的数据需要用多个page来存储，MySQL就会为该表创建 专门存储目录的page（目录页） 和 专门存储数据的page（数据页） 。构建出一棵类似于多叉树结构的数据结构来管理这多个page之间的关系，没错，这个多叉树的数据结构就是 B+树 。

这棵B+树就是我们说的索引 ！！！（ 注意：这是 innodb 和 MyISAM 存储引擎的索引结构 ）

https://i-blog.csdnimg.cn/direct/3c0861fca8fa40a59eb14c87f10723de.png

在这颗B+树中，只有叶子结点存放数据，其他所有结点都只存放目录。

为什么索引能够提高查询的效率

理由一： 数据结构和算法提高查询效率。

当我们在索引当中查找数据的时候，MySQL首先会从根结点，也就是最上面的目录页开始，根据我们指定的搜索关键字，判断我们要查找的数据在那个区域段，从而选择对应的目录页，依次从上往下，直到找到数据所在的叶子节点，在叶子结点内部，又可以通过查找目录项快速查找数据。

理由二： IO次数的减少提高查找效率。

在目录页中，所有的空间都用来存储目录，而不存储数据，也就意味着，一个目录页可以存储很多的目录，这也就势必导致整个索引结构是一颗比较矮胖的多叉树结构，既然这棵树比较矮胖，那么从根结点到叶子结点的路径就短了，也就意味着要加载的page少了，这不就意味着 IO的次数少了吗，也就提高了搜索的效率。

其他数据结构来建立索引是否可行

链表？

  • 如果使用链表建立索引的话，我们查找数据的时候，只能线性遍历，效率低下；
  • 并且线性遍历的时候还需要线性的加载page，不能很好的减少page的加载，也就无法保证减少IO的次数。

二叉搜索树？

  • 二叉搜索树最大的问题就是在面对特殊数据的时候，肯能会退化成单支树，也就变成链表结构了，此时也是线性遍历。

AVL树、红黑树？

  • AVL树和红黑树就是为了解决二叉搜索树退化问题而设计的，能够有效避免二叉搜索树退化成单支问题，从而达到平衡或者近似平衡。
  • 虽然是平衡或者近似平衡，但是毕竟是二叉结构，相比较多阶B+，意味着树整体过高，大家都是自顶向下找，树的高度越高，意味着系统与硬盘需要进行更多的IO，这一点会让效率大打折扣。

Hash？

  • 官方的索引实现方式中， MySQL 是支持HASH的，不过 InnoDB 和 MyISAM 并不支持。Hash跟进其算法特征，决定了虽然有时候也很快(O(1))，不过，在 面对范围查找就明显不行。

B树 VS B+树

B树 ：在B树中，结点既有数据又有指针；叶子结点互不相连。

https://i-blog.csdnimg.cn/direct/6a9221752e454721a316d29c7bc6721b.png

B+树 ：在B+树中，只有叶子结点中有数据，其他结点中只有目录页，并且叶子结点首尾相连。

https://i-blog.csdnimg.cn/direct/b1c8ee06f3164ba8ae562a778d6f7ff6.png

innodb为何选择B+树作为索引而不是B树？

B+树的非叶子节点中全部存储目录，而不存储数据，这就意味着B+树的目录页能够存储更多的目录，也势必就导致B+树相比于B树会更加矮胖，需要加载的page会变少，IO的次数也就减少了，所以使用B+树作为索引的效率会高于B树做索引。

并且，B+树的叶子结点首尾相连，更方便进行范围查找；如果使用B树作为索引的话，范围查找时需要不断的从根结点开始遍历，遍历的次数多了，加载的page也会变多，IO次数多了，效率自然就低了。

聚簇索引 VS 非聚簇索引

在MySQL当中，MyISAM 存储引擎和 InnoDB 存储引擎都是采用B+树作为索引结构，他们之间的不同是，MyISAM采用非聚簇索引的方案，而InnoDB采用聚簇索引的方案。

非聚簇索引：用户数据与索引数据分离；叶节点的data域存放的是数据记录的地址。

聚簇索引：用户数据与索引数据在一起；叶节点的data域存放的就是数据记录。

MyISAM 索引结构：

https://i-blog.csdnimg.cn/direct/0d8b1fe703b04387874e4538f9f26a3d.png

InnoDB 索引结构：

https://i-blog.csdnimg.cn/direct/247183438dd0454cb13acdf534962392.png

辅助索引

在MySQL中，除了默认建立的主键索引外，用户也可以指定列来建立索引，一个索引就是一张B+树结构；用户指定列建立的索引就是辅助索引。

在MyISAM 存储引擎中，建立辅助索引和主键索引没有区别，无非就是主键不可重复。

在InnoDB 存储引擎中，建立的辅助索引，最终的叶子结点中的数据域中存储的是对应的构建主键索引的主键值，因此，在InnoDB中使用辅助索引查找数据需要先在辅助索引中找到对应的 主键值，然后通过主键值从主键索引查找对应的记录，这种行为叫做回表查询。InnoDB只在主键索引的叶子结点存储数据，能够很好的节省空间。

4.索引的操作

创建索引

创建索引的原则：

  • 比较频繁作为查询条件的字段应该创建索引（查的多）
  • 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件（唯一性要好）
  • 更新非常频繁的字段不适合作创建索引（更新少）
  • 不会出现在where子句中的字段不该创建索引（会在where子句中出现）

创建主键索引

创建主键索引有三种方式：

方式一：在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));

方式二：在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));

方式三：创建表以后再添加主键
create table user3(id int, name varchar(30));

alter table user3 add primary key(id);

创建唯一索引

方式一：在表定义时，在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);

方式二：创建表时，在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));

方式三：创建表之后再添加主键
create table user6(id int primary key, name varchar(30)）；

alter table user6 add unique(name);

创建普通索引

方式一：在创建表的最后指定索引
create table user8(id int primary key,
    name varchar(20),
    email varchar(30),
    index(name) --在表的定义最后，指定某列为索引
);

方式二：创建表之后通过变更表的属性添加普通索引
create table user9(id int primary key, name varchar(20), email varchar(30));

alter table user9 add index(name); --创建完表以后指定某列为普通索引

方式三：通过 create index 为指定的表创建索引
create table user10(id int primary key, name varchar(20), email varchar(30));
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);

查询索引

  • 第一种方法： show keys from 表名
  • 第二种方法: show index from 表名;
  • 第三种方法（信息比较简略）： desc 表名；

以第一种方式为例：

https://i-blog.csdnimg.cn/direct/2acf0dbef3204cea89c76dc231a98329.png

如果我们不想以表格的形式显示，可以带上\G：

https://i-blog.csdnimg.cn/direct/def52ea6fc2d4d6689feadbde9bd638e.png

删除索引

  • 第一种方法（删除主键索引）： alter table 表名 drop primary key;

  • 第二种方法（删除其他索引）： alter table 表名 drop index 索引名；

    索引名就是 show keysfrom 表名 中的 Key_name 字段

  • 第三种方法： drop index 索引名 on 表名

使用示例

创建并查看索引：

https://i-blog.csdnimg.cn/direct/35a924d0d6c94a4f83a09d8c858e7b35.png

删除并查看索引：

https://i-blog.csdnimg.cn/direct/59f8422c22324061967f35492e30fe5d.png

更新于 2025-03-12
 数据库, Mysql
返回 | 主页