一、索引是什么？为什么这么重要？

索引就像字典的目录

想象一下，你要在一本1000页的字典里找"程序员"这个词，你会怎么做？

  • 没有目录：从第1页开始一页一页翻，可能要翻500页才能找到
  • 有目录：直接翻到目录，找到"程"字开头的词在第300页，瞬间就找到了

数据库索引就是这样的"目录"，它能帮我们快速定位数据的位置。

索引的神奇效果

场景无索引有索引性能提升
100万条数据查询扫描100万行扫描3-4行提升25万倍+
用户登录验证50ms1ms提升50倍
订单查询200ms5ms提升40倍

真实的例子

-- 没有索引的查询（慢得要命）
SELECT * FROM users WHERE email = 'john@example.com';
-- 执行时间：1.2秒（扫描了50万行数据）-- 给email字段添加索引后
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = 'john@example.com';
-- 执行时间：0.01秒（直接定位到1行数据）

看到了吗？同样的查询，性能差了120倍！

二、索引的底层原理：B+树的魔法

什么是B+树？

 

B+树的查找过程

让我们用一个简单例子来理解：

-- 假设我们要查找 id = 75 的用户
SELECT * FROM users WHERE id = 75;

查找步骤：

  1. 第1步：从根节点开始，75在50-100之间，走中间分支
  2. 第2步：到达叶子节点，找到id=75的数据位置
  3. 第3步：根据位置直接获取完整的用户数据

整个过程只需要3次磁盘IO，而全表扫描可能需要几万次！

为什么B+树这么快？

特点优势实际效果
多路平衡树的高度很低减少磁盘访问次数
叶子节点连接支持范围查询ORDER BY、分页查询快
只在叶子存数据内部节点小更多索引数据放入内存

三、MySQL索引的类型详解

1. 主键索引（Primary Key）

主键索引是最特殊的索引，它就像身份证号码一样：

-- 创建主键索引
CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,  -- 自动创建主键索引name VARCHAR(50),email VARCHAR(100)
);-- 主键查询超级快
SELECT * FROM users WHERE id = 12345;  -- 毫秒级响应

主键索引的特点：

  • 唯一且不能为空
  • 一个表只能有一个主键
  • 查询性能最好
  • 数据按主键顺序存储

2. 唯一索引（Unique Index）

-- 给邮箱添加唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);-- 插入重复邮箱会报错
INSERT INTO users(name, email) VALUES('张三', 'test@qq.com');  -- 成功
INSERT INTO users(name, email) VALUES('李四', 'test@qq.com');  -- 失败，邮箱重复

3. 普通索引（Normal Index）

最常用的索引类型：

-- 给姓名添加普通索引
CREATE INDEX idx_name ON users(name);-- 快速查找用户
SELECT * FROM users WHERE name = '张三';

4. 复合索引（Composite Index）

多个字段组合的索引，功能更强大：

-- 创建复合索引
CREATE INDEX idx_name_age_city ON users(name, age, city);-- 这些查询都能用到索引
SELECT * FROM users WHERE name = '张三';                          -- ✓ 能用到
SELECT * FROM users WHERE name = '张三' AND age = 25;             -- ✓ 能用到  
SELECT * FROM users WHERE name = '张三' AND age = 25 AND city = '北京';  -- ✓ 能用到
SELECT * FROM users WHERE age = 25;                               -- ✗ 用不到
SELECT * FROM users WHERE city = '北京';                          -- ✗ 用不到

复合索引的使用规则（最左前缀原则）：

-- 索引：(name, age, city)
-- 可以理解为创建了三个索引：
-- 1. (name)
-- 2. (name, age) 
-- 3. (name, age, city)

四、索引设计的黄金法则

法则1：为WHERE条件添加索引

-- 经常这样查询
SELECT * FROM orders WHERE user_id = 123;
SELECT * FROM orders WHERE status = 'paid';
SELECT * FROM orders WHERE create_time > '2024-01-01';-- 就应该创建这些索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_create_time ON orders(create_time);

