SQL優化_以MySQL為例

MySQL SQL 優化詳細教程與案例

1. 理解SQL執行過程

在優化之前，需要了解MySQL如何處理SQL查詢：

  1. 客戶端發送SQL語句到服務器
  2. 服務器檢查查詢緩存（MySQL 8.0已移除查詢緩存）
  3. 解析器解析SQL，生成解析樹
  4. 預處理器驗證權限和表結構
  5. 優化器生成執行計劃
  6. 執行引擎執行查詢并返回結果

2. 使用EXPLAIN分析查詢

EXPLAIN是優化SQL的最重要工具，它顯示MySQL如何執行查詢。

基礎使用：

sql

EXPLAIN SELECT * FROM users WHERE age > 30;

解讀EXPLAIN結果的關鍵列：

  • id: 查詢標識符
  • select_type: 查詢類型（SIMPLE, PRIMARY, SUBQUERY等）
  • table: 訪問的表
  • type: 訪問類型（從好到壞：system > const > eq_ref > ref > range > index > ALL）
  • possible_keys: 可能使用的索引
  • key: 實際使用的索引
  • rows: 估計要檢查的行數
  • Extra: 額外信息（Using where, Using temporary, Using filesort等）

案例：分析慢查詢

sql

-- 原始查詢
EXPLAIN SELECT * FROM orders 
WHERE customer_id = 100 
AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY total_amount DESC;-- 可能顯示type: ALL（全表掃描），需要優化

3. 索引優化策略

3.1 創建合適的索引

sql

-- 案例：為上述查詢創建復合索引
ALTER TABLE orders ADD INDEX idx_customer_date_amount (customer_id, order_date, total_amount);-- 再次分析
EXPLAIN SELECT * FROM orders 
WHERE customer_id = 100 
AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY total_amount DESC;
-- 現在應該顯示type: range，使用索引

3.2 避免索引失效的情況

sql

-- 1. 不要在索引列上使用函數
-- 不好的寫法
SELECT * FROM users WHERE YEAR(create_time) = 2023;
-- 好的寫法
SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';-- 2. 注意LI查詢的通配符位置
-- 不能使用索引
SELECT * FROM users WHERE name LIKE '%john%';
-- 可以使用索引
SELECT * FROM users WHERE name LIKE 'john%';-- 3. 避免對索引列進行運算
-- 不能使用索引
SELECT * FROM products WHERE price * 1.1 > 100;
-- 可以使用索引
SELECT * FROM products WHERE price > 100 / 1.1;

3.3 使用覆蓋索引

sql

-- 需要回表查詢
SELECT * FROM orders WHERE customer_id = 100;-- 使用覆蓋索引（只需要索引列）
SELECT customer_id, order_date, total_amount 
FROM orders 
WHERE customer_id = 100;
-- 為這個查詢創建索引
ALTER TABLE orders ADD INDEX idx_customer_cover (customer_id, order_date, total_amount);

4. 查詢優化技巧

4.1 避免SELECT *

sql

-- 不好的寫法
SELECT * FROM users WHERE age > 30;-- 好的寫法
SELECT id, name, email FROM users WHERE age > 30;

4.2 優化JOIN查詢

sql

-- 確保JOIN字段有索引
EXPLAIN SELECT u.name, o.order_date, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'USA';-- 為user_id和country添加索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
ALTER TABLE users ADD INDEX idx_country (country);-- 使用小表驅動大表
SELECT /*+ STRAIGHT_JOIN */ u.name, o.order_date, o.amount
FROM users u -- 假設users表比orders表小
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'USA';

4.3 優化子查詢

sql

-- 使用JOIN代替子查詢（通常更快）
-- 原始子查詢
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);-- 使用JOIN優化
SELECT DISTINCT u.* 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;-- 或者使用EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000
);

4.4 優化GROUP BY和ORDER BY

sql

