澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:数据库索引,为什麽大家

2019-06-29 作者:数据库网络   |   浏览(189)

 

1.分类

一般的交易系统里面我们都会以自增列或交易时间列作为聚集索引列,因为一般这些系统都是写多读少

--Ref

 一般分为聚集索引和非聚集索引,二者一般采用B树或者hash方法实现,但是较常用的是B树(mysql默认采用的,设计数据库时可以选择),这里主要讲采用B树的方式。hash方法效果听说并不是很好。

每天的交易数据会不停的插入到数据库,但是读取数据就没有数据插入那么频繁

  • SQL Server 索引基础知识系列文章
  • SQL Server 索引结构及其使用系列文章

聚集索引是采用B 树实现,非聚集索引使用B-树实现

因为这些系统一般是写多读少,所以我们会选择在自增列或交易时间列上建立聚集索引

--用法总结

2.区别


下面的表总结了何时使用聚集索引或非聚集索引(很重要):

聚集索引:所有关键字记录仅保存在叶子节点中(叶子节点中的多个关键字以链表连接),索引节点不保存记录,仅保存索引关键字,同一索引下的叶子节点按序存储,查找快。但是主键上聚集索引插入操作时会比较慢,因为要比较所有叶子节点上主键是否有和插入的相同

测试

 

非聚集索引:索引节点保存记录的关键字,及指向该关键字的指针,同一索引的记录不按序存储,查找慢。但是在主键上的聚集索引插入操作时只需要比较索引节点即可

测试环境:SQLSERVER2012 SP1  WINDOWS7 64位

动作描述 使用聚集索引 使用非聚集索引
列经常被分组排序
返回某范围内的数据 不应--物理顺序不同
一个或极少不同值 不应 不应--selectivity小
小数目的不同值 不应
大数目的不同值 不应
频繁更新的列 不应
外键列
主键列
频繁修改索引列 不应

3.适用场景

我们来做一个测试,测试脚本如下:

