mysql資料庫的數據切分,sql數據拆分

本文目錄一覽：

• 1、怎樣多台電腦公用一個電腦的mysql資料庫
• 2、MySQL切分查詢用法分析
• 3、如何複製mysql資料庫到另一台電腦上
• 4、Mysql某個表有近千萬數據，CRUD比較慢，如何優化？
• 5、mysql 水平切分時，如何保證primary key id不衝突

怎樣多台電腦公用一個電腦的mysql資料庫

這種架構一般用在以下三類場景

1. 備份多台 Server 的數據到一台如果按照數據切分方向來講，那就是垂直切分。比如圖 2，業務 A、B、C、D 是之前拆分好的業務，現在需要把這些拆分好的業務匯總起來備份，那這種需求也很適用於多源複製架構。實現方法我大概描述下：業務 A、B、C、D 分別位於 4 台 Server，每台 Server 分別有一個資料庫來隔離前端的業務數據，那這樣，在從庫就能把四台業務的數據全部匯總起來，而不需要做額外的操作。那沒有多源複製之前，要實現這類需求，只能在匯總機器上搭建多個 MySQL 實例，那這樣勢必會涉及到跨庫關聯的問題，不但性能急劇下降，管理多個實例也沒有單台來的容易。

2. 用來聚合前端多個 Server 的分片數據。

同樣，按照數據切分方向來講，屬於水平切分。比如圖 3，按照年份拆分好的數據，要做一個匯總數據展現，那這種架構也非常合適。實現方法稍微複雜些：比如所有 Server 共享同一資料庫和表，一般為了開發極端透明，前端配置有分庫分表的中間件，比如愛可生的 DBLE。

3. 匯總併合並多個 Server 的數據

第三類和第一種場景類似。不一樣的是不僅僅是數據需要匯總到目標端，還得合併這些數據，這就比第一種來的相對複雜些。比如圖 4，那這樣的需求，是不是也適合多源複製呢？答案是 YES。那具體怎麼做呢？

MySQL切分查詢用法分析

本文實例講述了MySQL切分查詢用法。分享給大家供大家參考，具體如下：

對於大查詢有時需要『分而治之’,將大查詢切分為小查詢:

每個查詢功能完全一樣，但只完成原來的一小部分，每次查詢只返回一小部分結果集。

刪除舊的數據就是一個很好地例子。定期清理舊數據時，如果一條sql涉及了大量的數據時，可能會一次性鎖住多個表或行，耗費了大量的系統資源，卻阻塞了其他很多小的但重要的查詢。將一個大得DELETE語句切分為較小的查詢時，可以盡量減少影響msql的性能，同時減少mysql複製造成的延遲。

例如，每個月會運行一次的語句，以清理三個月前的數據:

複製代碼

代碼如下:mysql

DELETE

FROM

messages

WHERE

dtDATE_SUB(NOW(),

INTERVAL

3

MONTH);

可以用以下的方法來完成這樣的任務：

rows_affected

=

do

{

rows_affected

=

do_query(“DELETE

FROM

messages

WHERE

dtDATE_SUB(NOW(),

INTERVAL

3

MONTH)

LIMIT

10000″)

}while

rows_affected0

一次刪除一萬行數據是個比較高效且對伺服器影響較小的做法。同時如果每次刪除數據時暫停一會，可以將伺服器原本的一次性壓力分散到一個較長的時間段中，從而減少刪除時鎖表鎖行的時間。

更多關於MySQL相關內容感興趣的讀者可查看本站專題：《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》、《MySQL資料庫鎖相關技巧匯總》及《MySQL常用函數大匯總》

希望本文所述對大家MySQL資料庫計有所幫助。

如何複製mysql資料庫到另一台電腦上

有兩種辦法。

1、在B機器上裝mysql。

將A機器上的mysql/data下的你的資料庫目錄整個拷貝下來。

將B機器上的mysql服務停止。

找到B機器上的mysql/data目錄，將你拷貝的目錄粘貼進去，然後啟動mysql服務就可以了。

2、使用SQL語句備份和恢復

你可以使用SELECT

INTO

