學(xué) 傳DBA成 ，授IT人立身分預(yù)先組織表的方MySQL將特定表行分配為行的子

特定于MySQL分區(qū)的分布是跨物理進(jìn)行每個分區(qū)為其自己的單mysql>mysql>SHOW***********************43.rowName:Status:Type:STORAGELibrary:NULLLicense:PROPRIETARY >mysqld--skip-加快和刪除行運行時速MySQL使用WHERESELECTSELECTtitleFROMWHEREcategory=ANDpublished>1990ANDpublished<SELECTtitleSELECTtitleFROMbookPARTITION(p0)WHEREcategory='medicine‘ANDpublished>DELETECREATETABLE<table_name>(<table_column_options>)PARTITIONBY<type>CREATETABLE<table_name>(<table_column_options>)PARTITIONBY<type>包含ID號小于10,000的訂單的分包含ID號介10,00019,999之間的訂單的分以此類推，直到p4p4(40,000<=id<p3(30,000<=id<p2(20,000<=id<表p1(10,000<=id<p0(id<RANGE分區(qū)->idINTAUTO_INCREMENTPRIMARY->store_idINT,sales_id->)ENGINE=->PARTITIONBYRANGE(id)->在此“訂單”示例中，按每個地區(qū)的ID為訂單指定分p4p4(store_id=p3(store_id=p2(store_id=表p1(store_id=p0(store_id=LIST分區(qū)->idINT->sales_idINT,order_dateDATE,note->INDEXidx(id))ENGINE=->PARTITIONp0VALUESIN(1,3,4,->PARTITIONp1VALUESIN(2,12,->PARTITIONp2VALUESIN(6,8,->PARTITIONp3VALUESIN(5,7,9,11,->PARTITIONp4VALUESIN(10,13,15,->LIST分區(qū)表僅接受匹配分區(qū)的行，適用于基于列表中指定的值與RANGELIST不同，HASH在此“訂單”示例中，指定一個整數(shù)來將表分區(qū)為四個相等的p3p3p2表p1p0HASH分區(qū)->idINTAUTO_INCREMENTPRIMARY->noteVARCHAR(500))ENGINE=KEYPARTITIONBYKEY類似于HASHMySQLp3p3p2表p1p0KEY分區(qū)->idINT->INDEXidx(id))ENGINE=RANGE和LIST子分區(qū)自身可以是HASH在此“訂單”數(shù)據(jù)庫示例中，根據(jù)orders_range表，可以進(jìn)一p4sp1p4sp1p4sp0p4(id<p3sp1p3sp0p3(id<p2sp1p2(id<p2sp0p1sp1p1(id<p1sp0p0sp1p0(id<p0sp0子分區(qū)->->)ENGINE=->SUBPARTITIONBY->SUBPARTITIONS2- - - - - - SHOWCREATE查詢INFORMATION_SCHEMA.PARTITIONS顯示給定SELECT獲取分區(qū)信息：SHOWCREATEmysql>SHOWCREATETABLE********************1.row********************Table:orders_hashCreateTable:CREATETABLE`orders_hash``id`int(11)NOTNULL`customer_surname`varchar(30)DEFAULT`store_id`int(11)DEFAULT _id`int(11)DEFAULT`order_date`dateDEFAULT`note`varchar(500)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULT/*!50100PARTITIONBYHASH(id)PARTITIONS4獲取分區(qū)信息：SHOWTABLEmysql>mysql>SHOWTABLESTATUSLIKE********************1.rowName:Engine:Create_options:partitionedmysql>mysql>SELECT->->FROM->WHERE->GROUP_BY+|+++|group_concat(partition_name)+|orders_range|||||||+||||獲取分區(qū)信息：EXPLAINEXPLAINEXPLAINPARTITIONSSELECT*FROM********************1.rowid:select_type:table:partitions:type:ALLpossible_keys:key:key_len:ref:rows:使用ALTERTABLE將區(qū)表更改為分區(qū)表將ALTERTABLEPARTITIONBYRANGE更改為ALTERALTERTABLEPARTITIONBYHASH(id)PARTITIONS將分區(qū)或子分區(qū)中的所有現(xiàn)有行移至區(qū)將區(qū)表中的所有現(xiàn)有行移至表分區(qū)或子分ALTERTABLEorders_rangeALTERTABLEorders_rangeEXCHANGEPARTITIONp0WITHTABLERANGE或LIST將ALTERTABLE與DROPPARTITIONALTERALTERTABLEorders_rangeDROPPARTITION現(xiàn)在分區(qū)模式已更++++|PARTITION_NAME|PARTITION_DESCRIPTION | | | | | | | | | | DROPPARTITION需要DROP才能使用DROPPARTITIONALTERALTERTABLEorders_rangeDROPPARTITIONp1,此操作不會刪除任何表數(shù)ALTERTABLEorders_rangeREMOVEALTERTABLEorders_rangeREMOVE++|+|++++|PARTITION_NAME|PARTITION_DESCRIPTION+|++|++|+對于大型事務(wù)表，DROPPARTITIONDELETEADDPARTITIONRANGELISTKEYHASH表上運行的ADDPARTITION，速度取決于已經(jīng)I/O分區(qū) 分區(qū)在與InnoDB表相同位置中的文件可以提供DATADIRECTORY選項來重定位分每個分區(qū)在以下數(shù) 中具有其自己的文如果禁用innodb_file_per_table，分區(qū)將在共享表空間不能僅對數(shù)據(jù)或僅對索引進(jìn)行分分區(qū)不可用于以下引MERGE、FEDERATED、鎖定操作在執(zhí)行每個語句過程中由表引擎來處理每個引擎處理鎖的方式均不相每個分區(qū)有一個引擎實只鎖定刪改分僅在執(zhí)行語句過程中保持該鎖每個表的最大分區(qū)數(shù)為不支持空間類不能對臨時表進(jìn)行分不能對日志表進(jìn)行分不支持外不支持FULLTEXT索無全局索引