--聚集索引vs非聚集索引

  1. 聚簇索引是行的物理顺序和索引的顺序是一致的。页级,低层等索引的各个级别上都包含实际的数据页。一个表只能是有一个聚簇索引。由于 update,delete语句要求相对多一些的读操作,因此聚簇索引常常能加速这样的操作。在至少有一个索引的表中,你应该有一个聚簇索引。
    在下面的几个情况下,你可以考虑用聚簇索引:

    • 例如: 某列包括的不同值的个数是有限的(但是不是极少的)顾客表的州名列有50个左右的不同州名的缩写值,可以使用聚簇索引。
    • 例如: 对返回一定范围内值的列可以使用聚簇索引,比如用between,>,>=,<,<=等等来对列进行操作的列上。select * from sales where ord_date between ’5/1/93’ and ’6/1/93’
    • 例如: 对查询时返回大量结果的列可以使用聚簇索引。SELECT * FROM phonebook WHERE last_name = ’Smith’

      当有大量的行正在被插入表中时,要避免在本表一个自然增长(例如,identity列)的列上建立聚簇索引。如果你建立了聚簇的索引,那么insert的性能就会大大降低。因为每一个插入的行必须到表的最后,表的最后一个数据页。当一个数据正在被插入(这时这个数据页是被锁定的),所有的其他插入行必须等待直到当前的插入已经结束。一个索引的叶级页中包括实际的数据页,并且在硬盘上的数据页的次序是跟聚簇索引的逻辑次序一样的。
      当有大量的行正在被插入表中时,要避免在本表一个自然增长(例如,identity列)的列上建立聚簇索引。如果你建立了聚簇的索引,那么insert的性能就会大大降低。因为每一个插入的行必须到表的最后,表的最后一个数据页。当一个数据正在被插入(这时这个数据页是被锁定的),所有的其他插入行必须等待直到当前的插入已经结束。一个索引的叶级页中包括实际的数据页,并且在硬盘上的数据页的次序是跟聚簇索引的逻辑次序一样的

  1. 一个非聚簇的索引就是行的物理次序与索引的次序是不同的。一个非聚簇索引的叶级包含了指向行数据页的指针。在一个表中可以有多个非聚簇索引,你可以在以下几个情况下考虑使用非聚簇索引。在有很多不同值的列上可以考虑使用非聚簇索引
    例如:一个part_id列在一个part表中 select * from employee where emp_id = ’pcm9809f’
    例如:查询语句中用order by 子句的列上可以考虑使用
  • 这里有一个比较关键的概念 Bookmark Lookup 可参看【揭秘SQL Server 2000中的Bookmark Lookup】
    虽然聚集和非聚集索引结构相似,但是一个非聚簇的索引就是行的物理次序与索引的次序是不同的.聚集索引叶节点包含的是实际的值;非聚集索引有两种情况
    1.对于堆表:该指针是指向行的指针
    2.对于聚集索引表:该指针叫做行定位器Bookmark
    SQL Server在查找数据时,服务器先使用和使用聚集索引相同的查找方法找到该索引的行定位器 Bookmark,然后通过行定位器来找到所需要的数据,这种通过行定位器查找数据的方式就是Bookmark Lookup;
    这里注意不是所有的在一个聚集表上使用非聚集索引进行查询,其性能低于在堆集上使用非聚集索引进行查询.因为当返回的字段包含了非聚集索引和聚集索引的列值,那么就会产生索引覆盖,而堆集上使用非聚集索引的返回字段只能是只身才会形成索引覆盖
    3.索引覆盖:在基于非聚集索引查找数据时,还有另外一种情形,那就是如果放回的数据列就包含于索引的键值中,或者包含于索引的键值 聚集索引的键值中,那么就不会发生Bookup Lookup,因为找到索引项,就已经找到所需的数据了,没有必要再到数据行去找了。这种情况,叫做索引覆盖;请记住:无论您是否经常使用聚合索引的其他列,但其前导列一定要是使用最频繁的列。
    可以看看这个例子【Sql Server Database Indexes and Execution Plans】

  • 一个例子sp_spaceused 'order' 结果是3G大小谓词where date = '2009-12-10' 此时date字段上有非聚集索引,那么选择器将会
    1.自增列上建立聚集索引:对date字段上的非聚集索引扫描后,还需要去聚集索引树上seek一下 [子增列建立索引的问题]
    2.表没有聚集索引:去掉自增列上的主键聚集索引,此时表为堆,在非聚集索引扫描后直接就拿到ROWID(堆表的非聚集索引叶节点所存储的标 识所在行包括:FileID,pageID,SlotID)
    3.索引覆盖:将所有需要的字段都汇总到非聚集索引上比如

    select a,b from table where c; 
    --2000中索引覆盖为
    create index idx on t(c,a,b)
    
    --2005中索引覆盖为
    create index idx on t(c) include (a,b)
    
    通过扫描C键值所在的索引上层结构快速找到where条件所需的边界,然后扫描子叶层;循环扫描到a,b的记录位置
    
    --??我觉得这里有一个可以测试的地方就是到底是索引覆盖还是date字段上建立聚集索引好,上一篇文章中有一个查询性能比较:
    1. 返回行数较多:索引覆盖>聚集索引>表扫描>堆集的非聚集索引>聚集的非聚集索引
    2. 返回行数较少:索引覆盖=聚集索引>堆集的非聚集索引>聚集的非聚集索引>表扫描
    
  • 测试

    --SQL Server 2005 Performance Tuning性能调校 代码列表 6.14:通过各种索引,测试所花的 IO 页数.sql
    USE Credit
    GO
    EXEC spCleanIdx 'Charge'
    
    --要求返回 IO 的统计,也就是分页访问的数目
    SET STATISTICS IO ON
    
    --没有索引的页数
    --表 'charge'。扫描计数 1,逻辑读取 584,实际读取 0,读取前读取 0,LOB 逻辑读取 0,LOB 实际读取 0,LOB 读取前读取 0。
    SELECT charge_no FROM charge 
    WHERE charge_amt BETWEEN 20 AND 3000
    
    --通过聚簇索引查询的页数
    --表 'charge'。扫描计数 1,逻辑读取 419,实际读取 0,读取前读取 14,LOB 逻辑读取 0,LOB 实际读取 0,LOB 读取前读取 0。
    CREATE CLUSTERED INDEX ix_charge_amt ON Charge(charge_amt)
    SELECT charge_no FROM charge WHERE charge_amt BETWEEN 20 AND 3000
    
    DROP INDEX Charge.ix_charge_amt
    
    --强制通过非聚簇索引查询的页数,用错索引比不用索引糟糕很多倍
    CREATE INDEX ix_charge_amt ON Charge(charge_amt)
    --表 'charge'。扫描计数 5,逻辑读取 60198,实际读取 0,读取前读取 3,LOB 逻辑读取 0,LOB 实际读取 0,LOB 读取前读取 0。
    --表 'Worktable'。扫描计数 0,逻辑读取 0,实际读取 0,读取前读取 0,LOB 逻辑读取 0,LOB 实际读取 0,LOB 读取前读取 0。
    
    SELECT charge_no FROM charge WITH(INDEX(ix_charge_amt)) WHERE charge_amt BETWEEN 20 AND 3000
    
    DROP INDEX Charge.ix_charge_amt
    
    --通过字段顺序不适用的覆盖索引查询的页数
    CREATE INDEX ix_charge_amt ON Charge(charge_no,charge_amt)
    --表 'charge'。扫描计数 1,逻辑读取 292,实际读取 0,读取前读取 0,LOB 逻辑读取 0,LOB 实际读取 0,LOB 读取前读取 0。
    SELECT charge_no FROM charge WHERE charge_amt BETWEEN 20 AND 3000
    
    DROP INDEX Charge.ix_charge_amt
    
    --通过覆盖索引查询的页数
    CREATE INDEX ix_charge_amt ON Charge(charge_amt,charge_no)
    --表 'charge'。扫描计数 1,逻辑读取 175,实际读取 0,读取前读取 0,LOB 逻辑读取 0,LOB 实际读取 0,LOB 读取前读取 0。
    SELECT charge_no FROM charge WHERE charge_amt BETWEEN 20 AND 3000
    
    DROP INDEX Charge.ix_charge_amt
    
    --通过字段顺序不适用的覆盖索引查询的页数
    CREATE INDEX ix_charge_amt ON Charge(charge_no) INCLUDE(charge_amt)
    --表 'charge'。扫描计数 1,逻辑读取 290,实际读取 0,读取前读取 0,LOB 逻辑读取 0,LOB 实际读取 0,LOB 读取前读取 0。
    SELECT charge_no FROM charge WHERE charge_amt BETWEEN 20 AND 3000
    
    DROP INDEX Charge.ix_charge_amt
    
    --透过子叶层覆盖索引查询的页数
    CREATE INDEX ix_charge_amt ON Charge(charge_amt) INCLUDE(Charge_no)
    --表 'charge'。扫描计数 1,逻辑读取 174,实际读取 0,读取前读取 0,LOB 逻辑读取 0,LOB 实际读取 0,LOB 读取前读取 0。
    SELECT charge_no FROM charge WHERE charge_amt BETWEEN 20 AND 3000
    
    DROP INDEX Charge.ix_charge_amt
    

     

     

  • 上边文章里还有些重点

    • 一个堆集在sysindexes内有一行,其indid=0;
    • 某个表和视图的聚集索引在sysindexes内有一行,其indid=1,root列指向聚集索引B树的顶端;
    • 某个表或视图的非聚集索引在索引在sysindexes内也有一行,其indid值从2到250,root列指向非聚集索引B树的顶端;
    • SQL Server 的数据文件中有一类是IAM,即索引分配映射表,它存储有关表和索引所使用的扩展盘区信息;
    • Bookmark Lookup逻辑运算符和物理运算符使用书签(行 ID 或聚集键)在表或聚集索引内查找相应的行;

