索引sql server_SQL Server索引–系列介绍

索引sql server

描述 (Description)

In this series, we will dive deeply in the SQL Server Indexing field, starting from the surface by understanding the internal structure of the SQL Server tables and indexes, going deeper by describing the guidelines and best practices that we can follow to design the most efficient index and what operations can be performed on the created indexes. Having these knowledges about the SQL Server indexes, we have all the tools that help us in testing the lower part of the ocean and dive deeper with the two main types of the SQL Server Indexes; the Clustered and Non-Clustered, and the other types of indexes that can be customized to serve us improving your environment. After that, the adventure becomes more interesting when learning how to use this knowledge to tune the performance of our queries and touch the bottom of the ocean. In our way back to the surface, and before celebrating our achievements, we will collect statistical information about these indexes and use this information to maintain the indexes to take benefits from it continuously and gain the best application performance.

在本系列文章中，我们将深入了解SQL Server索引字段，从表面开始，首先了解SQL Server表和索引的内部结构，然后再介绍可为设计最高效的方法而遵循的指导原则和最佳实践，从而进行更深入的研究。索引以及可以对创建的索引执行哪些操作。 掌握了有关SQL Server索引的这些知识之后，我们便拥有了所有工具来帮助我们测试海洋的下部并深入研究SQL Server索引的两种主要类型； 集群索引和非集群索引以及可以自定义以帮助我们改善环境的其他类型的索引。 之后，在学习如何使用这些知识来调整查询的性能并触及海底时，冒险变得更加有趣。 回到表面，在庆祝我们的成就之前，我们将收集有关这些索引的统计信息，并使用这些信息来维护这些索引，以不断地从中受益，并获得最佳的应用程序性能。

SQL Server表结构概述 (SQL Server table structure overview )

The tables are the database objects that behave as containers for the data, in which the data will be logically organized in rows and columns format. Tables can be also used as a security boundary/mechanism, where database users can be granted permissions at the table level.

表是充当数据容器的数据库对象，其中数据将以行和列格式逻辑组织。 表还可以用作安全边界/机制，可以在其中向数据库用户授予表级别的权限。

Publish date: March 7, 2018

发布日期： 2018年3月7日

SQL Server索引结构和概念 (SQL Server index structure and concepts )

SQL Server index is created to speed up the data retrieval and the query processing operations from a database You can imagine the table index as a book’s index that allows you to find the requested information very fast.

创建SQL Server索引的目的是加快从数据库的数据检索和查询处理操作的速度。您可以将表索引想象为一本书的索引，从而可以非常快速地找到所需的信息。

Publish date: March 19, 2018

发布日期： 2018年3月19日

SQL Server索引设计基础和准则 (SQL Server index design basics and guidelines )

The decision of choosing the right index that fits the system’s workload is not an easy task, as you need to compromise between the speed of data retrieval operations and the overhead of adding that index on the data modification operations

选择适合系统工作负载的正确索引不是一件容易的事，因为您需要在数据检索操作的速度和在数据修改操作上添加该索引的开销之间进行权衡

Publish date: April 5, 2018

发布日期： 2018年4月5日

SQL Server索引操作 (SQL Server index operations )

In this article, we will go through the operations that can be performed on the SQL Server indexes. A SQL Server index can be created using the CREATE INDEX T-SQL statement or from the New Index dialog box using the SQL Server Management Studio tool.

在本文中，我们将介绍可以在SQL Server索引上执行的操作。 可以使用CREATE INDEX T-SQL语句或使用SQL Server Management Studio工具从“新建索引”对话框中创建SQL Server索引。

Publish date: April 19, 2018

发布日期： 2018年4月19日

设计有效SQL Server群集索引 (Designing effective SQL Server clustered indexes )

We will see how we could design an effective clustered index that the SQL Server Query Optimizer will always take benefits from. SQL Server allows us to create only one Clustered index per each table, as the data can be sorted in the table using one order criteria.

我们将看到如何设计有效的聚集索引，SQL Server Query Optimizer将始终从中受益。 SQL Server允许我们在每个表中仅创建一个聚集索引，因为可以使用一个顺序条件在表中对数据进行排序。

Publish date: May 3, 2018

发布日期： 2018年5月3日

设计有效SQL Server非聚集索引 (Designing effective SQL Server non-clustered indexes )

A Non-clustered index is different from a Clustered index in that, the underlying table rows will not be stored and sorted based on the Non-clustered key, and the leaf level nodes of the Non-clustered index are made of index pages instead of data pages.

非聚集索引与聚集索引的不同之处在于，将不会基于非聚集键存储基础表行并对其进行排序，并且非聚集索引的叶级节点由索引页而不是索引页组成数据页。

Publish date: May 7, 2018

发布日期： 2018年5月7日

使用不同SQL Server索引类型 (Working with different SQL Server indexes types )

We will go through the different types of SQL Server indexes (Unique index, Filtered index, Spatial index, XML index), above and beyond Clustered and Non-clustered indexes classification, and when to use them.

我们将介绍各种类型SQL Server索引（唯一索引，过滤索引，空间索引，XML索引），以及超出群集索引和非群集索引分类的范围以及何时使用它们。

Publish date: May 8, 2018

发布日期： 2018年5月8日

使用SQL Server索引跟踪和调整查询 (Tracing and tuning queries using SQL Server indexes )

We are ready to design the most effective SQL Server index that the SQL Server Query Optimizer will always take benefits from, in speeding up the data retrieval process on our queries, which is the main goal of creating an index, with the minimum disk I/O operations and the least system resources usage.e.

我们已经准备好设计最有效SQL Server索引，SQL Server Query Optimizer将始终从中受益，以加快查询中的数据检索过程，这是创建索引的主要目标，同时使用最少的磁盘I / O操作和最少的系统资源使用。e。

Publish date: May 18, 2018

发布日期： 2018年5月18日

收集SQL Server索引统计信息和使用情况信息 (Gathering SQL Server indexes statistics and usage information )

After creating the indexes, we should proactively know which indexes are badly used, or totally unused in order to perform the correct decision to maintain these indexes or replace it with more efficient ones.

创建索引之后，我们应该主动知道哪些索引使用不当或完全未使用，以便执行正确的决定来维护这些索引或将其替换为更有效的索引。

Publish date: May 24, 2018

发布日期： 2018年5月24日

维护SQL Server索引 (Maintaining SQL Server indexes )

One of the most important administration tasks that every database administrator should care about is maintaining database indexes. The index that fits you now, may degrade the performance of your query in the future, due to the different changes performed on your database data or schema.

每个数据库管理员应该关注的最重要的管理任务之一是维护数据库索引。 由于对数据库数据或架构执行的更改不同，现在适合您的索引将来可能会降低查询性能。

Publish date: May 31, 2018

发布日期： 2018年5月31日

翻译自: https://www.sqlshack.com/sql-server-indexes-series-intro/

索引sql server