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
xiaoyaocmx
V2EX  ›  MySQL

求助 MySQL 建表 schema 的优化

  •  
  •   xiaoyaocmx · 2019-10-31 10:23:48 +08:00 · 5286 次点击
    这是一个创建于 1849 天前的主题,其中的信息可能已经有所发展或是发生改变。

    v 友们大家好… 我现在做的 project 要求对于 mysql 的 query 尽可能的做优化,完全没有插入和更新操作,纯粹 get。现在定的 schema 是 user id 和 timestamp,对于 userid 做了 index。我现在想优化 range query,比如对于如下 query “user id 在范围 A~B, tiemestamp 在 C~D 之间”,要返回所有行。

    请问有什么其他可以优化的点吗,我在看 mysql 的官方文档时发现用 BETWEEN ... AND 可以提高速度,但是 database 这边还可以做什么优化呢?有针对 range 做的 index 吗?感谢大家!

    26 条回复    2019-10-31 21:08:16 +08:00
    maierhuang
        1
    maierhuang  
       2019-10-31 10:28:58 +08:00
    建立 user id 和 tiemestamp 的联合索引
    Jrue0011
        2
    Jrue0011  
       2019-10-31 10:36:23 +08:00
    https://github.com/Meituan-Dianping/SQLAdvisor
    之前无意中找到的,但是没用过,不知道有没有用
    xiaoyaocmx
        3
    xiaoyaocmx  
    OP
       2019-10-31 10:55:29 +08:00   ❤️ 1
    @Jrue0011 谢谢!我也在读美团他们写的优化慢查询的方法,真的很有帮助 https://tech.meituan.com/2014/06/30/mysql-index.html
    xiaoyaocmx
        4
    xiaoyaocmx  
    OP
       2019-10-31 10:56:02 +08:00
    @maierhuang 恩恩 我们也准备这么做,准备做两个实验看看哪个在前 performance 更好
    xiaoyaocmx
        5
    xiaoyaocmx  
    OP
       2019-10-31 10:58:18 +08:00
    补充:只有一个 table,所以没有 join 的 overhead,也没有 order by 操作
    xiaoyaocmx
        6
    xiaoyaocmx  
    OP
       2019-10-31 11:00:17 +08:00
    已经 enable caching
    qwerthhusn
        7
    qwerthhusn  
       2019-10-31 11:14:54 +08:00
    既然都没有修改操作,可以尝试一下 MyIASM,查询是比 InnoDB 快的
    taogen
        8
    taogen  
       2019-10-31 11:52:56 +08:00 via Android
    range query 用 B+ tree index 已经比较高效率的了

    数据量太大,建议水平拆分。
    xiaoyaocmx
        9
    xiaoyaocmx  
    OP
       2019-10-31 12:09:16 +08:00
    @qwerthhusn 有道理,查了一下发现确实可能可行,之后我试试! 感谢
    xiaoyaocmx
        10
    xiaoyaocmx  
    OP
       2019-10-31 12:10:58 +08:00
    @taogen 水平拆分是指用多个 table 吗?还是说 sharding 到不同的 machine 上呀?如果 query 的 range 恰好 cross table 的话,performance 不会下降吗。。当然这只是我主观猜测
    xiaoyaocmx
        11
    xiaoyaocmx  
    OP
       2019-10-31 12:13:57 +08:00
    补充:数据量在 15G 左右,可能只有一些(相对而言比较少)常遇到的 range query,但是这个我们需要 log query 才能发现 pattern。。
    hantsy
        12
    hantsy  
       2019-10-31 12:18:19 +08:00
    换 Elastic Search 对应海量数据查询。
    ddup
        13
    ddup  
       2019-10-31 12:28:33 +08:00 via Android
    分区表了解一下
    wangyzj
        14
    wangyzj  
       2019-10-31 12:51:19 +08:00
    加入默认自增 id 主键
    innodb
    userid 索引
    xiaoyaocmx
        15
    xiaoyaocmx  
    OP
       2019-10-31 12:54:07 +08:00
    @hantsy project 有要求,不能用 es。。只考虑 mysql 的 tuning、schema 的 design
    xiaoyaocmx
        16
    xiaoyaocmx  
    OP
       2019-10-31 12:55:38 +08:00
    @wangyzj 谢谢,现在是 innodb,已经对 userid 做了索引,按照 1 楼的建议,准备做 userid 和 timestamp 的 composite index。但是自增 id 做主键是什么意思呢?
    xiaoyaocmx
        17
    xiaoyaocmx  
    OP
       2019-10-31 13:00:54 +08:00
    @ddup 看了一下,发现基于 timestamp 做 range 分区应该会有帮助,但是怎么分还要根据 query 的 statistics 来决定,加到 todo 的实验里了……谢啦
    wangyzj
        18
    wangyzj  
       2019-10-31 13:01:26 +08:00
    @xiaoyaocmx 推荐有个自增 id 会效果好一些,uid 如果是自增也行
    xiaoyaocmx
        19
    xiaoyaocmx  
    OP
       2019-10-31 13:13:56 +08:00
    @wangyzj 好的,那我想的是 load 完数据后对 user id 这个 column 做个 sorting,保证 ascending order,应该可以达到自增 id 的效果
    hantsy
        20
    hantsy  
       2019-10-31 15:16:20 +08:00
    @xiaoyaocmx 有工具可以同步在 ES 建索引的,搜索的时候用 ES,其他 Insert 什么的还是用 MySQL。MySQL 一张表数据量太大了,怎么优化都没有用,查询性能比 PG 差很多。
    littleylv
        21
    littleylv  
       2019-10-31 15:27:22 +08:00
    歪个楼,楼主是海归,或者在外企,或者在国外?

    否则这种中文中夹杂着不是必要用英文的英文,实话说挺反感的
    Canvas26
        22
    Canvas26  
       2019-10-31 15:45:50 +08:00
    @littleylv 也就看到“项目”和“索引”两个可以翻译过来的,其他 mysql 的名词不翻译更自然吧
    littleylv
        23
    littleylv  
       2019-10-31 15:52:14 +08:00
    @Canvas26 #22
    “query”一般叫查询没问题吧
    “performance”叫性能也没问题吧
    “overhead”
    “还是说 sharding 到不同的 machine 上呀”
    “这个 column 做个 sorting”
    crclz
        24
    crclz  
       2019-10-31 18:59:25 +08:00
    看看平均情况下,符合 timestamp 筛选条件的行多,还是符合纯 userid 筛选条件的行多。
    假如符合 userid 筛选条件的范围的行少,那么就在 userid 加聚簇索引。不敢保证是最优的。
    然后和 index(userid,timestamp)还有 index(timestamp, userid)比一比。
    最后记得延迟关联。
    xiaoyaocmx
        25
    xiaoyaocmx  
    OP
       2019-10-31 21:04:41 +08:00 via iPhone
    @littleylv 谢谢,身边同学讨论这么惯了没注意到…下次注意哈
    xiaoyaocmx
        26
    xiaoyaocmx  
    OP
       2019-10-31 21:08:16 +08:00 via iPhone
    @hantsy 好的,我研究一下…感谢
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2788 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 12:51 · PVG 20:51 · LAX 04:51 · JFK 07:51
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.