012-- mysql的分區和分表

分區

分區就是把一個數據表的文件和索引分散存儲在不同的物理文件中。

mysql支持的分區類型包括Range、List、Hash、Key,其中Range比較常用:

RANGE分區:基于屬于一個給定連續區間的列值,把多行分配給分區。

LIST分區:類似于按RANGE分區,區別在于LIST分區是基于列值匹配一個離散值集合中的某個值來進行選擇。

HASH分區:基于用戶定義的表達式的返回值來進行選擇的分區,該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL 中有效的、產生非負整數值的任何表達式。

KEY分區:類似于按HASH分區,區別在于KEY分區只支持計算一列或多列,且MySQL服務器提供其自身的哈希函數。必須有一列或多列包含整數值。

案例:

建立一個user 表 以id進行分區 id 小于10的在user_1分區id小于20的在user_2分區

create table user(id int not null auto_increment,username varchar(10),primary key(id)
)engine = innodb charset=utf8
partition by range (id)(partition user_1 values less than (10),partition user_2 values less than (20)
);

建立后添加分區:

maxvalue 表示最大值 ? 這樣大于等于20的id 都出存儲在user_3分區

alter table user add partition(partition user_3 values less than maxvalue
);

刪除分區:

alter  table user drop partition user_3;

?

如果表使用的存儲引擎是MyISAM類型,就是:

user#P#user_1.MYD,user#P#user_1.MYI和user#P#user_2.MYD,user#P#user_2.MYI

由此可見,mysql通過分區把數據保存到不同的文件里,同時索引也是分區的。相對于未分區的表來說,分區后單獨的數據庫文件索引文件的大小都明顯降低,效率則明顯的提示了。可以插入一條數據然后分析查詢語句驗證一下:

insert into user values(null,'測試');explain partitions select * from user where id =1;

可以看見僅僅在user_1分區執行了這條查詢。

具體分區的效率是多少還需要看數據量。在分區時可以通過 DATA DIRECTORY 和?  INDEX DIRECTORY 選項吧不同的分區放到不同的磁盤上進一步提高系統的I/O吞吐量。

分區類型的選擇,通常使用Range類型,不過有些情況,比如主從結構中,主服務器很少使用‘select’查詢,在主服務器上使用 Range類型分區通常沒有太大的意義,此時使用Hash類型分區更好例如:

partition by hash(id) partitions 10;

當插入數據時,根據id吧數據平均散到各個分區上,由于文件小,效率高,更新操作變得更快。

在分區時使用的字段,通常情況下按時間字段分區,具體情況以需求而定。劃分應用的方式有很多種,比如按時間或用戶,哪種用的多,就選擇哪種分區。如果使用主從結構可能就更加靈活,有的從服務器使用時間,有的使用用戶。不過如此一來當執行查詢時,程序應該負責選擇真確的服務器查詢,寫個mysql proxy腳本應該可以透明的實現。

分區的限制:

1.主鍵或者唯一索引必須包含分區字段,如primary key (id,username),不過innoDB的大組建性能不好。

2.很多時候,使用分區就不要在使用主鍵了,否則可能影響性能。

3.只能通過int類型的字段或者返回int類型的表達式來分區,通常使用year或者to_days等函數(mysql 5.6 對限制開始放開了)。

4.每個表最多1024個分區,而且多分區會大量消耗內存。

5.分區的表不支持外鍵,相關的邏輯約束需要使用程序來實現。

6.分區后,可能會造成索引失效,需要驗證分區可行性。

分區模式詳解:

*?Range(范圍)?– 這種模式允許DBA將數據劃分不同范圍。例如DBA可以將一個表通過年份劃分成三個分區,80年代(1980's)的數據,90年代(1990's)的數據以及任何在2000年(包括2000年)后的數據。

CREATE TABLE users (  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  usersname VARCHAR(30) NOT NULL DEFAULT '',  email VARCHAR(30) NOT NULL DEFAULT ''  
)  
PARTITION BY RANGE (id) (  PARTITION p0 VALUES LESS THAN (3000000),  PARTITION p1 VALUES LESS THAN (6000000), PARTITION p2 VALUES LESS THAN (9000000),  PARTITION p3 VALUES LESS THAN MAXVALUE     
);  

在這里,將用戶表分成4個分區,以每300萬條記錄為界限,每個分區都有自己獨立的數據、索引文件的存放目錄。

