Mysql中的索引

索引的常见模型有哈希表、有序数组和搜索树。
哈希表：一种以 KV 存储数据的结构，只适合等值查询，不适合范围查询。
有序数组：只适用于静态存储引擎，涉及到插入的时候比较麻烦。可以参考 Java 中的 ArrayList。
搜索树：按照数据结构中的二叉树来存储数据，不过此时是 N 叉树（B+树）。广泛应用在存储引擎层中。

B+树比 B 树优势在于：

1. B+ 树非叶子节点存储的只是索引，可以存储的更多。B+树比 B 树更加矮胖，IO 次数更少。
2. B+ 树叶子节点前后管理，更加方便范围查询。同时结果都在叶子节点，查询效率稳定。
3. B+树中更有利于对数据扫描，可以避免 B 树的回溯扫描

索引的优点：

1. 唯一索引可以保证每一行数据的唯一性 ；
2. 提高查询速度 ；
3. 加速表与表的连接 ；
4. 显著的减少查询中分组和排序的时间；
5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点：

1. 创建跟维护都需要耗时 ；
2. 创建索引时，需要对表加锁，在锁表的同时，可能会影响到其他的数据操作 ；
3. 索引需要磁盘的空间进行存储，磁盘占用也很快；
4. 当对表中的数据进行 CRUD 的时，也会触发索引的维护，而维护索引需要时间，可能会降低数据操作性能。

索引的设计原则：
索引不是越多越好。索引太多，维护索引需要时间跟空间；
频繁更新的数据，不宜建索引；
数据量小的表没必要建立索引。

重复率小的列建议生成索引。因为重复数据少，索引树查询更有效率，等价基数越大越好；
数据具有唯一性，建议生成唯一性索引。在数据库的层面，保证数据正确性 ；
频繁 group by、order by 的列建议生成索引。可以大幅提高分组和排序效率 ；
经常用于查询条件的字段建议生成索引。通过索引查询，速度更快。

索引失效的场景：

1. 模糊搜索：左模糊或全模糊都会导致索引失效，比如‘%a‘和‘%a%‘。但是右模糊是可以利用索引的，比如‘a%‘ 。
2. 隐式类型转换：比如 select * from t where name = xxx , name 是字符串类型，但是没有加引号，所以是由 MySQL 隐式转换的，所以会让索引失效 3、当语句中带有 or的时候：比如 select * from t where name=‘sw’ or age=14
3. 不符合联合索引的最左前缀匹配：(A,B,C)的联合索引，你只 where 了 C 或 B 或只有 B,C

索引的基本概念
主键索引：主键索引的叶子节点存的是整行数据信息。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。主键自增是无法保证完全自增的哦，遇到唯一键冲突、事务回滚等都可能导致不连续。
唯一索引：以唯一列生成的索引，该列不允许有重复值，但允许有空值(NULL)
普通索引跟唯一索引查询性能：InnoDB 的数据是按数据页为单位来读写的，默认每页 16KB，因此这两种索引查询数据性能差别微乎其微。
change buffer：普通索引用在更新过程的加速，更新的字段如果在缓存中，如果是普通索引则直接更新即可。如果是唯一索引需要将所有数据读入内存来确保不违背唯一性，所以尽量用普通索引。
非主键索引：非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）
回表：先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。
覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。
联合索引：相对单列索引，组合索引是用多个列组合构建的索引，一次性最多联合 16 个。
最左前缀原则：对多个字段同时建立的组合索引(有顺序，ABC，ACB 是完全不同的两种联合索引) 以联合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc 三个索引。另外组合索引实际还是一个索引，并非真的创建了多个索引，只是产生的效果等价于产生多个索引。
索引下推：MySQL 5.6 引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。
索引维护：B+树为了维护索引有序性涉及到页分裂跟页合并。增删数据时需考虑页空间利用率。
自增主键：一般会建立与业务无关的自增主键，不会触发叶子节点分裂。
延迟关联：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据。
InnoDB 存储： .frm 文件是一份定义文件，也就是定义数据库表是一张怎么样的表。.ibd 文件则是该表的索引，数据存储文件，既该表的所有索引树，所有行记录数据都存储在该文件中。
MyISAM 存储： .frm 文件是一份定义文件，也就是定义数据库表是一张怎么样的表。 .MYD 文件是 MyISAM 存储引擎表的所有行数据的文件。* .MYI 文件存放的是 MyISAM 存储引擎表的索引相关数据的文件。MyISAM 引擎下，表数据和表索引数据是分开存储的。
MyISAM 查询：在 MyISAM 下，主键索引和辅助键索引都属于非聚簇索引。查询不管是走主键索引，还是非主键索引，在叶子结点得到的都是目的数据的地址，还需要通过该地址，才能在数据文件中找到目的数据。
PS：InnoDB 支持聚簇索引，MyISAM 不支持聚簇索引。

Mysql中的索引

原文：https://www.cnblogs.com/z-dk/p/14231098.html