--是否值得建索引

无论在哪个数据库里都会有这样的疑问,但是这里永远有三个标准帮助我们来选择,他们是selectivity,density,distribution

  • selectivity:首先要看需要建索引列的选择性,例如

    select * from test where id = 1 --假定select count(*) from test 是10000 那么这个的选择性就是 1/10000,选择性很高,适合建立索引
    select * from test where id > 1 --假定select count(*) from test 是10000 那么这个的选择性就是 9999/10000,选择性很低,不适合建立索引
    除非在id字段是聚集索引,如果采用非聚集索引,反而变成需要读至少9999页以上,因为每读取一条记录时都要将整页读出,再从中取出目标记录,就算数据记录在同一页上也要读多次
    
  • density:密度指键值唯一的记录条数分之一 

    select 1/(select count(distinct id) from test) 
    --当结果越小也就是唯一性越高,就越合适建立索引,也可以使用以下方法检测看传回的All Density值
    Create index idx_id on test(id)
    DBCC Show_Statistics(test,idx_id)
    
  • distribution:一个范围之内的记录条数,或者某个分区的记录条数

  • 看看是否建多了索引
    select * from  sys.dm_db_澳门新萄京官方网站,index_usage_stats where object_id=object_id('table_name')
  • 一些分析索引缺失的视图 (SQL Server 2005 Performance Tuning性能调校(含光盘) P300)
  • select * from sys.dm_db_missing_index_groups
    select * from sys.dm_db_missing_index_group_stats
    select * from sys.dm_db_missing_index_details
    
    SELECT mig.*, statement AS table_name,
        column_id, column_name, column_usage
    FROM sys.dm_db_missing_index_details AS mid
    CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
    INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle
    ORDER BY mig.index_group_handle, mig.index_handle, column_id;
    通过动态管理对象sys.dm_db_missing_index_details和sys.dm_db_missing_index_columns函数返回的结果呈现所需索引键数据行是相等(Equality),不相等(Inequality)或包容(Include)sys.dm_db_missing_index_details视图会在Equality_Columns,Inequality_Columns或Include_Columns等行返回这些信息sys.dm_db_missing_index_columns函数会在其column_usage数据行中返回此信息所以最后的规则就是将Equality_Columns放在最前边,Inequality_Columns随后,然后把Include_Columns放到Include子句中create index idx_test on test(Equality_Columns,Inequality_Columns) include (Include_Columns_1,Include_Columns_2)
    

