10 операторов индекса T-SQL, необходимых для работы с администратором баз данных

Администраторы баз данных SQL Server (администраторы баз данных) — администраторы баз данных очень хорошо знают, что записи индекса в базе данных очень похожи на индекс в разделе библиотеки или просто понимают, что индекс в базе данных — это структура, которая тесно связана с таблицами для быстрого сбора информации из строк в этой таблице. В следующей статье мы покажем вам операторы T-SQL, относящиеся к Index in SQL, которые очень полезны для любого администратора баз данных, выполняющего эту работу. В частности, мы разделим на 3 основные категории: концепция индекса, создание операторов запросов — запросы, связанные с информацией, и, наконец, процесс обслуживания — обслуживание.

Общее понятие индекса:

1. Кластерный индекс:

Основная задача — хранить в таблице данные строк, упорядоченных по значению ключевого ключа. В каждой таблице можно создать только один кластеризованный индекс, потому что данные строк могут быть расположены только в определенном порядке. Еще один момент заключается в том, что кластеризованный индекс можно «сгенерировать» при создании ограничений, таких как первичный ключ, в существующей таблице данных.

Например:

ИЗМЕНИТЬ ТАБЛИЦУ [MyAddress]
ДОБАВИТЬ ОГРАНИЧЕНИЕ [PK_Address_AddressID] ПЕРВИЧНЫЙ КЛЮЧ КЛАСТЕРИРОВАН
(
[AddressID] ASC
) НА [PRIMARY]
ИДТИ

Кроме того, кластеризованный индекс также может быть создан для каждого столбца без связанной ссылки. Например:

СОЗДАТЬ КЛАСТЕРНЫЙ ИНДЕКС [MyAddress_id_CIX] НА [MyAddress1]
(
[ID] ASC
) НА [PRIMARY]
ИДТИ

2. Некластерный индекс:

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

Некластеризованные индексы могут быть созданы для существующих таблиц, включая столбцы, не входящие в кластеризованный индекс. Например:

СОЗДАТЬ УНИКАЛЬНЫЙ НЕКЛАСТЕРНЫЙ ИНДЕКС
[NIX_col5_col2_col3_col4_col6]
НА [MyAddress]
(
[AddressLine1] ASC,
[AddressLine2] ASC,
[City] ASC,
[StateProvinceID] ASC,
[PostalCode] ASC
) НА [PRIMARY]
ИДТИ

Или некластеризованный индекс также может быть создан при создании ссылок в существующей таблице, например:

ИЗМЕНИТЬ ТАБЛИЦУ [MyAddressType]
ДОБАВИТЬ ОГРАНИЧЕНИЕ [DEFF_MyAddressType_ModifiedDate]
ПО УМОЛЧАНИЮ (getdate ()) ДЛЯ [ModifiedDate]
ИДТИ

3. XML-индекс:

Еще одна концепция, созданная для столбца данных XML и кластерного индекса по первичному ключу. 1 XML-индекс похож на Frimary:

СОЗДАТЬ ПЕРВИЧНЫЙ XML-ИНДЕКС idx_xCol_MyTable в MyTable (xCol)

А с вторичным индексом XML это выглядит следующим образом:

СОЗДАТЬ ТАБЛИЦУ MyTable (ПЕРВИЧНЫЙ КЛЮЧ INT INT, XmlCol XML)
ИДТИ
— Создать первичный индекс.
СОЗДАТЬ ПЕРВИЧНЫЙ ИНДЕКС XML PIdx_MyTable_XmlCol
ON T (XmlCol)
ИДТИ
— Создайте вторичные индексы (PATH, VALUE, PROPERTY).
СОЗДАТЬ XML-ИНДЕКС PIdx_MyTable_XmlCol_PATH НА MyTable (XmlCol)
ИСПОЛЬЗОВАНИЕ XML-ИНДЕКСА PIdx_MyTable_XmlCol
ДЛЯ ПУТИ
ИДТИ
СОЗДАТЬ XML-ИНДЕКС PIdx_MyTable_XmlCol_VALUE ON T (XmlCol)
ИСПОЛЬЗОВАНИЕ XML-ИНДЕКСА PIdx_MyTable_XmlCol
НА СТОИМОСТЬ
ИДТИ

4. Пространственный индекс:

Компонент SQL Server 2008 предоставляет пользователям специальные столбцы данных, относящиеся к символьным данным, относящимся к пространственной области, таким как география и геометрия.

1 структуру пространственного индекса можно создать с помощью следующего синтаксиса:

СОЗДАТЬ ТАБЛИЦУ MySpatialTable (первичный идентификатор int, geometry_col geometry);
СОЗДАТЬ ПРОСТРАНСТВЕННЫЙ ИНДЕКС SIndx_MySpatialTable_geometry_col1
НА MySpatialTable (geometry_col)
С (BOUNDING_BOX = (0, 0, 500, 200));

Индекс запроса, связанный с данными метаданных:

5. Выполните поиск по всем индексам:

Во-первых, если вы хотите найти все индексы, используйте оператор запроса — запрос по таблице, столбцу и ключу индекса существующей базы данных.

