帝國CMS多少數(shù)據(jù)分表好？已發(fā)布的數(shù)據(jù)怎么平均分表？

文章編號：518 / 更新時間：2025-03-08 15:07:07 / 瀏覽：次

帝國CMS 多少數(shù)據(jù)分表好？
1、數(shù)據(jù)庫50G，應(yīng)新建主表；

2、滿5W條數(shù)據(jù)新建副表，并將最新建的副表設(shè)為當前存放表；【也有建議10W條數(shù)據(jù)分一次表的】

數(shù)據(jù)量過大，已導致MYSQL數(shù)據(jù)對IO的操作讀寫量巨大，導致整個服務(wù)器負載過大。

特別是帝國cms后臺操作緩慢，尤其是數(shù)據(jù)量大的欄目，本人的站點沒分表前，只要在后臺點擊數(shù)據(jù)量大的欄目時，打開非常慢，服務(wù)器負載瞬間100%，但是內(nèi)存和cpu均在20%左右（2核心，4G內(nèi)存，20M帶寬）

已發(fā)布的數(shù)據(jù)如何進行分表操作？
已經(jīng)發(fā)布的數(shù)據(jù)，比如一個數(shù)據(jù)表數(shù)據(jù)200W文章，如何平均把這些文章分配到20個數(shù)據(jù)分表里呢？一個表10w數(shù)據(jù)！

很簡單，直接打開數(shù)據(jù)庫操作，這里演示將第二個5w數(shù)據(jù)移動到新建立的數(shù)據(jù)表！

請先創(chuàng)建副表！

第一步，復(fù)制副表_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;

第二步，修改主表的數(shù)據(jù)，使之指引到副表_2

update phome_ecms_news set stb=REPLACE(stb,1,2)
WHERE ID>= 50001 AND ID<= 100000;

第三步，刪除副表_1中已經(jīng)移動完成的數(shù)據(jù)

DELETE FROM phome_ecms_news_data_1
WHERE ID>= 50001 AND ID<= 100000;

帝國cms百萬數(shù)據(jù)如何平均分配到分表
sql一個一個寫也是挺慢的，所以好心的鄙人給大家寫好了！

#---------------------------第1組分表SQL------------------------------
#副表_2數(shù)據(jù)遷移，遷移50001到100000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_2
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 50001 AND T2.ID<= 100000;

#更新主表與副表_2的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,2)
WHERE ID>= 50001 AND ID<= 100000;

#刪除副表_2中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 50001 AND ID<= 100000;

#---------------------------第2組分表SQL------------------------------
#副表_3數(shù)據(jù)遷移，遷移100001到150000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_3
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 100001 AND T2.ID<= 150000;

#更新主表與副表_3的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,3)
WHERE ID>= 100001 AND ID<= 150000;

#刪除副表_3中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 100001 AND ID<= 150000;

#---------------------------第3組分表SQL------------------------------
#副表_4數(shù)據(jù)遷移，遷移150001到200000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_4
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 150001 AND T2.ID<= 200000;

#更新主表與副表_4的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,4)
WHERE ID>= 150001 AND ID<= 200000;

#刪除副表_4中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 150001 AND ID<= 200000;

#---------------------------第4組分表SQL------------------------------
#副表_5數(shù)據(jù)遷移，遷移200001到250000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_5
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 200001 AND T2.ID<= 250000;

#更新主表與副表_5的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,5)
WHERE ID>= 200001 AND ID<= 250000;

#刪除副表_5中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 200001 AND ID<= 250000;

#---------------------------第5組分表SQL------------------------------
#副表_6數(shù)據(jù)遷移，遷移250001到300000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_6
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 250001 AND T2.ID<= 300000;

#更新主表與副表_6的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,6)
WHERE ID>= 250001 AND ID<= 300000;

#刪除副表_6中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 250001 AND ID<= 300000;

#---------------------------第6組分表SQL------------------------------
#副表_7數(shù)據(jù)遷移，遷移300001到350000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_7
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 300001 AND T2.ID<= 350000;

#更新主表與副表_7的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,7)
WHERE ID>= 300001 AND ID<= 350000;

#刪除副表_7中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 300001 AND ID<= 350000;

