MySQL SQL語句性能優化

MySQL SQL語句性能優化指南

  • 一、查詢設計優化
    • 1. 避免 SELECT *
    • 2. 使用 WHERE 進行條件過濾
    • 3. 避免在索引列上使用函數和表達式
    • 4. 使用 LIMIT 限制返回行數
    • 5. 避免使用子查詢
    • 6. 優化 JOIN 操作
    • 7. 避免全表掃描
  • 二、索引優化
    • 1. 使用合適的索引
    • 2. 覆蓋索引
    • 3. 索引選擇性
    • 4. 多列索引順序
  • 三、表結構優化
    • 1. 垂直拆分
    • 2. 水平分區
    • 3. 使用適當的數據類型
  • 四、查詢緩存優化
    • 1. 查詢緩存的工作原理
    • 2. 配置查詢緩存
    • 3. 查詢緩存的優缺點
    • 4. 查詢緩存的最佳實踐
  • 五、配置優化
    • 1. 調整連接池大小
    • 2. 使用慢查詢日志
  • 六、其他優化技巧
    • 1. 避免使用臨時表
    • 2. 使用批量插入
    • 3. 定期優化表
    • 4. 避免使用鎖表
  • 七、使用 EXPLAIN 分析查詢
  • 總結

MySQL作為一款流行的關系型數據庫管理系統，廣泛應用于各類應用系統中。然而，隨著數據量的增加和查詢復雜度的提高，SQL查詢性能可能會成為系統瓶頸。本文將系統地介紹MySQL SQL語句性能優化的原則和方法，幫助提升數據庫的運行效率。

一、查詢設計優化

1. 避免 SELECT *

SELECT * 會檢索表中的所有列，可能會帶來不必要的I/O開銷和網絡傳輸。因此，應盡量選擇需要的列。

-- 不推薦
SELECT * 
FROM users 
WHERE id = 1;-- 推薦
SELECT id, username, email 
FROM users 
WHERE id = 1;

2. 使用 WHERE 進行條件過濾

在查詢中盡量使用 WHERE 子句進行條件過濾，減少全表掃描的行數，從而提高查詢效率。

-- 不推薦
SELECT * 
FROM orders;-- 推薦
SELECT * 
FROM orders 
WHERE status = 'completed';

3. 避免在索引列上使用函數和表達式

在 WHERE 子句中的索引列上使用函數或表達式會導致無法使用索引，影響查詢性能。

-- 不推薦
SELECT * 
FROM users 
WHERE YEAR(created_at) = 2024;-- 推薦
SELECT * 
FROM users 
WHERE created_at BETWEEN '2024-12-01' AND '2024-12-10';

4. 使用 LIMIT 限制返回行數

對于需要分頁顯示的數據，應使用 LIMIT 限制返回的行數，避免一次性讀取過多數據。

SELECT * 
FROM orders 
WHERE status = 'completed' 
LIMIT 100;

5. 避免使用子查詢

在可能的情況下，盡量避免使用子查詢，而是使用連接（JOIN）來優化查詢。

-- 不推薦
SELECT * 
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'completed');-- 推薦
SELECT users.* 
FROM users 
JOIN orders ON users.id = orders.user_id 
WHERE orders.status = 'completed';

6. 優化 JOIN 操作

在使用JOIN操作時，確保被連接的列上有索引，并盡量減少JOIN的數量和復雜度。

-- 創建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);-- 使用索引優化JOIN查詢
SELECT users.* 
FROM users 
JOIN orders ON users.id = orders.user_id 
WHERE orders.status = 'completed';

7. 避免全表掃描

當表中的數據量非常大時，執行沒有過濾條件的查詢或者查詢條件不適合索引時，數據庫可能需要進行全表掃描。

這不僅會增加查詢時間，還會加重數據庫負擔。為了避免全表掃描，應該盡量通過索引列、合理的過濾條件等優化查詢，減少掃描的數據量。