ВЫБЕРИТЕ OBJECT_SCHEMA_NAME (BaseT. [Object_id], DB_ID ()) КАК [Schema],
BaseT. [Name] В ВИДЕ [table_name], И. [name] В ВИДЕ [index_name], AC. [Name] В ВИДЕ [column_name],
Я. [type_desc]
ОТ sys. [Tables] AS BaseT
INNER JOIN sys. [Indexes] Я НА BaseT. [Object_id] = I. [object_id]
INNER JOIN sys. [Index_columns] IC ON I. [object_id] = IC. [Object_id]
INNER JOIN sys. [All_columns] AC ON BaseT. [Object_id] = AC. [Object_id] И IC. [Column_id] = AC. [Column_id]
ГДЕ BaseT. [Is_ms_shipped] = 0 И I. [type_desc] «КУЧА»
ЗАКАЗАТЬ ПО BaseT. [Name], И. [index_id], IC. [Key_ordinal]

6. Фрагментация:

In Fragmentation — поиск элементов индекса в состоянии «Фрагментация» всех таблиц данных в текущей базе данных. Вот примеры:

ВЫБЕРИТЕ имя_объекта (IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
ОТ sys.dm_db_index_physical_stats (db_id (DB_NAME ()), NULL, NULL, NULL, ‘ПОДРОБНЕЕ’) IPS
ПРИСОЕДИНЯЙТЕСЬ к sys.tables ST С (nolock) ON IPS.object_id = ST.object_id
ПРИСОЕДИНЯЙТЕСЬ к sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
ГДЕ ST.is_ms_shipped = 0
заказ по IPS.avg_fragment_size_in_pages desc

7. Отсутствующий индекс:

При потере компонентов индекса SQL Server по-прежнему может отслеживать и отслеживать состояние созданных индексов, чтобы повысить производительность строки запроса. В приведенном ниже коде есть функция перечисления всех потерянных записей указателя:

ВЫБЕРИТЕ sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) Влияние на AS
, ‘CREATE NONCLUSTERED INDEX ix_IndexName ON’ + sys.objects.name COLLATE DATABASE_DEFAULT + ‘(‘ + IsNull (mid.equality_columns, ») + CASE WHEN mid.inequality_columns IS NULL
ТОГДА »
ИНАЧЕ СЛУЧАЙ, КОГДА mid.equality_columns ЕСТЬ NULL
ТОГДА »
ELSE ‘,’ END + mid.inequality_columns END + ‘)’ + CASE WHEN mid.included_columns IS NULL
ТОГДА »
ИНАЧЕ ‘ВКЛЮЧИТЬ (‘ + mid.included_columns + ‘)’ КОНЕЦ + ‘;’ AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
ИЗ sys.dm_db_missing_index_group_stats AS migs
ВНУТРЕННЕЕ СОЕДИНЕНИЕ sys.dm_db_missing_index_groups КАК mig ON migs.group_handle = mig.index_group_handle
ВНУТРЕННЕЕ СОЕДИНЕНИЕ sys.dm_db_missing_index_details КАК mid ON mig.index_handle = mid.index_handle И mid.database_id = DB_ID ()
ВНУТРЕННЕЕ СОЕДИНЕНИЕ sys.objects С (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
ГДЕ (migs.group_handle IN
(
ВЫБРАТЬ TOP (500) group_handle
ИЗ sys.dm_db_missing_index_group_stats С (nolock)
ЗАКАЗАТЬ ПО (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
И СВОЙСТВО ОБЪЕКТА (sys.objects.OBJECT_ID, ‘isusertable’) = 1
ЗАКАЗАТЬ 2 DESC, 3 DESC

8. Index больше не использует:

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

ВЫБЕРИТЕ o.name, indexname = i.name, i.index_id
, читает = user_seeks + user_scans + user_lookups
, пишет = user_updates
, rows = (SELECT SUM (p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, ДЕЛО
КОГДА s.user_updates
ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
КОНЕЦ КАК reads_per_write
, ‘DROP INDEX’ + QUOTENAME (i.name)
+ ‘ON’ + QUOTENAME (c.name) + ‘.’. + QUOTENAME (OBJECT_NAME (s.object_id)) как ‘оператор удаления’
ИЗ sys.dm_db_index_usage_stats s
ВНУТРЕННЕЕ СОЕДИНЕНИЕ sys.indexes i ON i.index_id = s.index_id И s.object_id = i.object_id
ВНУТРЕННЕЕ СОЕДИНЕНИЕ sys.objects o на s.object_id = o.object_id
ВНУТРЕННЕЕ СОЕДИНЕНИЕ sys.schemas c o.schema_id = c.schema_id
ГДЕ СВОЙСТВО ОБЪЕКТА (s.object_id, ‘IsUserTable’) = 1
И s.database_id = DB_ID ()
И i.type_desc = «некластеризованный»
И i.is_primary_key = 0
И i.is_unique_constraint = 0
И (ВЫБРАТЬ СУММУ (строчки) ИЗ sys.partitions p ГДЕ p.index_id = s.index_id И s.object_id = p.object_id)> 10000
ORDER BY читает

Поддержка индекса:

9. Индекс реструктуризации:

Для построения воссоздайте записи индекса после выполнения дефрагментации или когда вы хотите создать структуру таблицы данных. Следующий оператор похож на DBCC DBREINDEX в версиях SQL Server с 2005 года:

ИСПОЛЬЗУЙТЕ AdventureWorks2008R2;
ИДТИ
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
ВОССТАНОВИТЬ;
ИДТИ

10. РЕОРГАНИЗАЦИЯ:

Синтаксис REORGANIZE применяется к различным индексам конечного уровня, в частности, эти операторы REORGANIZE всегда выполняются онлайн, и технически этот синтаксис аналогичен DBCC INDEXDEFRAG в версиях SQL Server после 2005 года.

ИСПОЛЬЗУЙТЕ AdventureWorks2008R2;
ИДТИ
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
РЕОРГАНИЗАЦИЯ;
ИДТИ

Удачи!

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.