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. 再按主鍵值,再次檢索出具體的數據,獲取其它列的值

查詢涉及到的列都為組合索引列時,包括:selectwhereordergroup等,索引覆蓋(索引下推),避免回表查詢。

避免使用*,以避免回表查詢;不常用的查詢列或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
更多相關文章
  • 在新版本的pandas中,上述代碼會引起警告,建議改成SQLAlchemy connectable(engine/connection),後續代碼將引入這種升級的連接方式。 ...
  • *以下內容為本人的學習筆記,如需要轉載,請聲明原文鏈接 微信公眾號「englyf」https://mp.weixin.qq.com/s/2GFLTstDC7w6u3fTJxflNA 本文大概 1685 個字,閱讀需花 6 分鐘內容不多, 但也花了一些精力如要交流, 歡迎關註我然後評論區留言 謝謝你的 ...
  • 目錄 背景 get 與 post 的區別 所有介面都用 post 請求? 背景 最近在逛知乎的時候發現一個有趣的問題:公司規定所有介面都用 post 請求,這是為什麼? 看到這個問題的時候其實我也挺有感觸的,因為我也曾經這樣問過我自己。在上上一家公司的時候接到一個項目是從零開始搭建一個微服務,當時就 ...
  • 前言 .NET6 開始,.NET Croe API 項目取消了 Startup.cs 文件,在 Program.cs 文件的 Main 函數中完成服務的註冊和中間件管道的管理。但當我們項目引入更多包的時候,Program.cs 文件也會看起來很臃腫。 而且,我們不只會有一個後端項目,為了方便快速創建 ...
  • 一:背景 1. 講故事 年前遇到了好幾例托管堆被損壞的案例,有些運氣好一些,從被破壞的托管堆記憶體現場能觀測出大概是什麼問題,但更多的情況下是無法做出準確判斷的,原因就在於生成的dump是第二現場,借用之前文章的一張圖,大家可以理解一下。 為了幫助更多受此問題困擾的朋友,這篇來整理一下如何 快狠準 的 ...
  • 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
  • 概述:在C#中,++i和i++都是自增運算符,其中++i先增加值再返回,而i++先返回值再增加。應用場景根據需求選擇,首碼適合先增後用,尾碼適合先用後增。詳細示例提供清晰的代碼演示這兩者的操作時機和實際應用。 在C#中,++i 和 i++ 都是自增運算符,但它們在操作上有細微的差異,主要體現在操作的 ...
  • 上次發佈了:Taurus.MVC 性能壓力測試(ap 壓測 和 linux 下wrk 壓測):.NET Core 版本,今天計劃準備壓測一下 .NET 版本,來測試並記錄一下 Taurus.MVC 框架在 .NET 版本的性能,以便後續持續優化改進。 為了方便對比,本文章的電腦環境和測試思路,儘量和... ...
  • .NET WebAPI作為一種構建RESTful服務的強大工具,為開發者提供了便捷的方式來定義、處理HTTP請求並返迴響應。在設計API介面時,正確地接收和解析客戶端發送的數據至關重要。.NET WebAPI提供了一系列特性,如[FromRoute]、[FromQuery]和[FromBody],用 ...
  • 原因:我之所以想做這個項目,是因為在之前查找關於C#/WPF相關資料時,我發現講解圖像濾鏡的資源非常稀缺。此外,我註意到許多現有的開源庫主要基於CPU進行圖像渲染。這種方式在處理大量圖像時,會導致CPU的渲染負擔過重。因此,我將在下文中介紹如何通過GPU渲染來有效實現圖像的各種濾鏡效果。 生成的效果 ...
  • 引言 上一章我們介紹了在xUnit單元測試中用xUnit.DependencyInject來使用依賴註入,上一章我們的Sample.Repository倉儲層有一個批量註入的介面沒有做單元測試,今天用這個示例來演示一下如何用Bogus創建模擬數據 ,和 EFCore 的種子數據生成 Bogus 的優 ...
  • 一、前言 在自己的項目中,涉及到實時心率曲線的繪製,項目上的曲線繪製,一般很難找到能直接用的第三方庫,而且有些還是定製化的功能,所以還是自己繪製比較方便。很多人一聽到自己畫就害怕,感覺很難,今天就分享一個完整的實時心率數據繪製心率曲線圖的例子;之前的博客也分享給DrawingVisual繪製曲線的方 ...
  • 如果你在自定義的 Main 方法中直接使用 App 類並啟動應用程式,但發現 App.xaml 中定義的資源沒有被正確載入,那麼問題可能在於如何正確配置 App.xaml 與你的 App 類的交互。 確保 App.xaml 文件中的 x:Class 屬性正確指向你的 App 類。這樣,當你創建 Ap ...
  • 一:背景 1. 講故事 上個月有個朋友在微信上找到我,說他們的軟體在客戶那邊隔幾天就要崩潰一次,一直都沒有找到原因,讓我幫忙看下怎麼回事,確實工控類的軟體環境複雜難搞,朋友手上有一個崩潰的dump,剛好丟給我來分析一下。 二:WinDbg分析 1. 程式為什麼會崩潰 windbg 有一個厲害之處在於 ...
  • 前言 .NET生態中有許多依賴註入容器。在大多數情況下,微軟提供的內置容器在易用性和性能方面都非常優秀。外加ASP.NET Core預設使用內置容器,使用很方便。 但是筆者在使用中一直有一個頭疼的問題:服務工廠無法提供請求的服務類型相關的信息。這在一般情況下並沒有影響,但是內置容器支持註冊開放泛型服 ...
  • 一、前言 在項目開發過程中,DataGrid是經常使用到的一個數據展示控制項,而通常表格的最後一列是作為操作列存在,比如會有編輯、刪除等功能按鈕。但WPF的原始DataGrid中,預設只支持固定左側列,這跟大家習慣性操作列放最後不符,今天就來介紹一種簡單的方式實現固定右側列。(這裡的實現方式參考的大佬 ...