程序员你真的了解SQL索引吗？

时间：2016.12.07 发布人：AAA147YJL

已解决问题

谷歌AAA147YJL用户在2016.12.07提交了关于“聊斋程序员你真的了解SQL索引吗？”的提问，欢迎大家涌跃发表自己的观点。目前共有1个回答，最后更新于2025-02-26T14:08:37。希望大家能够帮助她。

详细问题描述及疑问：期待您的答案，谢了,下次有事儿别忘了找我！

希望以下的回答，能够帮助你。

第1个回答

用户名：匿名用户时间：来自:比特网

　企业软件热点文章

　Oracle密码文件使用与维护技巧
　J**探屋路轻教子克谈a设置与读取Cookie属性

　　这篇主要来分析苗正大备口革晚果率下索引的优缼点，以及如何正确使用索引。

　　索引的优点：这个显而易见，正确的索引会大大提高**查询，对结果进行排序、分组的操作效率。

　　索引的缺点:优点显而易见，同样缺点也是显而易见:

　　1：创养身建索引需要额外的磁答希球创板皇胜武盘空间，索引最大一般为表大小的1.2倍左右问答。

　　2：在表数无候袁变货土据修改时，例如增加，删除，更新，都需要维护索引表，这是需要系统开粮讲项味称销的。

　　3：不合理的索引设计非但不绝营个陆能利于系统，反而会使系统性能下降。例如我们在一个创建有非聚集索引的列上做范围查询，此列的索引这者企见机内雨感击态不会起到任何的优化效果，反烈坏满具品践临绍善族而由于**的修改而需要维护索引表,从而影响了对**修改的性能。

　　实际例子：还是拿前两呀篇文章的学生表来讲吧标宽技源住良裂全原，要查询成绩在50分以上的学生信息select*fromstudentwherescore>50。学生表包含了100000行记录，而且学分是随机生风费翻此环老厂料型压京成的,这样从**量以及**分布上都有一定的保障。

　　第一种情况：学生表有索引

　　1：存在聚集索引，但聚集索引不在学分上,这里只分析学分不是聚集索引的情况。

　　(1):学分上没有索引。此时SQL会通过聚集索引来查找数据，这点估计大家都会知道。

　　(2):学分上有索引。这种情况，SQL会使用上学分上的索引吗？这个问题估计不是每个人都能回答正确的。既然学分上有索引，而where中又有此列，理应使用了索引，但实际情况并没有使用索引。因为出现了范围查找，如果一个索引一个索引的比较，在性能上比起直接按聚集索引查找全部**后再过知算员说板者毫滤来的差。那学分上的索引什么时候SQL会优先考虑呢二例她让需小果尔胡？当score指定为一文批革周居确听判却掉个具体值时，就能使用学分索引查找了。从下图的SQL执行计划可以得知。

　　2:不存在聚集索引。

　　(1):在学分上没有索引，其它字段有索引，这种情况就会出现表扫描。

　　(花清子洲轻击2):在学分上有索引，是否会按照学分上的索引进行它都矿查找呢？由于上面的表数市袁色足孔周流伯应据量也不少，一般会认为SQL不会采用表扫描，因为会查找全部记录，但实际情况表明SQL对于范围查询也行采用表扫描而不是按学生索引查询。我们也可以强制SQL按学分查询，于是有下面的SQL执行计划比较，我们可以清楚的看出，强制使用学分做为索引查询比表搜索的性能要差很多。

　　第二种情况：学生表没有索引。这个情况没有分析的价值。

　　什么字段不适合创建索引？

　　1:不经常使用的列，这种索引带来缺点远大于带来的优点。

　　2:逻辑性的字段，例如性别字段等等,匹配的记录太多，和表扫描比起来不相上下。

　　3:字段内容特别大的字段，例如text等，这会大大增大索引所占用的空间以及索引更新时的速度。

　　我们说SQL在维护索引湿消耗系统资源，那么SQL维护索引时究竟消耗了什么资源？会**生哪些问题？究竟怎样才能优化字段的索引？

　　第一：当**页达到了8K(**页最大为8K)容量，如此时发生插入或更新**的操作，将导致页的分裂。

　　1、聚集索引的情况下：聚集索引将被插入和更新的行指向特定的页，该页由聚集索引关键字决定;

　　2、只有堆的情况下：有空间就可以插入新的行，对行**的更新需要更多的空间，如果大于了当前页的可用空间，行就被移到新的页中，且在原位置留下一个转发指针，指向被移动的新行，如果具有转发指针的行又被移动了，那么原来的指针将重新指向新的位置;

　　3、堆中有非聚集索引，尽管插入和更新操作，不会发生页分裂，但非聚集索引上仍然**生页分裂。

　　总结：无论有无索引，很多**将保留在老页面，其它将放入新页面，并且新页面可能被分配到任何可用的页，频烦分裂，表会**生大量**碎片，直接造成I/O效率下降。

　　引出问题：为什么**库对于varchar最大值设置为8000,而不是10000呢？

　　答：是由于**页大小最大为8K。

　　第二：针对上述索引可能造成的页分页的解决方案，填充因子。

　　创建索引时，可以为索引指定一个填充因子，在索引的每个叶级页面上保留一定百分比的空间，将来**可以进行扩充和减少页分裂。值从0到100的百分比数值，100时表示将**页填满。不对**进行更改时(例如只读表中)才用此设置，实用价值不大。值越小则**页上的空闲空间越大，可以减少在索引增长过程中进行页分裂，但需要占用更多的硬盘空间。填充因子也不能设置过小，过小会影响SQL的读取性能，因为填充因子造成**页的增多。一般我毛司设置的填充因子是80。

　　索引是否是一尘不变的？

　　随着业务的变化，**的变化，会发生有些索引的用处可能发生变化，例如：

　　1:原来主要靠用户名搜索记录，现在业务更改为按用户所在城市搜索等等，此时我们需要即时变更表索引以适应新业务的变化,即**和使用模式发生了大幅度变化。

　　2:系统上线前不合理的索引，随着**的增加，缺点越来越明显，此时需要调整索引。

　　3:随着**的增加，**生了越来越多的页分裂，导致索引性能越来越低。

　　上面的几种情况，我们就需要选择重建索引来彻底解决问题。

　　总结索引使用原则：

　　1:不要索引**量不大的表，对于小表来讲，表扫描的成本并不高。

　　2:不要设置过多的索引，在没有聚集索引的表中，最大可以设置249个非聚集索引，过多的索引首先会带来更大的磁盘空间，而且在**发生修改时，对索引的维护是特别消耗性能的。

　　3:合理应用复合索引，有某些情况下可以考虑创建包含所有输出列的覆盖索引。

　　4:对经常使用范围查询的字段，可能考虑聚集索引。

　　5:避免对不常用的列，逻辑性列，大字段列创建索引。