MySQL常規優化操作

来源:https://www.cnblogs.com/lixingwu/archive/2023/09/20/17716474.html
-Advertisement-
Play Games

查詢SQL語句執行頻率 查詢 mysql 服務啟動時長 SHOW STATUS LIKE 'uptime'; 下列輸出表示服務啟動了276324秒 + + + | Variable_name | Value | + + + | Uptime | 276324 | + + + 查詢全局SQL執行的頻率 ...


查詢SQL語句執行頻率

查詢 mysql 服務啟動時長
SHOW STATUS LIKE 'uptime';

下列輸出表示服務啟動了276324秒

+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Uptime        | 276324 |
+---------------+--------+
查詢全局SQL執行的頻率
-- 執行了多少次select
SHOW GLOBAL STATUS LIKE 'com_select';
-- 執行了多少次insert
SHOW GLOBAL STATUS LIKE 'com_insert';
-- 執行了多少次update
SHOW GLOBAL STATUS LIKE 'com_update';
-- 執行了多少次delete
SHOW GLOBAL STATUS LIKE 'com_delete';
查詢InnoDB引擎的資料庫SQL執行頻率
SHOW GLOBAL STATUS LIKE 'Innodb_rows_%';

輸出

+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| Innodb_rows_deleted  | 0       |
| Innodb_rows_inserted | 7682    |
| Innodb_rows_read     | 4022470 |
| Innodb_rows_updated  | 0       |
+----------------------+---------+

定位低效的查詢SQL

慢查詢記錄
查看實時執行進程
mysql> SHOW PROCESSLIST;

在執行時，一條sql正在執行中，就可以看到SQL的執行情況：

執行進程

如果發現正在執行的Time時間過長，我們就可以把這條低效的SQL拿來進行優化。

執行計劃EXPLAIN

概述

EXPLAIN是SQL語句執行的分析器，在執行一條SQL語句時，我們可以使用EXPLAIN命令查看SQL語句的執行計劃，從而知道SQL語句時如何執行的。

在使用EXPLAIN時，我們只需要在執行的SQL語句前面加上EXPLAIN就可以列印執行計劃：

EXPLAIN SELECT * FROM app_user WHERE email = '[email protected]';

image-20211010224453613

在執行查詢計劃後會輸出以上的一條數據，其中：

1）id 序列號

表結構的執行序列號，序號一樣就從上而下執行，序號不同值，值越大優先順序越高，先執行；

一般簡單查詢只會有一條記錄，連接查詢、子查詢會根據查詢的層次出現多條記錄，這時序號就會就會不同。

2）select_type 查詢類型

如果是連接查詢，嵌套查詢的，每層查詢的類型可能不一樣；

select_type值 說明
SIMPLE 簡單查詢（不使用UNION或子查詢）
PRIMARY 查詢中包含子查詢的，最外層標記為PRIMARY
UNION 第二個SELECT出現在UNION之後，則被標記為UNION
UNION RESULT 在UNION結果中查詢
SUBQUERY 在SELECT或者WHERE中包含子查詢的查詢
DERIVED 在FROM列表中包含子查詢
3）table 引用表

輸出行所引用的表的名稱，可能還會出現以下值：

  • <unionM,N>：引用union結果，M,N表示進行union的兩條記錄的ID；
  • <derivedN>：引用了ID為N的派生結果，例如，派生表可能來自FROM中的子查詢；
  • <subqueryN>：引用了ID為N的子查詢結果。
4）partitions 匹配分區

如果表設置了分區，會顯示數據從哪些分區中查詢，多個分區使用逗號隔開。

5）type 聯結類型

