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
  • 在本篇教程中,我們學習瞭如何使用 Taurus.MVC WebMVC 框架創建一個簡單的頁面。 我們創建了一個控制器並編寫了一個用於呈現頁面的方法,然後創建了對應的視圖,並最終成功運行了應用程式。 在下一篇教程中,我們將繼續探索 Taurus.MVC WebMVC 框架的更多功能和用法。 ...
  • 一:背景 1. 講故事 很多.NET開發者在學習高級調試的時候,使用sos的命令輸出會發現這裡也看不懂那裡也看不懂,比如截圖中的這位朋友。 .NET高級調試屬於一個偏冷門的領域,國內可觀測的資料比較少,所以很多東西需要你自己去探究源代碼,然後用各種調試工具去驗證,相關源代碼如下: coreclr: ...
  • 我一直都以為c中除以2的n次方可以使用右移n位代替,然而在實際調試中發現並不都是這樣的。是在計算餘數是發現了異常 被除數:114325068 右移15計算結果:3488 除法取整計算結果:3489 右移操作計算餘數:33772 除法取整計算餘數:1005 顯然:這是不一樣的。 移位操作是一條cpu指 ...
  • 在上一篇文章中,我們介紹了ReentrantLock類的一些基本用法,今天我們重點來介紹一下ReentrantLock其它的常用方法,以便對ReentrantLock類的使用有更深入的理解。 ...
  • Excelize 是 Go 語言編寫的用於操作電子錶格辦公文檔的開源基礎庫,2024年2月26日,社區正式發佈了 2.8.1 版本,該版本包含了多項新增功能、錯誤修複和相容性提升優化。 ...
  • 雲採用框架(Cloud Adoption Framework,簡稱CAF)為企業上雲提供策略和技術的指導原則和最佳實踐,幫助企業上好雲、用好雲、管好雲,併成功實現業務目標。本雲採用框架是基於服務大量企業客戶的經驗總結,將企業雲採用分為四個階段,並詳細探討企業應在每個階段採取的業務和技術策略;同時,還 ...
  • 與TXT文本文件,PDF文件更加專業也更適合傳輸,常用於正式報告、簡歷、合同等場合。項目中如果有使用Java將TXT文本文件轉為PDF文件的需求,可以查看本文中介紹的免費實現方法。 免費Java PDF庫 本文介紹的方法需要用到Free Spire.PDF for Java,該免費庫支持多種操作、轉 ...
  • 指針和引用 當我們需要在程式中傳遞變數的地址時,可以使用指針或引用。它們都可以用來間接訪問變數,但它們之間有一些重要的區別。 指針是一個變數,它存儲另一個變數的地址。通過指針,我們可以訪問存儲在該地址中的變數。指針可以被重新分配,可以指向不同的變數,也可以為NULL。指針使用*運算符來訪問存儲在地址 ...
  • 即使再小再簡單的需求,作為研發開發完畢之後,我們可以直接上線麽?其實很多時候事故往往就是由於“不以為意”發生的。事故的發生往往也遵循“墨菲定律”,這就要求我們更要敬畏線上,再小的需求點都需要經過嚴格的測試驗證才能上線。 ...
  • 這裡給大家分享我在網上總結出來的一些知識,希望對大家有所幫助 一、是什麼 許可權是對特定資源的訪問許可,所謂許可權控制,也就是確保用戶只能訪問到被分配的資源 而前端許可權歸根結底是請求的發起權,請求的發起可能有下麵兩種形式觸發 頁面載入觸發 頁面上的按鈕點擊觸發 總的來說,所有的請求發起都觸發自前端路由或 ...