開發專案過程中總是提到最佳化的概念，這篇文章是對Mysql資料最佳化實務的探索旅程，簡單介紹了分區原因，方法，分區表管理方法和一次簡單的實務。
【為什麼分區】
在大數據操作時，將資料表分而治之，將一張資料量很大的表分為一個更小的操作單元，每個操作單元都會有一個單獨的名稱。同時，對於程式開發人員來說，分區和沒有分區是一樣的，通俗來說，mysql分區對於程式應用是透明的，只是資料庫對資料的一次重新整理操作。
分區作用：
（1）提升效能。
分區的最終目的是提升性能，分區完成後，mysql針對每個分區生成特定數據文件和索引文件，檢索時通過檢索特定的部分數據，因此更好的執行和維護資料庫。這是因為分區後表被指派到不同的實體磁碟機上，同時存取多個分區時減少分區實體I/O爭用。
（2）容易管理。
分割區後，管理資料可以直接管理對應的分割區。操作簡單，當資料達到百萬級時，直接操作分區遠比操作資料表來的更直接。
（3）容錯
分割完成後，一個分割被破壞後，不會影響其他資料。
【分區方法】
mysql 的分區方式有：RANGE分區、LIST分區、HASH分區、KEY分區。
RANGE分區：根據某個欄位的值來進行分區管理，是直接建立表格時進行的分區。 eg：
create table emp
(empno varchar(20) not null ,
empname varchar(20),
deptno int,
birthdate date,
salary int
)
partition by range(salary)
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than maxvalue
);
登入後複製
LIST分區：類似於RANG分區，不同的是，list分區是一個個雜湊值，RANG分區是根據某個字段範圍進行分區。 eg：
create table emp
(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,15),
partition p2 values in (20,25),
partition p3 values in (30,35)
);
登入後複製
HASH分區：確保資料在預先指定書目的分區中平均分佈，分區時指定分區根據的列值和分區數量。 eg：
create table emp
(empno varchar(20) not null ,
empname varchar(20),
deptno int,
birthdate date not null,
salary int
)
partition by hash(year(birthdate))
partitions 4;
登入後複製
KEY分區：類似HASH分區，區別於KEY分區只支援計算一列或多列，MySQL伺服器提供其自身雜湊函數，必須有一列或多列包涵整數值。 eg:
create table emp
(empno varchar(20) not null ,
empname varchar(20),
deptno int,
birthdate date not null,
salary int
)
partition by key(birthdate)
partitions 4;
登入後複製
【分割區的管理作業方法】
刪除分區:
alter table emp drop partition p1;
不可以刪除hash或key分割區。
一次刪除多個分割區，alter table emp drop partition p1,p2;
增加分割區:
alter table emp add partition (partition p3 values less than (#4000));
alter table empl add partition ( partition p3 values in (40));
分解分割區:
#Reorganizepartition關鍵字可以對資料表的部分分割區或全部分割區進行修改，並且不會遺失數據。分解前後分區的整體範圍應該一致。
alter table te
reorganize partition p1 #into
(
#partition p1 values less than (100#) ,
partition p3 values less than (#1000)
#); ----不會遺失資料
#
合併分割區:
Merge分割區：把2個分割區合併為一個。
alter table te
#reorganize partition p1,p3 into
##(partition p1 values less than (#1000));
----不會遺失資料
#
重新定義hash分區表:
Alter table emp partition by hash(salary )partitions 7;
----#不會遺失資料
重新定義range分區表:
Alter table emp partitionbyrange(salary)
#(
partition p1 #values less than (2000),
partition p2 values less than (##partition p2 values
less than (
4000 )
)
; ----#不會遺失資料
刪除資料表的所有分割區:
#
##Alter
table### emp removepartitioning###;--######不會遺失資料############## ## #############重建分割區：###############這和先刪除儲存在分割區中的所有記錄，然後重新插入它們，具有同樣的效果。它可用於整理分區碎片。 ######
ALTER TABLE emp rebuild partitionp1,p2;
#
#最佳化分割區：
#如果從分割區中刪除了大量的行，或對一個有可變長度的行（也就是說，有VARCHAR，BLOB，或TEXT類型的列）作了許多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”來收回沒有使用的空間，並整理分區資料檔的碎片。
ALTER TABLE emp optimize partition p1,p2#;
#
分析分割區：
讀取並儲存分區的鍵分佈。
ALTER TABLE emp analyze partition p1,p2;
#
修補分割區：
#修補被破壞的分區。
ALTER TABLE emp repairpartition p1,p2;
檢查分割區：
#可以使用幾乎與對非分割資料表使用CHECK TABLE 相同的方式檢查分割區。
ALTER TABLE emp CHECK partition p1,p2;
#
这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。如果发生了这种情况，使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。
【分区实践】
1. 创建分区表和不分区表：
-- 创建分区表
CREATE TABLE part_tab
(c1 int NULL, c2 VARCHAR(30), 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 nopart_tab
(c1 int NULL, c2 VARCHAR(30), c3 date not null)
登入後複製
2. 创建大数据操作环境。为了测试结果的准确度提高，需要表中存在大数据，通过以下事务可在数据表中创建800万条数据：
-- 创建生成数据事物
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(); ，因为执行此事务执行的时间很长，我只在表中插入了283304条数据。
创建完成一张表后，可以将该表的数据复制到未分区表，这样执行速度会很快：
insert into test.nopart_tab select * from test.part_tab
登入後複製
3. 查看分区表分区结构：
-- 查询分区情况
select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name='part_tab';
登入後複製
执行结果：
3. 测试速度：
执行分区表查询语句：
select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';
登入後複製
执行时间：
执行未分区表查询语句：
select count(*) from nopart_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';
登入後複製
执行时间：
从时间对比可以看出，同样的查询语句，分区表执行速度在20ms左右，未分区表在175ms左右，执行速度相差8倍左右，因此得出结论：分区表的执行速度要比未分区表执行速度快。
【分区局限性】
1. MySQL分区处理NULL值的方式
如果分区键所在列没有notnull约束。
如果是range分区表，那么null行将被保存在范围最小的分区。
如果是list分区表，那么null行将被保存到list为0的分区。
在按HASH和KEY分区的情况下，任何产生NULL值的表达式mysql都视同它的返回值为0。
为了避免这种情况的产生，建议分区键设置成NOT NULL。
2. 分區鍵必須是INT類型，或透過表達式傳回INT類型，可以為NULL。唯一的例外是當分
區類型為KEY分區的時候，可以使用其他類型的欄位作為分區鍵（ BLOB or TEXT 欄位除外）。
3. 對分區表的分區鍵建立索引，那麼這個索引也會被分區,分區鍵沒有全域索引一說。
4. 只有RANG和LIST分區能進行子分區，HASH和KEY分區不能進行子分區。
5. 臨時表不能被分割。
以上就是Mysql最佳化實驗（一）-- 分區的內容，更多相關內容請關注PHP中文網（www.php.cn）！
#
