优化查询性能

Question

我的应用程序(使用php)性能非常慢，因为我的mysql表中有更多的数据，我的表结构如下所示。

`CREATE TABLE `xxxxx` 
(`ID` int(11) NOT NULL AUTO_INCREMENT,
 `IP` varchar(45) NOT NULL,
 `Cname` varchar(45) NOT NULL,
 `Port` int(5) NOT NULL DEFAULT'3306',
 `Variable` varchar(150) DEFAULT NULL,
 `status` varchar(100) DEFAULT NULL,
 `moddate` datetime NOT NULL,
 `Servertime` datetime NOT NULL,
 PRIMARY KEY (`ID`,`moddate`),
 KEY `idx_variable` (`Variable`) ) 
  ENGINE=InnoDB AUTO_INCREMENT=85054928 DEFAULT CHARSET=ucs2
    /*!50100 PARTITION BY LIST ( MONTH (moddate))
    (PARTITION Jan VALUES IN (1) ENGINE = InnoDB,
    PARTITION Feb VALUES IN (2) ENGINE = InnoDB,
    PARTITION Mar VALUES IN (3) ENGINE = InnoDB,
    PARTITION Apr VALUES IN (4) ENGINE = InnoDB,
    PARTITION May VALUES IN (5) ENGINE = InnoDB,
    PARTITION Jun VALUES IN (6) ENGINE = InnoDB,
    PARTITION Jul VALUES IN (7) ENGINE = InnoDB,
    PARTITION Aug VALUES IN (8) ENGINE = InnoDB,
    PARTITION Sep VALUES IN (9) ENGINE = InnoDB,
    PARTITION Oct VALUES IN (10) ENGINE = InnoDB,
    PARTITION Nov VALUES IN (11) ENGINE = InnoDB,
    PARTITION DECM VALUES IN (12) ENGINE = InnoDB) */`

它有大约4.2 GB的数据。我的获取数据的select语句是，

SELECT 
      `status`,
      `moddate` 
   FROM 
      `xxxxxx` 
   WHERE 
          Cname='xxxx' 
      and ip='0.0.0.0' 
      and port='3306' 
      and Variable='xxxxxx' 
      and status REGEXP '^[0-9]+$|^ON$' 
      and moddate between '2015-01-01 00:47' and '2015-02-01 01:07';

但需要4:00分钟。从15天开始，我就一直被这个问题困扰着。我知道我们需要将索引放在列上。但我不知道如何优化它。你可以请任何人建议我，哪些字段需要索引。或者分享任何其他链接来优化我的应用程序。谢谢..。

Answer 1

我将查看where子句，并尝试首先确定较小的结果集字段，并将其放在复合索引的第一个位置

您可能有许多客户端"xxxxx“的条目，但IP地址的条目较少。不确定你的“变量”上下文是什么，也许这个更小。但我会试一试

( IP, port, cname, variable, moddate, status )

但这基本上是几乎所有东西的索引。所以，也就是说，我可能会把它砍下来，看看性能是基于...

( IP, port, cname, moddate )

经过更多的思考，将CName放在第一位可能会更好……

( CName, IP, port, variable, moddate, status )

因为它击中我，cname可能就像一个网站地址，因为一个服务器在一个IP上可以托管许多网站名称，CName将小于整个IP地址。

Answer 2

这是您的查询：

SELECT status, moddate
FROM xxxxx 
WHERE Cname = 'xxxxxx' and
      ip = '0.0.0.0' and 
      port = 3306 and
      Variable = 'TABLE_LOCKS_IMMEDIATE' and
      status REGEXP '^[0-9]+$|^ON$' and
      moddate between '2015-01-01 00:47' and '2015-02-01 01:07';

注意：port是一个整数，所以我删除了3306两边的单引号。有时，在某些数据库中，混合比较的使用可能会影响索引的使用(尽管在这种情况下可能不会)。

您的查询正在访问一个表，因此定义索引的起点是WHERE子句。包含表中提到的所有六列的索引称为“覆盖”索引。而覆盖索引通常是最好的性能。

对于这样的索引，从具有相等条件的列开始。然后你得到一个关于不等式和in的位置。然后其余的列都在索引中，这样引擎就不必访问数据页了。因此，最好的索引是xxxxxx(Cname, ip, port, Variable, moddate, status)。前四列的顺序对此查询没有什么不同，因为所有列在from子句中都有相等索引。