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