MySQL大數據查詢效能最佳化教學（圖）

MySQL效能最佳化包含表格的最佳化與列類型選擇，表的最佳化可以細分為什麼？  1、定長與變長分離；2、常用字段與不常用字段要分離；  3、在1對多，需要關聯統計的字段上添加冗餘字段。

一、表格的最佳化與欄位型別選擇

#表的最佳化：

        #1、延長與變長分離

                如id int,佔4個位元組，char(4)佔4個字元長度，也是定長，time即每個單元值佔的位元組是固定的。

                核心且常用字段，宜建成定長，放在一張表中。

                而varchar,text,blob這種變長字段，適合單放一張表，用主鍵與核心表關聯。

        2、常用欄位與不常用欄位要分離

                選擇結合網站特定的業務來分析，分析欄位的查詢場景，查詢頻率較低的欄位，單一頻率值拆出來。

        3、在1對多，需要關聯統計的欄位上新增冗餘欄位。

                看如下的效果：

##               「每個版塊中，有一個版本的貼文數。

                這是如何做的

           ，select count(*) from post group by board_id，得出每個版塊的貼文數。

二、列型別選擇

        1、欄位類型優先權

         整數>date#;date

#;date

#;date

#;date

#; ##         time>enum

        char>varchar>blob,text

        整數：定長，沒有國家/地區之分，也沒有字元集的差異。例如：

       tinyint 1,2,3,4,5 char(1) a,b,c,d,e

       從空間上，皆佔1個字節，但是order by 排序，前者快。原因，或需要考慮字元集與校對集（就是排序規則）；

       time定長，運算快，節省空間。考慮時區，寫sql時不方便where > `2018-08-08`;

       enum,能起到約束的目的，內部用整數來存儲，但與cahr聯查時，內部要經歷串與值的轉換；

       char定長，考慮字元集與（排序）校對集；

       varchar不定長，要考慮字元集的轉換與排序時的校對集，速度慢；

       text/blob 無法使用記憶體臨時表（排序等作業只能在磁碟上進行）

       附：關於date/time的選擇，大師的明確意見，直接選int unsgined not null,儲存時間戳記。

      例如：

##      性別：以utf8為例

      char(1) ,3個字長位元組

##   頟 enum('男','女')； 內部轉成數字來存，多一個轉換過程

      tinyint()， 定長1個字節

   

  2、夠用就行，不要慷慨（如smallint varchar(N)）

      原因：大的位元組浪費內存，影響速度。

      以年齡為例 tinyint unsigned not null，可儲存255歲，足夠。用int浪費了3個位元組；

      以varchar(10),varchar(300)儲存的內容相同，但在表聯查時varchar(300)要花更多記憶體。

     

3、盡量避免用NULL()

      原因：NULL不利於索引，要用特殊的字元來標示。

      在磁碟上佔據的空間其實更大（MySQL5.5已對null做的改進，但查詢仍是不便）

三、索引最佳化策略

1、索引類型

       

1.1 B-tree索引

#

        名叫btree索引，大的面向看，都用的平衡樹，但具體的實現上，各引擎稍有不同，比如，嚴格的說，NDB引擎，使用的是T-tree.

       但抽像B-tree系統，可理解為「排好序的快速查詢結構」。

       1.2 hash索引

       在memory表裡預設為hash索引，hash的理論查詢時間複雜度為O(1)。

       疑問：既然hash的查找如此高效，為什麼不都用hash索引？

       回答：

      1、hash函數計算後的結果，是隨機的，如果是在磁碟上放置數據，以主鍵為id為例，那麼隨著id的增長，id對應的行，在磁碟上隨機放置。

      2、無法對範圍查詢進行最佳化。

      3、無法利用前綴索引，例如在btree中，field列的值「helloworld」,並加上索引查詢x=helloworld自然可以利用索引，x=hello也可以利用索引（左前綴索引） 。

       4、排序也無法最佳化。

       5、必須回行，就是說透過索引拿到資料位置，必須回到表格中取資料。

        2、btree索引的常見誤解

       2.1 在where條件常用的欄位加索引，例如：

       where cat_p ;100;查詢第三個欄目，100元以上的商品。

       誤解：cat_id 上方和price上都加上索引。

       錯：只能使用上cat_id 或 price索引，因為是獨立的索引，同時只能用一個。

       2.2 在多列上建立索引後（共同索引），查詢哪個列，索引都會將發揮作用

       誤區：多列索引上，索引發揮作用，需要滿足左前綴要求。

       以index(a,b,c) 為例，（注意力和順序有關）

4、索引實驗

        例如：select * from t4 where c1=3 and c2 = 4 and c4>5 and c3=2;

##       使用了哪些索引：##se# from#      explect   where c1=3 and c2 = 4 and c4>5 and c3=2 \G

      如下：

           ）

五、叢集索引與非叢集索引

Myisam與innodb引擎，索引檔案的異同

Myisam：由news.myd和new.myi兩個文件，索引文件和資料檔是分開的，叫做非叢集索引。主索引和次索引都指向實體行（磁碟的位置）

innodb：索引和資料是聚在一起的，所以是叢集索引。 innodb的主索引檔案上直接存放該行數據，次索引指向對主鍵索引的引用。

注意：innodb來說：

1、主鍵索引 即存放索引值，又在葉子中儲存行的資料。

2、如果沒有主鍵（primary key），則會unique key做主鍵。

3、如果沒有unique，則係統產生一個內部的rowid做主鍵。

4、像innodb中，主鍵的索引結構中，即儲存了主鍵值又儲存了行數據，這種結構稱為叢集索引。

叢集索引

優點：根據主鍵查詢項目比較少時，不用回行（資料就在主鍵節點下）

劣勢：如果碰到不規則資料插入時，造成頻繁的頁分裂

相關文章：

Mysql 效能最佳化

##相關影片：

MySQL優化影片教學

#

以上是MySQL大數據查詢效能最佳化教學（圖）的詳細內容。更多資訊請關注PHP中文網其他相關文章！