-- 不推薦
SELECT * FROM users WHERE name LIKE '%J%';-- 推薦
SELECT * FROM users WHERE user_id = 123 AND name LIKE '%J%';

在上述查詢中，name LIKE '%J%' 會導致全表掃描，因為數據庫無法利用索引來加速這種模糊匹配操作，特別是當表中的數據量非常大的時候，查詢會非常慢。

改進后的查詢通過添加具有索引的 user_id 作為條件，能夠利用索引優化查詢，避免全表掃描。

二、索引優化

1. 使用合適的索引

為常用的查詢條件和排序條件添加索引，避免全表掃描。

-- 創建索引
CREATE INDEX idx_users_username ON users(username);-- 使用索引的查詢
SELECT * 
FROM users 
WHERE username = 'john_doe';

2. 覆蓋索引

覆蓋索引包含查詢所需的所有列，可以避免回表查詢，進一步提高查詢性能。

-- 創建覆蓋索引
CREATE INDEX idx_orders_status_created_at ON orders(status, created_at);-- 使用覆蓋索引的查詢
SELECT status, created_at 
FROM orders 
WHERE status = 'completed';

3. 索引選擇性

索引的選擇性（即唯一值的比例）越高，索引的效率越高。對于低選擇性的列（如性別），單獨建立索引效果不佳，應考慮與其他高選擇性列組合建立聯合索引。

4. 多列索引順序

在創建多列索引時，應將選擇性高的列放在索引的前面，以提高索引的效率。

-- 選擇性高的列在前
CREATE INDEX idx_users_lastname_firstname ON users(lastname, firstname);-- 查詢時利用多列索引
SELECT * 
FROM users 
WHERE lastname = 'Smith' AND firstname = 'John';

三、表結構優化

1. 垂直拆分

將表中使用頻率不同的字段拆分到不同的表中，減少查詢的復雜度和數據量。

-- 原始表
CREATE TABLE user_details (id INT PRIMARY KEY,username VARCHAR(50),email VARCHAR(100),address TEXT,phone_number VARCHAR(20)
);-- 拆分后的表
CREATE TABLE users (id INT PRIMARY KEY,username VARCHAR(50),email VARCHAR(100)
);CREATE TABLE user_contacts (user_id INT,address TEXT,phone_number VARCHAR(20),FOREIGN KEY (user_id) REFERENCES users(id)
);

2. 水平分區

對于數據量非常大的表，可以使用分區來提高查詢性能。

-- 創建分區表
CREATE TABLE orders (id INT,order_date DATE,amount DECIMAL(10, 2)
) PARTITION BY RANGE (YEAR(order_date)) (PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022)
);

3. 使用適當的數據類型

選擇適當的數據類型可以減少存儲空間和提高查詢性能。例如，使用整數類型代替字符串類型作為主鍵。

-- 使用整數類型作為主鍵
CREATE TABLE users (id INT PRIMARY KEY,username VARCHAR(50),email VARCHAR(100)
);

四、查詢緩存優化

在 MySQL 中，查詢緩存是一個用于存儲 SELECT 查詢結果的機制。通過查詢緩存，MySQL 可以避免重復執行相同的查詢，直接從緩存中返回結果，從而顯著提高查詢性能，減少數據庫負載。

1. 查詢緩存的工作原理

查詢緩存將查詢的結果存儲在內存中，并且是基于查詢的文本來緩存的。只要查詢的 SQL 語句完全相同，MySQL 會直接從緩存中獲取結果，而不是重新執行查詢。

工作流程：

  1. 用戶提交查詢時，MySQL 會首先檢查查詢緩存中是否存在相同的查詢結果。
  2. 如果緩存中存在查詢結果，MySQL 會直接返回緩存中的結果。
  3. 如果緩存中不存在結果，MySQL 會執行查詢，將結果存入緩存，并返回給用戶。

注意：查詢緩存只會緩存 SELECT 查詢的結果，不會緩存 INSERT、UPDATE、DELETE 等修改數據的操作。

