讨论一下 mysql 和 postgresql

[flyingfz]

前段时间有个需求：在 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 语句， 大家有什么想说的？

[gouchaoer]

首先，你用一个 sql 语句把该类以及子类的 category_id 都 select 出来；
然后再用 select * from product where category_id in ( 1, 2, 3...) order by create_time desc;这样

[suspended]

许多年来一直听说 MySQL 是个玩具，我个人是从来不用的。我能决定选型的情况下只用三种数据库：Oracle, PostgreSQL 和 SQlite。

[gouchaoer]

而且这个也是扫表没有用索引的，你 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]

只是某些情况导致行为差别而已，原因没搞明白就切数据库也是厉害

[pq]

很多人都因为某个具体场景的应用体验不好，就断定这个东西比别的差，但我觉得，既然 mysql 用得如此普遍，肯定有它的优势的。

[flyingfz]

@gouchaoer
你说的方法 ，我也试验过。 执行时间 与 前文提到的语句，差不多。
只是在这个贴子里没有写出来。

[zhx1991]

上面说 mysql 是玩具我要笑死了

我所在公司基本全用 mysql, 一天上亿的数据.

[sagaxu]

不贴执行计划的 db 性能对比，都是耍流氓

[flyingfz]

@pq
其实我用的比较多的是 MS Sql Server , Oracle 和 Postgresql 也用过一段时间。mysql 用的比较少。
这个项目一开始是其他人选择的 mysql，碰到了这样的情况就很奇怪，
因为在其他数据库里，类似的语句基本上感觉不出来执行的时间。

也许 mysql 有很多强大的功能，但我觉得，还是切换到我相对熟悉一点的数据库上比较好一点。

[glues]

MySQL 近几年停滞不前，落后是事实，无论是特性还是性能落后 PG 也是事实。
肯定又有人说好多大厂都用 MySQL，大厂用的 MySQL 早就改的不是原来的 MySQL 了，只是协议兼容而已，真不要觉得你跟他用是同一个东西

[whx20202]

我感觉你是用了一种“巧妙的” 方法，让 mysql 走了很差的执行计划
其次 innodb 还是 myisam 引擎也没有说
第三，应该贴出来执行计划

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

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

按照这个思路 你可以尝试优化一下 SQL

[akira]

@flyingfz 说到底就是，你自己熟悉什么数据库，就用什么数据库。 再好的东西，你不会用也是没办法用好的

[flyingfz]

不好意思。确实是漏了这些重要的信息。

引擎是 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]

using filesort，是因为排序超过内存的阈值了，这个类似于 postgresql 里面的 work_mem 和 external_merge Disk: xxxMB

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

试试

[zjp]

同样默认配置下，测试插入简单数据（除一个自增主键外没有复杂数据类型 /约束） MySQL(Innodb) 22 毫秒，PostgreSQL 9 毫秒。其他简单测试也是 PostgreSQL 完胜。我觉得我个人能对 MySQL 的优化很有限，而且厌烦 utf8mb4_unicode_ci，准备换 PostgreSQL，反正只是个人小项目

[PythonAnswer]

pg 的全文搜索好用吗？要用什么东西分词啊

[CitizenR]

为单位做的小网站也从 Mysql 转到 Postgresql，这两者性能差异没有直观感受。

[cstj0505]

我的感受是，简单查询，简单场景，那个数据库都能胜任，复杂查询 mysql 就呵呵哒了

[FullBridgeRect]

@pq 我感觉最近几年 mysql 的技术优势基本没有了，只剩下先发优势了

[reus]

pg 的 explain 结果的可读性比 mysql 好得多

[ziding]

mysql 更像一个存储引擎，pg 更像企业级的数据库，没有更好，就看那个更适合而已。

[shyling]

explain 啊。explain extended 啊，explain analyze 啊。。

[likuku]

@flyingfz mysql 只是先入为主在互联网大潮时期恰巧搭上 LAMP 的顺风车从而大面积推广，然后高企的市场占有又刺激继续推广...

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

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

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

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

前些天也在另一个讨论 mysql 慢的贴里回复，提到自己以前在同一台机器上对比测试过百万行的表的基础查询，
PG 比 MySQL-InnoDB 快 7-8 倍。

[zjp]

@likuku MySQL8 终于把 utf8mb4 设为默认字符集了，虽然说最后用的时候还是得稳妥起见，手动指定字符集…

[msg7086]

没啥太多好说的吧。
业务逻辑要是都用 SQL 写，那直接上 Oracle 就好了。
幸好我们业务逻辑是用代码写的。

[realpg]

@msg7086 #25
+10086
又想起了当年面试的奇葩 让 PHP 实现个小功能，有效利用数据库 给我写了个 4KB 文字的 SQL 解决问题……