mysql數據庫千萬級別數據的查詢優化和分頁測試

原文：http://blog.sina.com.cn/s/blog_438308750100im0b.html 我原來的公司是一家網絡游戲公司,其中網站交易與游戲數據庫結合通過ws實現的,但是交易記錄存放在網站上,級別是千萬級別的數據庫是mysql數據庫. 可能有人會問mysql是否支持千萬級數據庫,還有既然

原文：http://blog.sina.com.cn/s/blog_438308750100im0b.html

我原來的公司是一家網絡游戲公司,其中網站交易與游戲數據庫結合通過ws實現的,但是交易記錄存放在網站上,級別是千萬級別的數據庫是mysql數據庫.

可能有人會問mysql是否支持千萬級數據庫,還有既然已經到了這個數據量公司肯定不差,為什么要用mysql而不用oracle這里我做一下解答
1. mysql絕對支持千萬級數據庫是可以肯定的，
2. 為什么選擇擇mysql呢？
1> 第一也是最主要的一條是mysql他能做到。
2> 在第一點前提下以下的就不是太重要了,mysql相對操作簡單,測試容易,配置優化也相對容易很多
3> 我們這里的數據僅僅是為了記錄交易保證交易是被記錄的,對于查詢的還是相對少只有管理后臺操作中需要對數據庫進行查詢
4> 數據結構簡單,而且每條記錄都非常小,因為查詢速度不管和記錄條數有關和數據文件大小也有直接關系.
5> 我們采用的是大小表的解決辦法,每天大概需要插入數據庫好幾百萬條,這里可能還是有人懷疑,其實沒問題,如果批量插入我測試的在普通的pc機子上帶該一個 線程并發我插入的是6千萬條記錄大概需要“JDBC插入6000W條數據用時:9999297ms”,小表保存最近插入的內容,把幾天前的保存到大表中, 這里我說的就是大表大概6-7千萬條數據;

帶著這些疑問和求知欲望咱們來做一個測試，因為在那個時候我也不是dba不知道人家是怎么搞的能夠做成這么大的數據量,我們平時葉總探討一些相關的內容

1.mysql的數據查詢,大小字段要分開,這個還是有必要的,除非一點就是你查詢的都是索引內容而不是表內容,比如只查詢id等等
2.查詢速度和索引有很大關系也就是索引的大小直接影響你的查詢效果,但是查詢條件一定要建立索引,這點上注意的是索引字段不能太多，太多索引文件就會很大那樣搜索只能變慢,
3.查詢指定的記錄最好通過Id進行in查詢來獲得真實的數據.其實不是最好而是必須，也就是你應該先查詢出復合的ID列表,通過in查詢來獲得數據

我們來做一個測試ipdatas表：
CREATE TABLE `ipdatas` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`uid` INT(8) NOT NULL DEFAULT ‘0’,
`ipaddress` VARCHAR(50) NOT NULL,
`source` VARCHAR(255) DEFAULT NULL,
`track` VARCHAR(255) DEFAULT NULL,
`entrance` VARCHAR(255) DEFAULT NULL,
`createdtime` DATETIME NOT NULL DEFAULT ‘0000-00-00 00:00:00′,
`createddate` DATE NOT NULL DEFAULT ‘0000-00-00′,
PRIMARY KEY (`id`),
KEY `uid` (`uid`)
) ENGINE=MYISAM AUTO_INCREMENT=67086110 DEFAULT CHARSET=utf8;
這是我們做的廣告聯盟的推廣ip數據記錄表，由于我也不是mysql的DBA所以這里咱們僅僅是測試
因為原來里面有大概7015291條數據

這里我們通過jdbc的batch插入6000萬條數據到此表當中“JDBC插入6000W條數據用時:9999297ms”；
大概用了兩個多小時,這里面我用的是batch大小大概在1w多每次提交,還有一點是每次提交的數據都很小，而且這里用的myisam數據表，因為我需要知道mysql數據庫的大小以及索引數據的大小結果是
ipdatas.MYD 3.99 GB (4,288,979,008 字節)
ipdatas.MYI 1.28 GB (1,377,600,512 字節)
這里面我要說的是如果真的是大數據如果時間需要索引還是最好改成數字字段,索引的大小和查詢速度都比時間字段可觀。

步入正題:
1.全表搜索
返回結構是67015297條數據
SELECT COUNT(id) FROM ipdatas;
SELECT COUNT(uid) FROM ipdatas;
SELECT COUNT(*) FROM ipdatas;
首先這兩個全表數據查詢速度很快,mysql中包含數據字典應該保留了數據庫中的最大條數
查詢索引條件
SELECT COUNT(*) FROM ipdatas WHERE uid=1;?? 返回結果時間:2分31秒594
SELECT COUNT(id) FROM ipdatas WHERE uid=1;? 返回結果時間:1分29秒609
SELECT COUNT(uid) FROM ipdatas WHERE uid=1; 返回結果時間:2分41秒813
第二次查詢都比較快因為mysql中是有緩存區的所以增大緩存區的大小可以解決很多查詢的優化，真可謂緩存無處不在啊在程序開發中也是層層都是緩存
查詢數據
第一條開始查詢
SELECT * FROM ipdatas ORDER BY id DESC LIMIT 1,10 ; 31毫秒
SELECT * FROM ipdatas LIMIT 1,10 ; 15ms

第10000條開始查詢
SELECT * FROM ipdatas ORDER BY id ASC LIMIT 10000,10 ; 266毫秒
SELECT * FROM ipdatas LIMIT 10000,10 ; 16毫秒

第500萬條開始查詢
SELECT * FROM ipdatas LIMIT 5000000,10 ;11.312秒
SELECT * FROM ipdatas ORDER BY id ASC LIMIT 5000000,10 ; 221.985秒
這兩條返回結果完全一樣,也就是mysql默認機制就是id正序然而時間卻大相徑庭

第5000萬條開始查詢
SELECT * FROM ipdatas LIMIT 60000000,10 ;66.563秒 (對比下面的測試)
SELECT * FROM ipdatas ORDER BY id ASC LIMIT 50000000,10; 1060.000秒
SELECT * FROM ipdatas ORDER BY id DESC LIMIT 17015307,10; 434.937秒
第三條和第二條結果一樣只是排序的方式不同但是用時卻相差不少，看來這點還是不如很多的商業數據庫,像oracle和sqlserver等都是中間不成兩邊還是沒問題，看來mysql是開始行越向后越慢，這里看來可以不排序的就不要排序了性能差距巨大,相差了20多倍

查詢數據返回ID列表
第一條開始查
select id from ipdatas order by id asc limit 1,10; 31ms
SELECT id FROM ipdatas LIMIT 1,10 ; 0ms

第10000條開始
SELECT id FROM ipdatas ORDER BY id ASC LIMIT 10000,10; 68ms
select id from ipdatas limit 10000,10;0ms

第500萬條開始查詢
SELECT id FROM ipdatas LIMIT 5000000,10; 1.750s
SELECT id FROM ipdatas ORDER BY id ASC LIMIT 5000000,10;14.328s

第6000萬條記錄開始查詢
SELECT id FROM ipdatas LIMIT 60000000,10; 116.406s
SELECT id FROM ipdatas ORDER BY id ASC LIMIT 60000000,10; 136.391s

select id from ipdatas limit 10000002,10; 29.032s
select id from ipdatas limit 20000002,10; 24.594s
select id from ipdatas limit 30000002,10; 24.812s
select id from ipdatas limit 40000002,10; 28.750s? 84.719s
select id from ipdatas limit 50000002,10; 30.797s? 108.042s
select id from ipdatas limit 60000002,10; 133.012s? 122.328s

select * from ipdatas limit 10000002,10; 27.328s
select * from ipdatas limit 20000002,10; 15.188s
select * from ipdatas limit 30000002,10; 45.218s
select * from ipdatas limit 40000002,10; 49.250s?? 50.531s
select * from ipdatas limit 50000002,10; 73.297s?? 56.781s
select * from ipdatas limit 60000002,10; 67.891s?? 75.141s

select id from ipdatas order by id asc limit 10000002,10; 29.438s
select id from ipdatas order by id asc limit 20000002,10; 24.719s
select id from ipdatas order by id asc limit 30000002,10; 25.969s
select id from ipdatas order by id asc limit 40000002,10; 29.860d
select id from ipdatas order by id asc limit 50000002,10; 32.844s
select id from ipdatas order by id asc limit 60000002,10; 34.047s

至于SELECT * ipdatas order by id asc 就不測試了 大概都在十幾分鐘左右
可見通過SELECT id 不帶排序的情況下差距不太大,加了排序差距巨大
下面看看這條語句
SELECT * FROM ipdatas WHERE id IN (10000,100000,500000,1000000,5000000,10000000,2000000,30000000,40000000,50000000,60000000,67015297);
耗時0.094ms
可見in在id上面的查詢可以忽略不計畢竟是6000多萬條記錄，所以為什么很多lucene或solr搜索都返回id進行數據庫重新獲得數據就是因為這 個,當然lucene/solr+mysql是一個不錯的解決辦法這個非常適合前端搜索技術,比如前端的分頁搜索通過這個可以得到非常好的性能.還可以支 持很好的分組搜索結果集,然后通過id獲得數據記錄的真實數據來顯示效果真的不錯,別說是千萬級別就是上億也沒有問題,真是吐血推薦啊.

上面的內容還沒有進行有條件的查詢僅僅是一些關于orderby和limit的測試,請關注我的下一篇文件對于條件查詢的1億數據檢索測試

原文地址：mysql數據庫千萬級別數據的查詢優化和分頁測試, 感謝原作者分享。

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com