MySQL常見注意事項

MySQL常見注意事項

• 模糊查詢 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字母這段範圍之內只能逐行比較。

   

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

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

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

   

   

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

   

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

  答：

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

   

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

   

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

  答：

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

   

• 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自身的緩衝需要注意兩點：

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

  分析：

   

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

   

   

• MySQL的架構分析（讀寫分離）

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

  怎麼查詢是讀為主？

  # show status like "%Com_%";

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

   

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

   

   

   

  讀寫分離概圖：

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

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

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

   

  問題：

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

  需要解決一致性的問題？

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

   

  使用步驟：

  主伺服器配置：

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

   

  從伺服器配置：

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

   

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

   

   

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