Настройка запросов MySQL (большой набор данных) и объяснение плана

Я использую mysql5.1, у меня есть таблица, которая содержит около 15 лакхов (1,5 миллиона) записей. В этой таблице есть записи для разных сущностей, то есть дочерние записи для всех основных сущностей.

В этой таблице 8 столбцов, из которых 6 столбцов объединены для создания первичного ключа. Эти столбцы могут быть отдельными внешними ключами, но из-за производительности мы внесли это изменение.

Даже простой оператор select с двумя условиями занимает 6-8 секунд. Ниже приведен план объяснения того же.

Запрос

explain extended 
 select distinct location_code, Max(trial_number) as replication
   from status_trait  t  
  where t.status_id='N02'
    and t.trial_data='orange'
  group by location_code

Результаты EXPLAIN EXTENDED

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
 1  SIMPLE  t   index       FK_HYBRID_EXP_TRAIT_DTL_2   5       1481572 100.00  Using where; Using index

У меня есть такие вопросы:

  1. Как работать с таблицами с большими данными
  2. Индексируется нормально для этой таблицы

mysql optimization explain normalization
person sumedha    schedule 15.04.2014    source источник
comment
lac - это единица измерения, с которой многие из нас не знакомы.   -  person Mihai Stancu    schedule 15.04.2014
comment
1,5 миллиона записей не так уж и много. Если у вас есть один индекс по 6 ключам, это не поможет отфильтровать таблицу по двум из этих ключей. Попробуйте добавить отдельные индексы для каждого из столбцов, используемых для фильтрации (может занимать много места даром, если они не используются), или попробуйте добавить индексы для каждой группы столбцов, используемых для фильтрации.   -  person Mihai Stancu    schedule 15.04.2014
comment
Это имело значение, но поскольку мое время выполнения по-прежнему составляет 3,6 секунды, могу ли я уменьшить больше, теперь я создал два индекса, один со столбцами, которые я использовал для вышеуказанного запроса, а другой включает еще несколько столбцов.   -  person sumedha    schedule 15.04.2014
comment
Какие индексы вы создали для этой таблицы?   -  person O. Jones    schedule 15.04.2014
comment
Если вы хотите, чтобы мы помогли оптимизировать запрос, вам необходимо показать нам определения таблиц и индексов, а также количество строк для каждой из таблиц. Возможно, ваши таблицы плохо определены. Возможно, индексы созданы неправильно. Возможно, у вас нет индекса по этому столбцу, который, как вы думали, у вас есть. Не видя определений таблиц и индексов, мы не можем сказать. Нам также нужно количество строк, потому что это может сильно повлиять на оптимизацию запроса. Если вы знаете, как сделать EXPLAIN или получить план выполнения, также укажите результаты в вопросе. Если у вас нет индексов, посетите use-the-index-luke.com как можно скорее.   -  person Andy Lester    schedule 15.04.2014

Ответы (1)


arrow_upward
1
arrow_downward

Здесь вам могут помочь две вещи.

Во-первых, SELECT DISTINCT не имеет смысла в агрегирующем запросе. Просто используйте SELECT.

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

(status_id, trial_data, location_code, trial_number)

Почему это правильный индекс? Поскольку индексы MySQL организованы как BTREE. Эта организация позволяет серверу осуществлять произвольный доступ к индексу для поиска определенных значений. В вашем случае вам нужны конкретные значения status_id и trial_data. После того, как сервер произвел случайный доступ к индексу, он может сканировать его последовательно. В этом случае вы надеетесь просканировать различные значения location_code. Сервер знает, что он найдет эти разные значения уже по порядку. Наконец, серверу необходимо извлечь значения trial_number для использования в вашей MAX() функции. И вот, вот они в указателе, готовые к ощипыванию.

(Если вы часто выполняете агрегирование и запросы к большим таблицам, вам имеет смысл узнать, как работают составные и покрывающие индексы.)

Добавление индекса связано с расходами: когда вы INSERT или UPDATE строк, вам также нужно обновить свой индекс. Но такой индекс значительно ускорит ваш поиск.

person O. Jones    schedule 15.04.2014
comment
Это имело огромное значение, я смог увидеть результаты за 0,03 секунды. Но сколько будет стоить моя вставка и обновление? - person sumedha; 16.04.2014
comment
Вот в чем дело: вам нужны правильные индексы в вашей таблице, чтобы эффективно удовлетворять ваши общие запросы. Но вам не нужны другие индексы. Судя по вашему вопросу, похоже, что вы взламывали свой первичный ключ (который сам по себе является уникальным индексом), чтобы попытаться повысить эффективность. Но мне интересно, достигли ли вы какого-либо прироста производительности. Могу я предложить вам задать вопрос по codereview.stackexchange.com, который представляет результат SHOW CREATE TABLE yourtable, а также показывает несколько ваших наиболее распространенных запросов? Ссылка на вопрос здесь. - person O. Jones; 16.04.2014
comment
Опубликовано codereview.stackexchange.com/questions/47468/ - person sumedha; 17.04.2014
comment
Вы переместили поток на dba.stackexchange.com, где я не могу комментировать, поэтому я комментирую здесь в соответствии с вашим комментарием, что для 4-го индекса я могу использовать (эксперимент_id, trait_name, hybrid_number), что, если у меня есть запрос, который использует hybrid_number, trait_name и эксперимент_id? - person sumedha; 18.04.2014