您的位置:新葡亰496net > 网络数据库 > 什么挑选相当的目录,为什麽大家一般会在自增

什么挑选相当的目录,为什麽大家一般会在自增

发布时间:2019-07-05 13:01编辑:网络数据库浏览(54)

     

    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
    

    本文由新葡亰496net发布于网络数据库,转载请注明出处:什么挑选相当的目录,为什麽大家一般会在自增

    关键词:

上一篇:新葡亰496net:读书笔记,数据压缩

下一篇:没有了