MySQL常見注意事項及最佳化

標籤：

MySQL常見注意事項

1. 模糊查詢 like 預設是對name欄位建立了索引

   注意：在使用模糊查詢的時候，當% 在第一個字母的位置的時候，這個時候索引是無法被使用的。但是% 在其他的位置的時候，索引是可以被使用的。

   ?

   # select * from tableName where name like "%zhangsan"; ?可以使用到索引啊？ 不可以。

   分析：因為是不確定查詢，在表中任何一行記錄都有可能滿足查詢條件。

   ?

   #select * from tableName where name like "zh%"; 可以使用嗎？ 可以

   #select * from tableName where name like "zh%三"; 可以使用嗎？ 可以

   # select * from tableName where name like "z%san"; ? 可以使用嗎？可以，首先可以快速定位z字母開頭的部分。讀z字母這段範圍之內只能逐行比較。

   ?

2. 對查詢的列不要使用函數或者運算。否則索引無法使用。

   # select * from tableName where id+1 = 1000;

   # select * from tableName where id = 999; 等價。

   ?

   ?

   有的時候，會在查詢欄位上面使用函數。使用函數的時候也是無法使用所有的，一般的解決方案是將查詢後的結果交給php程式（字串 和 數組）來實現處理。不要把函數的處理放在MySQL裡面完成。

   ?

3. 在你開發之中，什麼樣的欄位適合建立索引？簡述為什嗎？

   答：

4. 在where後面作為查詢條件的欄位是適合建立索引的。
   1. 注意：對於唯一很差的欄位不適合建立索引，例如 只有 男 和 女 這兩種情況的欄位。
5. 在需要排序的欄位上面，也可以建立索引。

   ?

   執行order by 不加限定，全表掃描，filesort含義 注意：問題？

   ?

6. MySQL最佳化的一個通用方案？

   答：

7. 開啟MySQL的慢查詢記錄功能，讓系統運行一段時間（測試時間 都是在半個月到一個月時間）
8. 檢查慢查詢的日誌資訊，分析出可能有問題的sql語句
9. 使用profile工具詳細去分析sql語句執行的時候，每個步驟所花費的時間，sending data（可能出現的問題 是MySQL沒有使用索引的情況下，會去磁碟上擷取資料：經驗）
10. 對sql語句最佳化（本身寫出來的sql語句就存在問題，例如對查詢的欄位使用了函數）或者對錶的結構（表中有些查詢欄位沒有建立索引）適當的做一些調整（索引重建或者建立的更合適）
11. 使用explain工具分析一下這些sql語句在執行的時候，是如何執行（可能使用索引或者是使用那些索引）
    1. type： const \ index \ 出現這些 代表索引正常使用
12. 反覆上面的過程具體分析。

    ?

13. MySQL的自身緩衝

    解釋：MySQL的用戶端在發送sql語句到MySQL伺服器端之後，會先去檢查一下許可權，之後去查詢該條sql語句的緩衝資訊是否存在，如果存在，則直接返回；如果不存在，MySQL伺服器需要去分析該sql語句，做詞法文法分析，然後編譯，生產執行樹，去磁碟上擷取資料，擷取資料後，緩衝到自身的一個緩衝容器裡面，然後在返回資料。

    ?

    使用：

    # show variables like "%cache%";

    更改MySQL緩衝的大小(32M)，注意 單位是 B（位元組）

    # set global query_cache_size = 1024*1024*32;

    注意：第一個是要加關鍵字 global 第二個是大小的單位為 B（位元組） 第三具體給多少合適，取決於自身作業系統的記憶體大小。

    測試對比：

    ?

    注意：MySQL自身的緩衝需要注意兩點：

14. MySQL的sql語句裡面不能出現不確定資訊（例如在條件裡面使用 now（）函數），MySQL的自身緩衝將無法被使用。查詢的結構不會被緩衝起來。

    分析：

    ?

15. MySQL的自身的緩衝是嚴格基於 sql 語句的（MySQL自身緩衝是嚴格區分sql語句的大小寫） select === SELECT 關鍵字 其實是一樣的

    ?

    ?

16. MySQL的架構分析（讀寫分離）

    由於一個網站業務中 70%的業務基本都是讀操作，剩下的都是寫操作。所以這個時候對讀的壓力過大，需要使用一定的方式來減少壓力，這個時候可以使用讀寫分離這種架構來實現壓力的分擔。

    怎麼查詢是讀為主？

    # show status like "%Com_%";

    通過上面的分析一段時間，就可以大致計算出網站的讀寫情況

    ?

    # show status ; 可以查看MySQL的一個狀態資訊。

    ?

    ?

    ?

    讀寫分離概圖：

    mysql-proxy這個工具可以實現對sql語句的分析，判斷sql語句是讀操作（select關鍵字） 還是 寫操作（insert、update、delete）。最後去串連不同的伺服器實現業務的完成。

    當完成讀的時候，MySQL-proxy會從對台讀伺服器按照一定策略去選擇一台（輪詢、加權、ip_hash）完成讀操作

    當完成寫的時候，直接去串連寫的伺服器

    ?

    問題：

    由於資料只在主伺服器上實現寫操作，但是從伺服器上是沒有完成寫操作的，這個時候資料就會不一致。

    需要解決一致性的問題？

    答：可以使用MySQL的一個bin日誌來完成資料的一致性問題。

    ?

    使用步驟：

    主伺服器配置：