#---------------------------第7組分表SQL------------------------------
#副表_8數(shù)據(jù)遷移，遷移350001到400000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_8
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 350001 AND T2.ID<= 400000;

#更新主表與副表_8的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,8)
WHERE ID>= 350001 AND ID<= 400000;

#刪除副表_8中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 350001 AND ID<= 400000;

#---------------------------第8組分表SQL------------------------------
#副表_9數(shù)據(jù)遷移，遷移400001到450000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_9
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 400001 AND T2.ID<= 450000;

#更新主表與副表_9的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,9)
WHERE ID>= 400001 AND ID<= 450000;

#刪除副表_9中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 400001 AND ID<= 450000;

#---------------------------第9組分表SQL------------------------------
#副表_10數(shù)據(jù)遷移，遷移450001到500000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_10
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 450001 AND T2.ID<= 500000;

#更新主表與副表_10的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,10)
WHERE ID>= 450001 AND ID<= 500000;

#刪除副表_10中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 450001 AND ID<= 500000;

#---------------------------第10組分表SQL------------------------------
#副表_11數(shù)據(jù)遷移，遷移500001到550000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_11
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 500001 AND T2.ID<= 550000;

#更新主表與副表_11的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,11)
WHERE ID>= 500001 AND ID<= 550000;

#刪除副表_11中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 500001 AND ID<= 550000;

#---------------------------第11組分表SQL------------------------------
#副表_12數(shù)據(jù)遷移，遷移550001到600000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_12
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 550001 AND T2.ID<= 600000;

#更新主表與副表_12的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,12)
WHERE ID>= 550001 AND ID<= 600000;

#刪除副表_12中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 550001 AND ID<= 600000;

#---------------------------第12組分表SQL------------------------------
#副表_13數(shù)據(jù)遷移，遷移600001到650000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_13
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 600001 AND T2.ID<= 650000;

#更新主表與副表_13的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,13)
WHERE ID>= 600001 AND ID<= 650000;

#刪除副表_13中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 600001 AND ID<= 650000;

#---------------------------第13組分表SQL------------------------------
#副表_14數(shù)據(jù)遷移，遷移650001到700000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_14
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 650001 AND T2.ID<= 700000;

#更新主表與副表_14的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,14)
WHERE ID>= 650001 AND ID<= 700000;

#刪除副表_14中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 650001 AND ID<= 700000;

#---------------------------第14組分表SQL------------------------------
#副表_15數(shù)據(jù)遷移，遷移700001到750000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_15
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 700001 AND T2.ID<= 750000;

#更新主表與副表_15的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,15)
WHERE ID>= 700001 AND ID<= 750000;

#刪除副表_15中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 700001 AND ID<= 750000;

#---------------------------第15組分表SQL------------------------------
#副表_16數(shù)據(jù)遷移，遷移750001到800000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_16
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 750001 AND T2.ID<= 800000;

#更新主表與副表_16的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,16)
WHERE ID>= 750001 AND ID<= 800000;

#刪除副表_16中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 750001 AND ID<= 800000;

#---------------------------第16組分表SQL------------------------------
#副表_17數(shù)據(jù)遷移，遷移800001到850000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_17
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 800001 AND T2.ID<= 850000;

#更新主表與副表_17的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,17)
WHERE ID>= 800001 AND ID<= 850000;

#刪除副表_17中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 800001 AND ID<= 850000;

#---------------------------第17組分表SQL------------------------------
#副表_18數(shù)據(jù)遷移，遷移850001到900000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_18
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 850001 AND T2.ID<= 900000;

#更新主表與副表_18的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,18)
WHERE ID>= 850001 AND ID<= 900000;

#刪除副表_18中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 850001 AND ID<= 900000;

#---------------------------第18組分表SQL------------------------------
#副表_19數(shù)據(jù)遷移，遷移900001到950000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_19
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 900001 AND T2.ID<= 950000;

#更新主表與副表_19的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,19)
WHERE ID>= 900001 AND ID<= 950000;

#刪除副表_19中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 900001 AND ID<= 950000;

#---------------------------第19組分表SQL------------------------------
#副表_20數(shù)據(jù)遷移，遷移950001到1000000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_20
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 950001 AND T2.ID<= 1000000;

