MySQL索引与性能优化入门：让查询提速的秘密武器【MySQL系列】

本文将深入讲解 MySQL 索引的底层原理、常见类型、使用技巧，并结合 EXPLAIN 工具分析查询执行计划，配合慢查询日志识别瓶颈，逐步建立起系统的 MySQL 查询优化知识体系。适合有一定基础、希望在数据量增长或面试中脱颖而出的开发者阅读。

一、MySQL索引是什么？

1.1 索引的本质

索引是一种数据结构，其目的是提升数据库查询效率。它将表中的某些列值抽取出来，构建一个高效的查找结构（通常是 B+ 树），通过该结构定位数据的存储位置。

换句话说，索引是表数据的“加速器”。没有索引时，MySQL 只能做全表扫描；有索引时，可快速缩小查找范围。

1.2 索引的类比

  • 无索引：就像找一本书中某个词，必须逐页翻阅。
  • 有索引：像是查字典，有字母目录直接定位页码。

二、MySQL常见索引类型

2.1 主键索引（PRIMARY KEY）

每张表只能有一个主键索引，默认是聚簇索引。

2.2 唯一索引（UNIQUE）

保证字段值唯一，适合如邮箱、身份证号等字段。

2.3 普通索引（INDEX）

最基础的索引，无任何约束，只提升查询性能。

2.4 组合索引（Composite Index）

在多个列上创建的索引，遵循“最左前缀”原则。

2.5 全文索引（FULLTEXT）

用于全文搜索，支持自然语言分析。

2.6 空间索引（SPATIAL）

主要用于 GIS 地理信息类型字段。

三、索引底层原理：B+树结构详解

MySQL 的 InnoDB 存储引擎默认使用 B+ 树作为索引结构。

3.1 B+树特性

  • 所有数据都存储在叶子节点。
  • 非叶子节点只存储键值（索引项），不存储数据。
  • 所有叶子节点通过链表相连，方便区间查询。

3.2 聚簇索引 vs 非聚簇索引

  • 聚簇索引：主键索引，数据和索引存储在一起。
  • 二级索引（辅助索引）：索引结构中存储的是主键的值，需要二次回表查询原始数据。

四、创建索引的最佳实践

4.1 如何选择索引列？

  • 用于 WHERE 子句过滤的字段
  • 用于 JOIN、ORDER BY、GROUP BY 的字段
  • 高基数（distinct 值多）的字段优先考虑

4.2 创建索引示例

-- 普通索引
CREATE INDEX idx_email ON users(email);-- 唯一索引
CREATE UNIQUE INDEX idx_mobile ON users(mobile);-- 组合索引
CREATE INDEX idx_multi ON orders(user_id, status);

4.3 删除索引

DROP INDEX idx_email ON users;

4.4 查看索引

SHOW INDEX FROM users;

五、查询优化利器：EXPLAIN 执行计划

5.1 基本使用

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

5.2 关键字段解析

字段含义
id查询序列编号
select_type查询类型（SIMPLE、PRIMARY、SUBQUERY 等）
table当前访问的表
type连接类型（ALL、index、range、ref、const、eq_ref、NULL）
key使用的索引
rows预计扫描的行数
Extra额外信息，如"Using where"、“Using index”

5.3 type 字段详解

  • ALL：全表扫描（最差）
  • index：全索引扫描
  • range：范围扫描，如 BETWEEN、>、<
  • ref：使用非唯一索引查找
  • const：唯一索引等值查找，最多一行

5.4 案例：组合索引未命中

CREATE INDEX idx_user_status ON orders(user_id, status);-- 命中索引
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';-- 未命中组合索引
SELECT * FROM orders WHERE status = 'paid';

六、慢查询日志：发现性能瓶颈

6.1 开启慢查询日志

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

6.2 查询慢日志内容

mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log

6.3 使用 pt-query-digest 分析慢查询

pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt

七、常见查询优化技巧

7.1 避免 SELECT *

明确列字段，避免读取不必要数据。

7.2 使用覆盖索引

查询所用字段全部在索引中，避免回表。

-- 创建覆盖索引
CREATE INDEX idx_name_age ON users(name, age);-- 查询使用覆盖索引
SELECT name, age FROM users WHERE name = 'Tom';

7.3 避免在 WHERE 中对索引字段做函数操作

-- 不走索引
SELECT * FROM users WHERE DATE(create_time) = '2024-01-01';-- 优化后
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

