MySQL索引完全指南

一、索引是什么？為什么這么重要？

索引就像字典的目錄

想象一下，你要在一本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