什么是分表和分區(qū) MySql數(shù)據(jù)庫分區(qū)和分表方法
1、為什么要分表和分區(qū)
日常開發(fā)中我們經常會遇到大表的情況，所謂的大表是指存儲了百萬級乃至千萬級條記錄的表。這樣的表過于龐大，導致數(shù)據(jù)庫在查詢和插入的時候耗時太長，性能低下，如果涉及聯(lián)合查詢的情況，性能會更加糟糕。分表和表分區(qū)的目的就是減少數(shù)據(jù)庫的負擔，提高數(shù)據(jù)庫的效率，通常點來講就是提高表的增刪改查效率。
2、什么是分表和分區(qū)
2.1 分表
分表是將一個大表按照一定的規(guī)則分解成多張具有獨立存儲空間的實體表，我們可以稱為子表，每個表都對應三個文件，MYD數(shù)據(jù)文件，.MYI索引文件，.frm表結構文件。這些子表可以分布在同一塊磁盤上，也可以在不同的機器上。app讀寫的時候根據(jù)事先定義好的規(guī)則得到對應的子表名，然后去操作它。
2.2 分區(qū)
分區(qū)和分表相似，都是按照規(guī)則分解表。不同在于分表將大表分解為若干個獨立的實體表，而分區(qū)是將數(shù)據(jù)分段劃分在多個位置存放，可以是同一塊磁盤也可以在不同的機器。分區(qū)后，表面上還是一張表，但數(shù)據(jù)散列到多個位置了。app讀寫的時候操作的還是大表名字，db自動去組織分區(qū)的數(shù)據(jù)。
分區(qū)的主要目的是為了在特定的SQL操作中減少數(shù)據(jù)讀寫的總量以縮減響應時間。
2.3 mysql分表和分區(qū)有什么聯(lián)系呢？
1）、都能提高mysql的性能，在高并發(fā)狀態(tài)下都有一個良好的表現(xiàn)。
2）、分表和分區(qū)不矛盾，可以相互配合的，對于那些大訪問量，并且表數(shù)據(jù)比較多的表，我們可以采取分表和分區(qū)結合的方式，訪問量不大，但是表數(shù)據(jù)很多的表，我們可以采取分區(qū)的方式等。
3）、分表技術是比較麻煩的，需要手動去創(chuàng)建子表，app服務端讀寫時候需要計算子表名。采用merge好一些，但也要創(chuàng)建子表和配置子表間的union關系。
4）、表分區(qū)相對于分表，操作方便，不需要創(chuàng)建子表。
3、分表的幾種方式
3.1 mysql集群
它并不是分表，但起到了和分表相同的作用。集群可分擔數(shù)據(jù)庫的操作次數(shù)，將任務分擔到多臺數(shù)據(jù)庫上。集群可以讀寫分離，減少讀寫壓力。從而提升數(shù)據(jù)庫性能。
3.2 自定義規(guī)則分表
大表可以按照業(yè)務的規(guī)則來分解為多個子表。通常為以下幾種類型，也可自己定義規(guī)則。
Range（范圍）–這種模式允許將數(shù)據(jù)劃分不同范圍。例如可以將一個表通過年份劃分成若干個分區(qū)。
Hash（哈希）–這中模式允許通過對表的一個或多個列的Hash Key進行計算，最后通過這個Hash碼不同數(shù)值對應的數(shù)據(jù)區(qū)域進行分區(qū)。例如可以建立一個對表主鍵進行分區(qū)的表。
Key（鍵值）–上面Hash模式的一種延伸，這里的Hash Key是MySQL系統(tǒng)產生的。
List（預定義列表）–這種模式允許系統(tǒng)通過預定義的列表的值來對數(shù)據(jù)進行分割。
Composite（復合模式）–以上模式的組合使用
分表規(guī)則與分區(qū)規(guī)則一樣，在分區(qū)模塊詳細介紹。
下面以Range簡單介紹下如何分表（按照年份表）。
假設表結構有4個字段：自增id，姓名，存款金額，存款日期
把存款日期作為規(guī)則分表，分別創(chuàng)建幾個表
2011年：account_2011
2012年：account_2012
……
2015年：account_2015
app在讀寫的時候根據(jù)日期來查找對應的表名，需要手動來判定。
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.3 利用merge存儲引擎來實現(xiàn)分表
merge分表，分為主表和子表，主表類似于一個殼子，邏輯上封裝了子表，實際上數(shù)據(jù)都是存儲在子表中的。
我們可以通過主表插入和查詢數(shù)據(jù)，如果清楚分表規(guī)律，也可以直接操作子表。
子表2011年
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年
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 ;
主表，所有年
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 ;
創(chuàng)建主表的時候有個INSERT_METHOD，指明插入方式，取值可以是：0 不允許插入；FIRST 插入到UNION中的第一個表； LAST 插入到UNION中的最后一個表。
通過主表查詢的時候，相當于將所有子表合在一起查詢。這樣并不能體現(xiàn)分表的優(yōu)勢，建議還是查詢子表。
4、分區(qū)的幾種方式
4.1 Range
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 );
4.2 List
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) );
4.3 Hash
create table hash( a int(11), b datetime )partition by hash (YEAR(b) partitions 4;
4.4 key
create table t_key( a int(11), b datetime) partition by key (b) partitions 4;
4.5 分區(qū)管理
4.5.1 新增分區(qū)
ALTER TABLE sale_data ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));
4.5.2 刪除分區(qū)
當刪除了一個分區(qū)，也同時刪除了該分區(qū)中所有的數(shù)據(jù)。
ALTER TABLE sale_data DROP PARTITION p201010;
4.5.3 合并分區(qū)
下面的SQL，將p201001 - p201009 合并為3個分區(qū)p2010Q1 - p2010Q3
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) );
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持腳本之家。
相關文章
rpm -ivh方式安裝mysql并修改數(shù)據(jù)存儲位置的實現(xiàn)
在Linux環(huán)境下進行MySQL的安裝可以使用不同的方式,但在本文中我們將關注一種特定的方式,即通過RPM包的方式進行安裝,本文主要介紹了rpm -ivh方式安裝mysql并修改數(shù)據(jù)存儲位置的實現(xiàn),感興趣的可以了解一下2023-09-09
這篇文章主要為大家詳細介紹了MySQL中常用的表級操作,文中的示例代碼簡潔易懂,對我們學習MySQL有一定的幫助,感興趣的小伙伴可以學習一下2023-08-08
DBeaver是一個開源的通用數(shù)據(jù)庫管理工具,可以連接多種不同類型的數(shù)據(jù)庫,本文主要介紹了MySQL客戶端軟件(DBeaver)連接報錯解決方案,具有一定的參考價值,感興趣的可以了解一下2024-04-04
下面小編就為大家?guī)硪黄猠xplain分析sql效率的方法。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-03-03
MySQL數(shù)據(jù)庫MyISAM存儲引擎轉為Innodb的方法
mysql數(shù)據(jù)庫存儲引擎為MyISAM的時候,在大訪問量的情況下數(shù)據(jù)表有可能會出現(xiàn)被鎖的情況，這就會導致用戶連接網站時超時而返回502，此時就需要MySQL數(shù)據(jù)庫MyISAM存儲引擎轉為Innodb，這篇文章主要介紹了MySQL數(shù)據(jù)庫MyISAM存儲引擎轉為Innodb的方法,需要的朋友可以參考下2014-06-06
使用Shell腳本進行MySql權限修改的實現(xiàn)教程
原先數(shù)據(jù)配置文件中有bind-address=127.0.0.1,注釋掉此配置后,原數(shù)據(jù)庫中默認帶%root的權限,現(xiàn)在需要通過腳本實現(xiàn)白名單列表中的ip添加權限允許訪問數(shù)據(jù)庫,本文給大家介紹了使用Shell腳本進行MySql權限修改的實現(xiàn)教程,需要的朋友可以參考下2024-03-03
這篇文章主要介紹了mysql數(shù)據(jù)NULL避坑指南,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-05-05
linux 安裝 mysql 8.0.19 詳細步驟及問題解決方法
這篇文章主要介紹了linux 安裝 mysql 8.0.19 詳細步驟,本文給大家列出了常見問題及解決方法，通過實例代碼給大家介紹的非常詳細，具有一定的參考借鑒價值，需要的朋友可以參考下2020-02-02
MySQL如何從數(shù)據(jù)庫中刪除表中所有數(shù)據(jù)
這篇文章主要介紹了MySQL如何從數(shù)據(jù)庫中刪除表中所有數(shù)據(jù)問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-06-06
這篇文章主要為大家詳細介紹了MySQL中字符集的相關使用，例如字符集的查詢與修改和比較規(guī)則等，文中的示例代碼講解詳細，需要的可以參考一下2023-05-05