mysql索引

01. 索引基础

1.1 索引是什么？

索引就是"数据的目录" ， 想象一下你去图书馆找书，没有目录的话你得一本本翻，有了目录就能直接找到想要的书。

• 没有索引：数据库要扫描整张表，从头开始依次匹配数据知道找到为止
• 有索引：直接定位到数据位置，效率更高

假设有一个 users 表存储了 100 万条用户数据：

无索引： 时全表扫描不会提前终止，无论目标数据位置多靠前，都会扫描完所有记录。

SELECT * FROM users WHERE username = 'bob';

例如在第二行已经找到匹配的数据，仍会扫描到最后一行再返回数据。这就导致了以下问题：

• 效率低 ：全部扫描
• I/O 开销大 ：需从磁盘读取所有数据

有索引： 在username列建立索引

CREATE INDEX idx_username ON users(username);

这样数据库会使用B+树索引快速定位bob，时间复杂度为O(log N)。

1.2 为什么需要索引？

没有索引时，数据库必须逐行扫描（全表扫描），如同在图书馆从第一本书开始逐个查找，数据量越大查询越慢。

有了索引，数据库能快速定位数据位置，查询效率可提升数十倍甚至百倍。

数据量越大，索引的价值越明显——百万级或千万级表中，有索引的查询可能仅需几毫秒，而无索引时可能需要数秒甚至更久。

但索引并非完美，它通过占用额外存储空间和降低写入性能来换取查询速度的提升，需合理权衡。

1.3 索引的常见类型

常见索引类型包括：

• 普通索引：基础索引，无限制，允许重复值和空值。
• 唯一索引：索引列值必须唯一，允许空值。
• 主键索引：特殊的唯一索引，不允许空值，每表仅一个。
• 联合索引：多列组合索引，遵循最左前缀原则。

此外还有全文索引（适用于文本搜索）、空间索引（适用于地理数据）等特殊类型。 不同索引适用于不同场景，例如用户名适合唯一索引，文章内容适合全文索引。

1.4 认识磁盘

扇区:
数据库文件，本质其实就是保存在磁盘的盘片当中。也就是上面的一个个小格子中，就是我们经常所说的扇区。当然，数据库文件很大，也很多，一定需要占据多个扇区。

当我们在使用Linux，所看到的大部分目录或者文件，其实就是保存在硬盘当中的。(当然，有一些内存文件系统，如：sys 之类，我们不考虑)

#数据库文件，本质其实就是保存在磁盘的盘片当中，就是一个一个的文件
[root@VM-0-3-centos ~]# ls /var/lib/mysql -l   #我们目前MySQL中的文件

所以，最基本的，找到一个文件的全部，本质，就是在磁盘找到所有保存文件的扇区。而我们能够定位任何一个扇区，那么便能找到所有扇区，因为查找方式是一样的。

• 柱面(磁道): 多盘磁盘，每盘都是双面，大小完全相等。那么同半径的磁道，整体上便构成了一个柱面
• 每个盘面都有一个磁头，那么磁头和盘面的对应关系便是1对1的
• 所以，我们只需要知道，磁头（Heads）、柱面(Cylinder)(等价于磁道)、扇区(Sector)对应的编号。即可在磁盘上定位所要访问的扇区。这种磁盘数据定位方式叫做的并不是CHS（但是硬件是），而是LBA ，一种线性地址，可以想象成虚拟地址与物理地址。系统将LBA地址最后会转化成为CHS ，交给磁盘去进行数据读取。不过，我们现在不关心转化细节，知道这个东西，让我们逻辑自洽起来即可。

我们现在已经能够在硬件层面定位，任何一个基本数据块了(扇区)。但在系统软件上，就直接按照系统读取磁盘，是以块为单位的，基本单位是4KB 。

磁盘访问方式对比：

随机访问：磁头需要大幅移动（寻址）到不连续的扇区，效率较低。
连续访问：磁头直接读取相邻扇区，无需额外寻址，效率较高。