法则2：为ORDER BY字段添加索引

-- 经常按创建时间排序
SELECT * FROM articles ORDER BY create_time DESC LIMIT 10;-- 创建索引让排序飞快
CREATE INDEX idx_create_time ON articles(create_time);

法则3：复合索引的顺序很关键

-- 如果经常这样查询
SELECT * FROM users WHERE city = '北京' AND age > 25 ORDER BY create_time;-- 索引字段顺序应该是：过滤性强的字段在前
CREATE INDEX idx_city_age_create_time ON users(city, age, create_time);

法则4：覆盖索引让查询更快

-- 如果只需要这几个字段
SELECT id, name, email FROM users WHERE age = 25;-- 创建覆盖索引，连回表都省了
CREATE INDEX idx_age_name_email ON users(age, name, email);

五、实战案例：订单系统优化

场景描述

假设我们有一个订单表：

CREATE TABLE orders (id BIGINT PRIMARY KEY AUTO_INCREMENT,user_id BIGINT NOT NULL,order_no VARCHAR(50) NOT NULL,status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled'),total_amount DECIMAL(10,2),create_time DATETIME,update_time DATETIME
);

常见查询场景及优化

场景1：用户查看自己的订单
-- 原始查询（慢）
SELECT * FROM orders WHERE user_id = 12345 ORDER BY create_time DESC;-- 优化方案
CREATE INDEX idx_user_id_create_time ON orders(user_id, create_time);

优化效果：

  • 优化前：扫描50万行数据，耗时800ms
  • 优化后：直接定位用户订单，耗时5ms
场景2：订单状态查询
-- 查询待支付订单
SELECT * FROM orders WHERE status = 'pending' AND create_time > '2024-01-01';-- 优化方案
CREATE INDEX idx_status_create_time ON orders(status, create_time);
场景3：订单号精确查找
-- 通过订单号查找
SELECT * FROM orders WHERE order_no = 'ORD20240101001';-- 优化方案
CREATE UNIQUE INDEX idx_order_no ON orders(order_no);

优化前后对比

查询类型优化前耗时优化后耗时提升倍数
用户订单查询800ms5ms160倍
状态筛选1200ms8ms150倍
订单号查找600ms2ms300倍

六、索引的注意事项：别踩这些坑

坑1：不要给小表建索引

-- 错误示例：给只有100行数据的字典表建索引
CREATE TABLE dict_status (id INT PRIMARY KEY,name VARCHAR(20)
);
-- 这个表数据量太小，建索引反而浪费空间

坑2：不要在区分度低的字段建索引

-- 错误示例：性别字段只有男/女两个值
CREATE INDEX idx_gender ON users(gender);  -- 没意义，区分度太低

坑3：索引不是越多越好

-- 错误示例：给每个字段都建索引
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_city ON users(city);
CREATE INDEX idx_phone ON users(phone);
CREATE INDEX idx_email ON users(email);
-- 太多索引会严重影响INSERT/UPDATE性能

坑4：复合索引的字段顺序

-- 错误示例
CREATE INDEX idx_age_name ON users(age, name);
SELECT * FROM users WHERE name = '张三';  -- 用不到索引-- 正确示例
CREATE INDEX idx_name_age ON users(name, age);
SELECT * FROM users WHERE name = '张三';  -- 能用到索引

七、索引优化实战技巧

技巧1：使用EXPLAIN分析查询

-- 分析查询是否使用了索引
EXPLAIN SELECT * FROM orders WHERE user_id = 12345;

EXPLAIN结果解读：

字段说明好的值坏的值
type访问类型const, eq_ref, refALL, index
key使用的索引有具体索引名NULL
rows扫描行数越少越好很大的数字
Extra额外信息Using indexUsing filesort

技巧2：监控慢查询

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒的查询记录下来-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log_file';

技巧3：定期分析表统计信息

