mysql - 如何优化大型数据库的 MySQL 查询

Question

最近我注意到一个严重的问题，当我的数据库增加到超过 620000 条记录时。以下查询:

SELECT *,UNIX_TIMESTAMP(`time`) AS `time` FROM `log` WHERE (`projectname`="test" OR `projectname` IS NULL)  ORDER BY `time` DESC LIMIT 0, 20

在本地数据库上的执行时间约为 2.5 秒。我想知道如何才能加快速度？

EXPLAIN 命令产生以下输出:

ID: 1
select type: SIMPLE
TABLE: log
type: ref_or_null
possible_keys: projectname
key: projectname
key_len: 387
ref: const
rows: 310661
Extra: Using where; using filesort

我已经在项目名称、时间列上设置了索引。

有什么帮助吗？

编辑:感谢 ypercube 响应，我能够减少查询执行时间。但是，当我只向 WHERE 子句添加另一个条件(并且严重程度 =“更改”)时，它再次持续 2 秒。将所有可能的“WHERE”列包含到我的合并索引中是一个好的解决方案吗？

ID: 1 
select type: SIMPLE 
TABLE: log 
type: ref_or_null 
possible_keys: projectname 
key: projectname 
key_len: 419 
ref: const, const 
rows: 315554 
Extra: Using where; using filesort

表结构:

   CREATE TABLE `log` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `projectname` VARCHAR(128) DEFAULT NULL,
  `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `master` VARCHAR(128) NOT NULL,
  `itemName` VARCHAR(128) NOT NULL,
  `severity` VARCHAR(10) NOT NULL DEFAULT 'info',
  `message` VARCHAR(255) NOT NULL,
  `more` TEXT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `projectname` (`severity`,`projectname`,`time`)
) ENGINE=INNODB AUTO_INCREMENT=621691 DEFAULT CHARSET=utf8

Answer 1

在(项目名称，时间)上添加索引:

ALTER TABLE log
  ADD INDEX projectname_time_IX            -- choose a name for the index
    (projectname, time) ;

然后使用原始列作为ORDER BY

SELECT *, UNIX_TIMESTAMP(time) AS unix_time 
FROM log 
WHERE (projectname = 'test' OR projectname IS NULL)  
ORDER BY time DESC 
LIMIT 0, 20 ;

或此变体 - 确保索引得到有效使用:

  ( SELECT *, UNIX_TIMESTAMP(time) AS unix_time 
    FROM log 
    WHERE projectname = 'test'
    ORDER BY time DESC 
    LIMIT 20 
  )
  UNION ALL 
  ( SELECT *, UNIX_TIMESTAMP(time) AS unix_time 
    FROM log 
    WHERE projectname IS NULL
    ORDER BY time DESC 
    LIMIT 20 
  ) 
  ORDER BY time DESC
  LIMIT 20 ;