mysql - 查询速度太慢，如何优化？

Question

我的数据库包含大量数据，但我在查询优化方面遇到问题。我怎样才能优化这个查询

explain SELECT distinct UNIX_TIMESTAMP(timestamp)*1000 as timestamp,count(ip_src) as counter from acid_event a,saher.network n where inet_aton(n.net)!=a.ip_src group by timestamp;
+----+-------------+-------+-------+---------------+-------------+---------   +------+---------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows    | Extra                                        |
+----+-------------+-------+-------+---------------+-------------+---------+------+---------+----------------------------------------------+
|  1 | SIMPLE      | n     | index | NULL          | fsi         | 27      | NULL | 2691027 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | index | NULL          | timestamp_2 | 13      | NULL | 6770718 | Using where; Using index; Using join buffer  |
+----+-------------+-------+-------+---------------+-------------+---------+------+---------+----------------------------------------------+

Answer 1

1. 尝试通过例如以下方式摆脱 INET_ATON()将包含 IP 地址数值的新列添加到您的 acid_event 表

2. 然后，如果结果集很大(例如数千)，则去掉 UNIX_TIMESTAMP(timestamp)*1000

3. 如果这还不够，请修复索引。这是 book这深入解释了索引。