MySQL最佳化四（最佳化表結構）

標籤：create   水平   div   緊急   提高效率   註冊   今天   tin   建立   

MySQL最佳化四（最佳化表）

昨晚吃吃喝喝的太多，熬夜到淩晨二點。今天頭髮雜亂，臉龐憔悴，像是吸毒了。下午去買衣服，肚子一看大了不少。奈何女朋友還沒有一個，就已經發福了。管不住口，邁不開腿。

一、最佳化表結構

1.盡量將表欄位定義為NOT NULL約束，這時由於在MySQL中含有空值的列很難進行查詢最佳化，NULL值會使索引以及索引的統計資訊變得很複雜，可以使用0或者Null 字元串來代替。

2.可以使用enum、set 等符合資料類型。對於只包含特定類型的欄位。不過在工作過程中一般就使用tinyint 來表示了。

3.數值型欄位的比較比字串的比較效率高得多，欄位類型盡量使用最小、最簡單的資料類型。IP地址可以使用int類型。

二、表拆分1、垂直分割

垂直分割按照欄位進行拆分，其實就是把組成一行的多個列分開放到不同的表中，這些表具有不同的結構，拆分後的表具有更少的列，例如使用者表中的一些欄位可能經常訪問，可以把這些欄位放進一張表裡。另外一些不經常使用的資訊就可以放進另外一張表裡。插入的時候使用事務，也可以保證兩表的資料一致。缺點也很明顯，由於拆分出來的兩張表存在1：1的關係，需要使用冗餘欄位，而且需要join操作，我們在使用的時候可以分別取兩次，這樣的來說既可以避免join操作，又可以提高效率。

2、水平分割

水平分割按照行進行拆分，常見的就是分庫分表。以使用者表為例，可以取使用者ID,然後使用php的十進位轉換16進位的方法 dechex,截取其中的第一個字元，將使用者均勻的分配進這 0-9 、a-f 這16個表中。尋找的時候也按照這種規則，又快又方便。當然類似的規則很多，也可以使用求餘法，按照餘數將資料分發進不同的表中。有些表業務關聯比較強，那麼可以使用按時間劃分的。以我公司的某業務為例，每天都要建立一張表。這種業務類型就是需要高速插入，但是對於查詢的效率不太關心。表越大，插入資料所需要索引維護的時間也就越長。

三、分區

分區這個概念，我第一次在實踐中看到是在去年，資料中心的表按照時間一個月分成一個區。我從這些表中讀取資料，然後繼續做後續的業務處理。一般來說在主業務中很少使用這種分區概念，使用分區是大資料處理後的產物。比如系統使用者的註冊推廣等等，會產生海量的日誌，當然也可以按照時間去建立多張表，但在實際操作中，就發生過一次營運人員忘記切換表，導致資料報錯的緊急事件。可見分區適用於日誌記錄，查詢少，一般用於背景資料報表分析。對於這些資料匯總需求，需要很多日誌表去做資料彙總，我們能夠容忍1s到2s的延遲，只要資料準確能夠滿足需求就可以。

MySQL主要支援4種模式的分區：range分區、list預定義列表分區，hash 分區，key索引值分區。後幾種沒有看到資料中心的同事用過，可能是操作不方便，應用不廣泛，所以就暫且不講了。

-- 建立表create table testpar(    f_userid int unsigned not null default 0,    f_date datetime)engine=innodb,charset=utf8;-- 分區alter table testpar  partition by range columns(f_date) (    partition p0 values less than (‘2017-01-31‘),    partition p2 values less than (‘2017-02-20‘));-- 查看錶結構mysql> show create table testpar\G*************************** 1. row ***************************       Table: testparCreate Table: CREATE TABLE `testpar` (  `f_userid` int(10) unsigned NOT NULL DEFAULT ‘0‘,  `f_date` datetime DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8/*!50500 PARTITION BY RANGE  COLUMNS(f_date)(PARTITION p0 VALUES LESS THAN (‘2017-01-31‘) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (‘2017-02-20‘) ENGINE = InnoDB) */1 row in set (0.00 sec)

MySQL最佳化四（最佳化表結構）