深入浅出数据库索引

深入浅出数据库索引

　　前几天在支持网上面接到一个任务，说是打开一个页面要2-3分钟，希望能查明原因并做优化，没办法，作为实习生的我，确实没什么经验，只有System.currentTimeMillis()去一段一段的排查问题出现的地方，结果就定位到这样的一个SQL:

SELECT  A.CDOCUMENTID,A.CDOCUMENTVER,<....>   
FROM DOC_008 A 
         LEFT OUTER JOIN DOC_030 D ON(A.SECRECYGRADEID=D.SECRECYGRADEID)
         LEFT OUTER JOIN PRJ_019 FF ON(FF.CTASKID=A.CPROJECTID) 
         LEFT OUTER JOIN PRJ_019 GG ON(GG.CTASKID=A.CPRODUCT)  
         LEFT OUTER JOIN TECH_001 TECH001 
             ON (TECH001.ID = A.CACTIVEID)
         LEFT OUTER JOIN SYS_019 C ON(A.CCREATORID=C.CPERSONID)
    ,DOC_017 B,DOC_003 F ,DOC_002 G ,SYS_019 H  
WHERE A.CMKINDID=B.CMKINDID AND B.KINDTYPE=0  
    AND F.OBJECTID=A.CDOCUMENTID 
    AND F.OBJECTVER=A.CDOCUMENTVER AND F.APPLYID NOT IN 
        (SELECT APPLYID FROM DOC_020 where PRINTTYPE='申请文档' ) 
    AND F.STATE like '%PASS' AND F.APPLYID = G.APPLYID 
    AND G.PERSON = H.CPERSONID AND F.PRINTNUM <> '0'  
ORDER BY F.OPERATIONTIME DESC

　　乍一看这个SQL没什么问题啊，但是查询数据集只有两万条记录，结果集只有7千多条，单独执行这个SQL就是需要一分半，作为一个小白，我就网上到处找SQL语句优化调整，就是没什么效果，询问带我的老师，他说你去了解了解数据库索引，没准能有办法.这不试不知道，一试吓一跳，我在DOC_020的PRINTTYPE上面加一个非聚集索引，SQL执行时间里面就变成了零点几秒。

--DOC_020添加索引 优化文档打印界面SQL执行效率
if not exists(select 1 from sysindexes where id=object_id('doc_020') and name='PRINTTYPE_index') 
begin
CREATE INDEX PRINTTYPE_index ON doc_020(PRINTTYPE)
END 
GO

　　这样的效果也是让我非常震惊，为什么一个小小的索引可以带来这么大的查询效率优化，自己呢，也是瞎猫碰上死耗子连蒙带猜的完成了优化任务，今天呢，想起来好好看一下数据库索引到底是什么。我就找了一篇，我觉得比较不错的讲解:https://www.cnblogs.com/aspwebchh/p/6652855.html相当于写一个读后的总结吧，支持大佬原创，推荐大家也去看看。

1.数据库索引原理

　　想要理解数据库索引原理，必须搞清楚，索引使用的数据结构大致能分为两种平衡树（BTree）和哈希桶，一般主流的关系数据库都使用平衡树当做数据表默认的索引数据结构（MARK:稍后会详细了解一下这两种数据结构作为索引的区别）。
　　平衡树，即平衡二叉树（Balanced Binary Tree），具有以下性质 ：它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。平衡二叉树的常用算法有红黑树、AVL、Treap、伸展树、SBT等。（数据结构知识点，学过了但是真的没老实学，有点忘记了），大致的意思就是我们把索引的值当做树种的节点（类似与查找树），当我们进行索引时，平衡树能保证比较稳定的高的查找效率。

2.DB中的聚集索引和非聚集索引

　　数据库中具体是如何使用平衡树的呢？数据库中索引分为聚集索引和非聚集索引，一般来索聚集索引只有一个，那就是主键。聚集索引的作用就是，通过聚集索引值构建平衡二叉树，查找到的就是记录的位置；非聚集索引（常规索引）的作用就是，通过索引值构建平衡树，查找到的是记录对应的主键值，再使用主键的值通过聚集索引查找需要的数据。

select * from table where id = 250;
--id 主键值，聚集索引

　　执行这个SQL的where时，id为主键有聚集索引，使用索引值250到平衡树中查询记录的位置(真实数据的位置)，就可以找到具体的记录。（平衡树的查找复杂度不做讨论，数据结构基础）

/建立索引
create index index_birthday on user_info(birthday);
//查询生日在1991年11月1日出生用户的用户名
select user_name from user_info where birthday = '1991-11-1'

　　执行这个SQL过程为:1.通过非聚集索引index_birthday 查找birthday为1991-11-1的所有记录的主键值（ID值）；2.通过得到的主键值执行聚集索引查找，找到记录存储的位置，最后取得user-name字段的值返回。

3.多个字段的索引（复合索引）

　　一个索引有一个独立的平衡树，一个索引可以建立在一个字段，也可以建立在多个字段，那么建立在多个字段上的索引和单个有什么区别呢？接着上面的例子，我们修改单个字段索引为两个字段索引。

create index index_birthday_and_user_name on user_info(birthday, user_name);

select user_name from user_info where birthday = '1991-11-1'

　　索引建立在birthday和user_name两个字段上面，那么，平衡树上面的叶节点除了有主键值以外，username也在平衡树上，执行select语句时，就不会再用主键值去聚集索引，直接就能放回username值，能大大的提高查询的性能。

4.总结分析

　　1.建立索引可以提高性能，那么所有的值都建立索引，不就很完美了吗？答案是错误的，数据库在建立索引的同时，字段中的数据就会被复制一份出来，需要更多的空间存储平衡树结构，等同于用空间换取时间。如果我空间足够多，是不是就能随意添加索引了呢？答案也是错误的，索引带来的是查询的效率优化，也就是我们能直观的体验到的，但是在我们新建，修改或者删除一条记录的时候，平衡树结构会被破坏，我们需要花费多的时间去维护平衡树保持在一个正确的状态，也就是每个平衡树都要重新去梳理一遍，这会带来不小的性能开销，会给除了查询以外的操作带来副作用。所以要合理的建立索引，取得时间空间各个花销的平衡点。

　　2.不管以任何方式查询表，最终都会利用主键通过聚集索引来定位真实主句的位置，即是，聚集索引（主键）是通往真实数据所在的唯一途径。上面复合索引的例子，是特例，只查询某个特定的字段值，可以不使用聚集索引（特定字段值不等同于真实数据）。

　　3.

　　 - 列经常被分组排序的时候，可以考虑使用索引
　　 - 返回某范围内的数据，可以考虑使用聚集索引
　　 - 频繁更新的列，最好不要使用聚集索引
　　 - 频繁使用一个列的值，查询另外一列的值，考虑使用符合索引
　　 - 复合索引的前导列一定要是使用最频繁的列

　　4.读完了上面推荐的博客，网页查看别的博文时，发现了我的一个误区，就是下面的这篇博客:https://www.cnblogs.com/hyd1213126/p/5828937.html
　　误区:主键就是聚集索引
　　我们建立数据库的习惯都是，使用一个步长为1 的自增长的ID列作为主键，SQL_server会将此列默认为聚集索引，这样的好处就是，可以让数据在数据库中按照ID进行物理排序。主键值不能相同，那么在这个主键值上面建立聚集索引，带来的效率并不是最好的。（具体可以看上面的这篇博文）
　　
＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃OVER＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