MySQL 排序慢

Question

我有一个 barnyard2 和 snort 正在运行。

我正在尝试连接 3 个表，这些表本身工作正常。当按 cid 订购选择时，它的速度真的很糟糕。在这里您可以看到两个选择以及它们之间的时间。

在没有订单的情况下运行:

mysql> SELECT event.sid, event.cid, event.timestamp, iphdr.ip_src, iphdr.ip_dst, 
      signature.sig_name 
      FROM event 
      INNER JOIN iphdr 
      ON event.cid=iphdr.cid 
      INNER JOIN signature 
      ON event.signature=signature.sig_id 
      WHERE sig_name like 'ICMP%' limit 10;
<.....>
10 rows in set (0.02 sec)

按以下顺序运行:

mysql> SELECT event.sid, event.cid, event.timestamp, iphdr.ip_src, iphdr.ip_dst, 
          signature.sig_name 
       FROM event 
       INNER JOIN iphdr 
       ON event.cid=iphdr.cid 
       INNER JOIN signature 
       ON event.signature=signature.sig_id 
       WHERE sig_name like 'ICMP%' 
       order by event.cid desc limit 10;
<.....>
10 rows in set (6 min 1.52 sec)

描述事件；

 +-----------+------------------+------+-----+---------+-------+
 | Field     | Type             | Null | Key | Default | Extra |
 +-----------+------------------+------+-----+---------+-------+
 | sid       | int(10) unsigned | NO   | PRI | NULL    |       |
 | cid       | int(10) unsigned | NO   | PRI | NULL    |       |
 | signature | int(10) unsigned | NO   | MUL | NULL    |       |
 | timestamp | datetime         | NO   | MUL | NULL    |       |
 +-----------+------------------+------+-----+---------+-------+
 4 rows in set (0.00 sec)

desc签名；

 +--------------+------------------+------+-----+---------+----------------+
 | Field        | Type             | Null | Key | Default | Extra          |
 +--------------+------------------+------+-----+---------+----------------+
 | sig_id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
 | sig_name     | varchar(255)     | NO   | MUL | NULL    |                |
 | sig_class_id | int(10) unsigned | NO   | MUL | NULL    |                |
 | sig_priority | int(10) unsigned | YES  |     | NULL    |                |
 | sig_rev      | int(10) unsigned | YES  |     | NULL    |                |
 | sig_sid      | int(10) unsigned | YES  |     | NULL    |                |
 | sig_gid      | int(10) unsigned | YES  |     | NULL    |                |
 +--------------+------------------+------+-----+---------+----------------+
 7 rows in set (0.00 sec)

描述iphdr；

 +----------+----------------------+------+-----+---------+-------+
 | Field    | Type                 | Null | Key | Default | Extra |
 +----------+----------------------+------+-----+---------+-------+
 | sid      | int(10) unsigned     | NO   | PRI | NULL    |       |
 | cid      | int(10) unsigned     | NO   | PRI | NULL    |       |
 | ip_src   | int(10) unsigned     | NO   | MUL | NULL    |       |
 | ip_dst   | int(10) unsigned     | NO   | MUL | NULL    |       |
 | ip_ver   | tinyint(3) unsigned  | YES  |     | NULL    |       |
 | ip_hlen  | tinyint(3) unsigned  | YES  |     | NULL    |       |
 | ip_tos   | tinyint(3) unsigned  | YES  |     | NULL    |       |
 | ip_len   | smallint(5) unsigned | YES  |     | NULL    |       |
 | ip_id    | smallint(5) unsigned | YES  |     | NULL    |       |
 | ip_flags | tinyint(3) unsigned  | YES  |     | NULL    |       |
 | ip_off   | smallint(5) unsigned | YES  |     | NULL    |       |
 | ip_ttl   | tinyint(3) unsigned  | YES  |     | NULL    |       |
 | ip_proto | tinyint(3) unsigned  | NO   |     | NULL    |       |
 | ip_csum  | smallint(5) unsigned | YES  |     | NULL    |       |
 +----------+----------------------+------+-----+---------+-------+
 14 rows in set (0.00 sec)

好久没用MySQL了，查询能力不是很好。

Answer 1

event.cid 似乎没有索引。它是主键的一部分，但如果它在该键中排在第二位，则不能用于排序。因此，您的点餐速度很慢。

如果您查看 EXPLAIN，您可能会看到它。请执行 EXPLAIN，然后为该列添加索引，并再次检查 EXPLAIN。它可能会有所帮助:)