2. 配置查詢緩存

啟用查詢緩存

在 MySQL 配置文件 my.cnf 中，可以通過設置以下選項來啟用查詢緩存：
適當調整MySQL的緩存參數，如 query_cache_size、innodb_buffer_pool_size 等，可以提高查詢性能。

[mysqld]
query_cache_type = 1                # 啟用查詢緩存
query_cache_size = 256M             # 設置查詢緩存大小
query_cache_limit = 1M              # 設置緩存的查詢大小限制，超過此大小的查詢將不緩存
  • query_cache_type：指定查詢緩存的啟用方式。1 表示啟用查詢緩存，0 表示禁用查詢緩存，2 表示只有 SQL_NO_CACHE（禁用緩存）標記的查詢才不緩存。
  • query_cache_size：設置查詢緩存的大小，單位為字節。合理設置緩存大小可以避免過多的內存消耗。
  • query_cache_limit：設置緩存的查詢結果大小限制。如果查詢的結果超過該大小，則不緩存。

動態調整查詢緩存（運行時）

除了在配置文件中設置外，也可以通過 SQL 命令在運行時動態調整查詢緩存的大小和啟用狀態：

-- 啟用查詢緩存
SET global query_cache_size = 1000000;   # 設置查詢緩存大小為 1MB
SET global query_cache_type = 1;          # 啟用查詢緩存-- 執行查詢
SELECT * FROM users WHERE username = 'John';

SET global query_cache_size：此命令設置查詢緩存的大小。在此示例中，將緩存大小設置為 1MB。
SET global query_cache_type：設置查詢緩存的啟用類型。1 表示啟用查詢緩存。

查看查詢緩存的狀態
你可以通過以下 SQL 命令查看查詢緩存的狀態：

SHOW VARIABLES LIKE 'query_cache%';
SHOW STATUS LIKE 'Qcache%';

這些命令會顯示與查詢緩存相關的配置信息和當前狀態：

  • Qcache_free_blocks：查詢緩存中空閑的塊數。
  • Qcache_hits：查詢緩存命中次數。
  • Qcache_inserts：查詢緩存插入次數。
  • Qcache_lowmem_prunes：查詢緩存由于內存不足而被清理的次數。
  • Qcache_not_cached：未緩存的查詢次數。

3. 查詢緩存的優缺點

優勢

  • 減少數據庫負載：查詢緩存通過緩存 SELECT 查詢的結果，避免了對數據庫的重復訪問，尤其是在讀取密集型應用中。

  • 提高響應速度：查詢緩存使得相同查詢不再執行，而是直接返回緩存結果，減少查詢時間，提升應用性能。

劣勢

  • 緩存失效：當表中的數據發生變化（如 INSERT、UPDATE、DELETE 操作）時，查詢緩存會失效。這意味著緩存可能會在某些操作后被清空或無效，導致重新計算查詢結果。

  • 占用內存：查詢緩存會占用一定的內存空間，特別是在緩存較大的查詢結果時。如果配置不當，可能會導致內存壓力過大。

  • 適用場景限制：查詢緩存對于頻繁變更的數據表效果較差，因為每次數據更新都會導致緩存失效。在高并發的環境中，查詢緩存可能會造成性能瓶頸。

  • 全表掃描問題：對于需要掃描大量數據的查詢，查詢緩存并不能顯著提高性能。

4. 查詢緩存的最佳實踐

適用于讀取密集型的應用

查詢緩存對于那些以讀取操作為主且數據變化不頻繁的應用非常有效。在這種場景下，緩存的查詢結果可以顯著提高應用性能，減少對數據庫的請求。

  • 數據分析報表：如果一個報表的查詢結果不經常改變，查詢緩存可以有效提高查詢速度。

  • 商品信息查詢：電商網站中，商品信息的變化不頻繁，查詢緩存可以用來緩存商品查詢結果，提升響應速度。