--一些测试(这个是我看别人文章的总结,忘记出处了,抱歉)

  1. 用聚合索引比用一般的主键作order by时,速度快了3/10。事实上,如果数据量很小的话,用聚集索引作为排序列要比使用非聚集索引速度快得明显的多;而数据量如果很大的话,如10万以上,则二者的速度差别不明显。 
  2. 时间搜索:使用聚合索引内的时间段,搜索时间会按数据占整个数据表的百分比成比例减少,而无论聚合索引使用了多少个

    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-1-1'' 
    --用时:6343毫秒(提取100万条) 整年
    
    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-6-6'' 
    --用时:3170毫秒(提取50万条)半年
    
    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''
    --用时:3326毫秒(和上句的结果一样.如果采集的数量一样,那么用大于号和等于号是一样的,和半年的数据量一样)
    
    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-1-1'' and fariqi<''2004-6-6'' 
    --用时:3280毫秒 半年
    
    --得出以上速度的方法是:在各个select语句前加:
    declare @d datetime
    set @d=getdate()
    --SQL Query
    select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) 
    

--碎片

  • 读书笔记 - 高效维护数据库的关键技巧

--统计信息对索引使用的影响

  • 建立测试环境  

    澳门新萄京官方网站 1澳门新萄京官方网站 2建立测试环境

    USE Tempdb
    --测试统计过期的结果
    SET NOCOUNT ON
    SET STATISTICS IO OFF
    SET STATISTICS PROFILE OFF
    
    CREATE TABLE tblTest(
    UserId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    UserName NVARCHAR(20),
    Gender NCHAR(1))
    
    --一开始构造 100000 笔 '女' 一笔 '男' 的悬殊记录差异
    INSERT tblTest VALUES('Hello World','男')
    
    DECLARE @int INT
    SET @int=1
    WHILE @int<100000
    BEGIN
        INSERT tblTest VALUES('Hello '   CONVERT(NVARCHAR,@int),
                --CASE WHEN @int%2 = 0 THEN '男' ELSE '女' END
                '女'
                )
        SET @int=@int 1 
    END 
    

     

    澳门新萄京官方网站 3澳门新萄京官方网站 4执行计划建立,更新,删除命令

    --执行计划建立,更新,删除命令
    ALTER DATABASE SET
    CREATE STATISTICS
    DBCC SHOW_STATISTICS
    sp_help 'et_order'
    DBCC SHOW_STATISTICS ('et_order', idx_et_0);
    
    DROP STATISTICS
    sp_autostats
    sp_createstats
    UPDATE STATISTICS
    

     

  • 统计数据的影响

    --此时建立索引所同时产生的统计会记录如此悬殊的比值
    CREATE INDEX idxGender ON tblTest(Gender)
    EXEC sp_helpindex tblTest
    --没有单独的统计数据
    EXEC sp_helpstats tblTest
    
    --统计是正确的,索引合用于当下的查询
    SET STATISTICS IO ON
    SELECT * FROM tblTest WHERE Gender='男'
    --强迫表扫描
    SELECT * FROM tblTest WITH(INDEX(0)) WHERE Gender='男'
    SET STATISTICS IO OFF
    
    --故意要求不要自动更新统计数据
    --EXEC sp_dboption 'Credit','Auto Update Statistics', { TRUE | FALSE} --针对整个表
    EXEC sp_autostats 'tblTest','OFF',idxGender
    
    --将记录改成 1:1
    UPDATE tblTest SET Gender='男' WHERE UserID %2=0
    
    SELECT Gender,COUNT(*) FROM tblTest GROUP BY Gender
    
    --比对一下用错索引时,两者的 I/O 差异
    SET STATISTICS IO ON
    
    --通过 SET STATISTICS PROFILE 输出的 Rows 和 EstimateRows 
    --可以比较真实与估计的记录数差异
    SET STATISTICS PROFILE ON
    
    SELECT * FROM tblTest WHERE Gender='男'
    
    --强迫表扫描
    SELECT * FROM tblTest WITH(INDEX(0)) WHERE Gender='男'
    
    DBCC SHOW_STATISTICS(tblTest,idxGender)--这个是建立在统计信息基础上的,上边把统计信息停止后,这个返回的结果是错误的
    
    --做完统计更新后,可以再试一次前述的范例
    --但要先清除旧的运行计划
    UPDATE STATISTICS tblTest
    DBCC FREEPROCCACHE
    

