Оптимизация индекса таблицы MySQL

Question

Я работаю с приложением, которое имеет базу данных MySQL на Amazon RDS. Таблица вопросов задается так:

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 секунд, а это означает, что веб-приложение будет часто зависать и оставит пустой отчет (я также посмотрю на время ожидания, но этот вопрос относится к корню причина).

Я не очень хорошо разбираюсь в базах данных, но я думаю, что именно МЕЖДУ убивает меня здесь. Я думаю, что, возможно, мне следует каким-то образом использовать метку времени в качестве индекса. Временная метка с именем пользователя по-прежнему должна обеспечивать уникальность (я ни для чего не использую поле 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

EXPLAIN из SELECT оператор возвращает следующее

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

Ну, индекс в столбце timestamp и 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 использует для выполнения запроса. Там будет столбец с именем ключа. Это указывает, какой индекс MySQL использует в запросе. Я подозреваю, что вы увидите там ВСЕ, что означает, что MySQL сканирует таблицу сверху вниз, сопоставляя каждую строку с вашим предложением where. Теперь создайте индекс для столбцов timestamp и userid. Запустите оператор EXPLAIN еще раз. Вы должны увидеть индекс, который вы создали в ключевом столбце.

Если MySQL использует индекс, тогда ваш запрос должен быть значительно быстрее. Просто помните, чтобы не переоценить. Индексы делают вставки, обновления и удаления медленнее. Когда вы вставляете новую строку в таблицу и в таблице есть три индекса, новая строка должна записать 3 значения в три разных индекса. Так что это обоюдоострый меч.