mysql的分區(qū)技術詳細介紹
一、概述
當 MySQL的總記錄數(shù)超過了100萬后，會出現(xiàn)性能的大幅度下降嗎？答案是肯定的，但是，性能下降>的比率不一而同，要看系統(tǒng)的架構、應用程序、還有>包括索引、服務器硬件等多種因素而定。當有網(wǎng)友問我這個問題的時候，我最常見的回答>就是：分表，可以根據(jù)id區(qū)間或者時間先后順序等多種規(guī)則來分表。分表很容易，然而由此所帶來的應用程序甚至是架構方面的改動工作卻不>容小覷，還包括將來的擴展性等。
在以前，一種解決方案就是使用 MERGE
類型，這是一個非常方便的做飯。架構和程序基本上不用做改動，不過，它的缺點是顯見的：
1.只能在相同結構的 MyISAM 表上使用
2.無法享受到 MyISAM 的全部功能，例如無法在 MERGE 類型上執(zhí)行 FULLTEXT 搜索
3.它需要使用更多的文件描述符
4.讀取索引更慢
這個時候，MySQL 5.1 中新增的分區(qū)(Partition)功能的優(yōu)勢也就很明顯了：
1.與單個磁盤或文件系統(tǒng)分區(qū)相比，可以存儲更多的數(shù)據(jù)
2.很容易就能刪除不用或者過時的數(shù)據(jù)
3.一些查詢可以得到極大的優(yōu)化
4.涉及到 SUM()/COUNT() 等聚合函數(shù)時，可以并行進行
5.IO吞吐量更大
分區(qū)允許可以設置為任意大小的規(guī)則，跨文件系統(tǒng)分配單個表的多個部分。實際上，表的不同部分在不同的位置被存儲為單獨的表。
分區(qū)應該注意的事項：
1、 做分區(qū)時，要么不定義主鍵，要么把分區(qū)字段加入到主鍵中。
2、 分區(qū)字段不能為NULL，要不然怎么確定分區(qū)范圍呢，所以盡量NOT NULL
二、分區(qū)的類型
1.RANGE 分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。
2.LIST 分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進行選擇。
2.HASH分區(qū)：基于用戶定義的表達式的返回值來進行選擇的分區(qū)，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數(shù)可以包>含MySQL中有效的、產(chǎn)生非負整數(shù)值的任何表達式。
3.KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL服務器提供其自身的哈希函數(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)
);
對于innodb和myisam引擎，一條語句插入多條記錄的時候，如果中間有值不能插入，innodb會全部回滾，myisam在錯誤值之前的數(shù)據(jù)可以插入到表中。對于innodb和myisam引擎，一條語句插入多條記錄的時候，如果中間有值不能插入，innodb會全部回滾，myisam在錯誤值之前的數(shù)據(jù)可以插入到表中。
3.hash分區(qū)
hash分區(qū)的目的是將數(shù)據(jù)均勻的分布到預先定義的各個分區(qū)中，保證各分區(qū)的數(shù)據(jù)量大致一致。
create table t_hash(
a int(11),
b datetime
)partition by hash (YEAR(b)
partitions 4;
hash的分區(qū)函數(shù)頁需要返回一個整數(shù)值。partitions子句中的值是一個非負整數(shù)，不加的partitions子句的話，默認為分區(qū)數(shù)為1。
4.key分區(qū)
key分區(qū)和hash分區(qū)相似，不同在于hash分區(qū)是用戶自定義函數(shù)進行分區(qū)，key分區(qū)使用mysql數(shù)據(jù)庫提供的函數(shù)進行分區(qū)，NDB cluster使用MD5函數(shù)來分區(qū)，對于其他存儲引擎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ū)的進化，COLUMNS分區(qū)可以直接使用非整形數(shù)據(jù)進行分區(qū)。COLUMNS分區(qū)支持以下數(shù)據(jù)類型：
所有整形，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL則不支持。
日期類型，如DATE和DATETIME。其余日期類型不支持。
字符串類型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT類型不支持。
COLUMNS可以使用多個列進行分區(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ū)
--當刪除了一個分區(qū)，也同時刪除了該分區(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個分區(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
相關文章
MYSQL 如此方便和穩(wěn)定，以至于我們在開發(fā) WEB 程序的時候很少想到它。即使想到優(yōu)化也是程序級別的，比如，不要寫過于消耗資源的 SQL 語句。但是除此之外，在整個系統(tǒng)上仍然有很多可以優(yōu)化的地方。2016-01-01
MySQL學習筆記之數(shù)據(jù)的增、刪、改實現(xiàn)方法
這篇文章主要介紹了MySQL學習筆記之數(shù)據(jù)的增、刪、改實現(xiàn)方法,簡單介紹了mysql實現(xiàn)數(shù)據(jù)的增、刪、改相關語法與使用技巧,需要的朋友可以參考下2016-09-09
深入mysql "ON DUPLICATE KEY UPDATE" 語法的分析
本篇文章是對mysql "ON DUPLICATE KEY UPDATE"語法進行了詳細的分析介紹，需要的朋友參考下2013-06-06
MYSQL數(shù)據(jù)庫GTID實現(xiàn)主從復制實現(xiàn)(超級方便)
這篇文章主要介紹了MYSQL數(shù)據(jù)庫GTID實現(xiàn)主從復制實現(xiàn)(超級方便)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-11-11
MySQL數(shù)據(jù)庫備份恢復實現(xiàn)代碼
這篇文章主要介紹了MySQL數(shù)據(jù)庫備份恢復實現(xiàn)代碼,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2020-06-06
MySQL數(shù)據(jù)庫函數(shù)之JSON_EXTRACT示例代碼
這篇文章主要介紹了MySQL數(shù)據(jù)庫函數(shù)之JSON_EXTRACT的相關資料,JSON_EXTRACT()函數(shù)用于從JSON文檔中提取值,支持對象屬性、數(shù)組元素和嵌套結構的訪問,示例展示了如何提取簡單值、嵌套值和數(shù)組中的值,需要的朋友可以參考下2025-02-02
這篇文章主要介紹了MySQL中order by在子查詢中失效的問題解決,文中補充介紹了Mysql 5.7版本導致的子查詢order by排序無效問題的探究，需要的朋友可以參考下2023-07-07
這篇文章主要介紹了mysql中insert與select的嵌套使用方法,代碼功能非常實用,需要的朋友可以參考下2014-07-07
這篇文章主要為大家詳細介紹了MySQL中數(shù)據(jù)類型和字段類型的使用，文中的示例代碼講解詳細，對我們學習MySQL有一定幫助，需要的可以參考一下2022-09-09
這篇文章主要介紹了淺談MySQL與redis緩存的同步方案，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-03-03