Производительность Mysql с большой таблицей

ProgramBox

Производительность Mysql с большой таблицей

Post author:admin
Запись опубликована:19 апреля, 2023
Post category:Вопросы по программированию

#mysql #performance #full-text-search

#mysql #Производительность #полнотекстовый поиск

Вопрос:

у меня есть таблица резюме — имена, текст резюме, почтовый индекс, имя, фамилия, долгота, широта, zip … более 500 000 строк

я запрашиваю это слишком разными способами:

по местоположению, например:

 1) SELECT * FROM resumes 
WHERE ((latitude BETWEEN 44.3523845787 AND 45.6809474213) 
AND (longitude BETWEEN -110.873064254 AND -108.993603746)) 
GROUP BY lastname,longitude 
LIMIT 0, 50 
by name

2) SELECT * from resumes 
   (MATCH(resume) AGAINST ('donald')) AS relevance 
    FROM resumes 
    WHERE (MATCH(resume) AGAINST ('donald')) 
    GROUP BY lastname,longitude 
    ORDER BY relevance DESC 
    LIMIT 0, 50

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

как я могу ускорить эти запросы? Спасибо

1. Быстрый / грязный совет: Любое поле, используемое в предложении where or join или group by , должно иметь индекс.

2. вы добавили индексы широты, долготы, фамилии и резюме? (то есть, что другой марк только что сказал, пока я печатал это)

Ответ №1:

 1) SELECT * FROM resumes 
WHERE ((latitude BETWEEN 44.3523845787 AND 45.6809474213) 
AND (longitude BETWEEN -110.873064254 AND -108.993603746)) 
GROUP BY lastname,longitude 
LIMIT 0, 50 
by name

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

Для этого вам нужен пространственный индекс.

 2) SELECT * from resumes 
   (MATCH(resume) AGAINST ('donald')) AS relevance 
    FROM resumes 
    WHERE (MATCH(resume) AGAINST ('donald')) 
    GROUP BY lastname,longitude 
    ORDER BY relevance DESC 
    LIMIT 0, 50

Аналогично, для этого также необходим особый тип индекса, который не является btree — полнотекстовый индекс, если быть точным.

1. ВЫБЕРИТЕ имя, фамилию, добавленную дату, почтовый индекс, resume_id ИЗ резюме, ГДЕ ((широта МЕЖДУ 34.3861330183 И 35.7139349817) И (долгота МЕЖДУ -87.9631695654 И -86.3412124346)) ОГРАНИЧЕНИЕ 0, 50

2. после попытки «alter table возобновляет добавление пространственного индекса (широта)» я получаю ошибку # 1089 — Неверный ключ вложенной части; используемая часть ключа не является строкой, используемая длина больше, чем часть ключа, или механизм хранения не поддерживает уникальные вложенные ключи

3. убедитесь, что ваша таблица использует движок myisam . затем: CREATE SPATIAL INDEX resumes_sp_index ON resumes (latitude, longitude); и CREATE FULLTEXT INDEX resumes_ft_index ON resumes (resume); .

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

5. Пункт кажется правильным, но это может быть поле, которое вам нужно для его индексации… (В Postgres я бы использовал поле напрямую, поскольку именно оно в конечном итоге оказывается в индексе в любом случае.) Поскольку mysql не позволяет индексировать выражение, которое я проверял в последний раз, возможно, вам захочется исследовать его заполнение с использованием триггеров.

Ответ №2:

Используйте индексы для всех полей, используемых для объединения таблиц.
Используйте индексы для полей, которые вы используете в вашем предложении where.
Не используйте ‘select *’, выберите только те поля, которые вам нужны.
Group by сортирует ваш результирующий набор по сгруппированным полям, если вы упорядочиваете по другому полю (или в другом порядке), вы заставляете выполнять дополнительную сортировку, замедляя работу.
MySQL выполняет вычисление с помощью ярлыка, сначала в вашем предложении where ставится условие, которое ограничивает максимальное количество строк.
Выберите * с помощью group by — это другой способ написания «устранить повторяющиеся строки». Если вы размещаете свои таблицы так, чтобы в них изначально не было повторяющихся строк, вам также не нужна group by. Это значительно ускорит ваши запросы.
Укажите ваши поля широты и долготы в качестве типа point и поместите в них пространственный индекс. Я бы дал вам ссылку, но сейчас я на iphone, так что это немного затруднительно.