MySQL 索引

索引是存储引擎用于快速找到记录的一种数据结构

一、索引类型

索引是在存储引擎层实现的，不同的存储引擎的索引工作方式并不一样。

我们先来看看 MySQL 支持的索引类型

• B-Tree 索引
  大多数 MySQL 存储引擎都支持该索引，不过，不同的存储引擎可能使用不同的存储结构，如：InnoDB 使用的是 B+Tree

B- Tree 通常意味着所有值都是按顺序存储的，并且每一个叶子页到根的距离相同，关于 B 树不了解的同学，可以先到这个博客了解下
关于索引的B tree B-tree B+tree B*tree 详解结构图 - football0的博客 - CSDN博客

• 哈希索引
  哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每行数据，存储引擎会对所有的索引列计算出一哈希码，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
  MySQL 中，只有 Memory 引擎支持哈希索引。如果有多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

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

• 全文索引
  它查找的是文本中的关键词，而不失直接比较索引中的值。全文索引适用于 MATCH AGAINST 操作，而不是 WHERE 操作。

二、索引的优点

• 索引大大减少了服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机 I/O 变成顺序 I/O

三、高性能的索引策略

3.1 独立的列
始终要将索引列单独凡在比较符号的一侧
如： select id form user where id+1=6;
这时索引无法生效

3.2 前缀索引和索引选择性
长字符串索引，会让索引变得大且慢，这时可以索引开始的部分字符，但这样会降低索引的选择性，
索引的选择性指，不重复的索引值和数据表的记录数的记录总数的比值，索引的选择性越高则查询效率越高
选择足够长的前缀可以保证选择性，但又不能太长，能做到前缀的选择性接近完整列的选择性是最好的

3.3 多列索引
MySQL 查询能够同时使用两个（多个）单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR 条件的联合（union），AND 条件的相交（intersection），组合前两种情况的联合及相交。

索引合并策略是一个优化结果，但这也说明索引建的很糟糕
那如何进行优化呢？

• 当出现多个索引做相交操作时（多个 AND 操作），需要一个包含所有相关列的多列索引，而不是多个独立的单列索引
• 当需要多个索引做联合操作时（多个 OR 操作），通常要耗费大量 CPU 和内存资源在算法的缓存、排序和合并操作上
• 优化器不会把这些计算到 “查询成本” ，导致执行计划还不如直接走全表查询

3.4 选择合适的索引列顺序
在多列 B-Tree 索引，索引列的顺序从最左列进行排序，其次时第二列，所以多列索引的顺序至关重要。
当不需要考虑排序和分组时，将选择性最高的列放在前面通常时很好的。（选择性前面有讲）

3.5 聚簇索引
聚簇索引并不是一种单独的索引类型，而是一种数据的存储方式。InnoDB 的聚簇索引实际上在同一个结构中保存了 B-Tree 索引和数据行。当表有聚餐索引时，它的数据行实际存放在索引的叶子页

下图展示了聚簇索引中的记录时如何存放的

聚簇索引的优点

• 可以把相关数据保存在一起，能快速读取相关数据
• 数据访问更快，聚簇索引将索引和数据保存在同一个 B-Tree 中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点

• 聚簇索引最大限度地提高了 I／O 密集型应用的性能，但如果数据放在内存中，则访问的顺序就没那么重要了
• 插入速度严重依赖插入顺序，如果不是按照主键顺序加载数据，那么在加载完成后最好使用 OPTIMIZE TABLE 命令冲洗组织一下表
• 更新聚簇索引的代价很高，InnoDB 将每个被更新的行移动到新的位置
• 在插入新行，或主键被更新导致需要移动行的时候，可能会有 “ 页分裂 ” 问题。当行主键将这一行已满的页中，存储引擎会将页分裂成两个页面，会导致表占用更多的磁盘空间
• 在行比较稀疏或存储不连续时，会导致全表扫描变慢
• 二级索引（非聚簇索引）可能比想象的更大，因为要在二级索引的叶子节点包含了引用行的主键列
• 二级索引访问需要两次查找索引，而不是一次，因为二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值

3.6 覆盖索引
如果一个索引包含或覆盖所有需要查询的字段的值，称为覆盖索引
优点

如果你想更深入理解索引，推荐你一本书
《Relational Database Index Design and the Optimizers》