MySQL8.0性能優化（實踐）

来源:https://www.cnblogs.com/Sol-wang/archive/2023/01/30/17076128.html
-Advertisement-
Play Games

一、背景 二、概述 三、下線流程與原理 1.讀取待下線節點列表 2.判斷節點下線模式 3.設置超時時間 4.RMNode 處理下線事件 5.監控節點的狀態、下線節點 四、相關的Yarn集群配置 一、背景 接手部門 Hadoop 和 Flink 集群半年了，一直忙著上雲的事兒，很少有時間去琢磨運維的事 ...


一臺幾年前的舊筆記本電腦的虛擬系統運行環境，作為本次實踐的運行工具，僅供參考。

案例環境：Linux、Docker、MySQLCommunity8.0.31、InnoDB。

過早的MySQL版本不一定適用本章內容，僅圍繞 InnoDB 引擎的闡述。

一、索引

1.1 索引的管理

-- create 方式創建
create [unique] index {index_name} on {tab_name}({col_name}[(length)]);
-- alter表 方式創建
alter {tab_name} add [unique] index {index_name} on ({col_name}[(length)]);
-- 創建組合索引
create index {index_name} on ({col_name1}[(length)], {col_name2}[(length)], {col_name3}[(length)]);

-- unique：唯一索引
-- col_name：一列為單列索引；逗號隔開的多列為組合索引
-- length：欄位中前幾個字元有效，避免無限長度（通常能夠明顯區分值即可的長度；如：員工表的Email，@後面都一樣）


-- 查看表中的索引
show index from {tab_name};

-- 刪除索引
drop index {index_name} on {tab_name};

1.2 索引創建的場景

過多查詢的表，過少寫入的表。

數據量過大導致的查詢效率慢。

經常作為條件查詢的列。

批量的重覆值，不適合創建索引；比如<業務狀態>列

值過少重覆的列，適合創建索引；比如<usercode>、<email>列

1.3 理想的索引特征

  • 儘量能夠覆蓋常用欄位
  • 欄位值區分度高
  • 欄位長度小（合適的長度，不是越小越好，至少能足夠區分每個值）
  • 相對低頻的寫入操作，以及高頻的查詢操作的表和欄位上建立索引

通過非聚集索引檢索記錄的時候，需要2次操作，先在非聚集索引中檢索出主鍵，然後再到聚集索引中檢索出主鍵對應的記錄，這個過程叫做回表，比聚集索引多了一次操作。

1.4 非主鍵索引

where全部為and時，無所謂位置，都會命中索引（當多個條件中有索引的時候，並且關係是and的時候，會自動匹配索引區分度高的）

where後面為 or 時，索引列 依影響數據範圍越精確 按序靠前寫。

1.5 索引的使用

使用原則：

  • 按條件後面涉及到的列，創建出組合索引
  • 越精確的條件，就排在條件的順序首位，最左匹配原則
-- 按現有數據，計算哪個列最精確；越精確的列，位置越靠前優先。
 select sum(depno=28), sum(username like 'Sol%'), sum(position='dev') from tab_emp;
 +---------------+---------------------------+---------------------+
 | sum(depno=28) | sum(username like 'Sol%') | sum(position='dev') |
 +---------------+---------------------------+---------------------+
 | 366551        | 3                         | 109                 |
 +---------------+---------------------------+---------------------+
-- 由此得出：username列的範圍最精確，應該放到where後的首位；不在組合索引的列放到最後。

-- 如下組合索引的創建方式：
create index {index_name} on {tab_name}(username,position,depno);
-- 如下組合索引的查詢方式：
select username,position,depno from tab_emp where username like 'Sol%' and position='dev' and depno=106 and age<27;

1.5.1 使用索引查詢

這裡準備兩張兩千萬相同表數據，測試效果如下圖：

1.5.2 組合索引的使用

表創建的組合索引，如下圖：

兩千萬數據表，組合索引查詢效果，如下圖：

總結：組合索引所包含的列，儘量在where, order中寫全，非索引列或過少的組合索引列可能不會產生索引效果。

1.5.3 高性能分頁查詢

通常MySQL分頁時用到的limit，當limit值過大時，查詢效果會很慢。

當如 limit 9000000,10 時，需要先查詢出900萬數據，再拋掉900萬數據；這900萬的過程可否省略？

假如：每次查詢一頁時，把當前頁的最後一條數據的重要欄位都做記錄，並標識是第幾頁；當查詢它的下頁時，拿它的最後一條數據的重要欄位作為追加的查詢條件，如何呢...??

下圖示例：usercode 為主要的索引及排序欄位，上頁的最後一條作為追加條件，再往下取5條，效果有了顯著提升。(排序列重覆數據呢?) 當然適用於類似code、time等這樣重覆數據較少的列。

