MySQL索引原理

提鸟笼的老头

已于 2022-03-01 21:24:38 修改

阅读量260

点赞数

分类专栏： MySQl 文章标签： mysql

于 2022-03-01 19:27:19 首次发布

本文链接：https://blog.csdn.net/qq_39597872/article/details/123210877

版权

MySQl 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL索引原理

索引是帮助MySQL高效获取数据，排好序的数据结构。

如何创建，删除索引

主键索引

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

唯一索引

ALTER TABLE `table_name` ADD UNIQUE (`column`)

普通索引：

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

联合索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

查看索引

show index from table_name;

数据是如何存储的

MySQL数据是存储在安装文件的data目录下，innoDB引擎，一张表分为两个文件，分别为.frm和.ibd文件，frm文件存储的是表的元信息，ibd文件存储的是表的数据，数据的存储方式是采用B+树的结构存储。
MySQL数据存储位置

索引的数据结构

本文所有的分析都是采用innoDB的引擎。

Hash

优点：

对索引的key进行一次hash运算就可以定位到数据存放的位置地址；

缺点：

hash容易导致hash冲突；
hash只能适应in或者“=”的查询，无法适应范围查询；

二叉树

特点：在插入的时候容易引起树的高度过高，它无法自动平衡树两边的结构。
在这里插入图片描述

B-Tree

特点：

叶节点具有相同的高度，叶节点的指针为空
所有索引元素不重复
节点中的数据从左往右递增排列
由于每个节点中都带有当行的数据，一页能存储的数据较少。MySQL一页读取的数据为16kb

B+树

1、非叶子节点不存储data，只存储索引（冗余），每一页可以放更多的索引；
2、叶子节点包含当行的所有数据；
3、叶子节点之间用指针链接，页和页之间互相存储对方在磁盘中的位置；在这里插入图片描述

索引优缺点

无索引是怎么查找数据
我们都知道数据库的数据是存储在磁盘当中的，当数据没有索引的时候，查询sql的时候会从磁盘中一页页（MySQL默认一页是16kb数据）的读取数据，然后在内存中比对，直到找到所有结果。
优点：快速的查找数据
缺点： 1. 增加磁盘空间，每次建立索引是要增加磁盘空间的。
2. 导致增加，修改和删除的复杂度增加，因为索引为了维护有序。

索引的分类

逻辑分类
主键索引：主键索引其实包含了唯一索引，他的区别是主键索引不能为空，我们的数据库中有主键索引时数据的结构其实是这样的
在这里插入图片描述

唯一索引：

唯一索引和主键索引类似的数据结构，这里不重复赘述。

一般索引

一般索引与唯一索引的区别是他的叶子节点存储的主键索引的值，而不是整行的数据。
在这里插入图片描述

联合索引

联合索引与一般索引类似，只不过索引的key为多个字段组成，叶子节点也是存储的主键索引的值。

在这里插入图片描述

物理分类

聚簇索引

叶子节点中包含了整行的数据

非聚簇索引

叶子节点中的数据只包含主键的值，查询的数据较多的情况下需要回表。

覆盖索引

SQL语句只通过索引，就取到了所需要的数据，这个过程就叫做索引覆盖。如果创建的二级索引为name,age,sex的联合索引，我们的sql语句包含的列直接可以通过此索引获得，无需回表可拿到所有数据。

select id,name.age,sex from user;

回表

非聚簇索引查询的过程中需要用到回表，例如上文中只有已name建立普通索引，下面的语句就会去回表查询，查询步骤，首先是通过name=lisi去查询这个二级索引，拿到主键的值，然后通过主键的值找聚簇索引拿到整行的记录。涉及到多次IO操作，效率不高。

select * from user where name = 'lisi'

最左前缀原则

最左前缀原则是发生在多个字段建立联合索引的时候，数据库中是按照最左边的字段依次来构造索引，就像我们的order by排序一样，首先是按照第一个字段来排序，只有在第一个字段相同的情况下才按照第二个字段排序。所以我们在建立索引和查询语句要对应起来。比如我们按照name,age,sex建立联合索引

select * from user where name = 'lisi' and age='18' and sex='1'; -- 此句就会走索引
select * from user where  age='18' and sex='1'; -- 此句就不会走索引
select * from user where   sex='1'; -- 此句就不会走索引