-- 更新表的统计信息，让优化器做出更好的选择
ANALYZE TABLE orders;

技巧4：合理使用前缀索引

-- 对于很长的字符串字段，使用前缀索引
CREATE INDEX idx_title_prefix ON articles(title(20));  -- 只索引前20个字符

八、高级索引特性

1. 函数索引（MySQL 8.0+）

-- 给计算字段创建索引
ALTER TABLE orders ADD INDEX idx_year ((YEAR(create_time)));-- 这个查询能用到索引
SELECT * FROM orders WHERE YEAR(create_time) = 2024;

2. 降序索引（MySQL 8.0+）

-- 创建降序索引
CREATE INDEX idx_create_time_desc ON orders(create_time DESC);-- 降序排序更快
SELECT * FROM orders ORDER BY create_time DESC LIMIT 10;

3. 不可见索引

-- 创建不可见索引（用于测试）
CREATE INDEX idx_test ON orders(status) INVISIBLE;-- 测试性能后再设为可见
ALTER INDEX idx_test VISIBLE;

九、索引维护：让索引保持最佳状态

定期检查索引使用情况

-- 查看索引使用统计
SELECT schema_name,table_name,index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE schema_name = 'your_database';

删除无用索引

-- 找出从未使用的索引
SELECT t.table_schema,t.table_name,t.index_name
FROM information_schema.statistics t
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage pON t.table_schema = p.object_schemaAND t.table_name = p.object_nameAND t.index_name = p.index_name
WHERE p.index_name IS NULLAND t.table_schema = 'your_database'AND t.index_name != 'PRIMARY';

重建碎片化的索引

-- 检查索引碎片化程度
SHOW TABLE STATUS WHERE name = 'orders';-- 重建索引
ALTER TABLE orders ENGINE=InnoDB;

十、实际项目中的索引策略

电商系统索引设计

-- 商品表
CREATE TABLE products (id BIGINT PRIMARY KEY,category_id INT,name VARCHAR(200),price DECIMAL(10,2),stock INT,status TINYINT,create_time DATETIME,-- 核心索引INDEX idx_category_status_price (category_id, status, price),INDEX idx_name (name),INDEX idx_create_time (create_time)
);-- 订单表
CREATE TABLE orders (id BIGINT PRIMARY KEY,user_id BIGINT,status TINYINT,total_amount DECIMAL(10,2),create_time DATETIME,-- 核心索引INDEX idx_user_id_create_time (user_id, create_time),INDEX idx_status_create_time (status, create_time)
);

社交系统索引设计

-- 用户关注表
CREATE TABLE user_follows (id BIGINT PRIMARY KEY,follower_id BIGINT,    -- 关注者following_id BIGINT,   -- 被关注者create_time DATETIME,-- 核心索引INDEX idx_follower_id (follower_id),        -- 查询我关注的人INDEX idx_following_id (following_id),      -- 查询关注我的人UNIQUE KEY uk_follow (follower_id, following_id)  -- 防止重复关注
);

十一、性能测试与优化案例

案例1：用户登录优化

场景： 用户登录验证

-- 优化前的查询
SELECT id, password_hash FROM users WHERE email = 'user@example.com';-- 性能测试结果
-- 数据量：100万用户
-- 查询时间：平均 850ms
-- 扫描行数：平均 50万行

优化方案：

-- 1. 创建邮箱唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);-- 2. 创建覆盖索引（避免回表）
CREATE INDEX idx_email_password ON users(email, password_hash);

优化效果：

指标优化前优化后提升
查询时间850ms2ms425倍
扫描行数50万行1行50万倍
CPU使用率85%5%17倍

案例2：分页查询优化

场景： 商品列表分页查询

-- 优化前：传统分页（深度分页很慢）
SELECT * FROM products 
WHERE category_id = 5 
ORDER BY create_time DESC 
LIMIT 50000, 20;  -- 第2500页，超级慢-- 优化后：游标分页
SELECT * FROM products 
WHERE category_id = 5 AND create_time < '2024-01-15 10:30:00'
ORDER BY create_time DESC 
LIMIT 20;

