索引原理与比较

什么是索引

索引是存储引擎快速找到记录的一种数据结构。在mysql中也称为key键。

为什么需要索引（优点）

索引能加快检索速度，提高查询性能。
1.大大减少服务器扫描的数据行数
2.避免服务器进行排序和分组，也因此避免了创建临时表
3.将随机I/O变成顺序I/O（下面会提到B+树的存储物理顺序和逻辑顺序一致）

索引的实现

目前来说大部分索引的都是由B+树实现的。B+树索引是一种按字段排序的树形数据结构。
InnoDB的B+树索引分为主索引和辅助索引。主索引的叶子节点data域记录着完整的数据记录，根节点和分支结点记录着索引。辅助索引的叶子节点记录着数据的主键值，使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。
（在MySQL数据库的InnoDB存储引擎中，主键索引就是聚簇索引，所有数据都会按照主键索引进行组织；而在MyISAM存储引擎中，就没有聚簇索引了，因为MyISAM存储引擎中的数据不是按索引顺序进行存储的）

1.聚簇索引
聚簇索引规定了表中数据的物理存放顺序，数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚簇索引，可以类比于字典中的按拼音目录。
它的优势在于：查询小数据量的表；查询指定范围内的数据。但聚簇索引不适用于频繁删改的数据，这导致整行移动，影响性能，个人认为类似于数组的删除和插入带来的问题。
由于聚簇索引实质上同时包括了完整数据和索引，而数据不可能存在两个地方，因此一个表的聚簇索引只有一个。
简单理解聚簇索引，可以认为查看已知字的解释时，翻看新华字典就是聚簇索引，因为它本身内容就是按顺序的。比如说我们查询一个“李”字，我们直接会直接翻到拼音Li，相当于索引和内容是在一起的。内容本身就是目录，不需要查看其他目录就能直接找到数据的方式就是聚簇索引。
2.非聚簇索引
当我们不认识一个字，需要根据部首去翻阅目录，根据目录找到内容的方式可以认为是非聚簇索引。目录纯粹目录，正文纯粹正文的方式我们认为是非聚簇索引。

Mysql索引分类和比较

B+树索引

是大多数mysql默认的索引类型。
优点：不需要对数据进行全面扫描，只需要对数搜索就好了，查找速度快一点。除了查找外，B+树索引还可以对数据进行排序和分组。
适用情况：全键值，键值范围和键前缀查找。

哈希索引

哈希索引能以O(1)时间进行查找，毕竟哈希码可以直接找到数据，但是失去了有序性。
优缺点：查找速度更快，但较B+树来说，哈希索引不能完成分组和排序。
适用情况：精确查找。

全文索引

全文索引是比较文中的关键字，而不是像B+树或者哈希索引那样直接比较相等。
全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。
关键词到文件ID的映射，每个关键词都对应着一系列的文件，这些文件中都出现这个关键词。

空间数据索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。

引起索引失效的情况

（1）以“%”开头的LIKE语句，模糊匹配
（2）OR语句前后没有同时使用索引
（3）数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）
（4）带非操作符，如<>/!=/not in/not exist
（5）列上作运算
最后总结一下，MySQL只对以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)，而对于<>，not in是不会使用索引的。