前言

最近在開發自己的小程序的時候，由于業務功能對系統性能的要求很高，系統性能損耗又主要在mysql上，而業務功能的數據表很多，單表數據量也很大，又涉及到很多場景的數據查詢，所以我針對mysql調用做了優化，成功地把原本一次復雜請求的時間從3到5秒加速到0.5秒以內，順便總結了一些mysql性能優化的方法

一 mysql配置調整

1.1 內存相關

1.1.1 innodb_buffer_pool_size

mysql數據頁索引頁緩沖區的大小： mysql中命中率高的數據頁會長期駐留，讀取數據時如果在buffer pool中，就無需訪問磁盤，InnoDB的數據頁和索引頁都會緩存在這里，

對于專用數據庫服務器，可以設為物理內存的60%~80%，但是要注意，如果設置得太高，會導致操作系統內存不足而swap，系統整體性能下降，而且內存競爭影響其他服務，可以通過以下語句監控buffer pool得命中率

SHOW ENGINE INNODB STATUS

如果status中的buffer pool hit rate命中率小于99%就說明太小了

1.1.2?innodb_log_buffer_size

redo log緩沖區的大小，事務執行過程中，修改操作寫入內存中的log buffer，提交時flush到redo log 文件，redo log緩沖區用于存放事務提交前的redo日志，在事務提交時刷寫到磁盤。

一般設置為 8MB~64MB（默認 16MB），如果事務頻繁、單個事務很大，可以設置更大一點，減少磁盤寫入次數，如果innodb_log_buffer_size設置得太小，會導致大事務頻繁觸發flush，性能下降，可以監控Innodb_log_waits，值大于 0 表示內存不夠，

SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';

1.2 連接和并發

1.2.1 max_connections

服務器連接得最大并發連接數，超過該值的新連接會被拒絕。每個連接占用一定資源（內存+線程）

此參數是系統連接保護閾值。Web應用一般設置在 200~1000，高并發系統可適當調高，搭配連接池使用，設置太小時，高峰期連接被拒，出現 “Too many connections” 錯誤，太大時每個連接占用資源，連接過多會耗盡內存，崩潰風險增加，可以查看 Max_used_connections 是否接近 max_connections的值

SHOW GLOBAL STATUS LIKE 'Max_used_connections';

1.2.2?thread_cache_size

MySQL會重用已結束的線程，減少頻繁創建線程的開銷。線程結束后保留在線程緩存池中，下一個連接復用已有線程。

數據庫服務一般設置為16~100，根據連接頻繁程度和CPU核心數決定，設置太小時會導致Threads_created 值很高，頻繁創建線程，影響性能，設置太大時占用內存資源而且提升不明顯

SHOW GLOBAL STATUS LIKE 'threads_created';

1.2.3?table_open_cache

打開的表文件的緩存數量，避免頻繁打開/關閉表文件，MySQL 每次訪問表都會打開表文件，保存在 cache 中，復用效率更高。注意它緩存的是表文件的句柄和元數據結構，而不是表的數據變身

一般小型系統設置為512~2048，大表多或高并發可設為 8192，太小時會導致Opened_tables 值高，頻繁打開表影響性能，太多則會占用過多內存，可以通過下面語句獲取系統一共打開過多少次表文件

SHOW GLOBAL STATUS LIKE 'opened_tables';

你需要間隔一段時間（例如 1 分鐘、5 分鐘）對比兩次結果，觀察 opened_tables 的增長量。如果 5分鐘內增長了幾百甚至上千個，那就是異常的。正常情況下opened_tables 每分鐘增長小于10 ，如果每分鐘增長50到100之間，說明 table_open_cache 太小或有短連接頻繁打開關閉表

也可以使用下面語句拿到獲取表時命中緩存和沒命中緩存的case數量，自行計算

SHOW GLOBAL STATUS LIKE 'Table_open_cache_hits';
SHOW GLOBAL STATUS LIKE 'Table_open_cache_misses';
?

命中率 = Table_open_cache_hits / (Table_open_cache_hits + Table_open_cache_misses)，通常命中率會大于95%，如果命中率小于90%，就應該考慮增加 table_open_cache

1.3 日志和事務

1.3.1 innodb_flush_log_at_trx_commit

這個配置控制事務redo日志何時寫入磁盤

• 1：每次提交事務都同步寫磁盤（最安全）

• 2：寫OS緩存，根據系統自己的策略定時刷盤（折中）

• 0：僅寫內存，崩潰時候redo日志全部丟失

高可靠性場景：設為1，對性能要求高并且允許少量數據丟失時設為2，數據丟失幾乎不造成損失時可以考慮設置為0

1.3.2 sync_binlog

控制bin log刷盤頻率，影響主從一致性。

• 1：每次事務都刷盤，最安全

• 0：交給操作系統定期刷盤，性能較高，但可能丟 binlog

• N：每 N 次事務刷一次

