mysql生成了100w条文章数据（2G数据）做测试用

昨天写了个php程序，给mysql插入随机的文章数据，仿照真实文章，中文数据，主要是测试在100w级数量时查询上的优化及测试mysql内置的全文检索与其它类似全文检索引擎的差距等。总数据量：article 101w左右  user表10w左右，总计占用硬盘2G左右

今天开始测试在海量数据查询时的优化手段

1. 测试添加索引对数据查询的影响：
   sql语句：select * from article as article order by time limit 10;
   没有对time字段建立索引时查询所花时间：31.237657s
   新建time字段的索引，花掉近二分钟，建立了111298条索引，没有建立完phpmyadmin就timeout了。
   建立time索引后，同样的sql语句执行只花了：0.380957s，不过time索引没有建完，但是时间相差不大;
   总结：在关键字段的索引上，建与不建速度相差近100倍！
    
2. 测试limit在不同的基数上的影响：
   sql语句：select * from article as article order by id limit  0,10
   当limit的基数为0时【limit  0,10】，执行时间：0.001557s
   当limit的基数为20000时【limit  20000,10】，，执行时间：0.572288s
   当limit的基数为100000时，执行时间：2.793380s
   当limit的基数为50W 时，执行时间：14.444422s
   当limit的基数为100W时，执行时间：28.270054s
   总结：在海量数据中要想查找比较靠后的数据时，要注意limit的基数，因为随着数据量的加大，查找时间继续增长！在取比较后面的数据时，可以通过desc方式把数据反向查找，以减少对前段数据的扫描，让limit的基数越小越好！
    
3. 测试field为*或是id对查询速度的影响：
   sql语句：select id from article as article order by id limit 100000,10
   为了数据更真实，所以通过limit让sql执行时间长些，以便更容易看出区别
   当field为id时，执行时间：2.617519s
   当field为*时，执行时间：2.867721s
   总结：由数量可见，二者在sql执行时间上相差并不大，但是限定field可以大幅度的减少内存开支
    
4. 测试limit限定结果与between限定结果上的区别：
   当用limit          select * from article as article order by id limit 1000000,10      [31.163212s]
   当用between  select * from article as article where id between 1000000 and 1000010 order by id       [0.001433s]
   总结：between 限定上比limit快太多了，所以当在海量数据访问时，建议用between或是where把limit替换掉，但是between也有缺陷，如果id中间有断行或是中间部分id不读取的话，总读取的数量会少于预计数量！
    
5. 测试left join对读取数据的影响
   user表，数据为100000条
   select id from article as article order by id limit 500000      [14.980397s]
   select article.id from article as article left join user as user on user.uid=article.uid order by article.id,user.username limit 500000      [60.488099s]
   总结：当数据量比较大时，用left join做连接是非常慢的，速度是查单表的4倍多，所以如果在大数据量的情况下，如果要实现刚实现用户表与文章表的连表功能，还是把全部的会员数据预先存入memcache等内存缓存中，10万条用户数据也不过5m不到，用户更新资料或登陆时，把用户的数据更新入memcache的指定用户中就ok了。这样速度就快了！

转自：http://www.phpd.cn/archives/230/