顯示查詢使用了何種類型，按照從最佳到最壞類型排序；

  • NULL：不訪問任何表，比如直接列印 NOW() 函數，就不查詢任何表；

  • system：一次就查詢到了，查詢系統表才會出現；

  • const：通過索引一次就查詢到了，通常使用了主鍵索引、唯一索引的記錄查詢時會出現；

  • eq_ref：常見連接查詢，查詢的結果只出現一條記錄；

  • ref：根據非唯一索引進行條件查詢，匹配到查詢條件的所有行；

  • range：根據檢索的條件，搜索到指定範圍的數據，常見為WHRER出現範圍查詢；

  • index：遍歷了索引樹，但是沒有去遍曆數據，速度比遍曆數據快一點；

  • all：遍歷了全表的數據，查詢最慢；

一般保證查詢至少達到range級別，最好能達到ref。

6）possible_keys 可使用索引

表示數據可以從哪些索引中檢索數據。

7）key 選擇索引

MySQL在possible_keys中最終選擇了那一key進行檢索數據。

8）key_len 鍵長度

MySQL決定使用的key的長度。

9）ref 比較列

顯示將哪些列或常量與鍵列中命名的索引進行比較，以從表中選擇行。

10）rows 查詢行數

根據表統計信息以及索引選用情況，大致估算出找到所需的記錄所需要掃描的記錄數。

11）filtered 過濾百分比

按表條件過濾的表行的估計百分比。最大值為100，這意味著沒有發生行過濾，從100開始減小的值表示過濾量增加。

12）Extra 擴展信息
  • Using filesort：如果MySQL無法使用索引完成排序而使用了文件排序，需要優化；
  • Using temporary：使用了臨時表來來保存了結果，在排序或者分組時沒有使用索引，需要優化；
  • Using index condition：從索引中檢索到數據的坐標，需要回表查詢到具體數據。
  • Using index：從索引中檢索到數據，一般保持這個，不需要做優化；
  • Using where：使用了where條件查詢到的，但是沒有使用索引，建議給查詢條件添加索引；

PROFILES 分析

顯示當前會話過程中執行的語句資源使用信息，一般在調試中使用，比較耗費資源，生產環境不要使用。

我們先使用have_profiling變數查看mysql是否支持PROFILES，如果輸出yes，表示支持：

SELECT @@have_profiling;

查看當前回話是否開啟了profiling，0表示未開啟：

SELECT @@profiling;

當前回話 開啟/關閉profiling

-- 開啟profiling
SET profiling=1;
-- 關閉profiling
SET profiling=0;

我們先開啟profiling，然後執行我們需要分析的SQL語句：

-- 執行SQL
SELECT * FROM `app_user` WHERE `name`='用戶14' OR `phone`='18620769501';

-- 分析
SHOW PROFILES;

輸出內容如下：

image-20211011201453904

由圖片我們可知，上面執行的sql語句，耗時約1.045，如果還想查看每一步執行的耗時，可以使用下麵語句：

SHOW PROFILE FOR QUERY 930;

image-20211011201535347

其中 930 是 SHOW PROFILES 記錄的Query_ID。

trace分析

查看優化器如何選擇執行計劃的，和PROFILES一樣，trace分析非常消耗資源，不建議在生產環境開啟。

我們先開啟trace分析器，把輸出的內容改成json字元串：

-- 開啟trace分析器
SET optimizer_trace="enabled=on",end_markers_in_json=on;
-- 設置記錄的trace的最大值
SET optimizer_trace_max_mem_size=1000000;

在調試完成後記得關閉trace分析器：

SET optimizer_trace="enabled=off";

我們現在執行一條需要分析的SQL語句，讓trace分析器來進行分析：

SELECT * FROM app_user WHERE id = 2;

分析結果記錄到了 information_schema.optimizer_trace 系統表中，我們查詢一下即可得到分析結果：

SELECT * FROM information_schema.optimizer_trace;

image-20211012001458882

TRACE 中大概分為3塊：

  • join_preparation：準備階段，主要包括查詢語句轉換
  • join_optimization：優化階段，處理where條件、依賴檢查、索引評估、掃描行數統計，評估執行計劃
  • join_execution：執行階段，優化後執行