1.6 索引覆蓋，避免回表查詢

當查詢的列中包含了非索引列，系統相當於掃描了兩遍數據，如果能只掃描了一遍，也提高了查詢效率。

回表查詢的過程：

  1. 先按已有索引查詢到數據，得出此數據的主鍵值
  2. 再按主鍵值，再次檢索出具體的數據，獲取其它列的值

查詢涉及到的列都為組合索引列時，包括：select、where、order、group等，索引覆蓋(索引下推)，避免回表查詢。

避免使用*，以避免回表查詢；不常用的查詢列或text類型的列，儘量以單獨的擴展表存放。

通常列表數據需要的列並不多，查詢的時候可以考慮為索引列；通常詳細信息時涵蓋的列多，可通過主鍵單獨查詢。

1.7 命中索引

1.7.1 無效索引

列類型轉換可能會導致索引無效；如：

  • 字元轉數值，會導致索引無效
  • 數值轉字元，不影響索引。

不建議類型的轉換，儘量按原類型查詢。

條件中的函數導致索引無效；索引列不能用在函數內。如：where abs(Id) > 200

條件中的表達式導致索引無效；如：where (Id + 1) > 200

避免單列索引與組合索引的重覆列；在組合索引中的列，去除單列索引。

全模糊查詢導致索引無效；匹配開頭不會影響索引，如 'Sol%'；全模糊或'%Sol'時無效。

1.7.2 Explain

顯示執行過程，查看是否命中索引

mysql> explain select * from tab_emp where uname='Sol';
-- 可能用到的索引、實際用到的索引、掃描了的行數
+----+-------------+---------+-------+---------------+---------------+---------+-------+------+-----------------------+
| id | select_type | table   | type  | possible_keys | key           | key_len | ref   | rows | Extra                 |
+----+-------------+---------+-------+---------------+---------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab_emp | range | idx_emp_uname | idx_emp_uname | 4       | const |    1 | Using index condition |
+----+-------------+---------+-------+---------------+---------------+---------+-------+------+-----------------------+

在通常情況下，能不能命中索引，取決於索引列的值重覆程度；如果是極少重覆的值，就很容易命中索引。如果類似於狀態或類型的值，重覆程度很高，就很難命中索引，這是MySQL自動取捨的結果。

比如：沒有索引的列-電話號碼，有索引的列-部門，那麼很難命中部門索引，因為MySQL認為[電話號碼]更精確；或者使用force強行命中，通常MySQL的自動取捨是最有效的。

1.8 查詢總結

避免使用*，以避免回表查詢。

不常用的查詢列或text類型的列，儘量以單獨的擴展表存放。

條件避免使用函數。

條件避免過多的or，建議使用in()/union代替，in中的數據不可以極端海量，至少個數小於1000比較穩妥。

避免子查詢，子查詢的結果集是臨時表不支持索引、或結果集過大、或重覆掃描子表；以join代替子查詢，儘量以inner join代替最為妥當。

避免使用'%Sol%'查詢，或以'Sol%'代替。

二、表分區

表分區也就是把一張物理表的數據文件分成若幹個數據文件存儲，使得單個數據文件的量有限，有助於避免全表掃描數據，提升查詢性能。

那，跨區查詢的性能影響有多大，從整體看，表分區還是帶來了不少的性能提升。

如果表中有主鍵列，分區列必須是主鍵列之一。比如：又有自增主鍵，又想按年份分區，那主鍵就是組合索引咯。(id+date)

2.1 分區的種類

HASH：按演算法，平均分配到各分區

-- 表創建 HASH 分區12個
CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH(MONTH(signed))
PARTITIONS 12;

KEY：按演算法，無序不等的分配到各分區

-- 表創建12個 KEY 分區
CREATE TABLE clients_lk (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY LINEAR KEY(signed)
PARTITIONS 12;

RANGE：按劃定的範圍將數據存放到符合的分區

-- 按年份創建範圍分區
CREATE TABLE tr (
    id INT,
    name VARCHAR(50),
    purchased DATE
)
PARTITION BY RANGE(YEAR(purchased)) (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (2000)
);

LIST：按定義的一組包含值將數據存放到符合的分區

-- LIST 分組包含方式
CREATE TABLE tt (
    id INT,
    data INT
)
PARTITION BY LIST(data) (
    PARTITION p0 VALUES IN (5, 10, 15),
    PARTITION p1 VALUES IN (6, 12, 18)
);

2.2 分區的管理

新增 HASH/KEY 分區

-- 將原來的 12 個分區合併為 8 個分區
ALTER TABLE clients COALESCE PARTITION 4;
-- 在原有的基礎上增加 6 個分區
ALTER TABLE clients ADD PARTITION PARTITIONS 6;

新增 RANGE/LIST 分區

-- RANGE 追加分區
ALTER TABLE tr ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));
-- LIST 追加新分區（不可包含已存在的值）
ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

