索引原理、类型及优化

- 索引的目的

索引的目的在于提高查询的效率，就好比查字典的一样，查mysql这个单个单词就从m字母开始，然后找后面的y和sql，但如果没有索引，就意味着你需要把所有单词都查一遍，才能找到你想要的，显然这需要花大量的时间。

- 索引的原理

数据库系统使用的最多的数据结构为B+tree，

图中浅蓝色的磁盘块，深蓝色的为数据项，黄色的为指针，可以很明显的看出，每个磁盘块都包含几个数据项和指针。比如磁盘块1包含数据项17到35，指针p1表示小于17的数据项，p2表示在17到35之间的数据项，p3表示大于35的数据项，真实的数据保存在最下面的叶子结点即3、5、9、10、13、..、99而非叶子节点的节点则只储存指引搜索方向的数据项。
举个详细的例子，比如说我们要查找数据项10，首先会加载磁盘1到内存，确定10小于17，则锁定磁盘1的p1指针，在加载磁盘2到内存中，通过二分法确定数据项10位于8和12之间，锁定P2指针加载磁盘块6到内存然后通过二分法找到10，整个过程磁盘只进行了三次IO,如果没有索引，这个过程可能需要进行上百次IO，成本是非常高的。

- 索引的类型

大致理解了索引的原理后，在详细看下索引有哪些类型

1.普通索引
这是最基本的索引，没有任何限制

CREATE INDEX INDEX_NAME ON TB_NAME(NAME);

2.唯一索引 （UNIQUE）
该索引与普通索引类似，不同的是唯一索引的列值必要要求为唯一值，可以为空，如果是组合索引，组合值必须唯一

CREATE  UNIQUE INDEX INDEX_NAME ON TB_NAME(NAME);

3.主键索引（primary key）
在数据库表中经常会有一列的值标志该表中每一行，该列就称为改表的主键，在定义表的主键时，DBMS会自动创建主键索引，主键索引是唯一索引中的特定类型,一个表中只能有一个主键，一般是在创建表示指定

CREATE TABLE TABLE_NAME(ID NUMBER(10) NOT NULL,USERNAME VARCHAR(20) NOT NULL)， PRIMARY KEY（ID）; 

也可以在建表后增加修改

ALTER TABLE TB_NAME ADD PRIMARY KEY (ID) USING  INDEX;

如果以前存在主键，则要把以前的主键drop掉，在新增加。

4.复合索引
索引可以在一列或者多列上建立索引，当在两个列以上建立索引是就被称为复合索引。如果一个表在查询中经常同时出现多个字段，那么这些字段就可以建立复合索引

CREATE INDEX INDEX_NAME ON TABLE_NAME(col1，col2，col3)

但是在使用复合索引是有许多问题是需要注意的，如果我们建立如上复合索引，那么SELECT后WHERE条件则要对应索引的顺序,如果顺序不同就可能回导致复合索引无效例如

 SELECT *FROM TB_NAME WHERE COL1=A AND COL2=B AND COL3=C 

同时下面情况也会导致复合索引无效

 SELECT *FROM TB_NAME WHERE COL1=A；  SELECT *FROM TB_NAME WHERE COL1=A AND COL2=B；

- 索引的优化

前面说完了索引的类型，最后在说一下索引改建立在那些字段，以及写SQL时要注意的事情，那些情况会导致索引失效。

• 建立索引的原则

1.建立索引时首先考虑where、order by、group by等常用的字段

2.选择唯一性索引，因为唯一索引的值是唯一的，就能更快通过该索引来确定某条记录，就比如学生信息表中，因为学号是唯一的，而学生姓名会有同名的情况，这是就选则在学好上建立索引而不是在姓名上建立索引。

3.限制索引的数目，索引虽然能提高查询效率，但是并不是越多越好，每个索引多需要占磁盘空间，索引越多，占的空间也就越大当表更新时，维护索引的时间也就越久，一般一张表建立最多5个索引

4.删除不常用或者很少用的索引

5.对于在查询时经常同时出现的字段，建立复合索引

• 下面这些情况则会导致sql不走索引

1.在where查询条件后面带有<、>、！=操作符将会导致查询不走索引而而选择全表查询

2.在where子句中存在对null的判断，或not in ，not exist

3.where子句后面用or链接，但是一个字段有索引而另外的字段没有索引就会导致不走索引

4.使用like时如果前面有%也会导致sql不走索引比如

SELECT * FROM TB_NAME WHERE `uname` LIKE'ABC%' -- 走索引 SELECT * FROM `houdunwang` WHERE `uname` LIKE "%ABC%" -- 不走索引 

5.对索引列进行函数计算也会导致sql不走索引比如

SELECT TB_NAME FROM `stu` WHERE `age`+10=30;

6.建立组合索引，但查询谓词并未使用组合索引的第一列。

ORACLE中是有很多情况会导致INDEX失效的，走索引和全表扫描的差别是很大的，我们增加索引就是为提高查询的效率，因此这些情况在我们写sql的过程中都是要尽量避免的。

文章的最后希望大家注意一点
建立索引是需要付出代价的，索引会提高SELECT 的查询效率，但会降低 DELECT、INSTER等操作的效率，因为增删数据会更该B+树里面各个节点里面的索引内容，破坏B+树的结构，因此，每次数据改变时DBMS都需要重新梳理树的结构，就会带来不小的开销。