對於高頻訪問數據庫的應用系統,MySQL 優化至關重要。許多大型網站採用靜態頁面(HTML)策略,核心目的就是減少數據庫查詢壓力;但對於實時數據場景,引入緩存層便成為關鍵。本文系統整理 MySQL 優化的核心理論與實戰技巧,助你構建高性能數據庫架構。
梳理應用邏輯,將對同一表的多次訪問合併為單次 SQL 查詢,一次性提取結果集,從源頭減少數據庫交互次數。
原理:查詢緩存存儲 SELECT 語句文本及其返回結果。當相同查詢再次到達,服務器直接從緩存返回結果,跳過解析與執行階段。
適用場景:數據極少更新的表。一旦表結構或數據發生變更,相關緩存條目即被清空。
核心參數配置:
have_query_cache:服務器是否已配置緩存query_cache_size:緩存區大小(單位:MB)query_cache_type:0/OFF(關閉)、1/ON(開啟,排除 SQL_NO_CACHE)、2/DEMAND(僅缓存帶 SQL_CACHE 的查詢)性能監控指標(SHOW STATUS):
| 變量 | 含義 |
|---|---|
| Qcache_queries_in_cache | 緩存中已註冊的查詢數 |
| Qcache_inserts | 加入緩存的查詢數 |
| Qcache_hits | 緩存命中數 |
| Qcache_lowmem_prunes | 因內存不足被剔除的查詢數 |
| Qcache_not_cached | 未被緩存的查詢數 |
| Qcache_free_memory | 緩存空閒內存總量 |
| Qcache_free_blocks | 緩存空閒內存塊數 |
| Qcache_total_blocks | 緩存總塊數 |
CPU → Cache → Memory → Hard Disk,存取速度依次遞減。將熱點數據放入 Redis、Memcached 等高速緩存,由 Cache Manager 管理數據同步,應用層無感知,可大幅降低數據庫負載。數據庫廠商通常提供緩存相關配置參數,可根據業務特點調優。
INSERT INTO test VALUES (1,2),(1,3),(1,4)... 比多條單獨 INSERT 快數倍。INSERT DELAYED,數據先入內存隊列再寫盤,吞吐更高;LOW_PRIORITY 則在讀寫空閒時才執行。bulk_insert_buffer_size(僅 MyISAM 生效)。LOAD DATA INFILE 比多條 INSERT 快約 20 倍。REPLACE 或 INSERT IGNORE 處理重複鍵衝突。MySQL 默認對 GROUP BY col1, col2... 進行排序(等同 ORDER BY col1, col2...)。若需避免排序開銷,可顯式指定 ORDER BY NULL 禁用排序:
INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
滿足以下條件可利用索引避免額外排序:
可用索引示例:
SELECT * FROM t1 ORDER BY key_part1, key_part2;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
不可用索引場景:
ORDER BY key_part1 DESC, key_part2 ASCWHERE key2=const ORDER BY key1ORDER BY key1, key2(若索引為 key2, key1)MySQL 4.1+ 支持子查詢,但在某些場景 JOIN 效率更高。例如查找無訂單用戶:
-- 子查詢(較慢)
SELECT * FROM customerinfo WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo);
-- LEFT JOIN(更快,尤其 salesinfo.CustomerID 有索引時)
SELECT * FROM customerinfo
LEFT JOIN salesinfo ON customerinfo.CustomerID = salesinfo.CustomerID
WHERE salesinfo.CustomerID IS NULL;
JOIN 避免了創建臨時表的開銷,性能顯著提升。
MySQL 默認調度策略:寫操作優先於讀操作;寫操作串行;讀操作可並發。主要針對 MyISAM 引擎(InnoDB 由行鎖順序決定)。
調度修飾符:
LOW_PRIORITY:適用 DELETE、INSERT、LOAD DATA、REPLACE、UPDATE,降低寫優先級HIGH_PRIORITY:適用 SELECT、INSERT,提升讀/寫優先級DELAYED:適用 INSERT、REPLACE,異步寫入啟動參數:--low-priority-updates 使所有支援 LOW_PRIORITY 的語句默認低優先級;單條 INSERT 可用 INSERT HIGH_PRIORITY 覆蓋。