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
  • Dapr Outbox 執行流程
    Dapr Outbox 是1.12中的功能。 本文只介紹Dapr Outbox 執行流程，Dapr Outbox基本用法請閱讀官方文檔 。本文中appID=order-processor，topic=orders 本文前提知識：熟悉Dapr狀態管理、Dapr發佈訂閱和Outbox 模式。 Outbo ...
  • Advanced .Net Debugging 7：托管堆與垃圾收集
    引言 在前幾章我們深度講解了單元測試和集成測試的基礎知識，這一章我們來講解一下代碼覆蓋率,代碼覆蓋率是單元測試運行的度量值,覆蓋率通常以百分比表示，用於衡量代碼被測試覆蓋的程度，幫助開發人員評估測試用例的質量和代碼的健壯性。常見的覆蓋率包括語句覆蓋率（Line Coverage）、分支覆蓋率（Bra ...
  • C#S7.NET實現西門子PLCDB塊數據採集的完整步驟
    前言 本文介紹瞭如何使用S7.NET庫實現對西門子PLC DB塊數據的讀寫，記錄了使用電腦模擬，模擬PLC，自至完成測試的詳細流程，並重點介紹了在這個過程中的易錯點，供參考。 用到的軟體： 1.Windows環境下鏈路層網路訪問的行業標準工具（WinPcap_4_1_3.exe）下載鏈接：http ...
  • WPF控制項：密碼框綁定MVVM
    從依賴倒置原則（Dependency Inversion Principle, DIP）到控制反轉（Inversion of Control, IoC）再到依賴註入（Dependency Injection, DI）的演進過程，我們可以理解為一種逐步抽象和解耦的設計思想。這種思想在C#等面向對象的編 ...
  • Python中的私有屬性與私有方法
    關於Python中的私有屬性和私有方法 Python對於類的成員沒有嚴格的訪問控制限制，這與其他面相對對象語言有區別。關於私有屬性和私有方法，有如下要點： 1、通常我們約定，兩個下劃線開頭的屬性是私有的（private）。其他為公共的（public）； 2、類內部可以訪問私有屬性（方法）； 3、類外 ...
  • C++ 訪問說明符詳解：封裝數據，控制訪問，提升安全性
    C++ 訪問說明符 訪問說明符是 C++ 中控制類成員（屬性和方法）可訪問性的關鍵字。它們用於封裝類數據並保護其免受意外修改或濫用。 三種訪問說明符： public：允許從類外部的任何地方訪問成員。 private：僅允許在類內部訪問成員。 protected：允許在類內部及其派生類中訪問成員。 示 ...
  • static_cast與dynamic_cast到底是什麼？
    寫這個隨筆說一下C++的static_cast和dynamic_cast用在子類與父類的指針轉換時的一些事宜。首先，【static_cast，dynamic_cast】【父類指針，子類指針】，兩兩一組，共有4種組合：用 static_cast 父類轉子類、用 static_cast 子類轉父類、使用 ...
  • C語言數據結構：雙向鏈表的增刪操作
    /******************************************************************************************************** * * * 設計雙向鏈表的介面 * * * * Copyright (c) 2023-2 ...
  • @ComponentScan註解的實現，Spring掃描包的過程
    相信接觸過spring做開發的小伙伴們一定使用過@ComponentScan註解 @ComponentScan("com.wangm.lifecycle") public class AppConfig { } @ComponentScan指定basePackage，將包下的類按照一定規則註冊成Be ...
  • opensips開啟python支持
    操作系統 ：CentOS 7.6_x64 opensips版本： 2.4.9 python版本：2.7.5 python作為腳本語言，使用起來很方便，查了下opensips的文檔，支持使用python腳本寫邏輯代碼。今天整理下CentOS7環境下opensips2.4.9的python模塊筆記及使用 ...
所有分類