7.4 利用 LIMIT + 索引分页优化

-- 分页慢
SELECT * FROM users ORDER BY id LIMIT 10000, 10;-- 延迟关联优化
SELECT * FROM users WHERE id > (SELECT id FROM users ORDER BY id LIMIT 10000, 1) LIMIT 10;

7.5 拆分大查询

将一次性操作百万数据的语句，拆分为批量处理：

DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 1000;

八、避免这些索引误区

  • 所有字段都建索引：浪费空间 + 写入变慢
  • 忽视组合索引顺序：需遵循最左前缀原则
  • 数据量小也加索引：小表加索引反而可能变慢
  • 高频更新字段建索引：更新频繁的字段不建议建索引

九、实践案例：优化百万级用户查询

9.1 初始场景

SELECT * FROM users WHERE email = 'abc@example.com';
  • 数据量：用户表 500 万条
  • 无索引：执行时间 > 3 秒

9.2 添加索引

CREATE INDEX idx_email ON users(email);

9.3 使用 EXPLAIN 检查

EXPLAIN SELECT * FROM users WHERE email = 'abc@example.com';
-- type: ref, key: idx_email, rows: 1
  • 查询时间降低至 < 10ms

本项目适用于后台管理系统、电商用户中心、SaaS 用户模块等场景，特别适合开发者进行实战演练与面试准备。

一、项目背景与需求概述

我们将构建一个基础版的用户管理系统，具备以下业务功能：

  • 用户注册与登录
  • 用户角色与权限分配
  • 日志记录与用户状态追踪
  • 多条件用户查询与分页

涉及的核心业务对象包括：用户、角色、权限、日志等。

二、数据库建模与表结构设计

2.1 实体关系图（ER图）简要说明

  • 一位用户可以拥有多个角色（多对多）
  • 一个角色可以拥有多个权限（多对多）
  • 用户与登录日志是一对多关系

2.2 用户表（users）

CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL UNIQUE,password VARCHAR(100) NOT NULL,email VARCHAR(100),status TINYINT DEFAULT 1 COMMENT '0:禁用, 1:启用',created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2.3 角色表（roles）

CREATE TABLE roles (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL UNIQUE,description VARCHAR(255)
);

2.4 权限表（permissions）

CREATE TABLE permissions (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL UNIQUE,code VARCHAR(50) NOT NULL UNIQUE COMMENT '用于权限标识，如 user:view'
);

2.5 用户-角色关联表（user_role）

CREATE TABLE user_role (user_id INT,role_id INT,PRIMARY KEY (user_id, role_id),FOREIGN KEY (user_id) REFERENCES users(id),FOREIGN KEY (role_id) REFERENCES roles(id)
);

2.6 角色-权限关联表（role_permission）

CREATE TABLE role_permission (role_id INT,permission_id INT,PRIMARY KEY (role_id, permission_id),FOREIGN KEY (role_id) REFERENCES roles(id),FOREIGN KEY (permission_id) REFERENCES permissions(id)
);

2.7 登录日志表（login_logs）

CREATE TABLE login_logs (id INT PRIMARY KEY AUTO_INCREMENT,user_id INT,ip_address VARCHAR(45),login_time DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (user_id) REFERENCES users(id)
);

三、数据初始化脚本

3.1 插入初始角色与权限

INSERT INTO roles(name, description) VALUES ('admin', '系统管理员'), ('user', '普通用户');INSERT INTO permissions(name, code) VALUES
('查看用户', 'user:view'),
('新增用户', 'user:create'),
('删除用户', 'user:delete');-- 分配权限给角色
INSERT INTO role_permission(role_id, permission_id) VALUES
(1, 1), (1, 2), (1, 3), -- admin 拥有全部权限
(2, 1);                 -- user 仅能查看用户

3.2 插入测试用户

INSERT INTO users(username, password, email) VALUES
('alice', 'hashed_pwd1', 'alice@example.com'),
('bob', 'hashed_pwd2', 'bob@example.com');-- 分配角色
INSERT INTO user_role(user_id, role_id) VALUES
(1, 1), -- alice 为管理员
(2, 2); -- bob 为普通用户

四、典型查询场景实现

4.1 查询所有启用用户及其角色

SELECT u.id, u.username, r.name AS role
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
WHERE u.status = 1;

4.2 查询某用户拥有的所有权限

SELECT p.name, p.code
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN role_permission rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE u.username = 'alice';

4.3 查询最近7天登录日志

