《數(shù)據(jù)庫原理與應(yīng)用》09.索引的創(chuàng)建和使用

《《數(shù)據(jù)庫原理與應(yīng)用》09.索引的創(chuàng)建和使用》由會員分享，可在線閱讀，更多相關(guān)《《數(shù)據(jù)庫原理與應(yīng)用》09.索引的創(chuàng)建和使用（25頁珍藏版）》請在裝配圖網(wǎng)上搜索。

1、 數(shù) 據(jù) 庫 原 理 與 應(yīng) 用 第 九 講 索 引 的 創(chuàng) 建 和 使 用 本章的學(xué)習(xí)要點:一 .索 引 的 概 念 和 優(yōu) 點二 .索 引 的 分 類三 .創(chuàng) 建 索 引 的 方 法四 .對 索 引 進 行 管 理 索 引 概 述一.索引的概念: 索引是由表中的一列或若干列字段值以及相應(yīng)的指向表中物理標(biāo)識這些值的數(shù)據(jù)頁的邏輯指針構(gòu)成的. 索 引 概 述一.索引的優(yōu)點: 使 用 索 引 對 數(shù) 據(jù) 進 行 各 種 操 作 可 以 極大 的 提 高 系 統(tǒng) 性 能 ,尤 其 是 在 數(shù) 據(jù) 查 詢 方面 .1.利 用 索 引 的 數(shù) 據(jù) 查 詢 索 引 概 述2.不 利 用 索 引 的 數(shù) 據(jù)

2、 查 詢 （ 直 接 查 找 ） 系 統(tǒng) 直 接 在 數(shù) 據(jù) 表 中 查 詢 ， 從 表 的第 一 條 記 錄 開 始 ， 依 次 掃 描 比 較 數(shù) 據(jù) 表中 的 所 有 記 錄 ， 直 到 找 到 所 需 的 記 錄 或掃 描 完 數(shù) 據(jù) 表 的 所 有 記 錄 。 索 引 概 述二 .索 引 的 缺 點 :1.創(chuàng) 建 的 索 引 文 件 將 要 占 用 一 定 容 量 的 存 儲 空 間 .2.當(dāng) 每 次 對 數(shù) 據(jù) 表 進 行 更 新 時 ,數(shù) 據(jù) 庫 還 要 執(zhí) 行 額 外 的 操 作 來 維 護 索 引 .降 低 對 數(shù) 據(jù) 表 的 更 新 速 度 . 總 結(jié) :利 用 索 引 可

3、 以 提 高 對 表 的 查 詢 速 度 ,但是 會 降 低 對 表 的 更 新 速 度 ,所 以 過 多 的 索 引 不一 定 能 提 高 系 統(tǒng) 的 性 能 .我 們 必 須 科 學(xué) 的 設(shè) 計 索 引 ,才 能 帶 來 數(shù) 據(jù) 庫 性 能 的 提 高 . 索 引 概 述三 .建 立 索 引 需 參 照 的 原 則(一 )建 議 創(chuàng) 建 索 引 的 列1.在 經(jīng) 常 用 來 檢 索 的 列 上 創(chuàng) 建 索 引 (如 經(jīng) 常 在WHERE、 GROUP BY、 ORDER BY子 句 中 出 現(xiàn) 的 列 ).2.在 表 的 主 鍵 、 外 鍵 上 創(chuàng) 建 索 引 .3.在 聚 集 索 引 中

4、 ， 避 免 包 括 不 必 要 的 列 ， 盡 可 能使 用 較 小 的 數(shù) 據(jù) 類 型 ， 例 如 用 VARCHAR代 替 CHAR。 索 引 概 述三.建立索引需參照的原則(一 )建 議 創(chuàng) 建 索 引 的 列4.在 經(jīng) 常 用 于 表 間 連 接 的 字 段 上 建 立 索 引 .5.在 條 件 表 達 式 中 經(jīng) 常 用 到 的 不 同 值 較 多 的 字 段上 建 立 索 引 ， 在 不 同 值 較 少 的 字 段 上 不 要 建 立 索引 .6.如 果 經(jīng) 常 同 時 使 用 多 個 字 段 排 序 ， 可 以 在 這 些字 段 上 建 立 復(fù) 合 索 引 。 7.具 有 高

