當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL分库分表分库准备(6th)

發(fā)布時間：2024/1/17 数据库 21 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL分库分表分库准备(6th) 小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

前言

隨著業(yè)務的發(fā)展單庫中的分表的數(shù)量越來越多, 使用在單庫上存放過多的表這樣是不合理的。因此，我們就需要考慮將數(shù)據(jù)根據(jù)數(shù)據(jù)庫進行拆分。

一般mysql不建議表的數(shù)量超過1000個。當然，這不能一概而論，還需要根據(jù)你的數(shù)據(jù)量，和硬件來確定然后根據(jù)自己的服務器調(diào)整幾個mysql '%open%' 參數(shù)，從而來確定你的庫應該不超過幾張表性能能在可接受范圍內(nèi)。

分庫思路

在分庫前我們需要確定一下我們應該如何去分庫：

1、我們是根據(jù)用戶ID來進行分庫，和分表的思路一樣。

2、我們需要在用戶表中標記一下用戶的數(shù)據(jù)是在哪個庫。

3、在系統(tǒng)設置表中應該記錄下當前最大分庫數(shù)量。

4、在系統(tǒng)設置表中應該記錄現(xiàn)在所有分庫的庫名。

5、在系統(tǒng)設置表中應該記錄每個分庫的數(shù)據(jù)庫連接描述符信息。

分庫規(guī)則

我們以 '數(shù)字' 為分庫標識最終分庫的名稱如：test_1、test_2、test_3 ...

在新增加庫的時候，我們在新庫中創(chuàng)建的表的數(shù)量是在系統(tǒng)設置表中的最大分表數(shù)。如在系統(tǒng)設置表中 name='max_sharding_table_num' 的 value='10'，這時我們會初始化每個分表的個數(shù)為10個。

數(shù)據(jù)遷移

和分表一樣我們應該很清楚哪些表是需要進行分庫，我們需要分庫的表有 buy_order_n、goods_n、sell_order_n、order_goods_n。

我們應該將之前的數(shù)據(jù)的庫名進行統(tǒng)一。如之前test庫的數(shù)據(jù)要先遷移到 test_1 上

提醒：數(shù)據(jù)遷移慎重，不是說遷移就遷移的。其實也可以不用遷移的，如果不遷移之后的自動分庫的代碼就需要做多一點的判斷。這為了統(tǒng)一我就做了遷移。

