MySQL | 索引 | MyISAM存储引擎的索引和 InnoDB存储引擎的索引（图文详解）

最新推荐文章于 2023-07-02 12:51:10 发布

瘦弱的皮卡丘

最新推荐文章于 2023-07-02 12:51:10 发布

阅读量896

点赞数 3

分类专栏： SQL 文章标签：索引 MyISAM存储引擎 InnoDB存储引擎 myisam innodb

本文链接：https://blog.csdn.net/ThinPikachu/article/details/105433907

版权

SQL 专栏收录该内容

15 篇文章 3 订阅

订阅专栏

我们将MySQL中的索引简单的理解为一种数据结构，索引的加入提高了查询效率。

一. MyISAM存储引擎中的索引

MyISAM存储引擎中的索引在底层的实现为B+树。

1.MyISAM存储引擎中索引的实现特征

MyISAM存储引擎中索引的实现特征：数据和索引分离。

假如，现在有下面一张表，以及这张表中存放的数据

stu
id	name	age
001	zhangsan	18
002	lisi	20
003	wangwu	21
004	zhaoliu	19
005	gaoxin	20
......	.......	.......

现在我们基于stu表的id字段建立一个名为index_1的索引

create index index_1 on stu(id);

现在我们来详细的看一下索引index_1在底层的具体实现

我们知道，现在我们已经将基于stu表的id字段建立的名为index_1的索引创建好了，现在我们为了满足数据和索引分离的特征，以及还要能通过索引来查找数据，所以我们让这个B+树的叶子节点的记录集指针指向这个表中每行数据的地址，即：

假如现在我们进行了以下查询：（索引查询）

select *
from stu
where id = "003";

那么系统根据index_1这个索引查询的路径为下图

在这样的情况下的查询效率就是O( $log_{x}n$ )，x代表B+树的路数。

如果此时我们又进行了以下查询：（非索引查询）

select *
from stu
where name = "zhangsan"

我们知道当前的索引index_1是根据字段id来建立的，所以当我们使用name作为关键字来做查询时（非索引查询，因为没有使用建立索引的字段进行查询），就变成了了顺序遍历（因为，在B+树中，所有的叶子节点构成了一个链表，系统就会从第一个叶子节点开始寻找，依次遍历，直到找完为止。），在这样的情况下的查询效率就是O(n)。

当我们在MyISAM存储引擎中创建一个表时，这个表会相应生成三个文件

.frm文件，存放创建表时的基本信息。
.myi文件，这个表存储了数据的索引。
.md文件，这个表存储的是数据。

2.非聚集索引

非聚集索引是一种索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

因为在 MyISAM存储引擎中数据可以随意存储，因此它的离散成度也非常的大，所以我们将MyISAM存储引擎中建立的索引称为非聚集索引。

3.MyISAM存储引擎中的主索引

在MyISAM存储引擎中，只要索引值不重复的索引都被称为主索引。所以MyISAM存储引擎中可以从在多个主索引。

4.MyISAM存储引擎中的辅索引

在MyISAM存储引擎中，索引值重复的索引都被称为辅索引。

在MyISAM存储引擎中，无论是主索引还是辅索引，他们的叶子节点都保存的是数据的地址，因此多个索引之间可以保持同步的关系。

二.InnoDB存储引擎中的索引

InnoDB存储引擎中的索引底层的实现为B+树

1.InnoDB存储引擎中索引的实现特征

InnoDB存储引擎中索引的实现特征：数据和索引是结合起来的，即把索引当成数据的一部分。

同样，现在有一张stu表，以及这张表中存放的数据。

stu

id	name	age
001	zhangsan	18
002	lisi	20
003	wangwu	21
004	zhaoliu	19
005	gaoxin	20
......	.......	.......

同样，我们基于stu表的id字段建立一个名为index_1的索引

create index index_2 on stu(id);

我们知道，现在我们已经将基于stu表的id字段建立的名为index_2的索引创建好了，现在我们为了满足数据和索引结合的特征，以及还要能通过索引来查找数据，所以我们让这个B+树的叶子节点的记录集保存该字段代表的数据的所有内容（也就是说叶子节点存储的是完整的数据），即：

假如现在我们进行了以下查询：（索引查询）

select *
from stu
where id = "003";

那么系统根据index_2这个索引查询的路径为下图

在这样的情况下的查询效率就是O( $log_{x}n$ )，x代表B+树的路数。

2.聚集索引

聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况，所以，对应的聚集索引只能有一个。如果某索引不是聚集索引，则表中的行物理顺序与索引顺序不匹配，与非聚集索引相比，聚集索引有着更快的检索速度。

因为在 InnoDB 存储引擎中数据的存放依赖索引，不能随意存放，因此它的离散成度就比较小，所以我们将 InnoDB 存储引擎中建立的索引称为聚集索引。

3. InnoDB 存储引擎中的主索引

许多情况下我们在建好表之后，并没有主动的建立索引，这时系统会帮我们创建，

首先我们创建好表之后，系统会查看表中有没有主键，如果我们设置了主键，那么系统就会根据主键来建立一个主键索引，接着将这个主键索引当为主索引（主索引不允许索引值重复）。

如果表中没有设置主键，那么系统就会查看表中有没有唯一键，如果有唯一键，那么系统就会根据唯一键来建立一个唯一索引，接着将这个唯一索引当为主索引（主索引不允许索引值重复）。

如果表中没有设置唯一键，此时就不能根据其他字段或键来建立索引了，因为其他字段或键的值可能会重复。此时系统就会在表中添加一个隐藏字段，这个字段的大小为6字节。接着系统就会根据这个隐藏字段来建立索引，此时这个索引就变成了主索引了。