--

澳门新萄京官方网站 5

 1 --测试脚本  插入性能
 2 USE [test]
 3 GO
 4 --建表 以transtime为聚集索引列
 5 CREATE TABLE transtable(tranid INT ,transtime DATETIME)
 6 GO
 7 CREATE CLUSTERED INDEX CIX_transtable ON [dbo].[transtable]([transtime])
 8 GO
 9 
10 --建表 以tranid为聚集索引列
11 CREATE TABLE transtable2(tranid INT ,transtime DATETIME)
12 GO
13 CREATE CLUSTERED INDEX CIX_transtable2 ON [dbo].[transtable2]([tranid])
14 GO
15 
16 
17 ----------------------------------------------------------
18 --先插入测试数据,插入的tranid都为基数
19 DECLARE @i INT
20 SET @i = 1
21 WHILE @i <= 1000000
22     BEGIN 
23         INSERT  INTO [dbo].[transtable]
24                 SELECT  @i , GETDATE()
25         SET @i = @i   2
26     END
27 --------------------------------------
28 DECLARE @i INT
29 SET @i = 1
30 WHILE @i <= 1000000
31     BEGIN 
32         INSERT  INTO [dbo].[transtable2]
33                 SELECT  @i , GETDATE()
34         SET @i = @i   2
35     END
36 
37 -------------------------------------------

4.优点

在transtable表上的transtime(交易时间)上建立聚集索引,在transtable2表上的tranid(交易编号)上建立聚集索引

1)最大好处是可以加快检索速度