優化 INSERT 語句

  • 在MySQL中，插入多條數據，應該儘量避免使用多個INSERT語句；可以使用多值插入的方式，這種方式大大減少客戶端和資料庫服務連接的次數，比單獨執行多個INSERT語句效率高。
-- 低效的插入
INSERT INTO `login_user`(`loginName`,`loginPwd`) VALUES('1014','12134');
INSERT INTO `login_user`(`loginName`,`loginPwd`) VALUES('1015','12134');
INSERT INTO `login_user`(`loginName`,`loginPwd`) VALUES('1016','12134');

-- 高效的插入
INSERT INTO `login_user`(`loginName`,`loginPwd`) 
VALUES('1014','12134'),('1015','12134'),('1016','12134');
  • 在事務中進行插入，避免每次插入都進行提交，如果數據量比較大，可進行分段提交。
-- 低效的插入
INSERT INTO `login_user`(`loginName`,`loginPwd`) VALUES('1017','12134'),('1018','12134');
INSERT INTO `user_info`(`userId`,`mobile`) VALUES(1,'13800001017'),(1,'13800001018');

-- 使用手動事務提交，高效的插入
BEGIN;
INSERT INTO `login_user`(`loginName`,`loginPwd`) VALUES('1019','12134'),('1020','12134');
INSERT INTO `user_info`(`userId`,`mobile`) VALUES(1,'13800001019'),(1,'13800001020');
COMMIT;

ORDER BY 優化

  • 在排序時儘量避免回表查詢。在根據索引進行排序時，查詢的欄位如果沒有添加索引，會導致排序使用文件排序，索引失效；
-- 索引失效，進行文件排序
EXPLAIN SELECT * FROM `app_user` ORDER BY `name`;

image-20211013223449789

  • 如果查詢的欄位有索引，排序時會直接使用索引，不需要進行文件排序，效率較高。
-- 使用索引排序
EXPLAIN SELECT `id`,`name` FROM `app_user` ORDER BY `name`;

image-20211013223516173

  • 多欄位排序，要麼同時使用升序，要麼同時使用降序，多欄位避免使用不同的排序。
-- 多欄位同排序
EXPLAIN SELECT `id`,`name`,`phone`,`age` FROM `app_user` ORDER BY `name`,`phone`;
EXPLAIN SELECT `id`,`name`,`phone`,`age` FROM `app_user` ORDER BY `name` DESC,`phone` DESC;

image-20211013223552733

-- 多欄位不同排序，會使用文件排序
EXPLAIN SELECT `id`,`name`,`phone`,`age` FROM `app_user` ORDER BY `name` DESC,`phone` ASC;

image-20211013223618799

  • filesort排序優化，MySQL在進行文件排序filesort時，會根據變數max_length_for_sort_data來判斷使用什麼樣的排序演算法，小於這個變數使用單路排序，大於這個變數使用雙路排序；我們可以在配置文件中適當調大這個值，讓filesort更容易使用單路排序，但需要註意的是這會導致消耗資源增加。
-- 查看使用排序演算法的分界值，單位：位元組
SHOW VARIABLES LIKE 'max_length_for_sort_data';
-- 查看排序緩存的大小，單位：位元組
SHOW VARIABLES LIKE 'sort_buffer_size';

image-20211013225050908

GROUP BY 優化

  • MySQL在進行GROUP BY時，會先進行排序操作，我們可以手動禁止排序，讓TA直接進行分組操作。
-- 未添加緩存，未去除排序，自動使用了文件排序
EXPLAIN SELECT * FROM app_user GROUP BY `age`;

image-20211017194426144

-- 未添加緩存，禁止自動排序操作，耗時更短
EXPLAIN SELECT * FROM app_user GROUP BY `age` ORDER BY NULL;

image-20211017194653670

  • 使用索引欄位進行分組和排序，也可避免使用文件排序。
-- 未使用索引欄位進行分組
EXPLAIN SELECT `password`,COUNT(`password`) FROM app_user GROUP BY `password`;

image-20211017205520342