變更 RANGE/LIST 分區

-- RANGE 拆分原有分區（重組分區）
ALTER TABLE tr REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1980),
        PARTITION n1 VALUES LESS THAN (1990)
);
-- RANGE 合併相鄰分區
ALTER TABLE tt REORGANIZE PARTITION s1,s2 INTO (
    PARTITION s0 VALUES LESS THAN (1980)
);
-- LIST 重組原有分區
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);

刪除指定分區

-- 丟掉指定分區及其數據
ALTER TABLE {TABLE_NAME} DROP PARTITION p2,p3;
-- 刪除指定分區，保留數據
ALTER TABLE {TABLE_NAME} TRUNCATE PARTITION p2;
-- 刪除表全部分區，保留數據
ALTER TABLE {TABLE_NAME} REMOVE PARTITIONING;

分區詳細信息

-- 查詢指定分區的數據
SELECT * FROM tr PARTITION (p2);
-- 查詢各分區詳細
SELECT * FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tt';
-- 查看某個分區的狀態
ALTER TABLE tr ANALYZE PARTITION p3;

修複分區

-- 檢查分區是否損壞
ALTER TABLE tr CHECK PARTITION p1;
-- 修複分區
ALTER TABLE tr REPAIR PARTITION p1, p2;
-- 優化分區，整理分區碎片
ALTER TABLE tr OPTIMIZE PARTITION p0, p1;
-- 當前分區數據，重建分區
ALTER TABLE tr REBUILD PARTITION p0, p1;

三、查詢綜合測試

2000萬相同數據、相同表結構，相同的查詢方式，測試效果如下圖：(僅供參考)

數據量大了，查詢慢；加索引了，數據量越大，寫入越慢；

還是物理分表好呀~

四、SQL服務參數優化

僅列出了點官方認可的穩定性良好的可靠的參數，以 InnoDB 為主。

4.1 Connections

[mysqld]
# 保持在緩存中的可用連接線程
# default = -1（無）
thread_cache_size = 16
# 最大的連接線程數(關係型資料庫)
# default = 151
max_connections = 1000
# 最大的連接線程數(文檔型/KV型)
# default = 100
#mysqlx_max_connections = 700

4.2 緩衝區 Buffer

[mysqld]
# 緩衝區單位大小；default = 128M
innodb_buffer_pool_size = 128M
# 緩衝區總大小，記憶體的70%，單位大小的倍數
# default = 128M
innodb_buffer_pool_size = 6G
# 以上兩個參數的設定，MySQL會自動改變 innodb_buffer_pool_instances 的值

4.3 Sort merge passes

[mysqld]
# 優化 order/group/distinct/join 的性能
# SHOW GLOBAL STATUS 中的 Sort_merge_passes 過多就增加設置
# default = 1K
max_sort_length = 8K
# default = 256K
sort_buffer_size = 2M
# 通常別太大，海量join時大
# default = 256K
#join_buffer_size = 128M

4.4 I/O 線程數

[mysqld]
# 非同步I/O子系統
# default = NO
innodb_use_native_aio = NO
# 讀數據線程數
# default = 4
innodb_read_io_threads = 32
# 寫入數據線程數
# default = 4
innodb_write_io_threads = 32

4.5 Capacity 容量

[mysqld]
# default = 200
innodb_io_capacity = 1000
# default = 2000
innodb_io_capacity_max = 2500
# 數據日誌容量值越大，恢複數據越慢
# default = 100M
innodb_redo_log_capacity = 1G
# 數據刷新到磁碟的方式
# < 有些同學說用 O_DSYNC 方式，在寫入時，有很大提升。可官網說：
# < InnoDB does not use O_DSYNC directly because there have been problems with it on many varieties of Unix.
# < 也就是少部分系統可以穩定使用，或者已經過系統驗證確認。
# < 個人認為，預設值最可靠
# innodb_flush_method = fsync

4.6 Open cache

[mysqld]
# default = 5000
open_files_limit = 10000
# 計算公式：MAX((open_files_limit-10-max_connections)/2,400)
# default = 4000
table_open_cache = 4495
# 超過16核的硬體，肯定要增加，以發揮出最大性能
# default = 16
table_open_cache_instances = 32

五、寫入綜合測試

測試目的：

經過【四、SQL服務參數優化】的配置後，分別測試空表狀態批量寫入200萬和500萬數據的耗時。

測試場景：

一臺幾年前的破筆記本，創建的虛擬機4C8G，Docker + MySQL8.0.31。

