MySQL 最佳化筆記

chenlixin發表於2019-11-21
MySql筆記

記錄學習筆記，持續更新。

最佳化方向

SQL最佳化

  1. sql最佳化分析
  2. 索引最佳化

最佳化資料庫物件

  1. 最佳化表的資料型別
  2. 表拆分（水平、垂直）
  3. 反正規化
  4. 使用中間表

最佳化 mysql server

  1. mysql記憶體管理最佳化
  2. log機制及最佳化
  3. 調整mysql併發引數

應用最佳化

  1. 資料庫連線池
  2. 使用快取減少壓力
  3. 負載均衡建立叢集
  4. 主主同步、主從複製

Mysql最佳化問題分析定位

分析SQL執行頻率

show status

例如：分析讀為主，還是寫為主

定位執行效率低的SQl

慢查詢日誌定位
-log-slow-queries = xxx（指定檔名）

SHOW PROCESSLIST
檢視當前正在進行的執行緒，包括執行緒狀態、是否鎖表

分析SQL執行計劃

explain "your sql"

desc "your sql"

- 部分引數分析
select_type: 
SIMPLE 簡單表，不使用表連線或子查詢
PRIMARY 主查詢，即外層的查詢
UNION 
SUBQUER 子查詢的第一個select

type: 
ALL 全表掃描
index 索引全掃描
range 索引範圍掃描
ref 使用非唯一索引或唯一索引的字首掃描
eq_ref 類似ref，使用的索引是唯一索引
const/system 單表中最多有一個匹配行
NULL 不用訪問表或者索引，直接得到結果

show profile分析SQL

select @@have_profiling 是否支援
select @@profiling 是否開啟

執行 "your sql"
show profiles 
show profile block io for QUERY 17

索引最佳化

索引的儲存分類

B-TREE索引：常見，大部分都支援
HASH索引：只有memory引擎支援
R-TREE索引：空間索引是MyISAM的一個特殊索引型別，主要用於地理空間資料型別
full-text索引：全文索引，MyISAM的一個特殊索引型別，innodb從5.6開始支援

索引的建立與刪除

新增索引
ALTER Table `table_name` ADD PRIMARY KEY（`column`）
ALTER Table `table_name` ADD UNIQUE（`column`）
ALTER Table `table_name` ADD INDEX（`column`）
ALTER Table `table_name` ADD FULLTEXT（`column`）

刪除
ALTER Table `table_name` drop index index_name

Mysql中能使用索引的情況

匹配全值
匹配值範圍查詢
匹配最左字首
僅僅對索引進行查詢（覆蓋查詢）
匹配列字首 （新增字首索引）
部分精確+部分範圍

不能使用索引的場景

以%開關的like查詢
資料型別出現隱式轉換
複合索引查詢條件不包含最左部分
使用索引仍比全表掃描慢
用or分割開的條件

mysql語句最佳化

定期最佳化表

optimize table table_name 合併表空間碎片，對MyISAM、BDB、INNODB有效

如果提示不支援，可以用 mysql --skip-new 或者 mysql --safe-mode 來重啟，以便讓其他引擎支援

常用最佳化

儘量避免全表掃描，對where及orderby的列建立索引
儘量避免where使用 != 或 <>
儘量避免where子句用 or 連線條件
亂用%導致全表掃描
儘量避免where子句對欄位進行表示式操作
儘量避免where子句對欄位進行函式操作
覆蓋查詢，返回需要的欄位
最佳化巢狀查詢，關聯查詢優於子查詢
組合索引或複合索引，最左索引原則
用exist代替in
當索引列有大量重複資料時，SQL查詢可能不會去利用索引

最佳化資料庫物件

最佳化表資料型別

PROCEDURE ANALYSE (16,256) 排除多於16個，大於256位元組的ENUM建議

"your sql" PROCEDURE ANALYSE ()

表拆分

垂直拆分
針對某些列常用、不常用

水平拆分
表很大
表中的資料有獨立性，能簡單分類
需要在表存放多種介質

反正規化

增加冗餘列、增加派生列、重新組表和分割表

使用中間表

資料查詢量大
資料統計、分析場景

Mysql引擎比較

mysql有什麼引擎？

MySQL 最佳化筆記

關於表引擎的命令

show engines; 檢視myql所支援的儲存引擎
show variables like '%storage_engine'; 檢視mysql預設的儲存引擎
show create table table_name 檢視具體表使用的儲存引擎

關於innodb

1. 提供事務、回滾、系統奔潰修復能力、多版本併發控制事務
2. 支援自增列
3. 支援外來鍵
4. 支援事務以及事務相關聯功能
5. 支援mvcc的行級鎖

關於MyISAM

1. 不支援事務、不支援行級鎖，只支援併發插入的表鎖，主要用於高負載的select
2. 支援三種不同的儲存結構：靜態、動態、壓縮

調整引數最佳化mysql後臺服務

MyISAM記憶體最佳化

#修改相應伺服器位置的配置檔案 my.cnf

key_buffer_size
決定myisam索引塊快取區的大小，直接影響表的存取效率，建議1/4可用記憶體

read_buffer 讀快取

write_buffer 寫快取

InnoDB記憶體最佳化

innodb_buffer_pool_size 儲存引擎表資料和索引資料的最大快取區大小

innodb_old_blocks_pct LRU演算法 決定old sublist的比例

innodb_old_blocks_time LRU演算法 資料轉移間隔時間

mysql併發引數

max_connections 最大連線數，預設151

back_log 短時間內處理大量連線，可適當增大

table_open_cache 控制所有SQL執行執行緒可開啟表快取的數量，受其他引數制約

thread_cache_size 控制快取客戶服務執行緒數量，加快資料庫連線速度，根據threads_created/connections來衡量是否合適

innodb_lock_wait_timeout 控制事務等待行鎖時間，預設50ms

Mysql應用最佳化介紹

為什麼要做應用最佳化

  • 資料的重要性
  • mysql服務及自身效能瓶頸
  • 保證大型系統穩定可靠執行

應用最佳化方法

  1. 使用連線池

  2. 減少對mysql的真實連線
    a. 避免相同資料重複執行（查詢快取）
    b. 使用mysql快取（sql快取）

  3. 負載均衡
    a. LVS 分散式
    b. 讀寫分離（主主複製、主從複製保證資料一致性）

資料庫連線池

php-cp 擴充套件，僅記錄一下，這種方案可能已過時

主從備份及讀寫分離

主主備份

負載均衡

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章