還可以將這些分區所在的物理磁盤分開完全獨立,可以提高磁盤IO吞吐量。

CREATE TABLE users (  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  usersname VARCHAR(30) NOT NULL DEFAULT '',  email VARCHAR(30) NOT NULL DEFAULT ''  
)  
PARTITION BY RANGE (id) (  PARTITION p0 VALUES LESS THAN (3000000)  DATA DIRECTORY = '/data0/data'  INDEX DIRECTORY = '/data0/index',  PARTITION p1 VALUES LESS THAN (6000000)  DATA DIRECTORY = '/data1/data'  INDEX DIRECTORY = '/data1/index',  PARTITION p2 VALUES LESS THAN (9000000)  DATA DIRECTORY = '/data2/data'  INDEX DIRECTORY = '/data2/index',  PARTITION p3 VALUES LESS THAN MAXVALUE     DATA DIRECTORY = '/data3/data'   INDEX DIRECTORY = '/data3/index'  
);  

?

*?List(預定義列表)?– 這種模式允許系統通過DBA定義的列表的值所對應的行數據進行分割。例如:DBA根據用戶的類型進行分區。?

CREATE TABLE user (  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  name VARCHAR(30) NOT NULL DEFAULT '' ,user_type   int not null
)  
PARTITION BY LIST (user_type ) (  PARTITION p0 VALUES IN (0,4,8,12) , PARTITION p1 VALUES IN (1,5,9,13) ,  PARTITION p2 VALUES IN (2,6,10,14),  PARTITION p3 VALUES IN (3,7,11,15)   
);     

分成4個區,同樣可以將分區設置的獨立的磁盤中。



*?Key(鍵值)?– 上面Hash模式的一種延伸,這里的Hash Key是MySQL系統產生的。?

CREATE TABLE user (  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  name VARCHAR(30) NOT NULL DEFAULT '',  email VARCHAR(30) NOT NULL DEFAULT ''  
)  
PARTITION BY KEY (id) PARTITIONS 4 (  PARTITION p0,  PARTITION p1,  PARTITION p2,  PARTITION p3
);     

?

*?Hash(哈希)?– 這中模式允許DBA通過對表的一個或多個列的Hash Key進行計算,最后通過這個Hash碼不同數值對應的數據區域進行分區,。例如DBA可以建立一個對表主鍵進行分區的表。?

CREATE TABLE user (  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  username VARCHAR(30) NOT NULL DEFAULT '',  email VARCHAR(30) NOT NULL DEFAULT ''  
)  
PARTITION BY HASH (id) PARTITIONS 4 (  PARTITION p0 ,  PARTITION p1,  PARTITION p2,PARTITION p3  
);  

分成4個區,同樣可以將分區設置的獨立的磁盤中。


= 分區管理 =


刪除分區

ALERT TABLE users DROP PARTITION p0;  

重建分區

? RANGE 分區重建

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));  

將原來的 p0,p1 分區合并起來,放到新的 p0 分區中。

LIST 分區重建

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));  

將原來的 p0,p1 分區合并起來,放到新的 p0 分區中。

HASH/KEY 分區重建

ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;  

用 REORGANIZE 方式重建分區的數量變成2,在這里數量只能減少不能增加。想要增加可以用 ADD PARTITION 方法。

新增分區

新增 RANGE 分區??

alter table user add partition(partition user_3 values less than maxvalue);

?

新增 LIST 分區?

ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19));  

新增 HASH/KEY 分區

ALTER TABLE users ADD PARTITION PARTITIONS 8;  

將分區總數擴展到8個。

給已有的表加上分區

alter table results partition by RANGE (month(ttime))   
(PARTITION p0 VALUES LESS THAN (1),  
PARTITION p1 VALUES LESS THAN (2) , PARTITION p2 VALUES LESS THAN (3) ,  
PARTITION p3 VALUES LESS THAN (4) , PARTITION p4 VALUES LESS THAN (5) ,  
PARTITION p5 VALUES LESS THAN (6) , PARTITION p6 VALUES LESS THAN (7) ,  
PARTITION p7 VALUES LESS THAN (8) , PARTITION p8 VALUES LESS THAN (9) ,  
PARTITION p9 VALUES LESS THAN (10) , PARTITION p10 VALUES LESS THAN (11),  
PARTITION p11 VALUES LESS THAN (12),  
PARTITION P12 VALUES LESS THAN (13) );   