不適用于頻繁更新的數據表

查詢緩存不適用于頻繁更新的表，特別是數據表中頻繁的 INSERT、UPDATE 或 DELETE 操作會導致查詢緩存的頻繁失效，降低性能。

  • 電商訂單表：訂單數據頻繁變化，查詢緩存的使用可能會導致性能瓶頸，因為每次更新都會清除緩存。

  • 社交平臺的用戶動態：頻繁的動態數據更新使得查詢緩存無法有效提升性能，甚至可能會造成緩存失效和資源浪費。

五、配置優化

1. 調整連接池大小

根據應用的并發需求調整數據庫連接池的大小，避免連接不足或過多。

-- 連接池配置示例（在 my.cnf 文件中）
[mysqld]
max_connections = 5000

2. 使用慢查詢日志

啟用慢查詢日志，找出執行時間長的查詢，進行針對性優化。

-- 啟用慢查詢日志（在 my.cnf 文件中）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

六、其他優化技巧

1. 避免使用臨時表

臨時表會增加I/O操作，應盡量避免使用。如果必須使用，確保臨時表有適當的索引。

2. 使用批量插入

對于大量數據插入操作，使用批量插入可以顯著提高效率，減少數據庫連接次數和事務開銷。

-- 批量插入示例
INSERT INTO users (username, email) 
VALUES ('user1', 'user1@example.com'), ('user2', 'user2@example.com');

3. 定期優化表

定期使用 OPTIMIZE TABLE 命令優化表結構，減少碎片，提高查詢性能。

-- 優化表
OPTIMIZE TABLE users;

4. 避免使用鎖表

盡量避免使用 LOCK TABLES，以減少鎖爭用，提升并發性能。

七、使用 EXPLAIN 分析查詢

使用 EXPLAIN 語句分析查詢執行計劃，找出查詢中的瓶頸和潛在的優化點。

EXPLAIN 
SELECT * 
FROM orders 
WHERE status = 'completed' AND order_date BETWEEN '2020-01-01' AND '2020-12-31';

通過 EXPLAIN 的輸出，可以了解查詢是如何執行的，包括使用了哪些索引，掃描了多少行等。根據這些信息，可以進一步優化查詢。

總結

  1. 查詢設計：減少數據量，避免復雜計算和函數操作。
  2. 索引使用：合理創建索引，利用覆蓋索引。
  3. 表結構：垂直拆分和水平分區，選擇合適的數據類型。
  4. 配置優化：調整緩存和連接池，啟用慢查詢日志。
  5. 其他技巧：避免臨時表和鎖表，使用批量插入和定期優化表。
  6. 分析工具：使用 EXPLAIN 分析查詢執行計劃。

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

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

相關文章

Mybatis動態sql執行過程

Mybatis動態sql執行過程

動態SQL的執行原理主要涉及到在運行時根據條件動態地生成SQL語句，然后將其發送給數據庫執行。以下是動態SQL執行原理的詳細解釋： 一、接收參數 動態SQL首先會根據用戶的輸入或系統的條件接收參數。這些參數可以是查詢條件、更新數據等，它們…
閱讀更多...
java jar包加密 jar-protect

java jar包加密 jar-protect

介紹 java 本身是開放性極強的語言,代碼也容易被反編譯,沒有語言層面的一些常規保護機制,jar包很容易被反編譯和破解。 受classfinal（已停止維護）設計啟發,針對springboot日常項目開發,重新編寫安全可靠的jar包加殼加密技術,用于保護軟件版權。 使用說…
閱讀更多...
Linux：Git

Linux：Git

Git常見指令： git help xx_command git xx_command --help git --version 查看git版本git config --global user.name "xxx_name" 全局級別的簽名設置，全局的放在本用 git config --global user.ema…
閱讀更多...
【WiFi】WiFi中RSSI、SNR、NF之間關系及說明

【WiFi】WiFi中RSSI、SNR、NF之間關系及說明