-- 使用了索引欄位進行分組
EXPLAIN SELECT `name`,COUNT(`name`) FROM app_user GROUP BY `name`;

image-20211017205552628

嵌套查詢優化

儘量的使用多表連接查詢替代子查詢，避免子查詢產生的中間表。

-- 子查詢
EXPLAIN SELECT * FROM user_info WHERE userId IN(
	SELECT id FROM user WHERE dept_id=1
);

-- 內連接查詢替換子查詢
EXPLAIN SELECT i.* FROM user_info AS i
INNER JOIN user AS u ON u.id = i.userId
WHERE u.dept_id=1;

優化OR條件

對於包含了OR的查詢語句，如果要利用索引，則OR之間的每個條件列都必須使用索引（複合索引也必須滿足最左首碼原則）；如果沒有索引，則應該考慮添加索引。

-- OR,其中age沒有索引，全部索引失效
EXPLAIN SELECT * FROM app_user WHERE email='00' OR age=10;

image-20211018171039786

-- OR,email單列索引和name複合索引，都有索引，索引生效
EXPLAIN SELECT * FROM app_user WHERE email='00' OR name='用戶';

image-20211018171215684

建議使用UNION替換OR，避免其中有列沒有索引，引起全部索引失效。

-- email有索引，age沒有索引
-- 1層查詢走了索引，2層查詢沒有走索引，避免了全部索引失效
EXPLAIN SELECT * FROM app_user WHERE email='[email protected]' UNION SELECT * FROM app_user WHERE age=10;

image-20211018171729303

分頁查詢優化

在進行分頁時，如果在前幾頁的數據，查詢速度還算可以；

但是越往後面，查詢的數據就會越慢，這是因為，MySQL會根據條件查詢出符合的數據，最後才進行分頁操作，而前面查詢的記錄都被丟棄了，造成了大量的浪費。

假設查詢了第9萬頁的數據，MySQL會查詢出9萬頁的全部數據，然後丟棄前8.9萬的數據，返回第9萬頁的數據。

-- LIMIT 越往後，速度越慢
EXPLAIN SELECT * FROM app_user LIMIT 9000,10;

image-20211018175426584

針對這種情況我們可以，先在索引上完成分頁，然後再使用連接查詢篩選出數據，全在索引上進行分頁。

-- 把分頁操作移到索引上去操作，
-- 把以前的993537條記錄從數據文件上篩選，修改為在索引上進行篩選
EXPLAIN SELECT * FROM app_user AS u
INNER JOIN (
	SELECT id FROM app_user LIMIT 9000,10
) t
ON t.id = u.id;

image-20211018175537507

如果在主鍵自增的列上，我們可以把分頁的頁修改為條件，讓它直接在條件處就丟棄多餘的數據，但是這個情況下，數據順序不能變動，並且數據自增的序號還不能斷層，否則查詢的數據就不正確；如果滿足條件就使用，此方式查詢效率最高。

EXPLAIN SELECT * FROM app_user WHERE id>900000 LIMIT 10;

image-20211018180449991

記憶體優化

MyISAM 引擎記憶體使用 key_buffer 緩存索引塊，數據塊則直接讀取磁碟文件，我們可以調整以下參數，讓MyISAM 能緩存更多的索引。

  • key_buffer_size：緩存索引區大小，一般設置為MySQL記憶體的四分之一；
  • read_buffer_size：每個連接全表掃描緩存；
  • read_rnd_buffer_size：每個連接多欄位排序緩存；

InnoDB用記憶體區做緩存池，緩存了索引和數據塊，占用記憶體較大。

  • innodb_buffer_pool_size：緩存池的大小，儘量調大；
  • innodb_log_buffer_size：用於日誌緩存，調大避免在事務期間去進行io操作；

