昨天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了。这样速度就快了!


 

最后附上一截图

ssss.jpg