MYSQL索引

索引的常见模型

• 哈希表：因其无序性，哈希索引做区间查询的速度很慢。因此哈希表适用于只有等值查询的场景（e.g. Memcached及其他一些NoSQL引擎）
• 有序数组：在等值查询和范围查询场景中的性能就都非常优秀，缺点：有序数组索引只适用于静态存储引擎，更新数据的成本太高（因为需要挪动插入/删除记录之后的所有数据）
• 搜索树：二叉搜索树索引为了维持O(log(N))的查询复杂度，需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是O(log(N))。
• 注：实际上大多数数据库存储不使用二叉树。因为索引不止存在内存中，还要写到磁盘上。为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块（树的层数尽量小）。因此多使用“N叉”树。这里，“N叉”树中的“N”取决于数据块的大小。

N叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中。

MYSQL索引基本概念

在MySQL中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索
引的工作方式不一样

InnoDB中，使用了B+树索引模型（B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数），表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered
index）。
非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

基于非主键索引的查询需要多扫描一棵索引树（回表）。因此，我们在应用中应该尽量使用主键查询。

但是主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

• 覆盖索引：在一个查询里面，索引k已经“覆盖了”我们的查询需求，k索引树上可以直接提供查询结果，不需要回表，我们称为覆盖索引。覆盖索引可以减少树的搜索次数，显著提升查询性能，但索引字段的维护是有代价的，因此在建立冗余索引来支持覆盖索引时就需要权衡考虑。
• 前缀索引：B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录。只要满足最左前缀就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。    在建立联合索引的时候，如何安排索引内的字段顺序：因为可以支持最左前缀，所以当已经有了(a,b)这个联合索引后，一般就不需要单独在a上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
• 索引下推优化：在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。（适用于联合索引场景下）

普通索引和唯一索引

查询过程：InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，
并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。因此普通索引和唯一索引在查询过程中的性能差异微乎其微。

change buffer:当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

更新过程：唯一索引的更新就不能使用change buffer，普通索引可以使用。-->普通索引搭配change buffer 可以减少随机读磁盘的次数 提高更新效率。

Q:怎么给字符串字段加索引？

1. 直接创建完整索引，这样可能比较占用空间；
2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
4. 创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支
持范围扫描。

前缀索引分析：

MySQL是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。如果你创建索引的语句不指定前缀长度，那么索引就默认包含整个字符串。

使用前缀索引：

• 优点：索引结构占用的空间会更小
• 缺点：①可能导致查询语句读数据的次数变多；②使用前缀索引就用不上覆盖索引对查询性能的优化

定义好前缀索引长度，就可以做到既节省空间，又不用额外增加太多的查询成本。在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。