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 应用程序经常会超时并留下一个空报告(我也会查看超时情况，但这个问题是找出根本原因)。

我不太擅长数据库，但我的猜测是 BETWEEN 在这里杀死了我。我在想的是，我也许应该以某种方式使用时间戳作为索引。时间戳和用户名一起仍然应该提供唯一性(我不使用 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

时间戳列和用户 ID 上的索引会很有帮助。您需要能够读取 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 在查询中使用什么索引。我怀疑你会在那里看到 ALL，这意味着 MySQL 正在从上到下扫描表，将每一行与你的 where 子句相匹配。现在在时间戳和用户 ID 列上创建索引。再次运行 EXPLAIN 语句。您应该会在键列中看到您创建的索引。

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