MySQL索引

1.InnoDB索引在底层采用了哪些数据结构

• B+树索引
• 全文索引
• 哈希索引

InnoDB存储引擎支持的哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能认为干预是否在一张表中生成哈希索引。

B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用和最为有效的索引。
B+树索引的构造类似于二叉树，根据键值快速找到数据。

B+树是为磁盘或者其他直接存取辅助设备设计的一种平衡查找树。
在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点为指针进行连接。

2.B+树的特点有哪些

1. 数据数和叶子节点数相同。
2. 非叶子节点仅用作索引，他的数据和叶子节点有重复元素
3. 叶子节点用指针连接在一起
4. 叶子节点大小从左到右，从小到大。

3.B+树的优点

1. 层级更低，IO次数少
2. 每次都需要查询到叶子节点，查询性能稳定
3. 叶子节点形成有序链表，范围查询方便。

4. 聚集索引和辅助索引

又叫做聚簇索引和非聚簇索引。

B+树索引有又可以分为聚集索引和辅助索引。
他们内部都是B+树的，即高度平衡的，叶子节点存放着所有的数据。
聚集所有与辅助索引不同的是，叶子节点存放的是否一整行的信息。

4.1聚集索引

InnoDB存储表是索引组织表，即表中数据按照主键顺序存放。
聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。
聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。
同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

由于实际的数据页只能按照B+树进行排序，因此每张表只能拥有一个聚集索引。
在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。
此外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。
查询优化器能够快速发现某一段范围的数据页需要扫描。

4.2辅助索引

辅助索引也叫非聚集索引。
叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。
由于InnoDB存储引擎是索引组织表，因此InnDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

因此每张表上有多个辅助索引。

当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后通过主键索引来找到一个完整的行记录。

辅助索引一定会回表查询吗
不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必在进行回表查询了。

5.联合索引

联合索引是指对表上的多个列进行索引。
联合索引也是一颗B+树，索引他的联合key值也是经过排序的。
对于采用（a，b）联合索引，可以利用该索引查询a的值，但是不能查询b的值和（a，c）的值，因为索引b和（a，c）并未排序。

6.覆盖索引

InnoDB存储引擎支持覆盖索引（索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

7.哈希索引

自适应哈希索引采用之前谈论的哈希表的方式来实现。
这仅仅是由数据库自身创建并使用的，我们并不能进行干预。
哈希索引对字典类型的查找非常迅速，但是对于范围查找则无能为力。

8.全文索引

全文索引（Full-Text-Search）是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。
它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。

8.1倒排索引

全文检索通常会使用倒排索引（inverted index）来实现。

倒排索引同B+索引一样，也是一种索引结构。

它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置的映射。这通常利用管联数组来实现。

9Hash索引和B+索引有什么区别或者说优劣势

首先Hash索引和B+树索引的底层实现原理：
Hash索引底层就是hash表，进行查询时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。

B+树底层实现原理就是多路平衡查找树，对于每一次的查询都是从根节点出发，查询到叶子节点方可获得所查键键值，然后查询判断是否需要回表查询。

区别：
hash索引：

1. hash索引进行等值查询更快，但是无法进行范围查询，因为在hash索引中经过hahs函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。
2. hash索引不支持模糊查询已经多列索引的最左前缀匹配，因为hash函数的不可预测，例如：AAAA和AAAAB的索引没有相关性。
3. hash索引任何时候都避免不了回表查询数据
4. hash索引虽然在等值查询上快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，会发生hash碰撞，此时查询效率可能极差。
5. hash索引不支持使用索引进行排序，因为hash函数的不可预测。

B+树

1. B+树的所有节点都遵循（左节点小于父节点，右节点大于父节点，多叉树也类似）自然支持范围查询。
2. 在某些条件下（聚簇索引、覆盖索引等）的时候可以只通过索引完成查询，不需要回表。
3. 查询效率比较稳定，对于查询都是从根节点到叶子节点，且树的高度较低。

结论：
大多数情况下，直接使用B+树可以获得稳定且较好的查询速度，而不需要使用Hash索引。

添加索引时需要注意什么？什么情况下不适合添加索引？

适合

• 在进程需要搜索的列上，可以加快搜索的速度。
• 作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
• 经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
• 在经常使用在where子句的列上创建索引，加快条件的判断速度。

不适合

• 表记录太少
• 经常增删改的表
• 数据重复且分布平均的表字段，因此应该只有最经常查询和经常排序的数据列建立索引。

使用索引一定能够提高查询的性能吗？为什么

通常，通过索引查询数据比全表扫描要快，但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引被修改时，索引本身也会被修改，这意味着每条记录的insert、delete、update将为此多付出4,5次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢，使用索引查询不一定能提高查询的性能。

索引查询适合：

• 基于一个范围的检索，一般查询返回结果集小于表中记录数30%为佳。
• 基于非唯一性索引的检索。

索引就是为了提高性能而存在的，如果在查询中没有提高性能，只能说是用错了索引，或者将是场合不同。

什么是最左前缀索引

MySQL建立多列索引（联合索引）有最左前缀的原则，即最左优先。

如果有一个 2 列的索引 (col1, col2)，则已经对 (col1)、(col1, col2) 上建立了索引；
如果有一个 3 列索引 (col1, col2, col3)，则已经对 (col1)、(col1, col2)、(col1, col2, col3) 上建立了索引；

什么情况下会导致索引失效

1. 违反最左前缀法则
   如果索引有多列，要遵守最左前缀法则。
   即查询从索引的最左前列开始并且不跳过索引中的列。
2. 在索引列上做任何操作
   如计算、函数（手动或自动）类型转换等操作，会导致索引失效从而全表扫描。
3. 索引范围条件右边的列
   索引范围条件右边的索引列会失效
4. 尽量使用覆盖索引
   只访问索引查询（索引列和查询列一致），减少select*
5. 使用不等于（！=、< >）
   MySQL在使用不等于的时候无法使用索引会导致全表扫描，覆盖索引除外。
6. like以通配符开头（’%abc‘）
   索引失效
7. 字符串不加单引号索引失效
   explain select * from user where name = 2000;
8. or 连接
9. order by
10. group by

https://segmentfault.com/a/1190000021464570