V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
flyingfz
V2EX  ›  程序员

讨论一下 mysql 和 postgresql

  •  1
     
  •   flyingfz · 2017-12-29 15:25:39 +08:00 · 6064 次点击
    这是一个创建于 2507 天前的主题,其中的信息可能已经有所发展或是发生改变。

    前段时间有个需求:在 MySQL 数据库里, 查找某个分类(包含其子分类 )产品 ,按照 创建时间倒序,分页取 20 条, 写出了类似这样的 sql:

    select p.* from 
    product P 
    	inner join 
    	( 
    		select 
    			c.category_id 
    		from 
    			category c 
    		join 
    			category c2 
    		on 
    			c.parent_category_id = c2.category_id
    		join 
    			category c3  
    		on 
    			c2.parent_category_id = c3.category_id
    		where 
    			c.category_id  = @category_id or 
    			c2.category_id = @category_id or 
    			c3.category_id = @category_id 
    	)  as C 
    on 
    P.category_id = C.category_id 
    order by P.create_time desc
    limit 20	
    

    product 表,product_id(char(40)) 是主键,数据量,在 20k 左右,在 category_id(char(40)) 列,已经创建了 索引, category 表,category_id(char(40)) 是主键,总共只有 200 多条,

    就这么个语句,MySQL 在我的开发机器上,执行了 16 到 25 秒 左右。

    果断换 postgresql,每个表只有主键,未加其他索引 , 同样的数据量,同样的 sql 语句, 执行 30 到 40 毫秒 。

    差异太明显了,所以,这个项目的 数据库改为 postgresql 了。

    关于 mysql 和 postgresql ,以及上述的 sql 语句, 大家有什么想说的?

    26 条回复    2018-01-02 09:48:03 +08:00
    gouchaoer
        1
    gouchaoer  
       2017-12-29 15:53:29 +08:00
    首先,你用一个 sql 语句把该类以及子类的 category_id 都 select 出来;
    然后再用 select * from product where category_id in ( 1, 2, 3...) order by create_time desc;这样
    suspended
        2
    suspended  
       2017-12-29 16:01:35 +08:00
    许多年来一直听说 MySQL 是个玩具,我个人是从来不用的。我能决定选型的情况下只用三种数据库:Oracle, PostgreSQL 和 SQlite。
    gouchaoer
        3
    gouchaoer  
       2017-12-29 16:02:52 +08:00
    而且这个也是扫表没有用索引的,你 pg 执行这个语句也应该没法用索引的吧……如果你把 category 表同一个大类下的所有类的 parent_category_id 都设置成这个大类,那么就可以用:
    select * from product where parent_category_id=12 order by create_time desc limit 10;这样来走索引,而只需要建立一个 parent_category_id 和 create_time 的联合索引就 ok 了
    gouchaoer
        4
    gouchaoer  
       2017-12-29 16:04:43 +08:00
    只是某些情况导致行为差别而已,原因没搞明白就切数据库也是厉害
    pq
        5
    pq  
       2017-12-29 16:08:57 +08:00
    很多人都因为某个具体场景的应用体验不好,就断定这个东西比别的差,但我觉得,既然 mysql 用得如此普遍,肯定有它的优势的。
    flyingfz
        6
    flyingfz  
    OP
       2017-12-29 16:46:25 +08:00
    @gouchaoer
    你说的方法 ,我也试验过。 执行时间 与 前文提到的语句,差不多。
    只是在这个贴子里没有写出来。
    zhx1991
        7
    zhx1991  
       2017-12-29 16:51:13 +08:00
    上面说 mysql 是玩具我要笑死了

    我所在公司基本全用 mysql, 一天上亿的数据.
    sagaxu
        8
    sagaxu  
       2017-12-29 16:52:07 +08:00 via Android
    不贴执行计划的 db 性能对比,都是耍流氓
    flyingfz
        9
    flyingfz  
    OP
       2017-12-29 16:55:00 +08:00
    @pq
    其实我用的比较多的是 MS Sql Server , Oracle 和 Postgresql 也用过一段时间。mysql 用的比较少。
    这个项目一开始是其他人选择的 mysql,碰到了这样的情况就很奇怪,
    因为在其他数据库里,类似的语句基本上感觉不出来执行的时间。

    也许 mysql 有很多强大的功能,但我觉得,还是切换到我相对熟悉一点的数据库上比较好一点。
    glues
        10
    glues  
       2017-12-29 16:58:27 +08:00
    MySQL 近几年停滞不前,落后是事实,无论是特性还是性能落后 PG 也是事实。
    肯定又有人说好多大厂都用 MySQL,大厂用的 MySQL 早就改的不是原来的 MySQL 了,只是协议兼容而已,真不要觉得你跟他用是同一个东西
    whx20202
        11
    whx20202  
       2017-12-29 17:00:02 +08:00
    我感觉你是用了一种“巧妙的” 方法,让 mysql 走了很差的执行计划
    其次 innodb 还是 myisam 引擎也没有说
    第三,应该贴出来执行计划

    手头没有数据库,看 SQL 猜测一下:
    这里嵌套循环的执行效率比较高,最好的办法是 按照 order by 的字段,拿出 20 个 ID,走内层 join,内存 join 里面继续嵌套循环

    SQL 查询只有一个原则:
    尽可能读最少的行

    按照这个思路 你可以尝试优化一下 SQL
    akira
        12
    akira  
       2017-12-29 17:00:14 +08:00
    @flyingfz 说到底就是,你自己熟悉什么数据库,就用什么数据库。 再好的东西,你不会用也是没办法用好的
    flyingfz
        13
    flyingfz  
    OP
       2017-12-29 17:09:30 +08:00
    不好意思。确实是漏了这些重要的信息。

    引擎是 innodb
    上面的语句 explain 的结果 文本如下: 贴图不大方便,就没贴图。抱歉。

    "id" "select_type" "table" "partitions" "type" "possible_keys" "key" "key_len" "ref" "rows" "filtered" "Extra"
    "1" "SIMPLE" "P" \N "ALL" \N \N \N \N "15361" "100.00" "Using where; Using filesort"
    "1" "SIMPLE" "c" \N "eq_ref" "PRIMARY" "PRIMARY" "110" "productservice.P.category_id" "1" "100.00" "Using where"
    "1" "SIMPLE" "c2" \N "eq_ref" "PRIMARY" "PRIMARY" "110" "productservice.c.parent_category_id" "1" "100.00" "Using where"
    "1" "SIMPLE" "c3" \N "eq_ref" "PRIMARY" "PRIMARY" "110" "productservice.c2.parent_category_id" "1" "100.00" "Using index"

    @whx20202 你的思路 我再理解下, 看看情况如何。 谢谢。
    whx20202
        14
    whx20202  
       2017-12-29 17:19:50 +08:00
    using filesort,是因为排序超过内存的阈值了,这个类似于 postgresql 里面的 work_mem 和 external_merge Disk: xxxMB

    试试 p.create_time 建个索引
    c.parent_category_id 建个索引
    所有的 category_id 检查索引

    试试
    zjp
        15
    zjp  
       2017-12-29 18:19:34 +08:00 via Android   ❤️ 1
    同样默认配置下,测试插入简单数据(除一个自增主键外没有复杂数据类型 /约束) MySQL(Innodb) 22 毫秒,PostgreSQL 9 毫秒。其他简单测试也是 PostgreSQL 完胜。我觉得我个人能对 MySQL 的优化很有限,而且厌烦 utf8mb4_unicode_ci,准备换 PostgreSQL,反正只是个人小项目
    PythonAnswer
        16
    PythonAnswer  
       2017-12-30 02:47:00 +08:00 via Android
    pg 的全文搜索好用吗?要用什么东西分词啊
    CitizenR
        17
    CitizenR  
       2017-12-30 09:08:49 +08:00
    为单位做的小网站也从 Mysql 转到 Postgresql,这两者性能差异没有直观感受。
    cstj0505
        18
    cstj0505  
       2017-12-30 10:00:49 +08:00 via Android
    我的感受是,简单查询,简单场景,那个数据库都能胜任,复杂查询 mysql 就呵呵哒了
    FullBridgeRect
        19
    FullBridgeRect  
       2017-12-30 14:18:37 +08:00
    @pq 我感觉最近几年 mysql 的技术优势基本没有了,只剩下先发优势了
    reus
        20
    reus  
       2017-12-30 14:44:46 +08:00
    pg 的 explain 结果的可读性比 mysql 好得多
    ziding
        21
    ziding  
       2017-12-30 16:31:12 +08:00
    mysql 更像一个存储引擎,pg 更像企业级的数据库,没有更好,就看那个更适合而已。
    shyling
        22
    shyling  
       2017-12-30 19:45:57 +08:00
    explain 啊。explain extended 啊,explain analyze 啊。。
    likuku
        23
    likuku  
       2017-12-30 20:00:43 +08:00
    @flyingfz mysql 只是先入为主在互联网大潮时期恰巧搭上 LAMP 的顺风车从而大面积推广,然后高企的市场占有又刺激继续推广...

    和当初提到 Linux 就以为世界只有 红帽子 类似...

    虽然好多年前 mysql 用户还在为各种多字节语言编码头疼的时候,PG 早已实现 Unicode,所有语言通吃;

    mysql 对 SQL 国际标准都迟迟支持不全,最近几年不知是否完善;

    若没有 InnoDB 的加持,mysql 恐怕如今还是受限于不支持事务+只有表锁的 myisam。

    前些天也在另一个讨论 mysql 慢的贴里回复,提到自己以前在同一台机器上对比测试过百万行的表的基础查询,
    PG 比 MySQL-InnoDB 快 7-8 倍。
    zjp
        24
    zjp  
       2017-12-30 21:42:23 +08:00 via Android
    @likuku MySQL8 终于把 utf8mb4 设为默认字符集了,虽然说最后用的时候还是得稳妥起见,手动指定字符集…
    msg7086
        25
    msg7086  
       2017-12-31 09:11:31 +08:00
    没啥太多好说的吧。
    业务逻辑要是都用 SQL 写,那直接上 Oracle 就好了。
    幸好我们业务逻辑是用代码写的。
    realpg
        26
    realpg  
       2018-01-02 09:48:03 +08:00
    @msg7086 #25
    +10086
    又想起了当年面试的奇葩 让 PHP 实现个小功能,有效利用数据库 给我写了个 4KB 文字的 SQL 解决问题……
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2634 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 05:08 · PVG 13:08 · LAX 21:08 · JFK 00:08
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.