B+树索引在数据库中有一个特点是高扇出性，因此B+树的高度一般都在2 ~ 4层，也就是说查询某一键值的行记录是最多只需要2到4次IO。机械磁盘每秒至少100次IO，2 ~ 4次IO意味着查询时间只需 0.02 ~ 0.04秒。

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

---

聚集索引（clustered index）

定义

按照每张表的主键构造一颗B+树，叶子节点存放整张表的行记录数据，叶子节点即数据页。

由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。

优点

1. 能在叶子节点直接找到数据；
2. 由于定义了逻辑顺序，所以对于主键的排序查找和范围查找速度非常快。如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点即可得到页的范围，之后直接读取数据页即可。

---

辅助索引（secondary index）

• 叶子节点不包含行记录的全部数据。
• 每个叶子节点的索引行包含一个书签（bookmark），即相应行数据的聚集索引键。
• 每张表可由多个辅助索引。

工作原理

1. 先搜索辅助索引；
2. 根据辅助索引的叶子节点的书签到主键索引上找完整的行记录。

举例：
如果辅助索引高度为3，聚集索引高度同样为3；那么需要6次逻辑IO才能得到最终的数据页。

---

索引管理

1. 查看

show index from table_name;

• Table：索引所在表名。
• Non_unique：非唯一索引。
• Key_name：索引的名字，可以通过这个Drop index。
• seq_in index：索引在该列中的位置。
• Column_name：索引列的名称。
• Collation：列以什么方式存储在索引中。可以是A或NULL。B+树索引总是A，即排序的。
• cardinality：表示索引中唯一值的数据的估计值。
• Sub_part：是否是列的部分被索引。如果是整列为NULL，如果只对前100个字符进行索引，显示100。
• Packed：关键字如何被压缩。
• Null：是否索引的列含有NULL值。
• Index_type：索引的类型。
• Comment：注释。

2. 创建和删除

语法

alter table tbl_name add [index|key] [index_name] [index_type](index_col_name,...) [index_option]...

alter table tbl_name drop primary key | drop {index|key} index_name;

create [unique] index index_name [index_type] on tbl_name (index_col_name,...);

drop index index_name on tbl_name;

主键索引

创建

1. 创建一张新的临时表，表结构为新的；
2. 把原表数据导入临时表；
3. 删除原表；
4. 把临时表名改成原表名。

删除

和创建过程一样，需要重建一张表。

注意

因为需要创建临时表，必须保证有足够的空间存放临时表。

辅助索引

创建

1. Fast Index Creation，FIC
   创建的时候在表上加一个S锁，不需要重建表，但是由于加锁，创建过程中只能读操作，如果有大量的事务写操作，会导致数据库服务不可用。

2. Online DDL 在线数据定义
   允许DDL操作的同时进行DML操作。

删除

更新内部视图，将辅助索引的空间标记为可用，同时删除内部视图上对该表的索引定义。

Cardinality值

表示索引中不重复记录数量的预估值。

是一个预估值，并且不是实时更新的。如果需要更新，使用analyze table;命令，建议在非高峰时间，这能使优化器和索引更好的工作。

Cardinality / n_rows_in_table 应尽可能接近1。如果非常小，需要考虑是否有必要创建这个索引。