MySQL8.0效能最佳化（實踐）

Sol·wang發表於2023-01-30
MySql

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

案例環境：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 組合索引的使用

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

MySQL8.0效能最佳化（實踐）

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

總結：組合索引所包含的列，儘量在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

顯示執行過程，檢視是否命中索引

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萬相同資料、相同表結構，相同的查詢方式，測試效果如下圖：(僅供參考)

MySQL8.0效能最佳化（實踐）

資料量大了，查詢慢；加索引了，資料量越大，寫入越慢；

還是物理分表好呀~

四、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服務引數配置+表分割槽，最終有了成倍的效能提升；每次測試後的日誌記錄了最佳化的遞進過程；
如下圖：(日誌不夠細，懂就行)
MySQL8.0效能最佳化（實踐）

經過逐步最佳化：

  200萬資料寫入耗時從 9分4秒，提升到 5分50秒；(無表分割槽)

  500萬資料寫入耗時從 41分33秒，提升到 6分50秒。(有表分割槽)

相關文章