mysql 3200萬資料，最佳化分頁查詢

標籤：

my.ini參數修改了下

 

Java代碼  

1. table_cache=512  
2. bulk_insert_buffer_size = 100M  
3. innodb_additional_mem_pool_size=30M  
4. innodb_flush_log_at_trx_commit=0  
5. innodb_buffer_pool_size=207M  
6. innodb_log_file_size=128M  

 innodb_flush_log_at_trx_commit預設值1的意思是每一次事務提交或事務外的指令都需要把日誌寫入（flush）硬碟，這是很費時的。特別是使用電 池供電緩衝（Battery backed up cache）時。設成2對於很多運用，特別是從MyISAM錶轉過來的是可以的，它的意思是不寫入硬碟而是寫入系統緩衝。日誌仍然會每秒flush到硬 盤，所以你一般不會丟失超過1-2秒的更新。設成0會更快一點，但安全方面比較差，即使MySQL掛了也可能會丟失事務的資料。而值2隻會在整個作業系統 掛了時才可能丟資料。對於事務要求很強，設定為0 是存在安全問題的

mysql建立表

Sql代碼  

1. CREATE TABLE `news` (  
2.   `id` int(19) NOT NULL AUTO_INCREMENT,  
3.   `title` varchar(30) DEFAULT NULL,  
4.   `content` varchar(400) DEFAULT NULL,  
5.   `type` varchar(30) DEFAULT NULL,  
6.   PRIMARY KEY (`id`),  
7.   UNIQUE KEY `PK_NEWS_ID` (`id`),  
8.   KEY `INDEX_NEWS_ID_TYPE` (`id`,`type`),  
9.   KEY `INDEX_NEWS_TYPE` (`type`)  
10. ) ENGINE=InnoDB AUTO_INCREMENT=1072779 DEFAULT CHARSET=utf8  

java插入測試資料代碼放到文章最後面

mysql5.5 支援 insert into mytable value (xxx,xxx....),(xxx,xxx....)...........插入多條記錄，相比addBatch 好不到哪裡，而且mysql資料包有限制，超大字串對JVM來說也不好 

 測試結果300萬插入只需要597秒

查詢隨著select欄位增多會消耗更多時間，limit a,b 也會隨著a的增大而加大查詢時間。

現在資料已經添加到了至少3200萬條資料，

 

 幾千萬資料中只需0.001ms ， in的速度是驚人的， 最主要也是因為加了索引，

 

 可見複合索引帶來效能的優勢

這個表大約是三千多萬條記錄，綜合一下2個最重要的速度最快查詢 就是分頁的查詢語句

 

大表查詢總結： 1 複合索引好好使用 2   in 要好好使用

上面的都是傳統分頁的，分頁做下改進

1   當頁面傳到controller層有一個page對象，代表要查詢的頁數，那麼我們的sql可以隨著變化

start=(page-1)*pagesize+1  然後是where做限制 就是where加上 id >= start

如果表中增加了一個type=‘ios8‘ 因為ios8 的資料是從三千多萬多條開始的，假設是32888888條記錄，後面才增加type=‘ios8‘的記錄，那麼分頁就可以加上   id >= start + 32888888

2   對於新聞類，老的資料分頁是固定的，所以可以分表，新聞就要新聞

所以最新的新聞的分頁好辦，表裡面弄幾百頁就夠了，其他的資料放到一個老資料表裡面，老資料表的儲存引擎改成MyISAM

比如前500頁就查詢news的資料，後面的就查詢oldnews1，oldnews2.....表的資料，對old表做一下策略每個old表的一種分類只允許有100000頁的資料。

假設oldnew1表，是資料裡面最先入進去的，是知道id的範圍的，但是oldnews1表分頁的頁數是會變的

我們在老資料表每個表裡面都增加一個page欄位，儲存頁數，由於頁數是會變動的，所以我們需要頁碼欄位和資料倒著來，那麼插入的時候就不會改動前面的頁碼的，我們知道有多少個老表。假設有10個老表

limit a,b,那麼a就會大於 (news表的總頁數*pagesize-news表的總條數+ 9*100000*pagesize)，查詢頁碼的時候就需要-100000+1，因為頁碼欄位是倒著來的. 

3  上面分表+頁碼策略做的話效能會明顯的提升很多，但是表中有大欄位而且欄位超多始終會對效能產生影響，老新聞是不變的，可以做靜態化處理，我們新增一個路徑表urloldnew1,.....，只給一個id,page,url即可,查詢的時候就用這樣就可以讓表的資料欄位大大減少，不去查詢老的未經處理資料表。

url是產生的靜態檔案地址，這需要靜態化的時候進行二次加密，根據檔案路徑產生 加密碼，然後根據加密碼產生靜態路徑。

4   下面對單條新聞設計。

查詢單條新聞：

這是經過加密的，後台要解密前台傳遞過來的字串

如jkser896 _  89hhgii  _  oiy67hjk 

根據欄位表再次解密 java開發人員     _        /news/old8/      1243546678

這樣拼接路徑就成了。

最後把java插入測試資料放到附件裡

擷取【】 java後台架構源碼 springmvc mybatis

mysql 3200萬資料，最佳化分頁查詢