具有组的查询的mysql性能问题

Question

我有表演上的问题。我有一张有20列的桌子，名叫Alarmes。在这个表中，我只对2列(ALM_TAGNAME和ALM_LOGNODENAME)感兴趣。表是从另一个程序中自动创建和更新的，我只想做一个统计。好的，我在两个列ALM_TAGNAME和ALM_LOGNODENAME上添加了一个键，但是即使有了这个键，select也需要超过40秒来执行。我只是减少了线的数量，但它仍然超过100万行。

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

创建表Alarmes (

ALM_NATIVETIMEIN日期时间不为空，ALM_NATIVETIMELAST日期时间默认值为空，ALM_LOGNODENAME varchar(50)字符集utf8默认值为空，

ALM_PHYSLNODE varchar(50)字符集utf8默认值为空，ALM_TAGNAME varchar(50)字符集utf8默认值为空，ALM_TAGDESC varchar(50)字符集utf8默认值为空，

ALM_VALUE varchar(50)字符集utf8默认值为空，ALM_UNIT varchar(50)字符集utf8默认值为空，ALM_MSGTYPE varchar(50)字符集utf8默认值为空，

ALM_DESCR varchar(255)字符集utf8默认值为空，ALM_ALMSTATUS varchar(50)字符集utf8默认值为空，ALM_ALMPRIORITY varchar(50)字符集utf8默认值为空，

ALM_ALMAREA varchar(50)字符集utf8默认为空，

ALM_ALMEXTFLD1 varchar(50)字符集utf8默认为空，

ALM_ALMEXTFLD2 varchar(50)字符集utf8默认值为空，ALM_OPNAME varchar(50)字符集utf8默认值为空，ALM_OPFULLNAME varchar(50)字符集utf8默认值为空，

ALM_OPNODE varchar(50)字符集utf8默认值为空，ALM_PERFNAME varchar(50)字符集utf8默认值为空，ALM_PERFFULLNAME varchar(50)字符集utf8默认值为空，

ALM_PERFBYCOMMENT varchar(50)字符集utf8默认为空，

ALM_VERNAME varchar(50)字符集utf8默认为空，

ALM_VERFULLNAME varchar(50)字符集utf8默认为空，

ALM_VERBYCOMMENT varchar(50)字符集utf8默认为空，

ALM_DATEIN varchar(50)字符集utf8默认值为空，ALM_TIMEIN varchar(50)字符集utf8默认值为空，ALM_DATELAST varchar(50)字符集utf8默认值为空，

ALM_TIMELAST varchar(50)字符集utf8默认值为空，键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上添加索引还是添加两个索引？

如果添加ALM_TAGNAME+ALM_LOGNODENAME，则只在ALM_TAGNAME上添加一个索引。

Answer 2

数据更改到需要计数(*)s.的频率有多高，您是否考虑过每晚(甚至每小时)预聚集表以从该表获取查询/计数。然后，您对标记名称/日志节点名称上的表的查询将是一条记录并完成。