索引（二）innodb索引

索引（二）innodb索引

B+树索引

innoDB中的B+树索引可以分为两种索引：

• 聚集索引
• 辅助索引

聚集索引和辅助索引的不同点在于叶子节点存放的是否是一整行信息

这两个名字虽然都叫做索引，但是这并不是一种单独的索引类型，而是一种数据存储方式。

聚集索引

• 聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的是整张表的行记录数据，聚集索引的叶子节点
  也称为数据页，索引文件就是数据文件

• 一张表可以建立多个索引，但是聚集索引只有一个

• 对于主键的排序查找和范围查找速度非常快
  
  
  
  上图是InnoDB聚集索引的示意图。InnoDB存储引擎中，表是按照主键顺序组织存放，这种存储方式称为索引组织表

辅助索引

• 辅助索引的叶子节点不包含行记录的全部数据，存储的除了键值以外还包含了一个书签，书签就是相应行数据的聚簇索引键

• 每张表上面可以有多个辅助索引

• 辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
  
  
  

InnoDB的主键选择与插入优化

在使用InnoDB存储引擎时，如果没有特别的需要，请使用一个与业务无关的自增字段作为主键。下面从数据库索引优化角度去分析：

InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：

这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：





此时MySQL不得不为了将新记录插到合适位置而移动数据，会增加很多开销

总结下：
InnoDB引擎下，主键字段不应该过长，因为主键过长会会使所有的辅助索引都变大。且主键应该选择有序序列，否则会频繁做分裂调整B±tree

与MyISAM区别

假设一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

id	name	company
5	Gates	Microsoft
7	Bezos	Amazon
11	Jobs	Apple
14	Ellison	Oracle

---

看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪?

1.由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

2.辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位，后面会涉及）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。

B+树索引的使用

联合索引

联合索引是指对表上的多个列进行索引





联合索引对第二个键值做了排序处理，联合索引（a,b）其实是根据列a,b进行排序，因此下列语句可以直接使用联合索引得到结果：

SELECT ... FROM TABLE WHERE a=xxx ORDER BY b

那么对于联合索引（a,b,c）是怎么样子呢？

覆盖索引

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

• 好处：辅助索引不包含整行的所有信息，故其大小要远小于聚集索引，可以减少大量的IO操作

Q:(a,b)索引，下面的SQL是否可以用到覆盖索引

SELECT COUNT(*) FROM TABLE WHERE b<1000 AND b>0;

索引是不是越多越好？

既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。

• 第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了
• 不建议建索引的情况是该列区分度低，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0

建索引的几大原则

• 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询
  (>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

• =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

• 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

• 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。