MySQL之資料庫結構最佳化

標籤：blog   ar   io   使用   sp   for   on   資料   div   

1.選擇合適的資料類型

　　一、選擇能夠存下資料類型最小的資料類型

　　二、可以使用簡單的資料類型。int  要比varchar在MySQL處理上簡單

　　三、儘可能的使用not null  定義欄位

　　四、盡量少用txt類型，非用不可時考慮分表。

　　五、舉例：

　　　　使用int類型儲存日期時間，利用FROM_UNIXTIME(),UNIX_TIMESEAMP()兩個函數轉換

　　

mysql> SELECT FROM_UNIXTIME(1234567890, ‘%Y-%m-%d %H:%i:%S‘);+------------------------------------------------+| FROM_UNIXTIME(1234567890, ‘%Y-%m-%d %H:%i:%S‘) |+------------------------------------------------+| 2009-02-14 07:31:30                            |+------------------------------------------------+1 row in set (0.00 sec)mysql> SELECT UNIX_TIMESTAMP(2009-02-14 07:31:30);ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘07:31:30)‘ at line 1mysql> SELECT UNIX_TIMESTAMP(‘2009-02-14 07:31:30‘);+---------------------------------------+| UNIX_TIMESTAMP(‘2009-02-14 07:31:30‘) |+---------------------------------------+|                            1234567890 |+---------------------------------------+1 row in set (0.00 sec)mysql>

　　

　　使用bigint儲存IP地址，利用INET_ATON(),INET_NTOA()兩個函數轉換

　　　　

mysql> select    inet_aton(‘192.168.1.200‘);+----------------------------+| inet_aton(‘192.168.1.200‘) |+----------------------------+|                 3232235976 |+----------------------------+1 row in set (0.00 sec)mysql> select inet_ntoa(3232235976);+-----------------------+| inet_ntoa(3232235976) |+-----------------------+| 192.168.1.200         |+-----------------------+1 row in set (0.00 sec)

2.表的範式最佳化

　　一、標的範式化設計（符合三範式要求）

　　　　a:第一範式，每個屬性不能拆分。

　　　　b:第二範式,含有主鍵。

　　　　c:不能存在非關鍵字的傳遞依賴，都必須依賴主鍵。

　　範式可以避免資料冗餘，減少資料庫的空間，減輕維護資料完整性的麻煩。

　　二、表的反範式化

　　　　表的反範式是指為了查詢效率的考慮吧原來符合第三範式的表適當的增加冗餘，已達到最佳化查詢的目的。反範式是一種以空間換取時間的操作。

　　　　舉例：如我們現在要對一個 學校的課程表進行操作，現在有兩張表，一張是學生資訊student（a_id,a_name,a_adress,b_id）表，一張是課程表 　　　　　　　　       　　　subject(b_id,b_subject)，現在我們需要一個這樣的資訊，把選擇每個課程的的課程名稱和學生姓名輸出來：

　　　　　　　　SQL語句為：select  B.b_id,B.b_subject,A_a_name from student A ,subject B;

　　　　當上面的資料量不多時，我們這樣去查詢沒有問題；當我們的兩張表的資料都是在百萬級的時候，我們去查上面的資訊， 問題出現了，這個查詢動不動就是幾百毫秒，               甚至更慢，這樣的查詢效率根本不能滿足我們對於網頁速度的要求。我們可以這樣設計：在課程表裡面添加冗餘欄位——學生姓名，這 樣 我們就可以通過下面的查詢               達到同樣的目的：

　　　　　　SQL語句為：select  b_id,b_subject,a_name from subject B;

　　這樣的執行結果會在效率上面最佳化很多。可以通過SQL查詢試試看。

3.表的垂直分割

　　標的垂直分割就是指把含有很多列的表拆分為多個表，這樣就可以解決表的寬度問題。拆分原則如下：

　　　　a:不太常用的欄位放到一個表中

　　　　b:把大欄位獨立的放到一個表中

　　　　c：把經常使用的欄位放到一個表中

4.表的水平分割

　　表的水平分割是為瞭解決表中資料量大的問題，水平分割的表每一個表結構都是完全一致的。

　　　　一、根據業務屬性拆表

　　　　　　用業務屬性拆表，業務關係複雜的情況下，如果要根據其他條件查詢，其他的條件都必須和這個屬性關聯起來，查詢條件必須帶有這個屬性。這種分表方式的演算法             大致是模數，hash，md5等。

　　　　二、根據時間拆表

　　　　　　當表的關係比較複雜時，無法根據某個維度進行分表。但是有明顯的時效性。

　　　　三、根據自增長ID拆表

　　　　　　這種分割法不是模數分，而是每張表存指定量的資料。如果資料量到了，就存放到新表中。這樣可以完全控制每張表的資料量。關係非常簡單並且有時效性的情況下           可以用。

　　　　四、資料移轉的方式

　　　　　　當一些很久之前的資料，很少再查詢。比如員工工資表，我們可以只存今年的工資情況。而曆史資料我們可以遷移到一張salary_old表中,保證資料不會丟失。但            也可以用來查詢。

MySQL之資料庫結構最佳化