#更新主表與副表_20的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,20)
WHERE ID>= 950001 AND ID<= 1000000;

#刪除副表_20中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 950001 AND ID<= 1000000;

#---------------------------第20組分表SQL------------------------------
#副表_21數(shù)據(jù)遷移，遷移1000001到1050000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_21
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1000001 AND T2.ID<= 1050000;

#更新主表與副表_21的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,21)
WHERE ID>= 1000001 AND ID<= 1050000;

#刪除副表_21中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1000001 AND ID<= 1050000;

#---------------------------第21組分表SQL------------------------------
#副表_22數(shù)據(jù)遷移，遷移1050001到1100000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_22
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1050001 AND T2.ID<= 1100000;

#更新主表與副表_22的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,22)
WHERE ID>= 1050001 AND ID<= 1100000;

#刪除副表_22中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1050001 AND ID<= 1100000;

#---------------------------第22組分表SQL------------------------------
#副表_23數(shù)據(jù)遷移，遷移1100001到1150000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_23
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1100001 AND T2.ID<= 1150000;

#更新主表與副表_23的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,23)
WHERE ID>= 1100001 AND ID<= 1150000;

#刪除副表_23中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1100001 AND ID<= 1150000;

#---------------------------第23組分表SQL------------------------------
#副表_24數(shù)據(jù)遷移，遷移1150001到1200000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_24
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1150001 AND T2.ID<= 1200000;

#更新主表與副表_24的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,24)
WHERE ID>= 1150001 AND ID<= 1200000;

#刪除副表_24中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1150001 AND ID<= 1200000;

#---------------------------第24組分表SQL------------------------------
#副表_25數(shù)據(jù)遷移，遷移1200001到1250000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_25
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1200001 AND T2.ID<= 1250000;

#更新主表與副表_25的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,25)
WHERE ID>= 1200001 AND ID<= 1250000;

#刪除副表_25中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1200001 AND ID<= 1250000;

#---------------------------第25組分表SQL------------------------------
#副表_26數(shù)據(jù)遷移，遷移1250001到1300000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_26
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1250001 AND T2.ID<= 1300000;

#更新主表與副表_26的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,26)
WHERE ID>= 1250001 AND ID<= 1300000;

#刪除副表_26中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1250001 AND ID<= 1300000;

#---------------------------第26組分表SQL------------------------------
#副表_27數(shù)據(jù)遷移，遷移1300001到1350000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_27
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1300001 AND T2.ID<= 1350000;

#更新主表與副表_27的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,27)
WHERE ID>= 1300001 AND ID<= 1350000;

#刪除副表_27中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1300001 AND ID<= 1350000;

#---------------------------第27組分表SQL------------------------------
#副表_28數(shù)據(jù)遷移，遷移1350001到1400000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_28
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1350001 AND T2.ID<= 1400000;

#更新主表與副表_28的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,28)
WHERE ID>= 1350001 AND ID<= 1400000;

#刪除副表_28中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1350001 AND ID<= 1400000;

#---------------------------第28組分表SQL------------------------------
#副表_29數(shù)據(jù)遷移，遷移1400001到1450000之間的數(shù)據(jù)
INSERT INTO phome_ecms_news_data_29
SELECT * FROM phome_ecms_news_data_1 T2
WHERE T2.ID>= 1400001 AND T2.ID<= 1450000;

#更新主表與副表_29的關(guān)系
update phome_ecms_news set stb=REPLACE(stb,1,29)
WHERE ID>= 1400001 AND ID<= 1450000;

#刪除副表_29中已經(jīng)移動成功的數(shù)據(jù)
DELETE FROM phome_ecms_news_data_1
WHERE ID>= 1400001 AND ID<= 1450000;
Select CodeCopy

這里加一個PHP生成SQL的腳本，可以批量生成上面的SQL語句。

說真的，手寫得累死！

相關(guān)標簽：谷歌SEO、外鏈論壇、外鏈發(fā)布、外貿(mào)推廣、外鏈網(wǎng)站、軟文發(fā)布、外鏈平臺、

本文地址：http://www.schoolwires.net.cn/article-518.html

上一篇：seo搜索引擎優(yōu)化實戰(zhàn)...
下一篇：2025第十三屆江門先進制造業(yè)博覽會...