MySQL 索引详解
索引旨在提高数据库性能,通过特定数据结构组织内存中的数据以提升搜索效率。虽然查询速度可能成百上千倍提升,但写操作(插入、更新、删除)会增加 IO 开销。
详细讲解了 MySQL 索引的原理与应用。首先介绍了索引对查询性能的加速作用及写操作的代价。接着从硬件层面阐述了磁盘结构、IO 交互机制,以及软件层面的 Page、Buffer Pool 概念。核心部分解析了 B+ 树作为 InnoDB 索引结构的优势,包括其矮胖型特性、范围查找能力及聚簇与非聚簇索引的区别。最后总结了主键、唯一、普通及全文索引的创建与管理方法,并给出了索引设计的最佳实践原则。

索引旨在提高数据库性能,通过特定数据结构组织内存中的数据以提升搜索效率。虽然查询速度可能成百上千倍提升,但写操作(插入、更新、删除)会增加 IO 开销。
构建海量数据表进行测试。若未建立索引,查询特定记录耗时较长。例如查询员工编号为 998877 的员工,在无索引情况下耗时约 6.17 秒。在高并发场景下可能导致系统负载过高。
drop database if exists my_index;
create database if not exists my_index default character set utf8;
use my_index;
-- 创建存储过程添加海量数据
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 ;
delimiter $$
create procedure insert_emp(in start 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+i), rand_string(6), 'SALESMAN', 0001, curdate(), 2000, 400, rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;
call insert_emp(100001, 8000000);
解决方法:创建索引
alter table EMP add index(empno);
建立索引后再次查询,速度显著提升。
MySQL 数据存储于磁盘外设中。磁盘是机械设备,效率相对较低,IO 特征是性能优化的关键话题。
磁盘包含主轴马达和盘片,磁头进行寻址定位。数据保存在扇区中,默认大小为 512 字节。
MySQL InnoDB 引擎进行 IO 的基本单位是 16KB,称为 Page。
SHOW GLOBAL STATUS LIKE 'innodb_page_size';
MySQL 在内存中申请 Buffer Pool 空间用于缓存数据,减少磁盘 IO 次数。默认大小可通过配置调整。
乱序插入数据时,InnoDB 会自动排序。这是为了优化查询效率。
为解决多页查找效率问题,引入目录页(非叶子节点)和数据页(叶子节点),形成 B+ 树。
show keys from 表名;
show index from 表名;
desc 表名;
alter table 表名 drop primary key;
alter table 表名 drop index 索引名;
drop index 索引名 on 表名;

微信公众号「极客日志」,在微信中扫描左侧二维码关注。展示文案:极客日志 zeeklog
使用加密算法(如AES、TripleDES、Rabbit或RC4)加密和解密文本明文。 在线工具,加密/解密文本在线工具,online
在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL转CSV/JSON/XML在线工具,online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online