OUTFILE語句備份數據，並用LOAD

DATA

INFILE語句恢複數據。這種方法只能導出數據的內容，不包括表的結構，如果表的結構文件損壞，你必須要先恢復原來的表的結構。

語法：

SELECT

*

INTO

{OUTFILE

¦

DUMPFILE}

』file_name』

FROM

tbl_name

LOAD

DATA

[LOW_PRIORITY]

[LOCAL]

INFILE

』file_name.txt』

[REPLACE

¦

IGNORE]

INTO

TABLE

tbl_name

SELECT

…

INTO

OUTFILE

』file_name』

在dos命令提示符下使用mysqldump命令進行備份.

如下:

C:\Documents

and

Settings\Administratormysqldump

yinshi

c:\\backup.txt

-uroot

-p12142022

Mysql某個表有近千萬數據，CRUD比較慢，如何優化？

數據千萬級別之多，佔用的存儲空間也比較大，可想而知它不會存儲在一塊連續的物理空間上，而是鏈式存儲在多個碎片的物理空間上。可能對於長字元串的比較，就用更多的時間查找與比較，這就導致用更多的時間。

可以做表拆分，減少單表欄位數量，優化表結構。

在保證主鍵有效的情況下，檢查主鍵索引的欄位順序，使得查詢語句中條件的欄位順序和主鍵索引的欄位順序保持一致。

主要兩種拆分 垂直拆分，水平拆分。

垂直分表

也就是「大表拆小表」，基於列欄位進行的。一般是表中的欄位較多，將不常用的， 數據較大，長度較長（比如text類型欄位）的拆分到「擴展表「。 一般是針對 那種 幾百列的大表，也避免查詢時，數據量太大造成的「跨頁」問題。

垂直分庫針對的是一個系統中的不同業務進行拆分，比如用戶User一個庫，商品Product一個庫，訂單Order一個庫。 切分後，要放在多個伺服器上，而不是一個伺服器上。為什麼？ 我們想像一下，一個購物網站對外提供服務，會有用戶，商品，訂單等的CRUD。沒拆分之前， 全部都是落到單一的庫上的，這會讓資料庫的單庫處理能力成為瓶頸。按垂直分庫後，如果還是放在一個資料庫伺服器上， 隨著用戶量增大，這會讓單個資料庫的處理能力成為瓶頸，還有單個伺服器的磁碟空間，內存，tps等非常吃緊。 所以我們要拆分到多個伺服器上，這樣上面的問題都解決了，以後也不會面對單機資源問題。

資料庫業務層面的拆分，和服務的「治理」，「降級」機制類似，也能對不同業務的數據分別的進行管理，維護，監控，擴展等。 資料庫往往最容易成為應用系統的瓶頸，而資料庫本身屬於「有狀態」的，相對於Web和應用伺服器來講，是比較難實現「橫向擴展」的。 資料庫的連接資源比較寶貴且單機處理能力也有限，在高並發場景下，垂直分庫一定程度上能夠突破IO、連接數及單機硬體資源的瓶頸。

水平分表

針對數據量巨大的單張表（比如訂單表），按照某種規則（RANGE,HASH取模等），切分到多張表裡面去。 但是這些表還是在同一個庫中，所以庫級別的資料庫操作還是有IO瓶頸。不建議採用。

水平分庫分表

將單張表的數據切分到多個伺服器上去，每個伺服器具有相應的庫與表，只是表中數據集合不同。 水平分庫分表能夠有效的緩解單機和單庫的性能瓶頸和壓力，突破IO、連接數、硬體資源等的瓶頸。

水平分庫分表切分規則

1. RANGE

從0到10000一個表，10001到20000一個表；

2. HASH取模

一個商場系統，一般都是將用戶，訂單作為主表，然後將和它們相關的作為附表，這樣不會造成跨庫事務之類的問題。 取用戶id，然後hash取模，分配到不同的資料庫上。

3. 地理區域

比如按照華東，華南，華北這樣來區分業務，七牛雲應該就是如此。

4. 時間

