当前位置：
  1. 首页
  2. 建站教程
  3. 帝国cms教程
  4. 正文

帝国CMS 多少数据分表好？已发布的数据如何进行分表操作？帝国cms百万数据如何平均分配到分表？

帝国cms教程 0 42

帝国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语句。

***此处内容评论后可见***

温馨提示：此处为隐藏内容，需要评论或回复留言后可见

评论查看
上一篇 帝国cms 滚动加载更多文章
下一篇 自用帝国CMS高效随机调用

相关文章

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

插入图片
浏览器会保存昵称、邮箱和网站cookies信息，下次评论时使用。

评论(1)