思考SQL Server三十问之四索引基础

最新推荐文章于 2024-09-30 10:56:43 发布

weixin_30279671

最新推荐文章于 2024-09-30 10:56:43 发布

阅读量75

点赞数

文章标签：数据结构与算法数据库

原文链接：http://www.cnblogs.com/armor/archive/2012/05/26/2514742.html

版权

4. Index有哪些类型，它们的区别和实现原理是什么，索引有啥优点和缺点；
如何为SQL语句创建合适的索引，索引创建时有哪些需要注意的项，如何查看你创建的索引是否被使用；
如何维护索引；索引损坏如何检查，怎么修复；T-SQL有更好的索引存在，但是运行时并没有使用该索引，原因可能是什么；

思考：
索引设计的思路是采用了空间换时间的概念，一个好的数据结构，合理索引，可以让数据库的性能提高许多；
当然不合理的索引，也不能提高查询的效率，而是浪费空间。

索引的类型：
1. 聚集索引: 聚集索引基于聚集索引键按顺序排序和存储表或视图中的数据行，是一种B-树结构。
2. 非聚集索引：索引上顺序存储的键与堆表行定位符或聚集索引的键组成的数据结构。
3. 唯一索引：该索引确保每一个键值都是唯一的，会自动在该索引生成一个唯一约束。
4. 包含列索引：索引有字段长度（900），字段数（16）的限制，包含列仅存储与子叶节点。
5. xml索引：顾名思义，xml列上创建索引，必须拥有一个主xml索引，才能创建辅助xml索引，一般存储的是xml的路径、标记名、节点类型、基表的主键。
6. 全文索引：工作上没什么机会用到他，对此并不了解；也是一种数据结构，对某个字段做全文索引的时候，需要定时的更新索引的内容。

优点：提高查询效率，减少锁资源占用
缺点：需要额外的空间。
索引的好处还是大于坏处的，堆表的性能在高并发的随机读写远落后与有聚集索引的表。

如何创建合理的索引，需要经验以及对应用的了解；不了解应用就相当于你创建的索引是闭门造车。
一个表一般要求创建主键（一般为聚集索引），并主键要求尽可能的短（10字节以内）；另外一些字段上是否需要创建一些辅助索引，还需要根据业务的实际情况来判断，一个原则：
索引字段，离散度尽量高一些，这样的索引命中率更高一些。

索引是否命中，可以查看dmv里面的统计信息：

View Code

select *
 from sys.dm_db_index_usage_stats
where database_id=DB_ID('tempdb');

-- 查看tempdb库下索引的使用情况

/*
database_id    object_id    index_id    user_seeks    user_scans    user_lookups    user_updates    last_user_seek    last_user_scan    last_user_lookup    last_user_update    system_seeks    system_scans    system_lookups    system_updates    last_system_seek    last_system_scan    last_system_lookup    last_system_update
2    37575172    1    2    3    2    11    2012-05-25 22:38:13.230    2012-05-25 22:39:30.267    2012-05-25 23:35:02.587    2012-05-25 22:37:45.237    0    1    0    0    NULL    2012-05-25 22:38:06.663    NULL    NULL
2    37575172    2    2    0    0    11    2012-05-25 23:35:02.587    NULL    NULL    2012-05-25 22:37:45.237    0    1    0    0    NULL    2012-05-25 22:38:31.907    NULL    NULL
*/

关注user_seek/user_scan/user_lookup，这三个值，如果你的表很忙，但这些值都偏小，可能你的sql没有很好的利用索引。

定期的检查索引的碎片情况，根据碎片进行索引的重建或者重新组织；

select * from sys.dm_db_index_physical_stats(DB_ID('tempdb'),null,null,null,null);

-- 关注：avg_fragmentation_in_percent字段
-- 一般在10%以内算优，30%考虑reorgnize,70%rebuild...