-- 查看慢查詢?nèi)罩臼欠耖_啟
SHOW VARIABLES LIKE 'slow_query_log';
-- 開啟慢查詢?nèi)罩?
SET GLOBAL slow_query_log = 'ON';
-- 設(shè)置慢查詢時間閾值（單位：秒）
SET GLOBAL long_query_time = 1;

2. 優(yōu)化 SQL 查詢

• 優(yōu)化查詢語句：對慢查詢語句進(jìn)行優(yōu)化，避免使用復(fù)雜的子查詢、全表掃描等低效操作。例如，將子查詢轉(zhuǎn)換為連接查詢，合理使用索引來提高查詢效率。

-- 原查詢：使用子查詢
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'China');
-- 優(yōu)化后：使用連接查詢
SELECT orders.* FROM orders JOIN customers ON orders.customer_id = customers.customer_id WHERE customers.country = 'China';

• 添加合適的索引：根據(jù)查詢條件和經(jīng)常排序、分組的字段添加索引，但要注意避免創(chuàng)建過多索引，因?yàn)樗饕龝黾訉懖僮鞯拈_銷。

-- 為 customers 表的 country 字段添加索引
CREATE INDEX idx_country ON customers (country);

3. 調(diào)整 MySQL 配置參數(shù)

• 調(diào)整緩沖池大小：innodb_buffer_pool_size 參數(shù)控制 InnoDB 存儲引擎的緩沖池大小，適當(dāng)增大該參數(shù)可以減少磁盤 I/O，降低 CPU 使用率。

[mysqld]
innodb_buffer_pool_size = 2G

• 調(diào)整線程池參數(shù)：如果 MySQL 版本支持線程池，可以調(diào)整線程池的相關(guān)參數(shù)，如 thread_pool_size 來優(yōu)化線程管理，減少 CPU 上下文切換的開銷。

[mysqld]
thread_pool_size = 64

4. 優(yōu)化數(shù)據(jù)庫架構(gòu)

• 表分區(qū)：對于大表，可以考慮使用表分區(qū)技術(shù)，將數(shù)據(jù)分散存儲在不同的分區(qū)中，提高查詢效率。

-- 創(chuàng)建一個按范圍分區(qū)的表
CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

• 垂直拆分和水平拆分：如果表的字段過多，可以進(jìn)行垂直拆分，將不常用的字段分離到其他表中；如果表的數(shù)據(jù)量過大，可以進(jìn)行水平拆分，將數(shù)據(jù)分散到多個表中。

5. 檢查硬件資源

• 增加 CPU 資源：如果服務(wù)器的 CPU 核心數(shù)不足或性能較低，可以考慮升級 CPU 或者增加服務(wù)器的 CPU 核心數(shù)。
• 檢查磁盤 I/O：高 CPU 使用率可能是由于磁盤 I/O 瓶頸導(dǎo)致的?？梢允褂霉ぞ撸ㄈ?Linux 下的 iostat）檢查磁盤 I/O 情況，如果磁盤 I/O 過高，可以考慮使用更快的磁盤（如 SSD）或者優(yōu)化磁盤配置。

6. 處理鎖競爭問題

• 分析鎖等待情況：使用 SHOW ENGINE INNODB STATUS 查看 InnoDB 存儲引擎的狀態(tài)信息，分析是否存在鎖等待的情況。

SHOW ENGINE INNODB STATUS;

• 優(yōu)化事務(wù)：盡量縮短事務(wù)的執(zhí)行時間，避免長時間持有鎖?？梢詫⒋笫聞?wù)拆分成多個小事務(wù)，減少鎖的持有時間。

下面來看一個案例場景。

案例場景分析

案例背景是這樣的，在電商業(yè)務(wù)系統(tǒng)中，數(shù)據(jù)庫采用 MySQL 存儲商品信息、訂單信息、用戶信息等。近期，運(yùn)營部門反饋系統(tǒng)響應(yīng)變慢，尤其是在每天晚上 8 點(diǎn)到 10 點(diǎn)的促銷活動期間，系統(tǒng)幾乎處于卡頓狀態(tài)，經(jīng)過監(jiān)控發(fā)現(xiàn) MySQL 服務(wù)器的 CPU 使用率飆升至接近 100%。

問題排查過程

• 使用系統(tǒng)監(jiān)控工具：運(yùn)維人員使用 Linux 系統(tǒng)的 top 命令查看系統(tǒng)進(jìn)程，發(fā)現(xiàn) MySQL 進(jìn)程占用了大量的 CPU 資源。
• 查看 MySQL 執(zhí)行情況：執(zhí)行 SHOW PROCESSLIST 命令，發(fā)現(xiàn)有大量的查詢語句處于執(zhí)行狀態(tài)，其中一條查詢語句出現(xiàn)的頻率很高，該語句用于查詢某個熱門商品的詳細(xì)信息以及相關(guān)的用戶評論。

SELECT p.*, c.comment_content 
FROM products p 
JOIN comments c ON p.product_id = c.product_id 
WHERE p.product_id = 12345 
ORDER BY c.comment_time DESC;

• 分析慢查詢?nèi)罩?/strong>：開啟慢查詢?nèi)罩竞螅l(fā)現(xiàn)該查詢語句的執(zhí)行時間超過了 5 秒，屬于慢查詢。

-- 為 products 表的 product_id 字段添加索引
CREATE INDEX idx_products_product_id ON products (product_id);
-- 為 comments 表的 product_id 字段添加索引
CREATE INDEX idx_comments_product_id ON comments (product_id);
-- 為 comments 表的 comment_time 字段添加索引
CREATE INDEX idx_comments_comment_time ON comments (comment_time);

SELECT p.*, c.comment_content 
FROM products p 
JOIN comments c ON p.product_id = c.product_id 
WHERE p.product_id = 12345 
ORDER BY c.comment_time DESC 
LIMIT 10;

[mysqld]
innodb_buffer_pool_size = 4G