本文为本人最近利用几个小时才分析总结出的原创文章,希望大家转载,但是要注明出处

http://blog.sina.com.cn/s/blog_438308750100im0b.html

有什么问题:yubaojian0616@163.com?于堡舰

?

我原来的公司是一家网络游戏公司,其中网站交易与游戏数据库结合通过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 1000000