если это не сработает, попробуйте создать foo вместо выбора тега.

тоящее время я пытаюсь улучшить скорость SELECTS для таблицы MySQL и буду признателен за любые предложения по ее улучшению.

У нас более 300 миллионов записей в таблице, а в таблице есть тег структуры, дата, значение. Первичный ключ - это комбинированный ключ тега и даты. Таблица содержит информацию о 600 уникальных тегах, большинство из которых содержит в среднем около 400 000 строк, но может варьироваться от 2000 до более 11 миллионов строк.

Запросы, выполняемые к таблице:

  SELECT date,
         value 
    FROM table 
   WHERE tag = "a" 
     AND date BETWEEN 'x' and 'y' 
ORDER BY date

.... и очень мало, если таковые имеются.

Я попытался разделить данные по тегам на различное количество разделов, но, похоже, это немного увеличило скорость.

 piyush23 янв. 2011 г., 19:24
опубликовать объяснение запроса, выполнив EXPLAIN SELECT date, значение FROM таблицы, где tag = "a" и дата BETWEEN 'x' и 'y' упорядочены по дате
 Larry Lustig23 янв. 2011 г., 20:56
@ Пони: объявление первичного ключа создаст индекс для (тег, дата), предположительно?
 S.Lott23 янв. 2011 г., 19:24
Когда вы опускаетеORDER BYэто помогает? Можете ли вы опубликовать фактическое время запросов с и без ORDER BY?
 OMG Ponies23 янв. 2011 г., 19:28
Вы не упомянули индексы - попробуйте один для каждого столбца (тег, дата, значение) или один составной индекс, содержащий все три. Имейте в виду, что порядок столбцов имеет значение в составном индексе - начиная слева, если столбец не указан в запросе, индекс не будет использоваться.
 allyLogan24 янв. 2011 г., 09:46
Объяснить результат запроса: id: '1', select_type: 'SIMPLE', таблица: 'table', тип: 'range', возможные_ключи: 'PRIMARY', ключ: 'PRIMARY', key_len: '85 ', ref: NULL, строки: «29559», дополнительные: «Использование где»

Ответы на вопрос(8)

уг тега и даты, как указано выше:

alter table table add index (tag, date);

Затем разбейте ваш запрос на основной и выберите, в котором вы сужаете свои результаты, когда входите в основной запрос:

SELECT date, value
FROM table
WHERE date BETWEEN 'x' and 'y'
AND tag IN ( SELECT tag FROM table WHERE tag = 'a' )
ORDER BY date
Решение Вопроса

(имеет объемы, аналогичные вашим)

500 миллионов строк, 15 миллионов строк сканирования за 0,02 секунды.

MySQL и NoSQL: помогите выбрать правильный

затем измените ваш движок таблицы на innodb следующим образом:

create table tag_date_value
(
tag_id smallint unsigned not null, -- i prefer ints to chars
tag_date datetime not null, -- can we make this date vs datetime ?
value int unsigned not null default 0, -- or whatever datatype you require
primary key (tag_id, tag_date) -- clustered composite PK
)
engine=innodb;

вместо этого вы могли бы рассмотреть следующее как первичный ключ:

primary key (tag_id, tag_date, value) -- added value save some I/O

но только если значение не какого-то БОЛЬШОГО типа varchar!

запрос как раньше:

select
 tag_date, 
 value
from
 tag_date_value
where
 tag_id = 1 and
 tag_date between 'x' and 'y'
order by
 tag_date;

надеюсь это поможет :)

РЕДАКТИРОВАТЬ

о, забыл упомянуть - не используйте alter table, чтобы изменить тип движка с mysiam на innodb, а скорее выведите данные в файлы csv и повторно импортируйте их во вновь созданную и пустую таблицу innodb.

обратите внимание, я заказываю данные в процессе экспорта - кластерные индексы - это КЛЮЧ!

экспорт

select * into outfile 'tag_dat_value_001.dat' 
fields terminated by '|' optionally enclosed by '"'
lines terminated by '\r\n'
from
 tag_date_value
where
 tag_id between 1 and 50
order by
 tag_id, tag_date;

select * into outfile 'tag_dat_value_002.dat' 
fields terminated by '|' optionally enclosed by '"'
lines terminated by '\r\n'
from
 tag_date_value
where
 tag_id between 51 and 100
order by
 tag_id, tag_date;

-- etc...

Импортировать

импортируйте обратно в таблицу в правильном порядке!

start transaction;

load data infile 'tag_dat_value_001.dat' 
into table tag_date_value
fields terminated by '|' optionally enclosed by '"'
lines terminated by '\r\n'
(
tag_id,
tag_date,
value
);

commit;

-- etc...

что добавление индекса на(tag, date) помог бы:

alter table table add index (tag, date);

Пожалуйста, опубликуйте результат объяснения по этому запросу (EXPLAIN SELECT date, value FROM ......)

 Jon Black11 мар. 2011 г., 02:31
вероятно myisam и, следовательно, не кластеризованный btree PK index - eeek
 Larry Lustig23 янв. 2011 г., 20:55
Декларация первичного ключа создаст этот индекс.

