mysql如何進(jìn)行分區(qū)_mysql分區(qū)有哪些方法

　　MySQL 可應(yīng)用于多種語言，包括 PERL, C, C++, JAVA 和 PHP。 在這些語言中，MySQL 在 PHP 的 web 開發(fā)中是應(yīng)用最廣泛。大家知道m(xù)ysql如何分區(qū)的嗎?下面由學(xué)習(xí)啦小編為大家整理的mysql分區(qū)的方法，希望大家喜歡!

　　mysql分區(qū)的方法

　　一、概述

　　當(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)是顯見的：

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

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

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

　　4.讀取索引更慢

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

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

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

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

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

　　5.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ū)的類型

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

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

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

　　3.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)

　　代碼如下:

　　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ū)。

　　新增分區(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

　　刪除分區(qū)

　　代碼如下:

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

　　mysql> ALTER TABLE sale_data DROP PARTITION p201010;

　　Query OK, 0 rows affected (0.22 sec)

　　Records: 0 Duplicates: 0 Warnings: 0

　　分區(qū)的合并

　　下面的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

　　導(dǎo)入導(dǎo)出MySQL數(shù)據(jù)庫的方法

　　1. 概述

　　MySQL數(shù)據(jù)庫的導(dǎo)入，有兩種方法：

　　1) 先導(dǎo)出數(shù)據(jù)庫SQL腳本，再導(dǎo)入;

　　2) 直接拷貝數(shù)據(jù)庫目錄和文件。

　　在不同操作系統(tǒng)或MySQL版本情況下，直接拷貝文件的方法可能會(huì)有不兼容的情況發(fā)生。

　　所以一般推薦用SQL腳本形式導(dǎo)入。下面分別介紹兩種方法。

　　2. 方法一 SQL腳本形式

　　操作步驟如下：

　　2.1. 導(dǎo)出SQL腳本

　　在原數(shù)據(jù)庫服務(wù)器上，可以用phpMyAdmin工具，或者mysqldump(mysqldump命令位于mysql/bin/目錄中)命令行，導(dǎo)出SQL腳本。

　　2.1.1 用phpMyAdmin工具

　　導(dǎo)出選項(xiàng)中，選擇導(dǎo)出“結(jié)構(gòu)”和“數(shù)據(jù)”，不要添加“Drop DATABASE”和“Drop TABLE”選項(xiàng)。

　　選中“另存為文件”選項(xiàng)，如果數(shù)據(jù)比較多，可以選中“gzipped”選項(xiàng)。

　　將導(dǎo)出的SQL文件保存下來。

　　2.1.2 用mysqldump命令行

　　命令格式

　　mysqldump -u用戶名 -p 數(shù)據(jù)庫名 > 數(shù)據(jù)庫名.sql

　　范例：

　　mysqldump -uroot -p abc > abc.sql

　　(導(dǎo)出數(shù)據(jù)庫abc到abc.sql文件)

　　提示輸入密碼時(shí)，輸入該數(shù)據(jù)庫用戶名的密碼。

　　2.2. 創(chuàng)建空的數(shù)據(jù)庫

　　通過主控界面/控制面板，創(chuàng)建一個(gè)數(shù)據(jù)庫。假設(shè)數(shù)據(jù)庫名為abc，數(shù)據(jù)庫全權(quán)用戶為abc_f。

　　2.3. 將SQL腳本導(dǎo)入執(zhí)行

　　同樣是兩種方法，一種用phpMyAdmin(mysql數(shù)據(jù)庫管理)工具，或者mysql命令行。

　　2.3.1 用phpMyAdmin工具

　　從控制面板，選擇創(chuàng)建的空數(shù)據(jù)庫，點(diǎn)“管理”，進(jìn)入管理工具頁面。

　　在"SQL"菜單中，瀏覽選擇剛才導(dǎo)出的SQL文件，點(diǎn)擊“執(zhí)行”以上載并執(zhí)行。

　　注意：phpMyAdmin對(duì)上載的文件大小有限制，php本身對(duì)上載文件大小也有限制，如果原始sql文件

