普通索引

覆盖索引

覆盖索引是我们对于mysql建立索引的最终追求

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

Extra*：Using index*

如图，在innodb查询中，只需要通过普通索引就可以得到所查询的数据。无需通过主键索引取查询相应的数据，即为覆盖索引！

如果避免（innodb）回表查询

通过创建联合索引，避免回表查询问题：https://blog.mailjob.net/posts/2656561906.html

联合索引最左匹配原则

创建 (A,B,C)联合索引时，相当于创建了(A)单列索引，(A,B)联合索引以及(A,B,C)联合索引

在 mysql5.6 之前，想要索引生效的话,只能使用 A 和 A,B 和 A,B,C 三种组合。

在 mysql5.6 之后，由于mysql的优化器进行了优化，只要最终的排列顺序符合最左原则即可，那么此处符合覆盖索引条件的有以下索引：

A
A,B
A,B,C
A,C,B
C,B,A
C,A,B
B,A,C
B,C,A

A,C 组合是否用到了索引？

只用到了 A 的索引，C 并没有用到

Hash索引

在MySQL的存储引擎中，MyISAM不支持哈希索引，而InnoDB中的hash索引是存储引擎根据B-Tree索引自建的

hash索引的特点

1、hash索引是基于hash表实现的，只有查询条件精确匹配hash索引中的所有列的时候，才能用到hash索引。

2、对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码。

3、hash索引包括键值、hash码和指针 。

因为hash索引本身只需要存储对应的hash值，所以索引的结构十分紧凑，这也让hash索引查找的速度非常快。然而，hash索引也是存在其限制的：

hash索引的限制

  1. Hash索引必须进行二次查找 使用哈市索引两次查找，第一次找到相应的行，第二次读取数据，但是被频繁访问到的行一般会缓存在内存中，这点对数据库性能的影响不大。
  2. hash索引不能用于外排序hash索引存储的是hash码而不是键值，所以无法用于外排序
  3. hash索引不支持部分索引查找也不支持范围查找只能用到等值查询，不能范围和模糊查询
  4. hash索引中的hash码的计算可能存在hash冲突

当出现hash冲突的时候，存储引擎必须遍历整个链表中的所有行指针，逐行比较，直到找到所有的符合条件的行，若hash冲突很多的话，一些索引的维护代价机会很高，所以说hash索引不适用于选择性很差的列上（重复值很多）。姓名、性别、身份证（合适）

上面说到InnoDB的“自适应hash索引”。就是当InnoDB注意到某些索引值被使用的非常频繁时，它会在内存中基于B-Tree索引上在创建一个hash索引，这样就让B-tree索引也具有hash索引的一些优点。这是一个完全自动的内部的行为，用户无法控制或配置，不过，如果有需要，完全可以关闭该功能。

BTree索引和哈希索引的区别

Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引

Hash索引的弊端

1、Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。

2、Hash索引无法被用来避免数据的排序操作。

3、Hash索引不能利用部分索引键查询。

对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用

4、Hash索引在任何时候都不能避免表扫描。

Hash索引是将索引键通过Hash运算之后，将 Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果

5、Hash索引遇到大量Hash值相等的情况后性能并不一定就会比BTree索引高。

对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下

全文索引

全文索引,通过建立倒排索引,可以极大的提升检索效率,解决判断字段是否包含的问题. 例如: 有title字段,需要查询所有包含 "政府"的记录. 需要 like "%政府%“方式查询,查询速度慢,当查询包含"政府” OR "中国"的需要是,sql难以简单满足.全文索引就可以实现这个功能.

倒排索引（英语：Inverted index），也常被称为反向索引、置入档案或反向档案，是一种索引方法，被用来存储在全文搜索下某个单词在一个文档或者一组文档中的存储位置的映射。它是文档检索系统中最常用的数据结构。

注意

在MySQL 5.6版本以前,只有MyISAM存储引擎支持全文引擎.在5.6版本中,InnoDB加入了对全文索引的支持,但是不支持中文全文索引.在5.7.6版本,MySQL内置了ngram全文解析器,用来支持亚洲语种的分词.

全文索引带来的负面影响：

  1. 占有存储空间更大，如果内存一次装不下全部索引，性能会非常差。

  2. 增删改代价更大，修改文本中10个单词，则要操作维护索引10次，而不是普通索引的一次。

  3. 如果一个列上有全文索引则一定会用上，即使有性能更好的其他索引也不会用上。由于只是存储文档指针，也就用不上索引覆盖。

示例

根据名称创建全文索引：alter table customers1 add fulltext index testfulltext(name) with parser ngram;

select * from customers1 where match(name) against(‘-化实’,in boolean mode) limit 0,5;

索引选择原则

注：字段一般是推荐重复比较少的字段影响到数据的检索，如果是项目需求（可建立联合索引）

  1. 唯一字段可以单独建立单索引，非唯一考虑联合索引,推荐尽量使用唯一字段建立索引
  2. 索引的个数，联合索引的个数 最佳 6个 以内，如果索引因为项目需求：最多 10个
  3. 索引的使用遵循最左匹配原则其次覆盖索引
  4. 尽量选择小的字段建立索引 int ，varchar(10)， char(5)
  5. 避免<，<= ，> ,>= , % ,between 之前的条件。选择索引的字段的范围和模糊之前，因为范围与模糊会引起索引失效，针对于联合索引，就是联合索引的中间尽量不要有范围查询的字段
  6. 尽量多使用explain分析
  7. 避免更新频繁的字段 （二叉树会一直变化，导致性能变慢）
  8. 建立的索引- 优先考虑 建立 联合索引
  9. 索引字段不要有 null， 不是 ‘’