問(wèn)題背景介紹

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

多種解決方案對(duì)比

方案 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：查詢重寫(xiě)與分頁(yè)優(yōu)化

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

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

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

重寫(xiě)為“基于最后讀取位置的分頁(yè)”：

-- 前一頁(yè)最后一行的 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ū)表 & 分庫(kù)分表

• 原理：通過(guò)按時(shí)間或用戶 ID 手動(dòng)/自動(dòng)劃分表或數(shù)據(jù)庫(kù)，減少單表或單庫(kù)數(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ù)庫(kù)壓力。
• 實(shí)現(xiàn)：使用 Redis 哈希、Sorted Set 或自定義緩存策略。

示例：通過(guò) Spring Cache 簡(jiǎn)單集成：

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

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

選型建議與適用場(chǎng)景

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

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

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

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

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

場(chǎng)景：電商訂單列表查詢

• 典型 SQL：按照用戶查詢、按下單時(shí)間倒序分頁(yè)。
• 初始數(shù)據(jù)：orders 表記錄量 5000 萬(wàn)，按頁(yè)碼分頁(yè)時(shí) 5000 頁(yè)后響應(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 分頁(yè)重寫(xiě)：基于 created_at 游標(biāo)分頁(yè)，5000 頁(yè)查詢 95% 都在 50ms 內(nèi)完成。
• 方案 C 分庫(kù)分表：按用戶哈希分 8 庫(kù)后，最慢頁(yè)響應(yīng) < 100ms。
• 方案 D Redis 緩存：熱點(diǎn)前 100 頁(yè)結(jié)果均在 5ms 內(nèi)返回。

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

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

最新評(píng)論