MySQL分區，mysql表分區

MySQL分區，mysql表分區

mysql中資料庫learn目錄結構:

看一下表sales的定義：

show create  table sales \G

*************************** 1. row ***************************Table: salesCreate Table: CREATE TABLE `sales` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `amount` double NOT NULL,  `order_day` datetime NOT NULL,  PRIMARY KEY (`id`,`order_day`)) ENGINE=InnoDB DEFAULT CHARSET=latin1/*!50100 PARTITION BY RANGE (YEAR(order_day))(PARTITION p_2010 VALUES LESS THAN (2010) ENGINE = InnoDB, PARTITION p_2011 VALUES LESS THAN (2011) ENGINE = InnoDB, PARTITION p_2012 VALUES LESS THAN (2012) ENGINE = InnoDB, PARTITION p_catchall VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */1 row in set (0.00 sec)

表p_key的定義

show create  table p_key \G

*************************** 1. row ***************************Table: p_keyCreate Table: CREATE TABLE `p_key` (  `id` int(10) NOT NULL AUTO_INCREMENT,  `keyname` char(20) DEFAULT NULL,  `keyval` varchar(1000) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8/*!50100 PARTITION BY KEY (id)PARTITIONS 4 */1 row in set (0.01 sec)

對於MyISAM引擎，一張表對於儲存了3個檔案，fm儲存表結構，myi存放索引，myd存放資料。但p_key對應的還有一個檔案p_key.par。

重新建立一個Range分區的表fuhui_log，體驗分區查詢：

DROP TABLE IF EXISTS fuhui_log;CREATE TABLE fuhui_log (    object_id int(11),    title varchar(20) NOT NULL  ,    content varchar(20) ,    time int(11),    primary key (object_id))PARTITION BY range (object_id)(    PARTITION p1 VALUES less than (5000),    PARTITION p2 VALUES less than (10000),    PARTITION p3 VALUES less than MAXVALUE);

自訂預存程序，向資料庫中插入20000條資料：

delimiter //create procedure fun_fuhui_log() begin    declare i int;    set i = 1;    while i < 20000 do        insert into fuhui_log(object_id,title,content,time) values (i,concat('title_',i),'test content',i);        set i = i+1;    end while;end//

調用預存程序，進行資料插入：

delimiter ;call fun_fuhui_log();

擷取插入資料結果：

 select count(*) from fuhui_log;

查詢結果為19999，耗時：1 row in set (0.01 sec)；

select * from fuhui_log where object_id = 13588;

耗時0.00 sec

根據如上的步驟，建立一個基本表，並修改預存程序，插入同樣的資料：

DROP TABLE IF EXISTS fuhui_log2;CREATE TABLE fuhui_log2 (    object_id int(11),    title varchar(20) NOT NULL  ,    content varchar(20) ,    time int(11),    primary key (object_id));

資料結構設計的太簡單，資料量太小，看不出效果來，重先修改預存程序，插入80000條資料：

while i < 80000 do        replace into fuhui_log2(object_id,title,content,time) values (i,concat('title_',i),'test content',i);        set i = i+1;end while;

select count(*) from fuhui_log2;

執行結果：1 row in set (0.02 sec)

select count(*) from fuhui_log;

執行結果：1 row in set (0.03 sec)【沒有按照邏輯出牌】

這個例子很失敗，修改表結構，去掉primary key

 alter table fuhui_log drop primary key; alter table fuhui_log2 drop primary key;

例子仍然比較失敗，執行的效率很難發現

select * from fuhui_log where object_id = 56770 \G

耗時：0.05sec

select * from fuhui_log2 where object_id = 56770 \G

耗時0.06sec

對於count統計，fuhui_log比fuhui_log2耗時都多，count的並行計算，都被我給玷汙了

修改分區結構，重新計算：

 alter table fuhui_log reorganize partition p3 into ( partition p3_1 values less than (30000), partition p3_2 values less than (50000), partition p3_3 values less than MAXVALUE);

查看重新分區後的結果：

select table_schema,table_name,partition_name,PARTITION_METHOD from information_schema.partitions where table_name='fuhui_log';

然後重新計算：

select count(*) from fuhui_log ;

執行效果0.04sec，跟fuhui_log2的統計時間相等了。但是

select * from fuhui_log where object_id = 56770 \G

執行時間變成了0.02sec

居然已經寫這麼久了，今天就此罷筆吧