关键区别在于扇区地址是否连续，即使两次IO同时发出，地址相差较大仍属于随机访问。由于磁盘依赖机械寻址，连续访问的性能优势更明显。

1.5 MySQL与磁盘交互基本单位

而MySQL作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的I/O场景，所以，为了提高基本的I/O效率，MySQL进行I/O的基本单位是16KB (后面统一使用)

mysql> SHOW GLOBAL STATUS LIKE 'innodb_page_size';
 +------------------+-------+
 | Variable_name    | Value |
 +------------------+-------+
 | Innodb_page_size | 16384 |-- 16*1024=16384
 +------------------+-------+
 1 row in set (0.01 sec)

也就是说，磁盘这个硬件设备的基本单位是 512字节，而MvSOL,InnoDB引擎 使用16KB进行I/0交互,即，MySQL和磁盘进行数据交互的基本单位是16KB。这个基本数据单元，在MySQL这里叫做page(注意和系统的page区分)。

数据交互机制：

1. 数据存储与操作
   • 数据以 Page 为单位存储在磁盘
   • 所有 CURD 操作需通过 CPU 计算定位数据位置，因此数据需先加载到内存
2. 内存与磁盘交互
   • 通过 Buffer Pool 管理内存缓存
   • 操作先在内存完成，再按策略刷新到磁盘
   • 每次 I/O 的基本单位是 Page

磁盘 I/O 相比内存操作速度慢几个数量级，尽量减少磁盘 I/O 次数

02. 索引理解

2.1 理解单个页面

MySQL中要管理很多数据表文件，而要管理好这些文件,我们目前可以简单理解成一个个独立文件是由一个或者多个Page构成的。

不同的Page，在MySQL中，都是16KB，使用prev和next构成双向链表因为有主键的问题，MySQL会默认按照主键给我们的数据进行排序，从上面的Page内数据记录可以看出，数据是有序且彼此关联的。

2.2 理解多个页面

MySQL的页式存储通过整页加载减少I/O，但链表结构导致查询必须线性遍历（O(N)），千万级数据时效率极低。需要引入索引优化查找效率。

类比OS二级页表:

• 类似OS通过页表加速虚拟地址转换：
  • 一级页表定位页目录
  • 二级页表定位物理页框

但是由于MySQL缺乏类似"页目录"的快速定位机制，需要引入索引结构（如B+树）实现：

• 快速页定位（类似页目录）
• 页内数据有序存储（避免全页扫描）

2.2.1 页目录

当我们在看数学书时，如果我们要看<二次函数>找到该章节有两种做法：

1. 从头逐页的向后翻，直到找到目标内容
2. 通过书提供的目录，发现指针章节在234页(假设)，那么我们便直接翻到234页。同时，查找目录的方案，可以顺序找，不过因为目录肯定少，所以可以快速提高定位。

本质上，书中的目录，是多花了纸张的，但是却提高了效率，一种“空间换时间的做法”。

2.2.2 单页情况

当我们想要查找id=4记录时，直接通过目录2[3]，直接进行定位新的起始位置，减少查询次数。

2.2.3 多页情况

MySQL中每一页的大小只有16KB，单个Page大小固定，所以随着数据量不断增大，16KB 不可能存下所有的数据，那么必定会有多个页来存储数据。在单表数据不断被插入的情况下，MySQL会在容量不足的时候，自动开辟新的Page来保存新的数据，然后通过指针的方式，将所有的Page组织起来。但是这是一个理想结构，因为无法保证新插入数据后仍然保持有序。且在页面切换时仍然需要大量I/O操作。

故采用我们之前的思路，给Page也带上目录。

使用目录页来管理页目录，目录页中存放指向的那一页中最小的数据。这样就可通过比较，找到该访问那个Page，进而通过指针，找到下一个Page。目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址。

为了了解从哪里开始查找，故加上一个目录页，这变成了大名鼎鼎的B+树。

这样我们的表user构建完了主键索引。随便找一个·id=x·我们发现，现在查找的Page数一定减少了，也就意味着IO次数减少了，那么效率也就提高了。

