关于索引

2021-10-14 本文已影响0人 ys简单0

在解释数据库索引之前, 我们先来回忆下平时想要查询书中的某个章节,一般情况下,我们不会漫无目的的翻阅直到找到想要找到的内容, 而是会查找书的目录,获取到章节的页码后直接翻到对应的页码; 其实在这里目录就充当着书的索引, 来加快查询的速度.
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构.

二.常见索引分类

1.1唯一索引和普通索引

普通索引：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值，用来提高数据库的查找速度。
唯一索引：索引列的值必须唯一，但允许有NULL，如果列是字符类型，插入多条为null的数据不会触发唯一索引，但是如果是多条为空字符的会触发唯一索引。如果是组合索引，则列值的组合必须唯一。
主键索引：是一种特殊的唯一索引，不允许有空值

主键和唯一索引的区别:
主键:
1.用于唯一标识表中的每一条数据，不能重复，不能为空;
2.一般使用Long类型搭配auto_increment;(设置自增长的字段必须是主键，也就是说只有设为主键的列才可以设置为自增长).
唯一索引:
1.用于唯一标识表中的每一条数据，不能重复，可以为空;
2.一张表中只能有一个主键，但是一张表中可以有多个唯一键;

1.2创建示例

建表时添加:
CREATE TABLE `test`(
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `name` varchar(32) NOT NULL COMMENT '名称',
    `code` varchar(32) NOT NULL COMMENT '编码',
    `order_id` varchar(64) NOT NULL COMMENT '单号',
    `inserttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入库时间',
    `updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),   ---------------------------->主键
    KEY `idx_inserttime` (`inserttime`),---->普通索引(单列索引)
    UNIQUE KEY `idx_order_id` (`order_id`),----->唯一索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试表';

在已有表上添加:
主键:
ALTER TABLE `test ` ADD PRIMARY KEY(`id`);
普通索引:
ALTER TABLE `test ` ADD KEY `idx_inserttime`(`inserttime `);
唯一索引:
ALTER TABLE `test ` ADD UNIQUE KEY `idx_order_id `(`order_id `);

2.1单列索引和组合索引

单列索引：顾名思义单列索引就是以某一个字段来创建的索引,可以是普通索引,也可以是唯一索引。
组合索引：以2个或2个以上字段联合创建的索引称为组合索引或者多列索引。

建表时添加:
CREATE TABLE `test`(
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) NOT NULL COMMENT '名称',
    `code` varchar(20) NOT NULL COMMENT '编码',
    `order_id` varchar(64) NOT NULL COMMENT '单号',
    `inserttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入库时间',
    `updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),   ---------------------------->主键
    KEY `idx_name_order_id` (`name`,`order_id`),---->组合索引(普通索引)
    UNIQUE KEY `idx_name_code` (`name`,`code`),----->组合索引(唯一索引)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试表';

在已有表上添加:
组合索引:
ALTER TABLE `test ` ADD KEY `idx_name_order_id`(`name`,`order_id`);
单列索引:
ALTER TABLE `test ` ADD KEY `idx_order_id `(`order_id `);

三.索引的结构和原理

3.1磁盘结构以及数据存储

在了解索引结构前,我们先来看下正常情况下我们mysql数据库表中的数据是怎么存储在磁盘中的。
其中磁头是用于向磁盘读写信息的工具，磁盘上的一圈圈的圆周被称之为磁道，每圈磁道上的扇形小区域被称为扇区(以下称之为block)，扇区中又存在着很多存储单元用于存储比特信息。每一个block大小是一样的,假设是16384byte;操作系统读取数据都是按照block(也称之为一页)为单位进行。磁头可以旋转伸缩来定位一个block。

磁盘如何存储数据库数据:
在上面test表中:
id --> bigint --> 8byte
name --> varchar(20) --> 20byte
code --> varchar(20) --> 20byte
order_id --> varchar(64) --> 64byte
inserttime --> timestamp --> 8byte
updatetime --> timestamp --> 8byte
所以一条数的大小为128byte;
假设总共有1000条这样的数据,那么存储这些数据需要128 * 1000 / 16384 = 7.8 个block, 假设当前没有设置索引的情况下查询一条记录,最多需要查找8个block。下面来看下索引是如何提高查询速度的;

索引的数据结构
提到数据结构,就不得不提存储引擎, 因为在不同的存储引擎中索引的结构可能是不相同的, 最常见到的存储引擎有MyISAM 和 InnoDB, 在这两种存储引擎中索引的数据结构都是B+树,但在存储的过程中又稍有不同, 在MyISAM的主键索引B+树的叶子节点中存储的是该条数据的地址,0x....;而在InnoDB的主键索引B+树的叶子节点中存储的是真正的数据.

MyISAM
从上面我们可以知道数据顺序的写在磁盘上, 占用8个block; 现在我们使用id来创建一个索引, 并且还把当前这个id对应的数据在磁盘中的位置也记录下来(下面用pointer来描述),这样test表中的每一行数据都会有一条这样的记录。

结构.png

那在磁盘上存储这张索引表,需要占据多少个block呢?
id 8byte
pointer 8byte
所以一条数据为16byte,1000条需要使用 16 * 1000 / 16384 = 0.9 个block; 所以在这样的情况下查找一条test表中的数据最多只需要1次存储索引数据的block数据读入和1次存储test表数据的block数据读入就行了, 相比于没有索引的情况效率提升了很多

但是在上面的操作中如果数据量大的情况还是需要很多次IO,在MyISAM中使用了B+树的数据结构,即只有在叶子节点才存储行数据对应的地址(在InnoDB中存储真正的行数据)，而非叶子节点里面的内容其实是键值和指向数据页的指针。

InnoDB
存储结构下的B+数大概样子:

B+.png

下面来算一下一个2层的B+数能存储多少数据,一个block也就是一页是16384byte, id 8byte, pointer 8byte 总共为16byte,所以第一层最多可存储16384 / (8 + 8) = 1024条数据, 所以第二层就对应1024个block的首地址, 一个block可以存储16384 / 128 = 128条数据, 所以一个2层的B+树可以存储1024 * 128 = 131072条数据,也就是说十几万的数据量,如果要是查询的话最多2次IO就可以了;
一个3层的B+树就可以存储1024 * 1024 *128条数据, 可见效率相当之高。

需要注意的是上面是基于主键索引的, 也就是主键索引的B+数中叶子节点上存储的是真正的数据, 而普通索引,如用order_id创建的索引,其叶子节点上存储的不是整条的数据,而是数据对应的主键值, 查询到主键值后再从主键的B+数中获取到真正的数据。

关于索引

目录

1.索引的概念;

2.索引的分类;

3.索引的结构和原理;

4.索引的使用策略;

一.概念