mysql 分表與分區

標籤：

一.作業環境

   資料達到百w甚於更多的時候，我們的mysql查詢將會變得比較慢， 如果再加上連表查詢，程式可能會卡死。即使你設定了索引並在查詢中使用到了索引，查詢還是會慢。這時候你就要考慮怎麼樣來提高查詢速度了。

   拋棄其他的不講，只從mysql本身的最佳化來講，我所知道的方法有三種：mysql叢集，mysql分表，mysql分區

二.mysql叢集

   mysql叢集成本比較高，不過這不是這裡講的重點，後期開一篇文章，專門來講這方面的知識。

 

三.mysql分表

  1. 當資料達到百w，千w的時候，我們就想著怎麼把表資料分流到不同的表中去，縮小單個表的體積，表的操作最後還是歸根到對磁碟檔案的操作，小檔案的IO速度肯定要比大檔案的速度要快。

  2.分表的方式：分表有橫向分表和縱向分表兩種方式。

    橫向分表：指把表的資料分流要不同的表中去，比如100W的表資料分流到10個表中，每個表就只有10w條資料。

    縱向分表：指的是拆分表的結構，比如把新聞的標題和新聞的內容拆開，放到不同的表中。這種方法主要是運用在前期的表開始設計階段。

   3. 著重講一個橫向分表的方法：

       1）：hash演算法，就是通過一定的演算法，得出一個值，再根據這個表知道去操作對應的表，比如我想分10個表，user1,user2,user3..........假設我們用uid來做為分表的計算欄位,通過取餘的方式來。某個使用者的使用者ID是5，5%10=5，那這個使用者的資料就是放在user5這個表中。    這就是取餘演算法，當然還有其他的，具體根據實際業務的需求來定。

       2）：利用mysql本身的分表引擎merge

              這種方式適合在設計表的時候考慮不周，已經出現大的資料，並查詢緩慢的情況 。這種方式對程式碼的改動比較小，因為sql語句寫好了，如果再採用第一種的分表方式的話，一個表拆分成多個表了，那程式碼對錶的操作都要改。麻煩啊。

            

1. mysql> CREATE TABLE IF NOT EXISTS `user1` (  
2.  ->   `id` int(11) NOT NULL AUTO_INCREMENT,  
3.  ->   `name` varchar(50) DEFAULT NULL,  
4.  ->   `sex` int(1) NOT NULL DEFAULT ‘0‘,  
5.  ->   PRIMARY KEY (`id`)  
6.  -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  
7. Query OK, 0 rows affected (0.05 sec)  
8.   
9. mysql> CREATE TABLE IF NOT EXISTS `user2` (  
10.  ->   `id` int(11) NOT NULL AUTO_INCREMENT,  
11.  ->   `name` varchar(50) DEFAULT NULL,  
12.  ->   `sex` int(1) NOT NULL DEFAULT ‘0‘,  
13.  ->   PRIMARY KEY (`id`)  
14.  -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  
15. Query OK, 0 rows affected (0.01 sec)  
16.   
17. mysql> INSERT INTO `user1` (`name`, `sex`) VALUES(‘張映‘, 0);  
18. Query OK, 1 row affected (0.00 sec)  
19.   
20. mysql> INSERT INTO `user2` (`name`, `sex`) VALUES(‘tank‘, 1);  
21. Query OK, 1 row affected (0.00 sec)  
22.   
23. mysql> CREATE TABLE IF NOT EXISTS `alluser` (  
24.  ->   `id` int(11) NOT NULL AUTO_INCREMENT,  
25.  ->   `name` varchar(50) DEFAULT NULL,  
26.  ->   `sex` int(1) NOT NULL DEFAULT ‘0‘,  
27.  ->   INDEX(id)  
28.  -> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ;  
29. Query OK, 0 rows affected, 1 warning (0.00 sec)  
30.   
31. mysql> select id,name,sex from alluser;  
32. +----+--------+-----+  
33. | id | name   | sex |  
34. +----+--------+-----+  
35. |  1 | 張映 |   0 |  
36. |  1 | tank   |   1 |  
37. +----+--------+-----+  
38. 2 rows in set (0.00 sec)  
39.   
40. mysql> INSERT INTO `alluser` (`name`, `sex`) VALUES(‘tank2‘, 0);  
41. Query OK, 1 row affected (0.00 sec)  
42.   
43. mysql> select id,name,sex from user2  
44.  -> ;  
45. +----+-------+-----+  
46. | id | name  | sex |  
47. +----+-------+-----+  
48. |  1 | tank  |   1 |  
49. |  2 | tank2 |   0 |  
50. +----+-------+-----+  
51. 2 rows in set (0.00 sec) 

             

a，如果你使用 alter table 來把 merge 表變為其它表類型，到底層表的映射就被丟失了。取而代之的，來自底層 myisam 表的行被複製到已更換的表中，該表隨後被指定新類型。

b，網上看到一些說replace不起作用，我試了一下可以起作用的。暈一個先

c，一個 merge 表不能在整個表上維持 unique 約束。當你執行一個 insert，資料進入第一個或者最後一個 myisam 表（取決於 insert_method 選項的值）。mysql 確保唯一索引值在那個 myisam 表裡保持唯一，但不是跨集合裡所有的表。

d,當你建立一個 merge 表之時，沒有檢查去確保底層表的存在以及有相同的機構。當 merge 表被使用之時，mysql 檢查每個被映射的表的記錄長度是否相等，但這並不十分可靠。如果你從不相似的 myisam 表建立一個 merge 表，你非常有可能撞見奇怪的問題。

  

      3):第一種與第二種的比較，第一種的效率會比較高，但是第二種的擴充性會好一點。

 

mysql 分表與分區