SELECT u.username, l.ip_address, l.login_time
FROM login_logs l
JOIN users u ON l.user_id = u.id
WHERE l.login_time >= NOW() - INTERVAL 7 DAY
ORDER BY l.login_time DESC;

4.4 用户分页查询（带关键字搜索）

SELECT *
FROM users
WHERE username LIKE '%bob%'
ORDER BY created_at DESC
LIMIT 0, 10;

五、事务控制与一致性保障

在角色授权或用户注册等业务流程中，可以使用事务来确保数据完整性。

5.1 注册用户 + 分配默认角色（事务）

START TRANSACTION;INSERT INTO users(username, password, email) VALUES('charlie', 'hashed_pwd3', 'charlie@example.com');
SET @uid = LAST_INSERT_ID();
INSERT INTO user_role(user_id, role_id) VALUES(@uid, 2); -- 默认赋普通角色COMMIT;

5.2 授权失败时回滚

START TRANSACTION;-- 假设某权限不存在导致失败
INSERT INTO role_permission(role_id, permission_id) VALUES(1, 999);-- 失败时回滚
ROLLBACK;

六、索引优化与执行分析

6.1 建议加索引字段

  • users.username：用于登录验证、搜索
  • login_logs.user_id：日志查询
  • user_role.user_id / role_permission.role_id：JOIN 优化
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_user_log ON login_logs(user_id);

6.2 执行计划分析

EXPLAIN SELECT u.username, r.name FROM users u JOIN user_role ur ON u.id = ur.user_id JOIN roles r ON ur.role_id = r.id;

可查看索引是否使用、JOIN 类型、Rows 扫描数量等。

更多推荐【MySQL完整系列】：MySQL数据库从0到拿捏系列

  1. MySQL数据库零基础入门教程：从安装配置到数据查询全掌握
    关键词：安装、登录、客户端、库表基础、简单查询

  2. MySQL数据表操作全指南：建表、修改、删除一步到位
    关键词：DDL语句、字段类型、主键/外键、约束、规范设计
    聚焦表结构的创建和维护，配合真实业务建表案例（如用户表、订单表）。

  3. MySQL增删改查基础教程：熟练掌握DML语句操作
    关键词：INSERT、UPDATE、DELETE、SELECT、WHERE、ORDER BY
    实战演练日常的数据库操作命令，重点讲解查询语句的条件与排序。

  4. MySQL高级查询技巧：分组、聚合、子查询与分页
    关键词：GROUP BY、HAVING、聚合函数、LIMIT、子查询
    向中级进阶，涵盖常见报表需求与分页列表的查询实现。

  5. MySQL多表查询详解：内连接、外连接、自连接通通搞懂
    关键词：JOIN、INNER JOIN、LEFT JOIN、UNION、自连接
    深度讲解表与表之间如何通过字段建立关联并进行数据整合。

  6. MySQL索引与性能优化入门：让查询提速的秘密武器
    关键词：索引原理、EXPLAIN、慢查询、查询优化
    开启性能优化之路，适合准备应对数据量增长或面试的人。

  7. MySQL事务与锁机制详解：确保数据一致性的关键
    关键词：事务四大特性、锁类型、死锁案例、隔离级别
    涉及电商、支付系统等对数据一致性要求高的业务场景。

  8. MySQL项目实战演练：搭建用户管理系统的完整数据库结构
    关键词：业务建模、表关系设计、数据初始化、查询场景
    以实战带动知识回顾，模拟真实业务项目，整合前面所学内容。

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

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

相关文章

C 语言开发中常见的开发环境

C 语言开发中常见的开发环境

目录 1.Dev-C 2.Visual Studio Code 3.虚拟机 Linux 环境 4.嵌入式 MCU 专用开发环境 1.Dev-C 使用集成的 C/C 开发环境&#xff08;适合基础学习&#xff09;,下载链接Dev-C下载 - 官方正版 - 极客应用 2.Visual Studio Code 结合 C/C 扩展 GCC/MinGW 编译器&#xff0c…
阅读更多...
STM32G4 电机外设篇（二） VOFA + ADC + OPAMP

STM32G4 电机外设篇（二） VOFA + ADC + OPAMP

目录 一、STM32G4 电机外设篇&#xff08;二&#xff09; VOFA ADC OPAMP1 VOFA1.1 VOFA上位机显示波形 2 ADC2.1 用ADC规则组对板载电压和电位器进行采样 3 OPAMP&#xff08;运放&#xff09;3.1 结合STM32内部运放和ADC来完成对三相电流的采样3.2 运放电路分析 附学习参考…
阅读更多...
再见Notepad++，你好Notepad--