-- 確保GROUP BY和ORDER BY使用索引
EXPLAIN SELECT category, COUNT(*) 
FROM products 
GROUP BY category;-- 為category添加索引
ALTER TABLE products ADD INDEX idx_category (category);-- 對于混合排序和分組，可以使用索引優化
ALTER TABLE products ADD INDEX idx_category_price (category, price);EXPLAIN SELECT category, AVG(price)
FROM products
GROUP BY category
ORDER BY AVG(price) DESC;

4.5 分頁優化

sql

-- 傳統分頁在大偏移量時很慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;-- 使用索引優化分頁
SELECT * FROM orders 
WHERE id > (SELECT id FROM orders ORDER BY id LIMIT 1000000, 1)
ORDER BY id LIMIT 20;-- 或者使用JOIN方式
SELECT o.* 
FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
ON o.id = t.id;

5. 數據庫設計優化

5.1 規范化與反規范化

  • 規范化：減少數據冗余，提高數據一致性
  • 反規范化：適當增加冗余，提高查詢性能

sql

-- 示例：在訂單表中反規范化存儲用戶名
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(100);UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.customer_name = u.name;-- 這樣查詢訂單時就不需要JOIN用戶表
SELECT order_id, order_date, customer_name, amount
FROM orders
WHERE customer_name LIKE 'John%';

5.2 選擇合適的數據類型

sql

-- 使用更小的數據類型
-- 不好的設計
CREATE TABLE users (id BIGINT, -- 過度設計，除非真有數十億用戶age INT,   -- 用TINYINT足夠(0-255)status VARCHAR(10) -- 用ENUM更高效
);-- 好的設計
CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT,age TINYINT UNSIGNED,status ENUM('active', 'inactive', 'pending'),PRIMARY KEY (id)
);

5.3 分區表

sql

-- 按時間范圍分區 orders 表
ALTER TABLE orders PARTITION BY RANGE (YEAR(order_date)) (PARTITION p2019 VALUES LESS THAN (2020),PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022),PARTITION p2022 VALUES LESS THAN (2023),PARTITION p2023 VALUES LESS THAN (2024),PARTITION pfuture VALUES LESS THAN MAXVALUE
);-- 查詢特定年份的數據，只會掃描相關分區
EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31';

6. 服務器配置優化

6.1 調整緩沖區大小

ini

# 在my.cnf或my.ini中配置
[mysqld]
# 分配給InnoDB緩沖池的內存，建議為系統內存的50-70%
innodb_buffer_pool_size = 4G# 鍵緩沖區大小，主要用于MyISAM
key_buffer_size = 256M# 查詢緩存大小（MySQL 8.0已移除）
# query_cache_size = 128M

6.2 其他重要配置

ini

# 最大連接數
max_connections = 200# 臨時表大小
tmp_table_size = 256M
max_heap_table_size = 256M# InnoDB日志文件大小
innodb_log_file_size = 512M

7. 實戰優化案例

案例：電商平臺訂單查詢優化

問題：訂單查詢頁面響應緩慢，特別是篩選和分頁功能

原始查詢：

sql

SELECT * FROM orders 
WHERE status = 'completed' 
AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
AND customer_id IN (SELECT id FROM customers WHERE country = 'USA')
ORDER BY order_date DESC
LIMIT 0, 20;

優化步驟：

  1. 分析查詢：

    sql

    EXPLAIN SELECT ...;
-- 發現全表掃描，使用了文件排序

  2. 創建索引：

    sql

    ALTER TABLE orders ADD INDEX idx_status_date (status, order_date);
ALTER TABLE customers ADD INDEX idx_country (country);

  3. 重寫查詢：

    sql

    SELECT o.* 
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
AND o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
AND c.country = 'USA'
ORDER BY o.order_date DESC
LIMIT 0, 20;

  4. 進一步優化分頁：

    sql

    SELECT o.* 
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN (SELECT id FROM orders WHERE status = 'completed'AND order_date BETWEEN '2023-01-01' AND '2023-12-31'AND customer_id IN (SELECT id FROM customers WHERE country = 'USA')ORDER BY order_date DESCLIMIT 0, 20
) AS tmp ON o.id = tmp.id;

  5. 考慮反規范化：

    sql

    -- 在orders表中添加country字段
