【SQL应知应会】索引（三）• MySQL版：聚簇索引与非聚簇索引；查看索引与删除索引；索引方法

欢迎来到爱书不爱输的程序猿的博客, 本博客致力于知识分享，与更多的人进行学习交流

本文收录于SQL应知应会专栏,本专栏主要用于记录对于数据库的一些学习，有基础也有进阶，有MySQL也有Oracle

前言

✅今天继续SQL的索引的第 3 篇文章，主要讲到了聚簇索引与非聚簇索引、查看索引与删除索引、索引方法，大家拭目以待吧！
✴️文章中提供了代码和很具体的截图，代码是为了减轻大家学习的难度，同时用截图可以更形象的让大家去理解知识点想要表达的意思，希望大家跟着一起学起来
💖希望文章的内容对大家有所帮助，如果有什么不足的地方，大家可以在评论区或者私信我，感谢大家的支持
💻那么，快拿出你的电脑，跟着文章一起学习起来吧

一、索引

1.简介

• 在索引（一）中描述了索引的优点与缺点，可以帮助我们更好的理解为什么使用索引以及学习索引应该思考哪些问题。

2.索引类型之逻辑分类

• 在索引（一）中主要讲了索引按逻辑分类后，普通索引和复合索引的创建与应用，其中复合索引的创建是重中之重，重点讲述了六大应用，对于“最左前缀”进行了三种情况的详细分析；
• 在索引（二）重点补充了逻辑分类中的唯一索引、主键索引以及全文索引，并针对唯一索引与复合索引、主键索引与复合索引进行了对比分析，加强对各类索引的理解和应用。

3.索引类型之物理分类

• 分为聚簇索引(主键索引和唯一索引)和非聚簇索引（有时也称辅助索引或二级索引）
• 聚簇是为了提高某个属性(或属性组)的查询速度，把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块。

3.1 聚簇索引 (clustered index)

• 不是单独的一种索引类型，而是一种数据存储方式。
• 这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。
• 聚簇索引也可理解为将数据存储与索引放到了一块，找到索引也就找到了数据。

3.2 非聚簇索引

• 数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。
• 虽然InnoDB(支持事务)和MyISAM存储引擎(不支持事务)都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。
• 每张表最多只能拥有一个聚簇索引(一个表只能有一个主键)

3.3 索引方法

3.3.1 B-TREE

• B-树就是B树，多路搜索树，树高一层意味着多一次的磁盘I/O，下图是3阶B树

• 特征：
  • 关键字集合分布在整颗树中；
  • 任何一个关键字出现且只出现在一个结点中；
  • 搜索有可能在非叶子结点结束；
  • 其搜索性能等价于在关键字全集内做一次二分查找；
  • 自动层次控制

3.3.2 B+TREE

• B+树是B-树的变体，也是一种多路搜索树，见图：

• 特征：
  • 所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
  • 不可能在非叶子结点命中；
  • 非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
  • 每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历
  • 更适合文件索引系统

3.3.3 HASH

• 哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

• Hash索引仅仅能满足=,IN和<=>查询，不能使用范围查询,也不支持任何范围查询,例如WHERE price > 100

• 由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。
  

4.查看索引(SHOW INDEX)

4.1 查看索引的语法格式

SHOW INDEX FROM <表名> [ FROM <数据库名>]

• <表名>：指定需要查看索引的数据表名
• <数据库名>：指定需要查看索引的数据表所在的数据库

4.2 示范

• emp表的索引情况
  
• 使用SQL查看

show index from emp;

• table : 表名
• Non_unique : 表示该索引是否是唯一索引
  • 若不是唯一索引，则该列的值为 1；
  • 若是唯一索引，则该列的值为 0
• Key_name : 索引的名称
• Seq_in_index : 表示该列在索引中的位置
  • 如果索引是单列的，则该列的值为 1；
  • 如果索引是组合索引，则该列的值为每列在索引定义中的顺序
• Column_name : 定义索引的列字段
• Collation ：表示列以何种顺序存储在索引中
  • 在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类
• Cardinality : 索引中唯一值数目的估计值
  • 基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时， MySQL 使用该索引的机会就越大
• Sub_part : 表示列中被编入索引的字符的数量
  • 若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；
  • 若整列被编入索引，则该列的值为 NULL
• Packed : 指示关键字如何被压缩。若没有被压缩，值为 NULL
• Null : 显示索引列中是否包含 NULL
• Index_type : 显示索引使用的类型和方法（ BTREE、 FULLTEXT、 HASH、 RTREE）
• Comment : 显示注释

5. 删除索引

5.1 删除索引的语法格式

• 使用 DROP INDEX 语句

DROP INDEX <索引名> ON <表名>

<索引名>：要删除的索引名
<表名>：指定该索引所在的表名

• 使用 ALTER TABLE 语句

ALTER TABLE <> + [句1/句2/句3]

句1：DROP PRIMARY KEY：表示删除表中的主键索引
句2：DROP INDEX index_name：表示删除名称为 index_name 的索引
句3：DROP FOREIGN KEY fk_symbol：表示删除外键

如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除

5.2 示范

drop index index_empno on emp; # 索引名称为index_empno
# 前面4.2中的示范里，索引的名称是2，使用这条SQL语句删除时，会报错，正常情况下，索引名称都不会这样取得，所以无需纠结这种情况

alter table emp drop index index_empno;

😘感谢大家耐心的看完这篇文章，这篇文章是MySQL索引的第3篇文章
✅如果大家觉着内容还算可以，那么就关注一下爱书不爱输的程序猿吧
🏡也可以加入我的社区一起学习呀
🎁各种专栏，精彩不断

• SQL应知应会专栏，对于数据库的一些学习，有基础也有进阶，有MySQL也有Oracle
• UML应知应会专栏，对于UML的一些讲解，应有尽有
• … … 还有java的专栏、算法与数据结构的专栏等其他专栏，快去我的主页关注我吧