MYSQL - 索引_mysql中使用索引为什么能加快查询速度-CSDN博客

本文链接：https://blog.csdn.net/liu3290607528/article/details/147829466

1.为什么要用索引

索引能增加数据库查询数据的速度

MySQL索引类似于书籍的⽬录，通过指向数据⾏的位置，可以快速定位和访问表中的数据，⽐如汉语字典的⽬录（索引）⻚，我们可以按笔画、偏旁部⾸、拼⾳等排序的⽬录（索引）快速查找到需要的字

2.索引因该用到的数据结构

1.HASH

时间复杂度（O(1)），时间很快，可惜不支持范围查找

2.二叉搜索树

中序遍历能查找范围

弊端：

1.树的高度太高，会大大损耗磁盘I/O

2.最坏情况：成为单支树，时间复杂度为O(N)

3.b+树

时间复杂度O(logN)

b+树是对二叉搜索树的再优化,第一步：先将二叉树改造成N叉树，以解决磁盘I/O的问题，再将父亲节点放到最后叶子节点里，方便查询

问：于b树相比，b+树有何不同？

1.b+树叶子节点之间有元素的引用，能通过叶子节点找到另外的元素，更加方便查找

（叶子节点之间为双向链表）

2.b+树非叶子节点的值都保存在叶子节点里

MYSQL所有非叶子节点都只保存了对子节点的引用，没有保存真实的数据，所有的真实数据都保存在叶子节点内

3.b+树所有要找的数据都会到叶子节点中去寻找，保证了任意数据时间复杂度的稳定度，性能均衡

3.页

什么是页

在 .ibd ⽂件中最重要的结构体就是Page(⻚)，⻚是内存与磁盘交互的最⼩单元，默认⼤⼩为 16KB，每次内存与磁盘的交互⾄少读取⼀⻚，所以在磁盘中每个⻚内部的地址都是连续的，之所以这样做，是因为在使⽤数据的过程中，根据局部性原理，将来要使⽤的数据⼤概率与当前访问的数据在空间上是临近的，所以⼀次从磁盘中读取⼀⻚的数据放⼊内存中，当下次查询的数据还在这个⻚中时就可以从内存中直接读取，从⽽减少磁盘I/O提⾼性能

• 每⼀个⻚中即使没有数据也会使⽤ 16KB 的存储空间

MYSQL中有很多种页的类型，我们着重讲解数据页

数据页的结构

数据页主要分为页头，页尾，和数据行，如下图所示

其中页头包含很多信息，最重要的是 上一个页号和下一个页号，通过这俩我们能将页与页连接成双向链表，更好查询

其中校验和是为了当传输的数据只传到一半时，用来保护数据

⻚主体部分是保存真实数据的主要区域，每当创建⼀个新⻚，都会⾃动分配两个⾏，⼀个是⻚内最⼩⾏ Infimun ，另⼀个是⻚内最⼤⾏ Supremun ，这两个⾏并不存储任何真实信息，⽽是做为数据⾏链表的头和尾，第⼀个数据⾏有⼀个记录下⼀⾏的地址偏移量的区域 next_record 将⻚内所有数据⾏组成了⼀个单向链表，此时新⻚的结构如下所⽰：

当向⼀个新⻚插⼊数据时，将 Infimun 连接第⼀个数据⾏，最后⼀⾏真实数据⾏连接 Supremun ，这样数据⾏就构建成了⼀个单向链表，更多的⾏数据插⼊后，会按照主键从⼩到⼤的顺序进⾏链接，如下图所⽰

此时会有同学发出疑问，一个页里有那么数据，如果单纯利用单链表来遍历的话，是不是太慢了，于是，大佬们就发挥了他们的大脑，打造出了页目录

页目录

简单来说就是将头目录以外的分为8个一组，将这一组记为槽，存储在页目录的结构里，然后根据主键会从小到大的特性，使用二分查找，先找到槽，再在槽里面遍历，这样就大大减少了查找量

例如要查找主键为6的⾏，先⽐对槽中记录的主键值，定位到最后⼀个槽2，再从最后⼀个槽中的第⼀条记录遍历，第⼆条记录就是我们要查询的⽬标⾏。如下图

为了更好的记录槽数，行数等等我们使用到了数据页头

4.使用索引

1.主键索引

方式一：直接在创建表的时候创建主键索引

-- 主键索引
DROP TABLE if EXISTS love;
create table love (
  id bigint PRIMARY KEY auto_increment, -- 方式一 直接在创建表的时候创建主键索引
  name varchar(20)
);
desc love;

方式二，创建表时单独指定主键列

DROP TABLE if EXISTS loves;
create table loves (
  id bigint auto_increment,
  name varchar(20),
  PRIMARY KEY (id) -- 方式二，创建表时单独指定主键列
  );
  desc loves;

方式三，修改表中的列为主键索引

DROP TABLE if EXISTS lovess;
CREATE TABLE lovess (
  id bigint,
  name varchar(20)
);
ALTER TABLE lovess MODIFY id bigint PRIMARY key auto_increment; -- 方式三，修改表中的列为主键索引
desc lovess;

ALTER TABLE lovess add PRIMARY KEY (id);

drop TABLE if exists t_test_uK1;
CREATE TABLE t_test_uK1(
  id bigint primary key auto_increment,
  name varchar(20),
  UNIQUE(name)
);

2.唯一索引

方式一：直接在创建表的时候创建

drop table if exists t_test_uK;
CREATE TABLE t_test_uK(
  id bigint primary key auto_increment,
  name varchar(20) UNIQUE
  );
desc t_test_uK;

方式二，创建表时单独指定

drop TABLE if exists t_test_uK1;
CREATE TABLE t_test_uK1(
  id bigint primary key auto_increment,
  name varchar(20),
  UNIQUE(name)
);

方式三，修改表中的列

drop table if exists t_test_uK2;
CREATE TABLE t_test_uK2(
  id bigint primary key auto_increment,
  name varchar(20)
);

ALTER TABLE t_test_uK2 ADD UNIQUE (name);

3.普通索引

创建场景：一般是在工作中发现要多次使用某一项，就

⽅式⼀，创建表时指定索引列

drop table t_test_index;
create table t_test_index(
  id bigint PRIMARY KEY auto_increment,
  name varchar(20) UNIQUE,
  sno varchar(10),
  INDEX(sno)
);
desc t_test_index;

⽅式⼆，修改表中的列为普通索引

drop table t_test_index;
create table t_test_index(
  id bigint PRIMARY KEY auto_increment,
  name varchar(20) UNIQUE,
  sno varchar(10),
  INDEX(sno)
);
desc t_test_index;

⽅式三，单独创建索引并指定索引名（指定以知字段）推荐使用

create INDEX t_test_index1_sno ON t_test_index1(sno);

4.复合索引的使用

drop table if exists t_test_index3;
create table t_test_index3(
id BIGINT PRIMARY key auto_increment,
name varchar (20) UNIQUE,
sno VARCHAR(10),
class_id bigint
);
CREATE INDEX t_test_index3_sno_class_id on t_test_index3(sno,class_id);
desc t_test_index2;
show index from t_test_index3;