桌面應用以36個線程寫入隨機數據。

批量寫入腳本：INSERT INTO TABLE ... VALUES (...),(...),(...) 的方式，INSERT 每次1000條。

表結構：聚集索引 + 兩列的非聚集索引 + 一組三列的組合索引；(參照 1.5.2)

+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| id         | bigint       | NO   | PRI | NULL              | auto_increment    |
| usercode   | varchar(32)  | YES  | MUL | NULL              |                   |
| title      | varchar(128) | YES  |     | NULL              |                   |
| age        | int          | YES  | MUL | NULL              |                   |
| gender     | char(1)      | YES  |     | 男                |                   |
| phone      | char(11)     | YES  |     | NULL              |                   |
| job        | varchar(32)  | YES  |     | NULL              |                   |
| department | varchar(32)  | YES  |     | NULL              |                   |
| createtime | datetime     | NO   | PRI | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+

測試結果：

逐步追加MySQL服務參數配置+表分區，最終有了成倍的性能提升；每次測試後的日誌記錄了優化的遞進過程；
如下圖：(日誌不夠細，懂就行)

經過逐步優化：

  200萬數據寫入耗時從 9分4秒，提升到 5分50秒；(無表分區)

  500萬數據寫入耗時從 41分33秒，提升到 6分50秒。(有表分區)

鄙人拙見，有不妥望指出，萬分感謝。
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Python工具箱系列(二十三)
    在新版本的pandas中，上述代碼會引起警告，建議改成SQLAlchemy connectable(engine/connection)，後續代碼將引入這種升級的連接方式。 ...
  • 讀Java8函數式編程筆記04_類庫
    *以下內容為本人的學習筆記，如需要轉載，請聲明原文鏈接 微信公眾號「englyf」https://mp.weixin.qq.com/s/2GFLTstDC7w6u3fTJxflNA 本文大概 1685 個字，閱讀需花 6 分鐘內容不多， 但也花了一些精力如要交流， 歡迎關註我然後評論區留言 謝謝你的 ...
  • 公司規定所有介面都用 post 請求，這正確麽？
    目錄 背景 get 與 post 的區別 所有介面都用 post 請求？ 背景 最近在逛知乎的時候發現一個有趣的問題：公司規定所有介面都用 post 請求，這是為什麼？ 看到這個問題的時候其實我也挺有感觸的，因為我也曾經這樣問過我自己。在上上一家公司的時候接到一個項目是從零開始搭建一個微服務，當時就 ...
  • 學習ASP.NET Core Blazor編程系列二十三——登錄（2）
    前言 .NET6 開始，.NET Croe API 項目取消了 Startup.cs 文件，在 Program.cs 文件的 Main 函數中完成服務的註冊和中間件管道的管理。但當我們項目引入更多包的時候，Program.cs 文件也會看起來很臃腫。 而且，我們不只會有一個後端項目，為了方便快速創建 ...
  • C# 托管堆 遭破壞 問題溯源分析
    一：背景 1. 講故事 年前遇到了好幾例托管堆被損壞的案例，有些運氣好一些，從被破壞的托管堆記憶體現場能觀測出大概是什麼問題，但更多的情況下是無法做出準確判斷的,原因就在於生成的dump是第二現場，借用之前文章的一張圖，大家可以理解一下。 為了幫助更多受此問題困擾的朋友，這篇來整理一下如何 快狠準 的 ...
  • CRLF 文件從Window轉到Unix中換行符的轉換問題解決
    1.背景知識 CRLF用來表示文本換行的方式 ，CR是回車的意思，對應 \r ；LF 是換行的意思，對應 \n Windows 換行符是 \r\n Unix 換行是 \n 如果一個將要在Linux伺服器上使用的文件，比如某個配置文件，在windows電腦打開，編輯過，那麼文件每一行末尾會多有\r 。 ...
  • 什麼是乙太網
    一：什麼是乙太網： 乙太網是一種區域網技術，乙太網是一種用於數據鏈路層的協議類型。 二：乙太網的由來： 乙太網是美國施樂(Xerox)公司的Palo Alto研究中心(簡稱為PARC)於1975年研製成功的。那時乙太網是一種基帶匯流排區域網，當時的數據率為2.94 Mbit/s。乙太網用無源電纜作為總 ...
  • 什麼是網卡
    網卡 網卡，又叫做通信適配器(adapter)，早期的時候是插在機箱裡面的網路介面板，這種介面板又稱為網路介面卡NIC(Network Interface Card)或簡稱為“網卡”。因為後面集成到主板上了，所以又叫做通信適配器(adapter)。 網卡的作用： 電腦和外界區域網進行連接是通過通信 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類