知識準(zhǔn)備 索引 - 索引是存儲引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)
- B-Tree，適用于全鍵值，鍵值范圍或鍵最左前綴：(A,B,C): A, AB, ABC,B,C,BC
- 哪些列建議創(chuàng)建索引：WHERE, JOIN , GROUP BY, ORDER BY等語句使用的列
- 如何選擇索引列的順序：
- 經(jīng)常被使用到的列優(yōu)先
- 選擇性高的列優(yōu)先：選擇性=distinct(col)/count(col)
- 寬度小的列優(yōu)先：寬度 = 列的數(shù)據(jù)類型
慢查詢 原因 - 未使用索引
- 索引不優(yōu)
- 服務(wù)器配置不佳
- 死鎖
- ...
命令 看版本 mysql -V 客戶端版本 select version 服務(wù)器版本 explain 執(zhí)行計劃，慢查詢分析神器 - type
- const,system: 最多匹配一個行，使用主鍵或者unique進行索引
- eq_ref: 返回一行數(shù)據(jù)，通常在聯(lián)接時出現(xiàn)，使用主鍵或者unique索引（內(nèi)表索引連接類型）
- ref: 使用key的最左前綴，且key不是主鍵或unique鍵
- range: 索引范圍掃描，對索引的掃面開始于某一點，返回匹配的行
- index:以索引的順序進行全表掃描，優(yōu)點是不用排序，缺點是還要全表掃描
- all: 全表掃描 no no no
- extra
- using index : 索引覆蓋，只用到索引，可以避免訪問表
- using where: 在存儲引擎檢索行后再做過濾
- using temporary:使用臨時表，通常在使用GROUP BY，ORDER BY 時出現(xiàn)（嚴禁）
- using filesort: 到非索引順序的額外排序，當(dāng)order by col未使到索引時發(fā)生（嚴禁）
- possible_keys: 顯示查詢可能使用的索引
- key:優(yōu)化器決定采用哪個索引來優(yōu)化對該表的訪問
- rows:MySQL估算的為了找到所需行要檢索的數(shù)，優(yōu)化選擇key的參考 （不是結(jié)果集的行數(shù)）
- key_len: 使用的索引左前綴的長度(字節(jié)數(shù))，亦可理解為使用了索引中哪些字段
- 定長字段，int占4個字節(jié)、date占3個字節(jié)、timestamp占4個字節(jié)，char(n)占n個字節(jié)
- NULL的字段:需要加1個字節(jié)，因此建議盡亮設(shè)計為NOT NULL
- 變長字段varchar(n)，則需要 (n * 編碼字符所占字節(jié)數(shù) 2 、)個字節(jié)，如utf8編碼的， 個字符
- 占 3個字節(jié)，則 度為 n * 3 2
- 強制使用索引: USE INDEX （建議）或 FORCE_INDEX （強制）
show 命令 - show status
- 查看select語句的執(zhí)行數(shù) show global status like 'Com_select';
- 查看慢查詢的個數(shù) show global status like 'Slow_queries';
- 表掃描情況 show global status like 'Handler_read%'; Handler_read_rnd_next / com_select > 4000 需要考慮優(yōu)化索引
- show variables
- 查看慢查詢相關(guān)的配置 show variables like 'long_query_time';
- 將慢查詢時間線設(shè)置為2s set global long_query_time=2;
- 查看InnoDB緩存 show variables like 'innodb_buffer_pool_size';
- 查看InnoDB緩存的使用狀態(tài) show status like 'Innodb_buffer_pool_%'; 緩存命中率=(1-Innodb_buffer_pool_reads/ Innodb_buffer_pool_read_requests) * 100%；緩存率=（Innodb_buffer_pool_pages_data/ Innodb_buffer_pool_pages_total) * 100%
- SHOW PROFILES;該命令可以trace在整個執(zhí)行過程中各資源消耗情況(會話級)
- SHOW PROCESSLIST; 查看當(dāng)前有哪些線程正在運行，并且處在何種狀態(tài)
- SHOW ENGINE INNODB STATUS; 可用于分析死鎖，但需要super權(quán)限
|