按照時間切分，就是將6個月前，甚至一年前的數據切出去放到另外的一張表，因為隨著時間流逝，這些表的數據 被查詢的概率變小，所以沒必要和「熱數據」放在一起，這個也是「冷熱數據分離」。

分庫分表後面臨的問題

事務支持

分庫分表後，就成了分散式事務了。如果依賴資料庫本身的分散式事務管理功能去執行事務，將付出高昂的性能代價； 如果由應用程序去協助控制，形成程序邏輯上的事務，又會造成編程方面的負擔。

跨庫join

只要是進行切分，跨節點Join的問題是不可避免的。但是良好的設計和切分卻可以減少此類情況的發生。解決這一問題的普遍做法是分兩次查詢實現。在第一次查詢的結果集中找出關聯數據的id,根據這些id發起第二次請求得到關聯數據。

跨節點的count,order by,group by以及聚合函數問題

這些是一類問題，因為它們都需要基於全部數據集合進行計算。多數的代理都不會自動處理合併工作。解決方案：與解決跨節點join問題的類似，分別在各個節點上得到結果後在應用程序端進行合併。和join不同的是每個結點的查詢可以並行執行，因此很多時候它的速度要比單一大錶快很多。但如果結果集很大，對應用程序內存的消耗是一個問題。

數據遷移，容量規劃，擴容等問題

來自淘寶綜合業務平台團隊，它利用對2的倍數取余具有向前兼容的特性（如對4取余得1的數對2取余也是1）來分配數據，避免了行級別的數據遷移，但是依然需要進行表級別的遷移，同時對擴容規模和分表數量都有限制。總得來說，這些方案都不是十分的理想，多多少少都存在一些缺點，這也從一個側面反映出了Sharding擴容的難度。

ID問題

一旦資料庫被切分到多個物理結點上，我們將不能再依賴資料庫自身的主鍵生成機制。一方面，某個分區資料庫自生成的ID無法保證在全局上是唯一的；另一方面，應用程序在插入數據之前需要先獲得ID,以便進行SQL路由.

一些常見的主鍵生成策略

UUID

使用UUID作主鍵是最簡單的方案，但是缺點也是非常明顯的。由於UUID非常的長，除佔用大量存儲空間外，最主要的問題是在索引上，在建立索引和基於索引進行查詢時都存在性能問題。

Twitter的分散式自增ID演算法Snowflake

在分散式系統中，需要生成全局UID的場合還是比較多的，twitter的snowflake解決了這種需求，實現也還是很簡單的，除去配置信息，核心代碼就是毫秒級時間41位 機器ID 10位 毫秒內序列12位。

跨分片的排序分頁

一般來講，分頁時需要按照指定欄位進行排序。當排序欄位就是分片欄位的時候，我們通過分片規則可以比較容易定位到指定的分片，而當排序欄位非分片欄位的時候，情況就會變得比較複雜了。為了最終結果的準確性，我們需要在不同的分片節點中將數據進行排序並返回，並將不同分片返回的結果集進行匯總和再次排序，最後再返回給用戶。

mysql 水平切分時，如何保證primary key id不衝突

例如，表：create table blog (id bigint(20) unsigned not null auto_increment,author varchar(40) not null,content text not null,primary key (id));如果把該表shard到10個(隨時可能增加)物理資料庫伺服器上，那麼如何保證id不重複？方法1：通過uuid不可行：uuid的值太大，嚴重影響index性能；同時，innodb的clustered index在id不順序插入的情況下，性能大打折扣方法2：用個global table來記錄每個blog id，當要獲取新的id時，首先select max(id) from global_id_table，查到值後，insert into global_id_table不可行：global_id_table會變得非常大，而且造成插入瓶頸方法：利用mysql對ansi sql的擴展：replace into語句CREATE TABLE `ttt` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `stub` char(1) NOT NULL DEFAULT ”, PRIMARY KEY (`id`), UNIQUE KEY `stub` (`stub`))當要獲取新的id時：replace into ttt(stub) values(‘a’);SELECT LAST_INSERT_ID();table ttt將變成單點故障源，解決辦法：弄2個專門的db node，並通過auto-increment-increment解決