innodb 04 索引-B+树索引

索引：若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响

索引概述

InnoDB存储引擎支持以下几种常见的索引：
B+树索引
全文索引
哈希索引
InnoDB存储引擎支持的哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。

B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。将其读到内存中，
查找具体某一个记录：是二分查找page dictory。

1、B+ 树索引

B+树索引可以分为聚集索引（clustered inex）和辅助索引，聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

聚集索引：

聚集索引的叶子节点也被称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。
同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
很多文档会说：聚集索引按照顺序物理地存储数据。但是试想一下，如果聚集索引必须按照特定顺序存放物理记录，则维护成本显得非常之高。
所以，聚集索引的存储并不是物理上连续的，而是逻辑上连续的。

辅助索引：

叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签。
如果在一棵高度为3的辅助索引树中查找数据，那需要对这棵辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问

B+树索引的分裂：

B+树索引页的分裂并不总是从页的中间记录开始，这样可能会导致页空间的浪费。

InnoDB存储引擎的Page Header有以下几个部分用来保存插入的顺序信息：
PAGE_LAST_INSERT：最后插入记录的位置。
PAGE_DIRECTION：记录插入的方向。假如新插入的一条记录的主键值比上一条记录的主键值大，我们说这条记录的插入方向是右边，反之则是左边。用来表示最后一条记录插入方向的状态就是PAGE_DIRECTION。
PAGE_N_DIRECTION：假设连续几次插入新记录的方向都是一致的，InnoDBhi把沿着同一个方向插入记录的条数记下来，这个条数就用PAGE_N_DIRECTION这个状态表示。当然，如果最后一条记录的插入方向改变了的话，这个状态的值会被清零重新统计。

通过这些信息：innodb决定向左还是向右进行分裂，同时决定将分裂点记录为哪一个

如果插入随机：取页的中间记录作为分裂点的记录，这和之前介绍的相同
如果非随机：假设页一种有5个位置。如果插入位置是6，那么插入位置是分裂点，直接分裂到下一页。如果插入位置是1，那么3是分裂点，45在下一页。

2、索引的管理

索引的创建和删除可以通过两种方法，一种是ALTER TABLE，另一种是CREATE/DROP INDEX。
索引的查看：show index from tablename
查看结果：表名、是否是唯一索引、索引名字、Cardinality等。
其中Cardinality非常关键的值，表示索引中唯一值的数目的估计值。Cardinality表的行数应尽可能接近1，如果非常小，那么用户需要考虑是否可以删除此索引。

Cardinality值非常关键，优化器会根据这个值来判断是否使用这个索引。但是这个值并不是实时更新的，即并非每次索引的更新都会更新该值，因为这样代价太大了。
如果需要更新索引Cardinality的信息，可以使用ANALYZE TABLE命令。

3、索引的创建和删除

旧innodb创建索引过程：创建新表替换原表。
首先创建一张新的临时表，表结构为通过命令ALTER TABLE新定义的结构。
然后把原表中数据导入到临时表。
接着删除原表。
最后把临时表重名为原来的表名。

后来对于辅助索引:创建的时候对表上S锁。
主键索引的创建和删除仍然需要临时表替换。

通过新的ALTER TABLE语法，用户可以选择索引的创建方式：
ALTER　TABLE tbl_name
|ADD{INDEX|KEY}[index_name]
index_type[index_option]…
ALGORITHM[=]{DEFAULT|INPLACE|COPY}
LOCK[=]{DEFAULT|NONE|SHARED|EXCLUSIVE}
ALGORITHM指定了创建或删除索引的算法。copy是创建临时表，DEFAULT是默认INPLACE。即不创建临时表。
LOCK部分为索引创建或删除时对表添加锁的情况：NONE、SHARE(S)、EXCLUSIVE(X)、DEFAULT(尝试N->S->X)

InnoDB存储引擎实现Online DDL的原理是在执行创建或者删除操作的同时，将INSERT、UPDATE、DELETE这类DML操作日志写入到一个缓存中。待完成索引创建后再将重做应用到表上，以此达到数据的一致性。

4、Cardinality

Cardinality值非常关键，表示索引中不重复记录数量的预估值。
实际应用中，Cardinality/n_rows_in_table应尽可能地接近1。如果非常小，那么用户需要考虑是否还有必要创建这个索引。

数据库对于Cardinality的统计都是通过采样（Sample）的方法来完成的。
采样时机：
表中1/16的数据已发生过变化。
stat_modified_counter＞2 000 000 000。
采样方法：
取得B+树索引中叶子节点的数量，记为A。
随机取得B+树索引中的8个叶子节点。统计每个页不同记录的个数，即为P1，P2，…，P8。
根据采样信息给出Cardinality的预估值：Cardinality=（P1+P2+…+P8）*A/8。

5、联合索引，最左原则。

6、覆盖索引：

InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询的记录，那就将不会再去搜索聚集索引。

7、优化

a:某些情况下，优化器可能并没有选择索引查看数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。（多发于范围查找，join连接的情况下。）
如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是20%左右），优化器会选择通过聚集索引来查找数据。因为之前已经提到过，顺序读要远远快于离散读。

索引提示：显式地告诉优化器使用哪个索引。
使用时机：优化器选择了错误的索引，导致运行缓慢(非常少见)。sql语句可以选择的索引非常多，优化器选择执行计划的开销可能大于sql本身
using index语法：SELECTFROM t USE INDEX(a)WHERE a=1 AND b=2;。USE INDEX只是告诉优化器可以选择该索引，实际上优化器还是会再根据自己的判断进行选择
force index语法：SELECTFROM t FORCE INDEX(a)WHERE a=1 AND b=2;force index指定了要使用哪个索引。

b:MRR优化：
好处：
MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
减少缓冲池中页被替换的次数。
批量处理对键值的查询操作。
过程：
将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
将缓存中的键值根据RowID进行排序。
根据RowID的排序顺序来访问实际的数据文件。
案例：
MRR还可能把某些范围查询拆分为键值对：where 2<a and a<5 and b =3。可能不是范围查询，而是查询[3,3] [4 ,3]。

c: icp优化
当进行索引查询时，首先根据索引来查找记录，然后再根据WHERE条件来过滤记录。在支持Index Condition Pushdown后，MySQL数据库会在取出索引的同时，判断是否可以进行WHERE条件的过滤，也就是将WHERE的部分过滤操作放在了存储引擎层。

在某些查询下，可以大大减少上层SQL层对记录的索取。