MySQL索引(二)

自适应哈希索引

• 自适应哈希索引是InnoDB引擎的一个特殊功能，当它注意到某些索引值被使用的非常频繁时，会在内
  存中基于B-Tree索引之上再创键一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比
  如快速哈希查找
• 这是一个完全自动的内部行为，用户无法控制或配置，但如果有必要可以关闭该功能。

空间索引

• MyISAM的表支持空间索引，可以用作地理数据存储
• 和B-Tree索引不同，这类索引无需前缀查询。
• 空间索引会从所有维度来索引数据，查询时可以有效地使用任意维度来组合查询。
• 必须使用MySQL的GIS即地理信息系统的相关函数来维护数据
• MySQL对地理信息的支持并不完善，因此大基本不会使用这种索引。

全文索引

通过数值比较、范围过滤等就可以完成绝大多数需要的查询，但如果希望通过关键字匹配进行查询，就
需要基于相似度的查询，而不是精确的数值比较，全文索引就是为这种场景设计的。

• MyISAM的全文索引是一种特殊的B-Tree索引，共有两层
• 第一层是所有关键字，然后对于每一个关键字的第二层，包含的是一组相关的”文档指针”。
• 全文索引不会索引文档对象中的所有词语，它会根据规则过滤掉一些词语

聚簇索引

聚簇索引并不是一种索引类型，而是一种数据存储方式。InnoDB 的聚簇索引实际上在同一个结构中保存
了B-Tree索引和数据行。当表有聚餐索引时，它的行数据实际上存放在索引的叶子页中，因为无法同
时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。聚簇索引也叫主键索引

优点

• 可以把相关数据保存在一起，将数据放在索引树的叶子节点下，找到叶子就可以找到数据
• 数据访问更快，聚簇索引将索引和数据保存在同一个B-Tree中，因此获取数据比非聚簇索引要更快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

  缺点

• 聚簇索引最大限度提高了IO密集型应用的性能，如果数据全部在内存中将会失去优势
• 更新聚簇索引列的代价很高，因为会强制每个被更新的行移动到新位置
• 基于聚簇索引的表插入新行或主键被更新导致行移动时，可能导致页分裂，表会占用更多磁盘空间
• 当行稀疏或由于页分裂导致数据存储不连续时，全表扫描可能很慢。

非聚簇索引

• 非聚簇索引是在索引树的叶子节点上存放数据的地址，找到该地址后，需要到磁盘中查询一次才能获取到数据。
• MyISAM存储引擎的索引方式就是非聚簇索引，只在索引树的叶子节点上存放地址。

区别

• 在InnoDB 里，索引 B+Tree 的叶子节点存储了”整行数据”的是主键索引，也被称之为聚簇索引
• 索引B+Tree的叶子节点只存储了”主键的值和索引列”的是非主键索引，也被称之为非聚簇索引

索引覆盖

覆盖索引指一个索引包含或覆盖了所有需要查询的字段的值，不再需要根据索引回表查询数据。覆盖索
引必须要存储索引列的值，因此MySQL只能使用B-Tree索引做覆盖索引。

优点

• 索引条目通常远小于数据行大小，可以极大减少数据访问量
• 因为索引按照列值顺序存储，所以对于IO密集型防伪查询回避随机从磁盘读取每一行数据的IO少得多
• 由于InnoDB使用.聚簇索引，覆盖索引对InnoDB很有帮助。InnoDB 的二级索引在叶子节点保存了行的主键值，如果二级主键能覆盖查询那么可以避免对主键索引的二次查询。

什么情况下索引失效

• 模糊查询 %like
• 索引列参与计算,使用了函数
• 非最左前缀顺序
• where单列索引对null判断
• where不等于
• or操作有至少一个字段没有索引
• 需要回表的查询结果集过大（超过配置的范围）