电脑知识|欧美黑人一区二区三区|软件|欧美黑人一级爽快片淫片高清|系统|欧美黑人狂野猛交老妇|数据库|服务器|编程开发|网络运营|知识问答|技术教程文章 - 好吧啦网

您的位置:首頁技術文章
文章詳情頁

SQL Server中索引使用及維護

瀏覽:54日期:2023-11-02 08:40:45

在應用系統中,尤其在聯機事務處理系統中,對數據查詢及處理速度已成為衡量應用系統成敗的標準。而采用索引來加快數據處理速度也成為廣大數據庫用戶所接受的優化方法。

在良好的數據庫設計基礎上,能有效地使用索引是SQL Server取得高性能的基礎,SQL Server采用基于代價的優化模型,它對每一個提交的有關表的查詢,決定是否使用索引或用哪一個索引。因為查詢執行的大部分開銷是磁盤I/O,使用索引 提高性能的一個主要目標是避免全表掃描,因為全表掃描需要從磁盤上讀表的每一個數據頁,如果有索引指向數據值,則查詢只需讀幾次磁盤就可以了。所以如果建 立了合理的索引,優化器就能利用索引加速數據的查詢過程。但是,索引并不總是提高系統的性能,在增、刪、改操作中索引的存在會增加一定的工作量,因此,在 適當的地方增加適當的索引并從不合理的地方刪除次優的索引,將有助于優化那些性能較差的SQL Server應用。實踐表明,合理的索引設計是建立在對各種查詢的分析和預測上的,只有正確地使索引與程序結合起來,才能產生最佳的優化方案。本文就 SQL Server索引的性能問題進行了一些分析和實踐。

一、聚簇索引(clustered indexes)的使用

聚簇索引是一種對磁盤上實際數據重新組織以按指定的一個或多個列的值排序。由于聚簇 索引的索引頁面指針指向數據頁面,所以使用聚簇索引查找數據幾乎總是比使用非聚簇索引快。每張表只能建一個聚簇索引,并且建聚簇索引需要至少相當該表 120%的附加空間,以存放該表的副本和索引中間頁。建立聚簇索引的思想是:

1、大多數表都應該有聚簇索引或使用分區來降低對表尾頁的競爭,在一個高事務的環境中,對最后一頁的封鎖嚴重影響系統的吞吐量。

2、在聚簇索引下,數據在物理上按順序排在數據頁上,重復值也排在一起,因而在那些 包含范圍檢查(between、<、<=、>、>=)或使用group by或order by的查詢時,一旦找到具有范圍中第一個鍵值的行,具有后續索引值的行保證物理上毗連在一起而不必進一步搜索,避免了大范圍掃描,可以大大提高查詢速度。

3、在一個頻繁發生插入操作的表上建立聚簇索引時,不要建在具有單調上升值的列(如IDENTITY)上,否則會經常引起封鎖沖突。

4、在聚簇索引中不要包含經常修改的列,因為碼值修改后,數據行必須移動到新的位置。

5、選擇聚簇索引應基于where子句和連接操作的類型。

聚簇索引的侯選列是:

1、主鍵列,該列在where子句中使用并且插入是隨機的。

2、按范圍存取的列,如pri_order > 100 and pri_order < 200。

3、在group by或order by中使用的列。

4、不經常修改的列。

5、在連接操作中使用的列。

二、非聚簇索引(nonclustered indexes)的使用

SQL Server缺省情況下建立的索引是非聚簇索引,由于非聚簇索引不重新組織表中的數據,而是對每一行存儲索引列值并用一個指針指向數據所在的頁面。換句話 說非聚簇索引具有在索引結構和數據本身之間的一個額外級。一個表如果沒有聚簇索引時,可有250個非聚簇索引。每個非聚簇索引提供訪問數據的不同排序順 序。在建立非聚簇索引時,要權衡索引對查詢速度的加快與降低修改速度之間的利弊。另外,還要考慮這些問題:

1、索引需要使用多少空間。

2、合適的列是否穩定。

3、索引鍵是如何選擇的,掃描效果是否更佳。

4、是否有許多重復值。

對更新頻繁的表來說,表上的非聚簇索引比聚簇索引和根本沒有索引需要更多的額外開 銷。對移到新頁的每一行而言,指向該數據的每個非聚簇索引的頁級行也必須更新,有時可能還需要索引頁的分理。從一個頁面刪除數據的進程也會有類似的開銷, 另外,刪除進程還必須把數據移到頁面上部,以保證數據的連續性。所以,建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情況:

1、某列常用于集合函數(如Sum,....)。

2、某列常用于join,order by,group by。

3、查尋出的數據不超過表中數據量的20%。

三、覆蓋索引(covering indexes)的使用

覆蓋索引是指那些索引項中包含查尋所需要的全部信息的非聚簇索引,這種索引之所以比較快也正是因為索引頁中包含了查尋所必須的數據,不需去訪問數據頁。如果非聚簇索引中包含結果數據,那么它的查詢速度將快于聚簇索引。