为什么不采用二叉树或者AVL树？因为树的高度高的情况下I/O次数很多。不采用B树的原因是因为非页节点存放了数据和目录页的地址，同样会有上述不好影响。

总结：

• Page分为目录页和数据页。目录页只放各个下级Page的最小键值。
• 查找的时候，自定向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数

B树

B+树

2.3 聚簇索引 VS 非聚簇索引

MySQL的InnoDB索引数据结构是B+树，主键索引叶子节点的值存储的就是MySQL的数据行，普通索引的叶子节点的值存储的是主键值，这是了解聚簇索引和非聚簇索引的前提。

• 聚簇索引： 找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引，修改聚簇索引其实就是修改主键。
• 非聚簇索引： 索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。

2.3.1 二者区别

1. 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个
2. 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
3. 聚集索引：物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。
   非聚集索引：物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。
4. 索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

2.3.2 优缺点

聚集索引插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入），查询数据比非聚集数据的速度快。

2.3.3 回表查询

假设一个用户表：

CREATE TABLE users (
    id INT PRIMARY KEY,       -- 聚簇索引  一般情况下主键默认
    name VARCHAR(100),        -- 非聚簇索引
    age INT,
    INDEX idx_name (name)
);

执行操作如下：

SELECT * FROM users WHERE name = 'K';

查询过程：

1. 首先根据idx_name索引找到"name=K"的记录，获取对应的id值
2. 然后根据id值到主键索引查找用户的完整记录
3. 返回完整的用户数据

2.3.4 MyISAM(非聚簇索引) 与InnoDB的区别

MyISAM和InnoDB的主要区别包括：

• MyISAM 不支持事务
• MyISAM 表级锁（不是行级锁）
• MyISAM 适合读多写少的场景

MyISAM不支持事务，而InnoDB支持；

MyISAM只有表级锁，InnoDB支持行级锁；

MyISAM不支持外键，InnoDB支持；

MyISAM的崩溃恢复能力较弱，InnoDB更可靠；

MyISAM的全文索引较早出现，但现在InnoDB也支持了。

选择存储引擎时，如果不需要事务且读多写少，可以考虑MyISAM，否则应该选择InnoDB。

03. 索引的操作

3.1 创建主键索引

--在创建表的时候，直接在字段名后指定 primary key
create table userl(id int primary key ,name varchar(30)

--在创建表的最后，指定某列或某几列为主键索引
create tableuser2(id int,name varchar(30),primary key(id));

create table user3(id int,name varchar(30));
--创建表以后再添加主键
alter table user3 add primary key(id);

• 主键索引的特点：
  • 一个表中，最多有一个主键索引，当然可以使符合主键
  • 主键索引的效率高（主键不可重复）
  • 创建主键索引的列，它的值不能为null，且不能重复
  • 主键索引的列基本上是int

3.2 创建唯一索引

-- 在表定义时，在某列后直接指定unique唯一属性。	
	create table user4(id int primary key, name varchar(30) unique);

-- 创建表时，在表的后面指定某列或某几列为unique
	create table user5(id int primary key, name varchar(30), unique(name));
 
-- 通过ALTER TABLE添加主键
	create table user6(id int primary key, name varchar(30)）；
	alter table user6 add unique(name);

• 唯一索引的特点：
  • 一个表中，可以有多个唯一索引
  • 查询效率高
  • 如果在某一列建立唯一索引，必须保证这列不能有重复数据
  • 如果一个唯一索引上指定not null，等价于主键索引

3.3 创建普通索引

create table user8(id int primary key,
	name varchar(20)
	email varchar(30)
	index(name)--在表的定义最后，指定某列为索引

create table user9(id int primary key, name varchar(20),emai]varchar(30))
alter table user9 add index(name);--创建完表以后指定某列为普通索引

create table user10(id int primary key,name varchar(20).emai7varchar(30));
-- 创建一个索引名为 idx_name 的索引
create index idx name on user10(name);

• 普通索引的特点：
  • 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
  • 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引