高可靠性場景下設為1，性能優先：設為 100 或更高

?

1.4 臨時文件與排序

1.4.1?tmp_table_size和max_heap_table_size? ? ? ? ? ? ??

這兩個配置控制臨時表的最大內存使用，超出后寫磁盤，前者控制MySQL 創建內部臨時表（用于 GROUP BY、ORDER BY、DISTINCT 等操作）時，可使用的最大內存空間，后者控制用戶或系統創建的MEMORY引擎表的最大大小，當 MySQL創建一個內存臨時表時，會以 tmp_table_size 和 max_heap_table_size 中的較小值為準，作為臨時表在內存中的最大可用空間

默認16MB太小，建議提升到 64MB~256MB,太小會導致臨時表頻繁寫磁盤，性能下降

SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

1.4.2?sort_buffer_size

每個線程排序操作使用的緩沖區大小。ORDER BY、GROUP BY 會使用此 buffer，如果不足會寫磁盤

這個是單線程的變量，建議設置為2MB~8MB，太小時排序頻繁落盤，影響性能，不過高并發場景不要設太大，因為每個連接消耗內存大，有服務器內存撐爆風險

SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';

二 索引設計和sql優化

2.1 索引設計

2.1.1 合理的索引

覆蓋索引：如果查詢使用的索引語句包含了所有查詢需要的字段，mysql就不需要回表查詢表數據，大大提升性能

前綴索引：當字段是長字符串（如 VARCHAR 或 TEXT），為了節省索引空間和提升性能，可以只索引字段的前幾位（前綴）。這樣可以減少索引大小，但仍保持較好的查詢效率。比如? ? ? ? ? ? ? ?

CREATE INDEX idx_email_prefix ON users(email(10));

表示只索引 email 字段的前 10 個字符。

組合索引：當查詢條件涉及多個列時，可以將多個列聯合建立一個索引。MySQL會將多個字段組合在一起作為一個整體進行索引，大大提升多字段查詢性能。比如下面索引

CREATE INDEX idx_user_status ON users(user_id, status);

對于同時又user_id和status的查詢就能夠快速定位到。

2.1.1 避免不合理的索引設計

索引過多：索引不僅會占用磁盤空間，還是增加寫操作的開銷，建議不超過每表 5-6 個。不超過總字段的三分之一

避免長文本索引：普通索引會存儲值的所有內容，對于很長的字段會占用很大的空間，對于前面部分的區分度就比較高的長字符可以使用前綴索引

2.2 SQL 語句優化

• 避免 SELECT *，只查需要的列

• 保證查詢能使用到索引，對于組合索引和字符模糊匹配要注意最左匹配原則，盡可能利用覆蓋索引

• 使用 EXPLAIN 分析執行計劃，關注 type、rows、Extra 字段

• 大表避免 OFFSET 深分頁，推薦“游標式分頁”（如 WHERE id > ?）

• 避免 IN 過多項（>1000），或考慮改用臨時表

• 聯表限制：盡量不超過 3 張表JOIN，JOIN 字段必須加索引

• 盡量避免使用not in，or和union（盡可能用union all代替union）

• 避免索引列參與函數計算：WHERE DATE(create_time) = '2023-01-01' 會導致索引失效

三 應用系統

3.1 減少不必要的請求

• 緩存策略

  • 使用 Redis/Memcached 緩存熱點數據。

  • 對于不變數據（如省市、用戶等級），做本地緩存或CDN緩存。

• 連接池

  • 應用側應使用數據庫連接池，如 Druid、HikariCP，控制最大并發連接。

• 接口聚合

  • 盡量減少多次小查詢，改為批量查詢或數據合并。

3.2 數據庫訪問控制

• 限流與降級策略：高峰期臨時關閉非核心查詢。

• 使用讀寫分離：讀請求走從庫，寫請求走主庫。

• 使用中間層封裝數據庫訪問（如 DAO 層），方面中間層做統一的優化管理，避免業務層直接操作 SQL。

3.3 持續監控與預警

• 使用工具如：

  • 慢查詢日志 + pt-query-digest 分析慢 SQL。

  • Prometheus + Grafana 或 Percona Toolkit 做實時性能監控。

  • MySQL Enterprise Monitor 商業監控套件。

3.4 表設計

主鍵：優選整數型、自增主鍵（如 BIGINT AUTO_INCREMENT），避免使用 UUID 作為主鍵，因為UUID隨機性太大，插入時會頻繁觸發頁分裂

字段類型：明確字段類型，選擇最合適的數據類型，字段長度固定的場景下使用定長字段而不是變長字段，使用最小足夠類型，避免使用 TEXT/BLOB 除非確實要存大文本；這類字段性能差且不易索引

NULL值：非必要字段不要設置為 NULL，使用默認值（如 0、''）可減少 NULL 判斷。

冗余字段：對于查詢多寫入少的情況可以適當添加一些冗余字段避免join操作影響性能