mysql資料庫分割槽技術

mysql資料庫的分割槽技術，可以有效地解決大資料量引起的讀寫效率低的問題。

資料庫分割槽技術是mysql5.1版本以後才有的，該技術實現的目的大致上與資料庫的分表技術類同，不過對於php開發人員來說不用修改所讀取的表名，大大減少了由於分為多個水平表引起的維護代價。

一般來說一個資料庫超過了1000萬條時，各種查詢開銷都會很多，如果使用索引，索引佔用記憶體與CPU也是很大的，這時候就需要考慮分表或者分割槽了，分表之後引起了程式碼維護量過多，這裡建議分割槽，分割槽其實也會生成多個物理上的檔案，但是邏輯上還是同一個表。

我這裡的日誌分析表，每天基本上在1萬條，一年下來一個表基本上就幾百萬條記錄，可能到時候就會佔用過多的記憶體，而且查詢效率也不高。

由於日誌每天都會有記錄，不存在寫少讀多的情況，所以這裡建立表時就用innodb引擎，基本上可以保證不會因為鎖表相互影響。

而myisam引擎在讀表時，會導致另一個程式無法寫入該表，這就明顯不適合這一個工作場景了。

將每一個月的記錄儲存到一個分割槽裡，也可以將一個季度的資料放到一個分割槽中，這裡將每月的記錄儲存到了一張表中。分割槽的依據是寫日誌的時間，按照range進行分割槽。

基本上每一個表都要有一個主鍵，這裡不能以id作為主鍵，但是可以是自增長的型別，如果以id為主鍵，則分割槽依據中一定要包含id欄位才行，

這裡只是以range（oprtime）作為分割槽的依據，所以不用將id設為主鍵，但是這條日誌資訊需要看詳情時，需要根據id來查詢（這樣效率高一些），就需要將id作為一個索引了。

create table webservicelog(`id` int(11) unsigned not null auto_increment,
`fromto` tinyint(1) not null default '0',
`biztype` tinyint(2) not null default '0',
`bizcode` varchar(32) not null default '',
`result` tinyint(1) unsigned not null default '0',
`errmsg` varchar(256) not null default '',
`desc` varchar(100) not null default '',
`oprtime` datetime,
key `id` (`id`),
key `biz`(`biztype`,`bizcode`),
KEY `operatetime` (`oprtime`))ENGINE=InnoDB AUTO_INCREMENT=1  DEFAULT CHARSET=utf8 
partition by range(to_days(`oprtime`))
(partition p201610 values less than (to_days('2016-11-01')),
partition p201611 values less than (to_days('2016-12-01')),
partition p201612 values less than (to_days('2017-01-01')),
partition p201701 values less than maxvalue);


insert into webservicelog(fromto,biztype,bizcode,result,oprtime) values(1,1,'areyouok',1,'20161018081011');
insert into webservicelog(fromto,biztype,bizcode,result,oprtime) values(1,1,'areyouok',1,'20161118081011');
insert into webservicelog(fromto,biztype,bizcode,result,oprtime) values(1,1,'areyouok',1,'20161218081011');
insert into webservicelog(fromto,biztype,bizcode,result,oprtime) values(1,1,'areyouok',1,'20170118081011');
insert into webservicelog(fromto,biztype,bizcode,result,oprtime) values(1,1,'areyouok',1,'20170218081011');

如何檢視分割槽檔案呢？

可以看mysql的data資料夾下面的資料庫檔案，就可以檢視當前分割槽情況。

檢視一下mysql程式的datadir目錄，如下圖所示：

然後進入到datadir目錄

cd /var/lib/mysql
cd test
可以看到webservicelog表的相關檔案，如下圖所示：

其中的以 .ibd結尾的那幾個檔案就是分割槽檔案了。

也可以通過INFORMATION_SCHEMA.PARTITIONS表來檢視分割槽的詳細資訊，
可以檢視錶具有哪幾個分割槽、分割槽的方法、分割槽中資料的記錄數等重要資訊

檢視分割槽是否生效

explain partitions select * from webservicelog where oprtime< date '2016-10-21';
+----+-------------+---------------+------------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+---------------+------------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | webservicelog | p201610    | range | operatetime   | operatetime | 6       | NULL |    1 | Using where |
+----+-------------+---------------+------------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.00 sec)

###刪除並重新建立分割槽
alter table webservicelog drop partition p201701;
alter table webservicelog add partition(partition p201701 values less than (to_days('2017-02-01')));
alter table webservicelog add partition(partition p201702 values less than (to_days('2017-03-01')));

分割槽型別的區別：

hash型別只能針對int型別，但是key型別可以針對int型別欄位也可以針對字元型欄位。