再见Notepad++，你好Notepad--

Notepad-- 是一款国产开源的轻量级、跨平台文本编辑器&#xff0c;支持 Window、Linux、macOS 以及国产 UOS、麒麟等操作系统。 除了具有常用编辑器的功能之外&#xff0c;Notepad-- 还内置了专业级的代码对比功能&#xff0c;支持文件、文件夹、二进制文件的比对&#xff0c;支…
阅读更多...
跳动的爱心

跳动的爱心

跳动的心形图案&#xff0c;通过字符打印和延时效果模拟跳动&#xff0c;心形在两种大小间交替跳动。 通过数学公式生成心形曲线 #include <stdio.h> #include <windows.h> // Windows 系统头文件&#xff08;用于延时和清屏&#xff09; void printHeart(int …
阅读更多...
2.2HarmonyOS NEXT高性能开发技术：编译优化、内存管理与并发编程实践

2.2HarmonyOS NEXT高性能开发技术：编译优化、内存管理与并发编程实践

HarmonyOS NEXT高性能开发技术&#xff1a;编译优化、内存管理与并发编程实践 在HarmonyOS NEXT全场景设备开发中&#xff0c;高性能是跨端应用体验的核心保障。本章节聚焦ArkCompiler编译优化、内存管理工具及多线程并发编程三大技术模块&#xff0c;结合实战案例解析底层实现…
阅读更多...
C# 类和继承(使用基类的引用)

C# 类和继承(使用基类的引用)

使用基类的引用 派生类的实例由基类的实例和派生类新增的成员组成。派生类的引用指向整个类对象&#xff0c;包括 基类部分。 如果有一个派生类对象的引用&#xff0c;就可以获取该对象基类部分的引用&#xff08;使用类型转换运算符把 该引用转换为基类类型&#xff09;。类…
阅读更多...
如何在腾讯云 OpenCloudOS 上安装 Docker 和 Docker Compose

如何在腾讯云 OpenCloudOS 上安装 Docker 和 Docker Compose

从你提供的 /etc/os-release 文件内容来看&#xff0c;你的服务器运行的是 OpenCloudOS 9.2。这是一个基于 CentOS 和 RHEL 的开源操作系统&#xff0c;因此它属于 CentOS/RHEL 系列。 关键信息总结 操作系统名称&#xff1a;OpenCloudOS版本&#xff1a;9.2ID&#xff1a;op…
阅读更多...
趋势直线指标

趋势直线指标

趋势直线副图和主图指标&#xff0c;旨在通过技术分析工具帮助交易者识别市场趋势和潜在的买卖点。 副图指标&#xff1a;基于KDJ指标的交易策略 1. RSV值计算&#xff1a; - RSV&#xff08;未成熟随机值&#xff09;反映了当前收盘价在过去一段时间内的相对位置。通过计算当前…
阅读更多...
FEMFAT许可分析的数据可视化方法

FEMFAT许可分析的数据可视化方法

随着企业对FEMFAT软件使用的增加&#xff0c;如何有效地管理和分析许可数据成为了关键。数据可视化作为一种强大的工具&#xff0c;能够帮助企业直观地理解FEMFAT许可的使用情况&#xff0c;从而做出更明智的决策。本文将介绍FEMFAT许可分析的数据可视化方法&#xff0c;并探讨…
阅读更多...
AMBER软件介绍

AMBER软件介绍

AMBER软件介绍 AMBER&#xff08;Assisted Model Building with Energy Refinement&#xff09;是一套广泛应用于分子动力学&#xff08;MD&#xff09;模拟和生物分子结构分析的软件工具集&#xff0c;尤其在蛋白质、核酸、多糖等生物大分子的模拟中表现突出。以下是关于AMBE…
阅读更多...
GoogLeNet网络模型

GoogLeNet网络模型

GoogLeNet网络模型 诞生背景 在2014年的ImageNet图像识别挑战赛中&#xff0c;一个GoogLeNet的网络架构大放异彩&#xff0c;与VGG不同的是&#xff0c;VGG用的是3*3的卷积&#xff0c;而GoogLeNet从1*1到7*7的卷积核都用&#xff0c;也就是使用不同大小的卷积核组合。 网络…
阅读更多...
Free2AI：企业智能化转型的加速器

