索引sql server
In this article, we will discuss the most important points that we should consider when designing an optimal SQL index. Before going through the index design procedure, let us revise the SQL Server index concept.
在本文中，我們將討論在設計最佳SQL索引時應考慮的最重要點。 在進行索引設計過程之前，讓我們修改SQL Server索引概念。
SQL index is considered as one of the most important factors in the SQL Server performance tuning field. It helps in speeding up the queries by providing swift access to the requested data, called index seek operation, instead of scanning the whole table to retrieve a few records. It works similar to the book’s index that helps in identifying the location of each unique word, by providing the page where you can find that word, rather than spending the whole weekdays reading the book to check a specific subject or identifying that word. In other words, the existence of that index will save time and resources.
SQL索引被認為是SQL Server性能調整領域中最重要的因素之一。 通過提供對請求數據的快速訪問（稱為索引查找操作），而不是掃描整個表以檢索一些記錄，可以幫助加快查詢速度。 它的工作原理與書籍的索引相似，它通過提供可在其中找到該單詞的頁面來幫助識別每個唯一單詞的位置，而不是花整個工作日閱讀本書來檢查特定主題或識別該單詞。 換句話說，該索引的存在將節省時間和資源。
For more information about the SQL index structure, different operations that can be performed on an index and how to take advantage of the index in tuning the T-SQL queries performance, check the following articles:
有關SQL索引結構，可以對索引執行的不同操作以及如何在優化T-SQL查詢性能時利用索引的更多信息，請查看以下文章：
SQL Server provides us with two main types of indexes. The clustered index that is used to store the whole table data based on a select index key consists of one or multiple columns, with the ability to create only one clustered index per each table. The clustered index existence covert the table from an unsorted heap table to a sorted clustered table.
SQL Server為我們提供了兩種主要的索引類型。 用于基于選擇索引鍵存儲整個表數據的聚簇索引由一列或多列組成，并且每個表只能創建一個聚簇索引。 聚集索引的存在將表從未排序的堆表轉換為已排序的聚集表。
The second main type of SQL Server indexes is the non-clustered index in which the leaf nodes of that index stores only the index key values with a pointer to the storage location of that rows in the main heap table or the clustered index, with the ability to create up to 99 non-clustered indexes per each table.
SQL Server索引的第二種主要類型是非聚集索引，其中該索引的葉節點僅存儲索引鍵值，并帶有指向主堆表或聚集索引中該行的存儲位置的指針。每個表最多可以創建99個非聚集索引。
SQL Server provides us also with other special purposes SQL indexes, derived from the clustered and non-clustered types, that can help in improving the performance of the T-SQL queries. These indexes include the Unique index, Filtered index, Spatial Index, XML index, Clomunstore index, Full-Text index, and Hash index.
SQL Server還為我們提供了其他特殊用途SQL索引，這些索引是從群集和非群集類型派生的，它們可以幫助提高T-SQL查詢的性能。 這些索引包括唯一索引，過濾索引，空間索引，XML索引，Clomunstore索引，全文本索引和哈希索引。
After creating the index, we need also to monitor that index usage to make sure that it still efficient and useful for us. This can be performed by gathering statistical information about the indexes and its usage then perform the proper maintenance operation on these indexes to keep it in a healthy state.
創建索引后，我們還需要監視該索引的使用情況，以確保它仍然對我們有效。 這可以通過收集有關索引及其使用情況的統計信息 ，然后對這些索引執行適當的維護操作以使其保持健康狀態來執行。
The target of the SQL Server index design task is to have an index that SQL Server Query Optimizer will choose to enhance the performance of the submitted queries. I used to describe the index, in all my articles and sessions, as a double-edged sword. In this way, I will make sure that we are not blaming the index for our mistakes. The index will be our superhero and improve the performance of our queries if we design it in a correct way. But if that index is poorly designed, it will cause performance degradation in our queries and slow down the data retrieval process. In other words, the absence of poorly designed indexes is better than its existence.
SQL Server索引設計任務的目標是擁有一個索引，SQL Server Query Optimizer將選擇該索引來增強所提交查詢的性能。 我以前在所有文章和會話中都用一把雙刃劍來描述索引。 這樣，我將確保我們不會將錯誤歸咎于索引。 如果我們以正確的方式設計索引，它將成為我們的超級英雄，并改善查詢的性能。 但是，如果該索引設計不當，將導致查詢性能下降，并減慢數據檢索過程。 換句話說，缺少設計不當的索引比存在更好。
The process of selecting the right SQL Server index that fits your database and workload requirements is not an easy mission, but also not impossible mission. In this process, you need to balance between the index gain in the shape of speeding up the data retrieval operation and the index overhead on the data insertion and modification operations.
選擇適合您的數據庫和工作負載要求的正確SQL Server索引的過程并非易事，而且也不是不可能的任務。 在此過程中，您需要在加快數據檢索操作速度的索引增益與數據插入和修改操作的索引開銷之間取得平衡。
To help you in designing a proper index that SQL Server Query Optimizer will take advantage of to enhance your queries performance, we will discuss here the top five points that you need to consider when planning to create an index.
為了幫助您設計適當的索引，SQL Server Query Optimizer將利用該索引來提高查詢性能，我們將在此處討論計劃創建索引時需要考慮的前五點。
In order to design a proper index, you need to study the characteristics of the database on which the SQL Server index will be created. If the database is created to handle the Online Transaction Processing (OLTP) workload, with a large number of inserting and data modification queries, it is recommended not to overload the database with a large number of indexes. This is due to the fact that inserting, updating or deleting any row on the underlying table will also require reflecting the same changes to all related indexes in that table. So, you should create the minimum possible number of indexes in the OLTP tables with the least possible number of columns participating in the index’s key. In this way, we can take advantage of the created SQL indexes in speeding up the data retrieval process with minimal overhead on the data modification operations.
為了設計適當的索引，您需要研究將在其上創建SQL Server索引的數據庫的特征。 如果創建數據庫以處理帶有大量插入和數據修改查詢的聯機事務處理（OLTP）工作負載，則建議不要使包含大量索引的數據庫過載。 這是由于以下事實：插入，更新或刪除基礎表上的任何行也將需要反映對該表中所有相關索引的相同更改。 因此，您應該在OLTP表中創建盡可能少的索引，并且盡可能少的列參與索引的鍵。 這樣，我們可以利用創建SQL索引來以最小的數據修改操作開銷來加快數據檢索過程。
If the database is created to handle Online Analytical Processing (OLAP) workload, which is used in Data Warehouse as a part of the Business Intelligence structure, most of the workload will be in the shape of SELECT queries to retrieve a large amount of analytical data for analysis or reporting purposes, and a small number of data modification queries. In this case, you can create a large number of SQL Server indexes, adding all required columns as index key or non-key columns to enhance the performance of the SELECT queries and get the requested data faster.
如果創建數據庫來處理在線分析處理（OLAP）工作負載（在數據倉庫中用作商務智能結構的一部分），則大多數工作負載將采用SELECT查詢的形式來檢索大量分析數據用于分析或報告目的，以及少量的數據修改查詢。 在這種情況下，您可以創建大量SQL Server索引，將所有必需的列添加為索引鍵或非鍵列，以增強SELECT查詢的性能并更快地獲取請求的數據。
Another thing to consider when indexing a database table is the size of the table. If the table is small with less than 1000 pages, no performance enhancement can be gained from indexing that table, as SQL Server Query Optimizer will prefer scanning the whole table rather than examining the SQL index and try to create the best possible plan. In other words, this index on the small table will not be used and will have overhead on the table as it should be maintained when the table is changed.
索引數據庫表時要考慮的另一件事是表的大小。 如果表很小且少于1000頁，則對該表建立索引將無法提高性能，因為SQL Server Query Optimizer寧愿掃描整個表而不是檢查SQL索引并嘗試創建最佳方案。 換句話說，小表上的該索引將不被使用，并且將在表上產生開銷，因為在更改表時應保留該索引。
You need also to have a look at the database views and check the ones that contain multiple joins and aggregations and create indexes on these views to enhance the performance of reading from it.
您還需要查看數據庫視圖，并檢查包含多個聯接和聚合的數據庫視圖，并在這些視圖上創建索引以增強從數據庫中讀取的性能。
Studying the queries that are hitting the database tables very frequently, by checking with the system developer or using the profiling tools such as SQL Profiler or Extended Events, will help in designing the SQL Server index that helps more in enhancing the overall system performance.
通過與系統開發人員進行檢查或使用性能分析工具（例如SQL Profiler或Extended Events）來研究非常頻繁地訪問數據庫表的查詢，將有助于設計SQL Server索引，從而更有助于提高整體系統性能。
After getting statistics about the frequently executed queries, we should check the columns that are used in the predicates and join conditions in these queries and create the proper index, by adding all necessary columns to the index to cover the frequently executed query and avoid any unnecessary column, to speed up the data retrieval operation.
在獲得有關頻繁執行查詢的統計信息之后，我們應該檢查謂詞中使用的列以及這些查詢中的聯接條件，并通過向索引添加所有必要的列以覆蓋頻繁執行的查詢并避免任何不必要的操作來創建正確的索引列，以加快數據檢索操作。
As a SQL Server developer, it is recommended to write data insertion and modification queries to insert, update or delete as many rows as possible in the same query, rather than writing multiple queries. This will help in reducing the overhead of the index on the data modification statement, where all these changes performed on the table will be replicated to the SQL index as one-shot when executed as a single query.
作為SQL Server開發人員，建議編寫數據插入和修改查詢以在同一查詢中插入，更新或刪除盡可能多的行，而不是編寫多個查詢。 這將有助于減少數據修改語句上索引的開銷，在表中執行的所有這些更改將在作為單個查詢執行時作為一個快照復制到SQL索引。
After studying the characteristic of the frequently executed query that we need to enhance and having a list of columns to participate in the index key, we need to consider some points when choosing which column we should add to the index.
在研究了我們需要增強的頻繁執行查詢的特性并列出了參與索引鍵的列之后，在選擇應添加到索引的列時，我們需要考慮一些要點。
The first point is the column characteristic. Not all data types are recommended to be used as an index key. For example, the best candidate data type for the SQL Server index is the integer column due to its small size. On the other hand, columns with text, ntext, image, varchar(max), nvarchar(max), and varbinary(max) data types cannot participate in the index key. However, most of it still can be added to the non-clustered non-key columns. A column with XML data type can be added only to an XML index. In addition, a column with UNIQUE and NOT NULL values will be a good candidate, due to its high selectivity level, as an index key column that makes the index more useful.
第一點是色譜柱特性。 建議不要將所有數據類型都用作索引鍵。 例如，SQL Server索引的最佳候選數據類型是整數列，因為它的大小很小。 另一方面，具有text，ntext，image，varchar（max），nvarchar（max）和varbinary（max）數據類型的列不能參與索引鍵。 但是，大多數內容仍可以添加到非群集非鍵列中。 具有XML數據類型的列只能添加到XML索引中。 另外，具有高UNIQUE和NOT NULL值的列由于其較高的選擇性級別，因此可以作為索引鍵列，使索引更有用，這是一個很好的選擇。
The second point is the location of the column in the query. For instance, the columns used in the WHERE clause, the JOIN prediction, LIKE and the ORDER BY clause are the best candidate columns to be indexed. In addition, indexing the computed columns and the foreign key columns will enhance the performance of the queries that read from these columns.
第二點是查詢中列的位置。 例如，在WHERE子句，JOIN預測，LIKE和ORDER BY子句中使用的列是要建立索引的最佳候選列。 此外，索引計算列和外鍵列將增強從這些列讀取的查詢的性能。
An important point to consider after selecting the proper columns to be involved in the index key is the order of the columns in the index key, especially when the key consists of multiple columns. Try to place the columns that are used in the query conditions first in the SQL Server index key. Also, try to define the columns sorting criteria, ascending or descending, in a way that matches the order used in the ORDER BY clause in your query. In this way, you will overcome the SORT operator high overhead, enhancing the performance of the query.
選擇適當的列以包含在索引鍵中后，要考慮的重要一點是索引鍵中列的順序，尤其是當鍵包含多個列時。 嘗試首先將查詢條件中使用的列放在SQL Server索引鍵中。 另外，嘗試以與查詢中ORDER BY子句中使用的順序相匹配的方式定義列的升序或降序排序標準。 這樣，您將克服SORT運算符的高開銷，從而提高查詢的性能。
To that step, we have made a decision that we need to create an index in a specific database table to cover a specific query that is called very frequently, and we need to add candidate column(s) to the index key. We need now to decide which SQL index type fits the query requirements. In other words, we need to specify if we should create a clustered or non-clustered index, a unique or non-unique index, columnstore, or rowstore index. All these decisions will be made based on the query coverage and enhancements requirement.
為此，我們決定需要在特定的數據庫表中創建索引以覆蓋非常頻繁地調用的特定查詢，并且需要在索引鍵中添加候選列。 現在，我們需要確定哪種SQL索引類型符合查詢要求。 換句話說，我們需要指定是創建聚簇索引還是非聚簇索引，唯一索引還是非唯一索引，列存儲或行存儲索引。 所有這些決定都將基于查詢覆蓋率和增強要求而做出。
As mentioned previously, SQL Server provides us with different types of special purposes indexes that we can use to enhance the performance of the queries. For example, try to use a Filtered index on the columns that have well-defined data subsets, such as Sparse columns with mostly NULL values.
如前所述，SQL Server為我們提供了不同類型的特殊目的索引，我們可以使用這些索引來增強查詢的性能。 例如，嘗試在具有明確定義的數據子集的列上使用過濾索引，例如，稀疏列的值大多為NULL。
It is recommended to start indexing the table by creating a clustered index, that covers the column(s) called very frequently, which will convert it from the heap table to a sorted clustered table, then create the required non-clustered indexes that cover the remaining queries in the system. In this way, the non-clustered indexes will be built over the SQL Server clustered index and the pointers on the leaf level nodes of the non-clustered indexes will also point to the location of the row in the sorted clustered index.
建議通過創建聚集索引來開始建立表的索引，該聚集索引將覆蓋經常調用的列，該列會將其從堆表轉換為排序的聚集表，然后創建覆蓋該表的所需非聚集索引。系統中剩余的查詢。 這樣，將在SQL Server聚集索引之上構建非聚集索引，并且非聚集索引的葉級節點上的指針也將指向已排序聚集索引中的行的位置。
If the clustered index is created on a table with clustered indexes already exist, all the non-clustered indexes will be dropped and created again to change the pointers in the leaf level nodes that were pointing to the heap table to point to the newly-created clustered index. So that, creating it in the correct order will overcome the overhead of recreating the non-clustered index again.
如果在已經存在聚集索引的表上創建聚集索引，則將刪除并重新創建所有非聚集索引，以更改指向堆表的葉級節點中的指針以指向新創建的索引聚集索引。 這樣，以正確的順序創建它將克服重新創建非聚集索引的開銷。
When a SQL Server index is created, it will be stored in the same filegroup where the main table is created. The partitioned clustered index and the non-clustered index can be stored on the same filegroup as the main table or on a different filegroup.
創建SQL Server索引后，它將存儲在創建主表的同一文件組中。 分區的聚集索引和非聚集索引可以存儲在與主表相同的文件組中，也可以存儲在不同的文件組中。
Selecting the proper storage criteria for the index during the design phase will help in improving the query performance by increasing the I/O performance. For instance, creating the non-clustered index on a filegroup located in a different disk drive than the disk drive where the main table is created will improve the performance of the queries that use that non-clustered index, as it will not be affected by the concurrent reading of the data and SQL index pages, that are spread across different disks, which will be performed on different disk drives.
在設計階段為索引選擇適當的存儲條件將通過提高I / O性能來幫助提高查詢性能。 例如，在與創建主表所在的磁盤驅動器不同的磁盤驅動器上的文件組上創建非聚集索引將提高使用該非聚集索引的查詢的性能，因為它不會受到以下影響同時讀取分布在不同磁盤上的數據和SQL索引頁，這將在不同的磁盤驅動器上執行。
In addition, the clustered and non-clustered indexes, that are created over large tables, can be partitioned across multiple filegroups, with each filegroup stored on a separate disk drive, improving the concurrent data access and retrieval operations, due to the fact that the data is distributed over different disk drives within the SQL index and the Query Optimizer will process only the partitions that the query will access, excluding all other partitions.
此外，在大型表上創建的聚集索引和非聚集索引可以跨多個文件組進行分區，每個文件組存儲在單獨的磁盤驅動器上，從而改善了并發數據訪問和檢索操作。數據分布在SQL索引內的不同磁盤驅動器上，并且查詢優化器將僅處理查詢將訪問的分區，但不包括所有其他分區。
Another important storage concept that should be considered also is the FILLFACTOR option, which is an option that can be defined when creating or rebuilding an index, with its value between 0 and 100, that specifies the percentage of space that will be filled on each leaf-level data page in the created index. For example, setting the FILLFACTOR value to 80% will leave 20% of each page empty during the SQL Server index creation or rebuilding process, and this 20% percent will help when a new data is inserted, or an existing data is modified but not fit in the current space, where the data will be inserted in that free space instead of splitting the current page into multiple pages causing index fragmentation issue that will degrade the index performance with time. Fill factor will help in enhancing the performance of the T-SQL queries and minimize the amount of index storage and index maintenance overhead.
還應該考慮的另一個重要存儲概念是FILLFACTOR選項，該選項可以在創建或重建索引時定義，該索引的值在0到100之間，該索引指定將在每個葉子上填充的空間百分比創建的索引中的高級別數據頁。 例如，將FILLFACTOR值設置為80％將在SQL Server索引創建或重建過程中將每個頁面的20％留空，而這20％將在插入新數據或修改現有數據但不修改時提供幫助適合當前空間，在該空間中會將數據插入該可用空間，而不是將當前頁面分為多個頁面，這會導致索引碎片問題，從而導致索引性能隨時間下降。 填充因子將有助于提高T-SQL查詢的性能，并最大程度地減少索引存儲量和索引維護開銷。
It is also recommended to create narrow indexes with the least possible number of useful columns, rather than creating wide indexes with many unnecessary columns, as it requires less disk space and will have less SQL Server index maintenance overhead.
還建議創建有用索引數盡可能少的窄索引，而不是創建包含許多不必要列的寬索引，因為它需要更少的磁盤空間，并且將減少SQL Server索引維護的開銷。
All the previously mentioned points will help in designing the most optimal index that enhance the performance of the T-SQL queries, but it is very important to test the index first on a development environment before creating it on the production environment and make sure that it is useful for your workload and keep monitoring it and marinating it once created on the production environment.
前面提到的所有要點都將有助于設計可增強T-SQL查詢性能的最佳索引，但是在生產環境上創建索引之前先在開發環境上對其進行測試并確保該索引非常重要，這一點非常重要。對您的工作負載很有用，并在生產環境中創建后繼續對其進行監視和腌制。
翻譯自: https://www.sqlshack.com/top-five-considerations-for-sql-server-index-design/
索引sql server
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间：8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态
![[转]iOS开发使用半透明模糊效果方法整理](http://cc.cocimg.com/api/uploads/20141223/1419302410116248.png)
