这是一个创建于 2510 天前的主题，其中的信息可能已经有所发展或是发生改变。
前段时间有个需求：在 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
 |
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;这样 |
 |
2
suspended 2017-12-29 16:01:35 +08:00
许多年来一直听说 MySQL 是个玩具，我个人是从来不用的。我能决定选型的情况下只用三种数据库：Oracle, PostgreSQL 和 SQlite。
|
|
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 了 |
|
4
gouchaoer 2017-12-29 16:04:43 +08:00
只是某些情况导致行为差别而已，原因没搞明白就切数据库也是厉害
|
 |
5
pq 2017-12-29 16:08:57 +08:00
很多人都因为某个具体场景的应用体验不好，就断定这个东西比别的差，但我觉得，既然 mysql 用得如此普遍，肯定有它的优势的。
|
 |
7
zhx1991 2017-12-29 16:51:13 +08:00
上面说 mysql 是玩具我要笑死了
我所在公司基本全用 mysql, 一天上亿的数据. |
 |
8
sagaxu 2017-12-29 16:52:07 +08:00 via Android
不贴执行计划的 db 性能对比，都是耍流氓
|
 |
9
flyingfz OP @pq
其实我用的比较多的是 MS Sql Server , Oracle 和 Postgresql 也用过一段时间。mysql 用的比较少。 这个项目一开始是其他人选择的 mysql，碰到了这样的情况就很奇怪， 因为在其他数据库里，类似的语句基本上感觉不出来执行的时间。 也许 mysql 有很多强大的功能，但我觉得，还是切换到我相对熟悉一点的数据库上比较好一点。 |
 |
10
glues 2017-12-29 16:58:27 +08:00
MySQL 近几年停滞不前，落后是事实，无论是特性还是性能落后 PG 也是事实。
肯定又有人说好多大厂都用 MySQL，大厂用的 MySQL 早就改的不是原来的 MySQL 了，只是协议兼容而已，真不要觉得你跟他用是同一个东西 |
 |
11
whx20202 2017-12-29 17:00:02 +08:00
我感觉你是用了一种“巧妙的” 方法，让 mysql 走了很差的执行计划
其次 innodb 还是 myisam 引擎也没有说 第三，应该贴出来执行计划 手头没有数据库，看 SQL 猜测一下： 这里嵌套循环的执行效率比较高，最好的办法是 按照 order by 的字段，拿出 20 个 ID，走内层 join，内存 join 里面继续嵌套循环 SQL 查询只有一个原则： 尽可能读最少的行 按照这个思路 你可以尝试优化一下 SQL |
|
13
flyingfz OP 不好意思。确实是漏了这些重要的信息。
引擎是 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 你的思路 我再理解下， 看看情况如何。 谢谢。 |
|
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 检查索引 试试 |
 |
15
zjp 2017-12-29 18:19:34 +08:00 via Android  1
同样默认配置下，测试插入简单数据（除一个自增主键外没有复杂数据类型 /约束） MySQL(Innodb) 22 毫秒，PostgreSQL 9 毫秒。其他简单测试也是 PostgreSQL 完胜。我觉得我个人能对 MySQL 的优化很有限，而且厌烦 utf8mb4_unicode_ci，准备换 PostgreSQL，反正只是个人小项目
|
 |
16
PythonAnswer 2017-12-30 02:47:00 +08:00 via Android
pg 的全文搜索好用吗？要用什么东西分词啊
|
 |
17
CitizenR 2017-12-30 09:08:49 +08:00
为单位做的小网站也从 Mysql 转到 Postgresql，这两者性能差异没有直观感受。
|
 |
18
cstj0505 2017-12-30 10:00:49 +08:00 via Android
我的感受是，简单查询，简单场景，那个数据库都能胜任，复杂查询 mysql 就呵呵哒了
|
 |
19
FullBridgeRect 2017-12-30 14:18:37 +08:00
@pq 我感觉最近几年 mysql 的技术优势基本没有了，只剩下先发优势了
|
 |
20
reus 2017-12-30 14:44:46 +08:00
pg 的 explain 结果的可读性比 mysql 好得多
|
 |
21
ziding 2017-12-30 16:31:12 +08:00
mysql 更像一个存储引擎，pg 更像企业级的数据库，没有更好，就看那个更适合而已。
|
 |
22
shyling 2017-12-30 19:45:57 +08:00
explain 啊。explain extended 啊，explain analyze 啊。。
|
 |
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 倍。 |
|
24
zjp 2017-12-30 21:42:23 +08:00 via Android
@likuku MySQL8 终于把 utf8mb4 设为默认字符集了，虽然说最后用的时候还是得稳妥起见，手动指定字符集…
|
 |
25
msg7086 2017-12-31 09:11:31 +08:00
没啥太多好说的吧。
业务逻辑要是都用 SQL 写，那直接上 Oracle 就好了。 幸好我们业务逻辑是用代码写的。 |
Select Language