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

请教 MySQL 查询统计性能问题

  •  
  •   jowan · 2018-07-19 14:07:33 +08:00 · 3485 次点击
    这是一个创建于 2079 天前的主题,其中的信息可能已经有所发展或是发生改变。

    遇到一个问题,现业务中有一张表如下,一共有 650W 条数据, cid = 1 有 9.6W 条数据,cid = 4 有 354W 条数据, 本地查询 A、C、D 的速度还可以接受,大概在 2s 左右, 查询 B 比较慢,耗费了 30s,

    本人数据库优化这方面不是太懂, 请问一下各位这是什么原因,如何优化呢?

    表结构

    CREATE TABLE `wei_money_log` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
      `cid` tinyint(2) NOT NULL,
      `mid` int(11) NOT NULL,
      `type` tinyint(1) NOT NULL,
      `money` decimal(16,2) NOT NULL,
      `log` varchar(120) NOT NULL,
      `addtime` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `mid` (`mid`),
      KEY `cid` (`cid`),
      KEY `type` (`type`),
      KEY `money` (`money`),
      KEY `addtime` (`addtime`),
      KEY `log` (`log`),
      CONSTRAINT `wei_money_log_ibfk_1` FOREIGN KEY (`mid`) REFERENCES `wei_member` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    查询 A:

    SELECT SUM(`money`) AS `money`
    FROM `wei_money_log` 
    

    enter description here

    查询 B:

    SELECT SUM(`money`) AS `money`
    FROM `wei_money_log` WHERE cid = 4
    

    enter description here

    查询 C:

    SELECT SUM(`money`) AS `money`
    FROM `wei_money_log` WHERE cid = 1
    

    enter description here

    查询 D:

    SELECT SUM(`money`) AS `money`
    FROM `wei_money_log` WHERE id < 10000000
    

    enter description here

    第 1 条附言  ·  2018-07-19 14:51:48 +08:00
    问题解决了,900ms,加了 cid/money 的联合索引,感谢 @glacer
    17 条回复
    chenset
        1
    chenset  
       2018-07-19 14:15:36 +08:00
    这表全是索引...
    chenset
        2
    chenset  
       2018-07-19 14:16:52 +08:00
    B 的问题主要是 cid = 4 有 354W 条数据 导致的吧.
    jowan
        3
    jowan  
    OP
       2018-07-19 14:19:42 +08:00
    @chenset 实际业务中每个字段有检索条件 就都建了索引
    刚试了下 id not in(1,2,3)的速度 就正常了 但是 = 4 就慢
    Rekkles
        4
    Rekkles  
       2018-07-19 14:19:50 +08:00
    你这个条件太宽泛 而且索引设置的也有问题 建议高频次查询数据存 redis
    likuku
        5
    likuku  
       2018-07-19 14:25:55 +08:00
    D ... < 10000000 ... 这得扫非常多的记录了,看起来这需求上也没办法了。

    即时性很高?若即时要求不高(只需要给出前一个工作日 /上一个月),那么可以另外弄张表,
    专门存储类似 table_name, sum 这样的 sum 记录表,每天深夜定时统计更新一次。

    即时性很高,那么弄个触发器 /wei_money_log 这种数据变更事务里多加一个同时去更新 sum 记录表对应记录。
    lookatme
        6
    lookatme  
       2018-07-19 14:27:33 +08:00
    总共才 650w,cid=4 都有 300w,用索引反而没有全表遍历快
    linpf
        7
    linpf  
       2018-07-19 14:28:53 +08:00
    @jowan not in 不应该是效率非常差吗? 为什么会比直接=4 要快? 我的世界观崩了
    jowan
        8
    jowan  
    OP
       2018-07-19 14:30:44 +08:00
    @linpf 当前这个场景 要快 而且快 15 倍多 耗时 2.8s ,直接=4,33s
    jowan
        9
    jowan  
    OP
       2018-07-19 14:31:47 +08:00
    @likuku < 10000000 这里只做个测试对比的,耗时 2.8s ,跟 cid=4 比起来 还是快多了
    likuku
        10
    likuku  
       2018-07-19 14:32:55 +08:00
    @jowan 不好意思,看走眼了...原来最慢的是 B 而不是 D ...
    linpf
        11
    linpf  
       2018-07-19 14:34:13 +08:00
    @jowan 这…… not in 不是不能利用到索引吗? =4 不是可以利用到索引吗
    f4nyc
        12
    f4nyc  
       2018-07-19 14:38:11 +08:00 via iPhone
    因为 innodb 是聚簇索引啊,cid 是二级索引,先在二级索引查找到对应主键的值,然后再查聚簇索引,300w 次 O(logn),当然没有直接遍历 O(n)来的快,差不多就是这个时间比
    怀疑你的索引是无脑加的,推荐高性能 mysql
    f4nyc
        13
    f4nyc  
       2018-07-19 14:38:46 +08:00 via iPhone
    @linpf 聚簇索引了解一下
    jowan
        14
    jowan  
    OP
       2018-07-19 14:39:54 +08:00
    @f4nyc 因为实际业务中用到哪些检索条件就加了哪些索引,是无脑加的 (逃
    singer
        15
    singer  
       2018-07-19 14:41:22 +08:00 via iPhone
    你的 cid,type,money 加索引没有意义啊。
    可以按年份或者月份分表。都 30s 了,分表平均一下速度。
    还有就是日志建议设置热点数据,只能查 6 个月内的数据
    glacer
        16
    glacer  
       2018-07-19 14:42:17 +08:00   ❤️ 2
    A 查询走的全表却比 B 查询快,原因是因为 A 用到了覆盖索引(即索引是 money 而查询字段也是 money,那么数据就可以直接在索引上获得了。B 查询用的索引是 cid 而字段还是 money,在索引到行 id 后还需要根据 id 查找数据)。
    这里的要优化 B 的话可以按照这个思路,建一个(cid, money)的索引,这样 B 也就能用到覆盖索引了。
    当然你这个表全是索引了...索引不是这样建的,这样索引会比数据还大,要根据实际查询的情况而定。
    jowan
        17
    jowan  
    OP
       2018-07-19 14:44:36 +08:00
    @singer 我也想限制 业务要求 哎。。。
    @glacer 这是个后台统计账单功能,后台每个条件都能搭配检索,所以都无脑建了索引,加个 cid-money 的覆盖索引试试
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2995 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 14:42 · PVG 22:42 · LAX 07:42 · JFK 10:42
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.