mysql的各类索引+在B+树的体现

火冠蜂鸟

已于 2023-08-31 11:28:25 修改

阅读量1.2k

点赞数 1

分类专栏： # mysql 文章标签： mysql 数据库

于 2023-07-10 17:16:59 首次发布

本文链接：https://blog.csdn.net/weixin_70280523/article/details/131641014

版权

mysql 专栏收录该内容

33 篇文章 0 订阅

订阅专栏

一.四个索引类型

1、单列索引：一个索引只包含单个列，除了主键索引,一个表中可以有多个单列索引。

二.两者索引方法,btree索引和hash索引的区别

MySQL的索引分四类：

单列索引（Normal普通索引、Unique唯一索引、主键索引）、

多列索引 (联合主键,组合索引)、

Full Text 全文索引

SPATIAL 空间索引

MySQL的索引方法分两类：

btree索引和hash索引

一.四个索引类型

1、单列索引：一个索引只包含单个列，除了主键索引,一个表中可以有多个单列索引。

（1）Normal 普通索引(二级索引,非聚簇索引)：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹是为了查询数据更快一点。

（2）Unique 唯一索引(二级索引,非聚簇索引)：索引列中的值必须是唯一的，但是允许为空值。

（3）主键索引(聚簇索引)：是一种特殊的唯一索引，非空且唯一。（主键约束，就是一个主键索引）。

主键索引与唯一索引的区别：

（1）主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

（2）主键创建后一定包含一个唯一性索引，唯一性索引并不一定是主键。

（3）唯一性索引列允许空值，而主键列不允许为空值。

（4）主键索引在创建时，已经默认为非空值+唯一索引了。

（5）一个表最多只能创建一个主键索引，但可以创建多个唯一索引。

（6）主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

（7）主键可以被其他表引用为外键，而唯一索引不能。

2.多列索引:一个多列索引包含多个列

1.联合主键

1)什么是联合主键

(也叫复合主键、组合主键),一个表中有多个主键列组合在一起,叫联合主键,一个表中只能有一个联合主键.

create table test
(
   name varchar(19),
   id number,
   value varchar(10),
   primary key (name,id)
)

2)为什么要使用联合主键

比如一个部门人员表,你不想单独建一个id列作为主键的话,就可以用部门列和员工name 列都设为主键,作为表的联合主键,这样也可以保证表中每行数据都不重复.

2.组合索引

1)什么是组合索引

(也叫复合索引、联合索引),组合索引是不含主键索引的,是多个普通索引组合在一起的.一个表中可以有多个组合索引.

ALTER TABLE mytable ADD INDEX name_city_age (name,age,city);

把一个表的name,age,city三个非主键列设为一个组合索引,数据库实际上自动为我们设立了3个索引,分别是(name) (name,age) (name,age,city)

2)为什么要使用组合索引

减少开销。
建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。比分别建立索引(col1),(col2),(col3)少了两个索引. 每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
覆盖索引。
对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

3.最左原则

联合主键和组合索引都要遵守最左原则.

怎么查询才能保证全部走索引呢,那么就是要保证最左原则.

1.where条件后面必须含有第一个列name,

2.顺序不能跳,就是不能是name和city,而没有age.

3.where条件后面的name和age在写在前后都无所谓.

走全部索引的例子:

SELECT * FROM mytable WHREE name="admin"

SELECT * FROM mytable WHREE name="admin" AND age= 20

SELECT * FROM mytable WHREE age= 20 AND name="admin"

SELECT * FROM mytable WHREE name="admin" AND age= 20 AND city ="北京"

SELECT * FROM mytable WHREE city ="北京" AND name="admin" AND age= 20

走部分索引的例子:

SELECT * FROM mytable WHREE name="admin" AND city ="北京"

完全没走索引的例子:

SELECT * FROM mytable WHREE age= 20
SELECT * FROM mytable WHREE age= 20 AND city ="北京"

3.Full Text 全文索引

在大量数据中，通过其中的某个关键字，就能找到该字段所属的记录行。全文索引在开发中很少用，因为其占用很大的物理空间和降低了记录修改性。

4.SPATIAL 空间索引

空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