RSSI（接收信號強度指示） 定義： RSSI 是一個相對值，用于表示接收到的無線信號的強度。它通常由無線設備的硬件（如無線網卡或無線芯片）直接提供。 計算： RSSI 的計算通常是由設備的無線芯片完成的…
閱讀更多...
提升音頻轉錄準確性：VAD技術的應用與挑戰

提升音頻轉錄準確性：VAD技術的應用與挑戰

引言 在音頻轉錄技術飛速發展的今天，我們面臨著一個普遍問題：在嘈雜環境中，轉錄系統常常將非人聲誤識別為人聲，導致轉錄結果出現錯誤。例如，在whisper模式下，系統可能會錯誤地轉錄出“謝謝大家”。本文將探…
閱讀更多...
[ZMQ] -- ZMQ通信Protobuf數據結構 1

[ZMQ] -- ZMQ通信Protobuf數據結構 1

1、前言背景 工作需要域間實現zmq通信，剛開始需要比較簡單的數據結構，比如兩個bool，后面可能就需要傳輸比較大的數據，所以記錄下實現流程，至于為啥選擇proto數據結構去做大數據傳輸，可能是地平線也用這個&…
閱讀更多...
順序表的使用，對數據的增刪改查

順序表的使用，對數據的增刪改查

主函數： 3.c #include "3.h"//頭文件調用 SqlListptr sql_cerate()//創建順序表函數 {SqlListptr ptr(SqlListptr)malloc(sizeof(SqlList));//在堆區申請連續的空間if(NULLptr){printf("創建失敗\n");return NULL;//如果沒有申請成功&#xff…
閱讀更多...
React和Vue中暴露子組件的屬性和方法給父組件用，并且控制子組件暴露的顆粒度的做法

React和Vue中暴露子組件的屬性和方法給父組件用，并且控制子組件暴露的顆粒度的做法

React 在 React 中，forwardRef 是一種高級技術，它允許你將 ref 從父組件傳遞到子組件，從而直接訪問子組件的 DOM 節點或公開的方法。這對于需要操作子組件內部狀態或 DOM 的場景非常有用。為了使子組件能夠暴露其屬性和方法給父組件&#xf…
閱讀更多...
《C++ 實時視頻流物體跟蹤與行為分析全解析》

《C++ 實時視頻流物體跟蹤與行為分析全解析》

在當今科技飛速發展的時代，視頻監控與智能分析技術在眾多領域發揮著極為重要的作用。從安防監控到智能交通，從工業自動化到人機交互，利用 C 處理實時視頻流中的物體跟蹤和行為分析成為了熱門且極具挑戰性的研究與開發方向。本文將深入探討其中…
閱讀更多...
5G中的隨機接入過程可以不用收RAR？

5G中的隨機接入過程可以不用收RAR？

有朋友提到了一種不用接收RAR的RA過程，問這個是怎么回事。其實在剛剛寫過的LTM cell switch篇章中就有提到，這里把所有相關的內容整理如下。 在RACH-less LTM場景，在進行LTM cell switch之前就要先知道target cell的TA信息，進而才…
閱讀更多...
git 導出某段時間修改的文件 windows

git 導出某段時間修改的文件 windows

第一步：列出兩次commitID之間的文件變動 git diff oldid newid --name-only// 例如 git diff 4a886c57a8b5611a2abcfcd120461c2e92f7029a HEAD --name-only 4a886c57a8b5611a2abcfcd120461c2e92f7029a 代表之前 HEAD 代表最新或者換成某次commitID 例如&#xf…
閱讀更多...
Qt 聯合Halcon配置

Qt 聯合Halcon配置

文章目錄 配置代碼窗口綁定 配置 選擇添加庫 選擇外部庫 LIBS -LC:/Program Files/MVTec/HALCON-17.12-Progress/lib/x64-win64/ LIBS -lhalconcpp\-lhdevenginecpp\-lhalconINCLUDEPATH C:/Program Files/MVTec/HALCON-17.12-Progress/include DEPENDPATH C:/Program Fil…
閱讀更多...
new URL(`../assets/images/${name}`, import.meta.url).href

