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
繁體地址，請注明出處：http://hk.pswp.cn/bicheng/83157.shtml
英文地址，請注明出處：http://en.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