SQLインデックスデマ粉砕
7908 ワード
多くの文章ではIN,ORを使用するとインデックスが破壊され,全テーブルスキャンが発生すると言及されているが,実際のテストではそうではない.
または
以上のSQL文、第1グループ(=,IN)、第2グループ(=,OR,IN)は、各グループの2つのSQL文が同じ実行プランを使用しており、実行プランでも同じインデックスが使用されていることがわかり、ディスクアクティビティ情報の論理読み取り回数も同じである.
テストに必要な設定:
SET STATISTICS IO ONはSQLSERVERにTransact-SQL文で生成されたディスク活動量情報を表示させる.SET STATISTICS TIME ON表示分析、コンパイル、文実行に必要なミリ秒数
クエリーを実行するたびの処理:
CHECKPOINT 現在のデータベースのすべての汚れたページをディスクに強制的に書き込み、バッファをクリアします。 DBCC DROPCLEANBUFFERS すべてのCleanBuffersをバッファから削除
テスト:
SQLServer 2008 R2
SQLServer 2014
1.新規データベーステーブル:
2.Birthdayに大量のデータを挿入する
3.Birthday列の非集計インデックスの作成
4.クエリーテスト
表「TestTable」.スキャンカウント1、論理読み出し8回、物理読み出し2回、プリフェッチ5回、lob論理読み出し0回、lob物理読み出し0回、lobプリフェッチ0回.
CHECKPOINT DBCC DROPCLEANBUFFERSSELECT Birthday FROM [dbo].[TestTable] WHERE Birthday IN ('19901111')
表「TestTable」.スキャンカウント1、論理読み出し8回、物理読み出し2回、プリフェッチ5回、lob論理読み出し0回、lob物理読み出し0回、lobプリフェッチ0回.
次の文を実行すると、クエリー・プランと読み取り回数も同じです.
表「TestTable」.スキャンカウント2、論理読み出し16回、物理読み出し2回、プリフェッチ10回、lob論理読み出し0回、lob物理読み出し0回、lobプリフェッチ0回.
表「TestTable」.スキャンカウント2、論理読み出し16回、物理読み出し2回、プリフェッチ10回、lob論理読み出し0回、lob物理読み出し0回、lobプリフェッチ0回.
実行計画のスクリーンショットはここではアップロードされません.興味があれば、自分で検証することができます.
SELECT c FROM t WHERE c = 1
SELECT c FROM t WHERE c in (1)
または
SELECT c FROM t WHERE c = 1 OR c = 2
SELECT c FROM t WHERE c in (1,2)
以上のSQL文、第1グループ(=,IN)、第2グループ(=,OR,IN)は、各グループの2つのSQL文が同じ実行プランを使用しており、実行プランでも同じインデックスが使用されていることがわかり、ディスクアクティビティ情報の論理読み取り回数も同じである.
テストに必要な設定:
SET STATISTICS IO ONはSQLSERVERにTransact-SQL文で生成されたディスク活動量情報を表示させる.SET STATISTICS TIME ON表示分析、コンパイル、文実行に必要なミリ秒数
クエリーを実行するたびの処理:
CHECKPOINT 現在のデータベースのすべての汚れたページをディスクに強制的に書き込み、バッファをクリアします。 DBCC DROPCLEANBUFFERS すべてのCleanBuffersをバッファから削除
テスト:
SQLServer 2008 R2
SQLServer 2014
1.新規データベーステーブル:
CREATE TABLE [dbo].[TestTable](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Birthday] [varchar](10) NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
2.Birthdayに大量のデータを挿入する
3.Birthday列の非集計インデックスの作成
4.クエリーテスト
CHECKPOINT
DBCC DROPCLEANBUFFERS
SELECT birthday FROM [dbo].[TestTable] WHERE Birthday IN ('19901111')
表「TestTable」.スキャンカウント1、論理読み出し8回、物理読み出し2回、プリフェッチ5回、lob論理読み出し0回、lob物理読み出し0回、lobプリフェッチ0回.
CHECKPOINT DBCC DROPCLEANBUFFERSSELECT Birthday FROM [dbo].[TestTable] WHERE Birthday IN ('19901111')
表「TestTable」.スキャンカウント1、論理読み出し8回、物理読み出し2回、プリフェッチ5回、lob論理読み出し0回、lob物理読み出し0回、lobプリフェッチ0回.
次の文を実行すると、クエリー・プランと読み取り回数も同じです.
CHECKPOINT
DBCC DROPCLEANBUFFERS
SELECT Birthday FROM [dbo].[TestTable] WHERE Birthday = '19901111' OR Birthday = '19901212'
表「TestTable」.スキャンカウント2、論理読み出し16回、物理読み出し2回、プリフェッチ10回、lob論理読み出し0回、lob物理読み出し0回、lobプリフェッチ0回.
CHECKPOINT
DBCC DROPCLEANBUFFERS
SELECT birthday FROM [dbo].[TestTable] WHERE Birthday IN ('19901111','19901212')
表「TestTable」.スキャンカウント2、論理読み出し16回、物理読み出し2回、プリフェッチ10回、lob論理読み出し0回、lob物理読み出し0回、lobプリフェッチ0回.
実行計画のスクリーンショットはここではアップロードされません.興味があれば、自分で検証することができます.