我们分别在两个表上插入500000条记录,插入的时候有个特点,就是插入的tranid都是基数

2)在经常分组或者排序字段上加索引,会提高检索时间

1 SELECT COUNT(*) FROM [dbo].[transtable]
2 SELECT COUNT(*) FROM [dbo].[transtable2]
3 
4 SELECT TOP 10 * FROM [dbo].[transtable] ORDER BY [tranid]
5 SELECT TOP 10 * FROM [dbo].[transtable2] ORDER BY [tranid] 

3)创建唯一索引可以保证数据库每一行数据的唯一性

澳门新萄京官方网站 6

澳门新萄京官方网站:数据库索引,为什麽大家一般会在自增列或交易时间列上创设集中索引。4)通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能

我们创建两个存储过程,这两个存储过程为插入到表数据

5)可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义

 1 --------------------------------------------
 2 --创建两个存储过程
 3 CREATE PROC INSERTTranstable
 4 AS
 5     DECLARE @i INT
 6     SET @i = 1
 7     WHILE @i <= 1000
 8         BEGIN 
 9             IF ( @i % 2 = 0 )
10                 BEGIN
11                     INSERT  INTO [dbo].[transtable]
12                             SELECT  @i ,
13                                     GETDATE()
14                     SET @i = @i   1
15                 END
16             ELSE
17                 BEGIN
18                     SET @i = @i   1
19                     CONTINUE 
20                 END
21         END
22 ------------------------------------------
23 CREATE PROC INSERTTranstable2
24 AS
25     DECLARE @i INT
26 SET @i = 1
27 WHILE @i <= 1000
28     BEGIN 
29         IF ( @i % 2 = 0 )
30             BEGIN
31                 INSERT  INTO [dbo].[transtable2]
32                         SELECT  @i ,
33                                 GETDATE()
34                 SET @i = @i   1
35             END
36         ELSE
37             BEGIN
38              SET @i = @i   1
39                 CONTINUE 
40             END
41     END
42 ----------------------------



 1 --------------------------------------------
 2 --创建两个存储过程
 3 CREATE PROC INSERTTranstable
 4 AS
 5     DECLARE @i INT
 6     SET @i = 1
 7     WHILE @i <= 1000
 8         BEGIN 
 9             IF ( @i % 2 = 0 )
10                 BEGIN
11                     INSERT  INTO [dbo].[transtable]
12                             SELECT  @i ,
13                                     GETDATE()
14                     SET @i = @i   1
15                 END
16             ELSE
17                 BEGIN
18                     SET @i = @i   1
19                     CONTINUE 
20                 END
21         END
22 ------------------------------------------
23 CREATE PROC INSERTTranstable2
24 AS
25     DECLARE @i INT
26 SET @i = 1
27 WHILE @i <= 1000
28     BEGIN 
29         IF ( @i % 2 = 0 )
30             BEGIN
31                 INSERT  INTO [dbo].[transtable2]
32                         SELECT  @i ,
33                                 GETDATE()
34                 SET @i = @i   1
35             END
36         ELSE
37             BEGIN
38              SET @i = @i   1
39                 CONTINUE 
40             END
41     END
42 -----------------------------

5.缺点

测试脚本,测试一下插入到两个表的时间

1)创建和维护索引都需要开销

 1 测试插入偶数行的性能
 2 DECLARE @a DATETIME
 3 DECLARE @b DATETIME
 4 SELECT @a=GETDATE()
 5 EXEC INSERTTranstable
 6 SELECT @b=GETDATE()
 7 SELECT @b-@a
 8 --------------------------------------
 9 
10 DECLARE @c DATETIME
11 DECLARE @d DATETIME
12 SELECT @c=GETDATE()
13 EXEC INSERTTranstable2
14 SELECT @d=GETDATE()
15 SELECT @d-@c



 1 测试插入偶数行的性能
 2 DECLARE @a DATETIME
 3 DECLARE @b DATETIME
 4 SELECT @a=GETDATE()
 5 EXEC INSERTTranstable
 6 SELECT @b=GETDATE()
 7 SELECT @b-@a
 8 --------------------------------------
 9 
