MySQL最佳化技術

MySQL最佳化技術
簡介

由於頁面靜態化技術可以實現對動態資料的緩衝，但是有的時候還是需要去請求資料庫。所以對資料庫的最佳化也是不可缺少的。

最佳化思路

設計：儲存引擎，欄位，範式

自身：索引，自身的緩衝

架構：讀寫分離

 

儲存引擎：

MyISAM和InnoDB之間的對比。當然需要知道MySQL除了這兩種儲存引擎還有其他的儲存引擎（memory儲存引擎）。

MySQL在5.5版本之後預設的儲存引擎為InnoDB

在面試的過程中，只要說出MyISAM和InnoDB的區別即可

 

欄位選擇：

合適即好，能用tinyint就不要使用int

 

範式：三範式 主要是為了減少資料的一個冗餘，基本上設計出來的表都是滿足的。

 

注意：一般在開發的時候，設計之初，最先設計的就是表與表之間的關係，在後期的開發過程中是很少修改表結構的。（系統架構、經理）

 

自身考慮

在做MySQL自身最佳化的時候，既然要去最佳化，那就要先去發現問題？如何發現問題？

發現問題

1. 需要去定位問題？

   答：可以使用MySQL提供的一種慢查詢日誌功能來發現有問題的sql語句，然後對其進行最佳化。

2. 什麼事慢查詢日誌？

   答：MySQL提供的日誌，可以用來記錄超過某一個規定的時間界限的sql語句。

   使用MySQL的慢查詢功能
3. 開啟，MySQL的設定檔裡面，預設是不存在的，需要自己去添加，需要重啟MySQL

    

4. 測試看是否能夠記錄超過時間界限的sql語句
   1. 需要構建一張大型資料表

   b) 在沒有索引的欄位上做一個查詢

   慢查詢日誌分析：

   注意：在有索引的欄位上做查詢操作

    

   總結：通過對比發現使用索引之後，明顯查詢的速度會快很多，但是使用MySQL的慢查詢日誌功能記錄的時間相對來說還是很粗糙（慢查詢的記錄時間層級只能是 1s 以上的）的。

   這個時候如果需要記錄時間為 1s 以下的時候，慢查詢就無法發揮其作用，可以使用MySQL提供另外一個功能來完成，這個功能叫做 profile 。

    

   MySQL的profile的使用

    

   MySQL提供的一個可以記錄更加精確時間的功能，能在1s 以下的sql語句都記錄下來。

    

5. 使用：預設是關閉的，需要自己開啟

   # show varibales like "%profil%"; 當有的時候無法記清的時候，可以使用這種模糊比對查詢。

6. 開啟

   # set profiling=1;

7. 簡單的測試

   # show profiles; 可以查看profile工具記錄的時間

8. 使用該工具還可以詳細的分析這個sql語句在執行的時候，每一步（如：串連，許可權檢查，sql語句編譯，開啟表。。。。執行，資料返回）花費的時間。
   1. 例如現在詳細的定位第四條sql語句執行時候每一步花費的時間

      # show profile for query 4;

   注意：為什麼使用索引之後會那麼快？

   答：由於MySQL啟動的時候一般也會將索引檔案載入記憶體裡面，即使沒有，索引的載入也比去磁碟上對一條一條的尋找要快。 然後索引本身是非常利於尋找的一種結構，這個時候可以通過索引快速的去定位需要查詢的資料，當查詢到之後，然後在去磁碟上將資料取出來。

    

   總結：通過使用上面的這些工具，可以粗略發現，在使用索引之後，效果有明顯的提升，所以有必要對MySQL資料庫進行索引方面的最佳化操作。

    

   MySQL索引簡介

    

   MySQL索引的分類
9. 主鍵索引：主要是其快速尋找和約束的效果，並且一張表裡面只能有一個主鍵索引。不允許欄位裡面出現null
10. 唯一索引：主要是快速尋找和約束，但是欄位可以有null存在，在建立唯一索引的欄位上面的資訊不允許重複，但是可以為多個欄位建立多個唯一索引。
11. 普通索引：主要是為了快速尋找，但是需要注意的是，索引建立也是有代價的。索引能快速的尋找，但是在資料插入、更新的時候，索引會影響插入的效率。所以普通索引也不要建立的過多。
12. 全文索引：一般都是對text欄位生效，但是由於MySQL的儲存引擎只有MyISAM支援，並且只支援英文，所以可以使用另外的一個軟體叫做sphinx來代替全文索引。
    1. 還有一種情況，就是需要注意一般使用 like 做模糊查詢的時候 select * from tableName where name like "%張%"; 是無法使用索引的。 like這種查詢就要做全表掃描。
    索引的管理

     

    索引是一種排好序的，快速尋找的資料結構。

    什麼叫排好序？

    例如上面的這個結構，可以發現，在D字母之後是不可能在出現A字母相關的資訊的。

     

13. 索引的建立
    1. 建表時候建立
    1. 在修改表結構時候建立

     

14. 索引顯示

     

15. 索引的刪除

    問題：為什麼沒有提供更新操作？

    答：沒必要專門設計一個更新，只需要先刪，在建。

     

    索引的結構

     

    由於索引本身也是一種資料，也需要佔據磁碟空間，所以索引也是有資料結構的，常見的資料結構分為兩類：B-TREE結構 和 聚簇結構。

     

    B-TREE結構

     

    對於B-TREE結構的索引，索引節點裡面儲存的是物理行地址，當查詢的時候需要做回行的操作。對於MyISAM儲存引擎的所有索引時候的都是B-TREE結構。

    結構圖：

    聚簇結構

     

    對於聚簇結構的索引，索引節點裡面儲存的是該行的記錄，當查詢的時候不需要做回行的操作。對於InnoDB儲存引擎的所有索引時候的都是聚簇結構。

    注意1：對於InnoDB的儲存引擎來說，如果不存在主鍵索引，這個時候MySQL會自動的維持一個主鍵索引。

    注意2：對於InnoDB的儲存引擎來說，次級索引（唯一和普通索引）的索引節點裡面儲存的資訊是對主鍵索引的一個引用。

    注意3：對於InnoDB的儲存引擎來說，如果是大批量的添加資料，這個時候會有很大的效能開銷， 主要是在大批量插入資料的時候，主鍵索引也會重建，這個時候由於主鍵索引的索引節點裡面儲存的是該行的一個記錄，所以要大批量的在記憶體中移動，開銷非常大。

    結構圖：

     

    擴充

     

    閱讀書籍

    <<高效能MySQL>>

    地址：

     

    問題1：

    解決思路：

    只要使得上面的兩個條件中的一個或者兩個都不成立即可。

    那可以做一個重新整理緩衝的按鈕，再要在緩衝有效期間內更改了，就清除產生的靜態頁。

本文永久更新連結地址：