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
  • （開源）都進來！簡單易懂、功能強大的許可權+可視化流程管理系統
    1、預覽地址：http://139.155.137.144:9012 2、qq群:801913255 一、前言 隨著網路的發展，企業對於信息系統數據的保密工作愈發重視，不同身份、角色對於數據的訪問許可權都應該大相徑庭。 列如 1、不同登錄人員對一個數據列表的可見度是不一樣的，如數據列、數據行、數據按鈕 ...
  • 設計模式-C#實現簡單工廠模式
    前言 上一篇文章寫瞭如何使用RabbitMQ做個簡單的發送郵件項目，然後評論也是比較多，也是準備去學習一下如何確保RabbitMQ的消息可靠性，但是由於時間原因，先來說說設計模式中的簡單工廠模式吧！ 在瞭解簡單工廠模式之前，我們要知道C#是一款面向對象的高級程式語言。它有3大特性，封裝、繼承、多態。 ...
  • Nodify學習 一：介紹與使用
    Nodify學習 一：介紹與使用 - 可樂_加冰 - 博客園 (cnblogs.com) Nodify學習 二：添加節點 - 可樂_加冰 - 博客園 (cnblogs.com) 介紹 Nodify是一個WPF基於節點的編輯器控制項，其中包含一系列節點、連接和連接器組件，旨在簡化構建基於節點的工具的過程 ...
  • 使用C#/.NET解析Wiki百科數據實現獲取歷史上的今天
    創建一個webapi項目做測試使用。 創建新控制器，搭建一個基礎框架，包括獲取當天日期、wiki的請求地址等 創建一個Http請求幫助類以及方法，用於獲取指定URL的信息 使用http請求訪問指定url，先運行一下，看看返回的內容。內容如圖右邊所示，實際上是一個Json數據。我們主要解析 大事記 部 ...
  • .NET科普：.NET簡史、.NET Standard以及C#和.NET Framework之間的關係
    最近在不少自媒體上看到有關.NET與C#的資訊與評價，感覺大家對.NET與C#還是不太瞭解，尤其是對2016年6月發佈的跨平臺.NET Core 1.0，更是知之甚少。在考慮一番之後，還是決定寫點東西總結一下，也回顧一下.NET的發展歷史。 首先，你沒看錯，.NET是跨平臺的，可以在Windows、 ...
  • Nodify學習 二：添加節點
    Nodify學習 一：介紹與使用 - 可樂_加冰 - 博客園 (cnblogs.com) Nodify學習 二：添加節點 - 可樂_加冰 - 博客園 (cnblogs.com) 添加節點（nodes） 通過上一篇我們已經創建好了編輯器實例現在我們為編輯器添加一個節點 添加model和viewmode ...
  • 論如何直接用EF Core實現創建更新時間、用戶審計，自動化樂觀併發、軟刪除和樹形查詢（上）
    前言 資料庫併發，數據審計和軟刪除一直是數據持久化方面的經典問題。早些時候，這些工作需要手寫複雜的SQL或者通過存儲過程和觸發器實現。手寫複雜SQL對軟體可維護性構成了相當大的挑戰，隨著SQL字數的變多，用到的嵌套和複雜語法增加，可讀性和可維護性的難度是幾何級暴漲。因此如何在實現功能的同時控制這些S ...
  • C# 使用模式匹配的好處,因為好用所以推薦~
    類型檢查和轉換：當你需要檢查對象是否為特定類型，並且希望在同一時間內將其轉換為那個類型時，模式匹配提供了一種更簡潔的方式來完成這一任務，避免了使用傳統的as和is操作符後還需要進行額外的null檢查。 複雜條件邏輯：在處理複雜的條件邏輯時，特別是涉及到多個條件和類型的情況下，使用模式匹配可以使代碼更 ...
  • 推薦一種批量判斷文件是否存在的快速辦法
    在日常開發中，我們經常需要和文件打交道，特別是桌面開發，有時候就會需要載入大批量的文件，而且可能還會存在部分文件缺失的情況，那麼如何才能快速的判斷文件是否存在呢？如果處理不當的，且文件數量比較多的時候，可能會造成卡頓等情況，進而影響程式的使用體驗。今天就以一個簡單的小例子，簡述兩種不同的判斷文件是否... ...
  • 論如何直接用EF Core實現創建更新時間、用戶審計，自動化樂觀併發、軟刪除和樹形查詢（中）
    前言 資料庫併發，數據審計和軟刪除一直是數據持久化方面的經典問題。早些時候，這些工作需要手寫複雜的SQL或者通過存儲過程和觸發器實現。手寫複雜SQL對軟體可維護性構成了相當大的挑戰，隨著SQL字數的變多，用到的嵌套和複雜語法增加，可讀性和可維護性的難度是幾何級暴漲。因此如何在實現功能的同時控制這些S ...
所有分類