問題背景介紹

在大型互聯(lián)網(wǎng)或企業(yè)級應(yīng)用中，數(shù)據(jù)庫往往成為系統(tǒng)性能的瓶頸。隨著數(shù)據(jù)量和并發(fā)量的增長，單一的 SQL 查詢可能出現(xiàn)響應(yīng)遲緩、鎖等待、全表掃描等性能問題。為保證系統(tǒng)的穩(wěn)定性和用戶體驗(yàn)，需要對 SQL 查詢做深入的調(diào)優(yōu)。常見的調(diào)優(yōu)手段包括索引優(yōu)化、查詢重寫、分庫分表、緩存方案等。本文將從多種方案入手，對比分析各自優(yōu)缺點(diǎn)，并結(jié)合真實(shí)生產(chǎn)環(huán)境案例展示調(diào)優(yōu)效果。

多種解決方案對比

方案 A：索引優(yōu)化

• 原理：為頻繁篩選或排序的列建立合適的索引，避免全表掃描。
• 實(shí)現(xiàn)：使用 B-Tree、哈希索引或覆蓋索引。

示例：為訂單表的 user_id 和 created_at 建聯(lián)合索引：

ALTER TABLE orders 
  ADD INDEX idx_user_created (user_id, created_at DESC);

使用 EXPLAIN 查看執(zhí)行計(jì)劃：

EXPLAIN SELECT * FROM orders 
 WHERE user_id = 1234 
 ORDER BY created_at DESC
 LIMIT 10;

方案 B：查詢重寫與分頁優(yōu)化

• 原理：通過拆分復(fù)雜 SQL，避免大范圍排序與聯(lián)表；優(yōu)化分頁查詢。
• 實(shí)現(xiàn)：利用覆蓋索引分頁、二次過濾或游標(biāo)。

示例：傳統(tǒng)高頁碼分頁會嚴(yán)重影響性能：

SELECT * FROM orders 
 WHERE user_id = 1234 
 ORDER BY created_at DESC 
 LIMIT 100000, 20;

重寫為“基于最后讀取位置的分頁”：

-- 前一頁最后一行的 created_at 值
SET @last_time = '2024-07-01 12:34:56';

SELECT * FROM orders
 WHERE user_id = 1234
   AND created_at < @last_time
 ORDER BY created_at DESC 
 LIMIT 20;

方案 C：分區(qū)表 & 分庫分表

• 原理：通過按時(shí)間或用戶 ID 手動/自動劃分表或數(shù)據(jù)庫，減少單表或單庫數(shù)據(jù)量。
• 實(shí)現(xiàn)：MySQL 原生分區(qū)、Proxy 層分片、ShardingSphere 等。

示例：按月份進(jìn)行分區(qū)：

ALTER TABLE orders
  PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202407 VALUES LESS THAN (TO_DAYS('2024-08-01')),
    PARTITION p202408 VALUES LESS THAN (TO_DAYS('2024-09-01'))
);

方案 D：緩存層（Redis）

• 原理：將熱點(diǎn)查詢結(jié)果緩存在內(nèi)存中，減少數(shù)據(jù)庫壓力。
• 實(shí)現(xiàn)：使用 Redis 哈希、Sorted Set 或自定義緩存策略。

示例：通過 Spring Cache 簡單集成：

@Service
public class OrderService {
  @Cacheable(value = "orderList", key = "#userId")
  public List<Order> getRecentOrders(long userId) {
    return orderMapper.findByUserOrderByCreatedAt(userId, 20);
  }
}

各方案優(yōu)缺點(diǎn)分析

選型建議與適用場景

數(shù)據(jù)量中等（百萬級）且查詢模式穩(wěn)定：優(yōu)先考慮 方案 A：索引優(yōu)化 與 方案 B：查詢重寫。低成本、風(fēng)險(xiǎn)小。

業(yè)務(wù)增長迅速、表數(shù)據(jù)量突破千萬甚至億級：結(jié)合 方案 C：分區(qū)表/分庫分表。大型電商、日志系統(tǒng)等。

熱點(diǎn)數(shù)據(jù)重復(fù)訪問高：在以上方案基礎(chǔ)上引入 方案 D：緩存層。防止緩存雪崩采用雙層緩存或預(yù)熱策略。

混合場景：可按業(yè)務(wù)模塊拆分策略（OLTP 與 OLAP 分離），或采用 HTAP 數(shù)據(jù)庫（如 TiDB）兼顧多種需求。

實(shí)際應(yīng)用效果驗(yàn)證

場景：電商訂單列表查詢

• 典型 SQL：按照用戶查詢、按下單時(shí)間倒序分頁。
• 初始數(shù)據(jù)：orders 表記錄量 5000 萬，按頁碼分頁時(shí) 5000 頁后響應(yīng)時(shí)間超 2s。

優(yōu)化前 EXPLAIN：

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | rows    | Extra                |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | 50000000| Using filesort       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

• 方案 A 索引優(yōu)化：新增 (user_id, created_at) 聯(lián)合索引后，響應(yīng)時(shí)間降至 200ms。
• 方案 B 分頁重寫：基于 created_at 游標(biāo)分頁，5000 頁查詢 95% 都在 50ms 內(nèi)完成。
• 方案 C 分庫分表：按用戶哈希分 8 庫后，最慢頁響應(yīng) < 100ms。
• 方案 D Redis 緩存：熱點(diǎn)前 100 頁結(jié)果均在 5ms 內(nèi)返回。

綜合來看，方案 A + 方案 B 是快速見效的低成本首選；方案 C + 方案 D 可結(jié)合應(yīng)對超高并發(fā)與 PB 級數(shù)據(jù)量。

到此這篇關(guān)于MySQL中SQL查詢常見調(diào)優(yōu)方案對比與實(shí)踐的文章就介紹到這了,更多相關(guān)SQL查詢調(diào)優(yōu)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論