mysql資料庫中的資料是以檔案的形勢存在磁盤上的，預設放在/mysql/data下面（可以通過my.cnf中的datadir來檢視），
一張表主要對應着三個檔案，一個是frm存放表結構的，一個是myd存放表資料的，一個是myi存表索引的。
[root@laoyang test]# ls -al
總用量 1811444
drwx------ 2 mysql mysql 4096 10月 17 15:12 .
drwxr-xr-x 4 mysql mysql 4096 10月 17 14:37 ..
-rw-rw---- 1 mysql mysql 8962 10月 10 17:45 bz_info.frm
-rw-rw---- 1 mysql mysql 347727032 10月 17 15:16 bz_info.MYD
-rw-rw---- 1 mysql mysql 56341504 10月 17 15:16 bz_info.MYI
-rw-rw---- 1 mysql mysql 8962 10月 10 17:44 dz_info.frm
-rw-rw---- 1 mysql mysql 418645764 10月 17 15:15 dz_info.MYD
-rw-rw---- 1 mysql mysql 81381376 10月 17 15:15 dz_info.MYI
1
2
3
4
5
6
7
8
9
10
2）資料庫分區處理
如果一張表的資料量太大的話，那麼myd,myi就會變的很大，查找資料就會變的很慢，這個時候我們可以利用mysql的分區功能，在實體上将這一張表對應的三個檔案，分割成許多個小塊，這樣呢，我們查找一條資料時，就不用全部查找了，隻要知道這條資料在哪一塊，然後在那一塊找就行了。如果表的資料太大，可能一個磁盤放不下，這個時候，我們可以把資料配置設定到不同的磁盤裡面去。
表分區是Mysql被Oracle收購後推出的一個新特性。
一、表分區通俗解釋
通俗地講表分區是将一大表，根據條件分割成若幹個小表。mysql5.1開始支援資料表分區了。
如：某使用者表的記錄超過了600萬條，那麼就可以根據入庫日期将表分區，也可以根據所在地将表分區。當然也可根據其他的條件分區。
二、為什麼要對表進行分區？
為了改善大型表以及具有各種通路模式的表的可伸縮性，可管理性和提高資料庫效率。
2.1 表分區要解決的問題：
當表非常大，或者表中有大量的曆史記錄，而“熱資料”卻位于表的末尾。如日志系統、新聞。。此時就可以考慮分區表。【注：此處也可以使用分表，但是會增加業務的複雜性。】
2.2 表分區有如下優點：
1）與單個磁盤或檔案系統分區相比，可以存儲更多的資料。
2）對于那些已經失去儲存意義的資料，通常可以通過删除與那些資料有關的分區，很容易地删除那些資料。
相反地，在某些情況下，添加新資料的過程又可以通過為那些新資料專門增加一個新的分區，來很友善地實作。
同樣的，你可以很快的通過删除分區來移除舊資料。你還可以優化、檢查、修複個别分區。
3）一些查詢可以得到極大的優化。 可以把一些歸類的資料放在一個分區中，可以減少伺服器檢查資料的數量加快查詢。
這主要是借助于滿足一個給定WHERE語句的資料可以隻儲存在一個或多個分區内，這樣在查找時就不用查找其他剩餘的分區。
PS：因為分區可以在建立了分區表後進行修改，是以在第一次配置分區方案時還不曾這麼做時，可以重新組織資料，來提高那些常用查詢的效率。
4）涉及到例如SUM()和COUNT()這樣聚合函數的查詢，可以很容易地進行并行處理。
這種查詢的一個簡單例子如
“SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。
通過“并行”，這意味着該查詢可以在每個分區上同時進行，最終結果隻需通過總計所有分區得到的結果。
5）通過跨多個磁盤來分散資料查詢，來獲得更大的查詢吞吐量。
三、mysql分區類型
根據所使用的不同分區規則可以分成幾大分區類型。
3.1 RANGE 分區：
基于屬于一個給定連續區間的列值，把多行配置設定給分區。
舉例：
create table foo_range (
id int not null auto_increment,
created DATETIME,
primary key (id, created)
) engine = innodb partition by range (TO_DAYS(created))(
PARTITION foo_1 VALUES LESS THAN (TO_DAYS('2016-10-18')),
PARTITION foo_2 VALUES LESS THAN (TO_DAYS('2017-01-01'))
);
//新增一個分區
ALTER TABLE foo_range ADD PARTITION(
PARTITION foo_3 VALUES LESS THAN (TO_DAYS('2017-10-18'))
//插入資料
insert into `foo_range` (`id`, `created`) values (1, '2016-10-17'),(2, '2016-10-20'),(3, '2016-1-25');
//查詢
explain partitions select * from foo_range where created = '2016-10-20';
//查詢結果：
mysql> explain partitions select * from foo_range where created = '2016-10-20';
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | foo_range | foo_2 | index | NULL | PRIMARY | 12 | NULL | 2 | Using where; Using index |
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
3.2 LIST 分區：
類似于按RANGE分區，差別在于LIST分區是基于列值比對一個離散值集合中的某個值來進行選擇。
create table foo_list
(empno varchar(20) not null ,
empname varchar(20),
deptno int,
birthdate date not null,
salary int
)
partition by list(deptno)
(
partition p1 values in (10),
partition p2 values in (20),
partition p3 values in (30)
以上顯示，以部門号為分區依據，每個部門一個分區。
3.3 HASH分區：
基于使用者定義的表達式的傳回值來進行選擇的分區，該表達式使用将要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL中有效的、産生非負整數值的任何表達式。
HASH分區主要用來確定資料在預先确定數目的分區中平均分布。在RANGE和LIST分區中，必須明确指定一個給定的列值或列值集合應該儲存在哪個分區中。
在HASH分區中，MySQL 自動完成這些工作，你所要做的隻是基于将要被哈希的列值指定一個列值或表達式，以及指定被分區的表将要被分割成的分區數量。
create table foo_hash
partition by hash(year(birthdate))
partitions 4;
以上建立了4個分區。
3.4 KEY分區：
類似于按HASH分區，差別在于KEY分區隻支援計算一列或多列，且MySQL伺服器提供其自身的哈希函數。必須有一列或多列包含整數值。
create table foo_key
partition by key(birthdate)
3.5 複合分區：
基于RANGE/LIST 類型的分區表中每個分區的再次分割。子分區可以是 HASH/KEY 等類型。
四、常見分區操作
修改已有表舉例：
ALTER TABLE bj_info
PARTITION BY RANGE(id) PARTITIONS 14(
PARTITION part_00yntai VALUES LESS THAN (610001),
PARTITION part_01shxia VALUES LESS THAN (1220001),
PARTITION part_02zhfu VALUES LESS THAN (1830001),
PARTITION part_03fuhan VALUES LESS THAN (2440001),
PARTITION part_04mping VALUES LESS THAN (3660001),
PARTITION part_06chngdao VALUES LESS THAN (4270001),
PARTITION part_07lonkou VALUES LESS THAN (4880001),
PARTITION part_08layang VALUES LESS THAN (5490001),
PARTITION part_09laihou VALUES LESS THAN (6100001),
PARTITION part_10peglai VALUES LESS THAN (6710001),
PARTITION part_11zhoyuan VALUES LESS THAN (7320001),
PARTITION part_12qixa VALUES LESS THAN (7930001),
PARTITION part_13haiyng VALUES LESS THAN (8540000),
PARTITION part_05laisan VALUES LESS THAN MAXVALUE
五、擷取分區表資訊的方法
5.1 show create table 表名
可以檢視建立分區表的create語句
mysql> show create table foo_list;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
| foo_list | CREATE TABLE `foo_list` (
`empno` varchar(20) NOT NULL,
`empname` varchar(20) DEFAULT NULL,
`deptno` int(11) DEFAULT NULL,
`birthdate` date NOT NULL,
`salary` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (deptno)
(PARTITION p1 VALUES IN (10) ENGINE = MyISAM,
PARTITION p2 VALUES IN (20) ENGINE = MyISAM,
PARTITION p3 VALUES IN (30) ENGINE = MyISAM) */ |
1 row in set (0.01 sec)
5. 2 show table status
可以檢視表是不是分區表
SHOW TABLE STATUS LIKE ‘foo_range’;
結果如紅色部分所示：
5.3 檢視information_schema.partitions表
如下指令可以檢視表具有哪幾個分區、分區的方法、分區中資料的記錄數等資訊
mysql> select
-> partition_name part,
-> partition_expression expr,
-> partition_description descr,
-> table_rows
-> from information_schema.partitions where
-> table_schema = schema()
-> and table_name='foo_range';
+-------+------------------+--------+------------+
| part | expr | descr | table_rows |
| foo_1 | TO_DAYS(created) | 736620 | 2 |
| foo_2 | TO_DAYS(created) | 736695 | 1 |
| foo_3 | TO_DAYS(created) | 736985 | 0 |
3 rows in set (0.00 sec)
5.4 explain partitions select語句
通過此語句來顯示掃描哪些分區，及他們是如何使用的.
舉例如下：
mysql> explain partitions select * from foo_range;
+----+-------------+-----------+-------------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | foo_range | foo_1,foo_2,foo_3 | index | NULL | PRIMARY | 12 | NULL | 4 | Using index |
1 row in set (0.00 sec)
六、性能對比（分區表和非分區表）
步驟一：建立兩張表: part_tab(分區表),no_part_tab(普通表)
CREATE TABLE part_tab
(c1 int default NULL, c2 varchar(30) default NULL, c3 date not null)
PARTITION BY RANGE(year(c3))
(PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (1996) ,
PARTITION p2 VALUES LESS THAN (1997) ,
PARTITION p3 VALUES LESS THAN (1998) ,
PARTITION p4 VALUES LESS THAN (1999) ,
PARTITION p5 VALUES LESS THAN (2000) ,
PARTITION p6 VALUES LESS THAN (2001) ,
PARTITION p7 VALUES LESS THAN (2002) ,
PARTITION p8 VALUES LESS THAN (2003) ,
PARTITION p9 VALUES LESS THAN (2004) ,
PARTITION p10 VALUES LESS THAN (2010),
PARTITION p11 VALUES LESS THAN (MAXVALUE) );
CREATE TABLE no_part_tab(c1 int default NULL, c2 varchar(30) default NULL, c3 date not null);
步驟二：建立存儲過程。
CREATE PROCEDURE load_part_tab()
begin
declare v int default 0;
while v < 8000000
do
insert into part_tab
values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));
set v = v + 1;
end while;
end;
//調用存儲過程，插入資料
call load_part_tab();
//從 part_tab 導入資料到 no_part_tab
insert into no_part_tab select * from part_tab;
步驟三：執行查詢速度比對
select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
耗時：0.407s
select count(*) from no_part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';
耗時：3.716s：3.716/0.407=9.13倍。
掃描次數對比：
mysql> explain select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';
+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | part_tab | ALL | NULL | NULL | NULL | NULL | 798458 | Using where |
mysql> explain select count(*) from no_part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | no_part_tab | ALL | NULL | NULL | NULL | NULL | 8000000 | Using where |
如上：普通表掃描了 8000000次， 分區表掃描了798458次。
分區表掃描比例是普通表的：798458/ 8000000 = 9.98%。
七、分區适用場景
7.1常見使用場景
1）當資料量很大(過T)時，肯定不能把資料再如到記憶體中，這樣查詢一個或一定範圍的item是很耗時。另外一般這情況下，曆史資料或不常通路的資料占很大部分，最新或熱點資料占的比例不是很大。這時可以根據有些條件進行表分區。
2）分區表的更易管理，比如删除過去某一時間的曆史資料，直接執行truncate，或者狠點drop整個分區，這比detele删除效率更高
3）當資料量很大，或者将來很大的，但單塊磁盤的容量不夠，或者想提升IO效率的時候，可以把沒分區中的子分區挂載到不同的磁盤上。
4）使用分區表可避免某些特殊的瓶頸，例如Innodb的單個索引的互斥通路..
5）單個分區表的備份很恢複會更有效率，在某些場景下
總結：可伸縮性，可管理性，提高資料庫查詢效率。
7.2 業務場景舉例
項目中需要動态建立、删除分區。如新聞表，按照時間次元中的月份對其分區，為了防止新聞表過大，隻保留最近6個月的分區，同時預建後面3個月的分區，這個删除、預建分區的過程就是分區表的動态管理。
參考：
http://blog.51yip.com/mysql/1029.html http://blog.51yip.com/mysql/949.html http://blog.51yip.com/mysql/1013.html http://blog.csdn.net/feihong247/article/details/7885199 http://www.wiquan.com/article/669分區坑：
http://www.simlinux.com/archives/133.html建立路徑：
http://dev.mysql.com/doc/refman/5.7/en/create-table.html5.6版本才支援：
http://www.linuxidc.com/Linux/2014-01/95725.htm https://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html