以下將涵蓋一些 MySQL 比較進階的部分，例如: 分頁優化、讀寫鎖、視圖(View)、Stored Procedure、單條 Query 分析、Partition 等。
讀寫鎖:
讀鎖(Read Lock) 也可以稱為共享鎖 (Shared Lock) ，而寫鎖(Write Lock) 也可以稱為排他鎖(Exclusive Lock)。
Shared Lock: 多個查詢可以同時查詢同一個資源，但需要等到 Shared Lock 提交後其他的更新才能操作。
Exclusive Lock: 同一時刻只能一個查詢以及寫入，需要等到 Exclusive Lock 提交後才能查詢以及寫入。
對應到的 Laravel Method 為
lockForUpdate: 對應到 Exclusive Lock
sharedLock: 對應到 Shared Lock
注意:
- 要使用 Lock 記得 SQL 語句要包在 Transaction 內!
- 在使用鎖的時候記得要使用到索引，否則會全表鎖
操作演練:
A 分頁
BEGIN
SELECT * FROM WHERE id = 1 FOR UPDATE;先執行 transaction 但是不提交
B 分頁
SELECT * FROM WHERE id = 1 FOR UPDATE;接著在 B 分頁執行以上指令會發現卡住了，因為 A 分頁使用 Exclusive Lock 鎖住了其他的查詢。
參考資料:
分頁優化：
Offset、Limit:
一般我們在製作分頁功能的時候通常是使用 Offset 搭配 Limit，例如:
-- 抓取 10 筆資料
SELECT * FROM users LIMIT 0, 10;而第二頁語法則為，以此類推
SELECT * FROM users LIMIT 10, 10;但是這個當 offset 偏移值過大的時候會有效能問題，例如:
SELECT * FROM users LIMIT 1000000, 10;MySQL 其實先查詢 1000010 筆資料，最後再將前面的 1000000 給忽略。
Deferred Join (延遲 Join):
將 offset, limit 包在 subquery 內並套用覆蓋索引，例如:
select * from users
inner join (
select id from users
where created_at between ? and ?
limit 50, 10
) using(id);Cursor Pagination:
使用特定的 unique key 當作 cursor, 類似 Laravel 的 cursorPaginate。
$users->cursorPaginate(10);sql 語法產生如下
select * from users where id > ? limit 0, 10;這樣就不會有 offset 過大的問題了。
參考資料:
View:
將一些常用的 SQL 語法建立起來，後續要使用的話不需要在重需撰寫。
建立語法:
CREATE (OR REPLACE) VIEW <view name> AS statement假如執行以下語法:
CREATE VIEW show_expense_transaction_with_detail AS
SELECT t.*, td.amount AS detail_amount FROM transactions AS t
INNER JOIN transaction_details AS td
ON t.id = td.transaction_id
WHERE t.type = 'EXPENSE';說明:
創建一個 View 名稱為 show_expense_transaction_with_detail 且 SQL 語法為
SELECT t.*, td.amount AS detail_amount FROM transactions AS t
INNER JOIN transaction_details AS td
ON t.id = td.transaction_id
WHERE t.type = 'EXPENSE';要執行 View 的話就像操作資料表一樣
SELECT * FROM show_expense_transaction_with_detail;參考資料:
Stored Procedure:
預先寫好一段 SQL 腳本，且腳本可以跨表等處理一些更為複雜的 SQL 邏輯。
優點:
- 減少網路傳輸
- 因為將 SQL 邏輯放置 Stored Procedure, 因此便於管理
缺點:
- Debug 相當困難
建立 Stored Procedure
-- 定義分隔符
DELIMITER ;;
-- 創建叫做 get_test 的 stored procedure
CREATE PROCEDURE get_test()
BEGIN
DECLARE i INT DEFAULT 1;
SELECT i AS total;
-- 分隔符使用 ;;
END;;
-- 將分隔符重新定義回 ;
DELIMITER ;執行 get_test stored procedure
call get_test();參考資料:
Event:
定義每隔多久執行特定的動作。
在使用 event 前要先使用以下指令確認是否啟用 event
show variables like 'event_scheduler';啟用 event schedule
SET GLOBAL event_scheduler = ON;想要永久啟用 event_scheduler 的話則記得要在 my.cnf 啟用 event
[mysqld]
event_scheduler = on創建語法
CREATE EVENT <event name>
ON SCHEDULE EVERY 2 SECOND
DO
CALL <stored procedure>;參考資料:
Partition:
Partition 是一個優化查詢的一種手段，有 Partition 的話在執行 SQL SELECT 的時候就會限縮資料範圍去特定的 Partition 抓取從而加快查詢的效率。且在刪除資料的時候也可以指定要刪除哪個 Partition, 這很適合用在 log 相關的 table，因此往往過久以前的 log 紀錄未必需要在保留。
Partition key 建立注意事項:
- 在建立 partition key 時，所使用的 parition key 必須是 primary key 或者 unique key 的一部份，否則建立 partition 會失敗
- 有 foreign key 的話不能建立 partition
partition 種類:
range: 使用指定的 partition key 做範圍分區，通常會拿日期做 range key, 例如：每一年或者每半年製作一個 partition
ALTER TABLE transactions
PARTITION BY RANGE(TO_DAYS(created_at))
(
PARTITION p0 VALUES LESS THAN (TO_DAYS('2024-04-14')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2024-04-15')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2024-04-20')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2024-05-01')),
PARTITION p4 VALUES LESS THAN MAXVALUE
);hash: 可以使 partition 均勻分佈
key: 類似 hash 只是演算法不同
list: 使用 partition key 做分類分區
如何移除分區定義
ALTER TABLE <table name> REMOVE PARTITIONING;刪除特定 Partition 的資料
ALTER TABLE <table name> DROP PARTITION <partition name>;table 空間重新整理 (以下指令為鎖表，請慎重使用在 Production 環境)
OPTIMIZE TABLE <table name>;清空特定 Partition 的資料
ALTER TABLE <table name> TRUNCATE PARTITION <partition name>;增加分區
ALTER TABLE transactions ADD PARTITION (
PARTITION p5 VALUES LESS THAN (TO_DAYS('2024-05-10 00:00:00')),
PARTITION p_max VALUES LESS THAN MAXVALUE
);從特定的 partition 取得資料
SELECT * FROM transactions PARTITION(p1);參考資料:
https://blog.csdn.net/u010647035/article/details/106374248
單條 Query 分析:
檢查 table 是否需要進行 optimization
SELECT
TABLE_NAME,
ROUND(DATA_LENGTH/1024/1024) AS data_length,
ROUND(DATA_FREE/1024/1024) AS data_free
FROM information_schema.tables
HAVING data_length > 500;data_length: 資料表的總大小
data_free: 資料表未使用的空間大小
假如發現 data_free 比例過高，則需執行 optimization table
檢查 index 用量
SELECT
OBJECT_SCHEMA AS `Database`,
OBJECT_NAME AS `Table`,
INDEX_NAME AS `Index`,
COUNT_FETCH AS `Index Reads`
FROM
performance_schema.table_io_waits_summary_by_index_usage
WHERE
INDEX_NAME IS NOT NULL
AND OBJECT_SCHEMA = 'finance'
ORDER BY
COUNT_FETCH DESC;參考資料:
profiling:
可以進行 SQL 語句的分析
SET profiling = 1;profile type 有以下幾種:
ALL: 所有資訊
CPU: cpu 相關開銷
Memory: 記憶體相關開銷
swaps: 交換次數開銷
顯示所有 Query 的 ID 以及執行時間
SHOW PROFILES;顯示特定 Query 的 profile 資訊
SHOW PROFILE ALL FOR QUERY <Query ID>;
參考資源:
Full Text index
ngram parser
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body) WITH PARSER ngram
) ENGINE=INNODB CHARACTER SET utf8mb4;預設 mysql ngram 為 2, 可以特定修改 my.cnf 去調整 ngram
[mysqld]
ngram_token_size=2;也可以查看 tokenizer data , 需先設定 innodb_ft_aux_table
SET GLOBAL innodb_ft_aux_table="<database name>/<table name>";查看 tokenzier
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position; 
參考資料:
natural language mode: 以自然語言的方式去搜尋
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('Gary' IN NATURAL LANGUAGE MODE);boolean mode: 可以指定出現、不出現的字串，且可以給予特定字串權重。
以下語法指定須包含 Gary 字串且後面接任意字元。
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+Gary*' IN BOOLEAN MODE);基本語法包含:
- +:包含字串
- -: 不要包含該字串
- *:任意字元
- >: 有包含指定字串則增加權重
- <:有包含指定自傳則減少權重
expansion mode:
可以透過關聯字去搜尋資料，但是可能會查詢多次資料所以此 mode 會執行很久，因此在執行的時候需注意!!
SQL 語法包含 WITH QUERY EXPANSION or IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
參考資料: