mysql數(shù)據(jù)庫千萬級別數(shù)據(jù)的查詢優(yōu)化和分頁測試

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

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

我原來的公司是一家網(wǎng)絡(luò)游戲公司,其中網(wǎng)站交易與游戲數(shù)據(jù)庫結(jié)合通過ws實現(xiàn)的,但是交易記錄存放在網(wǎng)站上,級別是千萬級別的數(shù)據(jù)庫是mysql數(shù)據(jù)庫.

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

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

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

我們來做一個測試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;
這是我們做的廣告聯(lián)盟的推廣ip數(shù)據(jù)記錄表，由于我也不是mysql的DBA所以這里咱們僅僅是測試
因為原來里面有大概7015291條數(shù)據(jù)

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

步入正題:
1.全表搜索
返回結(jié)構(gòu)是67015297條數(shù)據(jù)
SELECT COUNT(id) FROM ipdatas;
SELECT COUNT(uid) FROM ipdatas;
SELECT COUNT(*) FROM ipdatas;
首先這兩個全表數(shù)據(jù)查詢速度很快,mysql中包含數(shù)據(jù)字典應(yīng)該保留了數(shù)據(jù)庫中的最大條數(shù)
查詢索引條件
SELECT COUNT(*) FROM ipdatas WHERE uid=1;?? 返回結(jié)果時間:2分31秒594
SELECT COUNT(id) FROM ipdatas WHERE uid=1;? 返回結(jié)果時間:1分29秒609
SELECT COUNT(uid) FROM ipdatas WHERE uid=1; 返回結(jié)果時間:2分41秒813
第二次查詢都比較快因為mysql中是有緩存區(qū)的所以增大緩存區(qū)的大小可以解決很多查詢的優(yōu)化，真可謂緩存無處不在啊在程序開發(fā)中也是層層都是緩存
查詢數(shù)據(jù)
第一條開始查詢
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秒
這兩條返回結(jié)果完全一樣,也就是mysql默認(rèn)機制就是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秒
第三條和第二條結(jié)果一樣只是排序的方式不同但是用時卻相差不少，看來這點還是不如很多的商業(yè)數(shù)據(jù)庫,像oracle和sqlserver等都是中間不成兩邊還是沒問題，看來mysql是開始行越向后越慢，這里看來可以不排序的就不要排序了性能差距巨大,相差了20多倍

查詢數(shù)據(jù)返回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多萬條記錄，所以為什么很多l(xiāng)ucene或solr搜索都返回id進(jìn)行數(shù)據(jù)庫重新獲得數(shù)據(jù)就是因為這 個,當(dāng)然lucene/solr+mysql是一個不錯的解決辦法這個非常適合前端搜索技術(shù),比如前端的分頁搜索通過這個可以得到非常好的性能.還可以支 持很好的分組搜索結(jié)果集,然后通過id獲得數(shù)據(jù)記錄的真實數(shù)據(jù)來顯示效果真的不錯,別說是千萬級別就是上億也沒有問題,真是吐血推薦啊.

上面的內(nèi)容還沒有進(jìn)行有條件的查詢僅僅是一些關(guān)于orderby和limit的測試,請關(guān)注我的下一篇文件對于條件查詢的1億數(shù)據(jù)檢索測試

原文地址：mysql數(shù)據(jù)庫千萬級別數(shù)據(jù)的查詢優(yōu)化和分頁測試, 感謝原作者分享。

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