Вопрос по mysql, performance, indexing – Mysql производительность на 6 миллионов строк таблицы

15

Однажды я подозреваю, что мне придется изучить hadoop и перенести все эти данные в неструктурированную базу данных, но я удивлен, обнаружив, что производительность значительно снижается за такой короткий период времени.

У меня есть таблица mysql с чуть менее 6 миллионов строк. Я делаю очень простой запрос к этой таблице и считаю, что у меня есть все правильные индексы на месте.

запрос

SELECT date, time FROM events WHERE venid='47975' AND date>='2009-07-11' ORDER BY date

объяснение возвращается

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  updateshows     range   date_idx    date_idx    7   NULL    648997  Using where

насколько я могу судить, я использую правильный индекс, но этот запрос занимает 11 секунд.

База данных - MyISAM, а phpMyAdmin сообщает, что таблица равна 1,0 ГБ.

Есть идеи здесь?

Отредактировано: Date_idx индексирует как столбцы даты, так и столбцы venid. Должны ли это быть два отдельных индекса?

Какой сейчас статус codefreaK
Ваш запрос объяснения говорит, что он должен сканировать 648997 строк (возможно, он недостаточно эффективно использует индексы. Я отдельно индексирую столбцы, если бы это был я). Сколько строк на самом деле возвращается? nos
Какие у вас показатели? Pafjo

Ваш Ответ

4   ответа
2

что таблица строк 6M должна быть оптимизирована с помощью обычных методов.

Я предполагаю, что у вас есть выделенный сервер базы данных, и он имеет разумное количество оперативной памяти (скажем, минимум 8G).

Вы захотите убедиться, что настроили mysql для эффективного использования оперативной памяти. Если вы используете 32-битную ОС, не делайте этого. Если вы используете MyISAM, настройте буфер ключей так, чтобы он использовал значительную часть, но не слишком большую часть памяти.

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

Спасибо @MarkR, и извините за очень поздний ответ. Это был второй веб-сайт, который я когда-либо создавал, поэтому понятия не имел о выделенных серверах БД или о чем-либо подобном. Я запускал его в течение нескольких лет со всеми процессами в одной коробке. Никаких проблем, я был поражен тем, насколько хорошо MySQL масштабируется до 8 миллионов строк. Я заархивировал более старые данные, когда они достигли этой точки. pedalpete
2

который охватывает venid и date (или наоборот, или оба ...)

Вы хотитеtime, тоже
Спасибо, Майкл, я не понял, что поля SELECT тоже должны быть проиндексированы. Приветствия. pedalpete
Отсутствие полей SELECT также в индексе делает систему более жесткой. Любые новые прогнозы должны быть добавлены в индекс. Это правильный путь?
Когда вы говорите «добавить ключ», вы имеете в виду индекс? Я отредактировал свою запись, указав, что date_idx находится в полях date и venid. pedalpete
42

что запрос будет использовать ТОЛЬКО индекс, поэтому убедитесь, что индекс охватывает все поля, которые вы выбираете. Кроме того, поскольку речь идет о запросе диапазона, сначала в индексе должен быть venid, поскольку он запрашивается как константа. Поэтому я бы создал и проиндексировал так:

ALTER TABLE events ADD INDEX indexNameHere (venid, date, time);

С этим индексом вся информация, необходимая для выполнения запроса, находится в индексе. Это означает, что, надеюсь, механизм хранения способен извлекать информацию, фактически не ища внутри самой таблицы. Однако MyISAM может быть не в состоянии это сделать, поскольку он не сохраняет данные в листьях индексов, поэтому вы можете не получить желаемого увеличения скорости. Если это так, попробуйте создать копию таблицы и использовать механизм InnoDB для этой копии. Повторите те же самые шаги там и посмотрите, получите ли вы значительное увеличение скорости. InnoDBdoes сохранить значения полей в листьях индекса и разрешить покрытие индексов.

Теперь, надеюсь, вы увидите следующее при объяснении запроса:

mysql> EXPLAIN SELECT date, time FROM events WHERE venid='47975' AND date>='2009-07-11' ORDER BY date;

id  select_type table  type  possible_keys        key       [..]  Extra
1   SIMPLE   events range date_idx, indexNameHere indexNameHere   Using index, Using where
если вы помните, каково было время выполнения нового запроса с индексом?
Извините за поздний ответ @JustinKrause (и другие), ваш комментарий поступил через несколько лет после первоначального вопроса. Я полагаю, что после исправления индексов время запроса составило чуть менее 0,4 секунды. Это было УДИВИТЕЛЬНО, как быстро это было, и это также не было на выделенном сервере. Это был хостинг среднего размера, в то время ничего особенного. Я не могу вспомнить, был ли это линод или я вскоре перешел на линод. pedalpete
@pedalpete Я задаю тот же вопрос, что и Джастин.
КЛАССНО!! благодарю вас. Я не понял, что мне нужно было покрыть поля SELECTED индексом. Я думал, что это были только поля ГДЕ, которые нужно было проиндексировать. pedalpete
+1: закрывающие индексы необходимы. С осторожными индексами и осторожными запросами, 6-миллиметровые строки не являются большой проблемой.
1

venid колонка.

Я только что отредактировал свою запись, date_idx находится как в полях date, так и в venid. Извините, я не вставил это изначально. pedalpete

Похожие вопросы