二.两者索引方法,btree索引和hash索引的区别

1、BTREE（B树（可以是多叉树）） {主流使用}
2、HASH（key,value）这种方式对范围查询支持得不是很好

hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。
可能很多人又有疑问了，既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。

（1）Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。

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

（2）Hash 索引无法被用来避免数据的排序操作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

（3）Hash 索引不能利用部分索引键查询。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash 索引在任何时候都不能避免表扫描。

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。
————————————————
原文链接：https://blog.csdn.net/guo_qiangqiang/article/details/88794971

三. 索引在B+树的工作原理

1.我先建了一个表

CREATE TABLE `user_innodb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我创建了一个存储引擎为InnoDB的表user_innodb，其中包含主键id、姓名字段（name）、性别字段（gender，用0，1表示不同性别）、手机号字段（phone），并批量初始化了500W+条数据。

2.主键索引(聚簇索引)

注意:一张表只能有一个主键,也只能有一个聚簇索引, 也就只能有一个主键树

不管这个主键是只有一个字段还是几个字段组成的联合主键, 这张表的主键树只有一个,

如果主键是一个字段, 那么这个主键树就只记录这个一个字段,

如果主键是两个字段,那么这个主键树就是组合索引B+树的样式, 记录的是两个主键字段在一个B+树的形式.

上图就是mysql的innodb引擎根据我们设的id自动为我们生成的一个主键B+树索引结构,我们给它取了个名字,叫主键索引.又叫做聚簇索引.

主键索引有两个特点：

按照主键的大小对用户记录和数据页进行排序，记录用单向链表连接，叶子节点使用双向链表连接；
B+树的叶子节点保存了用户的完整记录。

3.普通索引(二级索引,非聚簇索引)

主键索引是在搜索条件为主键的时候才会发挥作用，但是我要以name='蝉沐风'为搜索条件怎么办？

innodb的解决办法就是再创建一个B+树（我们称为name索引）,这就是普通索引.

这棵普通索引的B+树和聚簇索引的B+树有点区别：

叶子节点存放的不再是完整的用户记录，而是只记录name列和主键值；
数据页中存放的用户记录和目录项记录由原本的按照主键排序变为按照name列排序；
目录项记录除了存储索引列（name）和页号之外，同时还存储了主键值；（大家可以想一想，为什么要存储主键值,因为name列可能不是唯一的,如果两个name相同,那么谁排前面谁排后面呢,这时候就要按主键值来排序了.）

有了这棵B+树，你就可以通过name列快速找到主键值了，查找的方式和根据主键值查找用户记录的方式完全一样，只不过前者查到的是主键值，后者查找到的是一条完整的用户记录罢了。

你可能对字符串进行二分法感到有点奇怪，甚至没有接触过的相关知识的读者连对字符串进行排序都会觉得很诧异。其实在创建表的时候我们可以对字符串字段指定字符集和比较规则，如果你不指定，MySQL会默认给你设置，总之，MySQL总会找到一个方式对字符串进行排序。

现在得到主键的id了，然后根据主键id到主键索引中查找到完整的用户记录，这个过程叫做回表。如果没有为name列设置唯一性约束，那就可能找到多个符合条件的主键id，多回几次表就可以了。

对name这种单个列添加的索引叫做普通索引，也叫二级索引。

4.组合索引 (其实就是多列的主键索引和多列的普通索引)

(也叫复合索引、联合索引)

假设我们为name列和phone列建立联合索引,name和phone列可以是两个主键组成的联合主键

也可以是两个普通列组成的组合索引.

（注意我描述的顺序,name列在前,phone列在后,后面的图的顺序也就是按这个顺序排的），自然也是创建一棵B+树，这棵B+树和之前又稍微有点不同：

叶子节点存放的是name列、phone列和主键值；
目录项记录除了存储索引列（name、phone）和页号之外，同时还存储了主键值；（大家可以想一想，为什么要存储主键值）
数据页中存放的用户记录和目录项记录由原本的按照主键排序变为按照name列排序，如果name列相同，那就按照phone列排序；（如果phone列再一样呢？你现在明白为什么要存储主键值了吗？）

再画个图吧（有点偷懒了哈，数据页号没换）：