B+树索引使用的心得

一、B+树

1.1 B+树的特点

InnoDB存储引擎使用的是B+树索引，学完B+树索引后，需要明白B+树具有以下特点：

• 每个索引都对应一棵B+ 树。 B+ 树分为好多层，最下边一层是叶子节点，最上面一层是根节点，其余的是内节点。所有用户记录都存储在B+ 树的叶子节点，所有目录项记录都存储在根节点与内节点。
• InnoDB 存储引擎会自动为主键建立聚簇索引（如果没有主键或不为NULL且被UNIQUE修饰的键，会自动添加主键），聚簇索引的叶子节点包含完整的用户记录。
• 可以对感兴趣的列建立二级索引， 二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
• B+ 树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引最左边的列排序，如果该列值相同，再按照联合索引后边的列排序。
• 通过索引查找记录是从B+ 树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory （页目录），所以在这些页面中的查找非常快。

1.2 索引的代价

B+树索引虽然可以加快查询的速度，但是也会有一定的代价。比如：

• 空间上的代价

每建立一个索引都要为它建立一棵B+树，一棵B+树的每一个节点都是一个数据页，一个页占用16KB 的存储空间，一棵很大的B+ 树由许多数据页组成，所以会占据很大存储空间的。

• 时间上的代价
  • 每次对表中的数据进行增、删、改操作时，为了维护节点和记录的排序，会造成页分裂、页面回收等操作，导致性能变差。
  • 执行查询语句前，会生成一个执行计划，索引太多会导致成本分析时间过长。

二、B+树索引的应用

首先创建一个表：

CREATE TABLE sing_table(
	id INT NOT NULL AUTO_INCREAMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY(id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),