Mysql、(二) 索引

最新推荐文章于 2024-06-05 17:27:44 发布

早睡早起爱学习i

最新推荐文章于 2024-06-05 17:27:44 发布

阅读量79

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/qq_43684985/article/details/114272450

版权

MYSQL 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

一、索引概述

索引定义：索引(index) 是帮助MYSQL高效获取数据的数据结构(有序)，索引目的在于提高查找效率，类比于字典，可以简单理解为排好序的数据结构的快速查找。在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

二、索引的优势劣势

优势

提高数据检索的效率，降低数据库的IO成本；
降低数据排序的成本，降低CPU的消耗；

劣势

三、索引结构

在这里插入图片描述

BTREE索引

BTREE 结构(以m叉为例)：

实例：

BTREE树和二叉树相比，查询数据的效率更高，因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度更快。

B+ TREE索引

1.结构介绍

在这里插入图片描述

由于B+ 树的所有信息都保存在叶子节点上，因此查询任何key都要从root 走到叶子，保证了查询的稳定性。

2. Mysql中的B+ 树

在这里插入图片描述
Mysql在原 B+ Tree的基础上进行了优化，增加了一个指向相邻叶子结点的链表指针。这样当需要访问某个范围内的数据时就能够通过链表提高访问区间访问性能。

四、索引分类

InnoDB存储引擎，B+树索引可以分为聚簇索引(也称聚集索引)和辅助索引(也称二级索引、非聚簇索引)。这两种索引的内部都是B+树，聚簇索引的叶子节点存放着一整行的数据。

InnoDB中的主键索引是一种聚簇索引，辅助索引包括复合索引、前缀索引、唯一索引。
InnoDB使用的是聚簇索引，MyISAM使用的是非聚簇索引。

4.1 InnoDB_聚簇索引

在这里插入图片描述

聚簇索引就是按照每张表的主键构造一棵B+树，同时B+树的叶子节点存放的是整张表的行记录数据，我们也将聚簇索引的叶子节点称为数据页。这个特性决定了索引组织表中的数据也是索引的一部分，每张表只能有一个聚簇索引。

InnoDB通过主键聚集数据，如果没有定义主键，innoDB会选择非空的唯一索引来代替，如果也没有这种索引，那么InnoDB会定义隐藏的主键然后在上聚集。

聚簇索引的优点：

可以将相关的数据保存在一起。比如当实现电子邮箱时，可以按照user_id 进行聚集，这样从磁盘上提取几个页面时能够把某个用户的邮件全部抓取出来。如果没有使用聚簇索引，那么每个邮件都需要访问磁盘。
数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。

聚簇索引的缺点：

插入速度严重依赖于插入顺序。按照主键的顺序插入行是把数据装入 InnoDB表的最快方法。否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
聚集表可能比全表扫描慢，尤其是表存储的比较稀疏或者因为分页没有顺序存储的时候。

4.2 InnoDB_辅助索引

在这里插入图片描述

在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。
辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。
由于辅助索引的叶子节点包含了主键列，因此它会比预想的要大。所以我们在设计数据库时需要将主键定义的小一点。
辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

4.3 MyISAM_主键索引

MyISAM 索引文件和数据文件是分离的，索引文件仅仅保存数据的地址。
在这里插入图片描述

4.4 MyISAM_辅助索引

辅助索引和主键索引在结构上没有什么区别，只是主索引要求key是唯一的，而辅助索引的key是可以重复的。在这里插入图片描述
MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
MyISAM索引方式叫非聚簇索引，主要是为了和InnoDB区分开。

4.5 聚簇索引和非聚簇索引的区别

在这里插入图片描述
聚簇索引按照主键聚集，叶子结点存储了主键值和数据行；二级索引的叶子结点存放主键值或者指向数据行的指针。
非聚簇索引叶子结点存储的是数据行的地址。

4.6 覆盖索引

一个索引包含了所有需要查询的字段的值，就称为覆盖索引。覆盖索引可以直接获取列的数据，而不用在读取数据行，所以效率比较高。

覆盖索引的优点：

索引记录通常远小于全行大小。因此如果只读取索引，MySQL就能够极大地减少数据访问量。
对于IO密集型的负载也有帮助，因为索引比数据小很多，能够更好地装入内存。(这对于MyISAM尤其正确，它能够压缩索引；但是InnoDB不能够压缩索引)。
索引是按照索引值进行排序的，因此访问速度将会比随机从磁盘上读取快得多。
覆盖索引对InnoDB表特别有用，因为InnoDB的第二索引在叶子节点中保存了主键值。覆盖了查询的第二索引在主键中避免了另外一次索引查找。

4.7 哪些情况需要创建索引：

主键自动创建唯一索引；
频繁作为查询关键字的字段应该创建索引；
查询中与其它表关联的字段，外键关系建立索引；
频繁更新的字段不适合创建索引，因为每次更新需要重新调整索引；
Where 条件里用不到的字段不创建索引；
单键/组合索引的选择问题？高并发下倾向于创建组合索引；
查询中排序的字段，排序的字段如果通过索引去访问将大大提高速度；(Order by)
查询中统计或者分组字段； (group by)

4.8 哪些情况不适合创建索引：

表的记录太少；
频繁增删改字段。因为同时需要去维护更新索引。
数据重复且平均分布的字段；

五、索引语法(重点)

创建索引：

CREATE [UNIQUE|FULLTEXT|SPATIAL]  INDEX  index_name [USING index_type]  ON  tbl_name(column_name);

# 举例：在city数据表上 的 city_name 字段创建了索引
create index idx_city_name on city(ci	ty_name);

查看索引

show  index from table_name\G;

删除索引

drop index index_name on tbl_name;

alter命令

1) 给该表添加一个主键
   alter table tbl_name add primary key(column_list);
   
2) 添加唯一索引(可以出现多个null)
   alter table tbl_name add unique index_name(column_list);

3) 添加普通索引
   alter table tbl_name add index index_name(column_list);

4) 指定全文索引
   alter table tbl_name add fulltext index_name(column_list);

参考文章：
聚簇索引和非聚簇索引：https://blog.csdn.net/lm1060891265/article/details/81482136

聚簇索引、非聚簇索引、覆盖索引区别

早睡早起爱学习i

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql、(二) 索引

文章目录一、索引概述二、索引的优势劣势三、索引结构一、索引概述索引定义：索引(index) 是帮助MYSQL高效获取数据的数据结构(有序)。在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。二、索引的优势劣势优势提高数据检索的效率，降低数据库的IO成本；降低数据排序的成本，降低CPU的消耗；劣势三、索引结构...
复制链接

扫一扫