SQL之索引

什么是索引，直观上是一种列表，一种目录。索引实际上是属于数据库引擎级别，比如Mysql的MyISAM和InnoDB,使用的分别是非聚簇索引和聚簇索引。那么哈希索引，全文索引，B树索引，B+树索引，组合索引又是什么东西呢？
索引最大的作用就是加速查询，因为不需要扫描整张数据表，扫描索引就可以快速查询。具体机制是什么，下面介绍？
索引简单使用
创建索引：
建表时创建：

CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [indexName] (username(length))  
);
1
2
3
4
5

建表后创建

ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name);
或者
CREATE INDEX index_name ON my_table(column_name);
1
2
3

删除索引

DROP INDEX my_index ON tablename；
或者
ALTER TABLE table_name DROP INDEX index_name;
1
2
3

首先，我们谈几种索引的类型：
哈希索引：字面意思，就是哈希表的结构，键是列值，值是该行数据的物理地址。但是由于哈希表是单个查询很快，对于范围查询没有办法，而范围查询是很常见的，所以一般数据库不用哈希索引的结构。

全文索引：这也是一个比较独特的索引。它主要用于text的数据类型，比如如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。

SELECT * FROM table_name MATCH(ft_index) AGAINST('查询字符串');
1

组合索引：也就是多个列组成索引。注意组合索引的列不允许有空值。并且符合最左前缀原则，也就是如果组合A,B,C. 那么A,AB,ABC是符合条件的组合，而B,C等就不是。

接下来重点部分来了，，那就是B树和B+树；
B树实际上相当于一个平衡搜索多叉树，具体可以自己学习，我只说重点部分。B树每个节点有键、数据、指针三部分。每个节点有2到M个孩子。并且，所有叶子结点都在同一层上，所以说是平衡，这也是为了减少搜索时间（logn）。

B+树作为数据库引擎MyISAM和InnoDB使用的索引结构。它和B树的区别在于以下几点：
1、B+树只有叶子结点带有数据域，其余的只有键和指针。
2、B+树每个节点有M个孩子和M个键。（或者M和M+1，这不重要）
3、B+树在叶子结点添加了指向相邻叶子结点的指针。

为什么B+树比B树更适合作为索引呢？
1、因为数据库是存储在磁盘上的，我们读取数据是从磁盘读取到内存中。为了高效，我们通常一页一页地读数据（预读取，存放缓存中），所以要保证一个节点大小尽量和页大小一致（16k）。所以，B+树由于非叶子节点没有数据域，所以能够携带更多的键，所以B+树的层数少，看起来更矮胖一点。那么查询时，B+树所进行的I/O次数更少,因为途中经过每一层，我们都需要进行一次I/O读取一个结点。
2、由于B+树在叶子结点增加了指向相邻叶子结点的指针，当进行区间查询时，只要沿着指针读取就可以，天然具备排序功能。而B树的索引字段大小相邻近的结点可能隔得很远，要想进行区间查询需要不停的进行中序遍历，相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。这也是B+树作为索引的关键。

聚集索引和非聚集索引
首先明确，二者都是B+树结构的，区别在于，聚集索引的叶子结点数据域存储的是那一行数据本身，非聚集索引叶子结点的数据域存储的是那行数据的地址。
由于这一点，得出以下结论:
1、聚簇索引的顺序就是数据的存储的物理顺序，而非聚簇索引的索引顺序与数据物理排列顺序无关，物理顺序在杂乱的堆结构中。进一步，一个表只能有一个聚簇索引，可以有很多个非聚簇索引。因为存储的物理顺序只能有一个。
2、聚簇索引的数据和主键索引存储在一起。由于InnoDB是聚簇索引，所以InnoDB要求数据表必须有主键。聚簇索引都建立在主键上，如果你想不开建立在非主键上，那么会有一个辅助索引帮助找到主键，再到主键的聚簇索引找数据，这样就有两次过程，不好。
3、另外，最好采用自增的主键，1,2，3,4，5… 如果不是这样的话，那么非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效。

看起来好像聚簇索引很好，但是非聚簇索引也有好的一面：
1、由于聚簇索引存储的是数据本身，而非聚簇索引只存储指针，因此聚簇索引会占用更多的空间。
如果对非主键稿聚簇索引，那么还需要维护一个辅助索引，如果主键非常长，那么辅助索引将会非常大，很低效。
2、聚簇索引在增删改的时候比非聚簇索引慢很多，因为插入新数据时需要检测主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完成。
综上，聚簇索引适用于主键，查询快，只要一次就行，而非聚簇索引还要根据指针去找；
但是，聚簇索引占用空间大一点，在非主键用聚簇索引还需要维护辅助索引；
最后，聚簇索引对于更新表影响大一点，维护成本更高（因为是数据的移动,I/O更多），非聚簇索引对于更新表影响小一点。

最后，谈一下索引什么时候该用，什么时候不该用？
适用场景:
1、经常用于查询或排序的列，比如where, order by中的列需要索引，因为B+索引的查询和排序相对于扫描整张表而言是快的。就像用二叉搜索树找某个数字是log(n)复杂度，而普通数据是O(n)复杂度；
2、主键自动会创建索引；
3、在经常用在连接的列上，这些列主要是外键，可以加快连接速度。
4、用于聚合函数的列可以建立索引，为啥，因为B+树的全文搜索很快，只要在根据叶子结点链表搜下去就可以；

不适用场景：
1、经常增删改的列，因为维护索引结构需要时间和空间代价；
2、不怎么查询的列，因为，索引目的就是为了加速查询，如果不查询，索引就没用，而且维护需要代价；
3、数据量少的列。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快–不管有没有使用索引。索引是减少了I/O次数，即在磁盘查询的次数，所以快，在内存中，数据的查询很快，索引意义不大。
4、对于text等很大的数据，不要建立索引，因为聚簇索引中数据太大了，占用空间很大。除非text数据经常用到模糊查询，可以尝试建立全文索引。

总之，索引是在查询性能和修改性能的均衡，因为索引利于查询，不利于修改。