?表數據量大的時候一般都考慮水平拆分,即所謂的sharding.不過mysql本身具有分區功能,可以實現一定程度?

的水平切分.
mysql是具有MERGE這種引擎的,就是把一些結構相同的MyIASM表作為一個表使用,但是我覺得 MERGE不如partition實用,
因為MERGE會在所有的底層表上查詢,而partition只在相應的分區上查詢.
建立了兩個表,分別為分區和未分區的,分區表按年進行分區.
Sql代碼

CREATE TABLE `20130117date_par` (

`content` varchar(20) NOT NULL,

`create_time` datetime NOT NULL,

KEY `20130117date_idx_date` (`create_time`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE (YEAR(create_time))

(PARTITION p2009 VALUES LESS THAN (2010),

PARTITION p2010 VALUES LESS THAN (2011),

PARTITION p2011 VALUES LESS THAN (2012),

PARTITION p2012 VALUES LESS THAN (2013),

PARTITION p2013 VALUES LESS THAN (2014))

?

CREATE TABLE `20130117date` (

`content` varchar(20) NOT NULL,

`create_time` datetime NOT NULL,

KEY `20130117date_idx_date` (`create_time`)

) ENGINE=InnoDB

CREATE TABLE `20130117date_par` (`content` varchar(20) NOT NULL,`create_time` datetime NOT NULL,KEY `20130117date_idx_date` (`create_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE (YEAR(create_time)) (PARTITION p2009 VALUES LESS THAN (2010),PARTITION p2010 VALUES LESS THAN (2011),PARTITION p2011 VALUES LESS THAN (2012),PARTITION p2012 VALUES LESS THAN (2013),PARTITION p2013 VALUES LESS THAN (2014))CREATE TABLE `20130117date` (`content` varchar(20) NOT NULL,`create_time` datetime NOT NULL,KEY `20130117date_idx_date` (`create_time`) ) ENGINE=InnoDB
用sp向分區表和普通表各插入了90w條隨機數據.
用mysqlslap進行下測試

不用分區表
Sql代碼

select SQL_NO_CACHE * from 20130117date

where create_time BETWEEN '2013-01-01' and '2013-01-02';

select SQL_NO_CACHE * from 20130117date

where create_time BETWEEN '2012-12-25' and '2013-01-05';

select SQL_NO_CACHE * from 20130117date where create_time BETWEEN '2013-01-01' and '2013-01-02'; select SQL_NO_CACHE * from 20130117date where create_time BETWEEN '2012-12-25' and '2013-01-05';
引用
Benchmark
Average number of seconds to run all queries: 0.881 seconds
Minimum number of seconds to run all queries: 0.062 seconds
Maximum number of seconds to run all queries: 3.844 seconds
Number of clients running queries: 1
Average number of queries per client: 2
Benchmark
Average number of seconds to run all queries: 0.703 seconds
Minimum number of seconds to run all queries: 0.062 seconds
Maximum number of seconds to run all queries: 1.922 seconds
Number of clients running queries: 1
Average number of queries per client: 2
Benchmark
Average number of seconds to run all queries: 1.250 seconds
Minimum number of seconds to run all queries: 0.109 seconds
Maximum number of seconds to run all queries: 4.032 seconds
Number of clients running queries: 1
Average number of queries per client: 2

用分區表
Sql代碼

select SQL_NO_CACHE * from 20130117date_par

where create_time BETWEEN '2013-01-01' and '2013-01-02';

select SQL_NO_CACHE * from 20130117date_par

where create_time BETWEEN '2012-12-25' and '2013-01-05';

select SQL_NO_CACHE * from 20130117date_par where create_time BETWEEN '2013-01-01' and '2013-01-02'; select SQL_NO_CACHE * from 20130117date_par where create_time BETWEEN '2012-12-25' and '2013-01-05';
引用
Benchmark
Average number of seconds to run all queries: 0.068 seconds
Minimum number of seconds to run all queries: 0.047 seconds
Maximum number of seconds to run all queries: 0.110 seconds
Number of clients running queries: 1
Average number of queries per client: 2
Benchmark
Average number of seconds to run all queries: 0.250 seconds
Minimum number of seconds to run all queries: 0.031 seconds
Maximum number of seconds to run all queries: 1.078 seconds
Number of clients running queries: 1
Average number of queries per client: 2
Benchmark
Average number of seconds to run all queries: 0.046 seconds
Minimum number of seconds to run all queries: 0.046 seconds
Maximum number of seconds to run all queries: 0.047 seconds
Number of clients running queries: 1
Average number of queries per client: 2
看來性能還是有一定的提升的.

執行
Sql代碼

explain PARTITIONS select * from 20130117date_par

where create_time BETWEEN '2012-01-01' and '2012-01-02';

explain PARTITIONS select * from 20130117date_par where create_time BETWEEN '2012-01-01' and '2012-01-02';
可以看出這個query只掃描了p2012這個分區.
而且分區表的好處在于維護比較方便.比如2009年的數據不需要了,分區表的方法為
Sql代碼

alter table 20130117date_par drop PARTITION p2009

alter table 20130117date_par drop PARTITION p2009
不到1s就行了
普通表為
Sql代碼

delete from 20130117date

where create_time BETWEEN '2009-01-01' and '2010-01-01'

delete from 20130117date where create_time BETWEEN '2009-01-01' and '2010-01-01'
用了10.25s左右?

轉載于:https://blog.51cto.com/daheyuan/1131719

總結

以上是生活随笔為你收集整理的mysql Partition(分区)初探的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。