但是由于覆蓋索引的索引項比較多,要占用比較大的空間。而且update操作會引起索引值改變。所以如果潛在的覆蓋查詢并不常用或不太關鍵,則覆蓋索引的增加反而會降低性能。

四、索引的選擇技術

p_detail是住房公積金管理系統中記錄個人明細的表,有890000行,觀察 在不同索引下的查詢運行效果,測試在C/S環境下進行,客戶機是IBM PII350(內存64M),服務器是DEC Alpha1000A(內存128M),數據庫為SYBASE11.0.3。

1、 select count(*) from p_detail where;;op_date>’19990101’ and op_date<’19991231’ and pri_surplus1>300;2、 select count(*),sum(pri_surplus1) from p_detail;;where op_date>’19990101’ and;;pay_month between‘199908’ and’199912’不建任何索引查詢1 1分15秒查詢2 1分7秒在op_date上建非聚簇索引查詢1 57秒查詢2 57秒在op_date上建聚簇索引查詢1 <1秒查詢2 52秒在pay_month、op_date、pri_surplus1上建索引查詢1 34秒查詢2 <1秒在op_date、pay_month、pri_surplus1上建索引查詢1 <1秒查詢2 <1秒

從以上查詢效果分析,索引的有無,建立方式的不同將會導致不同的查詢效果,選擇什么樣的索引基于用戶對數據的查詢條件,這些條件體現于where從句和join表達式中。一般來說建立索引的思路是:

(1)、主鍵時常作為where子句的條件,應在表的主鍵列上建立聚簇索引,尤其當經常用它作為連接的時候。

(2)、有大量重復值且經常有范圍查詢和排序、分組發生的列,或者非常頻繁地被訪問的列,可考慮建立聚簇索引。

(3)、經常同時存取多列,且每列都含有重復值可考慮建立復合索引來覆蓋一個或一組查詢,并把查詢引用最頻繁的列作為前導列,如果可能盡量使關鍵查詢形成覆蓋查詢。

(4)、如果知道索引鍵的所有值都是唯一的,那么確保把索引定義成唯一索引。

(5)、在一個經常做插入操作的表上建索引時,使用fillfactor(填充因子)來減少頁分裂,同時提高并發度降低死鎖的發生。如果在只讀表上建索引,則可以把fillfactor置為100。

(6)、在選擇索引鍵時,設法選擇那些采用小數據類型的列作為鍵以使每個索引頁能夠容納盡可能多的索引鍵和指針,通過這種方式,可使一個查詢必須遍歷的索引頁面降到最小。此外,盡可能地使用整數為鍵值,因為它能夠提供比任何數據類型都快的訪問速度。

五、索引的維護

上面講到,某些不合適的索引影響到SQL Server的性能,隨著應用系統的運行,數據不斷地發生變化,當數據變化達到某一個程度時將會影響到索引的使用。這時需要用戶自己來維護索引。索引的維護包括:

1、重建索引

隨著數據行的插入、刪除和數據頁的分裂,有些索引頁可能只包含幾頁數據,另外應用在執行大塊I/O的時候,重建非聚簇索引可以降低分片,維護大塊I/O的效率。重建索引實際上是重新組織B-樹空間。在下面情況下需要重建索引:

(1)、數據和使用模式大幅度變化。

(2)、排序的順序發生改變。

(3)、要進行大量插入操作或已經完成。

(4)、使用大塊I/O的查詢的磁盤讀次數比預料的要多。

(5)、由于大量數據修改,使得數據頁和索引頁沒有充分使用而導致空間的使用超出估算。

(6)、dbcc檢查出索引有問題。

當重建聚簇索引時,這張表的所有非聚簇索引將被重建。

2、索引統計信息的更新

當在一個包含數據的表上創建索引的時候,SQL Server會創建分布數據頁來存放有關索引的兩種統計信息:分布表和密度表。優化器利用這個頁來判斷該索引對某個特定查詢是否有用。但這個統計信息并不 動態地重新計算。這意味著,當表的數據改變之后,統計信息有可能是過時的,從而影響優化器追求最有工作的目標。因此,在下面情況下應該運行update statistics命令:

(1)、數據行的插入和刪除修改了數據的分布。

(2)、對用truncate table刪除數據的表上增加數據行。

(3)、修改索引列的值。

六、結束語

實踐表明,不恰當的索引不但于事無補,反而會降低系統的執行性能。因為大量的索引在插入、修改和刪除操作時比沒有索引花費更多的系統時間。例如下面情況下建立的索引是不恰當的:

1、在查詢中很少或從不引用的列不會受益于索引,因為索引很少或從來不必搜索基于這些列的行。

2、只有兩個或三個值的列,如男性和女性(是或否),從不會從索引中得到好處。

另外,鑒于索引加快了查詢速度,但減慢了數據更新速度的特點。可通過在一個段上建表,而在另一個段上建其非聚簇索引,而這兩段分別在單獨的物理設備上來改善操作性能。