5、選 擇 性 的 列 是 索 引 的 好 候 選 列 ， 具 有 高密 度 的 列 是 索 引 最 糟 糕 的 候 選 列 。 索 引 概 述(二 )一 般 不 考 慮 用 于 創(chuàng) 建 索 引 的 列 .1.在 查 詢 中 幾 乎 不 涉 及 的 列2.包 含 太 多 重 復(fù) 值 的 列 (如 性 別 )3.當(dāng) 數(shù) 據(jù) 表 的 讀 寫 性 能 比 查 詢 更 重 要 時 ,應(yīng) 少建 或 不 建 索 引 .4.數(shù) 據(jù) 類 型 為 TEXT、 NTEXT和 IMAGE的 列 （ 不能 用 于 創(chuàng) 建 索 引 ） 。 索 引 分 類一 .從 數(shù) 據(jù) 表 存 儲 結(jié) 構(gòu) 的 角 度 分 類 :1.聚 集

6、 索 引2.非 聚 集 索 引二 .從 索 引 字 段 是 否 允 許 存 在 重 復(fù) 值 來 分 類 :1.唯 一 索 引2.普 通 索 引三 .從 創(chuàng) 建 索 引 依 據(jù) 的 字 段 個 數(shù) 來 分 類 :1.單 字 段 索 引2.復(fù) 合 索 引 (多 字 段 索 引 ， 最 多 可 包 含 16個 字 段 ， 但 列 的 數(shù) 據(jù) 類 型 的 長 度 之 和 不 能 超 過 900個 字 節(jié) ) 聚 集 索 引一 .概 念 : 聚 集 索 引 將 表 中 的 記 錄 在 物 理 數(shù) 據(jù) 頁 中 的 位 置按 索 引 字 段 值 重 新 排 列 .二 .創(chuàng) 建 聚 集 索 引 的 注 意 事

7、 項 :1.每 個 表 只 能 有 一 個 聚 集 索 引2.因 為 聚 集 索 引 改 變 了 表 中 行 的 物 理 順 序 ,所 以 在創(chuàng) 建 任 何 非 聚 集 索 引 之 前 要 首 先 創(chuàng) 建 聚 集 索 引 .3.在 聚 集 索 引 的 創(chuàng) 建 過 程 中 ,SQL SERVER會 臨 時 使 用 當(dāng) 前 數(shù) 據(jù) 庫 的 磁 盤 空 間 (大 約 為 原 表 的 1.2倍 ),所以 要 保 證 有 足 夠 的 空 間 創(chuàng) 建 聚 集 索 引 . 非 聚 集 索 引一 .概 念 : 非 聚 集 索 引 與 表 中 數(shù) 據(jù) 行 的 實 際 存 儲 結(jié) 構(gòu) 無 關(guān) ,非 聚 集 索 引

8、 存 儲 的 是 索 引 關(guān) 鍵 字 的 值 與 記 錄 的 存 儲位 置 的 對 應(yīng) 關(guān) 系 .二 .創(chuàng) 建 非 聚 集 索 引 的 注 意 事 項 :1.使 用 非 聚 集 索 引 的 查 詢 速 度 要 比 使 用 聚 集 索 引 的查 詢 速 度 慢 .2.一 個 表 可 以 創(chuàng) 建 多 個 非 聚 集 索 引 (最 多 可 達 249個 ). 3.當(dāng) 刪 除 、 新 建 或 重 建 聚 集 索 引 時 ， 系 統(tǒng) 會 自 動 更新 所 有 的 非 聚 集 索 引 。 創(chuàng) 建 索 引一 .由 系 統(tǒng) 自 動 創(chuàng) 建例 如 :當(dāng) 創(chuàng) 建 主 鍵 約 束 或 唯 一 性 約 束 時 系 統(tǒng)

9、 會自 動 的 創(chuàng) 建 唯 一 性 索 引 .二 .用 企 業(yè) 管 理 器 創(chuàng) 建 索 引打 開 企 業(yè) 管 理 器 右 擊 需 創(chuàng) 建 索 引 的 表 所 有 任 務(wù) 管 理 索 引 創(chuàng) 建 索 引二 .用 企 業(yè) 管 理 器 創(chuàng) 建 索 引注 意 事 項 :在 索 引 頁 已 填 滿 時 ,SQL SERVER就 必 須花 時 間 拆 分 該 索 引 頁 ,以 便 為 新 行 騰 出 空 間 .選 擇合 適 的 填 充 引 子 值 可 以 在 葉 級 索 引 頁 上 分 配 出 一定 百 分 比 的 可 用 空 間 ,以 減 少 頁 拆 分 .提 高 數(shù) 據(jù) 庫的 性 能 .1.填 充 索

