MYSQL-InnoDB-索引

InnoDB存储引擎支持以下索引：

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

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

B+树类似于二叉树，根据键值快速找到数据，B+树的B不是指二叉(binary),而是代表平衡(balance)，因为B+树由平衡二叉树演化来。

B+树索引不能根据一个给定的键值找到具体的行，而是先找到数据行所在的页，然后把页读到内存，再再内存中进行二分查找，找到需要的行数据。InnoDB中每页Page Directory的槽是按照主键的顺序存放，每一行的查询通过对Page Directory进行二分查找得到。

B+树的演变：二叉树->平衡二叉树->B树->B+树。B+树由B树和索引顺序访问方法(ISAM)演化来。

B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树

B+树的插入操作和删除操作

数据库中的B+树索引分为聚集索引和辅助索引，叶子节点存放所有的数据，聚集索引和辅助索引不同的是，叶子节点存放的是否是一整行的信息。

聚集索引的存储并不是物理上连续的，而是逻辑连续的。有两点：一是页通过双向链表连接，页按照主键顺序排序；二是每个页中的记录通过双向链表连接。好处：排序查找和范围查找特别快。

辅助索引(非聚集索引)叶子节点不包含行记录的全部数据。叶子节点除了包含键值(辅助索引B+树的键，不是行记录的主键),还包含相应行数据的聚集索引键。

InnoDB存储引擎的B+树的分裂需要考虑并发、页空间浪费问题。所以InnoDB的页的Page Header有部分信息保存插入的顺序信息。

索引创建和删除

• ALTER TABLE
• CREATE/DROP INDEX
  查看表的索引
• SHOW INDEX

SHOW INDEX字段列表中的Cardinality表示索引中唯一值的数目的估计值。优化器会根据这个值来判断是否使用这个索引。更新索引Cardinality的信息可以使用ANALYZE TABLE命令。建议在一个非高峰时间，对核心表做ANALYZE TABLE曹祖哦，使优化器和索引更好配合工作。

Cardinality原理：采样(默认取索引B+树的8个叶子节点)

Cardinality的触发和参数设置(知道就行)

B+树索引的使用

• 不同应用中B+树索引的使用
  OLTP应用一般只查询一小部分数据，使用索引有效；而OLAP应用中一般查询大量数据，如面共享分析的查询，这时索引意义不大。在OLAP应用中，一般对时间字段索引，这样可以根据时间维度进行数据的筛选。

• 联合索引
  联合索引也是一棵B+树，B+树的键值数量大于等于2,如下图。联合索引因为已经对第二个键值进行了排序，所以ORDER BY可以直接获取数据。
  

• 覆盖索引
  InnoDB存储引擎支持覆盖索引(索引覆盖)，即从辅助索引中就可以获取数据，不需要再查询聚集索引。覆盖索引的另一个好处是统计，对于COUNT(*)优化器会选择使用辅助索引，因为辅助索引远小于聚集索引，可以减少IO操作。对于联合索引(a,b)，根据b字段查询也可能会查询索引(辅助索引),如下图
  。
  
  

• 优化器选择不使用索引的情况
  全表扫描多发生于范围查找、JOIN连接操作等情况。即使可以使用辅助索引进行覆盖索引，如果查询的数据量大时，依然会进行全表扫描。可以使用关键字FORCE INDEX强制使用某个索引，如图。1
  

• 索引提示
  MYSQL支持索引提示(INDEX HINT),即告诉优化器使用哪个索引。使用索引提示的情况有两种：1、优化器错误选择某索引导致SQL语句运行很慢，高版本MYSQL以改进；2、某SQL语句可以选择的索引非常多，优化器选择执行计划开销过大，这时我们通过分析使用哪个索引提示优化器不进行索引选择。一般使用FORCE INDEX而不是USE INDEX。

• Multi-Range Read优化
  MYSQL5.6版本开始支持Multi-Range Read(MRR)优化，目标是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问。MRR可以适用于range，ref，eq_ref类型的查询。
  MRR的三点优化：
  1、查询辅助索引获取主键值列表，然后将列表进行排序，再进行聚集索引查询。
  2、减少缓冲池中页被替换的次数。
  3、批量处理对键值的查询操作。
  对于范围查询，MRR拆分为键值对来进行批量的数据查询，这样拆分过程中可以直接过滤一些不符合查询条件的数据。
  开启MRR,如图：
  
  

• Index Condition Pushdown（ICP）优化
  MYSQL5.6开始支持ICP，不支持ICP时，索引查询时，现根据索引查找记录，再根据WHERE条件过滤记录；使用ICP，MYSQL查询索引同时根据WHERE条件过滤。本质就是将WHERE过滤放在存储引擎层实现。ICP优化支持range、ref、eq_ref、ref_or_null类型的查询，MyISAM和InnoDB存储引擎都支持，使用ICP时，会在EXPLAIN执行计划看到Using index condition提示。
  
  ICP结合MRR,如图：
  

哈希算法

• 哈希表
  哈希表是为了解决遍历算法太慢出现的，旨在以O(1)时间复杂度找到记录
• InnoDB存储引擎的哈希算法
  InnoDB存储引擎中缓冲池中是Page(页)，那怎么样才能快速找到用户需要的页呢？就是利用了哈希算法，可以想象缓冲池有一个数组，当用户要查询某页的时候，根据页的相关信息快速在数组中找到因为页是对应与某一个表空间的，表空间有一个space_id，MYSQL用K=space_id<<20+space_id+offset,K%m(m为略大于2倍的缓冲池也数量的质数)得到一个散列值把从磁盘中读取的页放到数组中，并且用链表法连接散列值相同的页，每页通过chain指针连接起来。其中
  innodb_buffer_pool_size参数指定缓冲池的大小，如果是10M，因为每页大小为16KB，所以可以放640个页，所以缓冲池页页内存的哈希表大小是略大于640x2=1280的质数，即1399,所以m=1399。
• 自适应哈希索引
  每次从辅助索引查询到对应记录的主键,然后还要用主键作为search key去搜索主键B+树才能找到记录，当这种搜索变多了,innoDB引擎会进行优化。
  参考：AHI

全文检索
全文检索是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。MyISAM存储引擎支持全文检索，从InnoDB1.2.x开始InnoDB存储引擎支持全文检索。