如何对 mysql 进行优化的问题

[lopetver]

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

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

[xuanbg]

执行计划发出来

[huichao]

你的 avg, 单独写在一个 function 可能会好点儿， 所有的查询，基本都是先加条件拿出来较少的数据，再来进行其他的逻辑操作，会快很多。

[lopetver]

@xuanbg 你好，5 分钟执行一次，这个是通过每分钟的数据，来计算 5 分钟报表的

[l00t]

@lopetver #3 执行计划不是这个意思…… 执行计划是指数据库对这条语句的执行计划。你 explain 一下看看。

[yeqizhang]

别问，问就是加索引

[l00t]

表里总共 2000 多万数据，你查个 5 分钟也扫了 2000 多万，我怀疑你是不是没加索引。

[aitaii]

2000 万不应该这么慢，explain 看看，索引该加加，别乱加就行

[lijialong1313]

@l00t 查询时间看起来是没命中索引吧。

而且这个 where 1=1 怎么……看着好怪

[aitaii]

另外 oltp 做统计很蛋疼，交给 olap 去做

[totoro52]

不要 1=1 必全表扫描 explain 看下执行计划

[zhaokun]

根据条件先查 ID，拿到 ID 集合再拿其他信息，avg 可以考虑放到代码实现

[v2orz]

@totoro52 1=1 真的会不走索引吗？个人认为是不影响的

[UnAmico]

@totoro52 MySQL 有查询优化器, 1=1 会被优化掉

[yveJohn]

@lopetver #3 执行计划不是说 sql 多久执行一次,而是通过 mysql 的 explain 关键字查看 SQL 在数据库中执行时的表现.如 explan select * from table;

[securityCoding]

@zhaokun monitor_time 有索引的话会回表的 , 楼主这个不发执行计划就只能去瞎猜了

[lopetver]

@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]

1 = 1，查询字段又不仅仅是索引字段，估计扫全表了

[kimqcn]

参考一个类似的老问题:根据 IP 查地址。

[ymz]

@lopetver type = all，没用索引

[raptor]

两步：explain, 加索引

[x66]

全表扫描了，给 monitor_time 加个索引，查询的时候再给时间字符串外面包一个 str_to_date('2020-12-21 15:15:00',’%Y-%m-%d %H:%i:%s’)

[ETO]

@lijialong1313 我们以前项目也是这么用的，为了加 and 条件方便，直接无脑拼接 SQL，而且这个对会被优化器优化掉的吧，应该。

[pabupa]

flink 吧，做统计的话。
或者 canal 自己算。
只用 mysql 的话，再怎么优化也就那样啊。

[securityCoding]

@lopetver 拿你的业务 sql 执行计划

[sidong1993]

@lopetver 发你有问题的 sql 语句的执行计划啊。你发的 select * from d_lampblack_real_time，这个语句执行计划又看不出啥

[ymz]

1 = 1 会被优化

[lopetver]

@ymz 好的，这边优化下吧
@x66 收到，感谢
@sidong1993 OK，我再执行下业务的 sql 语句

[SjwNo1]

什么版本的 mysql, explain 显示没索引。。

[lopetver]

@SjwNo1 mysql5.7.31 的
@sidong1993 已添加到附言

[ymz]

lampblack_01 这个联合索引会不会有点太大，type=index，是二级索引全表扫描，难道你那么多查询字段都在联合索引里？

[securityCoding]

啰嗦一下 ,我提个小建议,v2 这里对于解决问题都是比较热情的,前提是希望你能清晰描述你的问题 , 如果我提类似问题的时候我会这样准备问题资料
1. mysql 版本
2. 业务表的 ddl ,以及数据量
3. 业务 sql 以及执行计划

[lopetver]

@ymz 业务上的事情我也不清楚呢，谢谢了。
@securityCoding 嗯嗯，感谢感谢，是我准备的不全面了，下次我注意

[junan0708]

rows_examined 27379421 扫描的记录数

[sidong1993]

感觉是全表扫了，lampblock_01 索引包含了哪些列，时间加个索引？然后考虑考虑一些数据处理流程放在应用里去做？

[mingszu]

@lopetver 能看看 lampblack_01 的索引信息吗？ show index from d_lampblack_real_time

[lopetver]

@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]

@mingszu lampblack_01 这个联合索引只有两个字段，执行计划的 type 怎么会是 index，大佬知道为什么么？

[sampeng]

哎…你就一个 where 条件。加索引优化是多难？现在写代码这么难了么？

另外如果你是 5.7 以前的版本，时间加索引没什么用。5.7 以后有倒排索引。这是其一。
其二，group 用程序实现…4000 万数据集还让 mysql 干这么重的事昂？

[taogen]

同楼上，where 加索引，group 在程序中做。

[vindurriel]

索引是(code, time) ,查询是 1. range of time, 2. group by code
建议把索引调换一下顺序试试(time, code)

[lijialong1313]

@ETO 如果真的无脑拼接其实反过来更好，就是 XXX AND XXX AND 1=1