?? ? ? ?開發(fā)項目過程中總是提到優(yōu)化的概念，本篇文章是對mysql數(shù)據(jù)優(yōu)化實踐的一次探索旅程，簡要介紹了分區(qū)原因，方法，分區(qū)表管理方法和一次簡單的實踐。
【為什么分區(qū)】
? ? ? ?在大數(shù)據(jù)操作時，將數(shù)據(jù)表分而治之，將一張數(shù)據(jù)量很大的表分為一個更小的操作單元，每一個操作單元都會有一個單獨的名稱。同時，對于程序開發(fā)人員來說，分區(qū)和沒有分區(qū)是一樣的，通俗來說，mysql分區(qū)對于程序應用是透明的，只是數(shù)據(jù)庫對數(shù)據(jù)的一次重新整理操作。
? ? ? 分區(qū)作用：? ? ?
? ? ? （1）提升性能。
? ? ? ?分區(qū)的最終目的是提升性能，分區(qū)完成后，mysql針對每個分區(qū)生成特定數(shù)據(jù)文件和索引文件，檢索時通過檢索特定的部分數(shù)據(jù)，因此更好的執(zhí)行和維護數(shù)據(jù)庫。這是因為分區(qū)后表被指派到不同的物理驅動器上，同時訪問多個分區(qū)時減少分區(qū)物理I/O爭用。
? ? ?（2）易于管理。
? ? ? 分區(qū)后，管理數(shù)據(jù)可以直接管理對應的分區(qū)。操作簡單，當數(shù)據(jù)達到百萬級別時，直接操作分區(qū)遠比操作數(shù)據(jù)表來的更加直接。
? ? ? （3）容錯? ? ? ?
? ? ? 分區(qū)完成后，一個分區(qū)被破壞后，不會影響其他數(shù)據(jù)。
【分區(qū)方法】
? ? ? ? ?mysql 的分區(qū)方法有：RANGE分區(qū)、LIST分區(qū)、HASH分區(qū)、KEY分區(qū)。
? ? ? ? RANGE分區(qū)：根據(jù)某個字段的值來進行分區(qū)管理，是在直接創(chuàng)建表時進行的分區(qū)。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分區(qū)：類似于RANG分區(qū)，不同的是，list分區(qū)是一個個散列值，RANG分區(qū)是根據(jù)某個字段范圍進行分區(qū)。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分區(qū)：確保數(shù)據(jù)在預先指定書目的分區(qū)中平均分布，分區(qū)時指定分區(qū)根據(jù)的列值和分區(qū)數(shù)量。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分區(qū)：類似于HASH分區(qū)，區(qū)別于KEY分區(qū)只支持計算一列或多列，MySQL服務器提供其自身哈希函數(shù)，必須有一列或者多列包涵整數(shù)值。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;
【分區(qū)的管理操作方法】? ? ?
刪除分區(qū):
alter?table?emp?drop?partition p1;
不可以刪除hash或者key分區(qū)。
一次性刪除多個分區(qū)，alter?table?emp?drop?partition p1,p2;
增加分區(qū):
alter?table?emp?add?partition (partition p3?values?less than (4000));
alter?table?empl?add?partition (partition p3?values?in?(40));
分解分區(qū):
Reorganizepartition關鍵字可以對表的部分分區(qū)或全部分區(qū)進行修改，并且不會丟失數(shù)據(jù)。分解前后分區(qū)的整體范圍應該一致。
alter?table?te
reorganize partition p1?into
(
partition p1?values?less than (100),
partition p3?values?less than (1000)
); —-不會丟失數(shù)據(jù)
?
合并分區(qū):
Merge分區(qū)：把2個分區(qū)合并為一個。
alter?table?te
reorganize partition p1,p3?into
(partition p1?values?less than (1000));
—-不會丟失數(shù)據(jù)
?
重新定義hash分區(qū)表:
Alter?table?emp partition?by?hash(salary)partitions?7;
—-不會丟失數(shù)據(jù)
重新定義range分區(qū)表:
Alter?table?emp partitionbyrange(salary)
(
partition p1?values?less than (2000),
partition p2?values?less than (4000)
); —-不會丟失數(shù)據(jù)
?
刪除表的所有分區(qū):
?
Alter?table?emp removepartitioning;–不會丟失數(shù)據(jù)
?
重建分區(qū)：
這和先刪除保存在分區(qū)中的所有記錄，然后重新插入它們，具有同樣的效果。它可用于整理分區(qū)碎片。
ALTER?TABLE?emp rebuild partitionp1,p2;
?
優(yōu)化分區(qū)：
如果從分區(qū)中刪除了大量的行，或者對一個帶有可變長度的行（也就是說，有VARCHAR，BLOB，或TEXT類型的列）作了許多修改，可以使用“ALTER TABLE … OPTIMIZE PARTITION”來收回沒有使用的空間，并整理分區(qū)數(shù)據(jù)文件的碎片。
ALTER?TABLE?emp?optimize?partition p1,p2;
?
分析分區(qū)：
讀取并保存分區(qū)的鍵分布。
ALTER?TABLE?emp?analyze?partition p1,p2;
?
修補分區(qū)：
修補被破壞的分區(qū)。
ALTER?TABLE?emp repairpartition p1,p2;
?
檢查分區(qū)：
可以使用幾乎與對非分區(qū)表使用CHECK TABLE 相同的方式檢查分區(qū)。
ALTER?TABLE?emp?CHECK?partition p1,p2;
這個命令可以告訴你表emp的分區(qū)p1,p2中的數(shù)據(jù)或索引是否已經(jīng)被破壞。如果發(fā)生了這種情況，使用“ALTER TABLE … REPAIR PARTITION”來修補該分區(qū)。
【分區(qū)實踐】
? ? ? 1. 創(chuàng)建分區(qū)表和不分區(qū)表：? ? ??
--?創(chuàng)建分區(qū)表 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)?);
--?創(chuàng)建沒有分區(qū)表 CREATE?TABLE?nopart_tab (c1?int?NULL,?c2?VARCHAR(30),?c3?date?not?null)
? ? ? 2. 創(chuàng)建大數(shù)據(jù)操作環(huán)境。為了測試結果的準確度提高，需要表中存在大數(shù)據(jù)，通過以下事務可在數(shù)據(jù)表中創(chuàng)建800萬條數(shù)據(jù)：
--?創(chuàng)建生成數(shù)據(jù)事物 CREATE?PROCEDURE?load_part_tab() ????begin ????declare?v?int?default?0; ????while?v?<p></p><p>? ? ? ? ?<span style="font-size: 18px;">執(zhí)行事務:call load_part_tab(); ，因為執(zhí)行此事務執(zhí)行的時間很長，我只在表中插入了283304條數(shù)據(jù)。</span><br></p><p><span style="font-size: 18px;">? ? ? 創(chuàng)建完成一張表后，可以將該表的數(shù)據(jù)復制到未分區(qū)表，這樣執(zhí)行速度會很快：</span></p><p><span style="font-size:18px"></span></p><pre class="brush:php;toolbar:false;">insert?into?test.nopart_tab?select?*?from?test.part_tab
? ? ?3. 查看分區(qū)表分區(qū)結構：
--?查詢分區(qū)情況 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';
? ? ? ?執(zhí)行結果：
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ? 3. 測試速度：
執(zhí)行分區(qū)表查詢語句：
select?count(*)?from?part_tab?where?c3?>?date?'1995-01-01'and?c3?<p><span style="font-size:18px">?執(zhí)行時間：</span></p><p><span style="font-size:18px"><img src="https://img.php.cn/upload/article/000/000/194/111264d52f3ce2a20f93d503877710b1-1.gif" alt=""><br></span></p><p><span style="max-width:90%">?執(zhí)行未分區(qū)表查詢語句：</span></p><pre class="brush:php;toolbar:false;">select?count(*)?from?nopart_tab?where?c3?>?date?'1995-01-01'and?c3?<p><span style="font-size: 18px;">執(zhí)行時間：</span><br></p><p><span style="font-size:18px">? ? ?<img src="https://img.php.cn/upload/article/000/000/194/111264d52f3ce2a20f93d503877710b1-2.gif" alt=""></span></p><p><span style="max-width:90%">? ? ? ?從時間對比可以看出，同樣的查詢語句，分區(qū)表執(zhí)行速度在20ms左右，未分區(qū)表在175ms左右，執(zhí)行速度相差8倍左右，因此得出結論：分區(qū)表的執(zhí)行速度要比未分區(qū)表執(zhí)行速度快。</span></p><h2> <span style="font-size:18px">【分區(qū)局限性】</span>? ? ? ?</h2><p><span style="font-size:18px"></span></p><p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; font-family:Helvetica,Tahoma,Arial,sans-serif; line-height:25.2px">? ? <span style="font-size:18px">?1. MySQL分區(qū)處理NULL值的方式</span></p><p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; font-family:Helvetica,Tahoma,Arial,sans-serif; line-height:25.2px"><span style="font-size:18px">? ? ? ? ?如果分區(qū)鍵所在列沒有notnull約束。</span></p><p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; font-family:Helvetica,Tahoma,Arial,sans-serif; line-height:25.2px"><span style="font-size:18px">? ? ? ? ?如果是range分區(qū)表，那么null行將被保存在范圍最小的分區(qū)。</span></p><p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; font-family:Helvetica,Tahoma,Arial,sans-serif; line-height:25.2px"><span style="font-size:18px">? ? ? ? ?如果是list分區(qū)表，那么null行將被保存到list為0的分區(qū)。</span></p><p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; font-family:Helvetica,Tahoma,Arial,sans-serif; line-height:25.2px"><span style="font-size:18px">? ? ? ? ?在按HASH和KEY分區(qū)的情況下，任何產(chǎn)生NULL值的表達式mysql都視同它的返回值為0。</span></p><p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; font-family:Helvetica,Tahoma,Arial,sans-serif; line-height:25.2px"><span style="font-size:18px">? ? ? ? ?為了避免這種情況的產(chǎn)生，建議分區(qū)鍵設置成NOT NULL。</span></p><p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; font-family:Helvetica,Tahoma,Arial,sans-serif; line-height:25.2px"><span style="font-size:18px">?</span></p><p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; font-family:Helvetica,Tahoma,Arial,sans-serif; line-height:25.2px"><span style="font-size:18px">? ? ?2. 分區(qū)鍵必須是INT類型，或者通過表達式返回INT類型，可以為NULL。唯一的例外是當分</span></p><p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; font-family:Helvetica,Tahoma,Arial,sans-serif; line-height:25.2px"><span style="font-size:18px">? ? ? ? ?區(qū)類型為KEY分區(qū)的時候，可以使用其他類型的列作為分區(qū)鍵（ BLOB or TEXT 列除外）。</span></p><p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; font-family:Helvetica,Tahoma,Arial,sans-serif; line-height:25.2px"><span style="font-size:18px">?</span></p><p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; font-family:Helvetica,Tahoma,Arial,sans-serif; line-height:25.2px"><span style="font-size:18px">? ? ?3. 對分區(qū)表的分區(qū)鍵創(chuàng)建索引，那么這個索引也將被分區(qū),分區(qū)鍵沒有全局索引一說。</span></p><p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; font-family:Helvetica,Tahoma,Arial,sans-serif; line-height:25.2px"><span style="font-size:18px">? ? ?4. 只有RANG和LIST分區(qū)能進行子分區(qū)，HASH和KEY分區(qū)不能進行子分區(qū)。</span></p><p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; font-family:Helvetica,Tahoma,Arial,sans-serif; line-height:25.2px"><span style="font-size:18px">? ? ?5. 臨時表不能被分區(qū)。</span></p><p>?以上就是Mysql優(yōu)化實驗（一）-- 分區(qū)的內容，更多相關內容請關注PHP中文網(wǎng)（www.php.cn）！<br></p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 25.2px;"><br></p>