10 DECLARE @c DATETIME
11 DECLARE @d DATETIME
12 SELECT @c=GETDATE()
13 EXEC INSERTTranstable2
14 SELECT @d=GETDATE()
15 SELECT @d-@c

 1 测试插入偶数行的性能
 2 DECLARE @a DATETIME
 3 DECLARE @b DATETIME
 4 SELECT @a=GETDATE()
 5 EXEC INSERTTranstable
 6 SELECT @b=GETDATE()
 7 SELECT @b-@a
 8 --------------------------------------
 9 
10 DECLARE @c DATETIME
11 DECLARE @d DATETIME
12 SELECT @c=GETDATE()
13 EXEC INSERTTranstable2
14 SELECT @d=GETDATE()
15 SELECT @d-@c

2)索引需要一定的物理空间开销,尤其是聚集索引

验证一下偶数的交易编号是否已经插入到两个表中

3)增删改时,索引要动态维护

澳门新萄京官方网站 7

6.应该在哪些列建立索引

1 SELECT TOP 10 * FROM [dbo].[transtable] ORDER BY [tranid]
2 SELECT TOP 10 * FROM [dbo].[transtable2] ORDER BY [tranid] 

1 SELECT TOP 10 * FROM [dbo].[transtable] ORDER BY [tranid]
2 SELECT TOP 10 * FROM [dbo].[transtable2] ORDER BY [tranid] 

1)经常分组或排序的列

澳门新萄京官方网站 8

2)where子句的查询属性列上

我们看一下时间

3)经常查询的属性列或者经常按范围查找的属性列上

第一个表

4)主键

澳门新萄京官方网站 9

7.哪些列不应建立索引

第二个表

1)仅有少量值的列上,比如性别,只有男女

澳门新萄京官方网站 10

2)很少使用的列,建立索引可能会比不建立有更多的开销

很明显,第一个表比第二个表快,因为的机器的硬盘是固态硬盘,时间差距不是很大,如果是机械硬盘时间差距会大一些,那么究竟为什麽会造成这种情况呢?

3)对text、image、bit列上,这些列的数据量要么比较多,要么很少

我们用下图来解析一下

4)修改较多,但是检索较少的列上

我们先说第二张表

澳门新萄京官方网站 11

澳门新萄京官方网站 12

当交易编号为2的那条记录插入进来的时候,后面的记录都需要向后移动,以使交易编号从小到大排序,因为聚集索引建立在交易编号列上

这个移动时间是有开销的,而且每次偶数交易编号插入到表中,每插入一次就移动一次,而当前面的记录插入到表中的时候移动的记录数就越多

例如:tranid:2,transtime:2014-1-26 31:22.180插入到表中的时候后面的记录都需要移动,而tranid:978,transtime:2014-01-26 00:29:10.830

这条记录插入到表中的时候,后面需要移动的记录数就没有那么多,总之那个开销挺大的。。。

 

第一张表的情况

澳门新萄京官方网站 13

因为第一张表是以交易时间为聚集索引列的,所以无论交易编号是多少,记录都会插入到表的最后,因为后来的记录的交易时间肯定比前面的记录的交易时间大

这样的话,基本上没有开销


现实系统中的情况

实际系统中,新生成的要插入到表中的交易编号是有可能小于当前表中的某条记录的交易编号的,那么这时候记录插入到表中就需要移位(如果聚集索引建立在交易编号上)

如果聚集索引建立在交易时间上,那么新生成的要插入到表中的交易记录时间肯定会大于当前表中的任何一条交易记录的时间

(除非人为修改系统时间造成当前时间比数据库中的某些记录的交易时间要早)


澳门新萄京官方网站:数据库索引,为什麽大家一般会在自增列或交易时间列上创设集中索引。总结

前公司的数据库有些表在自增列,有些表在交易时间列上建立了聚集索引,在交易时间列上建立聚集索引个人觉得很正常

因为在查询的时候按照交易时间来排序《order by 交易时间》,速度上是很快的,但是除了排序之外还有一个作用就是本文所讲到的

