mysql 分表 分割槽
為什麼要分表和分割槽?
日常開發中我們經常會遇到大表的情況,所謂的大表是指儲存了百萬級乃至千萬級條記錄的表。這樣的表過於龐大,導致資料庫在查詢和插入的時候耗時太長,效能低下,如果涉及聯合查詢的情況,效能會更加糟糕。分表和表分割槽的目的就是減少資料庫的負擔,提高資料庫的效率,通常點來講就是提高表的增刪改查效率。
什麼是分表?
分表是將一個大表按照一定的規則分解成多張具有獨立儲存空間的實體表,我們可以稱為子表,每個表都對應三個檔案,MYD資料檔案,.MYI索引檔案,.frm表結構檔案。這些子表可以分佈在同一塊磁碟上,也可以在不同的機器上。app讀寫的時候根據事先定義好的規則得到對應的子表名,然後去操作它。
什麼是分割槽?
分割槽和分表相似,都是按照規則分解表。不同在於分表將大表分解為若干個獨立的實體表,而分割槽是將資料分段劃分在多個位置存放,可以是同一塊磁碟也可以在不同的機器。分割槽後,表面上還是一張表,但資料雜湊到多個位置了。app讀寫的時候操作的還是大表名字,db自動去組織分割槽的資料。
mysql分表和分割槽有什麼聯絡呢?
1.都能提高mysql的性高,在高併發狀態下都有一個良好的表現。
2.分表和分割槽不矛盾,可以相互配合的,對於那些大訪問量,並且表資料比較多的表,我們可以採取分表和分割槽結合的方式(如果merge這種分表方式,不能和分割槽配合的話,可以用其他的分表試),訪問量不大,但是表資料很多的表,我們可以採取分割槽的方式等。
3.分表技術是比較麻煩的,需要手動去建立子表,app服務端讀寫時候需要計運算元表名。採用merge好一些,但也要建立子表和配置子表間的union關係。
4.表分割槽相對於分表,操作方便,不需要建立子表。
分表的幾種方式:
1、mysql叢集
它並不是分表,但起到了和分表相同的作用。叢集可分擔資料庫的操作次數,將任務分擔到多臺資料庫上。叢集可以讀寫分離,減少讀寫壓力。從而提升資料庫效能。
2、自定義規則分表
大表可以按照業務的規則來分解為多個子表。通常為以下幾種型別,也可自己定義規則。
1
2
3
4
5
|
Range(範圍)–這種模式允許將資料劃分不同範圍。例如可以將一個表通過年份劃分成若干個分割槽。 Hash(雜湊)–這中模式允許通過對錶的一個或多個列的Hash
Key 進行計算,最後通過這個Hash碼不同數值對應的資料區域進行分割槽。例如可以建立一個對錶主鍵進行分割槽的表。 Key (鍵值)-上面Hash模式的一種延伸,這裡的Hash
Key 是MySQL系統產生的。 List(預定義列表)–這種模式允許系統通過預定義的列表的值來對資料進行分割。 Composite(複合模式)
–以上模式的組合使用 |
下面以Range簡單介紹下如何分表(按照年份表)。
假設表結構有4個欄位:自增id,姓名,存款金額,存款日期
把存款日期作為規則分表,分別建立幾個表
2011年:account_2011
2012年:account_2012
……
2015年:account_2015
app在讀寫的時候根據日期來查詢對應的表名,需要手動來判定。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
var
getTableName = function ()
{ var
data = { name :
'tom' , money:
2800.00, date :
'201410013059' }; var
tablename = 'account_' ; var
year
= parseInt(data. date . substring (0,
4)); if
( year
< 2012) { tablename
+= 2011; // account_2011 }
else
if ( year
< 2013) { tablename
+= 2012; // account_2012 }
else
if ( year
< 2014) { tablename
+= 2013; // account_2013 }
else
if ( year
< 2015) { tablename
+= 2014; // account_2014 }
else
{ tablename
+= 2015; // account_2015 } return
tablename; } |
3、利用merge儲存引擎來實現分表
merge分表,分為主表和子表,主表類似於一個殼子,邏輯上封裝了子表,實際上資料都是儲存在子表中的。
我們可以通過主表插入和查詢資料,如果清楚分表規律,也可以直接操作子表。
子表2011年
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
CREATE
TABLE
`account_2011` ( `id`
int (11)
NOT
NULL
AUTO_INCREMENT , ` name `
varchar (50)
CHARACTER
SET
utf8 COLLATE
utf8_general_ci NULL
DEFAULT
NULL
, `money`
float
NOT
NULL
, `tradeDate`
datetime NOT
NULL PRIMARY
KEY
(`id`) ) ENGINE=MyISAM DEFAULT
CHARACTER
SET =utf8
COLLATE =utf8_general_ci AUTO_INCREMENT=2 CHECKSUM=0 ROW_FORMAT= DYNAMIC DELAY_KEY_WRITE=0 ; |
子表2012年
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
CREATE
TABLE
`account_2012` ( `id`
int (11)
NOT
NULL
AUTO_INCREMENT , ` name `
varchar (50)
CHARACTER
SET
utf8 COLLATE
utf8_general_ci NULL
DEFAULT
NULL
, `money`
float
NOT
NULL
, `tradeDate`
datetime NOT
NULL PRIMARY
KEY
(`id`) ) ENGINE=MyISAM DEFAULT
CHARACTER
SET =utf8
COLLATE =utf8_general_ci AUTO_INCREMENT=2 CHECKSUM=0 ROW_FORMAT= DYNAMIC DELAY_KEY_WRITE=0 ; |
主表,所有年
1
2
3
4
5
6
7
8
9
10
11
12
13
|
CREATE
TABLE
`account_all` ( `id`
int (11)
NOT
NULL
AUTO_INCREMENT , ` name `
varchar (50)
CHARACTER
SET
utf8 COLLATE
utf8_general_ci NULL
DEFAULT
NULL
, `money`
float
NOT
NULL
, `tradeDate`
datetime NOT
NULL PRIMARY
KEY
(`id`) ) ENGINE=MRG_MYISAM DEFAULT
CHARACTER
SET =utf8
COLLATE =utf8_general_ci UNION =(`account_2011`,`account_2012`) INSERT_METHOD= LAST ROW_FORMAT= DYNAMIC ; |
建立主表的時候有個INSERT_METHOD,指明插入方式,取值可以是:0 不允許插入;FIRST 插入到UNION中的第一個表; LAST 插入到UNION中的最後一個表。
通過主表查詢的時候,相當於將所有子表合在一起查詢。這樣並不能體現分表的優勢,建議還是查詢子表。
分割槽的幾種方式
Range:
1
2
3
4
5
6
7
8
9
10
|
create
table
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 ); |
List:
1
2
3
4
5
6
7
|
create
table
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) ); |
Hash:
1
2
3
4
5
|
create
table
hash( a
int (11),
b
datetime )partition
by
hash ( YEAR (b)
partitions
4; |
Key:
1
2
3
4
5
|
create
table
t_key( a
int (11),
b
datetime) partition
by
key
(b) partitions
4; |
新增分割槽
1
2
|
ALTER
TABLE sale_data ADD
PARTITION (PARTITION p201010 VALUES LESS THAN ( 201011 )); |
刪除分割槽
--當刪除了一個分割槽,也同時刪除了該分割槽中所有的資料。
ALTER TABLE sale_data DROP PARTITION p201010;
分割槽的合併
下面的SQL,將p201001 - p201009 合併為3個分割槽p2010Q1 - p2010Q3
1
2
3
4
5
6
7
8
9
|
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 ) ); |
相關文章
- mysql —— 分表分割槽(1)MySql
- mysql的分割槽和分表MySql
- mysql的分割槽與分表MySql
- MySql分表、分庫、分片和分割槽MySql
- mysql表水平拆分和分割槽分表MySql
- 【MYSQL】 分割槽表MySql
- MySQL 分割槽表MySql
- MySQL分割槽表MySql
- MySQL分表後原分割槽表處理方案MySql
- MySQL 分割槽表探索MySql
- mysql 分割槽表用法MySql
- MySQL表分割槽管理MySql
- MySQL 分割槽表 partition線上修改分割槽欄位MySql
- Oracle分割槽表基礎運維-01分割槽表分類Oracle運維
- mysql 進行表分割槽MySql
- Mysql表分割槽實現MySql
- mysql分割槽表筆記MySql筆記
- MySQL入門--分割槽表MySql
- MySQL的List分割槽表MySql
- Mysql表分割槽實操MySql
- MySQL分割槽表的分割槽原理和優缺點MySql
- 將mysql非分割槽錶轉換為分割槽表MySql
- MySQL 分割槽表知識整理MySql
- MySQL調優之分割槽表MySql
- mysql分割槽表小結2MySql
- OGG Oracle 分割槽壓縮表 到 MySQL分表的實現OracleMySql
- oracle分割槽表和分割槽表exchangeOracle
- hive 分割槽表和分桶表區別Hive
- MySQL資料表分割槽手記MySql
- (3) MySQL分割槽表使用方法MySql
- mysql之分割槽表小結1MySql
- Hash分割槽表分割槽數與資料分佈的測試
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- oracle分割槽表和非分割槽表exchangeOracle
- mysql~關於mysql分割槽表的測試MySql
- MySQL 分割槽表,為什麼分割槽鍵必須是主鍵的一部分?MySql
- Oracle分割槽表及分割槽索引Oracle索引
- INTERVAL分割槽表鎖分割槽操作