mysql group by 查询的性能问题

Question

我遇到性能问题。我有一个名为 Alarmes 的表，有 20 列。在此表中，我只对 2 列感兴趣(ALM_TAGNAME 和 ALM_LOGNODENAME)。该表是从另一个程序自动创建和更新的，我只想进行统计。好吧，我在 ALM_TAGNAME 和 ALM_LOGNODENAME 两列上添加了一个键，但即使使用此键，选择也会花费 40 秒以上的时间来执行。我只是减少了行数，但仍然超过 100 万行。

SELECT ALM_TAGNAME as 'tag', count(*) as  'cnt'    FROM Alarmes WHERE ALM_LOGNODENAME='plantid' GROUP BY ALM_TAGNAME; 

CREATE TABLE Alarmes (
ALM_NATIVETIMEIN datetime NOT NULL, ALM_NATIVETIMELAST datetime default NULL, ALM_LOGNODENAME varchar(50) character set utf8 default NULL,
ALM_PHYSLNODE varchar(50) character set utf8 default NULL, ALM_TAGNAME varchar(50) character set utf8 default NULL, ALM_TAGDESC varchar(50) character set utf8 default NULL,
ALM_VALUE varchar(50) character set utf8 default NULL, ALM_UNIT varchar(50) character set utf8 default NULL, ALM_MSGTYPE varchar(50) character set utf8 default NULL,
ALM_DESCR varchar(255) character set utf8 default NULL, ALM_ALMSTATUS varchar(50) character set utf8 default NULL, ALM_ALMPRIORITY varchar(50) character set utf8 default NULL,
ALM_ALMAREA varchar(50) character set utf8 default NULL,
ALM_ALMEXTFLD1 varchar(50) character set utf8 default NULL,
ALM_ALMEXTFLD2 varchar(50) character set utf8 default NULL, ALM_OPNAME varchar(50) character set utf8 default NULL, ALM_OPFULLNAME varchar(50) character set utf8 default NULL,
ALM_OPNODE varchar(50) character set utf8 default NULL, ALM_PERFNAME varchar(50) character set utf8 default NULL, ALM_PERFFULLNAME varchar(50) character set utf8 default NULL,
ALM_PERFBYCOMMENT varchar(50) character set utf8 default NULL,
ALM_VERNAME varchar(50) character set utf8 default NULL,
ALM_VERFULLNAME varchar(50) character set utf8 default NULL,
ALM_VERBYCOMMENT varchar(50) character set utf8 default NULL,
ALM_DATEIN varchar(50) character set utf8 default NULL, ALM_TIMEIN varchar(50) character set utf8 default NULL, ALM_DATELAST varchar(50) character set utf8 default NULL,
ALM_TIMELAST varchar(50) character set utf8 default NULL, KEY Alarmes_tagname (ALM_TAGNAME),
KEY Alarmes_lognodname (ALM_LOGNODENAME) )

EXPLAIN SELECT ALM_TAGNAME as 'tag', count(ALM_TAGNAME) as  'cnt'    FROM monitor.Alarmes   WHERE ALM_LOGNODENAME='bogota'   GROUP BY ALM_TAGNAME;    +----+-------------+---------+------+--------------------+--------------------+---------+-------+-------+----------------------------------------------+
| id | select_type | table   | type | possible_keys      | key                | key_len | ref   | rows  | Extra                                        |
+----+-------------+---------+------+--------------------+--------------------+---------+-------+-------+----------------------------------------------+
|  1 | SIMPLE      | Alarmes | ref  | Alarmes_lognodname | Alarmes_lognodname | 153     | const | 86143 | Using where; Using temporary; Using filesort |
+----+-------------+---------+------+--------------------+--------------------+---------+-------+-------+----------------------------------------------+

 DESCRIBE SELECT ALM_TAGNAME as 'tag', count(ALM_TAGNAME) as  'cnt'    FROM monitor.Alarmes   WHERE ALM_LOGNODENAME='bogota'   GROUP BY ALM_TAGNAME;
+----+-------------+---------+------+--------------------+--------------------+---------+-------+-------+----------------------------------------------+
| id | select_type | table   | type | possible_keys      | key                | key_len | ref   | rows  | Extra                                        |
+----+-------------+---------+------+--------------------+--------------------+---------+-------+-------+----------------------------------------------+
|  1 | SIMPLE      | Alarmes | ref  | Alarmes_lognodname | Alarmes_lognodname | 153     | const | 86154 | Using where; Using temporary; Using filesort |
+----+-------------+---------+------+--------------------+--------------------+---------+-------+-------+----------------------------------------------+

Answer 1

当您说“嗯，我在 ALM_TAGNAME 和 ALM_LOGNODENAME 两列上添加了一个键”时，您是在 ALM_TAGNAME+ALM_LOGNODENAME 上添加一个 INDEX 还是在两个索引上添加一个键？

如果您添加 ALM_TAGNAME+ALM_LOGNODENAME，请尝试在 ALM_TAGNAME 上仅添加一个索引。