MySQL表索引优化

Question

我正在使用一个在Amazon RDS中具有MySQL数据库的应用程序。问题中的表格是这样设置的：

CREATE TABLE `log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL,
  `username` varchar(45) NOT NULL,
  .. snip some varchar and int fields ..
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

这个系统已经处于测试阶段一段时间了，数据集已经相当庞大，查询开始变得相当缓慢。

SELECT COUNT(*) FROM log --> 16307224 (takes 105 seconds to complete)

这个表很大程度上仅用于根据如下查询构建一个报告

SELECT timestamp, username, [a few more] FROM log 
WHERE timestamp  BETWEEN '2012-03-30 08:00:00' AND '2012-03-30 16:00:00' 
AND username='XX' 

这通常会给出1000到6000行之间的内容，大约需要100-180秒才能完成，这意味着web应用程序经常会超时并留下一个空报告(我也会研究超时，但这个问题是针对根本原因的)。

我对数据库不是很在行，但我猜是这两者之间的问题要了我的命。我在想的是，也许我应该以某种方式使用时间戳作为索引。Timestamp和username一起使用应该仍然提供唯一性(我没有使用id字段做任何事情)。

如果有人对优化有什么建议，我洗耳恭听。

更新：

表现在已更改为以下内容

CREATE TABLE `log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL,
  `username` varchar(45) NOT NULL,
  .. snip ..
  `task_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_un_ts` (`timestamp`,`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SELECT语句的EXPLAIN返回以下内容

id => 1
select_type => SIMPLE
table => log
type => range
possible_keys => index_un_ts
key => index_un_ts
key_len => 55
ref => 
rows => 52258
Extra => Using where; Using index

Answer 1

那么，时间戳列和userid上的索引将会很有帮助。您需要能够读取EXPLAIN语句的输出。

转到MySQL并执行以下操作：

EXPLAIN SELECT timestamp, username, [a few more] FROM log 
WHERE timestamp  BETWEEN '2012-03-30 08:00:00' AND '2012-03-30 16:00:00' 
AND username='XX' 

这将向您显示MySQL用来执行查询的计划。将有一个名为key的列。这表示MySQL在查询中使用的索引。我怀疑您将看到所有内容，这意味着MySQL正在从上到下扫描表，根据where子句匹配每一行。现在，在timestamp和userid列上创建索引。再次运行EXPLAIN语句。您应该会在key列中看到您创建的索引。

如果MySQL使用索引，那么您的查询速度应该会快得多。只要记住不要过度索引即可。索引会使插入、更新和删除操作变慢。当您向表中插入新行时，如果表上有三个索引，则新行必须向这三个不同的索引写入3个值。所以这是一把双刃剑。