ALTER TABLE orders ADD COLUMN customer_country VARCHAR(50);UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.customer_country = c.country;-- 新查詢
SELECT * FROM orders 
WHERE status = 'completed'
AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
AND customer_country = 'USA'
ORDER BY order_date DESC
LIMIT 0, 20;

8. 監控與持續優化

8.1 啟用慢查詢日志

ini

# 在my.cnf中配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2  # 記錄執行時間超過2秒的查詢
log_queries_not_using_indexes = 1

8.2 使用Performance Schema

sql

-- 查看最耗時的SQL
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;-- 查看全表掃描最多的表
SELECT * FROM sys.schema_table_statistics
WHERE rows_full_scanned > 0
ORDER BY rows_full_scanned DESC LIMIT 10;

8.3 定期優化表

-- 優化碎片化的表
OPTIMIZE TABLE orders, customers;-- 分析表統計信息
ANALYZE TABLE orders, customers;

總結

MySQL SQL優化是一個持續的過程，需要結合查詢分析、索引優化、數據庫設計調整和服務器配置優化。關鍵步驟包括：

  1. 使用EXPLAIN分析查詢執行計劃
  2. 創建合適的索引，避免索引失效
  3. 重寫低效的查詢語句
  4. 優化數據庫 schema 設計
  5. 調整服務器配置參數
  6. 持續監控和優化性能

記住優化黃金法則：測量→優化→驗證。永遠基于實際性能數據做優化決策，而不是假設。

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/bicheng/95487.shtml
繁體地址，請注明出處：http://hk.pswp.cn/bicheng/95487.shtml
英文地址，請注明出處：http://en.pswp.cn/bicheng/95487.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

探索數據結構中的 “樹”：揭開層次關系的奧秘

探索數據結構中的 “樹”：揭開層次關系的奧秘

在計算機科學的廣袤森林中&#xff0c;有一種數據結構如同參天大樹般支撐著無數應用的根基 —— 它就是 “樹”&#xff08;Tree&#xff09;。它不僅僅是一個抽象概念&#xff0c;更是我們理解和組織信息、模擬現實世界層級關系的強大工具。1. 什么是 “樹”&#xff1f;從家族…
閱讀更多...
技術框架之RPC

技術框架之RPC

一、序言&#xff1a;為什么我們需要RPC&#xff1f;在單體應用時代&#xff0c;函數調用是進程內的簡單操作。但隨著業務規模擴大&#xff0c;系統被拆分為多個獨立服務&#xff08;如訂單服務、支付服務&#xff09;&#xff0c;服務間通信成為剛需。早期開發者常使用HTTPJSO…
閱讀更多...
【光照】Unity中的[光照模型]概念辨析

【光照】Unity中的[光照模型]概念辨析

【從UnityURP開始探索游戲渲染】專欄-直達 基礎光照模型? ?標準光照模型&#xff08;Standard Lighting Model&#xff09;? ?定義?&#xff1a;傳統光照計算的框架&#xff0c;通常包含漫反射、鏡面反射和環境光三部分。?特點?&#xff1a;非物理經驗模型&#xff0c…
閱讀更多...
MCU上跑AI—實時目標檢測算法探索

MCU上跑AI—實時目標檢測算法探索

MCU上跑實時目標檢測算法 前幾年一直忙著別的事情沒有在技術分享上下功夫, 這段時間穩定下來就想和幾個志同道合的朋友做點有意義的事情, 于是乎就使用MCU做了個與AI有識別相關的 “小玩意兒”. 本人負責嵌入式端相關的編碼, AI相關的工作由好友 AgeWang 負責. 這兒把一些成果給…
閱讀更多...
SpringBoot 整合 RabbitMQ 的完美實踐

SpringBoot 整合 RabbitMQ 的完美實踐