10、 引 :用 于 指 定 索 引 中 間 級 即 非 葉 節(jié) 點中 每 個 頁 (節(jié) 點 )上 保 持 開 放 的 空 間 .其 值 使 用填 充 因 子 所 指 定 的 大 小 . 2.填 充 因 子 :在 創(chuàng) 建 索 引 時 用 于 指 定 每 個 索 引頁 的 數(shù) 據(jù) 占 索 引 頁 大 小 的 百 分 比 . 創(chuàng) 建 索 引二.用企業(yè)管理器創(chuàng)建索引填 充 因 子 大 小 的 設(shè) 置 原 則 :1.對 于 那 些 頻 繁 進 行 大 量 數(shù) 據(jù) 插 入 或 刪 除 的表 ,填 充 因 子 可 大 約 設(shè) 為 10.2.對 數(shù) 據(jù) 不 更 改 、 只 讀 的 表 ,填 充 因 子 可 以 設(shè)

11、到 95以 上 . 創(chuàng) 建 索 引三 .利 用 TRANSACT-SQL語 句 創(chuàng) 建 索 引語 法 格 式 :CREATE UNIQUE CLUSTERED|NONCLUSTEREDINDEX INDEX_NAME ON TABLE|VIEW(COLUMN NAME ASC|DESC) ON FILEGROUPNAME 創(chuàng) 建 索 引三 .利 用 TRANSACT-SQL語 句 創(chuàng) 建 索 引例 1： 為 AAA數(shù) 據(jù) 庫 中 的 學(xué) 生 表 創(chuàng) 建 一 個 唯 一聚 集 索 引 CC， 依 據(jù) 學(xué) 號 字 段 進 行 降 序 排 列 。USE AAACREATE UNIQUE CLUSTE

12、RED INDEX CCON 學(xué) 生 表 （ 學(xué) 號 DESC） 創(chuàng) 建 索 引三 .利 用 TRANSACT-SQL語 句 創(chuàng) 建 索 引例 2：CREATE INDEX DDD ON 成 績 表 （ 語 文 ，數(shù) 學(xué) DESC， 英 語 ） 創(chuàng) 建 索 引三 .利 用 TRANSACT-SQL語 句 創(chuàng) 建 索 引例 3:先 接 入 學(xué) 成 績 降 序 再 按 出 生 日 期 升 序 對為 學(xué) 生 表 創(chuàng) 建 聚 集 索 引 ,索 引 名 為 Q. 刪 除 索 引語 法 ： DROP INDEX 表 名 .索 引 名 |視 圖 名 .索 引 名 , .n例 : CREATE UNIQUE

13、INDEX A ON 學(xué) 生 表 (姓 名 ) DROP INDEX 學(xué) 生 表 .A 刪 除 索 引關(guān) 于 刪 除 索 引 的 注 意 事 項 :1.執(zhí) 行 DROP INDEX 語 句 后 ， SQL Server 收 回 被 索 引 占用 的 磁 盤 空 間 .2.不 能 用 DROP INDEX 語 句 刪 除 PRIMARY KEY 約 束或 UNIQUE 約 束 創(chuàng) 建 的 索 引 。 必 須 先 刪 除 約 束 ， 使 索 引自 動 刪 除 .3.刪 除 表 的 時 候 ， 表 上 的 所 有 索 引 亦 同 時 被 刪 除 4.刪 除 聚 集 索 引 的 時 候 ， 所 有 表

14、 上 的 非 聚 集 索 引 都 會 自 動被 重 建 .5.DROP INDEX 語 句 不 能 在 系 統(tǒng) 表 上 使 用 如 何 獲 取 某 表 現(xiàn) 有 索 引 的 信 息1、 在 企 業(yè) 管 理 器 中 直 接 查 看2、 EXEC sp_helpindex 表 名 該 系 統(tǒng) 存 儲 過 程 返 回 指 定 表 的 所 有 索 引 信 息3、 EXEC sp_help 表 名該 系 統(tǒng) 存 儲 過 程 返 回 指 定 表 的 所 有 相 關(guān) 信 息 索 引 優(yōu) 化 向 導(dǎo)一.概念： 索引優(yōu)化向?qū)荢QL SERVER 提供的創(chuàng)建索引的工具,它使用查詢優(yōu)化器分析工作負(fù)荷中的查詢?nèi)蝿?wù),向有大量工作負(fù)荷的數(shù)據(jù)庫推薦最佳的索引混合方式,以加快數(shù)據(jù)庫的查詢速度,優(yōu)化整個查詢語句的性能. 索 引 優(yōu) 化 向 導(dǎo)二.使用索引優(yōu)化向?qū)?chuàng)建或更新索引的前提條件: 需要工作負(fù)荷,工作負(fù)荷來源于SQL SERVER捕捉的軌跡和包含SQL語句的文件. 索 引 優(yōu) 化 向 導(dǎo)三.利用索引優(yōu)化向?qū)?chuàng)建索引的步驟:打開查詢分析器查詢索引優(yōu)化向?qū)?