个人主页Cx330❄️个人专栏《C语言》《LeetCode刷题集》《数据结构-初阶》《C知识分享》《优选算法指南-必刷经典100题》《Linux操作系统》:从入门到入魔《Git深度解析》:版本管理实战全解 《Qt 极境架构》MySQL 核心技术与实战心向往之行必能Cx330的简介目录前言一. 为什么需要索引海量数据痛点索引的核心定义常见索引分类二. 实战演练海量数据检索测试2.1 创建测试表2.2 编写存储过程构建差异化数据2.3 无索引查询测试2.4 创建索引并再次测试三. 认识磁盘3.1 MySQL与存储3.2 磁盘随机访问 (Random Access) 与连续访问 (Sequential Access)四. 深入底层从磁盘 I/O 到 B 树4.1 磁盘与 MySQL 的交互单位Page3.2 为什么不用其他数据结构3.3 B 树的终极对决五. 存储引擎索引实现InnoDB vs MyISAM5.1 InnoDB 中的聚簇索引Clustered Index5.2 MyISAM 中的非聚簇索引Non-clustered Index5.3 聚簇索引Clustered IndexVS 非聚簇索引Non-clustered Index六. MySQL 索引管理实战6.1 创建索引6.2 查询索引6.3 删除索引6.4 玩转全文索引Fulltext Index七. 索引设计的黄金法则结语前言在 C/C 后端高并发系统开发中数据库往往是整个系统的性能瓶颈。要编写出高性能的后端服务仅仅精通 C 语法和网络模型是远远不够的还必须掌握数据库的底层优化。MySQL 索引作为“物美价廉”的性能优化神器不用我们加内存、改程序、调物理拓扑只需一条正确的CREATE INDEX就能将查询速度提升成百上千倍。但“天下没有免费的午餐”索引的引入势必会带来磁盘空间占用以及写操作插入、更新、删除时的 I/O 负担。本文将结合底层原理带你彻彻底底搞懂 MySQL 索引的“前世今生”。一. 为什么需要索引海量数据痛点在没有索引的情况下当我们在海量数据表中查询一条记录时数据库只能进行全表扫描Table Scan。这意味着需要将磁盘上的整张表数据逐行加载到内存中进行比对其时间复杂度为线性阶 O(N)。在面对数百万、甚至千万级数据量时这种查询方式会导致严重的磁盘 I/O 阻塞响应时间往往达到数秒甚至数分钟这在现代后端服务中是完全不可接受的。为了解决这一痛点MySQL 引入了索引Index机制。索引的核心定义索引是帮助 MySQL高效获取数据的排好序的数据结构。它的本质是通过空间换时间将无序的数据通过特定的结构组织起来使查询的时间复杂度降低到对数阶 O(log N)。常见索引分类主键索引Primary Key表中唯一标识一行数据的索引不允许重复且不能为 NULL。唯一索引Unique Index限制列值必须唯一但允许为 NULL。普通索引Normal Index最基本的索引没有任何限制。全文索引Fulltext Index用于解决中子文或大文本的模糊匹配检索问题。二. 实战演练海量数据检索测试为了直观感受索引的威力我们模拟构建一个包含8,000,000八百万条记录的海量数据表对比有无索引的查询性能。2.1 创建测试表CREATE DATABASE IF NOT EXISTS index_demo; USE index_demo; -- 创建海量数据表 CREATE TABLE EMP ( empno INT UNSIGNED DEFAULT 0, ename VARCHAR(20) DEFAULT , job VARCHAR(9) DEFAULT , mgr INT UNSIGNED DEFAULT 0, hiredate DATE NOT NULL, sal DECIMAL(7,2) DEFAULT 0.00, comm DECIMAL(7,2) DEFAULT 0.00, deptno MEDIUMINT UNSIGNED DEFAULT 0 ) ENGINEInnoDB DEFAULT CHARSETutf8;2.2 编写存储过程构建差异化数据由于海量数据必须具备差异性我们利用存储过程和随机生成函数来进行数据填充。-- 1. 产生随机字符串函数 DELIMITER $$ CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) BEGIN DECLARE chars_str VARCHAR(100) DEFAULT abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ; DECLARE return_str VARCHAR(255) DEFAULT ; DECLARE i INT DEFAULT 0; WHILE i n DO SET return_str CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1 RAND() * 52), 1)); SET i i 1; END WHILE; RETURN return_str; END $$ DELIMITER ; -- 2. 产生随机数字函数 DELIMITER $$ CREATE FUNCTION rand_num() RETURNS INT(5) BEGIN DECLARE i INT DEFAULT 0; SET i FLOOR(10 RAND() * 500); RETURN i; END $$ DELIMITER ; -- 3. 核心数据插入存储过程 DELIMITER $$ CREATE PROCEDURE insert_emp(IN start_num INT(10), IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; -- 默认关闭自动提交批量提交以提高插入效率 SET autocommit 0; REPEAT SET i i 1; INSERT INTO EMP VALUES ( (start_num i), rand_string(6), SALESMAN, 0001, CURDATE(), 2000.00, 400.00, rand_num() ); UNTIL i max_num END REPEAT; COMMIT; -- 统一提交 END $$ DELIMITER ;调用存储过程插入 800 万条数据该过程需要运行几分钟-- 插入 8,000,000 条记录 CALL insert_emp(100001, 8000000);2.3 无索引查询测试在未加任何索引的情况下查询员工编号为998877的员工信息SELECT * FROM EMP WHERE empno 998877;执行耗时约 6秒 ~ 7.0 秒。原因分析没有索引MySQL 执行了全表扫描type: ALL逐行读取磁盘数据进行比对I/O 开销极大。2.4 创建索引并再次测试我们在empno列上建立一个普通索引ALTER TABLE EMP ADD INDEX (empno);再次执行相同的查询SELECT * FROM EMP WHERE empno 4500123;执行耗时约0.00 秒极其微秒级原因分析此时 MySQL 直接通过索引树进行二分查找仅进行了几次磁盘 I/O 交互便定位到了目标行。三. 认识磁盘3.1 MySQL与存储MySQL给用户提供存储服务而存储的都是数据数据在磁盘这个外设当中。磁盘是计算机中的一个机 械设备相比于计算机其他电子元件磁盘效率是比较低的在加上IO本身的特征可以知道如何提交效率是 MySQL的一个重要话题。先来研究一下磁盘扇区数据库文件本质其实就是保存在磁盘的盘片当中。也就是上面的一个个小格子中就是我们经常所说的扇区。当然数据库文件很大也很多一定需要占据多个扇区。题外话从上图可以看出来在半径方向上距离圆心越近扇区越小距离圆心越远扇区越大那么所有扇区都是默认 512 字节吗目前是的我们也这样认为。因为保证一个扇区多大是由比特位密度决定的。不过最新的磁盘技术已经慢慢的让扇区大小不同了不过我们现在暂时不考虑。我们在使用 Linux所看到的大部分目录或者文件其实就是保存在硬盘当中的。(当然有一些内存文件系统如procsys之类我们不考虑)所以最基本的找到一个文件的全部本质就是在磁盘找到所有保存文件的扇区。而我们能够定位任何一个扇区那么便能找到所有扇区因为查找方式是一样的。柱面 (磁道)多盘磁盘每盘都是双面大小完全相等。那么同半径的磁道整体上便构成了一个柱面每个盘面都有一个磁头那么磁头和盘面的对应关系便是 1 对 1 的所以我们只需要知道磁头 (Heads)、柱面 (Cylinder)(等价于磁道)、扇区 (Sector) 对应的编号。即可在磁盘上定位所要访问的扇区。这种磁盘数据定位方式叫做 CHS。不过实际系统软件使用的并不是 CHS但是硬件是而是 LBA一种线性地址可以想象成虚拟地址与物理地址。系统将 LBA 地址最后会转化成为 CHS交给磁盘去进行数据读取。不过我们现在不关心转化细节知道这个东西让我们逻辑自洽起来即可。结论我们现在已经能够在硬件层面定位任何一个基本数据块了 (扇区)。那么在系统软件上就直接按照扇区 (512 字节部分 4096 字节)进行 IO 交互吗不是如果操作系统直接使用硬件提供的数据大小进行交互那么系统的 IO 代码就和硬件强相关换言之如果硬件发生变化系统必须跟着变化从目前来看单次 IO 512 字节还是太小了。IO 单位小意味着读取同样的数据内容需要进行多次磁盘访问会带来效率的降低。之前学习文件系统就是在磁盘的基本结构下建立的文件系统读取基本单位就不是扇区而是数据块。故系统读取磁盘是以块为单位的基本单位是 4KB。3.2 磁盘随机访问 (Random Access) 与连续访问 (Sequential Access)随机访问本次 IO 所给出的扇区地址和上次 IO 给出扇区地址不连续这样的话磁头在两次 IO 操作之间需要作比较大的移动动作才能重新开始读 / 写数据。 连续访问如果当次 IO 给出的扇区地址与上次 IO 结束的扇区地址是连续的那磁头就能很快的开始这次 IO 操作这样的多个 IO 操作称为连续访问。因此尽管相邻的两次 IO 操作在同一时刻发出但如果它们的请求的扇区地址相差很大的话也只能称为随机访问而非连续访问。磁盘是通过机械运动进行寻址的连续访问不需要过多的定位故效率比较高。四. 深入底层从磁盘 I/O 到 B 树作为 C 后端程序员我们不能只停留在表面的 SQL 语句必须探究底层的硬件交互与数据结构。4.1 磁盘与 MySQL 的交互单位PageMySQL 作为关系型数据库其数据最终是持久化在磁盘上的。然而系统从磁盘读取数据的最小单位并非“字节”而是以“块/页”为单位。操作系统的页通常为 4KB。MySQL InnoDB 存储引擎的页Page默认大小为16KB。------------------------------------------------------------- | InnoDB Page | | ------------------ ----------------- ------------ | | | File Header | | Page Header | | Infimum | | | ------------------ ----------------- ------------ | | | User Records | | | | Row 1 (Directory) - Row 2 - Row 3 - Row 4 ... | | | ------------------------------------------------------- | | | Page Directory | | File Trailer | | | | ------------------ ----------------- | | -------------------------------------------------------------MySQL 的 I/O 交互都是以 16KB 的 Page 为基本单位的。即使你只修改或查询 1 字节的数据MySQL 也会将整页16KB的数据加载到内存 Buffer Pool 中。3.2 为什么不用其他数据结构在设计索引时为什么不选择常见的二叉树、红黑树、Hash 表或 B 树数据结构缺点与局限性Hash 表虽然等值查询时间复杂度为 O(1)但不支持范围查询如、、between以及排序会退化为全表扫描。二叉搜索树 (BST)容易发生倾斜在顺序插入时会退化成单链表查询时间复杂度退化为 $O(N)$树的高度极高。红黑树 (RBT)虽然能自动平衡但在海量数据下树的高度Height依然不可控。每一次向下搜索父子节点都可能是一次磁盘 I/O。B 树 (B-Tree)每个节点既存储索引又存储实际的 Data 记录。这导致单个 Page16KB能容纳的索引数量大幅减少。为了容纳相同数量的数据树的高度会变得很高增加 I/O 次数。3.3 B 树的终极对决MySQL 最终选择了B 树B Tree作为其核心索引结构。它在 B 树的基础上做出了极具艺术性的改良------------------- | Page (Level 1) | -- 非叶子节点只存索引和指针 | [ 10 | 20 | 30 ]| ---------------- | | | ------------ | ------------ v v v --------------- --------------- --------------- | Page (Level 2)| | Page (Level 2)| | Page (Level 2)| -- 叶子节点存储完整的数据行 | [10] [12] [15]| | [20] [22] [25]| | [30] [32] [35]| --------------- --------------- --------------- (Double Linked List: ---------- ---------- )非叶子节点只存储键值Key和页面指针不存储实际行数据Data。因此一个 16KB 的非叶子节点 Page 可以存放上千个索引项。所有实际的数据记录全部存储在叶子节点Leaf Node。这确保了整棵树的高度极矮通常 800 万数据也只需要 3~4 层即最多进行 3~4 次磁盘 I/O 即可定位数据。叶子节点之间通过双向链表相连这使得范围查询、排序和分组操作变得异常高效。只需要定位到边界点即可通过链表进行顺序双向遍历。五. 存储引擎索引实现InnoDB vs MyISAMMySQL 常见的存储引擎有 InnoDB 和 MyISAM它们对 B 树索引的底层实现有着本质的区别。[ 查询键值: 18 ] | ---------------------------- | | v v 【InnoDB (聚簇索引)】 【MyISAM (非聚簇索引)】 --------------------- --------------------- | Index Data | | Index Address | | (索引与数据合二为一) | | (索引与数据文件分离) | --------------------- --------------------- | Key: 18 | | Key: 18 | | Data: {18, Jack...} | | Addr: 0x7fff1234 -- --------------------- --------------------- | v ---------------------- | MYD Data File | | Row: {18, Jack...} | ----------------------5.1 InnoDB 中的聚簇索引Clustered Index特点索引和数据是紧密结合的B 树的叶子节点直接存放了整行数据的完整记录。主键索引即聚簇索引如果表定义了主键则主键索引就是聚簇索引若无主键则会选择第一个非空唯一索引若依然没有InnoDB 会自动生成一个隐式的row_id。辅助索引Secondary Index / 非聚簇索引辅助索引的叶子节点并不存放完整行数据而是存放主键的值。注通过辅助索引查询时首先在辅助索引树中找到主键再拿着主键去主键索引树上查找完整数据。这个过程叫做回表Table Lookup。5.2 MyISAM 中的非聚簇索引Non-clustered Index特点索引和数据完全分离。文件构成.MYI文件存储索引树。.MYD文件存储实际的行数据。工作原理无论是主键索引还是普通索引MyISAM 的 B 树叶子节点中存放的都不是完整数据也不是主键值而是数据行在.MYD文件中的物理磁盘地址Address 指针。5.3 聚簇索引Clustered IndexVS 非聚簇索引Non-clustered Index六. MySQL 索引管理实战在开发中我们需要对索引进行灵活的创建、查询和删除。以下是完整的操作指令总结6.1 创建索引-- 方式 1创建表时指定索引 CREATE TABLE user10 ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), email VARCHAR(50), UNIQUE KEY idx_email (email), -- 唯一索引 INDEX idx_name (name) -- 普通索引 ); -- 方式 2在已有表上追加索引 ALTER TABLE user10 ADD INDEX idx_name_email (name, email); -- 复合索引 -- 方式 3直接创建索引 CREATE INDEX idx_name ON user10 (name);6.2 查询索引查询表中已存在的索引信息-- 方法 1最常用、最详尽 SHOW KEYS FROM user10\G -- 方法 2作用相同 SHOW INDEX FROM user10; -- 方法 3查看表结构信息较为简略 DESC user10;6.3 删除索引-- 方法 1删除主键索引 ALTER TABLE user10 DROP PRIMARY KEY; -- 方法 2删除普通索引/唯一索引/复合索引 ALTER TABLE user10 DROP INDEX idx_name; -- 方法 3直接 DROP INDEX 语法 DROP INDEX idx_email ON user10;6.4 玩转全文索引Fulltext Index普通的LIKE %database%查询会导致索引失效而进行全表扫描。在海量文本检索时必须使用全文索引。-- 创建包含全文索引的表 CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title, body) -- 建立全文索引 ) ENGINEInnoDB; -- 模拟插入测试数据 INSERT INTO articles (title, body) VALUES (MySQL Tutorial, DBMS stands for Database Management System...), (How To Use MySQL Well, After you apply fulltext index, you can query...), (Optimizing Databases, Active your query speed using index structures...); -- 使用 MATCH ... AGAINST 语法进行全文检索 SELECT * FROM articles WHERE MATCH (title, body) AGAINST (database);注意全文索引在使用时需要用特定的MATCH (列名) AGAINST (关键字)语法。使用EXPLAIN分析上述语句可以清晰地看到key: title代表成功命中了全文索引而不再是全表扫描。七. 索引设计的黄金法则索引虽好但不可贪多。以下是我们在后端架构设计中必须遵循的索引建立原则高选择性列优先选择性COUNT(DISTINCT column) / COUNT(*)越接近 1 的列越适合建索引。例如身份证号、手机号、邮箱适合建索引而性别、状态字段只有0和1由于重复率极高绝对不适合单独建索引。最左前缀法则Most Left Prefix对于复合索引多列联合索引MySQL 从左到右进行匹配。查询条件中必须包含复合索引的最左侧列否则索引将会失效。例如建立复合索引(a, b, c)查询WHERE a 1 AND b 2可以用索引而直接查询WHERE b 2 AND c 3则无法使用索引。覆盖索引优化Covering Index尽量让查询的列只包含在索引树的节点中这样可以避免“回表”操作极大地提升查询效率。避免在索引列上做运算如WHERE YEAR(add_time) 2026会导致add_time上的索引完全失效。应该改写为WHERE add_time 2026-01-01。结语在 C 编写的高性能后端服务中数据库的高效吞吐是第一要义。透彻理解 MySQL 索引在磁盘层面的 16KB Page 交互掌握 B 树的底层逻辑以及合理使用 InnoDB 聚簇索引与覆盖索引能让我们在面对海量数据和高并发场景时游刃有余。如果你觉得本文对你有所启发请不吝点赞、收藏、关注我们下期再见