InnoDB存储引擎索引概述

InnoDB 存储引擎索引B+树

     B+树和二叉树、平衡二叉树一样，都是经典的数据结构《B，B+树》。

     B+树索引其本质就是B+树在数据库中的实现，但是B+索引在数据库中有一个特点就是其高扇出性，因此在数据库中，B+树的高度一般都在2～3层，也就是对于查找某一键值的行记录，最多只需要2到3次IO，这倒不错。因为我们知道现在一般的磁盘每秒至少可以做100次IO，2～3次的IO意味着查询时间只需0.02～0.03秒。

对于传统B+树页面50%的数据量分裂操作分析：

1. 分裂之后，两个页面的空间利用率是一样的；如果新的插入是随机在两个页面中挑选进行，那么下一次分裂的操作就会更晚触发；
2. 空间利用率不高：按照传统50%的页面分裂策略，索引页面的空间利用率在50%左右；
3. 分裂频率较大：针对如上所示的递增插入(递减插入)，每新插入两条记录，就会导致最右的叶页面再次发生分裂；

例：针对1，2，3，4，5，6，7，8，9的记录，如果插入10

将拆分成page1[1,2,3,4] , page2[5,6,7,8,9,10],由于是顺序（逆序）插入，page1将造成极大的浪费，再插入2条，Page2又要分裂

B+树索引的分裂优化     

InnoDB存储引擎的 Page Header中有以下几个部分用来保存插入的顺序信息:

• PAGE_LAST_INSERT（最后插入记录的位置）
• PAGE_DIRECTION（最后插入记录的所在页的方向，可能会在左侧页PAGE_LEFT(0x01),右侧页）
• PAGE_N_DIRECTION（一个方向连续插入记录的数量）

通过这些信息, InnoDB存储引擎可以决定是向左还是向右进行分裂,同时决定将分裂点记录为哪一个。若插入是随机的,则取页的中间记录作为分裂点的记录,这和之前介绍的相同。若往同一方向进行插入的记录数量为5,并且目前已经定位( cursor)到的记录( InnoDB存储引擎插入时,首先需要进行定位,定位到的记录为待插人记录的前条记录)之后还有3条记录,则分裂点的记录为定位到的记录后的第三条记录,否则分裂点记录就是待插入的记录。
来看一个向右分裂的例子,并且定位到的记录之后还有3条记录,则分裂点记录如图所示。

图5-17向右分裂且定位到的记录之后还有3条记录, split record为分裂点记录最终向右分裂得到如图5-18所示的情况。

对于图5-19的情况,分裂点就为插入记录本身,向右分裂后仅插入记录本身,这在自增插人时是普遍存在的一种情况

 

聚集索引
InnoDB存储引擎表是索引组织表，即表中数据安装主键顺序存放《InnoDB存储结构》。而聚集索引就是按照每张表的主键构造一颗B+树，并且叶节点存放着整张表的行记录数据，因此也让聚集索引也是索引的一部分。
实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。
在很多情况下，查询优化器非常倾向于采用聚集索引，因为聚集索引能够让我们在索引的叶节点上直接找到数据。 

许多数据库的文档会这样告诉读者：聚集索引按照顺序物理地存储数据。但是试想，如果聚集索引必须按照特定顺序存放物理记录的话，则维护成本即显得非常之高了。所以，聚集索引的存储并不是物理上的连续，相反是逻辑上连续的。这其中有两点：一是我们前面说过的页通过双向链表链接，页按照主键的顺序排列。另一点是每个页中的记录也是通过双向链表进行维护，物理存储上可以同样不按照主键存储。
 

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

叶子节点为什么是 Key+主键，而不是 Key+地址 。虽然 Key+地址速度会快，一旦主聚集索引发生修改就要同步所有的辅助索引。

 

联合索引

联合索引 运用的是多个索引列（对第一排序，再对第二列排序）。 创建方法跟单个索引一样。

 

覆盖索引

InnoDB在1.0之后 或者 MySQL 在5.0或者以下的不支持覆盖索引。 就是从辅助索引中查询的记录，而不需要查询聚集索引中的记录。 好处就是辅助索引不包含整个行记录的所有信息，页面大小远小于聚集索引。因此可以减少大量的IO操作。

