一、概述

當(dāng) MySQL的總記錄數(shù)超過了100萬后，會(huì)出現(xiàn)性能的大幅度下降嗎？答案是肯定的，但是，性能下降>的比率不一而同，要看系統(tǒng)的架構(gòu)、應(yīng)用程序、還有>包括索引、服務(wù)器硬件等多種因素而定。當(dāng)有網(wǎng)友問我這個(gè)問題的時(shí)候，我最常見的回答>就是：分表，可以根據(jù)id區(qū)間或者時(shí)間先后順序等多種規(guī)則來分表。分表很容易，然而由此所帶來的應(yīng)用程序甚至是架構(gòu)方面的改動(dòng)工作卻不>容小覷，還包括將來的擴(kuò)展性等。

在以前，一種解決方案就是使用 MERGE

類型，這是一個(gè)非常方便的做飯。架構(gòu)和程序基本上不用做改動(dòng)，不過，它的缺點(diǎn)是顯見的：

只能在相同結(jié)構(gòu)的 MyISAM 表上使用

無法享受到 MyISAM 的全部功能，例如無法在 MERGE 類型上執(zhí)行 FULLTEXT 搜索

它需要使用更多的文件描述符

讀取索引更慢

這個(gè)時(shí)候，MySQL 5.1 中新增的分區(qū)(Partition)功能的優(yōu)勢也就很明顯了：

與單個(gè)磁盤或文件系統(tǒng)分區(qū)相比，可以存儲(chǔ)更多的數(shù)據(jù)

很容易就能刪除不用或者過時(shí)的數(shù)據(jù)

一些查詢可以得到極大的優(yōu)化

涉及到 SUM()/COUNT() 等聚合函數(shù)時(shí)，可以并行進(jìn)行

IO吞吐量更大

分區(qū)允許可以設(shè)置為任意大小的規(guī)則，跨文件系統(tǒng)分配單個(gè)表的多個(gè)部分。實(shí)際上，表的不同部分在不同的位置被存儲(chǔ)為單獨(dú)的表。

分區(qū)應(yīng)該注意的事項(xiàng)：

1、?做分區(qū)時(shí)，要么不定義主鍵，要么把分區(qū)字段加入到主鍵中。

2、?分區(qū)字段不能為NULL，要不然怎么確定分區(qū)范圍呢，所以盡量NOT NULL

二、分區(qū)的類型

RANGE 分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。

LIST 分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來進(jìn)行選擇。

HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個(gè)函數(shù)可以包>含MySQL中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。

KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含>整數(shù)值。

可以通過使用SHOW VARIABLES命令來確定MySQL是否支持分區(qū)，例如：

mysql> SHOW VARIABLES LIKE '%partition%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| have_partition_engine | YES |

+-----------------------+-------+

1 row in set (0.00 sec)

1 。range分區(qū)

create?table?t_range(id?int(11),money?int(11)?unsigned?not?null,date?datetime)partition?by?range(year(date))(partition?p2007?values?less?than?(2008),partition?p2008?values?less?than?(2009),partition?p2009?values?less?than?(2010)partition?p2010?values?less?than?maxvalue)；

2.list分區(qū)

create?table?t_list(?　　a?int(11),?　　b?int(11)?　　)(partition?by?list?(b)?　　partition?p0?values?in?(1,3,5,7,9),?　　partition?p1?values?in?(2,4,6,8,0)?　　);

對(duì)于innodb和myisam引擎，一條語句插入多條記錄的時(shí)候，如果中間有值不能插入，innodb會(huì)全部回滾，myisam在錯(cuò)誤值之前的數(shù)據(jù)可以插入到表中。

對(duì)于innodb和myisam引擎，一條語句插入多條記錄的時(shí)候，如果中間有值不能插入，innodb會(huì)全部回滾，myisam在錯(cuò)誤值之前的數(shù)據(jù)可以插入到表中。

3.hash分區(qū)

hash分區(qū)的目的是將數(shù)據(jù)均勻的分布到預(yù)先定義的各個(gè)分區(qū)中，保證各分區(qū)的數(shù)據(jù)量大致一致。

create?table?t_hash(?　　a?int(11),?　　b?datetime?　　)partition?by?hash?(YEAR(b)?　　partitions?4;

hash的分區(qū)函數(shù)頁需要返回一個(gè)整數(shù)值。partitions子句中的值是一個(gè)非負(fù)整數(shù)，不加的partitions子句的話，默認(rèn)為分區(qū)數(shù)為1。

4.key分區(qū)

key分區(qū)和hash分區(qū)相似，不同在于hash分區(qū)是用戶自定義函數(shù)進(jìn)行分區(qū)，key分區(qū)使用mysql數(shù)據(jù)庫提供的函數(shù)進(jìn)行分區(qū)，NDB cluster使用MD5函數(shù)來分區(qū)，對(duì)于其他存儲(chǔ)引擎mysql使用內(nèi)部的hash函數(shù)，這些函數(shù)基于password()一樣的算法。

create?table?t_key(?　　a?int(11),?　　b?datetime)?　　partition?by?key?(b)?　　partitions?4;

5。columns分區(qū)

上面的RANGE、LIST、HASH、KEY四種分區(qū)中，分區(qū)的條件必須是整形，如果不是整形需要通過函數(shù)將其轉(zhuǎn)換為整形。

mysql-5.5開始支持COLUMNS分區(qū)，可視為RANGE和LIST分區(qū)的進(jìn)化，COLUMNS分區(qū)可以直接使用非整形數(shù)據(jù)進(jìn)行分區(qū)。COLUMNS分區(qū)支持以下數(shù)據(jù)類型：

所有整形，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL則不支持。

日期類型，如DATE和DATETIME。其余日期類型不支持。

字符串類型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT類型不支持。

COLUMNS可以使用多個(gè)列進(jìn)行分區(qū)。

mysql>?ALTER TABLE?sale_data

->???ADD PARTITION?(PARTITION p201010 VALUES LESS THAN (201011));

Query OK, 0 rows affected (0.36 sec)

Records: 0??Duplicates: 0??Warnings: 0

--當(dāng)刪除了一個(gè)分區(qū)，也同時(shí)刪除了該分區(qū)中所有的數(shù)據(jù)。

mysql>?ALTER TABLE?sale_data?DROP PARTITIONp201010;

Query OK, 0 rows affected (0.22 sec)

Records: 0??Duplicates: 0??Warnings: 0

下面的SQL，將p201001 - p201009合并為3個(gè)分區(qū)p2010Q1 - p2010Q3

mysql>?ALTER TABLE?sale_data

->???REORGANIZE PARTITION?p201001,p201002,p201003,

->????????????????????????p201004,p201005,p201006,

->????????????????????????p201007,p201008,p201009?INTO

-> (

->???PARTITION p2010Q1 VALUES LESS THAN (201004),

->???PARTITION p2010Q2 VALUES LESS THAN (201007),

->???PARTITION p2010Q3 VALUES LESS THAN (201010)

-> );

Query OK, 0 rows affected (1.14 sec)

Records: 0??Duplicates: 0??Warnings: 0

與50位技術(shù)專家面對(duì)面20年技術(shù)見證，附贈(zèng)技術(shù)全景圖

總結(jié)

以上是生活随笔為你收集整理的mysql分区跨机器_(转) mysql的分区技术 .的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。