前段时间有个需求:在 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 语句, 大家有什么想说的?
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 就好了。 幸好我们业务逻辑是用代码写的。 |