17. 先在主伺服器上開啟bin日誌（bin日誌是MySQL的一個二進位日誌功能，可以記錄對MySQL資料造成更改的sql語句，形成一個記錄檔）
18. 在主伺服器上的設定檔裡面定義一個 server_id = Number 相當於標示一下這個伺服器
19. 在主伺服器上添加一個授權帳號用來到時候擷取bin日誌。

    ?

    從伺服器配置：

20. 開啟從伺服器的一個中繼日誌，這個日誌主要是把主伺服器bin日誌讀取之後（無法直接使用，需要先轉換為中繼日誌），形成轉換。
21. 在從伺服器上的設定檔定義一個 server_id =Number 不要和主伺服器重複
22. 使用主伺服器的授權帳號去連結主伺服器擷取bin日誌，然後讀取到本地，形成中級日誌，然後在本地的MySQL執行一遍，達到和主伺服器上資料檔案一致。
23. 啟動從伺服器的主從複製功能。

    ?

    總結：主從複製是完成讀寫分離的一個基礎。（稍微有點延時）

    ?

    最佳化技術

    索引覆蓋

    對於MyISAM的儲存引擎來說，如果查詢的欄位資訊正好在索引檔案裡面出現，這個時候不需要做回行的操作，直接可以從索引檔案裡面返回的現象就叫做索引覆蓋。（索引正好覆蓋了查詢的欄位）

    ?

    應用：在大資料（百萬資料）下的一個翻頁效果

    技術點：翻頁是如何做的？

    答：select * from tableName limit offset,page;

    好比現在是N頁，每頁顯示page條

    offset = （N-1) * page

    ?

    實際使用：

    分頁操作

    當很大頁碼的時候

    ?

    通過上面的對比發現，MySQL在使用limit分頁的時候，在頁碼足夠大的情況下，效率是很低的，為什嗎？

    答：主要原因是，MySQL在使用limit做查詢的時候，如下sql：

    select * from tableName limit offset,page;

    執行過程：

    先取出 offset+page 條記錄， 然後在丟棄 offset 條記錄 ，返回 page 條記錄。

    所以有必要對這種情況做最佳化操作：

    ?

24. 從業務上完成（限制使用者的行為，不允許翻頁超過規定的頁碼）

    百度限制使用者行為：

    ?

25. 不從限制使用者的行為上去考慮，可以使用索引來實現。
    1. select * from tableName where id > Number limit page;

    當分頁頁碼變大的情況下：

    通過使用 where id > Number limit 10; 這樣由於可以使用上id的主鍵索引，所以可以快速的定位，達到一個大資料的分頁的效果。

    ?

    ?

    問題：

    a. select * from tableName where id > Number limit page;

    b. select * from tableName limit Number, page;

    ?

    當上面的這兩條sql語句執行後，結果在什麼時候完全一致，什麼時候不一致？

    答：當資料沒有被物理行刪除的時候。這個時候資料是一致的，但是有物理行刪除的時候，資料是不一致的。

    如何解決上面的問題？

    答：既然是物理行刪除造成的，那就不做物理行的刪除，只做邏輯刪除（設定一個is_delete 欄位 0 代表沒有刪除 1 代表已經刪除）。

    使用邏輯刪除之後，資料會一致，只要在資料顯示的，在顯示層面（HTML）讓is_delete=1 不顯示出來即可。if( is_delete == 1) echo ‘該條資訊已被刪除！‘ 例如：常見的百度貼吧，網易新聞端。

    ?

    3. 實現物理行刪除，不限制使用者行為

    答：這個時候可以使用 索引覆蓋 + 延時關聯技巧 來實現。

    分析：

    程式碼實現：

    在php層面實現????????

26. 由於主鍵id是可以快速尋找，先查出滿足條件id

    ?

27. 然後根據id快速的去尋找對應的記錄

    foreach($data as $k=>$v){

    $sql = select * from tableName where id = $v;

    $res = mysql_query($sql);

    $row = mysql_fetch_assoc($res);

    $result[]= $row;

    }

    $result //分頁資料

    ?

    在MySQL層面聯表處理：

    使用聯表來完成大資料的分頁操作

    ?

    解釋

MySQL常見注意事項及最佳化