B+树索引在数据库中有一个特点是高扇出性，因此B+树的高度一般都在2 ~ 4层，也就是说查询某一键值的行记录是最多只需要2到4次IO。机械磁盘每秒至少100次IO，2 ~ 4次IO意味着查询时间只需 0.02 ~ 0.04秒。
B+树索引可以分为聚集索引和辅助索引。不同点：叶子节点存放的是否是一整行的信息。
聚集索引（clustered index）
定义
按照每张表的主键构造一颗B+树，叶子节点存放整张表的行记录数据，叶子节点即数据页。
由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。
优点
- 能在
叶子节点直接找到数据； - 由于定义了
逻辑顺序，所以对于主键的排序查找和范围查找速度非常快。如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点即可得到页的范围，之后直接读取数据页即可。
辅助索引（secondary index）
- 叶子节点
不包含行记录的全部数据。 - 每个叶子节点的索引行包含一个书签（bookmark），即相应行数据的聚集索引键。
- 每张表可由多个辅助索引。
工作原理
- 先搜索辅助索引；
- 根据辅助索引的叶子节点的书签到主键索引上找完整的行记录。
举例：
如果辅助索引高度为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;
主键索引
创建
- 创建一张新的临时表，表结构为新的；
- 把原表数据导入临时表；
- 删除原表；
- 把临时表名改成原表名。
删除
和创建过程一样，需要重建一张表。
注意
因为需要创建临时表，必须保证有足够的空间存放临时表。
辅助索引
创建
-
Fast Index Creation，FIC
创建的时候在表上加一个S锁，不需要重建表，但是由于加锁，创建过程中只能读操作，如果有大量的事务写操作，会导致数据库服务不可用。 -
Online DDL 在线数据定义
允许DDL操作的同时进行DML操作。
删除
更新内部视图，将辅助索引的空间标记为可用，同时删除内部视图上对该表的索引定义。
Cardinality值
表示索引中不重复记录数量的预估值。
是一个预估值，并且不是实时更新的。如果需要更新，使用analyze table;命令，建议在非高峰时间，这能使优化器和索引更好的工作。
Cardinality / n_rows_in_table 应尽可能接近1。如果非常小，需要考虑是否有必要创建这个索引。
网友评论