最新公告
  • 欢迎您光临站长源码网，本站秉承服务宗旨 履行“站长”责任，销售只是起点 服务永无止境！立即加入钻石VIP
    • 10年
    专注帝国cms
    升级SVIP
    升级SVIP尊享更多特权立即升级
    账号登录
    QQ登录
    首页
    当前位置：站长学院 > 综合教程 > 帝国CMS 多少数据分表好？已发布的数据怎么分表？帝国cms百万数据怎么平均分表？

    帝国CMS 多少数据分表好？已发布的数据怎么分表？帝国cms百万数据怎么平均分表？

    正文概述 管理员   2024-09-02   11

    帝国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百万数据怎么平均分表？

    常见问题FAQ

    免费下载或者VIP会员专享资源能否直接商用？
    本站所有资源版权均属于原作者所有，这里所提供资源均只能用于参考学习用，请勿直接商用。若由于商用引起版权纠纷，一切责任均由使用者承担。更多说明请参考 VIP介绍。
    提示下载完但解压或打开不了？
    最常见的情况是下载不完整: 可对比下载完压缩包的与网盘上的容量，若小于网盘提示的容量则是这个原因。这是浏览器下载的bug，建议用百度网盘软件或迅雷下载。若排除这种情况，可在对应资源底部留言，或 联络我们.。
    找不到素材资源介绍文章里的示例图片？
    对于PPT，KEY，Mockups，APP，网页模版等类型的素材，文章内用于介绍的图片通常并不包含在对应可供下载素材包内。这些相关商业图片需另外购买，且本站不负责(也没有办法)找到出处。 同样地一些字体文件也是这种情况，但部分素材会在素材包内有一份字体下载链接清单。
    模板不会安装或需要功能定制以及二次开发？
    请QQ联系我们

    管理员 SVIP

    分享到：

    相关推荐

    发表评论

    如需帝国cms功能定制以及二次开发请联系我们

    联系作者

    请选择支付方式

    ×
    支付宝支付
    余额支付
    ×
    微信扫码支付 0 元