　　比較大，可以先用gzip對(duì)它進(jìn)行壓縮，對(duì)于sql文件這樣的文本文件，可獲得1:5或更高的壓縮率。

　　gzip使用方法：

　　# gzip xxxxx.sql

　　得到

　　xxxxx.sql.gz文件。

　　2.3.2 用mysql命令行

　　命令格式

　　mysql -u用戶名 -p 數(shù)據(jù)庫名 < 數(shù)據(jù)庫名.sql

　　范例：

　　mysql -uabc_f -p abc < abc.sql

　　(導(dǎo)入數(shù)據(jù)庫abc從abc.sql文件)

　　提示輸入密碼時(shí)，輸入該數(shù)據(jù)庫用戶名的密碼。

　　方法2進(jìn)入mysql，建立數(shù)據(jù)庫，選擇數(shù)據(jù)庫后，打入下面代碼，d:112121.sql為數(shù)據(jù)庫目錄。

　　mysql>source d:112121.sql

　　3 方法二 直接拷貝

　　如果數(shù)據(jù)庫比較大，可以考慮用直接拷貝的方法，但不同版本和操作系統(tǒng)之間可能不兼容，要慎用。

　　3.1 準(zhǔn)備原始文件

　　用tar打包為一個(gè)文件

　　3.2 創(chuàng)建空數(shù)據(jù)庫

　　3.3 解壓

　　在臨時(shí)目錄中解壓，如：

　　cd /tmp

　　tar zxf mydb.tar.gz

　　3.4 拷貝

　　將解壓后的數(shù)據(jù)庫文件拷貝到相關(guān)目錄

　　cd mydb/

　　cp * /var/lib/mysql/mydb/

　　對(duì)于FreeBSD:

　　cp * /var/db/mysql/mydb/

　　3.5 權(quán)限設(shè)置

　　將拷貝過去的文件的屬主改為mysql:mysql，權(quán)限改為660

　　chown mysql:mysql /var/lib/mysql/mydb/*

　　chmod 660 /var/lib/mysql/mydb/*

　　MySQL怎么備份還原

　　一、Win32系統(tǒng)下MySQL的備份還原方法

　　備份：在“運(yùn)行”中輸入“cmd ”，利用“cd /Program Files/MySQL/MySQL Server 5.0/bin”進(jìn)入bin文件夾，輸入“mysqldump -u 用戶名 -p databasename >exportfilename”導(dǎo)出數(shù)據(jù)庫到文件，如mysqldump -u root -p voice>voice.sql，然后輸入密碼即可開始導(dǎo)出MYSQL數(shù)據(jù)，實(shí)現(xiàn)備份操作。

　　還原：進(jìn)入MySQL Command Line Client，輸入密碼，進(jìn)入到“mysql>”，輸入命令"show databases;"，回車，看看有些什么數(shù)據(jù)庫;建立你要還原的數(shù)據(jù)庫，輸入"create database voice;"，回車;切換到剛建立的數(shù)據(jù)庫，輸入"use voice;"，回車;導(dǎo)入數(shù)據(jù)，輸入"source voice.sql;"，回車，開始導(dǎo)入，再次出現(xiàn)"mysql>"并且沒有提示錯(cuò)誤即還原成功。

　　二、Linux下MySQL的備份與還原方法：

　　備份：[root@localhost ~]# cd /var/lib/mysql (進(jìn)入到MySQL庫目錄，根據(jù)自己的MySQL的安裝情況調(diào)整目錄)

　　[root@localhost mysql]# mysqldump -u root -p voice>voice.sql，輸入密碼即可。

　　還原：有兩種方法可選：

　　第一種方法：[root@localhost ~]# mysql -u root -p 回車，輸入密碼，進(jìn)入MySQL的控制臺(tái)"mysql>"，同1.2還原。

　　第二種方法：

　　[root@localhost ~]# cd /var/lib/mysql (進(jìn)入到MySQL庫目錄，根據(jù)自己的MySQL的安裝情況調(diào)整目錄)

　　[root@localhost mysql]# mysql -u root -p voice