引言： 本文總字數：約 9200 字 預計閱讀時間：38 分鐘 為什么 RabbitMQ 是消息中間件的優選？ 在分布式系統架構中，消息中間件扮演著 "交通樞紐" 的角色，負責協調各個服務之間的通信。目前主流的消息中間件有 RabbitMQ、Kafka 和 RocketMQ，它們各具特色： Kafka…
閱讀更多...
nestjs 發起請求 axios

nestjs 發起請求 axios

1、下載npm i --save nestjs/axios axios2、全局配置import { HttpModule } from nestjs/axios;Global() Module({imports: [HttpModule.registerAsync({inject: [ConfigService],useFactory: async (configService: ConfigService) > {return {timeout: configService.get(…
閱讀更多...
將 Logits 得分轉換為概率，如何計算

將 Logits 得分轉換為概率，如何計算

場景&#xff1a;動物識別&#xff0c;輸入一張28*28的圖像&#xff0c;模型輸出屬于 貓、狗、鳥 哪個類型。需求&#xff1a;假設模型 ??Logits&#xff08;模型在每個類別的置信度得分&#xff09; 輸出為??&#xff1a;[貓: 3.2, 狗: 1.5, 鳥: -0.8]。計算 ??Softmax …
閱讀更多...
【Qt】bug排查筆記——QMetaObject::invokeMethod: No such method

【Qt】bug排查筆記——QMetaObject::invokeMethod: No such method

問題如題目所示&#xff1a;QMetaObject::invokeMethod: No such method xxxx&#xff0c;在網上好一頓查&#xff0c;又將查到的資料喂給了 Ai&#xff0c;才最終將問題解決&#xff0c;特此記錄下。 一、問題背景 在做公司項目時&#xff0c;使用了插件的方式開發。主程序加載…
閱讀更多...
Spring Boot手寫10萬敏感詞檢查程序

Spring Boot手寫10萬敏感詞檢查程序

使用Spring Boot手寫10萬敏感詞檢查程序 本文將介紹如何使用Spring Boot構建一個高效的敏感詞檢查系統，能夠處理多達10萬個敏感詞的檢測需求。我們將使用DFA（Deterministic Finite Automaton）算法來實現高效匹配，并提供RESTful API接口。 實現步驟 1. 創建Spring Boot項…
閱讀更多...
零構建的快感！dagger.js 與 React Hooks 實現對比，誰更優雅？

零構建的快感！dagger.js 與 React Hooks 實現對比，誰更優雅？

“Add Tags” 技術方案并行對比&#xff1a;React Hooks vs dagger.js&#xff08;含核心 JS 代碼&#xff09; 源碼&#xff1a; React Hooks&#xff1a;https://codepen.io/prvnbist/pen/jJzROe?editors1010dagger.js&#xff1a;https://codepen.io/dagger8224/pen/ZErjzw…
閱讀更多...
矩池云中LLaMA- Factory多機多卡訓練

矩池云中LLaMA- Factory多機多卡訓練

LLaMA Factory 是一款開源低代碼大模型微調框架&#xff0c;集成了業界最廣泛使用的微調技術&#xff0c;支持通過 Web UI 界面零代碼微調大模型&#xff0c;目前已經成為開源社區內最受歡迎的微調框架之一。但是在矩池云上如何使用LLaMA-Factory多機多卡訓練模型呢&#xff1f…
閱讀更多...
Nginx的反向代理與正向代理及其location的配置說明

Nginx的反向代理與正向代理及其location的配置說明

一、Nginx中location匹配優先級Nginx中location匹配優先級location支持各種匹配規則&#xff0c;在多個匹配規則下&#xff0c;Nginx對location的處理是有優先級的&#xff0c;優先級高的規則會優先進行處理&#xff1b;而優先級低的規則可能會最后處理或者不進行處理。注意&am…
閱讀更多...
神經網絡正則化三重奏：Weight Decay, Dropout, 和LayerNorm

神經網絡正則化三重奏：Weight Decay, Dropout, 和LayerNorm

正則化是機器學習中防止模型過擬合、提升泛化能力的核心技術。Weight Decay、Dropout和LayerNorm是三種最常用的方法&#xff0c;但它們的工作原理和首要目標截然不同。下面的流程圖揭示了它們的核心區別與聯系&#xff1a; #mermaid-svg-vymek6mFvvfxcWiM {font-family:"…
閱讀更多...
兩臺電腦通過網線直連共享數據，設置正確，卻互相ping不通的解決方法

兩臺電腦通過網線直連共享數據，設置正確，卻互相ping不通的解決方法

因為某些原因&#xff0c;需要兩臺電腦互傳資源&#xff0c;但是某臺電腦可能無法連接外網。如果手頭有根網線&#xff0c;很容易想到通過一根網線連接兩臺電腦互傳數據。 這里先說一下基本的設置&#xff1a; 兩臺電腦最好都關閉防火墻&#xff1b;兩臺電腦都打開專用網絡和公…
閱讀更多...
面試新紀元：無聲勝有聲，讓AI成為你頸上的智慧伙伴

面試新紀元：無聲勝有聲，讓AI成為你頸上的智慧伙伴

面試&#xff0c;無論是對于面試官還是求職者&#xff0c;都像一場無聲的戰爭。 一方要精準識人&#xff0c;一方要完美自薦&#xff1b;一方怕問不到點子上&#xff0c;一方怕答不到心坎里。 緊張、遺忘、表達失誤、準備不足……這些問題幾乎每個人都經歷過。 有沒有一種方…
閱讀更多...
qt-C++筆記之QtDesigner-Creator按鈕圖標與樣式

qt-C++筆記之QtDesigner-Creator按鈕圖標與樣式

qt-C筆記之QtDesigner-Creator按鈕圖標與樣式 整理&#xff1a;如何用 .qrc 管理資源、在 Designer/Creator 中為 QPushButton 設置圖標&#xff08;資源或系統主題&#xff09;&#xff0c;以及用樣式表調整文字樣式。涵蓋 C/Qt 與 PySide/PyQt&#xff1b;Linux 桌面優先&am…
閱讀更多...
maven 常用指令

maven 常用指令

Maven 是 Java 項目構建和依賴管理的得力助手。這里為你總結了一些常用指令&#xff0c;希望能幫你提升開發效率。下面這個表格匯總了 Maven 最核心和常用的一些命令&#xff1a;命令主要功能典型使用場景mvn clean清理項目&#xff0c;刪除 target 目錄及其所有編譯輸出文件。…
閱讀更多...
# pdf.js完全指南：構建現代Web PDF查看與解析解決方案

# pdf.js完全指南：構建現代Web PDF查看與解析解決方案

在當今Web開發中&#xff0c;實現高質量的PDF查看功能一直是前端開發者面臨的挑戰之一。作為最受歡迎的JavaScript PDF庫&#xff0c;pdf.js已經成為解決這一問題的行業標準。由Mozilla開發并維護的pdf.js項目&#xff0c;通過純JavaScript實現PDF解析與渲染&#xff0c;徹底改…
閱讀更多...
高效對象屬性復制工具

高效對象屬性復制工具

日常編程中&#xff0c;經常會碰到對象屬性復制的場景&#xff0c;比如 VO、DTO、PO、VO 等之間的轉換&#xff0c;關于什么是VO、DTO、PO、VO 等可以看上篇文章&#xff0c;VO、DTO、PO、VO 等對象具體有哪些方式可以使用呢&#xff1f; set/get 方式 性能最好的方式&#x…
閱讀更多...
大疆圖傳技術參數對比 你了解多少？

大疆圖傳技術參數對比 你了解多少？

無人機是現代航空技術與智能控制技術結合的產物&#xff0c;已從軍事領域廣泛滲透至民用場景&#xff0c;成為推動各行業效率升級的關鍵工具。無人機的全稱為 “無人駕駛航空器&#xff08;Unmanned Aerial Vehicle&#xff0c;簡稱 UAV&#xff09;”&#xff0c;簡言之&#…
閱讀更多...
最新文章

Copyright @ 2022~2023