Оптимизация SQL запросов

У меня возникли проблемы с оптимизацией некоторых sql-запросов, которые учитывают поля datetime.

Прежде всего, моя структура таблицы выглядит следующим образом: 

  CREATE TABLE info (
  id int NOT NULL auto_increment,
  name varchar(20),
  infoId int,
  shortInfoId int,
  text varchar(255),
  token varchar(60),
  created_at DATETIME,
  PRIMARY KEY(id)
  KEY(created_at));

После использования explain для некоторых простых запросов я добавил ключ created_at,что улучшило большинство моих простых запросов. Теперь у меня возникли проблемы со следующим запросом: 

SELECT min(created_at), max(created_at) from info order by id DESC limit 10000

С помощью этого запроса я хочу получить промежуток времени между последними результатами 10k.

После использования explain я получаю следующее результаты: 

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE          info    ALL     NULL            NULL    NULL        NULL     4       NULL

Есть идеи, как можно улучшить производительность этого запроса?

2 2
mysql performance database database-performance

2 ответа:

Если вы хотите изучить первые 10k строк, упорядоченных по id, то вам нужно использовать подзапрос для достижения своей цели: 

SELECT MIN(created_at), MAX(created_at)
FROM (
    SELECT created_at
    FROM info
    ORDER BY id DESC
    LIMIT 10000
) tenK

Внутренний запрос получает первые 10k строк из таблицы, отсортированных по id (требуется только поле created_at). Внешняя таблица вычисляет минимальное и максимальное значение created_at из набора результатов, сформированного внутренним запросом.

Я не запускал EXPLAIN на нем, но я думаю, что он говорит "Using temporary" в столбце "Extra" (что не очень хорошо, но вы не можете сделайте лучше для этой просьбы). Однако 10 000 строк-это не так уж много; он работает быстро, и производительность не ухудшается по мере увеличения размера таблицы.

Обновление:

Теперь я заметил это предложение в вопросе:

С помощью этого запроса я хочу получить промежуток времени между последними результатами 10k.

Если вы хотите получить значение created_at самой последней строки и строки, которая составляет 10k строк в прошлом, то вы можете использовать два простых запроса, которые используют индекс на created_at и бегите быстро: 

(
    SELECT created_at
    FROM info
    ORDER BY id DESC
    LIMIT 1
)
UNION ALL
(
    SELECT created_at
    FROM info
    ORDER BY id DESC
    LIMIT 9999,1
)
ORDER BY created_at

Этот запрос производит 2 строки, первая-это значение created_at 100-й строки в прошлом, вторая - created_at самой последней строки (Я предполагаю, что created_at всегда растет).

0
SELECT min(created_at), max(created_at) from info order by id DESC limit 10000
Приведенный выше запрос даст вам одну строку, содержащую минимальное и максимальное значения created_at из таблицы info. Поскольку он возвращает только 1 строку, предложения order by и limit не вступают в игру.

10000-я запись с конца может быть доступна с условием order by & limit ORDER BY id DESC LIMIT 1 OFFSET 9999 (Спасибо @Mörre Noseshine за исправление)

Итак, мы можем написать предполагаемый запрос следующим образом: 

SELECT 
min_created_at.value, 
max_created_at.value
FROM 
(SELECT 
    created_at value 
 FROM info 
 ORDER BY id DESC 
 LIMIT 1 OFFSET 9999) min_created_at,

(SELECT 
    created_at value 
 FROM info 
 ORDER BY id DESC 
 LIMIT 1) max_created_at
0