Система баз данных является ядром информационной системы управления. Онлайн-обработка транзакций на основе базы данных (OLTP) и онлайн-аналитическая обработка (OLAP) являются одними из наиболее важных компьютерных приложений в банках, предприятиях, правительствах и других ведомствах. В этой статье, основанной на примерах приложений и теории баз данных, описывается применение технологии оптимизации запросов в реальных системах. Судя по примерам применения большинства систем, на операции запроса приходится наибольшая доля различных операций с базой данных, а оператор SELECT, на котором основана операция запроса, является самым дорогим оператором среди операторов SQL. Например, если объем данных накапливается до определенного уровня, например, информация таблицы базы данных банковских счетов накапливается до миллионов или даже десятков миллионов записей, полное сканирование таблицы часто занимает десятки минут или даже часов. Если вы выберете лучшую стратегию запроса, чем полное сканирование таблицы, вы часто сможете сократить время запроса до нескольких минут, что показывает важность технологии оптимизации запросов.
В ходе реализации проекта приложения автор обнаружил, что при разработке приложений баз данных с использованием некоторых интерфейсных инструментов разработки баз данных (таких как PowerBuilder, Delphi и т. д.) многие программисты сосредотачиваются только на красоте пользовательского интерфейса и не платят внимание к эффективности операторов запроса, что приводит ко всем проблемам. Разработанная система приложений неэффективна и приводит к серьезной трате ресурсов. Поэтому очень важно, как разрабатывать эффективные и разумные операторы запросов. В этой статье, основанной на примерах приложений и теории баз данных, описывается применение технологии оптимизации запросов в реальных системах.
Проанализируйте проблему
Многие программисты полагают, что оптимизация запросов — это задача СУБД (системы управления базами данных) и не имеет ничего общего с операторами SQL, написанными программистами. Это неверно. Хороший план запроса часто может повысить производительность программы в десятки раз. План запроса — это набор операторов SQL, отправленных пользователем, а план запроса — это набор операторов, созданных после оптимизации. Процесс обработки плана запроса СУБД заключается в следующем: после завершения лексической и синтаксической проверки запроса запрос передается в оптимизатор запросов СУБД. После того как оптимизатор завершает алгебраическую оптимизацию и оптимизацию путей доступа, предварительно скомпилированный модуль обрабатывает запрос. оператор и сгенерируйте план запроса, затем отправьте его в систему для обработки и выполнения в подходящее время и, наконец, верните результаты выполнения пользователю. В высоких версиях реальных продуктов баз данных (таких как Oracle, Sybase и т. д.) используются методы оптимизации на основе затрат. Эта оптимизация позволяет оценить стоимость различных планов запросов на основе информации, полученной из таблицы системного словаря, а затем выбрать. Лучшее планирование. Хотя современные продукты баз данных становятся все лучше и лучше в оптимизации запросов, операторы SQL, предоставляемые пользователями, являются основой для оптимизации системы. Трудно представить, что изначально плохой план запросов станет эффективным после оптимизации системы. Заявления, которые пишут пользователи, имеют решающее значение. На данный момент мы не будем обсуждать оптимизацию запросов, выполняемую системой. Далее основное внимание уделяется решениям для улучшения планов запросов пользователей.
решать проблемы
Ниже в качестве примера используется система реляционных баз данных Informix, чтобы представить методы улучшения планов пользовательских запросов.
1. Разумное использование индексов
Индекс — это важная структура данных в базе данных, и его основная цель — повысить эффективность запросов. Большинство продуктов баз данных теперь используют индексную структуру ISAM, впервые предложенную IBM. Использование индексов должно быть целесообразным, а принципы его использования заключаются в следующем:
●Создавайте индексы для столбцов, которые часто соединяются, но не обозначены как внешние ключи, в то время как оптимизатор автоматически генерирует индексы для редко связанных полей.
● Создавайте индексы для столбцов, которые часто сортируются или группируются (то есть группируются или упорядочиваются по операциям).
●Создавайте поиск по столбцам с множеством разных значений, которые часто используются в условных выражениях. Не создавайте индексы для столбцов с небольшим количеством разных значений. Например, в столбце «Пол» таблицы сотрудников есть только два разных значения: «Мужчина» и «Женщина», поэтому создавать индекс нет необходимости. Если вы создадите индекс, это не только не повысит эффективность запросов, но и серьезно снизит скорость обновления.
● Если необходимо отсортировать несколько столбцов, вы можете создать составной индекс для этих столбцов.
●Используйте системные инструменты. Например, в базе данных Informix есть инструмент tbcheck, который может проверять подозрительные индексы. На некоторых серверах баз данных индекс может быть недействительным или эффективность чтения может снижаться из-за частых операций. Если запрос с использованием индекса замедляется без видимой причины, вы можете попробовать использовать инструмент tbcheck для проверки целостности индекса. и отремонтировать его при необходимости. Кроме того, когда таблица базы данных обновляет большой объем данных, удаление и перестроение индекса может повысить скорость запросов.
2. Избегайте или упрощайте сортировку
Повторную сортировку больших таблиц следует упростить или избегать. Оптимизатор избегает этапа сортировки, когда может использовать индекс для автоматического вывода результатов в правильном порядке. Вот некоторые влияющие факторы:
●Индекс не включает один или несколько столбцов для сортировки;
●Порядок столбцов в предложении group by или order by отличается от порядка индекса;
●Отсортированные столбцы взяты из разных таблиц.
Чтобы избежать ненужной сортировки, необходимо правильно добавлять индексы и разумно объединять таблицы базы данных (хотя иногда это может влиять на нормализацию таблицы, но улучшение эффективности того стоит). Если сортировка неизбежна, следует попытаться упростить ее, например сузить диапазон столбцов для сортировки и т. д.
3. Устраните последовательный доступ к данным больших строк таблицы.
Во вложенных запросах последовательный доступ к таблицам может оказать фатальное влияние на эффективность запросов. Например, при использовании стратегии последовательного доступа, если запрос с тремя вложенными уровнями запрашивает 1000 строк на каждом уровне, то этот запрос запросит 1 миллиард строк данных. Основной способ избежать этого — индексировать соединяемые столбцы. Например, две таблицы: таблица студентов (номер студента, имя, возраст...) и таблица выбора курса (номер студента, номер курса, оценки). Если необходимо соединить две таблицы, необходимо создать индекс по полю соединения «номер студента».
Вы также можете использовать объединения, чтобы избежать последовательного доступа. Хотя во всех проверочных столбцах имеются индексы, некоторые формы предложенийwhere заставляют оптимизатора использовать последовательный доступ. Следующий запрос вызовет последовательные операции с таблицей заказов: SELECT * FROMorders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
Хотя индексы customer_num и order_num существуют, оптимизатор по-прежнему использует последовательный путь доступа для сканирования всей таблицы в приведенном выше операторе. Поскольку этот оператор извлекает отдельную коллекцию строк, его следует заменить следующим оператором:
ВЫБЕРИТЕ * ИЗ заказов ГДЕ customer_num=104 И order_num>1001
СОЮЗ
ВЫБРАТЬ * ИЗ заказов ГДЕ order_num=1008
Это позволяет использовать индексный путь для обработки запросов.
4. Избегайте коррелированных подзапросов
Если метка столбца появляется как в основном запросе, так и в запросе в предложенииwhere, вполне вероятно, что подзапрос необходимо будет повторно запросить, когда значение столбца в основном запросе изменится. Чем больше вложенных уровней запроса, тем ниже эффективность, поэтому следует избегать подзапросов, насколько это возможно. Если подзапрос неизбежен, отфильтруйте как можно больше строк в подзапросе.
5. Избегайте сложных регулярных выражений
Ключевые слова MATCHES и LIKE поддерживают сопоставление с подстановочными знаками, технически называемое регулярными выражениями. Но такое сопоставление занимает особенно много времени. Например: ВЫБЕРИТЕ * ОТ клиента ГДЕ почтовый индекс КАК «98_ _ _»
Даже если индекс создается по полю почтового индекса, в этом случае все равно используется последовательное сканирование. Если вы измените оператор на SELECT * FROM customer WHERE zipcode > «98000», индекс будет использоваться для запроса при выполнении запроса, что, очевидно, значительно улучшит скорость.
Кроме того, избегайте неначинающихся подстрок. Например, оператор: SELECT * FROM customer WHERE zipcode[2, 3]>"80" использует неначинающуюся подстроку в предложенииwhere, поэтому этот оператор не использует индекс.
6. Используйте временные таблицы для ускорения запросов
Сортировка подмножества таблицы и создание временной таблицы иногда может ускорить выполнение запросов. Это помогает избежать множественных операций сортировки и в целом упрощает работу оптимизатора. Например: SELECT cust.name, rcVBles.balance,... другие столбцы.
ВЫБЕРИТЕ cust.name,rcVBles.balance,... другие столбцы
ИЗ cust,rcvbles
ГДЕ cust.customer_id = rcvlbes.customer_id
И rcvblls.balance>0
И cust.почтовый индекс>"98000"
ЗАКАЗАТЬ ПО cust.name
Если этот запрос необходимо выполнить несколько раз, а не только один раз, вы можете найти всех неоплаченных клиентов во временном файле и отсортировать их по имени клиента: ВЫБЕРИТЕ cust.name, rcvbles.balance,...другие столбцы.
ВЫБЕРИТЕ cust.name,rcvbles.balance,... другие столбцы
ИЗ cust,rcvbles
ГДЕ cust.customer_id = rcvlbes.customer_id
И rcvblls.balance>0
ЗАКАЗАТЬ ПО cust.name
INTO TEMP cust_with_balance
Затем запросите временную таблицу следующим образом: SELECT * FROM cust_with_balance.
ГДЕ почтовый индекс> «98000»
Во временной таблице меньше строк, чем в основной таблице, а физический порядок является обязательным, что уменьшает дисковый ввод-вывод, поэтому рабочая нагрузка запросов может быть значительно уменьшена.
Примечание. После создания временной таблицы в ней не будут отражены изменения основной таблицы. Если данные в основной таблице часто изменяются, будьте осторожны, чтобы не потерять данные.
7. Используйте сортировку для замены непоследовательного доступа
Непоследовательный доступ к диску является самой медленной операцией и представляет собой движение взад и вперед рычага доступа к диску. Операторы SQL скрывают эту ситуацию, упрощая нам написание запросов, требующих доступа к большому количеству непоследовательных страниц при написании приложений. Иногда использование возможностей сортировки базы данных вместо непоследовательного доступа может улучшить запросы.
-