帝國CMS 多少數據分表好?
1、數據庫50G,應新建主表;
2、滿5W條數據新建副表,並將最新建的副表設爲當前存放表;【也有建議10W條數據分一次表的】
數據量過大,已導致MYSQL數據對IO的操作讀寫量巨大,導致整個服務器負載過大。
特別是帝國cms後臺操作緩慢,尤其是數據量大的欄目,本人的站點沒分表前,只要在後臺點擊數據量大的欄目時,打開非常慢,服務器負載瞬間100%,但是內存和cpu均在20%左右(2核心,4G內存,20M帶寬)
已發佈的數據如何進行分表操作?
已經發布的數據,比如一個數據表數據200W文章,如何平均把這些文章分配到20個數據分表裏呢?一個表10w數據!
很簡單,直接打開數據庫操作,這裏演示將第二個5w數據移動到新建立的數據表!
請先創建副表!
第一步,複製副表_1到副表_2
INSERT INTO phome_ecms_news_data_2
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 50001 AND T2.ID<= 100000;第二步,修改主表的數據,使之指引到副表_2
update phome_ecms_news set stb=REPLACE(stb,1,2)
WHERE ID>= 50001 AND ID<= 100000;第三步,刪除副表_1中已經移動完成的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 50001 AND ID<= 100000;帝國cms百萬數據如何平均分配到分表
sql一個一個寫也是挺慢的,所以好心的鄙人給大家寫好了!
#---------------------------第1組分表SQL------------------------------
#副表_2數據遷移,遷移50001到100000之間的數據
INSERT INTO phome_ecms_news_data_2
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 50001 AND T2.ID<= 100000;
#更新主表與副表_2的關係
update phome_ecms_news set stb=REPLACE(stb,1,2)
WHERE ID>= 50001 AND ID<= 100000;
#刪除副表_2中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 50001 AND ID<= 100000;
#---------------------------第2組分表SQL------------------------------
#副表_3數據遷移,遷移100001到150000之間的數據
INSERT INTO phome_ecms_news_data_3
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 100001 AND T2.ID<= 150000;
#更新主表與副表_3的關係
update phome_ecms_news set stb=REPLACE(stb,1,3)
WHERE ID>= 100001 AND ID<= 150000;
#刪除副表_3中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 100001 AND ID<= 150000;
#---------------------------第3組分表SQL------------------------------
#副表_4數據遷移,遷移150001到200000之間的數據
INSERT INTO phome_ecms_news_data_4
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 150001 AND T2.ID<= 200000;
#更新主表與副表_4的關係
update phome_ecms_news set stb=REPLACE(stb,1,4)
WHERE ID>= 150001 AND ID<= 200000;
#刪除副表_4中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 150001 AND ID<= 200000;
#---------------------------第4組分表SQL------------------------------
#副表_5數據遷移,遷移200001到250000之間的數據
INSERT INTO phome_ecms_news_data_5
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 200001 AND T2.ID<= 250000;
#更新主表與副表_5的關係
update phome_ecms_news set stb=REPLACE(stb,1,5)
WHERE ID>= 200001 AND ID<= 250000;
#刪除副表_5中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 200001 AND ID<= 250000;
#---------------------------第5組分表SQL------------------------------
#副表_6數據遷移,遷移250001到300000之間的數據
INSERT INTO phome_ecms_news_data_6
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 250001 AND T2.ID<= 300000;
#更新主表與副表_6的關係
update phome_ecms_news set stb=REPLACE(stb,1,6)
WHERE ID>= 250001 AND ID<= 300000;
#刪除副表_6中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 250001 AND ID<= 300000;
#---------------------------第6組分表SQL------------------------------
#副表_7數據遷移,遷移300001到350000之間的數據
INSERT INTO phome_ecms_news_data_7
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 300001 AND T2.ID<= 350000;
#更新主表與副表_7的關係
update phome_ecms_news set stb=REPLACE(stb,1,7)
WHERE ID>= 300001 AND ID<= 350000;
#刪除副表_7中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 300001 AND ID<= 350000;
#---------------------------第7組分表SQL------------------------------
#副表_8數據遷移,遷移350001到400000之間的數據
INSERT INTO phome_ecms_news_data_8
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 350001 AND T2.ID<= 400000;
#更新主表與副表_8的關係
update phome_ecms_news set stb=REPLACE(stb,1,8)
WHERE ID>= 350001 AND ID<= 400000;
#刪除副表_8中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 350001 AND ID<= 400000;
#---------------------------第8組分表SQL------------------------------
#副表_9數據遷移,遷移400001到450000之間的數據
INSERT INTO phome_ecms_news_data_9
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 400001 AND T2.ID<= 450000;
#更新主表與副表_9的關係
update phome_ecms_news set stb=REPLACE(stb,1,9)
WHERE ID>= 400001 AND ID<= 450000;
#刪除副表_9中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 400001 AND ID<= 450000;
#---------------------------第9組分表SQL------------------------------
#副表_10數據遷移,遷移450001到500000之間的數據
INSERT INTO phome_ecms_news_data_10
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 450001 AND T2.ID<= 500000;
#更新主表與副表_10的關係
update phome_ecms_news set stb=REPLACE(stb,1,10)
WHERE ID>= 450001 AND ID<= 500000;
#刪除副表_10中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 450001 AND ID<= 500000;
#---------------------------第10組分表SQL------------------------------
#副表_11數據遷移,遷移500001到550000之間的數據
INSERT INTO phome_ecms_news_data_11
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 500001 AND T2.ID<= 550000;
#更新主表與副表_11的關係
update phome_ecms_news set stb=REPLACE(stb,1,11)
WHERE ID>= 500001 AND ID<= 550000;
#刪除副表_11中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 500001 AND ID<= 550000;
#---------------------------第11組分表SQL------------------------------
#副表_12數據遷移,遷移550001到600000之間的數據
INSERT INTO phome_ecms_news_data_12
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 550001 AND T2.ID<= 600000;
#更新主表與副表_12的關係
update phome_ecms_news set stb=REPLACE(stb,1,12)
WHERE ID>= 550001 AND ID<= 600000;
#刪除副表_12中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 550001 AND ID<= 600000;
#---------------------------第12組分表SQL------------------------------
#副表_13數據遷移,遷移600001到650000之間的數據
INSERT INTO phome_ecms_news_data_13
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 600001 AND T2.ID<= 650000;
#更新主表與副表_13的關係
update phome_ecms_news set stb=REPLACE(stb,1,13)
WHERE ID>= 600001 AND ID<= 650000;
#刪除副表_13中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 600001 AND ID<= 650000;
#---------------------------第13組分表SQL------------------------------
#副表_14數據遷移,遷移650001到700000之間的數據
INSERT INTO phome_ecms_news_data_14
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 650001 AND T2.ID<= 700000;
#更新主表與副表_14的關係
update phome_ecms_news set stb=REPLACE(stb,1,14)
WHERE ID>= 650001 AND ID<= 700000;
#刪除副表_14中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 650001 AND ID<= 700000;
#---------------------------第14組分表SQL------------------------------
#副表_15數據遷移,遷移700001到750000之間的數據
INSERT INTO phome_ecms_news_data_15
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 700001 AND T2.ID<= 750000;
#更新主表與副表_15的關係
update phome_ecms_news set stb=REPLACE(stb,1,15)
WHERE ID>= 700001 AND ID<= 750000;
#刪除副表_15中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 700001 AND ID<= 750000;
#---------------------------第15組分表SQL------------------------------
#副表_16數據遷移,遷移750001到800000之間的數據
INSERT INTO phome_ecms_news_data_16
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 750001 AND T2.ID<= 800000;
#更新主表與副表_16的關係
update phome_ecms_news set stb=REPLACE(stb,1,16)
WHERE ID>= 750001 AND ID<= 800000;
#刪除副表_16中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 750001 AND ID<= 800000;
#---------------------------第16組分表SQL------------------------------
#副表_17數據遷移,遷移800001到850000之間的數據
INSERT INTO phome_ecms_news_data_17
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 800001 AND T2.ID<= 850000;
#更新主表與副表_17的關係
update phome_ecms_news set stb=REPLACE(stb,1,17)
WHERE ID>= 800001 AND ID<= 850000;
#刪除副表_17中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 800001 AND ID<= 850000;
#---------------------------第17組分表SQL------------------------------
#副表_18數據遷移,遷移850001到900000之間的數據
INSERT INTO phome_ecms_news_data_18
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 850001 AND T2.ID<= 900000;
#更新主表與副表_18的關係
update phome_ecms_news set stb=REPLACE(stb,1,18)
WHERE ID>= 850001 AND ID<= 900000;
#刪除副表_18中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 850001 AND ID<= 900000;
#---------------------------第18組分表SQL------------------------------
#副表_19數據遷移,遷移900001到950000之間的數據
INSERT INTO phome_ecms_news_data_19
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 900001 AND T2.ID<= 950000;
#更新主表與副表_19的關係
update phome_ecms_news set stb=REPLACE(stb,1,19)
WHERE ID>= 900001 AND ID<= 950000;
#刪除副表_19中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 900001 AND ID<= 950000;
#---------------------------第19組分表SQL------------------------------
#副表_20數據遷移,遷移950001到1000000之間的數據
INSERT INTO phome_ecms_news_data_20
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 950001 AND T2.ID<= 1000000;
#更新主表與副表_20的關係
update phome_ecms_news set stb=REPLACE(stb,1,20)
WHERE ID>= 950001 AND ID<= 1000000;
#刪除副表_20中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 950001 AND ID<= 1000000;
#---------------------------第20組分表SQL------------------------------
#副表_21數據遷移,遷移1000001到1050000之間的數據
INSERT INTO phome_ecms_news_data_21
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1000001 AND T2.ID<= 1050000;
#更新主表與副表_21的關係
update phome_ecms_news set stb=REPLACE(stb,1,21)
WHERE ID>= 1000001 AND ID<= 1050000;
#刪除副表_21中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1000001 AND ID<= 1050000;
#---------------------------第21組分表SQL------------------------------
#副表_22數據遷移,遷移1050001到1100000之間的數據
INSERT INTO phome_ecms_news_data_22
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1050001 AND T2.ID<= 1100000;
#更新主表與副表_22的關係
update phome_ecms_news set stb=REPLACE(stb,1,22)
WHERE ID>= 1050001 AND ID<= 1100000;
#刪除副表_22中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1050001 AND ID<= 1100000;
#---------------------------第22組分表SQL------------------------------
#副表_23數據遷移,遷移1100001到1150000之間的數據
INSERT INTO phome_ecms_news_data_23
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1100001 AND T2.ID<= 1150000;
#更新主表與副表_23的關係
update phome_ecms_news set stb=REPLACE(stb,1,23)
WHERE ID>= 1100001 AND ID<= 1150000;
#刪除副表_23中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1100001 AND ID<= 1150000;
#---------------------------第23組分表SQL------------------------------
#副表_24數據遷移,遷移1150001到1200000之間的數據
INSERT INTO phome_ecms_news_data_24
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1150001 AND T2.ID<= 1200000;
#更新主表與副表_24的關係
update phome_ecms_news set stb=REPLACE(stb,1,24)
WHERE ID>= 1150001 AND ID<= 1200000;
#刪除副表_24中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1150001 AND ID<= 1200000;
#---------------------------第24組分表SQL------------------------------
#副表_25數據遷移,遷移1200001到1250000之間的數據
INSERT INTO phome_ecms_news_data_25
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1200001 AND T2.ID<= 1250000;
#更新主表與副表_25的關係
update phome_ecms_news set stb=REPLACE(stb,1,25)
WHERE ID>= 1200001 AND ID<= 1250000;
#刪除副表_25中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1200001 AND ID<= 1250000;
#---------------------------第25組分表SQL------------------------------
#副表_26數據遷移,遷移1250001到1300000之間的數據
INSERT INTO phome_ecms_news_data_26
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1250001 AND T2.ID<= 1300000;
#更新主表與副表_26的關係
update phome_ecms_news set stb=REPLACE(stb,1,26)
WHERE ID>= 1250001 AND ID<= 1300000;
#刪除副表_26中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1250001 AND ID<= 1300000;
#---------------------------第26組分表SQL------------------------------
#副表_27數據遷移,遷移1300001到1350000之間的數據
INSERT INTO phome_ecms_news_data_27
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1300001 AND T2.ID<= 1350000;
#更新主表與副表_27的關係
update phome_ecms_news set stb=REPLACE(stb,1,27)
WHERE ID>= 1300001 AND ID<= 1350000;
#刪除副表_27中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1300001 AND ID<= 1350000;
#---------------------------第27組分表SQL------------------------------
#副表_28數據遷移,遷移1350001到1400000之間的數據
INSERT INTO phome_ecms_news_data_28
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1350001 AND T2.ID<= 1400000;
#更新主表與副表_28的關係
update phome_ecms_news set stb=REPLACE(stb,1,28)
WHERE ID>= 1350001 AND ID<= 1400000;
#刪除副表_28中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1350001 AND ID<= 1400000;
#---------------------------第28組分表SQL------------------------------
#副表_29數據遷移,遷移1400001到1450000之間的數據
INSERT INTO phome_ecms_news_data_29
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1400001 AND T2.ID<= 1450000;
#更新主表與副表_29的關係
update phome_ecms_news set stb=REPLACE(stb,1,29)
WHERE ID>= 1400001 AND ID<= 1450000;
#刪除副表_29中已經移動成功的數據
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1400001 AND ID<= 1450000;這裏加一個PHP生成SQL的腳本,可以批量生成上面的SQL語句。
說真的,手寫得累死!