標簽: Sql Server 數據庫
主站蜘蛛池模板: 精密五金冲压件_深圳五金冲压厂_钣金加工厂_五金模具加工-诚瑞丰科技股份有限公司 | 培训一点通 - 合肥驾校 - 合肥新亚驾校 - 合肥八一驾校 | 大米加工设备|大米加工机械|碾米成套设备|大米加工成套设备-河南成立粮油机械有限公司 | 微信小程序定制,广州app公众号商城网站开发公司-广东锋火 | 自动售货机_无人售货机_专业的自动售货机运营商_免费投放售货机-广州富宏主官网 | 压装机-卧式轴承轮轴数控伺服压装机厂家[铭泽机械] | hc22_hc22价格_hc22哈氏合金—东锜特殊钢| 深圳货架厂家_金丽声精品货架_广东金丽声展示设备有限公司官网 | 耐热钢-耐磨钢-山东聚金合金钢铸造有限公司 | 上海办公室设计_办公楼,写字楼装修_办公室装修公司-匠御设计 | 进口试验机价格-进口生物材料试验机-西安卡夫曼测控技术有限公司 | 定量包装秤,吨袋包装称,伸缩溜管,全自动包装秤,码垛机器人,无锡市邦尧机械工程有限公司 | 施工围挡-施工PVC围挡-工程围挡-深圳市旭东钢构技术开发有限公司 | 防伪溯源|防窜货|微信二维码营销|兆信_行业内领先的防伪防窜货数字化营销解决方案供应商 | 进口便携式天平,外校_十万分之一分析天平,奥豪斯工业台秤,V2000防水秤-重庆珂偌德科技有限公司(www.crdkj.com) | 真空乳化机-灌装封尾机-首页-温州精灌 | 标准件-非标紧固件-不锈钢螺栓-非标不锈钢螺丝-非标螺母厂家-三角牙锁紧自攻-南京宝宇标准件有限公司 | 东风体检车厂家_公共卫生体检车_医院体检车_移动体检车-锦沅科贸 | 泵阀展|阀门展|水泵展|流体机械展 -2025上海国际泵管阀展览会flowtech china | 双相钢_双相不锈钢_双相钢圆钢棒_双相不锈钢报价「海新双相钢」 双能x射线骨密度检测仪_dxa骨密度仪_双能x线骨密度仪_品牌厂家【品源医疗】 | 江西自考网-江西自学考试网| 不锈钢/气体/液体玻璃转子流量计(防腐,选型,规格)-常州天晟热工仪表有限公司【官网】 | 信阳市建筑勘察设计研究院有限公司 | 液压油缸-液压缸厂家价格,液压站系统-山东国立液压制造有限公司 液压油缸生产厂家-山东液压站-济南捷兴液压机电设备有限公司 | 优考试_免费在线考试系统_培训考试系统_题库系统_组卷答题系统_匡优考试 | 检验科改造施工_DSA手术室净化_导管室装修_成都特殊科室建设厂家_医疗净化工程公司_四川华锐 | 精密冲床,高速冲床等冲压设备生产商-常州晋志德压力机厂 | 钢托盘,钢制托盘,立库钢托盘,金属托盘制造商_南京飞天金属制品实业有限公司 | 健康管理师报名入口,2025年健康管理师考试时间信息网-网站首页 塑料造粒机「厂家直销」-莱州鑫瑞迪机械有限公司 | 贝朗斯动力商城(BRCPOWER.COM) - 买叉车蓄电池上贝朗斯商城,价格更超值,品质有保障! | 湖州织里童装_女童男童中大童装_款式多尺码全_织里儿童网【官网】-嘉兴嘉乐网络科技有限公司 | 气胀轴|气涨轴|安全夹头|安全卡盘|伺服纠偏系统厂家-天机传动 | 首页|专注深圳注册公司,代理记账报税,注册商标代理,工商变更,企业400电话等企业一站式服务-慧用心 | 冷却塔改造厂家_不锈钢冷却塔_玻璃钢冷却塔改造维修-广东特菱节能空调设备有限公司 | 济南网站建设|济南建网站|济南网站建设公司【济南腾飞网络】【荐】 | 北京百度网站优化|北京网站建设公司-百谷网络科技 | 冷油器-冷油器换管改造-连云港灵动列管式冷油器生产厂家 | 专业深孔加工_东莞深孔钻加工_东莞深孔钻_东莞深孔加工_模具深孔钻加工厂-东莞市超耀实业有限公司 | 东莞螺杆空压机_永磁变频空压机_节能空压机_空压机工厂批发_深圳螺杆空压机_广州螺杆空压机_东莞空压机_空压机批发_东莞空压机工厂批发_东莞市文颖设备科技有限公司 | 最新电影-好看的电视剧大全-朝夕电影网 | 无锡网站建设_小程序制作_网站设计公司_无锡网络公司_网站制作 |