併發參數

  • max_connections：允許鏈接MySQL服務的最大連接數，超過最大限制的將被積壓到請求棧中；
  • back_log：積壓請求棧大小（連接數），超過這個值，連接會直接返回錯誤；
  • table_open_cache：緩存表的數量，一般為 max_connections 乘以關聯查詢表數；
  • thread_cache_size：服務端緩存線程池大小，便於客戶端快速連接；
  • innodb_lock_wait_timeout：事務行鎖等待時間，快速反應的系統調小，大事務的系統調大，此處單位為秒；
原創內容，如果你覺得文章還可以的話，不妨點個贊支持一下！轉載請註明出處。
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 【解惑】孜孜不倦，用足球賽程詳解c#中的yield return用法
    近些年來，隨著WPF在生產，製造，工業控制等領域應用越來越廣發，很多企業對WPF開發的需求也逐漸增多，使得很多人看到潛在機會，不斷從Web，WinForm開發轉向了WPF開發，但是WPF開發也有很多新的概念及設計思想，如：數據驅動，數據綁定，依賴屬性，命令，控制項模板，數據模板，MVVM等，與傳統Wi... ...
  • 2.簡單的搭建後端，一步一步從基礎開始(2023-9-20優化更新第一次)
    上傳Git的忽略文件下載 千萬不能忘記配置忽略文件，不然可能會搞得你一個項目10多個G，很煩人 先梳理下我們需要新建的項目如下。介面層一般I(i)開頭，實現層不需要。後面還會增加擴展類或者其他的。 API程式層：FastEasyAPI 服務介面層：FastEasy.IService 服務實現層：Fa ...
  • 首次運行pyppeteer遇到報錯的定位思路與處理方法，MaxRetryError: HTTPSConnectionPool(host='storage.googleapis.com', port=443)
    一、Linux 的介紹 1）常見的操作系統 Windows，它微軟公司開發的一款桌面操作系統（閉源系統）。版本有dos、win98、win NT、win XP、win 7、win vista、win 8、win 10。伺服器操作系統：win server 2003、win server 2008、w ...
  • linux關閉終端，後臺程式不退出
    你要退出終端會話時，要是存在被停止的進程，會出現警告信息。但如果使用了後臺進程，只有某些終端模擬器會在你退出終端會話前提醒你還有後臺作業在運行。 如果希望運行在後臺模式的腳本在登出控制台後能夠繼續運行，我再一篇文章中看到，有方法可以實現。 有時你會想在終端會話中啟動shell腳本，然後讓腳本一直以後 ...
  • 讀高性能MySQL（第4版）筆記12_查詢性能優化（下）
    1. “快速、精確和實現簡單” 1.1. 三者永遠只能滿足其二，必須舍掉一個 2. 排序優化 2.1. 無論如何排序都是一個成本很高的操作，所以從性能角度考慮，應儘可能避免排序或者儘可能避免對大量數據進行排序 2.2. 文件排序（filesort） 2.2.1. MySQL需要自己進行排序，如果數據 ...
  • MySQL高級12-事務原理
    一、事務概念 事務是一組操作的集合，他是一個不可分割的工作單位，事務會把所有操作作為一個整體一起向系統提交或者撤銷請求操作，即這些操作要麼同時成功，要麼同時失敗。 二、事務特性 原子性（Atomicity）：事務是不可分割的最小操作單元，要麼全部成功，要麼全部失敗 一致性（Consistency）： ...
  • MySQL的欄位數量以及長度限制
    一、InnoDB行格式 行格式 緊湊的存儲特性 增強的可變長度列存儲 大型索引鍵首碼支持 壓縮支持 支持的表空間類型 REDUNDANT N N N N system, file-per-table, general COMPACT Y N N N system, file-per-table, g ...
  • 圖文結合丨GreatSQL MGR + ProxySQL集群搭建方案
    前言 ProxySQL ProxySQL 是基於 MySQL 的一款開源的中間件的產品，是一個靈活的 MySQL 代理層，可以實現讀寫分離，支持 Query 路由功能，支持動態指定某個 SQL 進行緩存，支持動態載入（無需重啟 ProxySQL 服務），故障切換和一些 SQL 的過濾功能。 Grea ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類