以后谁再跟我说MySQL性能好我跟谁急……

TNND,今天浪费了一天的时间在Mysql上面,先是改代码,然后是转换sqlite3数据库到mysql,然后发现原来好好的网站跑不起来了。 @。@ 然后就这么折腾了半天,基本上确定了,在select语句上,mysql的性能平均落后sqlite十倍左右,内存消耗超过sqlite则是三倍左右。
-
实际上mysql更灵活点,我的意思是:给mysql三倍的内存,那么他的表现只比sqlite慢十倍而已,如果你给他很抠门的内存?那么超时是唯一的结果。就像我一开始网站挂掉那样。
-

不过今天这么折腾的好处是:
1.对数据库的命令行操作有了全面的认识,现在我已经完全不需要phpmyadmin这种土货了
2.对数据库的优化有了全面的认识,我今天起码看了100篇以上的mysql索引优化方法,官方文档翻了个底朝天,看得吐血
-
结论是:
1.mysql太过傻X,order by怎么样都用不上index,而这应该是用膝盖想都要用index的(如果我错了,请指出,我实在是找遍网上资料,mysql官网文档都快犁了一遍了,还是没找到如何让order by用index的方法(我是指没有where语句,只有order by,至少sqlite是会去用index的,这从explain语句可以看出))
2.即使用fulltext全文索引,用where加limit搜索同样的内容,如果分页很大(例如limit 10000,1)的话,mysql的执行速度还是比sqlite慢十倍左右。
3.where加order by加limit,其中where和order by的关键字组成索引对,明显可以看出sqlite的性能提升很大,而mysql的性能则毫无多大变化。
-
有人说sqlite不开事务很悲剧,insert时间是别人的十几倍,我靠谁吃饱了撑的有事没事去insert?数据库到底是insert时间多还是select的时间多,哪怕insert时间比mysql慢一百倍,就冲它select时间比mysql快一百倍以上我就不会用mysql了。(真的有一百倍,如果用order by关键字的话,因为mysql怎么都教不会它用索引,用force index语句都不行,它就是顽固地要进行filesort,太无语了)
-
有人不信的话我可以提供数据库和测试语句,自己去折腾去
==========================
一些比较:
mysql设置:默认的large配置文件,修改sort相关的buff到16M以上
sqlite设置:无设置
数据库:verycd,表项有id,title,content,updatetime,category等,id是primary integer,数据表项有16万
索引都是三个,updatetime,(updatetime,title),(category,updatetime,title)
语句一:
select title from verycd limit 150000,1;
mysql=1 row in set (4.64 sec)
sqlite3=CPU Time: user 0.116007 sys 0.144009
语句二:
select title from verycd order by updtime desc limit 150000,1;
mysql=1 row in set (3.97 sec)
sqlite3=CPU Time: user 0.032002 sys 0.020001
语句三:
#mysql额外做了fulltext
select title from verycd where title like "%4%" limit 10000,1;
mysql=1 row in set (1.59 sec)
sqlite3=CPU Time: user 0.268016 sys 0.180011
语句四:
select title from verycd where title like "%5%" order by updtime desc limit 10000,1;
mysql=1 row in set (1.79 sec)
sqlite3=CPU Time: user 0.184011 sys 0.020002
语句五:为mysql开个特例,用primary key做索引,它倒是用了,但是……
select title from verycd where title like "%2%" order by verycdid desc limit 10000,1;
mysql=1 row in set (0.40 sec)
你好意思和sqlite3的0.02比么?
=================================
如果是我太土不会用mysql的话请千万告诉我,不然我以后就坚定地抱着mysql性能就是渣的想法了,以后除非高并发,不需要order by,也不需要limit分页的应用,否则很难相信我会去选择mysql了。

This entry was posted in VPS相关, 其他 and tagged , . Bookmark the permalink.