性能对比：

页数传统分页游标分页性能提升
第1页5ms3ms1.7倍
第100页50ms3ms16.7倍
第1000页500ms3ms166.7倍
第5000页2500ms3ms833.3倍

十二、总结与最佳实践

索引设计的黄金原则

1. 基础原则：

  • 主键索引是必须的
  • 经常WHERE查询的字段要建索引
  • 经常ORDER BY的字段要建索引
  • 区分度高的字段适合建索引

2. 复合索引原则：

  • 遵循最左前缀原则
  • 区分度高的字段放在前面
  • 经常组合查询的字段建复合索引

3. 性能原则：

  • 索引不是越多越好
  • 定期检查和清理无用索引
  • 监控慢查询，及时优化

常见的索引使用误区

误区说明正确做法
给所有字段建索引浪费空间，影响写性能只给查询频繁的字段建索引
忽略复合索引顺序索引失效按最左前缀原则设计
不监控索引使用情况存在无用索引定期检查，清理无用索引
小表也建索引得不偿失小表（<1000行）不建议建索引

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.pswp.cn/pingmian/86831.shtml
繁体地址，请注明出处：http://hk.pswp.cn/pingmian/86831.shtml
英文地址，请注明出处：http://en.pswp.cn/pingmian/86831.shtml

如若内容造成侵权/违法违规/事实不符，请联系英文站点网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

学习使用dotnet-dump工具分析.net内存转储文件（2）

学习使用dotnet-dump工具分析.net内存转储文件（2）

运行ShenNiusModularity项目&#xff0c;使用createdump工具dump完整的进程内存映射文件&#xff0c;然后运行dotnet-dump analyze命令加载dump文件。   可以先使用dumpheap命令显示有关垃圾回收堆的信息和有关对象的收集统计信息。dumpheap支持多类参数&#xff08;如下所示…
阅读更多...
Oracle BIEE 交互示例（一）同一分析内

Oracle BIEE 交互示例（一）同一分析内

Oracle BIEE 交互示例（一）同一分析内 1 示例背景2 实践目标3 实操步骤3.1 创建数据集3.1.1 TEST_TABLE3.1.2 保存名字为【01 TEST_TABLE】3.2 创建分析3.2.1 创建列3.2.2 创建视图3.2.2.1 数据透视表3.2.2.2 图形3.2.2.3 表3.3 设置交互4 结果示例1 示例背景 版本：OBIEE 12…
阅读更多...
使用API有效率地管理Dynadot域名，出售账户中的域名

使用API有效率地管理Dynadot域名，出售账户中的域名

关于Dynadot Dynadot是通过ICANN认证的域名注册商&#xff0c;自2002年成立以来&#xff0c;服务于全球108个国家和地区的客户&#xff0c;为数以万计的客户提供简洁&#xff0c;优惠&#xff0c;安全的域名注册以及管理服务。 Dynadot平台操作教程索引&#xff08;包括域名邮…
阅读更多...
Vite 打包原理详解 + Webpack 对比

Vite 打包原理详解 + Webpack 对比

&#x1f680; Vite 打包原理详解 Webpack 对比 &#x1f44b; 本文适合&#xff1a;Vite 使用者、Vue/React 工程师、希望搞清楚打包流程及与 Webpack 区别的开发者 &#x1f310; 技术背景&#xff1a;Vite 采用 ES Modules 原生浏览器能力驱动开发体验&#xff0c;Webpack…
阅读更多...
区块链RWA（Real World Assets）系统开发全栈技术架构与落地实践指南

区块链RWA（Real World Assets）系统开发全栈技术架构与落地实践指南

