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

如何对 mysql 进行优化的问题

  •  1
     
  •   lopetver · 2020-12-29 08:46:57 +08:00 · 3649 次点击
    这是一个创建于 1185 天前的主题,其中的信息可能已经有所发展或是发生改变。

    如下图,mysql 慢日志记录到的查询语句,目前 mysql 的 CPU 使用率达 70%左右

    image.png

    目前这个 d_lampblack_real_time 表中有 2000W 行的数据,请教下各位大佬该如何优化下。

    第 1 条附言  ·  2020-12-29 10:15:22 +08:00

    image.png

    xuanbg
        1
    xuanbg  
       2020-12-29 08:52:37 +08:00   ❤️ 1
    执行计划发出来
    huichao
        2
    huichao  
       2020-12-29 09:00:56 +08:00   ❤️ 1
    你的 avg, 单独写在一个 function 可能会好点儿, 所有的查询,基本都是先加条件拿出来较少的数据,再来进行其他的逻辑操作,会快很多。
    lopetver
        3
    lopetver  
    OP
       2020-12-29 09:01:10 +08:00
    @xuanbg 你好,5 分钟执行一次,这个是通过每分钟的数据,来计算 5 分钟报表的
    l00t
        4
    l00t  
       2020-12-29 09:09:08 +08:00   ❤️ 1
    @lopetver #3 执行计划不是这个意思…… 执行计划是指数据库对这条语句的执行计划。你 explain 一下看看。
    yeqizhang
        5
    yeqizhang  
       2020-12-29 09:09:57 +08:00 via Android   ❤️ 1
    别问,问就是加索引
    l00t
        6
    l00t  
       2020-12-29 09:10:04 +08:00   ❤️ 1
    表里总共 2000 多万数据,你查个 5 分钟也扫了 2000 多万,我怀疑你是不是没加索引。
    aitaii
        7
    aitaii  
       2020-12-29 09:12:48 +08:00 via iPhone   ❤️ 1
    2000 万不应该这么慢,explain 看看,索引该加加,别乱加就行
    lijialong1313
        8
    lijialong1313  
       2020-12-29 09:13:39 +08:00   ❤️ 1
    @l00t 查询时间看起来是没命中索引吧。

    而且这个 where 1=1 怎么……看着好怪
    aitaii
        9
    aitaii  
       2020-12-29 09:14:37 +08:00 via iPhone   ❤️ 1
    另外 oltp 做统计很蛋疼,交给 olap 去做
    totoro52
        10
    totoro52  
       2020-12-29 09:21:20 +08:00   ❤️ 1
    不要 1=1 必全表扫描 explain 看下执行计划
    zhaokun
        11
    zhaokun  
       2020-12-29 09:28:52 +08:00   ❤️ 1
    根据条件先查 ID,拿到 ID 集合再拿其他信息,avg 可以考虑放到代码实现
    v2orz
        12
    v2orz  
       2020-12-29 09:42:32 +08:00   ❤️ 1
    @totoro52 1=1 真的会不走索引吗?个人认为是不影响的
    UnAmico
        13
    UnAmico  
       2020-12-29 09:50:35 +08:00   ❤️ 2
    @totoro52 MySQL 有查询优化器, 1=1 会被优化掉
    yveJohn
        14
    yveJohn  
       2020-12-29 09:51:47 +08:00   ❤️ 1
    @lopetver #3 执行计划不是说 sql 多久执行一次,而是通过 mysql 的 explain 关键字查看 SQL 在数据库中执行时的表现.如 explan select * from table;
    securityCoding
        15
    securityCoding  
       2020-12-29 09:58:52 +08:00   ❤️ 1
    @zhaokun monitor_time 有索引的话会回表的 , 楼主这个不发执行计划就只能去瞎猜了
    lopetver
        16
    lopetver  
    OP
       2020-12-29 09:59:37 +08:00
    @xuanbg
    @yveJohn

    执行结果如下

    MySQL [lampblack]> explain select * from d_lampblack_real_time;
    +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+
    | 1 | SIMPLE | d_lampblack_real_time | NULL | ALL | NULL | NULL | NULL | NULL | 5254449 | 100.00 | NULL |
    +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+
    1 row in set, 1 warning (0.02 sec)
    ymz
        17
    ymz  
       2020-12-29 10:00:09 +08:00   ❤️ 1
    1 = 1,查询字段又不仅仅是索引字段,估计扫全表了
    kimqcn
        18
    kimqcn  
       2020-12-29 10:03:06 +08:00   ❤️ 1
    参考一个类似的老问题:根据 IP 查地址。
    ymz
        19
    ymz  
       2020-12-29 10:03:56 +08:00   ❤️ 1
    @lopetver type = all,没用索引
    raptor
        20
    raptor  
       2020-12-29 10:05:04 +08:00   ❤️ 1
    两步:explain, 加索引
    x66
        21
    x66  
       2020-12-29 10:05:23 +08:00   ❤️ 2
    全表扫描了,给 monitor_time 加个索引,查询的时候再给时间字符串外面包一个 str_to_date('2020-12-21 15:15:00',’%Y-%m-%d %H:%i:%s’)
    ETO
        22
    ETO  
       2020-12-29 10:05:40 +08:00   ❤️ 1
    @lijialong1313 我们以前项目也是这么用的,为了加 and 条件方便,直接无脑拼接 SQL,而且这个对会被优化器优化掉的吧,应该。
    pabupa
        23
    pabupa  
       2020-12-29 10:06:26 +08:00 via Android   ❤️ 1
    flink 吧,做统计的话。
    或者 canal 自己算。
    只用 mysql 的话,再怎么优化也就那样啊。
    securityCoding
        24
    securityCoding  
       2020-12-29 10:06:46 +08:00   ❤️ 1
    @lopetver 拿你的业务 sql 执行计划
    sidong1993
        25
    sidong1993  
       2020-12-29 10:07:02 +08:00   ❤️ 1
    @lopetver 发你有问题的 sql 语句的执行计划啊。你发的 select * from d_lampblack_real_time,这个语句执行计划又看不出啥
    ymz
        26
    ymz  
       2020-12-29 10:07:45 +08:00   ❤️ 1
    1 = 1 会被优化
    lopetver
        27
    lopetver  
    OP
       2020-12-29 10:13:05 +08:00
    @ymz 好的,这边优化下吧
    @x66 收到,感谢
    @sidong1993 OK,我再执行下业务的 sql 语句
    SjwNo1
        28
    SjwNo1  
       2020-12-29 10:14:05 +08:00   ❤️ 1
    什么版本的 mysql, explain 显示没索引。。
    lopetver
        29
    lopetver  
    OP
       2020-12-29 10:16:21 +08:00
    @SjwNo1 mysql5.7.31 的
    @sidong1993 已添加到附言
    ymz
        30
    ymz  
       2020-12-29 10:35:21 +08:00
    lampblack_01 这个联合索引会不会有点太大,type=index,是二级索引全表扫描,难道你那么多查询字段都在联合索引里?
    securityCoding
        31
    securityCoding  
       2020-12-29 10:38:29 +08:00
    啰嗦一下 ,我提个小建议,v2 这里对于解决问题都是比较热情的,前提是希望你能清晰描述你的问题 , 如果我提类似问题的时候我会这样准备问题资料
    1. mysql 版本
    2. 业务表的 ddl ,以及数据量
    3. 业务 sql 以及执行计划
    lopetver
        32
    lopetver  
    OP
       2020-12-29 10:41:52 +08:00
    @ymz 业务上的事情我也不清楚呢,谢谢了。
    @securityCoding 嗯嗯,感谢感谢,是我准备的不全面了,下次我注意
    junan0708
        33
    junan0708  
       2020-12-29 10:57:08 +08:00
    rows_examined 27379421 扫描的记录数
    sidong1993
        34
    sidong1993  
       2020-12-29 14:40:42 +08:00
    感觉是全表扫了,lampblock_01 索引包含了哪些列,时间加个索引?然后考虑考虑一些数据处理流程放在应用里去做?
    mingszu
        35
    mingszu  
       2020-12-29 14:43:08 +08:00
    @lopetver 能看看 lampblack_01 的索引信息吗? show index from d_lampblack_real_time
    lopetver
        36
    lopetver  
    OP
       2020-12-29 14:59:53 +08:00
    @mingszu

    +-----------------------+------------+--------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +-----------------------+------------+--------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | d_lampblack_real_time | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
    | d_lampblack_real_time | 0 | lampblack_01 | 1 | equipment_code | A | 0 | NULL | NULL | | BTREE | | |
    | d_lampblack_real_time | 0 | lampblack_01 | 2 | monitor_time | A | 0 | NULL | NULL | | BTREE | | |
    | d_lampblack_real_time | 1 | lampblack_02 | 1 | fs | A | 0 | NULL | NULL | YES | BTREE | | |
    | d_lampblack_real_time | 1 | lampblack_02 | 2 | ps | A | 0 | NULL | NULL | YES | BTREE | | |
    +-----------------------+------------+--------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    5 rows in set (0.00 sec)

    现在已经把这个业务停了,应该看不出来原因了。回头再用了,这边再看下吧
    ymz
        37
    ymz  
       2020-12-29 15:38:50 +08:00
    @mingszu lampblack_01 这个联合索引只有两个字段,执行计划的 type 怎么会是 index,大佬知道为什么么?
    sampeng
        38
    sampeng  
       2020-12-29 18:25:01 +08:00 via iPhone
    哎…你就一个 where 条件。加索引优化是多难?现在写代码这么难了么?

    另外如果你是 5.7 以前的版本,时间加索引没什么用。5.7 以后有倒排索引。这是其一。
    其二,group 用程序实现…4000 万数据集还让 mysql 干这么重的事昂?
    taogen
        39
    taogen  
       2020-12-29 19:48:01 +08:00 via Android
    同楼上,where 加索引,group 在程序中做。
    vindurriel
        40
    vindurriel  
       2020-12-29 20:18:23 +08:00 via iPhone
    索引是(code, time) ,查询是 1. range of time, 2. group by code
    建议把索引调换一下顺序试试(time, code)
    lijialong1313
        41
    lijialong1313  
       2020-12-30 09:33:16 +08:00
    @ETO 如果真的无脑拼接其实反过来更好,就是 XXX AND XXX AND 1=1
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   979 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 38ms · UTC 20:18 · PVG 04:18 · LAX 13:18 · JFK 16:18
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.