43 Responses to 以后谁再跟我说MySQL性能好我跟谁急……

  1. Dianso says:

    VERYCD是MYSQL数据库,我上VERYCD几年了,保存过好几次mysql繁忙的截图。

  2. observer says:

    verycd每天号称200万IP,那个我这个不知道今天2000IP会不会有,所以不能这样比较吧,呵呵。
    不过话说回来人家verycd肯定是用n台服务器,然后让n个squid或者nginx转发分流的,隶属verycd的服务器ip我就见过大概4个了。而我这个不过是一个512MB内存的虚拟主机而已。
    我现在对于优化这种东西很有心得,要是让我来整verycd,性能肯定比现在要好上一大截。

  3. observer says:

    最起码verycd的页面源代码太丑陋了,javascript满地爬,非常没有效率。

  4. LD says:

    SQLite支持存储过程吗? 存取控制? 分布式解决方案? mysql的速度不是单单的增加内存,。 另外实际应用中 insert多于select的情况也十分常见。

    mysql的优点并不是速度一项, 它有很多类型的表 ISAM, MyISAM, HEAP, BDB, InnoDB … 每个表的特性不同,所以相同配置下性能会差别很大。

  5. observer says:

    看来LD同学是mysql达人,那么请教一下我这种应用环境下应该用哪种表,怎么配置?

  6. observer says:

    你说错了,mysql如果有优点的话,速度绝对不是其中的一项

  7. 阿正 says:

    其实未必JavaScript多了效率就低,JavaScript实在客户端运行的,实际上对于服务器而言,是减轻服务器的负载

  8. kid says:

    我之前也搞了一个测试,1w条数据,用三个数据库做比较mysql,postgres,sqlite,结果不论insert还是select,sqlite都慢一倍

    我现在只用postgres了,mysql的话对中文支持不够(当然,不排除我是菜鸟的可能性),sqlite做个测试还是非常好的!django里用起来很爽!

  9. txchine says:

    没做过mysql的测试。不过我线上有一个项目凌晨会做一些crond任务,sqlite是非常的慢。20W用户能跑到2个小时以上,因为每天的数据在增加,sqlite的时间也在增长。不过没有发现有死锁的现象,你说的有一些绝对。

    用mysql和sqlite分应用的情况吧。sqlite是轻量级数据库,不能分布式是最大的缺点,mysql不会吧。对于你的应用来说可能sqlite能发挥到极致,但是对于其他应用来说mysql会合适一些。

    • observer says:

      其实我最不满的还是mysql的索引功能太弱了,很明显如果是我来写数据库软件,order by怎么说都是要调用索引的,它怎能不用?太让我费解了。用了索引以后性能和sqlite相差不是太大,就是内存消耗太厉害了。

      楼上那些可能是1w条数据都比较小,可以全部读入内存,我做搜索的时候全部读入内存就废掉了,要1G内存,我的虚拟主机上可没那么多内存。这时候同样内存不足下,sqlite就比mysql好无数了。

      你们说的对,我是说的太绝对了,确实,sqlite的insert,update瓶颈限制了它的使用,像我这个项目基本上只要select,这时候sqlite就显示出其优势了。

  10. zigne says:

    mysql的fulltext不是给like用的,而且有些索引会在运行一段时间后才开始建立

  11. P.Linux says:

    首先,上面的仁兄说了,FullText索引要使用Match(title) Against(’4′)才会起作用,MySQL以空格等分隔符为界拆分,也有一定的自然语言处理。
    SQLite和MySQL、Oracle本来就是面向不同层级的应用,到了百万千万级的数据,SQLite就无法胜任了,都有自己的用处。
    对于直接Order By不使用索引,Oracle 10g以后也是这样。我想,大概是基于开销的考虑,读取索引本身也是需要代价的,尤其是索引key_len比较长的时候,还不如直接全部拿出来排序好了,不过这个我也不确定,Oracle 8i里面直接Order By能用索引,10g后就不行了,原因我也不是太清楚。
    并且,优化要多基于业务需求,光靠SQL总有撑垮数据库的时候。比如说,你有一个计数器知道当前数据又多少条记录的话,可以通过ASC和DESC配合limit做最少的扫描,同时可以通过Partition分区比较大的数据表。
    另外建索引的方式,如果是WHERE key1<XX and key2<YY Order by (Group By) key3,key4,建索引的顺序为(key1,key2,key3,key4),排序(分组)字段放最后,这样就可避免排序。
    理论上Group by key1 order by key2也可以通过(key1,key2)这样的索引来完全避免排序的,不过不知道为什么数据库都不采用这样的执行计划,不知道是不是也是因为分组以后数据很少了,排序代价比读取索引代价小。
    PS.非常支持你的项目,加油!

  12. 神仙 says:

    你真的尝试写数据库了就知道了,很多时候,order by就是用不上索引。

    如果你了解索引的数据结构就会明白很多了。

    其实你这篇文章说了半天也就是说了个limit的问题而已。

  13. 神仙 says:

    另外,我想了解一下,sqlite为什么对
    select title from verycd where title like “%4%” limit 10000,1;
    都能那么快。难道全加在在内存里了?

  14. MeaCulpa says:

    MySQL已经有了这样的占有率,为什么PostgresSQL和Sqlite以及那么多DBMS还会存在?
    本文给出了原因。

  15. P.Linux says:

    @神仙,SQLite也有内存模式,像MySQL的Memory引擎一样。

  16. observer says:

    to神仙
    (updtime,title)的索引我估算了一下也就16M左右的东西,完全可以读到内存里面来专门做搜索,我没专门那么处理,但是sqlite可能自动这么做了,这样也好,省得我去优化。
    而这时候mysql教不会索引的弱点就显现出来了,我实在不知道怎么指挥mysql去把这段东西放入内存,平时搜索就用它,也许需要分表,建立内存表,或者什么其他复杂的罗里罗嗦一堆操作,不过我又不是dba,实在没兴趣去折腾mysql了,有简单的高性能的方案,就不用复杂的可能高性能/可能性能也就那样的方案了。

  17. P.Linux says:

    to observer
    MySQL也会把索引加载到内存,但是你要设置key_buffer_size能放下这些索引,并且第一次读的时候才会把索引加载进内存,而不是数据库启动就加载。你的SQL我也测试了,第一次执行效率不高,但反复执行效率就非常高了,即使排除query_cache的影响,也没有你列的这么夸张啦。当然,你只是把数据库当个简单的数据容器,SQLite就足矣了,数据量大了之后,提供MySQL或者PostgreSQL的接口还是有必要的~

    • observer says:

      mysql的large站默认配置文件的key_buffer_size貌似是256M,也没见它去读索引。总之其实是它死活不用索引所以我比较无语。@.@

      我不觉得我夸张啊,我就是把同一台VPS,同一个数据库,同样的索引方式,同样的查询语句的数据截下来而已,可能自己电脑内存大会不一样吧。

  18. P.Linux says:

    @observer
    SELECT * FROM xxx ORDER BY col这种格式的SQL,MySQL确实不读索引,Oracle也是,原因我还真不清楚,不过我对比了FORCE INDEX和直接执行的结果,几乎无差异。
    我说的key_buffer是把磁盘索引载入内存,并不影响MySQL优化器对执行计划的判断。
    我觉得SQLite应该是默认启用Memoey模式。你可以把MySQL数据表的引擎调整为Memory,不过会占用很多内存。
    我测试的结果在不启用缓存的情况下即使没有用索引大约只有你的10%的时间。我觉得你还是参数没设置好。我的VPS总共只有360M的内存。
    另外,缓存方面的优化,
    select title from verycd limit 150000,1;
    select title from verycd order by updtime desc limit 150000,1;
    这两条语句你来建一个(title,updtime)的索引再试试。
    like “%5%”这种语句,MyISAM确实不好处理,除非是’xxx 5 xxx’这种情况,FullText索引能用MATCH(title) AGAINST(’5′)的方式匹配,如果是’xxx5xxx’的形式,就没办法了。
    SQLite对like “%5%”怎么处理,我就不知道了,如果数据全部在内存里,那可以理解。

  19. feline says:

    可以试试postgresql这个数据库,也是开源的

  20. 张沈鹏 says:

    mysql 你多查询几遍试试看
    第一遍的确会很慢
    不过以后往往就快了

    有空和我聊天 如题如题

  21. Jerry Chen says:

    MySQL真的要用的话,推荐你用上Memcached来加快性能。
    这是我写的一个class:
    https://file.cloud.gd/public/3d23618fb41d32cae73aaccee10005f2/db.memcache.class.php

    • observer says:

      已经用了:),这是去年这个时候的文章了。

      不过,memcached用来存很少变的东东效果还不错,存经常变的就意义不大了,order by和limit应用正是经常变的东东,所以即使用了memcached也好不到哪里去,而且limit参数可以随便改,都存在内存太浪费了。

      实际上我这个需求,最好的办法还是自己写数据结构,后来我还发过一篇数据库性能比拼的文章,用十几行代码完虐所有数据库,包括所有内存数据库。

  22. leeolevis says:

    请问sqlite高并发可能会锁死数据库的情况怎么解决

  23. Aaron says:

    please sent me the code

  24. 呵呵 says:

    数据库不是比效率的吧

    高并发可以硬件解决,
    或用软件实现类似取号码排队
    或数据库内部可以分割表,临时表等加快速度

  25. wangyeee says:

    一般实际的应用里面,在搜索的时候很少用到like语句的,一般都是在应用程序里面建立索引,有搜索请求的时候查索引,然后在数据库里面直接定位的。

    • observer says:

      嗯,原来因为图省事,就like了,君不见SimpleCD一开始的数据才一个表,现在改写起来真是愁死我了

  26. icat says:

    饿 小弟初入这行 毛们的问句,用oracle有戏不? 那个不是商业使用没有授权问他。。

    • kin29 says:

      要付高额的使用费用,如果你要盗版,会造成高额的后期费用。如果兄弟你想个人资助的话。

  27. jestwu says:

    请教一下observer,怎么将sqlite3数据转换到MYsql上,或者有什么直接转换的工具吗,我弄了很久也没有转换成功啊,!

  28. sinoxu says:

    这个blog用的是哪个数据库呢?

  29. MAJINN says:

    非常欣赏你的simplecd的爬虫程序可不可以指点一二。shyshysunday@gmail.com

  30. Apache says:

    其实是思路有问题,我没有看你网站的源代码,但从你的描述来看,来自页面的查询,都是100%命中数据库的.

    对于一个高并发的网站来说,从开始设计就应该考虑有缓存机制,尽量将页面访问到数据库查询比率控制在1%-5% 之内.

    因为你的资料内容并不是频繁变更,可以让大部分访问命中缓存,当资料变更时,再更新缓存的内容

  31. Apache says:

    另外补充一点, 相对来说PHP比pyhon是更好的选择

    python 之前的版本对多核的使用情况不好,不知道现在如何了.

  32. ark.sg says:

    you are in reality a just right webmaster. The web site loading pace is incredible.
    It seems that you are doing any unique trick. Furthermore, The contents are masterpiece.
    you have done a great activity in this matter!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>