индексация таблиц и ее назначение
индексы в выделенных таблицах SQL пула в Azure синапсе Analytics
Рекомендации и примеры индексирования таблиц в выделенном пуле SQL в Azure синапсе Analytics.
Типы индексов
В выделенном пуле SQL доступно несколько вариантов индексирования, включая использование кластеризованных индексов Columnstore, а также других кластеризованных и некластеризованных индексов. Кроме того, предоставляется вариант без индексов — использование кучи.
Сведения о том, как создать таблицу с индексом, см. в документации по операции CREATE TABLE (для выделенного пула SQL).
Кластеризованные индексы Columnstore
Если для таблицы не заданы параметры индексирования, по умолчанию выделенный пул SQL создает кластеризованный индекс columnstore. Кластерированные таблицы Columnstore предоставляют не только самый высокий уровень сжатия данных, но и высочайшую производительность запросов. Обычно эти таблицы более эффективны, чем таблицы с кластеризованными индексами и таблицы без кластеризованных индексов, и их рекомендуется использовать в больших таблицах. Таким образом, если вы не уверены, какой метод индексации применить к вашей таблице, используйте кластеризованные индексы Columnstore.
Чтобы создать кластеризованную таблицу columnstore, просто укажите CLUSTERED COLUMNSTORE INDEX в предложении WITH или оставьте предложение WITH выключенным:
Есть несколько сценариев, в которых не рекомендуется использовать кластеризованный индекс Columnstore.
Таблицы без кластеризованных индексов
Если вам необходимо временно разместить данные в выделенном пуле SQL, использование таблицы без кластеризованных индексов может существенно сократить время загрузки данных. Это связано с тем, что загрузка в кучи выполняется быстрее, чем в таблицы с кластеризованными индексами, и в некоторых случаях из кэша можно выполнять последующее считывание. Загрузка таблицы в таблицу без кластеризованных индексов перед выполнением преобразования данных выполняется намного быстрее, чем загрузка данных в таблицу с кластеризованными индексами Columnstore. Кроме того, загрузка данных во временную таблицу выполняется быстрее, чем загрузка таблицы в постоянное хранилище. После загрузки данных можно создавать индексы в таблице для повышения производительности запросов.
Сжатие в кластеризованных таблицах columnstore выполнятся оптимально при наличии более 60 миллионов строк. Для небольших таблиц уточняющих запросов (менее 60 млн строк) для повышения производительности запросов лучше использовать кучу или кластеризованный индекс.
Чтобы создать таблицу без кластеризованных индексов, укажите в предложении WITH параметр HEAP.
Кластеризованные и некластеризованные индексы
Кластеризованные индексы могут быть более эффективны, когда требуется извлечь отдельную строку. Кластеризованные или вторичные некластеризованные индексы рекомендуется использовать, если требуется выполнить запросы на очень быструю подстановку одной или нескольких строк в таблицу. Недостаток использования кластеризованного индекса заключается в том, что эффективными будут только те запросы, в которых используется высокоизбирательный фильтр для столбца с кластеризованным индексом. Чтобы улучшить фильтр в других столбцах, добавьте в них некластеризованный индекс. Однако каждый добавленный в таблицу индекс увеличивает пространство и время обработки загрузки.
Чтобы создать таблицу с кластеризованным индексом, укажите в предложении WITH параметр CLUSTERED INDEX.
Чтобы добавить некластеризованный индекс для таблицы, используйте приведенный ниже синтаксис.
Оптимизация кластеризованных индексов Columnstore
В кластеризованных таблицах Columnstore данные упорядочены по сегментам. Качество сегментов существенно влияет на эффективность выполнения запросов в таблице Columnstore. Его можно определить по числу строк в сжатой группе строк. Наиболее оптимальное качество сегментов — это то, что по крайней мере 100 K строк на сжатую группу строк и производительность достигается по мере того, как количество строк на группу строк составляет 1 048 576 строк, то есть наибольшей строки, которую может содержать группа строк.
Приведенное ниже представление можно создать и использовать в системе для вычисления среднего количества строк на группу строк и определения всех неоптимальных индексов columnstore кластера. Последний столбец в этом представлении создает инструкцию SQL, которую можно использовать для перестроения индексов.
После создания представления выполните этот запрос, чтобы найти таблицы с группами строк с количеством строк менее 100 КБ. Если вы ищете более оптимальное качество сегментов, вам может потребоваться увеличить пороговое значение в 100 КБ.
После выполнения запроса можно начать просмотр данных и анализ результатов. В таблице ниже показано, на какие данные следует обратить внимание при анализе групп строк.
Влияние обслуживания индекса
Для таблицы с упорядоченным кластеризованным индексом columnstore ALTER INDEX REBUILD будет выполнять повторную сортировку данных с помощью tempdb. Мониторинг базы данных tempdb во время операций перестроения. Если вам необходимо больше места в базе данных tempdb, можно увеличить объем хранилища данных. Масштаб можно вернуть обратно после перестроения индекса.
Причины низкого качества индекса Columnstore
Если вы обнаружили таблицы с сегментами низкого качества, необходимо определить причину. Ниже описаны распространенные причины низкого качества сегментов:
По приведенным выше причинам количество строк в каждой группе будет значительно меньше 1 миллиона. Кроме того, вместо перехода в сжатую группу строки могут попасть в разностную группу строк.
Нехватка памяти при создании индекса.
Количество строк в сжатой группе строк непосредственно зависит от ширины строк и объема памяти, доступного для обработки группы строк. Если во время записи строк в таблицы Columnstore возникает нехватка памяти, качество сегмента Columnstore может ухудшиться. Поэтому для записи строк в таблицы Columnstore рекомендуется выделить максимально возможный объем памяти. Так как показатели памяти и параллелизма взаимосвязаны, выделение памяти зависит от класса данных в каждой строке таблицы, числа единиц базы данных хранилища данных, выделенного для системы, а также числа слотов параллелизма, предоставляемых для сеанса записи данных в таблицу.
Высокая интенсивность операций DML.
Интенсивные операции DML по обновлению и удалению строк приводят к неэффективной работе Columnstore. Это особенно применимо в случае изменения большей части строк в группе строк.
Пакетные операции обновления и вставки, превышающие пороговое значение в 102 400 строк для распределения с выравниванием по секциям, записываются непосредственно в формате columnstore. Чтобы это произошло, при условии равномерного распределения потребуется изменить более 6,144 млн строк в рамках одной операции. Если число строк для данного распределения с распределением по секциям меньше 102 400, то строки переходят в разностное хранилище и остаются там, пока не будут вставлены или изменены достаточное количество строк, чтобы закрыть группу строк или перестроить индекс.
Небольшой объем операций загрузки или потоковые загрузки
Небольшие загрузки, поступающие в выделенный пул SQL, иногда называют потоковыми. Обычно они представляют собой практически постоянный поток данных, принимаемых системой. Однако поскольку этот поток является не совсем непрерывным, объем строк невелик. Чаще всего количество данных значительно ниже порогового значения, необходимого для непосредственной загрузки в формате columnstore.
В таких ситуациях рекомендуется сначала разместить данные в хранилище BLOB-объектов Azure, накопить их и лишь затем загрузить. Этот метод часто называют микропакетной обработкой.
Слишком много секций
На качество индекса также влияет секционирование в кластеризованных таблицах Columnstore. Перед секционированием данные в выделенном пуле SQL разбиваются на 60 баз данных. Во время секционирования выполняется дальнейшее разделение данных. Чтобы воспользоваться преимуществами кластеризованного индекса columnstore при секционировании данных, каждая секция должна содержать по крайней мере 1 миллион строк. Если таблица состоит из 100 секций, то чтобы использовать кластеризованный индекс columnstore, она должна состоять из минимум 6 миллиардов строк (60 распределений * 100 секций * 1 миллион строк). Если эта таблица не содержит такого количества строк, рекомендуется уменьшить количество секций или использовать таблицу без кластеризованных индексов.
Когда в таблицу будут загружены данные, чтобы определить и перестроить таблицы с неоптимальными кластеризованными индексами Columnstore, выполните приведенные ниже действия.
Повышение качества сегментов за счет перестроения индексов
Шаг 1. Определение или создание пользователя, который использует соответствующий класс ресурсов
Простой способ быстро повысить качество сегментов — перестроить индекс. SQL, возвращаемое приведенным выше представлением, содержит инструкцию ALTER INDEX rebuild, которую можно использовать для перестроения индексов. При перестроении индексов необходимо выделить достаточный объем памяти для сеанса. Для этого повысьте класс ресурсов для пользователя, который имеет разрешение на перестроение индекса для этой таблицы, до рекомендованного минимального класса.
Ниже приведен пример того, как можно выделить дополнительный объем памяти для пользователя, увеличив класс ресурсов. Сведения об использовании классов ресурсов см. в статье Классы ресурсов для управления рабочими нагрузками.
Шаг 2. Перестройка кластеризованных индексов Columnstore, используя пользователя с более высоким классом ресурсов
Войдите от имени пользователя из шага 1 ( LoadUser ), который теперь использует более высокий класс ресурсов, и выполните инструкции ALTER INDEX. Убедитесь, что этот пользователь имеет разрешение ALTER в отношении таблиц, в которых будет выполнятся перестроение индекса. В этих примерах показано перестроение всего индекса Columnstore и перестроение одной секции. В больших таблицах целесообразно перестроить только одну секцию за раз.
Кроме того, вместо перестроения индекса можно копировать таблицу в новую таблицу, используя инструкцию CTAS. Какой способ лучше? Для больших объемов данных инструкция CTAS обычно выполняется быстрее, чем ALTER INDEX. Что же касается небольших объемов данных, рекомендуем использовать инструкцию ALTER INDEX. Она проще в использовании и не требует замены таблицы.
В выделенном пуле SQL операция перестроения индекса выполняется в автономном режиме. Дополнительные сведения о перестройке индексов см. в разделе об использовании инструкции ALTER INDEX REORGANIZE в статьях Дефрагментация индексов columnstore и работе с ALTER INDEX (Transact-SQL).
Шаг 3. Проверка улучшения качества кластеризованных сегментов Columnstore
Повторно выполните запрос на определение таблицы с сегментами низкого качества и убедитесь, что качество сегментов улучшилось. Если это не так, возможно, в таблице слишком широкие строки. Если для перестроения индексов требуется более высокий объем памяти,
Перестроение индексов с помощью CTAS и переключения секций
В этом примере для перестроения секции таблицы используется инструкция CREATE TABLE AS SELECT (CTAS) и переключение секций.
Дополнительные сведения о перестроении секций с помощью CTAS см. в статье Секционирование таблиц в выделенном пуле SQL.
15) Индексирование в базах данных
Что такое индексирование?
INDEXING — это метод структуры данных, который позволяет вам быстро извлекать записи из файла базы данных. Индекс — это небольшая таблица, имеющая всего два столбца. Первый столбец содержит копию первичного или потенциального ключа таблицы. Его второй столбец содержит набор указателей для хранения адреса дискового блока, где хранится это конкретное значение ключа.
Из этого руководства по индексированию СУБД вы узнаете:
Типы индексации
Индексация базы данных определяется на основе ее атрибутов индексации. Два основных типа методов индексации:
Первичная индексация
Первичный индекс — это упорядоченный файл с фиксированной длиной и двумя полями. Первое поле — это тот же первичный ключ, а второе поле указывает на этот конкретный блок данных. В первичном индексе всегда существует отношение один к одному между записями в таблице индекса.
Первичная индексация также делится на два типа.
Плотный индекс
В плотном индексе запись создается для каждого поискового ключа, оцененного в базе данных. Это помогает быстрее выполнять поиск, но требует больше места для хранения записей индекса. В этом индексировании записи метода содержат значение ключа поиска и указывают на реальную запись на диске.
Разреженный индекс
Это индексная запись, которая отображается только для некоторых значений в файле. Разреженный индекс поможет вам решить проблемы плотного индексирования. В этом методе методики индексирования диапазон столбцов индекса хранит один и тот же адрес блока данных, и когда данные должны быть извлечены, адрес блока будет выбран.
Однако разреженный индекс хранит записи индекса только для некоторых значений ключа поиска. Ему требуется меньше места, меньше затрат на обслуживание для вставки и удаления, но он медленнее по сравнению с плотным индексом для поиска записей.
Пример разреженного индекса
Вторичный индекс
Вторичный индекс может быть создан с помощью поля, которое имеет уникальное значение для каждой записи, и это должен быть ключ-кандидат. Он также известен как некластеризованный индекс.
Этот двухуровневый метод индексации базы данных используется для уменьшения размера отображения первого уровня. Для первого уровня из-за этого выбирается большой диапазон чисел; размер отображения всегда остается небольшим.
Пример вторичной индексации
В базе данных банковского счета данные хранятся последовательно с помощью acc_no; Вы можете найти все счета в конкретном отделении банка ABC.
Здесь вы можете иметь вторичный индекс для каждого поискового ключа. Индексная запись — это точка записи в корзину, которая содержит указатели на все записи с определенным значением ключа поиска.
Индекс кластеризации
В кластеризованном индексе сами записи хранятся в индексе, а не в указателях. Иногда индекс создается для столбцов не первичного ключа, которые могут быть не уникальными для каждой записи. В такой ситуации вы можете сгруппировать два или более столбцов, чтобы получить уникальные значения и создать индекс, который называется кластеризованным индексом. Это также поможет вам быстрее идентифицировать запись.
Пример:
Давайте предположим, что компания набрала много сотрудников в различных отделах. В этом случае кластерная индексация должна быть создана для всех сотрудников, принадлежащих к одному отделу.
Он рассматривается в одном кластере, а индексные точки указывают на кластер в целом. Здесь Department _no — неуникальный ключ.
Что такое многоуровневый индекс?
Многоуровневое индексирование создается, когда первичный индекс не помещается в памяти. В этом методе индексации вы можете сократить число обращений к диску, чтобы сократить любую запись и сохранить ее на диске в виде последовательного файла, а также создать разреженную базу для этого файла.
B-Tree Index
Индекс B-дерева — это широко используемые структуры данных для индексации. Это метод многоуровневого индексного формата, который сбалансирован бинарными деревьями поиска. Все конечные узлы дерева B обозначают фактические указатели данных.
Более того, все конечные узлы связаны между собой списком ссылок, что позволяет дереву B поддерживать как произвольный, так и последовательный доступ.
Преимущества индексации
Важные плюсы / преимущества индексирования:
Недостатки индексации
Важными недостатками / минусами индексации являются:
Индексация таблиц баз данных MySQL
Вспомним любую публичную библиотеку. Пусть детскую или даже школьную. Помните зал со стеллажами книг? И даже если вы были очень давно в библиотеке, вы прекрасно знаете, что все книги в этом уважаемом заведении расставлены не абы как, не в порядке их поступления в библиотеку (как поступают данные в базу), а по каким-то правилам. Обычно, книги разносят по темам, авторам и по алфавиту.
Я думаю, излишне объяснять, зачем все это делается, и почему библиотекари так ревностно следят за порядком размещения книг на стеллажах. Но я обращу ваше внимание на сравнительную эффективность поиска в такой структурированной системе, которой, кстати, обычно пренебрегают при построении и использовании компьютерных баз данных.
Предположите, что вы в библиотеке ищите книгу «Как выращивать цветы в сухой местности».
Если вы начнете перебирать все книги в библиотеке, то у вас на это уйдет не день или даже не месяц, если это крупная библиотека.
Но если вы знаете автора или год или тему книги, то, подойдя к соответствующим стеллажам, вы найдете издание за несколько минут, а то и секунд. Как, собственно, это и бывает в библиотеке.
Так зачем же мы заставляем наши компьютеры искать необходимые нам данные методом глубокого перебора? Только потому, что они это делают быстро?
Да, быстро. Но это если поиск надо произвести в тысячах записей. А если речь идет о миллионах? Или вы думаете, что ваша база данных не так велика, чтобы заниматься ее оптимизацией? Ошибаетесь, дорогие мои. Как только к вашей базе обратятся сотни человек, так ваши тысячи записей тут же превратятся для движка баз данных в миллионы! И ваш хостинг провайдер совершенно резонно сделает вам замечание.
Итак, в том, что базы данных надо индексировать не сомневается ни один здравомыслящий программист. Правильно построенные индексы позволяют находить нужную информацию очень быстро.
Как это происходит технически — нам знать не обязательно. Достаточно иметь в виду, что MySQL, как и любой другой движок баз данных, использует дополнительное место на диске для хранения индексных файлов. Это надо знать, только для того, чтобы не плодить ненужных индексов. Во всем надо знать меру. Даже в таком полезном деле, как индексация таблиц.
Так давайте сразу и определимся с тем, что нам надо индексировать: индексировать надо те поля таблицы, по которым происходит поиск или сортировка данных.
Например, у нас есть таблица book и таблица author. В первой мы храним информацию о книгах, а во второй — информацию об их авторах. Разумеется, самое логичное для подобной базы — искать в ней книги по названию и по автору.Не имея индексации такой базы, примитивный поиск по первым буквам произведения вынудит компьютер просмотреть все записи в таблице, чтобы выдать полный результат. Если база большая, на это уйдет время.
Если же мы добавим в таблицу book индекс по полю book_name (название книги), то MySQL создаст индекс этой таблицы. То есть, отсортирует таблицу по указанному полю и расставит метки и ссылки на ячейки в реальной таблице. Другими словами, индекс — это копия таблицы без данных, но отсортированная по определенным правилам, где каждая ячейка — есть ссылка на родительскую ячейку в основной таблице. Надеюсь, что выразился достаточно ясно.
Построив индекс по полю book_name, наш компьютер найдет по первым буквам нужные нам произведения практически мгновенно, ведь ему теперь не надо перебирать всю таблицу, а достаточно посмотреть на ту ее часть, где хранятся записи на нужную букву.
Еще проще говоря, если мы ищем книги на букву «К», то компьютер не станет перебирать записи, начинающиеся на другие буквы, прекрасно зная, что там нет записей, удовлетворяющих запросу. И если в нашей книжной базе находится 100.000 книг, среди которых только 30 на букву Ж, то по запросу «найти все книги на букву Ж», компьютер переберет только 30 записей при наличии индекса, или переберет 100.000 записей при его отсутствии. По-моему, польза очевидна.
Кстати, индексация текстовых полей — занятие чуть более сложное, чем индексация полей другого типа. Поясню. Цифровые, буквенные, булевы, поля дат, времени и другие — индексируются, как правило, без каких либо дополнительных размышлений. Скажем, если в таблице книг есть поле book_date, хранящее дату публикации книги, то добавление индекса к такому полю будет выглядеть примерно так:
alter table tbl_book add index i_date (book_date);
Эта директива указывает MySQL создать индекс по полю book_date.
Теперь компьютеру не составит труда найти все книги 1993-го года или выстроить все найденные книги в порядке даты их публикации. Точно так же можно создать индексы для других полей, по которым нам интересно производить поиск или сортировку.
Создание же индекса по текстовому полю осложняется только тем, что надо указать количество символов от начала записи, по которым надо построить индекс. Тут вы уже сами должны оценить размер базы, похожесть первых символов разных записей и так далее.
Например, если мы строим индекс по названиям улиц, то нам не надо индексировать их по всей длине, а можно предположить, что подавляющее большинство записей начнут различаться уже где-то на пятой букве, а если мы индексируем коды шариковых подшипников, то индексировать лучше как можно больше символов, ибо большое количество изделий могут нести коды, отличающиеся только последними буквами или цифрами.
Иногда нам не надо специально строить индексы. Достаточно того, что MySQL сам построит индексы по полю, если при объявлении структуры таблицы мы зададим полю уникальность UNIQUE, говорящую о том, что данное поле не может хранить два одинаковых значения.
Другое ключевой слово, создающее индексы — KEY. Помните объявление primary key, которое я обязательно использую в каждой таблице для id-поля.Если вы хотите увидеть, какие поля в таблице проиндексированы, дайте команду MySQL:
и MySQL выведет всю информацию о таблице tbl_name, включая отметки об индексации полей, или более подробно, только об индексах:
show index from tbl_name;
Остается еще отметить, что в индексе может участвовать множество полей. Не обязательно одно. Если в базе накладных чаще всего производится поиск по сумме с учетом диапазона дат, то логично создать индекс по этим двум полям: сумма и дата. Именно, создать один индекс по двум полям, а не два индекса по каждому полю!
alter table tbl_name add index i_name (field1, field2);
Поиск по уникальным индексам производится чуть быстрее, поэтому, если значение какого-то поля должно быть обязательно уникальным — не поленитесь отметить это в конструкции таблицы. MySQL отблагодарит вас скоростью своих ответов на ваши вопросы. Прежде чем попрощаться, я бы хотел подсказать вам одну полезную директиву MySQL. Называется она explain. Если вы поставите это слово перед любым запросом к базе данных, MySQL не станет выводить вам результат запроса, а покажет подробную информацию о том, какими средствами пришлось воспользоваться и сколько операций пришлось произвести, чтобы получить ответ на ваш запрос. Это волшебное слово explain позволит оценить эффективность любого запроса и отрегулировать все индексы вашей базы.
Индексирование таблиц MySQL: создание индекса MySQL, удалить индекс и другие операции
От правильно составленной структуры базы данных очень сильно зависит скорость работы всего проекта. Еще одним инструментом, позволяющим значительно сократить время отклика базы, являются индексы БД MySQL. Перед тем, как рассматривать различные операции с ними, стоит определиться, что же такое индексы СУБД и какие преимущества можно получить при их использовании.
Индексы – это специфические объекты базы данных, позволяющие значительно повысить скорость поиска значений из таблиц базы данных. MySQL index представляет из себя структуру, в которой хранятся значения одного (в некоторых случаях — нескольких) столбца таблицы и ссылок на строки, где эти значения расположены. Так как для хранения индексов чаще всего используются бинарные деревья, поиск среди них занимает чрезвычайно мало места.
Принцип работы индексов очень прост. Для примера рассмотрим запрос:
SELECT Name FROM Persons WHERE Points Persons, в которой есть два поля: Name и Points. При поиске в неиндексированной таблице, система последовательно перебирает все строки и сравнивает их с 10. При выполнении условия – выводит их. Даже при наличии в таблице всего 1000 записей, такой запрос может оказаться ресурсоемким, если одновременно поступит от 100 абонентов. Индексирование таблиц MySQL позволяет сократить число операций. Если поле Points будет проиндексировано, поиск будет проводиться по самому индексу, без перебора всех строк таблицы.
Индексы бывают двух видов:
Индексирование таблиц MySQL
Использование индексирования таблиц MySQL имеет свои особенности:
Определившись с назначением и особенностями использования индексов СУБД, перейдем к основным операциям с ними.
Создание индекса в MySQL
Для операции создания индекса MySQL предусматривает такой синтаксис:
CREATE [UNIQUE | FULLTEXT] INDEX ON ( [(length)],… )
где:
[UNIQUE | FULLTEXT] – определяет, будет ли индекс содержать только уникальные значения (UNIQUE), или в нем будут присутствовать и повторяющиеся значения (FULLTEXT). По умолчанию используется режим FULLTEXT. Length – определяет длину символов поля для индексирования. Если Length оставить пустым, то в индекс попадет поле целиком вне зависимости от длины.
— уникальный идентификатор индекса. Если это поле не определено, ему будет присвоено имя первого подлежащего индексации столбца.
В версиях MySQL младше 3.22 эта команда не активна, а в более поздних – в плане создания индексов работает аналогично команде ALTER TABLE. При работе с ALTER TABLE добавление записей происходит при помощи команды ADD INDEX, MySQL при помощи этой команды позволяет создавать индексы PRIMARY KEY (создать индекс такого типа при помощи CREATE INDEX нельзя).
Удаление индекса в MySQL
В MySQL удалить индекс можно при помощи такого оператора:
DROP INDEX ON
Читайте также
В MySQL есть два расширения, которые позволяют атомарно вставить или обновить запись. Годятся они для штук с довольно высокой нагрузкой,…
Индексы Индекс формируется из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки таблицы и, таким образом, позволяет…
Есть таблица меню в БД с полем порядка order_id, но порядок неправильный и надо порядок обновить по алфавиту другого поля title.…