数据库的索引

一、前言

索引对于数据库的高效运行是至关重要的，在我们谈到优化查询性能的时候一般都是指添加索引。
索引分单列索引和组合索引：

• 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引；
• 组合索引，即一个索引包含多个列。

---

索引也分聚集索引和非聚集索引：

• 聚集索引：索引中键值的逻辑顺序决定了表中相应行的物理顺序；
• 非聚集索引：索引的逻辑顺序与磁盘上行的物理存储顺序不同。

对物理存储顺序的理解可以类比字典中按照拼音查找和按照部首查找。按拼音查找，索引的顺序与实际数据存储的顺序是相同的，即同一拼音的字总是在同一块区域，且b开头的字的页码一定在a开头的字的页码的后面；按部首查找，同一部首的字会散落在不同的页码，没有顺序关系。

聚集索引与非聚集索引
聚集（clustered）索引，也叫聚簇索引。采用的都是BTREE结构，在聚集索引中，树的叶子节点实际存储了数据，即叶子节点是数据节点；而在非聚集索引中，叶子节点中存储的是指向数据存储地址的指针，即叶子节点仍然是索引节点。
聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行 的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上设置聚集索引（物理排序），避免每次查询该列时都进行排序，从而节省成本。

---

当然，索引不是全是好处没有坏处的。索引大大提升了查询速度，但会降低更新表的速度，在对表进行INSERT、UPDATE和DELETE操作时，数据库不单要修改数据，还要修改保存索引文件。索引本身是占用磁盘空间的。

二、索引类型

普通索引

最基本的索引，以下全以MySQL为例，创建语句为：

CREATE INDEX indexName ON tableName(columnName(length)); 

或者修改表：

ALTER table tableName ADD INDEX indexName(columnName)

唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
在普通索引的基础上加一个unique即可：

CREATE UNIQUE INDEX indexName ON tableName(columnName(length)) 

同样，也可以用修改表的语句：

ALTER table tableName ADD UNIQUE indexName (columnName(length))

三、索引操作的一些语句

删除索引

DROP INDEX indexName ON tableName;
ALTER TABLE tableName DROP INDEX indexName;

显示索引信息

SHOW INDEX FROM tableName;

四、索引的数据结构

常见的有两种，Hash索引和Btree索引。

4.1Hash索引

Hash索引只能用于对等比较，例如=、<>、<=>（等价）操作符。由于是一次定位数据，不像树结构的索引需要从根节点到枝节点，最后才能访问到叶节点这样多次IO访问，所以检索效率远高于BTree索引。
但Hash索引的劣势也很明显,
1. 仅仅能满足=、IN、<=>（等价于，比如3.0<=>3）查询，不能使用范围查询；
2. 无法避免排序操作。Hash索引中存放的是经过Hash计算后的哈希值，其大小关系与运算前的值并无关系，原值有顺序关系hash运算后会丢失这种关系；
3. 无法避免全表扫描。Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行比较，并得到相应的结果。
4. 遇到大量Hash值相等的情况后性能并不一定会比BTree高。对于选择性比较低的索引键（即值的可能性很少，比如性别字段），如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据访问，而造成整体性能底下。

4.2Btree索引

这是最常见的索引算法，它不单能用在比较符上，甚至能用于like操作，使用前提是查询条件不以一个通配符（%）开头的常量。
实际上MySQL的Btree索引是B+tree的数据结构，它是一个平衡多叉树。每个叶子节点的高度差不超过1，且同层级之间的节点有指针相互连接。
关于完整的Btree结构，一时半会讲不清楚，现在掌握地也不好，希望之后能另起一个专题专门讲讲数据结构。

五、随便总结点啥

我们在设计数据库表时，一般都会有主键，而在建立主键的时候那个主键就自动拥有了索引，还是唯一索引。
MySQL都是在自增ID上建立主键，另外除了在where条件中会用到索引，在count时也会用到索引，见图：
用explain来查看sql的执行计划，可以看到在count时也能利用到索引。

MySQL还有一种特殊的索引叫做全文索引（FULLTEXT INDEX），它只能用在char、varchar和text的字段上。 旧版本的MySQL只支持MyISAM引擎，当然现在也支持InnoDB了（5.6版本新增特性）。创建和删除全文索引的方式与其他索引并无不同，但使用方式比较奇特。

SELECT * FROM `student` WHERE MATCH(`address`) AGAINST('guangdong')

当然全文索引是不支持中文的，在5.7版本中有全新的文本插件来处理中文检索n-gram parser或者经典的Sphinx检索引擎。