SQL Server에서 비트 필드를 인덱싱해야합니까?
카디널리티가 낮은 필드 (고유 값 수가 적음)를 인덱싱하는 것은 실제로 할 가치가 없다는 것을 읽은 적이 있습니다. 나는 그것이 왜인지 이해하기 위해 인덱스가 어떻게 작동하는지에 대해 충분히 알지 못한다는 것을 인정합니다.
그러면 1 억 개의 행이있는 테이블이 있고 비트 필드가 1 인 레코드를 선택하면 어떻게됩니까? 그리고 어느 시점에서나 비트 필드가 1 (0이 아닌) 인 레코드가 소수라고 가정 해 보겠습니다. 해당 비트 필드를 인덱싱 할 가치가 있습니까? 왜?
물론 테스트하고 실행 계획을 확인하는 것만으로도 할 수 있지만, 그이면의 이론도 궁금합니다. 카디널리티는 언제 중요하고 언제 중요하지 않습니까?
SQL에서 인덱스가 무엇인지 고려하십시오. 인덱스는 실제로 다른 메모리 청크 (예 : 행에 대한 포인터)를 가리키는 메모리 청크입니다. 인덱스는 페이지로 나뉘어 인덱스의 일부가 사용량에 따라 메모리에서로드 및 언로드 될 수 있습니다.
행 세트를 요청할 때 SQL은 인덱스를 사용하여 테이블 스캔보다 빠르게 행을 찾습니다 (모든 행 확인).
SQL에는 클러스터형 및 비 클러스터형 인덱스가 있습니다. 클러스터형 인덱스에 대한 나의 이해는 비슷한 인덱스 값을 동일한 페이지로 그룹화한다는 것입니다. 이렇게하면 인덱스 값과 일치하는 모든 행을 요청할 때 SQL이 클러스터 된 메모리 페이지에서 해당 행을 반환 할 수 있습니다. 이것이 GUID 열의 클러스터 인덱스를 시도하는 것이 나쁜 생각 인 이유입니다. 임의의 값을 클러스터링하지 마십시오.
정수 열을 인덱싱 할 때 SQL의 인덱스에는 각 인덱스 값에 대한 행 집합이 포함됩니다. 범위가 1-10이면 인덱스 포인터가 10 개가됩니다. 얼마나 많은 행이 있는지에 따라 다르게 페이징 될 수 있습니다. 쿼리가 "1"과 일치하는 인덱스를 찾은 다음 Name에 "Fred"가 포함 된 경우 (Name 열이 인덱싱되지 않았다고 가정) SQL은 "1"과 일치하는 행 집합을 매우 빠르게 가져온 다음 테이블을 검색하여 나머지를 찾습니다.
따라서 SQL이 실제로하는 일은 반복해야하는 작업 집합 (행 수)을 줄이는 것입니다.
비트 필드 (또는 일부 좁은 범위)를 인덱싱 할 때 해당 값과 일치하는 행 수만큼만 작업 집합을 줄입니다. 일치하는 행 수가 적 으면 작업 세트가 많이 줄어 듭니다. 50/50 배포를 사용하는 많은 행의 경우 인덱스를 최신 상태로 유지하는 것보다 성능이 거의 향상되지 않을 수 있습니다.
모든 사람들이 테스트하라고 말하는 이유는 SQL이 테이블 스캔이 더 빠르다고 판단하면 인덱스를 무시하거나 정렬을 사용하거나 메모리 페이지를 구성 할 수있는 매우 영리하고 복잡한 옵티 마이저를 포함하고 있기 때문입니다.
나는 다른 방법 으로이 질문을 보았습니다. 소수의 레코드 만이 1의 값을 가정한다고 가정하고 (그리고 관심있는 레코드라고 가정하면) 필터링 된 인덱스가 좋은 선택이 될 수 있습니다. 다음과 같은 것 :
create index [IX_foobar] on dbo.Foobar (FooID) where yourBitColumn = 1
이렇게하면 옵티마이 저가 쿼리에서 조건 자일 때 사용할 수있을만큼 스마트 한 인덱스가 상당히 작아집니다.
비트 필드가 1로 설정된 몇 개만있는 레코드 1 억 개? 예, 비트 필드를 인덱싱하면 확실히 비트 = 1 레코드 쿼리 속도가 빨라질 것이라고 생각합니다. 인덱스에서 로그 검색 시간을 얻은 다음 비트 = 1 레코드가있는 몇 페이지 만 터치해야합니다. 그렇지 않으면 1 억 레코드 테이블의 모든 페이지를 터치해야합니다.
다시 말하지만, 저는 확실히 데이터베이스 전문가가 아니며 중요한 것을 놓칠 수 있습니다.
분포가 잘 알려져 있고 불균형 한 경우 (예 : 행의 99 %가 비트 = 1이고 1 %가 비트 = 0 인 경우), 비트 = 1로 WHERE 절을 수행하면 전체 테이블 스캔이 다음과 거의 같은 시간에 수행됩니다. 인덱스 스캔. 비트 = 0 인 빠른 쿼리를 원하는 경우 가장 좋은 방법은 필터링 된 인덱스를 만들고 WHERE 비트 = 0 절을 추가하는 것입니다. 그러면 해당 인덱스는 1 % 행만 저장합니다. 그런 다음 WHERE 비트 = 0을 수행하면 쿼리 옵티마이 저가 해당 인덱스를 선택하게하고 그로부터 모든 행은 비트 = 0이됩니다. 또한 비트의 전체 인덱스와 비교할 때 필요한 디스크 공간이 매우 적다는 이점이 있습니다. .
비트 열만 인덱싱 할 것이라고 생각하지는 않지만 복합 인덱스의 일부로 비트 열을 포함하는 것은 매우 일반적입니다.
간단한 예는 애플리케이션이 거의 항상 활성 고객을 찾을 때 성 대신 ACTIVE, LASTNAME에 대한 인덱스입니다.
아직 읽지 않은 경우 Jason Massie는이 주제에 대해 최근에 기사를 썼습니다.
http://statisticsio.com/Home/tabid/36/articleType/ArticleView/articleId/302/Never-Index-a-BIT.aspx
편집 : 새 기사 위치-http: //sqlserverpedia.com/blog/sql-server-bloggers/never-index-a-bit
이전 "신규"기사 위치에 대한 웨이 백 머신 : http://web.archive.org/web/20120201122503/http://sqlserverpedia.com/blog/sql-server-bloggers/never-index-a-bit/
새로운 SQL Server Pedia 위치는 Toadworld이며,이 주제에 대해 Kenneth Fisher의 새 기사가 있습니다.
물론 가치가 있습니다. 특히 해당 값으로 데이터를 검색해야하는 경우에는 더욱 그렇습니다. 일반 행렬을 사용하는 대신 희소 행렬을 사용하는 것과 비슷합니다.
이제 SQL 2008에서는 분할 함수를 사용할 수 있으며 인덱스에 포함되는 데이터를 필터링 할 수 있습니다. 이전 버전의 단점은 모든 데이터에 대해 인덱스가 생성된다는 점이지만 흥미로운 값을 별도의 파일 그룹에 저장하여 최적화 할 수 있습니다.
다른 사람들이 말했듯이 이것을 측정하고 싶을 것입니다. 이 글을 어디에서 읽었는지 기억 나지 않지만 인덱스가 효과적이려면 열의 카디널리티가 매우 높아야합니다 (약 95 %). 이에 대한 가장 좋은 테스트는 인덱스를 작성하고 BIT 필드의 0 및 1 값에 대한 실행 계획을 조사하는 것입니다. 실행 계획에 인덱스 검색 작업이 표시되면 인덱스가 사용된다는 것을 알 수 있습니다.
가장 좋은 방법은 기본 SELECT * FROM 테이블 WHERE BitField = 1을 사용하여 테스트하는 것입니다. 응용 프로그램에 대한 현실적인 쿼리를 얻을 때까지 단계별로 기능을 천천히 구축하고 모든 단계에서 실행 계획을 검사하여 인덱스 검색이 여전히 사용되고 있는지 확인합니다. 물론이 실행 계획이 프로덕션에 사용될 것이라는 보장은 없지만 그럴 가능성이 높습니다.
일부 정보는 sql-server-performance.com 포럼 및 참조 문서 에서 찾을 수 있습니다.
"저는 카디널리티가 낮은 필드 (고유 값 수가 적음)를 인덱싱하는 것이 실제로 가치가 없다는 것을 읽은 적이 있습니다."
That because SQL Server will almost always find its more efficient to just do a table-scan than to read the index. So basically your index will never get used and it's a waste to maintain it. As others have said it might be ok in a compound index.
If your goal is to make querying for records where the bit field value equals '1' faster you might try an indexed view of your base table which only contains records where your bit field equals '1'. In enterprise edition if a query could make use of an indexed view instead of a specified table to improve query performance it will use the view. In theory this would increase the speed of select queries which only look for records with a bit field value of '1'.
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx
All this assumes you are Microsoft SQL Server 2005 Enterprise. The same might apply to 2008, I'm not familiar with that version.
If you want to know if an index has the effects you desire: test and test again.
In general you don't want an index that doesn't narrow down your table enough, because of the cost to maintain an index. (cost > profit). But if the index in your case will cut the table in half, you may gain something but putting it on the table. It all depends on the exact size/structure of your table and how you are using it (number of reads/writes).
On its own, no as it results in very little selectivity. As part of a compound index. quite possibly but only after other equality columns.
You can't index a bit field in SQL Server 2000, as was indicated in the Books Online at the time:
bit
Integer data type 1, 0, or NULL.
Remarks
Columns of type bit cannot have indexes on them.
Yes, if you have only a handful of rows, out of millions, an index will help. But if you want to do it in this case you need to make the column a tinyint.
Note: Enterprise Manager will not let you create an index on a bit column. If you wish you can still manually create an index on a bit column:
CREATE INDEX IX_Users_IsActiveUsername ON Users
(
IsActive,
Username
)
But SQL Server 2000 will not actually use such an index - running a query where the index would be a perfect candidate, e.g.:
SELECT TOP 1 Username
FROM Users
WHERE IsActive = 0
SQL Server 2000 will do a table scan instead, acting as though the index doesn't even exist. If you change the column to a tinyint SQL Server 2000 will do an index seek. Also, the following non-covered query:
SELECT TOP 1 *
FROM Users
WHERE IsActive = 0
It will perform an index seek, followed by a bookmark lookup.
SQL Server 2005 does have limited support for indexes on bit columns. For example:
SELECT TOP 1 Username
FROM Users
WHERE IsActive = 0
will cause an index seek through the covering index. But the non-covered case:
SELECT TOP 1 *
FROM Users
WHERE IsActive = 0
will not cause an index seek followed by a bookmark lookup, it will perform a table scan (or clustered index scan), rather than performing the index seek followed by a bookmark lookup.
Verified by experimentation and direct observation.
very late answer...
Yes, it can be useful according to SQL CAT team (updated, has been consolidated)
Is this a common query? It may be worth it when looking for the "handful" of records but won't help you much on the other rows. Are there other ways to identify the data?
Cardinality is one factor, the other is how well does the index divide your data. If you have about half 1s and half 0s, then it will help. (Assuming that that index is a better path to choose than some other index). However, how often are you inserting and updating? Adding indexes for SELECT performance also hurt the INSERT, UPDATE and DELETE performance, so keep that in mind.
I would say, if the 1s to 0s (or vice versa) isn't better than 75% to 25%, don't bother.
measure response time before and after and see if it is worthwhile; theoretically it should improve performance for queries using the indexed fields but it really depends on the distribution of true/false values and the other fields involved in the queries that you're concerned about
Ian Boyd is correct when he says that you could not do it via Enterprise Manager for SQL 2000 (see his note regarding creating it throught T-SQL.
You need to be smart here to query, you must know the load value on your column if the load of true is more in your system and you want to check all the true values writ your query to check not false.. it will help alot, it just trick.
참고URL : https://stackoverflow.com/questions/231125/should-i-index-a-bit-field-in-sql-server
'Programing' 카테고리의 다른 글
| '콘텐츠'와 '텍스트'의 차이점은 무엇입니까? (0) | 2020.08.29 |
|---|---|
| 키 저장소, 인증서 및 별명 이해 (0) | 2020.08.29 |
| C ++에서 임시 수명 보장? (0) | 2020.08.29 |
| “X11 DISPLAY 변수 없음”-무슨 의미입니까? (0) | 2020.08.29 |
| C #에서 목록이 비어 있는지 확인 (0) | 2020.08.28 |