關于mysql存儲大資料的問題

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;

這裡用的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

  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 不帶排序的情況下差距不太大,加了排序差距巨大