new URL(`../assets/images/${name}`, import.meta.url).href

背景： 文章講述了Vite框架中關于資源文件（如圖片）在默認配置下，如何正確處理開發環境和打包后的不同引用方式。重點介紹了使用import.meta.url和new URL() 來動態獲取并處理靜態資源URL的方法，以及注意事項&#xff0…
閱讀更多...
8、筆記本品牌分類介紹：LG - 計算機硬件品牌系列文章

8、筆記本品牌分類介紹：LG - 計算機硬件品牌系列文章

LG筆記本品牌以其高性能和先進技術而聞名，?提供多種型號以滿足不同用戶的需求。? LG筆記本產品線包括多種類型，?以滿足不同用戶的需求。?其中，?LG Gram Pro系列以其超薄設計和高性能配置受到關注。?該系列筆記本采用16:10的OLED顯示屏&…
閱讀更多...
367_C++_計算mouse移動過程中，視頻框的右側、底部邊距，以及根據實時的右側、底部邊距計算—視頻框的左上角位置

367_C++_計算mouse移動過程中，視頻框的右側、底部邊距，以及根據實時的右側、底部邊距計算—視頻框的左上角位置

代碼分析 1. restorePos 方法 restorePos 的作用是恢復 NavigationFrame 的位置，將其移動到父窗口或者指定矩形內的特定位置。 void NavigationFrame::restorePos() {// 獲取目標矩形：優先使用 `m_pRect`，否則默認使用視頻區域或父窗口區域RSRect videoRect(m_pVide
閱讀更多...
Tiptap,: 富文本編輯器入門與案例分析

Tiptap,: 富文本編輯器入門與案例分析

Tiptap 是一個現代的富文本編輯器，基于 ProseMirror 打造，旨在提供一個靈活且功能強大的文本編輯解決方案。它具有開箱即用的能力，同時也允許開發者根據業務需求進行高度定制化擴展。與傳統的富文本編輯器相比，Tiptap 提供了更精細…
閱讀更多...
scala的泛型類

scala的泛型類

泛型：類型參數化 泛型類指的是把泛型定義到類的聲明上, 即:該類中的成員的參數類型是由泛型來決定的. 在創建對象時, 明確具體的數據類型. 定義格式: class 類名（成員名：數據類型） class 類名[泛型名](成員名:泛型名) 參考代…
閱讀更多...
對比損失（Contrastive Loss）與大模型：Contrastive Loss and Large Models (中英雙語)

對比損失（Contrastive Loss）與大模型：Contrastive Loss and Large Models (中英雙語)

對比損失（Contrastive Loss）與大模型：從原理到實踐 在現代深度學習中，對比損失（Contrastive Loss）是一種核心技術，尤其是在對比學習（Contrastive Learning）中被廣泛使用…
閱讀更多...
Java基礎學習：java常用啟動命令

Java基礎學習：java常用啟動命令

一、java -jar 1、系統屬性傳遞 使用形式：java -DpathD:\jacoco -jar 獲取方式：System.getProperties() 2、系統參數傳遞 使用形式：java -jar application.jar --jacocoPathD:\tomcat 獲取方式：通過啟動方法入口main的參數arg…
閱讀更多...
Linux下SVN客戶端保存賬號密碼

Linux下SVN客戶端保存賬號密碼

參考文章：解決：Linux上SVN 1.12版本以上無法直接存儲明文密碼_linux svn 保存密碼-CSDN博客新版本svn使用gpg-agent存儲密碼-CSDN博客svn之無法讓 SVN 存儲密碼，即使配置設置為允許_編程設計_ITGUEST 方法一：明文方式保存密碼 首…
閱讀更多...
最新文章

Copyright @ 2022~2023