сколько различных значений появляется в этом поле)? Если дата МЕЖДУ 'x' И 'y' является более ограничивающей, чем тег = 'a' в предложении WHERE, попробуйте сделать ваш первичный ключ (date, tag) вместо (tag, date), разрешив использовать date как индексированное значение.

Кроме того, будьте осторожны, указав «x» и «y» в предложении WHERE. В некоторых случаях MySQL будет приводить каждое поле даты в соответствии с не подразумеваемым типом значений, с которыми вы сравниваете.

 allyLogan25 янв. 2011 г., 11:15
хотя мы называем дату «date», на самом деле это метка времени со значениями каждую секунду или около того, а некоторые теги имеют 11 миллионов значений, поэтому диапазон дат довольно широк. Я попытался создать новую таблицу с (date, tag) в качестве первичного ключа, но после примерно 12 часов загрузки данных это было всего лишь 6-й путь до конца !!
 Larry Lustig25 янв. 2011 г., 13:51
Увеличенная мощность фактической метки времени делает еехороший кандидат на то, чтобы быть первым в первичном ключе, но только если выполняемые вами поиски возвращают относительно небольшой диапазон значений меток времени. Так что, похоже, мое предложение не поможет вам.
 allyLogan24 янв. 2011 г., 10:25
Для столбца даты намного больше разных значений, чем для столбца тега, поскольку столбец даты может иметь значение каждые пару секунд, и это не предсказуемо, а столбец тега имеет только 600 различных значений.
 Larry Lustig24 янв. 2011 г., 14:55
В зависимости от ширины диапазона дат от x до y, вы можете значительно повысить производительность, если будете использовать (date, tag) в качестве первичного ключа. Попробуйте.

рок внешний вид данных может изменить оптимальный подход.

   SELECT date, value 
   FROM table 
   WHERE tag = "a" 
     AND date BETWEEN 'x' and 'y' 
   ORDER BY date

Есть несколько вещей, которые могут замедлить этот запрос выбора.

Очень большой набор результатов, который нужно отсортировать (упорядочить по).Очень большой набор результатов. Если тег и дата присутствуют в индексе (и давайте предположим, что это так же хорошо, как получается), каждая строка результата должна будет покинуть индекс, чтобы найти поле значения. Думайте об этом как о необходимости первого предложения каждой главы книги. Если вам нужно было только знать названия глав, просто - вы можете получить их из оглавления, но, поскольку вам нужно первое предложение, вам нужно перейти к самой главе. В некоторых случаях оптимизатор может выбрать просто пролистать всю книгу (просмотр таблицы в жаргоне плана запроса), чтобы получить эти первые предложения.Фильтрация по неправильному предложению where. Если индекс находится в теге order, date ..., тег должен (для большинства ваших запросов) быть более строгим из двух столбцов. Таким образом, в основном, если у вас нет больше тегов, чем дат (или, может быть, чем даты в типичном диапазоне дат), тогда даты должны быть первым из двух столбцов в вашем индексе.

Пара рекомендаций:

Подумайте, возможно ли обрезать некоторые из этих данных, если они слишком стары, чтобы заботиться о них большую часть времени.Попробуйте поиграть с вашим текущим индексом - то есть изменить порядок элементов в нем.Не используйте текущий индекс и замените его индексом покрытия (содержит все 3 поля)Запустите несколько EXPLAIN и убедитесь, что он вообще использует ваш индекс.Переключитесь на другое хранилище данных (mongo db?) Или иным образом убедитесь, что эта таблица монстров хранится в памяти как можно больше.

е выбор во временной таблице для тегов и упорядочения.

CREATE temporary table foo
SELECT date, value 
FROM table 
WHERE date BETWEEN 'x' and 'y' ;

ALTER TABLE foo ADD INDEX index( tag );

SELECT date, value 
FROM foo 
WHERE tag = "a" 
ORDER BY date;

если это не сработает, попробуйте создать foo вместо выбора тега.

CREATE temporary table foo
SELECT date, value 
FROM table 
WHERE tag = "a";    

ALTER TABLE foo ADD INDEX index( date );

SELECT date, value 
FROM foo 
WHERE date BETWEEN 'x' and 'y' 
ORDER BY date;

value столбец в нижней части ваших проблем с производительностью. Он не является частью индекса, поэтому у нас будет доступ к таблице. Кроме того, я думаю, что ORDER BY вряд ли так сильно повлияет на производительность, так как он является частью вашего индекса и должен быть упорядочен.

Я буду аргументировать мои подозрения в отношенииvalue Столбец из-за того, что разбиение не действительно сокращает время выполнения запроса. Можете ли вы выполнить запрос безvalue и далее дайте нам некоторые результаты, а также ОБЪЯСНИТЬ? Вы действительно нуждаетесь в этом для каждой строки и какой это столбец?

Ура!

 Lachezar Balev24 янв. 2011 г., 21:10
Так что все еще кажется, что это проблема. Что если вы запустите запрос без значения для теста? Является ли увеличение значительным?
 allyLogan24 янв. 2011 г., 12:15
Значение является VARCHAR и необходимо для каждого запроса. Я разместил объяснение выше ...

что ваш единственный шанс улучшить его - это индекс покрытия со всеми тремя столбцами (тег, данные, значение). Это позволяет избежать доступа к таблице.

Я не думаю, что разделение может помочь с этим.

Ваш ответ на вопрос