分表

分表和分區類似,區別是,分區是把一個邏輯表文件分成幾個物理文件后進行存儲,而分表則是把原先的一個表分成幾個表。進行分表查詢時可以通過union或者視圖。

分表又分垂直分割和水平分割,其中水平分分割最為常用。水平分割通常是指切分到另外一個數據庫或表中。例如對于一個會員表,按對3的模進行分割:

table = id%3

如果id%3 = 0 則將用戶數據放入到user_0表中,如id%3=1就放入user_1表中,依次類推。

在這里有個問題,這個uid應該是所有會員按序增長的,可他是怎么得到的呢?使用auto_increment是不行的,這樣就用到序列了。

對于一些流量統計系統,其數據量比較大,并且對過往數據的關注度不高,這時按年、月、日進行分表,將每日統計信息放到一個以日期命名的表中;或者按照增量進行分表,如每個表100萬數據,超過100萬就放入第二個表。還可以按Hash進行分表,但是按日期和取模余數分表最為常見,也容易擴展。

分表后可能會遇到新的問題,那就是查詢,分頁和統計。通用的方法是在程序中進行處理,輔助視圖。

使用分表案例:

案例1:

對會員數據對5取模,放在5個表中,如何查詢會員數據:

1.已知id查詢會員數據,代碼如下:

<?php
//查詢單個會員數據
$customer_table = 'customer'.$id%5;
$sql = 'select * from '.$customer_table.' where customer_id = '.$id;
//查詢全部會員數據
$sql = '';
$tbale = ['customer0','customer1','customer2','customer3','customer4'];
foreach($table as $v){
$sql .='select * from '.$v.' union';
}
$sql = substr($sql,0,-5);?>

這樣就可以查詢某一個會員的數據或者全部會員的數據了。同理,分頁的話在這個大集合中使用limit 就可以了。但是這樣做又會有一個疑問,把所有的表連起來查詢和部分表沒有什么區別,其實在實際的應用中,不可能查看所有的會員資料,一次查看20個然后分頁。完全沒有必要做union,僅查詢一個表就可以了,唯一需要考慮的是在分頁零界點時的銜接。其實,這個銜接是否那么重要?即使偶爾出現幾條數據的差異,也不會對業務有任何的影響。

2.和其它表進行關聯和1類似。

3.根據會員姓名搜索用戶信息。在這種需求下,需要搜索所有的表,并對結果進行匯總。雖然這樣做產生了多次的查詢,但并不代表效率低。好的sql語句執行10次也比差的sql語句執行一次快。

案例2:

在一個流量監控系統中,由于網絡流量巨大,統計數據很龐大,需要按天分表。先要得到任意日,周,月的數據。

1.需要任意一天的數據。直接查詢當天的數據表即可。

2.需要幾天的數據。分愛查詢這幾天的數據,然后進行匯總。

3.需要查詢一周的數據。對一周的數據定期匯總到一個week表,從這個表里面查詢。這個匯總過程可以由一個外部程序完成,也可以由定期的腳本完成。

4.查詢一個月的數據。匯總本月所有的數據到month表,在此表查詢。

5.查詢5個月內的詳細數據。不支持。僅支持最多3個月的詳細數據。數據沒3個月已歸檔一次。在大數據的處理中,必須做出一些犧牲。對于超出3個月的數據,僅提供統計數據,詳細數據需要查看歸檔。90天或者180天,給數據保存設個界限,也是大部分這類系統的常規做法,超出90天的數據就不再提供數據詳單了。比如,移動的通話記錄最多保存半年,即180天,超過這個范圍的數據不在提供查詢。如果你實在需要,可能就要聯系移動的工程師了。

分表前應該盡量按照實際業務來分表,參考依據就是哪些字段在查詢中起到作用,那就這些字段來分表,并且需要在分表前就估算好規模,也就是先確定好規則在分表。

對于分表后的操作,依然是聯合查詢,視圖等基本操作,或者使用merge引擎合并數據并在此表中查詢。復雜一些操作需要借助存儲過程來完成,借助外部工具實現對分表的管理。

