关于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 不带排序的情况下差距不太大,加了排序差距巨大