一、技术架构设计&#xff1a;分层架构与模块协同 1. 核心区块链层 区块链选型策略&#xff1a; 公链&#xff1a;以太坊主网&#xff08;安全性高&#xff0c;DeFi生态完备&#xff09; Polygon CDK&#xff08;Layer2定制化合规链&#xff0c;Gas费低至$0.003&#xff09;…
阅读更多...
GBDT：梯度提升决策树——集成学习中的预测利器

GBDT：梯度提升决策树——集成学习中的预测利器

核心定位&#xff1a;一种通过串行集成弱学习器&#xff08;决策树&#xff09;、以梯度下降方式逐步逼近目标函数的机器学习算法&#xff0c;在结构化数据预测任务中表现出色。 本文由「大千AI助手」原创发布&#xff0c;专注用真话讲AI&#xff0c;回归技术本质。拒绝神话或妖…
阅读更多...
Redis持久化机制深度解析：RDB与AOF全面指南

Redis持久化机制深度解析：RDB与AOF全面指南

摘要 本文深入剖析Redis的持久化机制&#xff0c;全面讲解RDB和AOF两种持久化方式的原理、配置与应用场景。通过详细的操作步骤和原理分析&#xff0c;您将掌握如何配置Redis持久化策略&#xff0c;确保数据安全性与性能平衡。文章包含思维导图概览、命令实操演示、核心原理图…
阅读更多...
CentOS7升级openssh10.0p2和openssl3.5.0详细操作步骤

CentOS7升级openssh10.0p2和openssl3.5.0详细操作步骤

背景 近期漏洞扫描时&#xff0c;发现有很多关于openssh的相关高危漏洞&#xff0c;因此需要升级openssh的版本 升级步骤 由于openssh和openssl的版本是需要相匹配的&#xff0c;这次计划将openssh升级至10.0p2版本&#xff0c;将openssl升级至3.5.0版本&#xff0c;都是目前…
阅读更多...
fishbot随身系统安装nvidia显卡驱动

fishbot随身系统安装nvidia显卡驱动

小鱼的fishbot是已经配置好的ubuntu22.04,我听说在预先配置系统时需要勾选安装第三方图形化软件&#xff0c;不然直接安装会有进不去图形化界面的风险&#xff0c;若没有勾选&#xff0c;建议使用其他安装方法&#xff0c;比如禁用系统自带的驱动那套安装流程 1.打开设置->关…
阅读更多...
学习昇腾开发的第十天--ffmpeg推拉流

学习昇腾开发的第十天--ffmpeg推拉流

1、FFmpeg推流 注意&#xff1a;在推流之前先运行rtsp-simple-server&#xff08;mediamtx&#xff09; ./mediamtx 1.1 UDP推流 ffmpeg -re -i input.mp4 -c copy -f rtsp rtsp://127.0.0.1:8554/stream 1.2 TCP推流 ffmpeg -re -i input.mp4 -c copy -rtsp_transport t…
阅读更多...
成为一名月薪 2 万的 web 安全工程师需要掌握哪些技能？？

成为一名月薪 2 万的 web 安全工程师需要掌握哪些技能？？

现在 web 安全工程师比较火&#xff0c;岗位比较稀缺&#xff0c;现在除了一些大公司对学历要求严格&#xff0c;其余公司看中的大部分是能力。 有个亲戚的儿子已经工作 2 年了……当初也是因为其他的行业要求比较高&#xff0c;所以才选择的 web 安全方向。 资料免费分享给你…
阅读更多...
Pytorch8实现CNN卷积神经网络

Pytorch8实现CNN卷积神经网络

CNN卷积神经网络 本章提供一个对CNN卷积网络的快速实现 全连接网络 VS 卷积网络 全连接神经网络之所以不太适合图像识别任务&#xff0c;主要有以下几个方面的问题&#xff1a; 参数数量太多 考虑一个输入10001000像素的图片(一百万像素&#xff0c;现在已经不能算大图了)&…
阅读更多...
平地起高楼: 环境搭建

平地起高楼: 环境搭建