插入数据到表中的效率问题

个人觉得一般商场管理系统,油站管理系统都是这类型系统

 

本文的意见纯属我自己的个人意见,并不一定适合您的系统,如果交易时间的选择性不是太高的话,那么可能在交易时间或自增列上建立聚集索引就不是太合适了

我们以前的系统的交易时间的选择性是挺高的,而且通常查询都需要按照交易时间排序,那么聚集索引列建立在交易时间上就是比较好了

 

本次实验用到的完整脚本

1 --测试脚本  插入性能
  2 USE [test]
  3 GO
  4 --建表 以transtime为聚集索引列
  5 CREATE TABLE transtable(tranid INT ,transtime DATETIME)
  6 GO
  7 CREATE CLUSTERED INDEX CIX_transtable ON [dbo].[transtable]([transtime])
  8 GO
  9 
 10 --建表 以tranid为聚集索引列
 11 CREATE TABLE transtable2(tranid INT ,transtime DATETIME)
 12 GO
 13 CREATE CLUSTERED INDEX CIX_transtable2 ON [dbo].[transtable2]([tranid])
 14 GO
 15 
 16 ----------------------------------------------------------
 17 --先插入测试数据,插入的tranid都为基数
 18 DECLARE @i INT
 19 SET @i = 1
 20 WHILE @i <= 1000000
 21     BEGIN 
 22         INSERT  INTO [dbo].[transtable]
 23                 SELECT  @i , GETDATE()
 24         SET @i = @i   2
 25     END
 26 --------------------------------------
 27 DECLARE @i INT
 28 SET @i = 1
 29 WHILE @i <= 1000000
 30     BEGIN 
 31         INSERT  INTO [dbo].[transtable2]
 32                 SELECT  @i , GETDATE()
 33         SET @i = @i   2
 34     END
 35 
 36 -------------------------------------------
 37 SELECT COUNT(*) FROM [dbo].[transtable]
 38 SELECT COUNT(*) FROM [dbo].[transtable2]
 39 
 40 SELECT TOP 10 * FROM [dbo].[transtable] ORDER BY [tranid]
 41 SELECT TOP 10 * FROM [dbo].[transtable2] ORDER BY [tranid] 
 42 
 43 --------------------------------------------
 44 --创建两个存储过程
 45 CREATE PROC INSERTTranstable
 46 AS
 47     DECLARE @i INT
 48     SET @i = 1
 49     WHILE @i <= 1000
 50         BEGIN 
 51             IF ( @i % 2 = 0 )
 52                 BEGIN
 53                     INSERT  INTO [dbo].[transtable]
 54                             SELECT  @i ,
 55                                     GETDATE()
 56                     SET @i = @i   1
 57                 END
 58             ELSE
 59                 BEGIN
 60                     SET @i = @i   1
 61                     CONTINUE 
 62                 END
 63         END
 64 ------------------------------------------
 65 CREATE PROC INSERTTranstable2
 66 AS
 67     DECLARE @i INT
 68 SET @i = 1
 69 WHILE @i <= 1000
 70     BEGIN 
 71         IF ( @i % 2 = 0 )
 72             BEGIN
 73                 INSERT  INTO [dbo].[transtable2]
 74                         SELECT  @i ,
 75                                 GETDATE()
 76                 SET @i = @i   1
 77             END
 78         ELSE
 79             BEGIN
 80              SET @i = @i   1
 81                 CONTINUE 
 82             END
 83     END
 84 -----------------------------
 85 
 86 测试插入偶数行的性能
 87 DECLARE @a DATETIME
 88 DECLARE @b DATETIME
 89 SELECT @a=GETDATE()
 90 EXEC INSERTTranstable
 91 SELECT @b=GETDATE()
 92 SELECT @b-@a
 93 --------------------------------------
 94 
 95 DECLARE @c DATETIME
 96 DECLARE @d DATETIME
 97 SELECT @c=GETDATE()
 98 EXEC INSERTTranstable2
 99 SELECT @d=GETDATE()
100 SELECT @d-@c

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站:数据库索引,为什麽大家

关键词: