文章ID:518時間:2025-03-08人氣:
| 帝國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; Select CodeCopy 這里加一個PHP生成SQL的腳本,可以批量生成上面的SQL語句。 說真的,手寫得累死! |
內容聲明:1、本站收錄的內容來源于大數據收集,版權歸原網站所有!
2、本站收錄的內容若侵害到您的利益,請聯系我們進行刪除處理!
3、本站不接受違規信息,如您發現違規內容,請聯系我們進行清除處理!
4、本文地址:http://www.5052-h112.com.cn/article-518.html,復制請保留版權鏈接!
手機容量告急,怎么辦?筆記本電腦硬盤不夠用,怎么辦?高質量的創作內容和大型軟件、游戲帶來的存儲壓力日益增加,手機中的照片、視頻占據...
2023-12-14 18:27:08
我們在聽歌的時候,會聽到一些好聽的英文mp3歌曲,但是有些歌詞的意思我們聽不懂或是不理解,其實面對這種問題,我們可以將這些mp3...
2023-12-01 18:30:44
隨著時間的推移,技術的更新迭代勢不可擋。在經過13次維護版本更新之后,Kernel.org官網正式將LinuxKernel6.5標記為EOL(生命周期...
2023-12-01 18:30:25
歐洲是全球品牌的高地,這是中國出海企業的共識。巨大的經濟規模和較高的人均收入,使其成為高端的代名詞,也是高端品牌的必爭之地,在歐洲...
2023-12-01 18:30:15
據《紐約時報》報道,由于數十個主要品牌暫停投放廣告,馬斯克旗下X平臺到今年年底可能會損失高達7500萬美元(IT之家備注:當前約5.36億元人民幣)的廣告收入。 馬斯克此前發表反猶太主義言論,導致包括華特迪士尼和華納兄弟探索在內的多家公司暫停在X投放廣告。據多家新聞媒體報道,蘋果也撤回了在X上的廣告支出。
2023-11-25 22:49:18
SSL負責在計算機間對整個會話進行加密處理,能有效地對用戶的個人信息進行安全防護。在SSL的加密過程中,結合使用了公鑰和私鑰兩種密鑰。本文狂人SEO詳細講下SSL是什么意思?域名SSL證書的作用是什么?一、SSL是什么意思SSL英文全稱是“SecureSocketsLayer”,中文含義為“安全...
2023-09-17 17:21:58
9月13日,全球權威商業媒體《財富》中國發布了“2023年中國最具社會影響力的65家創業公司”榜單,店匠科技以創新科技實力和商業模式成功入選,共同上榜的還有In
2023-09-15 19:57:21