Free2AI：企业智能化转型的加速器

随着数字化与智能化的深度交融&#xff0c;企业的竞争舞台已悄然转变为数据处理能力和智能服务水平的竞技场。Free2AI以其三大核心功能——智能数据采集、多格式文档解析、智能FAQ构建&#xff0c;为企业铺设了一条从数据洞察到智能服务的全链路升级之路&#xff0c;成为推动企…
阅读更多...
Vue 核心技术与实战day07

Vue 核心技术与实战day07

1. vuex概述 2. 构建 vuex [多组件数据共享] 环境 <template><div id"app"><h1>根组件- {{ title }}- {{ count }}</h1><input :value"count" input"handleInput" type"text"><Son1></Son1>…
阅读更多...
【原神 × 插入排序】刷圣遗物也讲算法：圣遗物评分系统背后的排序逻辑你真的懂吗？

【原神 × 插入排序】刷圣遗物也讲算法：圣遗物评分系统背后的排序逻辑你真的懂吗？

📘 改编自：王争《数据结构与算法之美》 🎮 游戏演绎：米哈游《原神》 🧠 核心关键词：插入排序、排序算法、评分系统、属性评价、强化圣遗物、冒泡排序对比 🧭 引言：原神刷本=刷排序？ 玩《原神》的玩家每天日常是啥？体力用来刷圣遗物、精通头、暴击头、攻充沙………
阅读更多...
quasar electron mode如何打包无边框桌面应用程序

quasar electron mode如何打包无边框桌面应用程序

预览 开源项目Tokei Kun 一款简洁的周年纪念app&#xff0c;现已发布APK&#xff08;安卓&#xff09;和 EXE&#xff08;Windows&#xff09; 项目仓库地址&#xff1a;Github Repo 应用下载链接&#xff1a;Github Releases Preparation for Electron quasar dev -m elect…
阅读更多...
微信小程序真机调试时如何实现与本地开发环境服务器交互

微信小程序真机调试时如何实现与本地开发环境服务器交互

最近在开发微信小程序项目,真机调试时需要在手机上运行小程序,为了实现本地开发服务器与手机小程序的交互,需要以下步骤 1.将手机连到和本地一样的局域网 2.Visual Studio中将IIS Express服务器的localhost端口地址修改为本机的IP自定义的端口: 1&#xff09;找到web api项目…
阅读更多...
Scratch节日 | 拯救屈原 | 端午节

Scratch节日 | 拯救屈原 | 端午节

端午节快乐&#xff01; 这款特别为端午节打造的Scratch游戏 《拯救屈原》&#xff0c;将带你走进古代中国&#xff0c;感受历史与文化的魅力&#xff01; &#x1f3ee; 游戏介绍 扮演勇敢的探险者&#xff0c;穿越时空回到古代&#xff0c;解锁谜题&#xff0c;完成任务&…
阅读更多...
PHP下实现RSA的加密，解密，加签和验签

PHP下实现RSA的加密，解密，加签和验签

前言&#xff1a; RSA下加密&#xff0c;解密&#xff0c;加签和验签是四种不同的操作&#xff0c;有时候会搞错&#xff0c;记录一下。 1.公钥加密&#xff0c;私钥解密 发送方通过公钥将原数据加密成一个sign参数&#xff0c;相当于就是信息的载体&#xff0c;接收方能通过si…
阅读更多...
Win10秘笈：两种方式修改网卡物理地址（MAC）

Win10秘笈：两种方式修改网卡物理地址（MAC）

Win10秘笈&#xff1a;两种方式修改网卡物理地址&#xff08;MAC&#xff09; 在修改之前&#xff0c;可以先确定一下要修改的网卡MAC地址&#xff0c;查询方法有很多种&#xff0c;比如&#xff1a; 1、在设置→网络和Internet→WLAN/以太网&#xff0c;如下图所示。 2、在控…
阅读更多...
C++中IO文件输入输出知识详解和注意事项

C++中IO文件输入输出知识详解和注意事项

以下内容将从文件流类体系、打开模式、文本与二进制 I/O、随机访问、错误处理、性能优化等方面&#xff0c;详解 C 中文件输入输出的使用要点&#xff0c;并配以示例。 一、文件流类体系 C 标准库提供三种文件流类型&#xff0c;均定义在 <fstream> 中&#xff1a; std…
阅读更多...
最新文章

Copyright @ 2022~2023