@ex0rcist

Как правильно проставить индексы в таблице addrobj справочника ФИАС?

Здравствуйте, коллеги!

Занимаюсь разработкой гема для работы с ФИАС - сделал бОльшую часть грязной работы по импорту в mysql, и на реальном проекте столкнулся с тем, что запросы выполняются достаточно медленно.

В таблице addrobj около 2.5 млн строк, индексы имею следующие:
aoid - размер 2410267
formalname - размер 267807,
aolevel - размер 8,
actstatus - размер 1,
aoguid - размер 1205133,
shortname - размер 113

Запрос (Поиск улицы на "гру" по Москве)

SELECT SQL_NO_CACHE * FROM `geo_fias_addrobj` WHERE `geo_fias_addrobj`.`actstatus` = 1 AND `geo_fias_addrobj`.`aolevel` = 7 AND `geo_fias_addrobj`.`shortname` IN ('аллея', 'б-р', 'въезд', 'заезд', 'кв-л', 'кольцо', 'линия', 'наб', 'остров', 'парк', 'пер', 'пл', 'проезд', 'пр-кт', 'проулок', 'сквер', 'тракт', 'туп', 'ул', 'уч-к', 'ш', 'аул', 'городок', 'д', 'вал') AND `geo_fias_addrobj`.`parentguid` = '0c5b2444-70a0-4932-980c-b4dc0d3f02b5' AND (formalname LIKE '%Гру%') LIMIT 10

Выполняется на стандартном вагрант-образе около 4 сек (mac mini 2013)

EXPLAIN выдает

select_type - simple
type - ref
possible_keys - aolevel, actstatus, shortname
key - actstatus
ref - const
rows - 1303958
extra - using where

Очевидно, что используется индекс по флагу акутальности, и перебирает больше миллиона строк. Подскажите, какие есть способы оптимизировать структуру индексов?
  • Вопрос задан
  • 3774 просмотра
Решения вопроса 1
Т.к. MySQL может использовать в запросе только один индекс, то надо использовать составные индексы. Первым столбцом в этом индексе должен идти тот, которому соответствует наименьшее кол-во строк. В вашем случае, это скорее всего `parentguid`. Далее должен идти столбец, который отсеит максимальное кол-во оставшихся строк. Я плохо знаком со структурой базы данных ФИАС, но предположу, что следущим столбцом может идти `aolevel`. Ну и третьим - `formalname`. Только, если вы хотите, чтобы индекс по `formalname` работал, то надо искать по началу поля, т.е. "formalname LIKE 'Гру%' ". Использование остальных столбцов в индексе не принесет ощутимой пользы (т.е. они отсеят наименьшее кол-во строк и, скорее всего, MySQL не будет их использовать). Получается следующий индекс:
ALTER TABLE geo_fias_addrobj ADD INDEX (parentguid, aolevel, formalname)

В запросе, в секции WHERE эти столбцы должны появляться в том же порядке, что и в индексе. Значит запрос получается такой:
SELECT SQL_NO_CACHE * FROM `geo_fias_addrobj` 
WHERE `geo_fias_addrobj`.`parentguid` = '0c5b2444-70a0-4932-980c-b4dc0d3f02b5'
    AND `geo_fias_addrobj`.`aolevel` = 7
    AND formalname LIKE 'Гру%'
    AND `geo_fias_addrobj`.`actstatus` = 1
    AND `geo_fias_addrobj`.`shortname` IN ('аллея', 'б-р', 'въезд', 'заезд', 'кв-л', 'кольцо', 'линия', 'наб', 'остров', 'парк', 'пер', 'пл', 'проезд', 'пр-кт', 'проулок', 'сквер', 'тракт', 'туп', 'ул', 'уч-к', 'ш', 'аул', 'городок', 'д', 'вал')
LIMIT 10
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы