Блог

Запрос на весеннюю загрузку

 @Query(value="SELECT *   FROM products p   join product_generic_name pg on pg.id = p.product_generic_name_id   where (p.product_name like %?1%   and p.parent_product_id IS NULL   and p.is_active=true and   (p.is_laboratory is null or p.is_laboratory = false)  )   or (pg.product_generic_name like %?1%   and pg.is_active = true) ",nativeQuery = true)  Pagelt;Productsgt; findByProductNameLikeAndGenericNameLike(String searchText, Pageable pageable);  

Таблица продуктов содержит более 3 миллионов записей, а выполнение запроса занимает около 4 минут.Как оптимизировать производительность запросов. Я попытался индексировать столбец product_name, но не сильно улучшил производительность.

Есть два узких места:

• like %?1% — Ведущий подстановочный знак означает, что он должен считывать и проверять каждую строку.
• OR — Это редко поддается оптимизации.

Если like %?1% смотреть только на «слова», то с помощью FULLTEXT индекса и MATCH будет работать намного быстрее.

OR может быть превращен в UNION . Вероятно , так и должно быть UNION DISTINCT , если предположить, что ?1 это может быть как name в том, так и в generic_name другом .

Больше памяти, больше регулярных индексов и т. Д. И т. Д.-Это вряд ли поможет. EXPLAIN и другие инструменты анализа рассказывают вам, что происходит сейчас, а не о том, как улучшить запрос и/или индексы. Дефрагментация (в InnoDB) — это в основном пустая трата времени. Существует только узкий диапазон скоростей процессора; это не изменилось за последние 20 лет. Дополнительные ядра бесполезны, так как MySQL будет использовать только одно ядро для этого запроса. Всего 3 м строк означает, что у вас, вероятно, более чем достаточно оперативной памяти.

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

• is_active
• is_laboratory
• parent_product_id

Однако выполнение поиска «свободного текста» с двумя подстановочными знаками в начале и в конце поиска не является отличным вариантом использования реляционной базы данных. Это лучший дизайн для решения этой проблемы? Если у вас 3 миллиона продуктов, не могли бы вы создать «группу продуктов», которую пользователь должен выбрать, чтобы уменьшить количество строк для поиска? Или, в качестве альтернативы, это вариант использования, который хорошо подходит для полнотекстовой поисковой системы, такой как ElasticSearch или Solr.

Я бы сказал, что это очень открытый вопрос.

Я постараюсь сломать его для тебя.

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

Совет 1. Оптимизация запросов Во многих случаях проблемы с производительностью базы данных вызваны неэффективными SQL-запросами. Оптимизация ваших SQL-запросов-один из лучших способов повысить производительность базы данных. Когда вы попытаетесь сделать это вручную, вы столкнетесь с несколькими дилеммами, связанными с выбором наилучшего способа повышения эффективности запросов. Они включают понимание того, следует ли писать соединение или подзапрос, следует ли использовать EXISTS или IN и многое другое. Когда вы знаете наилучший путь вперед, вы можете писать запросы, которые повышают эффективность и, следовательно, производительность базы данных в целом. Это означает меньше узких мест и меньше недовольных конечных пользователей.

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

Совет 2. Улучшение индексов В дополнение к запросам другим важным элементом базы данных является индекс. Если все сделано правильно, индексирование может повысить производительность базы данных и помочь оптимизировать продолжительность выполнения запроса. Индексирование создает структуру данных, которая помогает упорядочить все ваши данные и облегчает поиск информации. Поскольку поиск данных проще, индексирование повышает эффективность поиска данных и ускоряет весь процесс, экономя как ваше, так и системное время и усилия.

Совет 3. Дефрагментация данных Дефрагментация данных-один из лучших подходов к повышению производительности базы данных. Со временем, когда в вашу базу данных постоянно записывается и удаляется так много данных, ваши данные могут стать фрагментированными. Эта фрагментация может замедлить процесс поиска данных, поскольку она мешает запросу быстро находить информацию, которую он ищет. При дефрагментации данных вы разрешаете группировать соответствующие данные вместе и стираете проблемы с индексными страницами. Это означает, что ваши операции, связанные с вводом-выводом, будут выполняться быстрее.

Совет 4: Увеличьте объем памяти Эффективность вашей базы данных может значительно снизиться, если у вас недостаточно доступной памяти для правильной работы базы данных. Даже если вам кажется, что у вас в общей сложности много памяти, вы можете не соответствовать требованиям вашей базы данных. Хороший способ выяснить, нужно ли вам больше памяти, — это проверить, сколько ошибок страниц в вашей системе. Когда количество ошибок велико, это означает, что у ваших хостов либо не хватает, либо полностью отсутствует доступная память. Увеличение объема выделяемой памяти поможет повысить эффективность и общую производительность.

Совет 5: Усиление процессора Лучший процессор напрямую преобразуется в более эффективную базу данных. Вот почему вам следует рассмотреть возможность обновления до процессора более высокого класса, если у вас возникли проблемы с производительностью базы данных. Чем мощнее ваш процессор, тем меньше нагрузки он будет испытывать при работе с несколькими запросами и приложениями. При оценке вашего процессора вы должны отслеживать все элементы производительности процессора, включая время готовности процессора, которые сообщают вам о времени, когда ваша система пыталась использовать процессор, но не смогла, потому что ресурсы были заняты в противном случае.