例设表t,(OrderID,ProductID)的联合索引

现在对表2种方式查询 select OrderID,ProductID from t 或者 select * from t。

第一句的SQL想要的数据直接在我们的(OrderID,ProductID)的联合索引中可以拿的到，不用去聚集索引再拿。这种情况称为覆盖索引。

 

B+树索引的管理

1.  索引的创建和删除可以通过两种方法，一种是ALTER TABLE，另一种是CREATE/DROP INDEX。
2. 索引可以索引整个列的数据，也可以只索引一个列的开头部分数据，如前面我们创建的表t，b列为varchar(8000)，但是我们可以只索引前100个字段，如：alter table t add key idx_b (b(100));
3. 查看表中索引的信息可以使用SHOW INDEX语句。

我们来分析表t，之前先加一个联合索引，可得：

alter table t add key idx_a_b(a,c);
show index from t;

因为在表t上有3个索引：一个主键索引，c列上的索引，和b列前100个字节构成的索引。

接着我们来具体讲解每个列的含义：

Table：索引所在的表名。
Non_unique：非唯一的索引，可以看到primary key是0，因为必须是唯一的。
Key_name：索引的名称，我们可以通过这个名称来DROP INDEX。
Seq_in_index：索引中该列的位置，如果看联合索引idx_a_b就比较直观了。
Column_name：索引的列
Collation：列以什么方式存储在索引中。可以是'A'或者NULL。B+树索引总是A，即排序的。如果使用了Heap存储引擎，并且建立了Hash索引，这里就会显示NULL了。因为Hash根据Hash桶来存放索引数据，而不是对数据进行排序。
Cardinality：非常关键的值，表示索引中唯一值的数目的估计值。Cardinality/表的行数应尽可能接近1，如果非常小，那么需要考虑是否还需要建这个索引。
Sub_part：是否是列的部分被索引。如果看idx_b这个索引，这里显示100，表示我们只索引b列的前100个字符。如果索引整个列，则该字段为NULL。
Packed：关键字如何被压缩。如果没有被压缩，则为NULL。
Null：是否索引的列含有NULL值。可以看到idx_b这里为Yes。因为我们定义的b列允许NULL值。
Index_type：索引的类型。InnoDB存储引擎只支持B+树索引，所以这里显示的都是BTREE。
Comment：注释。

 

B+树索引的使用

并不是在所有的查询条件下出现的列都需要添加索引。对于什么时候添加B+树索引，我的经验是访问表中很少一部分行时，使用B+树索引才有意义。对于性别字段、地区字段、类型字段，它们可取值的范围很小，即低选择性。
对于性别，可取值的范围只有'M'、'F'。对上述SQL语句得到的结果可能是该表50%的数据（我们假设男女比例1：1），这时添加B+树索引是完全没有必要的。相反，如果某个字段的取值范围很广，几乎没有重复，即高选择性，则此时使用B+树索引是最适合的，例如姓名字段，基本上在一个应用中都不允许重名的出现。

因此，当访问高选择性字段并从表中取出很少一部分行时，对这个字段添加B+树索引是非常有必要的。但是如果出现了访问字段是高选择性的，但是取出的行数据占表中大部分的数据时，这时MySQL数据库就不会使用B+树索引了。

怎样查看索引是否有高选择性？通过SHOW INDEX结果中的列Cardinality来观察。非常关键，表示所以中不重复记录的预估值，需要注意的是Cardinality是一个预估值，而不是一个准确值基本上用户也不可能得到一个准确的值，在实际应用中，Cardinality/n_row_in_table应尽可能的接近1，如果非常小,那用户需要考虑是否还有必要创建这个索引。故在访问高选择性属性的字段并从表中取出很少一部分数据时，对于字段添加B+树索引是非常有必要的。

Cardinality值非常关键，优化器会根据这个值来判断是否使用这个索引。但是这个值并不是实时更新的，并非每次索引的更新都会更新该值，因为这样代价太大了。因此这个值是不太准确的，只是一个大概的值。上面显示的结果主键的Cardinality为2，但是很显然我们表中有4条记录，这个值应该是4。如果需要更新索引Cardinality的信息，可以使用ANALYZE TABLE命令。如：

• analyze table t;
• show index from t;

这时的Cardinality的值就对了