34,593
社区成员
发帖
与我相关
我的任务
分享蒙圈了！谁能给俺讲讲 建索引的原则和注意事项
这两天看了一些关于索引和锁的资料，看完以后我就蒙圈了
资料里讲了索引的好处也说了如果索引建不好会出现的坏处，以及索引对增删改查的影响。但是没告诉我，在实际的应用中应该怎样进行取舍才能实现最优的效果。
我想请教的问题是在实际应用中应该按什么样的原则来规划和使用索引 ？ 以及应该注意的事项。
谢谢
资料里讲了索引的好处也说了如果索引建不好会出现的坏处，以及索引对增删改查的影响。但是没告诉我，在实际的应用中应该怎样进行取舍才能实现最优的效果。
我想请教的问题是在实际应用中应该按什么样的原则来规划和使用索引 ？ 以及应该注意的事项。
谢谢
...全文
请发表友善的回复…
发表回复
lcdldd 2018-01-18
- 打赏
- 举报
学习了
shiguangxin 2017-04-21
- 打赏
- 举报
聚集索引和非聚集索引区别：
聚集索引 ：可以帮助把很大的范围，迅速减小范围。但是查找该记录，就要从这个小范围中Scan了。
非聚集索引：把一个很大的范围，转换成一个小的地图。你需要在这个小地图中找你要寻找的信息的位置。然后通过这个位置，再去找你所需要的记录。
索引与主键的区别：
主键：主键是唯一的，用于快速定位一条记录。
聚集索引：聚集索引也是唯一的。（因为聚集索引的划分依据是物理存储）。而聚集索引的主要是为了快速的缩小查找范围，即记录数目未定。
主键和索引没有关系。他们的用途相近。如果聚集索引加上唯一性约束之后，他们的作用就一样了。
以前认为 聚集索引就是应该归主键所有的（默认的就是这样） ，所以从来就没有在非主键的字段上建立过聚集索引。现在看来以前我认为的主键跟聚集索引的关系是错误的
shiguangxin 2017-04-21
- 打赏
- 举报
主键就是聚集索引，这种想法笔者认为是极端错误的，是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。
通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1。我们的这个办公自动化的实例中的列Gid就是如此。此时，如果我们将这个列设为主键，SQL SERVER会将此列默认为聚集索引。这样做有好处，就是可以让您的数据在数据库中按照ID进行物理排序，但笔者认为这样做意义不大。
显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。
从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中，因为ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则;当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。
下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现(3个月内的数据为25万条)：
(1)仅在主键上建立聚集索引，并且不划分时间段：
Select gid,fariqi,neibuyonghu,title from tgongwen
用时：128470毫秒(即：128秒)
(2)在主键上建立聚集索引，在fariq上建立非聚集索引：
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())
用时：53763毫秒(54秒)
(3)将聚合索引建立在日期列(fariqi)上：
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())
用时：2423毫秒(2秒)
虽然每条语句提取出来的都是25万条数据，各种情况的差异却是巨大的，特别是将聚集索引建立在日期列时的差异。事实上，如果您的数据库真的有1000万容量的话，把主键建立在ID列上，就像以上的第1、2种情况，在网页上的表现就是超时，根本就无法显示。这也是我摒弃ID列作为聚集索引的一个最重要的因素。
以上是从网上找来的，各位以为然否 ？
shiguangxin 2017-04-21
- 打赏
- 举报
动作描述 使用聚集索引 使用非聚集索引
外键列 应 应
主键列 应 应
列经常被分组排序(order by) 应 应
返回某范围内的数据 应 不应
小数目的不同值 应 不应
大数目的不同值 不应 应
频繁更新的列 不应 应
频繁修改索引列 不应 应
一个或极少不同值 不应 不应
shiguangxin 2017-04-21
- 打赏
- 举报
http://bbs.csdn.net/topics/390470793
吉普赛的歌 版主 2017-04-21
- 打赏
- 举报
一切要根据实际情况而定， 没有一定之规。
用时间列作为聚集索引， 只适合表大， 时间列普遍作为查询条件。
对于小表， 增、删、改频繁， 经常要获取单条数据， 查询时间字段没那么多， 就不适合了。
shiguangxin 2017-04-20
- 打赏
- 举报
转一个：
先来看看我们最常见的分页的样子：
WITH CTE AS(
SELECT ROW_NUMBER() OVER ( ORDER BY (A.CreateTime ) AS OrderNo ,
Table_A.ID , --主键
Table_A.其它字段
FROM Table_A WITH ( NOLOCK )
WHERE RecID = 220051
)
SELECT * FROM CTE
WHERE OrderNo BETWEEN 1 AND 50;
的确，这样的写法很符合我们的思维逻辑，并且我们在RecID上建立非聚集索引，那么它的效率看上去也是不错的。当然根据这条SQL，最佳索引实践应该是:
CREATE INDEX IX_Table_A_RecID_CreateTime_Inc
ON Table_A(RecID,CreateTime)
INCLUDE(Table_A.其它字段)
但是，这真的是最佳的了吗？当SQL的Where条件变多，Table_A.其它字段变得越来越多，OVER()子句中的OrderBy字段越来越多或者变成Order By ColumnA/ColumnB这样的计算表达式，这条语句变得越来越不堪重负，最终性能问题凸现出来，另外，作为DBA，我们总是尽量维持索引的简单性、可重用度，而不想建立成为某个语句专用的索引。举例来说，在Include中，我们总不能把Table_A.其它字段中的所有字段都放进去吧，个数少还行，如果遇上几十个字段或者有大容量字符字段，维护成本将大大增加，那将是我们不愿意看到的。
这个时候就要求我们看看是否能对语句做出一些优化了。
在上面的SQL中，我们看它的执行计划，我已经建立了索引，该索引并未Include SELECT列表中的其它字段：
CREATE INDEX IX_Table_A_RecID_CreateTime_Inc
ON Table_A(RecID,CreateTime)
根据上图的执行计划，可以看到，WHERE条件走的是我刚刚建立的索引，下面的键查找与其并行，我们先不讨论该执行计划的具体细节，下面我们来设想几个问题：
在WHERE条件简单，并且索引合适，统计信息正确的前提下，SQL Server可以很容易获得那50行，并且回到聚集索引中找到属于它的其它字段的数据，这是SQL Server的智能编译的结果，也是我们希望看到的返回方式。
但是，在WHERE条件较为复杂，多个WHERE条件均为范围字段或者状态字段时，执行计划也许并没有我们想象的那么智能了，比如它可能采用这样的方式：
当SQL Server无法准确的取出你要的那些行时，那么它便会取回全部的行数后，再去聚集索引中找回属于它的其它字段的数据，当where条件可以返回几十万数据时，你可以想象它的效率有多低，它会仍然使用上文中类似的执行计划，这显然不是我们希望看到的。
我们想看到的是什么？
1、根据WHERE条件和排序规则，先取出那50条数据所属的主键。
SELECT ROW_NUMBER() OVER ( ORDER BY A.CreateTime ) AS OrderNo ,
Table_A.ID --主键
INTO #1
FROM Table_A WITH ( NOLOCK )
WHERE RecID = 220051
2、利用上个步骤中返回的主键，去原始表取回这50条记录的其它字段数据。
SELECT B.*,A.其它字段 FROM Table_A A WITH ( NOLOCK )
INNER JOIN #1 B ON A.ID=B.ID
WHERE B.OrderNo BETWEEN 1 AND 50;
那么，上面两个步骤合在一起：
WITH CTE AS(
SELECT ROW_NUMBER() OVER ( ORDER BY A.CreateTime ) AS OrderNo ,
Table_A.ID --主键
FROM Table_A WITH ( NOLOCK )
WHERE RecID = 220051
)
SELECT CTE.*,A.其它字段 FROM Table_A A WITH ( NOLOCK )
INNER JOIN CTE ON A.ID=CTE.ID
WHERE CTE.OrderNo BETWEEN 1 AND 50;
很好，现在我们再来看一下这个SQL的执行计划：
Binggo!这才是我们理想中的样子！
针对这个SQL，我们只需要建立一个合适的索引，而不用顾忌SELECT列表中那些烦人的其它列，因为他们回聚集索引取数据，也不过几百个IO而已（需要返回的行数*Index_Level）。它不需要再为过期的统计信息或者错误的执行计划而付出沉重的代价！
吉普赛的歌 版主 2017-04-20
- 打赏
- 举报
多谢 版主大人的指点
1.小表(如小于1000行)无需建索引.
请问这是为什么呢？ 如果小表需要频繁的跟别的表进行连接呢？ 比如 部门表、职务表、类别表 等等
另外 请教一下： select 的时候，在什么情况下会出现表扫描
[/quote]
跟你打个比方吧：
比如你去教室找一个小孩， 教室里只有 几 个人，
两种办法：
1. 你一个个地看， 可以很快找到人， 这个花不了多少时间（表扫描）;
2. 给每个小孩编学号， 你叫学号时， 小孩喊到（索引查找）。
表扫描说起来不好听， 但在特殊情况下比索引查找还快。
SQL Server 会衡量一下各种方案， 看哪种占资源最少取哪一种。
shiguangxin 2017-04-20
- 打赏
- 举报
--## 分析表的索引建立情况：
DBCC showcontig('tabname')
--##执行重建索引命令：
DBCC DBREINDEX('tabname')
shiguangxin 2017-04-20
- 打赏
- 举报
--查看当前数据库中没用的索引
select tb_name=object_name(a.object_id)
,idx_name=b.name
,last_user_update
,c.colid,c.keyno
,col_name=d.name
into #tmp
from sys.dm_db_index_usage_stats a left join sys.indexes b on a.object_id=b.object_id and a.index_id=b.index_id
left join sys.sysindexkeys c on c.id=a.object_id and c.indid=a.index_id
left join syscolumns d on d.id=c.id and d.colid=c.colid
where database_id=db_id()
and last_user_seek is null
and last_user_scan is null
and last_user_lookup is null
and last_user_update is not null
order by tb_name,idx_name,keyno
select tb_name,idx_name,last_user_update
,keywords= stuff(
(select '+'+ col_name
FROM #tmp
where tb_name=a.tb_name and idx_name=a.idx_name
order by tb_name,idx_name,keyno
for xml path('') )
,1,1,'')
from #tmp a
group by tb_name,idx_name,last_user_update
shiguangxin 2017-04-20
- 打赏
- 举报
-- 当前数据库可能缺少的索引
select d.*
, s.avg_total_user_cost
, s.avg_user_impact
, s.last_user_seek
,s.unique_compiles
from sys.dm_db_missing_index_group_stats s
,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details d
where s.group_handle = g.index_group_handle
and d.index_handle = g.index_handle
order by s.avg_user_impact desc
go
0与1之间 2017-04-20
- 打赏
- 举报
表的索引数量不要太多
常使用的条件可以加索引
索引字段重复率低
shiguangxin 2017-04-20
- 打赏
- 举报
谢谢 qq_18633095 的捧场 ， 只是你的回复在下有点看不懂啊。
shiguangxin 2017-04-20
- 打赏
- 举报
多谢 版主大人的指点
1.小表(如小于1000行)无需建索引.
请问这是为什么呢？ 如果小表需要频繁的跟别的表进行连接呢？ 比如 部门表、职务表、类别表 等等
另外 请教一下： select 的时候，在什么情况下会出现表扫描
shiguangxin 2017-04-20
- 打赏
- 举报
满满的干货 多谢 yenange
唐诗三百首 2017-04-20
- 打赏
- 举报
几个建议:
1.小表(如小于1000行)无需建索引.
2.控制表上的索引数量.
3.借助DMV视图sys.dm_db_index_usage_stats可查看索引的使用情况统计.
4.借助DMV视图sys.dm_db_missing_index_groups,sys.dm_db_missing_index_details可查看系统建议需创建的索引.
5.注意索引碎片问题,定期重整.
家有大汤圆 2017-04-20
- 打赏
- 举报
先将左边是0的提取出来拼右边的计算结果
DECLARE @id NVARCHAR(100) ='0110098'
SELECT left(@id,patindex('%[^0]%',@id)-1)+cast(cast(@id as int)+1 as varchar)
吉普赛的歌 版主 2017-04-20
- 打赏
- 举报
--1. 所有的连接字段必须要有索引
--2. 能快速减少结果集的字段必须建立索引，如：结算日期
--3. 对于状态比较少的不需要建立索引
--所谓取大放小， 对于价值不高的索引不急着建, 先建好了看效果，效果好就先用着， 不要一个劲地折腾。
--可以做监控， 有慢SQL 再具体事情具体分析 ， 一口气吃不成个胖子吉普赛的歌 版主 2017-04-20
- 打赏
- 举报
SELECT T1.流水号,
T1.单据状态,
T1.会员ID,
T2.姓名,
T2.微信,
T3.会员类型名称,
T3.会员等级,
T1.联系人,
T1.联系方式
T1.应收金额,
T1.实收金额,
T1.结算方式ID,
T4.项目ID,
T4.项目ID,
T5.名称,
T4.数量,
T4.金额,
T6.员工ID,
T7.姓名,
T8.职务名称
FROM 单据主表 T1 WITH (NOLOCK) INNER
JOIN T2.会员表
ON T1.会员ID = T2.ID
LEFT JOIN 会员类型表 T3 WITH (NOLOCK)
ON T2.类型ID = T3.ID
INNER JOIN 单据辅表 T4 WITH (NOLOCK)
ON T1.流水号 = T4.流水号
LEFT JOIN 项目和产品表 T5 WITH (NOLOCK)
ON T4.项目ID = T5.ID
INNER JOIN 员工绩效表 T6 WITH (NOLOCK)
ON T4.ID = T6.辅表ID
AND T4.流水号 = T6.流水号
INNER JOIN 员工表 T7 WITH (NOLOCK)
ON T6.员工ID = T7.ID
LEFT JOIN 职务表 T8 WITH (NOLOCK)
ON T7.职务ID = T8.ID
WHERE (T1.结算日期 >= '2017-1-1' AND T1.结算日期 <= '2017-2-1')
AND T1.单据状态 = 1
AND T1.结算状态 = 1
AND T5.类别ID IN (1, 3, 5, 7, 9)
AND T7.职务ID = 8
ORDER BY
T1.会员ID ASC,
T4.项目ID ASC,
T1.结算日期 asc
zbdzjx 2017-04-20
- 打赏
- 举报
把SQL语句放到“Database Engine Tuning Advisor”中跑一下，就知道要建什么样的索引了。根据提示，再研究一下原理。
加载更多回复（7）