所以InnoDB 存储引擎中的主索引只有一个。

4.InnoDB 存储引擎中的辅索引

我们知道在stu表中主键为字段id，因此我们以id建立的索引就被称为主索引，现在我们以字段name为索引值来建立索引。

create index index_4 on stu(name);

具体的索引如下图所示：

此时， InnoDB 存储引擎中stu这张表就存在了两个索引，index_2（主）和 index_4（辅），也就是说数据在这两个索引中进行了备份。如果，此时我们要修改数据，例如

updata stu
set age = 22
where name = "zhangsan";

因为我们是以name作为查询条件的，那么系统就会根据index_4这个索引查询，具体的查询的路径为下图：

因为现在stu存在两个索引，那么我们在修改数据时，只修改了index_4这个索引中zhangsan的年龄，而index_2索引中zhangsan的年龄还没有被修改，那么如何保证这个两个索引中的数据进行同步呢？

现在个问题我们可以细分为：

数据冗余
空间占有率太大，因为每个索引的叶子节点都要存储完整的数据

为了解决上面的两个问题，我们规定除了主索引的叶子结点存储完整的数据外，其他的所有辅索引的叶子结点都存储主索引的索引值。所以在InnoDB 存储引擎中我们可以将叶子节点存储完整的数据的索引称为主索引，剩下的其他所有索引都成为辅索引。在上述情况中，我们在按name字段做查询时，先查询了辅索引，通过辅索引查到了主索引的索引值，接着又查询了主索引，通过索引值查找到了数据，一共查询了两次。所以此时的查询效率为 O( $log_{x}n$ )+O( $log_{x}n$ )=2O( $log_{x}n$ )=O( $log_{x}n$ )。

三.索引的优点

创建索引可以大大提高系统的性能。

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？因为，增加索引也有许多不利的方面。

四.索引的缺点

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

五.索引的优化

1.哪种情况应该建立索引

经常用于查询的字段
主键的字段
用于两个表连接的字段
经常用于排序查询的字段
范围查询
经常用于条件过滤

在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引：

在经常需要搜索的列上，可以加快搜索的速度；
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

2.哪种情况下不应该建立索引

不经常用于查询的字段
数值较小的字段（例如sex字段）
数值较大的字段（例如某个字段存放的数据是text，BLOL类型的数据）
修改性能大于查询性能的字段

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：

对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

六.索引的分类

索引的分类是根据索引建立时字段的特性来确定的。

1.普通索引

这是最基本的索引，它没有任何限制。

创建方式：

①直接创建索引

CREATE INDEX index_name ON table(field_name);

②修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON field_name;

③创建表的时候同时创建索引

INDEX index_name(field_name);

2.唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。

创建方式：

①直接创建唯一索引

CREATE UNIQUE INDEX indexName ON table(field_name);

②修改表结构的方式添加唯一索引

ALTER TABLE table_name ADD UNIQUE INDEX indexName ON (field_name);

③创建表的时候同时创建索引

UNIQUE INDEX index_name(field_name);

3.主键索引

基于主键建立的索引被称为主键索引。

4.全文索引

MySQL的MyISAM存储引擎从3.23.23版开始支持全文索引和全文检索，InnoDB存储引擎目前还不支持全文索引。全文索引仅可用于 MyISAM 表。他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。对于较大的数据集，将你的资料输入一个没有全文索引的表中，然后创建索引，其速度比把资料输入现有全文索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

5.单列索引

基于一个字段建立的索引被称为单列索引。

6.多列索引（组合索引）

基于多个字段建立的索引被称为单列索引（组合索引）。

7.组合索引的最左前缀原则

假如现在有如下一张表

title
id	author	time	head	content

在这张表中我们基于 id ,author和 time三个字段来建立一个组合索引。因为，此时的索引值是由三个部分组成的，所以只有当下面三种情况出现时才能使用这个组合索引

//第一种
select xxx
from title
where id = xxx;

//第二种
select xxx
from title
where id = xxx AND author = "xxx";

//第三种
select xxx
from title
where id = xxx AND author = "xxx" AND time = "xxx";

即在条件过滤里面，从左边开始，依次包含的字段都可以使用索引。

七.索引的注意事项

索引不会包含有NULL值的列：只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
使用短索引：短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
索引列排序 ：MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
like语句操作：一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%xxx%” 和 like %xxx 不会使用索引进行查询。而like “xxx%”则可以使用索引进行查询。
不要在列上进行运算：例如有如下两个查询语句

select *
from stu
where time < "2019-8-01 00:00:00";

和

select *
from stu
where month(time) < 8;

我们看到上面的两个查询语句都是查找时间在8月份以前的数据的。但是，只有第一个查询语句可以使用索引进行查询，而第二个不能。因为第二个查询语句将在每个行上进行运算，这将导致索引失效而进行全表扫描。

瘦弱的皮卡丘

关注

3
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL | 索引 | MyISAM存储引擎的索引和 InnoDB存储引擎的索引（图文详解）

目录一. MyISAM存储引擎中的索引1.MyISAM存储引擎中索引的实现特征2.非聚集索引3.MyISAM存储引擎中的主索引4.MyISAM存储引擎中的辅索引二.InnoDB存储引擎中的索引1.InnoDB存储引擎中索引的实现特征2.聚集索引3. InnoDB 存储引擎中的主索引4.InnoDB 存储引擎中的辅索引三.索引的优点四.索引的缺点五...
复制链接

扫一扫