MySQL索引

最新推荐文章于 2024-10-02 10:22:35 发布

原创最新推荐文章于 2024-10-02 10:22:35 发布 · 1.2k 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #数据库 #java

索引的分类

主键索引

主键索引也就是在我们设置完表的主键之后自动生成的索引。

在innoDB下是聚簇索引
主键索引不能为null

创建一个表：

CREATE TABLE `test` (
	`id` INT ( 11 ) NOT NULL,
	`name` VARCHAR ( 255 ) DEFAULT NULL,
	`age` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;

查看该表索引信息：

show index from test

在这里插入图片描述

唯一索引

创建一个表，设置identity_card字段为唯一索引：

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `identity_card` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `identity_card_index` (`identity_card`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看该表索引信息：show index from test
在这里插入图片描述

唯一索引列的值必须唯一
该列可以存在null值

单列索引（普通索引）

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `identity_card` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `identity_card_index` (`identity_card`),
  KEY `name_index` (`name`)				
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在这里插入图片描述

一个单列索引只对应一个字段
一个表中可以有多个单列索引

复合索引

复合索引就是一个索引包含多个字段。
比如：

create table test02(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 key(`name`,`uid`)
);

在这里插入图片描述

复合索引一般用于我们在进行一些多字段检索的时候，如果我们经常使用某一个字段组合进行检索，那么就可以为这个组合字段添加一个复合索引，比如：select * from test02 where uid=? and name=?

一个面试题，在下列条件中，哪些能使用我们上述创建的联合索引？

1. name = ? and uid = ? and gender = ? // 可以
1. name = ? and gender = ? and uid = ? // 可以
1. uid = ? and gender = ? // 不可以

对于上述的问题，我们需要知道，复合索引是遵循最左匹配原则，什么是最左匹配原则？
也就是在检索的时候，从左到右的顺序按照检索条件进行检索。
所以在上述的例子中，第一个条件肯定是可以使用到我们复合索引的。

那么，第二个条件为什么也可以？

因为查询引擎为了提高我们复合索引的检索效率，它会对我们的检索条件进行排序，从而去匹配我们创建的复合索引，比如在第二个条件中，按照原来的顺序，那么复合索引的两个字段是分开的，但是在执行的过程中，它会将顺序调整为：name = ? and uid = ? and gender = ? ，这样就可以使用到我们的复合检索了。

尽量不要在表中定义多个单列索引，这样不如去使用复合索引。

其实在我们创建一个复合索引的时候，也就相当于创建了多个索引，包括单列索引。
比如有这样一个复合索引：key(name, age, gender)
相当于创建了三个索引：

name
name + age
name + age + gender

索引的数据结构

B+Tree

在innoDB中，索引的存储使用B+Tree进行存储的。
B+Tree长什么样子?
比如一个表中有以下数据，其实id作为主键：

在这里插入图片描述

innoDB的主键索引默认为聚簇索引，这里的图只是为了更直观的看B+树的结构，真正的存储每一页可以存储16KB的数据，不可能只有这几个的。

B+树的特点：

数据只保存在叶子节点，非叶子节点只存储索引（冗余）以及指向每一页的指针
其叶子节点就形成一个单链表
每一页的存储空间大小为16KB

使用B+Tree的好处？

比如说在上述的表中，我们去查询id为6的数据：

将6和我们根节点存储的索引进行比较，发现6在4到7这个区间中，那么则找到我们索引4的指针所指向的页（这里其实本质上就是二分查找）
找到第二页，存储数据的索引为4，5，6，这样id为6的数据就可以快速的找到了

主键索引为什么要进行排序？

首先，我们来插入一段无序的数据：

insert into test03 values("11", "zhangsan");
insert into test03 values("9", "jack");
insert into test03 values("8", "david");
insert into test03 values("10", "mike");
insert into test03 values("4", "nacy");
insert into test03 values("2", "rose");

select * from test03：

在这里插入图片描述

从图中我们可以看到，乱序插入的数据，查询出的结果确实有序的，这是为什么？

提高我们查询的速度。

其实也很好理解，从上述的例子中我们提到了，该索引的底层是一个B+Tree，在我们进行查询数据的时候，它会根据索引的值去判断当前数据在哪一个区间，定位到该数据在具体的哪一页，也就是二分查找，而这种方式要求索引必须有序。

所以，这也就是为什么不建议使用uuid作为主键值，因为uuid是随机生成的一串很长且有毫无顺序的数值，这样会增加我们内部对索引排序的复杂度，降低其效率。

B Tree（B-Tree）和 B+ Tree的区别

主要区别：

B+树是B树的升级版
B树在任何子节点都可以存储data数据，而B+树只能在叶子节点存储data数据。这样做的好处就是将非叶子节点只存储索引指针，而不存储数据，这样就能存储更多的索引指针（想一下，每一页16KB，数据比指针占用的空间要大），这样就能使我们的整个书变得更矮，不过因此叶子节点也会更胖更宽。数的高度越矮，我们进行的I/O次数就越少。

一般来说，一个B+Tree的层数都在2-4层，其实到达3、4层的B+数就很大了（4层的B+树大概能存储10亿条数据）。

在innoDB中，为了提高查询的效率，存储索引B+树的根节点（树的顶层）常保存在内存中，相当于如果我们要在一个4层的树中查询数据，那么最多只要进行3次I/O操作。（第二层、第三层、第四层）

聚簇索引和非聚簇索引

聚簇索引
将数据存储和索引放到了一块，索引结构的叶子节点存放了行数据，主键索引就是一种聚簇索引。

非聚簇索引
将数据的存储和索引结构分开，索引结构的叶子节点只存储数据的主键值。

在innoDB中，非聚簇索引也成为复制索引或者二级索引，比如像复合索引、单列索引、唯一索引这些非主键索引。它们索引的结构中，叶子节点不再存储数据，而是存储对应行数据的主键，在进行查找时都需要进行二次查找。

在这里插入图片描述
在查找辅助索引时，会先查找到对应叶子节点存储的对应行数据的主键，在根据主键去聚簇索引中查找到数据。

为什么不直接存储指向之际数据的地址，而去存储主键呢？

为了避免在增加或者删除数据的时候出现一些地址改变地址的操作，比如，我们在增加一条数据时，主键索引中存储的数据的物理地址肯定会发生改变，如果辅助索引存储的地址，那么它里面的存储的地址也要跟着改变，这样就会消耗更多的性能去完成这些操作，所以这里采用存储主键的方式。

总结

InnoDB：

InnoDB使用的是聚簇索引，在叶子节点存储行数据，如果查询条件为where id = 1这种根据主键查询，则会根据相关的算法找到叶子节点存储的对应的行数据；
如果检索条件为where name=“zhangsan”，name是一个单列索引，那么会先查找到辅助索引的叶子节点，找到其存储的对应行数据的主键，然后再拿着这个主键，去主键索引中找到改行数据；
聚簇索引默认是主键，如果表中没有定义主键，则InnoDB会选择一个非空的且唯一的索引代替，如果这样的索引也不存在，InnoDB会隐式的去定义一个主键来作为聚簇索引。

MYISAM：