數(shù)據(jù)遷移SQL

  --?創(chuàng)建新庫  CREATE?DATABASE?test_1;  use?test; --?拼出需要創(chuàng)建的表  SELECT?CONCAT('CREATE?TABLE?test_1.',   ??TABLE_NAME,   ??'?LIKE?',   ??TABLE_SCHEMA,?'.',?TABLE_NAME,?';'  )??????????????  FROM?information_schema.tables   WHERE?TABLE_SCHEMA?=?'test';  --?創(chuàng)建表這邊我們不遷移公用的表:user、store、user_guide、system_setting  CREATE?TABLE?test_1.buy_order_1?LIKE?test.buy_order_1;   CREATE?TABLE?test_1.buy_order_10?LIKE?test.buy_order_10;   CREATE?TABLE?test_1.buy_order_2?LIKE?test.buy_order_2;   CREATE?TABLE?test_1.buy_order_3?LIKE?test.buy_order_3;   CREATE?TABLE?test_1.buy_order_4?LIKE?test.buy_order_4;   CREATE?TABLE?test_1.buy_order_5?LIKE?test.buy_order_5;   CREATE?TABLE?test_1.buy_order_6?LIKE?test.buy_order_6;   CREATE?TABLE?test_1.buy_order_7?LIKE?test.buy_order_7;   CREATE?TABLE?test_1.buy_order_8?LIKE?test.buy_order_8;   CREATE?TABLE?test_1.buy_order_9?LIKE?test.buy_order_9;   CREATE?TABLE?test_1.goods_1?LIKE?test.goods_1;   CREATE?TABLE?test_1.goods_10?LIKE?test.goods_10;   CREATE?TABLE?test_1.goods_2?LIKE?test.goods_2;   CREATE?TABLE?test_1.goods_3?LIKE?test.goods_3;   CREATE?TABLE?test_1.goods_4?LIKE?test.goods_4;   CREATE?TABLE?test_1.goods_5?LIKE?test.goods_5;   CREATE?TABLE?test_1.goods_6?LIKE?test.goods_6;   CREATE?TABLE?test_1.goods_7?LIKE?test.goods_7;   CREATE?TABLE?test_1.goods_8?LIKE?test.goods_8;   CREATE?TABLE?test_1.goods_9?LIKE?test.goods_9;   CREATE?TABLE?test_1.order_goods_1?LIKE?test.order_goods_1;   CREATE?TABLE?test_1.order_goods_10?LIKE?test.order_goods_10;   CREATE?TABLE?test_1.order_goods_2?LIKE?test.order_goods_2;   CREATE?TABLE?test_1.order_goods_3?LIKE?test.order_goods_3;   CREATE?TABLE?test_1.order_goods_4?LIKE?test.order_goods_4;   CREATE?TABLE?test_1.order_goods_5?LIKE?test.order_goods_5;   CREATE?TABLE?test_1.order_goods_6?LIKE?test.order_goods_6;   CREATE?TABLE?test_1.order_goods_7?LIKE?test.order_goods_7;   CREATE?TABLE?test_1.order_goods_8?LIKE?test.order_goods_8;   CREATE?TABLE?test_1.order_goods_9?LIKE?test.order_goods_9;   CREATE?TABLE?test_1.sell_order_1?LIKE?test.sell_order_1;   CREATE?TABLE?test_1.sell_order_10?LIKE?test.sell_order_10;   CREATE?TABLE?test_1.sell_order_2?LIKE?test.sell_order_2;   CREATE?TABLE?test_1.sell_order_3?LIKE?test.sell_order_3;   CREATE?TABLE?test_1.sell_order_4?LIKE?test.sell_order_4;   CREATE?TABLE?test_1.sell_order_5?LIKE?test.sell_order_5;   CREATE?TABLE?test_1.sell_order_6?LIKE?test.sell_order_6;   CREATE?TABLE?test_1.sell_order_7?LIKE?test.sell_order_7;   CREATE?TABLE?test_1.sell_order_8?LIKE?test.sell_order_8;   CREATE?TABLE?test_1.sell_order_9?LIKE?test.sell_order_9;  --?生成插入表的數(shù)據(jù)  SELECT?CONCAT('INSERT?INTO?',   ??TABLE_SCHEMA,?'.',?TABLE_NAME,   ??'?SELECT?*?FROM?test',?'.',?TABLE_NAME,?';'  )??????????????  FROM?information_schema.tables   WHERE?TABLE_SCHEMA?=?'test_1';  --?插入數(shù)據(jù)  INSERT?INTO?test_1.buy_order_1?SELECT?*?FROM?test.buy_order_1;   INSERT?INTO?test_1.buy_order_10?SELECT?*?FROM?test.buy_order_10;   INSERT?INTO?test_1.buy_order_2?SELECT?*?FROM?test.buy_order_2;   INSERT?INTO?test_1.buy_order_3?SELECT?*?FROM?test.buy_order_3;   INSERT?INTO?test_1.buy_order_4?SELECT?*?FROM?test.buy_order_4;   INSERT?INTO?test_1.buy_order_5?SELECT?*?FROM?test.buy_order_5;   INSERT?INTO?test_1.buy_order_6?SELECT?*?FROM?test.buy_order_6;   INSERT?INTO?test_1.buy_order_7?SELECT?*?FROM?test.buy_order_7;   INSERT?INTO?test_1.buy_order_8?SELECT?*?FROM?test.buy_order_8;   INSERT?INTO?test_1.buy_order_9?SELECT?*?FROM?test.buy_order_9;   INSERT?INTO?test_1.goods_1?SELECT?*?FROM?test.goods_1;   INSERT?INTO?test_1.goods_10?SELECT?*?FROM?test.goods_10;   INSERT?INTO?test_1.goods_2?SELECT?*?FROM?test.goods_2;   INSERT?INTO?test_1.goods_3?SELECT?*?FROM?test.goods_3;   INSERT?INTO?test_1.goods_4?SELECT?*?FROM?test.goods_4;   INSERT?INTO?test_1.goods_5?SELECT?*?FROM?test.goods_5;   INSERT?INTO?test_1.goods_6?SELECT?*?FROM?test.goods_6;   INSERT?INTO?test_1.goods_7?SELECT?*?FROM?test.goods_7;   INSERT?INTO?test_1.goods_8?SELECT?*?FROM?test.goods_8;   INSERT?INTO?test_1.goods_9?SELECT?*?FROM?test.goods_9;   INSERT?INTO?test_1.order_goods_1?SELECT?*?FROM?test.order_goods_1;   INSERT?INTO?test_1.order_goods_10?SELECT?*?FROM?test.order_goods_10;   INSERT?INTO?test_1.order_goods_2?SELECT?*?FROM?test.order_goods_2;   INSERT?INTO?test_1.order_goods_3?SELECT?*?FROM?test.order_goods_3;   INSERT?INTO?test_1.order_goods_4?SELECT?*?FROM?test.order_goods_4;   INSERT?INTO?test_1.order_goods_5?SELECT?*?FROM?test.order_goods_5;   INSERT?INTO?test_1.order_goods_6?SELECT?*?FROM?test.order_goods_6;   INSERT?INTO?test_1.order_goods_7?SELECT?*?FROM?test.order_goods_7;   INSERT?INTO?test_1.order_goods_8?SELECT?*?FROM?test.order_goods_8;   INSERT?INTO?test_1.order_goods_9?SELECT?*?FROM?test.order_goods_9;   INSERT?INTO?test_1.sell_order_1?SELECT?*?FROM?test.sell_order_1;   INSERT?INTO?test_1.sell_order_10?SELECT?*?FROM?test.sell_order_10;   INSERT?INTO?test_1.sell_order_2?SELECT?*?FROM?test.sell_order_2;   INSERT?INTO?test_1.sell_order_3?SELECT?*?FROM?test.sell_order_3;   INSERT?INTO?test_1.sell_order_4?SELECT?*?FROM?test.sell_order_4;   INSERT?INTO?test_1.sell_order_5?SELECT?*?FROM?test.sell_order_5;   INSERT?INTO?test_1.sell_order_6?SELECT?*?FROM?test.sell_order_6;   INSERT?INTO?test_1.sell_order_7?SELECT?*?FROM?test.sell_order_7;   INSERT?INTO?test_1.sell_order_8?SELECT?*?FROM?test.sell_order_8;   INSERT?INTO?test_1.sell_order_9?SELECT?*?FROM?test.sell_order_9;   ?? --?向系統(tǒng)表中添加當前最大分庫數(shù)量  INSERT?INTO?test.system_setting   VALUES(NULL,?'max_sharding_database_num',?1);  --?向系統(tǒng)表中添加分庫名前綴  INSERT?INTO?test.system_setting   VALUES(NULL,?'sharding_database_prefix',?'test');  --?向系統(tǒng)表中添加當前有哪些分庫  INSERT?INTO?test.system_setting   VALUES(NULL,?'sharding_database',?'test_1');  --?修改系統(tǒng)表字段類value型為varchar(120)  ALTER?TABLE?test.system_setting   MODIFY?`value`?varchar(120)?NOT?NULL?COMMENT?'系統(tǒng)設置值';  --?向系統(tǒng)表添加響應數(shù)據(jù)庫鏈接描述符  INSERT?INTO?test.system_setting   VALUES(NULL,?'test_1',?'{"user":"root","password":"root","host":"127.0.0.1","port":3306,"database":"test_1"}');   ?? --?初始化用戶所在庫為test_1  ALTER?TABLE?user   ADD?db_name?VARCHAR(45)?NOT?NULL?DEFAULT?'test_1'   COMMENT?'用戶數(shù)據(jù)所在數(shù)據(jù)庫名';   

文章出自:http://www.ttlsa.com/mysql/mysql-distributed-database-and-table-prepare/

? ? ? 本文轉(zhuǎn)自027ryan ?51CTO博客，原文鏈接：http://blog.51cto.com/ucode/1746032，如需轉(zhuǎn)載請自行聯(lián)系原作者

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎勵來咯，堅持創(chuàng)作打卡瓜分現(xiàn)金大獎

總結(jié)

以上是生活随笔為你收集整理的MySQL分库分表分库准备(6th)的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。