對于比較龐大的數據,不論是否進行分表,都必須考慮功能和效率的平衡性,并在功能上做出讓步。我們不能事事遷就用戶,而應該對某些影響效率的功能做出限制。例如移動公司的180天限制、論壇禁止對老帖進行回復等。

轉載于:https://www.cnblogs.com/yxllovetm/p/10123731.html

本文來自互聯網用戶投稿,該文觀點僅代表作者本人,不代表本站立場。本站僅提供信息存儲空間服務,不擁有所有權,不承擔相關法律責任。
如若轉載,請注明出處:http://www.pswp.cn/news/395715.shtml
繁體地址,請注明出處:http://hk.pswp.cn/news/395715.shtml
英文地址,請注明出處:http://en.pswp.cn/news/395715.shtml

如若內容造成侵權/違法違規/事實不符,請聯系多彩編程網進行投訴反饋email:809451989@qq.com,一經查實,立即刪除!

相關文章

中科大計算機專業少實習_為什么很少有計算機科學專業的人?

中科大計算機專業少實習Hong Kong-based technology writer Dan Wang wrote a blog post exploring why so few people get degrees in computer science. And yes — it’s far fewer people than those who study other science and engineering fields.駐香港的技術作家王丹…

java犯的小錯誤_[Java教程]十個JavaScript中易犯的小錯誤,你中了幾槍?

[Java教程]十個JavaScript中易犯的小錯誤&#xff0c;你中了幾槍&#xff1f;0 2015-06-01 12:00:19序言在今天&#xff0c;JavaScript已經成為了網頁編輯的核心。尤其是過去的幾年&#xff0c;互聯網見證了在SPA開發、圖形處理、交互等方面大量JS庫的出現。如果初次打交道&…

Kali滲透測試——利用metasploit攻擊靶機WinXP SP1

搭建滲透測試環境 Kali攻擊機 WinXP SP1 靶機 啟動metasploit 跟windows RPC相關的漏洞 內部提供的漏洞攻擊 靶機winxp sp1網絡配置 查看虛擬機的NAT網段 配置WinXP SP1靶機的IP地址 執行漏洞利用 后漏洞利用&#xff1a;meterpreter> 靶機的信息 進程情況 查看到explorer.e…

創建響應式布局的優秀網格工具集錦《系列五》

在這篇文章中&#xff0c;我們為您呈現了一組優秀的網格工具清單。如果網頁設計和開人員采用了正確的工具集&#xff0c;并基于一個靈活的網格架構&#xff0c;以及能夠把響應圖像應用到到設計之中&#xff0c;那么創建一個具備響應式的網站并不一定是一項艱巨的任務。enjoy! 您…

【iOS - 周總結】開發中遇到的小知識點(2018.12.10-2018.12.15)

1.WKWebview加載html文本圖片過大&#xff0c;沒有自適應屏幕寬高。 在用Webview加載html文本有時候會遇到加載的圖片過大&#xff0c;不能自適應屏幕寬高的問題。那么如何解決這個問題&#xff1f;如何使圖片自適應屏幕&#xff1f;很簡單&#xff0c;只需要加一個js就可以。 …

如何使用Create React App DevOps自動化工作中所有無聊的部分

by James Y Rauhut詹姆士魯豪(James Y Rauhut) 如何使用Create React App DevOps自動化工作中所有無聊的部分 (How I automate all of the boring parts of my job with Create React App DevOps) When you have responsibilities as one of the only designers — and possib…

java 無侵入監控_MyPerf4J 一個高性能、無侵入的Java性能監控和統計工具

MyPerf4J一個針對高并發、低延遲應用設計的高性能且無侵入的實時Java性能監控和統計工具。 受 perf4j 和 TProfiler啟發而來。MyPerf4J具有以下幾個特性&#xff1a;無侵入: 采用JavaAgent方式&#xff0c;對應用程序完全無侵入&#xff0c;無需修改應用代碼高性能: 性能消耗非…

Apple Swift編程語言新手教程

文件夾 1 簡單介紹2 Swift入門3 簡單值4 控制流5 函數與閉包6 對象與類7 枚舉與結構1 簡單介紹 今天凌晨Apple剛剛公布了Swift編程語言&#xff0c;本文從其公布的書籍《The Swift Programming Language》中摘錄和提取而成。希望對各位的iOS&OSX開發有所幫…

javascript 減少回流

