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

2024-03-31 19:18
文章标签 mysql 详解 引擎 索引 存储 图文 innodb database myisam

本文主要是介绍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存储引擎中创建一个表时，这个表会相应生成三个文件

  1. .frm文件，存放创建表时的基本信息。
  2. .myi文件，这个表存储了数据的索引。
  3. .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. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  2. 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
  4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
  5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 

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

四.索引的缺点

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

五.索引的优化

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
idauthortimeheadcontent

在这张表中我们基于 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";

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

七.索引的注意事项

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

和

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

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

 

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


http://www.chinasem.cn/article/865117。

相关文章

Spring Security基于数据库验证流程详解

Spring Security基于数据库验证流程详解

Spring Security 校验流程图 相关解释说明（认真看哦） AbstractAuthenticationProcessingFilter 抽象类 /*** 调用 #requiresAuthentication(HttpServletRequest, HttpServletResponse) 决定是否需要进行验证操作。* 如果需要验证，则会调用 #attemptAuthentica
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
异构存储（冷热数据分离）

异构存储（冷热数据分离）

异构存储主要解决不同的数据，存储在不同类型的硬盘中，达到最佳性能的问题。 异构存储Shell操作 （1）查看当前有哪些存储策略可以用 [lytfly@hadoop102 hadoop-3.1.4]$ hdfs storagepolicies -listPolicies （2）为指定路径（数据存储目录）设置指定的存储策略 hdfs storagepolicies -setStoragePo
阅读更多...
HDFS—存储优化（纠删码）

HDFS—存储优化（纠删码）

纠删码原理 HDFS 默认情况下，一个文件有3个副本，这样提高了数据的可靠性，但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码，采用计算的方式，可以节省约50％左右的存储空间。 此种方式节约了空间，但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件，都会执行此策略。 默认只开启对 RS-6-3-1024k
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
OpenHarmony鸿蒙开发（ Beta5.0）无感配网详解

OpenHarmony鸿蒙开发（ Beta5.0）无感配网详解

1、简介 无感配网是指在设备联网过程中无需输入热点相关账号信息，即可快速实现设备配网，是一种兼顾高效性、可靠性和安全性的配网方式。 2、配网原理 2.1 通信原理 手机和智能设备之间的信息传递，利用特有的NAN协议实现。利用手机和智能设备之间的WiFi 感知订阅、发布能力，实现了数字管家应用和设备之间的发现。在完成设备间的认证和响应后，即可发送相关配网数据。同时还支持与常规Sof
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2