Mysql улучшает скорость SELECT

Ответ 1

найдите время, чтобы прочитать мой ответ здесь: (имеет аналогичные тома для вас)

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

но только если значение не является некоторым LARGE 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, чтобы сменить тип двигателя с mysiam на innodb, а скорее выгрузите данные в файлы csv и повторно импортируйте во вновь созданную и пустую таблицу innodb.

note Я заказываю данные во время процесса экспорта - кластерными индексами являются KEY!

Экспорт

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...

Ответ 2

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

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

Ответ 3

Я бы сделал две вещи: сначала бросьте некоторые индексы вокруг тега и даты, как было предложено выше:

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

Ответ 4

В запросе задается несколько вопросов - и с таким большим количеством строк внешний вид данных может изменить наилучший подход.

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

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

• Очень большой набор результатов, который нужно отсортировать (упорядочить).
• Очень большой набор результатов. Если тег и дата находятся в индексе (и пусть предполагают, что так хорошо, как это получается), каждая строка результатов должна будет оставить индекс для поиска поля значения. Подумайте об этом как о первом предложении каждой главы книги. Если вам нужно знать имена глав, просто: вы можете получить его из оглавления, но поскольку вам нужно первое предложение, вам нужно перейти к фактической главе. В некоторых случаях оптимизатор может выбрать просто перелистывать всю книгу (сканирование таблицы в лингво плана запроса), чтобы получить эти первые предложения.
• Сначала фильтрация недействительна. Если индекс находится в теге order, date..., то тег должен (для большинства ваших запросов) быть более строгим из двух столбцов. В основном, если у вас больше тегов, чем даты (или, может быть, даты в типичном диапазоне дат), то даты должны быть первым из двух столбцов в вашем индексе.

Несколько рекомендаций:

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

Ответ 5

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

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

Ответ 6

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

alter table table add index (tag, date);

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

Ответ 7

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

Я буду аргументировать мои подозрения для столбца value тем фактом, что разбиение на разделы действительно не уменьшает время выполнения запроса. Можете ли вы выполнить запрос без value, а также дать нам некоторые результаты, а также EXPLAIN? Вам действительно нужно это для каждой строки и какой это столбец?

Ура!

Ответ 8

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

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;