技术选型 本小册是采用纯前端的技术栈模拟实现小程序架构的系列文章&#xff0c;所以主要以前端技术栈为主&#xff0c;但是为了模拟一个App应用的效果&#xff0c;以及小程序包下载管理流程的实现&#xff0c;我们还是需要搭建一个基础的App应用。这里我们将选择 Tauri2.0 来…
阅读更多...
langgraph学习2 - MCP编程

langgraph学习2 - MCP编程

3中通信方式&#xff1a; 目前sse用的很少 3.开发mcp框架 主流框架2个&#xff1a; MCP skd 官方 Fast Mcp V2 &#xff0c;&#xff08;V1捐给MCP 官方&#xff09; 大模型如何识别用哪个tools&#xff0c; 以及如何使用tools&#xff1a;
阅读更多...
CSS 与 JavaScript 加载优化

CSS 与 JavaScript 加载优化

&#x1f4c4; CSS 与 JavaScript 加载优化指南&#xff1a;位置、阻塞与性能 让你的网页飞起来&#xff01;&#x1f680; 本文详细解析 CSS 和 JavaScript 标签的放置位置如何影响页面性能&#xff0c;涵盖阻塞原理、浏览器机制和最佳实践。掌握这些知识可显著提升用户体验…
阅读更多...
WSL安装发行版上安装podman

WSL安装发行版上安装podman

WSL安装发行版上安装podman 1.WSL拉取发行版1.1 拉取2.2.修改系统拉取的镜像&#xff0c;可以加速软件包的更新 2.podman安装2.1.安装podman 容器工具2.2.配置podman的镜像仓库2.3.拉取n8n镜像并创建容器 本文在windows11上&#xff0c;使用WSL拉取并创建ubuntu24.04虚拟机&…
阅读更多...
Excel 常用快捷键与对应 VBA 方法/属性清单

Excel 常用快捷键与对应 VBA 方法/属性清单

功能描述快捷键VBA 对应方法/属性 (核心逻辑)说明导航 (类似 End 方向键)这些是 End 键行为的直接对应向下到连续区域末尾Ctrl ↓ActiveCell.End(xlDown)从当前单元格向下&#xff0c;遇到第一个空单元格停止。向上到连续区域开头Ctrl ↑ActiveCell.End(xlUp)从当前单元格向上…
阅读更多...
计算机组成原理与体系结构-实验四 微程序控制器 (Proteus 8.15)

计算机组成原理与体系结构-实验四 微程序控制器 (Proteus 8.15)

一、实验目的 1、理解“微程序”设计思想&#xff0c;了解“指令-微指令-微命令”的微程序结构。 2、掌握微程序控制器的结构和设计方法。 二、实验内容 设计一个“最简版本”的 CPU 模型机&#xff1a;利用时序发生器来产生 CPU 的预定时序&#xff0c;通过微程序控制器的自…
阅读更多...
安卓端某音乐类 APP 逆向分享（二）协议分析

安卓端某音乐类 APP 逆向分享（二）协议分析

以歌曲搜索协议为例&#xff0c;查看charles中歌曲搜索协议详情 拷贝出搜索协议的Curl形式 curl -H Host: interface3.music.xxx.com -H Cookie: EVNSM1.0.0; NMCIDoufhty.1667355455436.01.4; versioncode8008050; buildver221010200836; resolution2392x1440; deviceIdYDwXa…
阅读更多...
七天学会SpringCloud分布式微服务——03——Nacos远程调用

七天学会SpringCloud分布式微服务——03——Nacos远程调用

1、微服务项目配置类放在地方 配置类型应放位置说明通用配置类&#xff08;如&#xff1a;跨服务通用的拦截器、全局异常处理、统一响应体封装等&#xff09;可放在一个**公共模块&#xff08;common/config&#xff09;**中&#xff0c;被各服务引入实现代码复用&#xff0c;…
阅读更多...
最新文章

Copyright @ 2022~2023