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

本文主要是介绍MySQL | 索引 | MyISAM存储引擎的索引和 InnoDB存储引擎的索引（图文详解），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

一. MyISAM存储引擎中的索引

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

2.非聚集索引

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

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

二.InnoDB存储引擎中的索引

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

2.聚集索引

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

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

三.索引的优点

四.索引的缺点

五.索引的优化

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

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

六.索引的分类

1.普通索引

2.唯一索引

3.主键索引

4.全文索引

5.单列索引

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

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

七.索引的注意事项

我们将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月份以前的数据的。但是，只有第一个查询语句可以使用索引进行查询，而第二个不能。因为第二个查询语句将在每个行上进行运算，这将导致索引失效而进行全表扫描。

这篇关于MySQL | 索引 | MyISAM存储引擎的索引和 InnoDB存储引擎的索引（图文详解）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

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

一. MyISAM存储引擎中的索引

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

2.非聚集索引

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

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

二.InnoDB存储引擎中的索引

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

2.聚集索引

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

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

三.索引的优点

四.索引的缺点

五.索引的优化

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

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

六.索引的分类

1.普通索引

2.唯一索引

3.主键索引

4.全文索引

5.单列索引

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

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

七.索引的注意事项

相关文章

mysql中的group by高级用法

Nginx location匹配模式与规则详解

Android实现在线预览office文档的示例详解

Mysql用户授权(GRANT)语法及示例解读

C# foreach 循环中获取索引的实现方式

Mysql如何解决死锁问题

Java实现优雅日期处理的方案详解

Java中的JSONObject详解

HTML5中的Microdata与历史记录管理详解

html5的响应式布局的方法示例详解

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

一. MyISAM存储引擎中的索引

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

2.非聚集索引

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

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

二.InnoDB存储引擎中的索引

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

2.聚集索引

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

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

三.索引的优点

四.索引的缺点

五.索引的优化

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

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

六.索引的分类

1.普通索引

2.唯一索引

3.主键索引

4.全文索引

5.单列索引

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

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

七.索引的注意事项

相关文章

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