V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
Breadykid
V2EX  ›  MySQL

刚才,领导对我的 sql 提出了建议

  •  
  •   Breadykid ·
    breadkid · 2019-09-03 12:44:17 +08:00 · 11039 次点击
    这是一个创建于 1907 天前的主题,其中的信息可能已经有所发展或是发生改变。

    背景:

    • 应用服务器一台,数据库服务器一台
    • 公司用的 mysql
    • 一个报表查询中,我使用了 DATE(create_date)函数,导致 create_date 的索引没被使用到,造成长时间查询

    领导见状后,如是说:

    • 1.索引字段不使用函数
    • 2.sql 的 group by 优于内存处理的 list.stream.Collectors.groupingby
    • 3.联表查首选于单表查
    • 4.数据量大时,考虑分表

    对于第三点我有疑问。。。不是太认同,想问问大家的看法

    第 1 条附言  ·  2019-09-03 15:49:24 +08:00
    再加个背景:
    目前单表 4200w
    41 条回复    2019-09-04 11:49:25 +08:00
    optional
        1
    optional  
       2019-09-03 13:56:24 +08:00   ❤️ 1
    看应用类型,对于高并发的互联网应用,2,3 都是 bad idea。
    optional
        2
    optional  
       2019-09-03 14:00:22 +08:00   ❤️ 1
    嗯 为了避免被喷,加一个 具体情况具体分析。
    knva
        3
    knva  
       2019-09-03 14:02:36 +08:00
    sql 压力越小越好
    notshytoday
        4
    notshytoday  
       2019-09-03 14:05:25 +08:00 via Android
    你不认同肯定有自己的原因的,说说呗

    赞楼上的具体问题具体分析
    Xbluer
        5
    Xbluer  
       2019-09-03 14:07:56 +08:00 via iPhone
    1. oracle 中有所谓的函数索引,即针对列计算出函数的值在生成索引。mysql 好像可以使用虚列曲线救国。
    airfling
        6
    airfling  
       2019-09-03 14:12:44 +08:00
    我觉得单表查询优先于多表,单挑优先于 group by,groupby 可以不再数据库中做,完全可以自己需要的数据直接取出来,自己在内存中做。除非一次取的数据特别大,但是数据特别大这种情况你就要考虑分表了,建索引等优化措施了,而是不是 sql 优化就能解决的
    LeeSeoung
        7
    LeeSeoung  
       2019-09-03 14:22:15 +08:00   ❤️ 2
    1 涉及表字段设计,设计恰当很少出现查询字段需要转换后再查询的情况
    2 是可以认同的,数据量大的情况下 请求大量数据回来,网络 IO 以及对内存都有要求
    3 我看了网上一篇文章中讲到《高性能 mysql 》有提到这个问题,从缓存、锁竞争等方面来说,单表多次查是由于联表查的
    4 完全同意
    scys
        8
    scys  
       2019-09-03 14:23:17 +08:00   ❤️ 3
    * 听领导的,先按照领导的做一遍;
    * 做完后发现没什么效果,研究下 2,3,看看哪里可以优化。

    感觉这样工作才比较有效。
    a719114136
        9
    a719114136  
       2019-09-03 14:27:19 +08:00
    做个实验就知道了,如果懒得做的话我告诉你,领导说的没错
    reus
        10
    reus  
       2019-09-03 14:33:38 +08:00
    你看别的数据库就从来没有什么单表优于多表的说法
    垃圾 mysql。
    xaplux
        11
    xaplux  
       2019-09-03 15:39:50 +08:00
    很明显,不认同领导说的。但是这次你领导说的没什么问题,1 很明显,2 要看具体情况,如果是单表的 groupby 没有问题,3 JOIN 的多张表都比较大时,确实单表查询效率要高,4 没毛病
    binux
        12
    binux  
       2019-09-03 15:44:34 +08:00
    3 看情况,如果 filter 字段都在表 1,join 表 2 取其他字段那是没问题的
    Breadykid
        13
    Breadykid  
    OP
       2019-09-03 15:45:14 +08:00
    @reus 同意,Postgres 就好很多
    luozic
        14
    luozic  
       2019-09-03 15:47:36 +08:00
    一切看数据量还有数据库服务器配置。
    Breadykid
        15
    Breadykid  
    OP
       2019-09-03 15:53:33 +08:00
    @knva 为什么领导说非多张大表连表查询的压力对于服务器来说微乎其微
    Breadykid
        16
    Breadykid  
    OP
       2019-09-03 15:55:11 +08:00
    @luozic
    @binux
    @xaplux
    @a719114136
    @LeeSeoung
    @airfling
    @optional

    为什么领导说非多张大表 联表查询的压力对于数据库来说微乎其微???
    LeeSeoung
        17
    LeeSeoung  
       2019-09-03 15:59:21 +08:00
    不是大表你联查还是单表查都差不多。。个位数相同,小数点后几位你还在乎多少位么。。
    Raymon111111
        18
    Raymon111111  
       2019-09-03 16:21:32 +08:00
    2. 尽可能把压力放在业务侧. 数据库资源很宝贵. 但是 groupby 涉及返回数据大小的问题, 所以还要具体问题具体分析.

    3. 尽可能不做连表查询. 一个很重要的原因是连表的索引往往踩不对.
    winglight2016
        19
    winglight2016  
       2019-09-03 16:49:35 +08:00
    有索引的情况下,多表关联没有增加多少 payload。4000 多万可以开始准备分表了。报表不要用生产库来生成。
    glacer
        20
    glacer  
       2019-09-03 16:56:59 +08:00   ❤️ 1
    从来没有 MySQL 不能多表联查的道理,高性能的 join 查询本身就是关系型数据库的一大优势。在一些规范中限制连表的数量是因为程序员的水平参差不齐容易写出慢查询。
    在索引命中合理的情况下,MySQL 对 Join 的处理性能是完全没有问题的,完爆将多表结果查出在内存中做聚合的做法。
    如果在索引不合理使用的情况下,将多表拆成单表来查,照样和不能命中索引。难道就对数据库的压力就比联表查小了么?
    reus
        21
    reus  
       2019-09-03 17:01:29 +08:00 via Android
    @Breadykid mysql 8 是可以的,5 就不行
    cwjokaka
        22
    cwjokaka  
       2019-09-03 17:07:02 +08:00
    单表查询容易看出有哪些索引被命中
    xuanbg
        23
    xuanbg  
       2019-09-03 17:15:43 +08:00
    大多数推崇单表查的程序员,怕是根本不知道索引为何物吧。真懂索引的程序员根本不怕多表 join。
    dog82
        24
    dog82  
       2019-09-03 17:47:19 +08:00
    第一点不是绝对,oracle 可以建基于函数的索引;
    其它表示认同。
    optional
        25
    optional  
       2019-09-03 17:50:41 +08:00
    @Breadykid join 在使用索引的情况下,效率损失不大。
    l00t
        26
    l00t  
       2019-09-03 17:56:07 +08:00
    @dog82 #24 这有什么不绝对的。基于函数的索引那是另一种索引,索引的是字段被函数处理后的样子,而不是当前的索引字段。
    jk1030
        27
    jk1030  
       2019-09-03 18:14:50 +08:00
    @xuanbg ??? 分表分库怎么处理
    xaplux
        28
    xaplux  
       2019-09-03 18:44:54 +08:00
    @Breadykid 因为如果是大表 JOIN 大表,笛卡尔积很可怕的,如果小表就还好呀
    wysnylc
        29
    wysnylc  
       2019-09-03 18:57:56 +08:00
    1.索引字段不使用函数
    2.sql 的 group by 优于内存处理的 list.stream.Collectors.groupingby
    3.联表查首选于单表查
    4.数据量大时,考虑分表

    1 没问题
    2 看情况,比如是需要原始数据+分组后数据的用 stream 肯定好因为用 sql 的 groupBy 要查询两次
    3 煞笔
    4 没问题

    所以你领导 100 分能得 67.5 分,及格而已
    sakuramanstein
        30
    sakuramanstein  
       2019-09-03 19:13:47 +08:00 via Android
    因为 mysql 对联表查有优化啊,比自己拆成几个表分别查要好
    ericls
        31
    ericls  
       2019-09-03 19:20:49 +08:00 via iPhone
    Benchmark it
    xuanbg
        32
    xuanbg  
       2019-09-03 19:35:46 +08:00
    @jk1030 分表之后当然就不能 join 了呀,正常的分页查询都没法做了呢。分表后需要正常分页查询的话,就必须借助 ES 或者 MongoDB 保存索引数据了,或者通过修改交互规避问题。
    Takamine
        33
    Takamine  
       2019-09-03 20:47:32 +08:00
    都丢到业务层处理,多次查询。
    lolizeppelin
        34
    lolizeppelin  
       2019-09-03 21:08:07 +08:00   ❤️ 2
    2 和 3 的核心问题是一样的


    查询过程无外乎


    数据库 应用层
    从硬盘读取数据——数据库解析数据——复制到网路层 ~~~~~~~~ 网络层复制到应用程序——应用程序解析


    数据库开销
    硬盘读取开销~解析开销~复制 /传输开销


    应用层开销
    解析开销~传输开销

    这些省不了的开销,无非是放在哪里而已

    用数据库解析, 数据库 cpu 消耗大,但是传输开销小
    应用解析,数据库 cpu 小,应用服务器 cpu 开销大,传输开销大
    但是数据量大起来以后,传输开销自然就不能忽视了

    如果 group by/join 前的数据远远大于处理后,思考两点
    1.应用层来 group/join,数据库计算量的虽然降低,但是好处是否会被大量传输抵消?
    2.一定量级上数据库层的 group/join 的否效率是否远远好于应用层?
    3.上述一定量级是如何判断?
    4.数据库层的 group/join 是否能降低硬盘 IO ?
    5.应用层 group/join 事务问题怎么解决


    -------------------------------------------------------------------------------------
    1.其他数据库要用函数索引,也需要索引函数对象,例如 index(int(column))
    2.mysql 不能多核并行查询,这个是死穴,当然少量数据肯定比你自己做强多了
    3.mysql 没有 hash join 和 merge join 稍微大的数据 join 起来肯定要死翘翘,但是少量数据又有索引肯定比应用层做好得多.
    至于到底多少数据量算大这个嘛............
    4.分表是下策,mysql 的表分区也是个残疾...上策嘛....换数据库!!

    所以说!!早点换 PG,压根就不纠结这些问题...真要纠结这些问题的时候,就是你换大数据的时候!!
    PG 一统江湖!
    akira
        35
    akira  
       2019-09-04 00:43:46 +08:00
    @Breadykid 3 要针对性的看。没有性能问题的时候 ,该连表连表 , 该怎么写还是怎么写。
    babedoll
        36
    babedoll  
       2019-09-04 08:25:45 +08:00
    联表优于单表,我考虑是老板觉得便于修改。如果做冗余,很可能内容在其他地方已经改了,但是这里还没改,就很麻烦。如果是通过外键联表,能保证数据正确。

    当然我感觉我 sql 学的比较浅,哪个老哥有更好的方法欢迎探讨。
    micean
        37
    micean  
       2019-09-04 08:26:32 +08:00
    就一台服务器+一台数据库
    楼里的兄弟就别谈互联网应用、nosql 了……
    leonme
        38
    leonme  
       2019-09-04 08:43:35 +08:00 via Android
    看 profile,进行性能分析啊,理论指导实践,但是实践的效果还是要具体问题具体分析的,尤其是 sql
    Breadykid
        39
    Breadykid  
    OP
       2019-09-04 11:05:56 +08:00
    @Raymon111111 连表索引问题我可以去哪看,explain 的哪个字段,或者有啥学习资料安利吗
    Breadykid
        40
    Breadykid  
    OP
       2019-09-04 11:10:24 +08:00
    @lolizeppelin pg 党握爪!!!
    lywaei
        41
    lywaei  
       2019-09-04 11:49:25 +08:00
    第三点 根本不是从性能上考虑至少不完全从性能考虑(更多的是维护上)
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2235 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 00:59 · PVG 08:59 · LAX 16:59 · JFK 19:59
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.