[TOC] ## 分析SQL查詢慢的方法 1. 記錄慢查詢日志 分析查詢日志,不要直接打開慢查詢日志進行分析,這樣比較浪費 時間和精力,可以使用pt- query- digest工具進行分析 2. 使用 show profile `set profiling=1`;開啟,服務器上執行的所有語句會檢測消耗的時 間,存到臨時表中 ``` set profiling=1; //開啟 profile show profiles show profile for query臨時表ID ``` demo ``` mysql > set profiling=1;s mysql > select from a; mysql> show profiles; ```  3. 使用 show status `show status`會返回一些計數器, `show global status`查看服務器級別的所有計數有時根據這些計數,可以猜測出哪些操作代價較高或者消耗時間多 4. 使用 show processlist 觀察是否有大量線程處于不正常 5. 使用 explain 分析單條SQL語句 `mysql> explain select * from fq_order\G;` ## 優化查詢過程中的數據訪問 - 訪問數據太多導致查詢性能下降 - 確定應用程序是否在檢索大量超過需要的數據,可能是太多行或列 - 確認 MYSQL服務器是否在分析大量不必要的數據行 - 查詢不需要的記錄,使用 limit解決 - 多表關聯返回全部列,指定Aid,A.name,Bage - 總是取出全部列, `SELECT*`會讓優化器無法完成索引覆蓋掃描的優化 - 重復查詢相同的數據,可以緩存數據,下次直接讀取緩存 ## 是否在掃描額外的記錄 使用 explain來進行分析,如果發現查詢需要掃描大量的數據但只返回少數的行,可以通過如下技巧去優化: 使用索引覆蓋掃描,把所有用的列都放到索引中,這樣存儲引擎不需要回表獲取對應行就可以返回結果 ## 一個復雜查詢好與多個簡單查詢 - MysαL內部每秒能掃描內存中上百萬行數據,相比之下,響應數據給客戶端就要慢得多 - 使用盡可能少的查詢是好的,但是有時將一個大的查詢分解為多個小的查詢是很有必要 ## 切分查詢 將一個大的查詢分為多個小的相同的查詢一次性刪除1000萬的數據要比一次刪除1萬,暫停一會的方案更加損耗服務器開銷 ## 分解關聯查詢 - 可以將一條關聯語句分解成多條SQL來執行 - 讓緩存的效率更高 - 執行單個查詢可以減少鎖的競爭 - 在應用層做關聯可以更容易對數據庫進行拆分 - 查詢效率會有大幅提升 - 較少冗余記錄的查詢 ## 優化 count()查詢 - `count(*`)中的`*`會忽略所有的列,直接統計所有列數,因此不要使用 count(列名) - MYISAM中,沒有任何 WHERE條件的` count(*)`非常快,當有 WHERE條件, MYISAM的 count統計不一定比其他表引擎快 - 可以使用 explain查詢近似值,用近似值替代 count() - 增加匯總表 - 使用緩存 ## 優化關聯查詢 - 確定ON或者 USING子句的列上有索引 - 確保 GROUP BY和 ORDER BY中只有一個表中的列,這樣 MYSQL才有可能使用索引 ## 優化子查詢 盡可能使用關聯查詢來替代 ## 優化 GROUP BY和 DISTINCT - 這兩種查詢均可使用索引來優化,是最有效的優化方法 - 關聯查詢中,使用標識列進行分組的效率會更高 - 如果不需要 ORDER BY,進行 GROUP BY時使用 ORDER BY NULL, MYSQL不會再進行文件排序 - WITH ROLLUP超級聚合,可以挪到應用程序處理 > 分類聚合后的結果進行匯總 [參考用法簡書 ](https://www.jianshu.com/p/5d2f700b0a31) ## 優化UNON查詢 UNION ALL的效率高于 UNION ## 分區表的原理 對用戶而言,分區表是一個獨立的邏輯表,但是底層 MYSQL將其分成了多個物理子表,這對用戶來說是透明的,每一個分區表都會使用一個獨立的表文件 . 創建表時使用 partition by子句定義每個分區存放的數據,執行查詢時,優化器會根據分區定義過濾那些沒有我們需要數據的分區,這樣查詢只需要查詢所需數據在的分區即可 分區的主要目的是將數據按照一個較粗的粒度分在不同的表中,這樣可以將相關的數據存放在一起,而且如果想一次性刪除整個分區的數據也很方便 ### 適用場景 1. 表非常大,無法全部存在內存,或者只在表的最后有熱點數據,其他都是歷史數據 2. 分區表的數據更易維護,可以對獨立的分區進行獨立的操作 3. 分區表的數據可以分布在不同的機器上,從而高效使用資源 4. 可以使用分區表來避免某些特殊的瓶頸 5. 可以備份和恢復獨立的分區 ### 限制 1. 一個表最多只能有1024個分區 2. 5.1版本中,分區表表達式必須是整數,5.5可以使用列分區 3. 分區字段中如果有主鍵和唯一索引列,那么主鍵列和唯一列都必須包含進來 4. 分區表中無法使用外鍵約束 5. 需要對現有表的結構進行修改 6. 所有分區都必須使用相同的存儲引擎 7. 分區函數中可以使用的函數和表達式會有一些限制 8. 某些存儲引擎不支持分區 9. 對于 MYISAM的分區表,不能使用 load index into cache 10. 對于 MYISAM表,使用分區表時需要打開更多的文件描述符 ## 分庫分表的原理 ### 工作原理 通過一些HASH算法或者工具實現將一張數據表垂直或者水平進行物理切分 ### 適用場景 1. 單表記錄條數達到百萬到千萬級別時 2. 解決表鎖的問題 ### 分表方式 #### 水平分割 表很大,分割后可以降低在查詢時需要讀的數據和索引的頁數,同時也降低了索引的層數,提高查詢速度 使用場景 1. 表中的數據本身就有獨立性,例如表中分別記錄各個地區的數據或者不同時期的數據,特別是有些數據常用,有些不常用 2. 需要把數據存放在多個介質上 水平分表缺點 1. 給應用增加復雜度,通常查詢時需要多個表名,查詢所有數據都需 `UNION` 操作 2. 在許多數據庫應用中,這種復雜性會超過它帶來的優點,查詢時會增加讀一個索引層的磁盤次數 ### 垂直分表 把主鍵和一些列放在一個表,然后把主鍵和另外的列放在另一個表中,就是多一張詳情表 使用場景 1. 如果一個表中某些列常用,而另外一些列不常用 2. 可以使數據行變小,一個數據頁能存儲更多數據,查詢時減少I/O次數 垂直分表缺點 1. 管理冗余列,查詢所有數據需要JoIN操作 ## MYSQL的復制原理及負載均衡 ### MYSQL主從復制工作原理 在主庫上把數據更改記錄到二進制日志 從庫將主庫的日志復制到自己的中繼日志 從庫讀取中繼日志中的事件,將其重放到從庫數據中 ### MYSQL主從復制解決的問題 數據分布:隨意停止或開始復制,并在不同地理位置分布數據備份 負載均衡:降低單個服務器的壓力 高可用和故障切換:幫助應用程序避免單點失敗 升級測試:可以使用更高版本的 MYSQL作為從庫 ### 解題方法 充分掌握分區和分表的工作原理和適用場景,在面試中,此類題通 常比較靈活,會給一些現有公司遇到問題的場景,大家可以根據分 區和分表以及Mysα復制、負載均衡的適用場景來根據情況進行 回答。