減少回流&#xff08;REFLOWS&#xff09; 當瀏覽器重新渲染文檔中的元素時需要 重新計算它們的位置和幾何形狀&#xff0c;我們稱之為回流。回流會阻塞用戶在瀏覽器中的操作&#xff0c;因此理解提升回流時間是非常有幫助的。 回流時間圖表 你應該批量地觸發回流或重繪&#x…

[國家集訓隊] 特技飛行

題目背景 1.wqs愛好模擬飛行。 2.clj開了一家神犇航空&#xff0c;由于clj還要玩游戲&#xff0c;所以公司的事務由你來打理。 注意&#xff1a;題目中只是用了這樣一個背景&#xff0c;并不與真實/模擬飛行相符 題目描述 神犇航空開展了一項載客特技飛行業務。每次飛行長N個單…

react 手指移開_代碼簡介:React的五個死亡手指

react 手指移開Here are three stories we published this week that are worth your time:這是我們本周發布的三個值得您關注的故事&#xff1a; React’s Five Fingers of Death. Master these five concepts, then master React: 10 minute read React的五指死亡。 掌握這五…

java lock接口_Java Lock接口

Java Lock接口java.util.concurrent.locks.Lock接口用作線程同步機制&#xff0c;類似于同步塊。新的鎖定機制更靈活&#xff0c;提供比同步塊更多的選項。 鎖和同步塊之間的主要區別如下&#xff1a;序列的保證 - 同步塊不提供對等待線程進行訪問的序列的任何保證&#xff0c;…

springcloud-05-ribbon中不使用eureka

ribbon在有eureka的情況下, 可以不使用eureka, 挺簡單, 直接上代碼 application.xml server:port: 7002 spring:# 設置eureka中注冊的名稱, 全小寫, 否則大小寫混雜出現問題application:name: microservice-consumer-movie-ribben-ymllogging:level:root: INFOorg.hibernate: I…

SQL mysql優化

慢查詢 如何通過慢查日志發現有問題的SQL&#xff1f; 查詢次數多且每次查詢占用時間長的SQL pt-query-digest分析前幾個查詢IO大的SQL pt-query-diges分析中的Rows examine項未命中索引的SQL pt-query-digest分析中Rows examine 和Rows Send的對比如何分析SQL查詢 使用explain…

轉: 關于 ssl的建立鏈接的過程

轉自&#xff1a; http://www.ruanyifeng.com/blog/2014/02/ssl_tls.html SSL/TLS協議運行機制的概述 作者&#xff1a; 阮一峰 日期&#xff1a; 2014年2月 5日 互聯網的通信安全&#xff0c;建立在SSL/TLS協議之上。 本文簡要介紹SSL/TLS協議的運行機制。文章的重點是設計思…

第一章第一個c#程序上機_我從第一個#100DaysOfCode中學到的東西

第一章第一個c#程序上機On May 17th, I completed my first round of #100DaysOfCode. In case you haven’t heard, #100DaysOfCode is a challenge, or movement, started by Alexander Kallaway for people interested in coding. The basis of the challenge is that you p…

[Swift通天遁地]一、超級工具-(2)制作美觀大方的環形進度條

★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★?微信公眾號&#xff1a;山青詠芝&#xff08;shanqingyongzhi&#xff09;?博客園地址&#xff1a;山青詠芝&#xff08;https://www.cnblogs.com/strengthen/&#xff09;?GitHub地址&a…

SPOJ QTREE6 lct

題目鏈接 島娘出的題。還是比較easy的 #include <iostream> #include <fstream> #include <string> #include <time.h> #include <vector> #include <map> #include <queue> #include <algorithm> #include <stack> #in…

使用charles 抓取手機上的操作

Charles上的設置要截取iPhone上的網絡請求&#xff0c;我們首先需要將Charles的代理功能打開。在Charles的菜單欄上選擇“Proxy”->“Proxy Settings”&#xff0c;填入代理端口8888&#xff0c;并且勾上”Enable transparent HTTP proxying” 就完成了在Charles上的設置。如…

FreeCodeCamp納什維爾聚會的回顧

by Seth Alexander塞斯亞歷山大(Seth Alexander) FreeCodeCamp納什維爾聚會的回顧 (A Recap from the freeCodeCamp Nashville Meetup) At a recent freeCodeCamp meetup, a small group of campers got together to solve some coding challenges and we talk shop.在最近的f…