mysql b-tree索引_MySQL - B-Tree索引

MySQL - B-Tree索引

InnoDB使用B+Tree实现其索引。

1B-Tree索引

数据库中B+Tree的高度一般在2-4之间，即查找某一键值的行记录时最多进行2-4次IO。

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节)，指针类型也一般为4或8个字节，也就是说一个页(B+Tree中的一个节点)中大概存储16KB/(8B+8B)=1K个键值(因为是估值，为方便计算，这里的K取值为10 ^ 3)。也就是说一个深度为3的B+Tree索引可以维护10 ^ 3 * 10 ^ 3 * 10 ^ 3 = 10亿 条记录。

B+Tree索引分为聚集索引和非聚集索引。

1.1聚集索引(聚簇索引，clustered index)

聚集索引：索引项的排序方式和表中数据记录排序方式一致的索引。

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

聚集索引的顺序就是数据的物理存储顺序。它会根据聚集索引键的顺序来存储表中的数据，即对表的数据按索引键的顺序进行排序，然后重新存储到磁盘上。因为数据在物理存放时只能有一种排列方式，所以一个表只能有一个聚集索引。

数据页上存放的是完整的每行的记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。

聚集索引的存储并不是物理上连续的，而是逻辑上连续的。

按顺序存放物理数据的成本过高。

数据页及其内部的数据通过双向链表连接，可以物理上不连续。(链表特性)

InnoDB中的聚集索引

如果表定义了主键，则主键就是聚集索引

如果表没有定义主键，则第一个not NULL unique列是聚集索引

否则，InnoDB会创建一个隐藏的row-id作为聚集索引

聚集索引的优点

数据访问更快，因为索引和数据放在同一棵B+Tree中。

对于主键的排序查找和范围查找速度非常快。

聚集索引的缺点

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。

更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

1.2非聚集索引(辅助索引/二级索引，Secondary Index)

建立在聚集索引上的索引。

Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键(主键值)。

辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。

如果在一棵高度为3的辅助索引树中查找数据，那需要对这棵辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问以得到最终的一个数据页。

回表查询：先定位主键，再定位数据，性能较低。覆盖索引可以避免回表查询。

2索引的管理

SHOW INDEX FROM可以查看表中索引的信息。

部分列的含义

Non_Unique：是否是非唯一的索引

Key_Name：索引名字

Seq_in_index：索引中该列的位置，从1开始

Collation：列以什么方式存储在索引中。对于B+Tree索引，该列总是A，即排序的。使用哈希索引时为NULL

Cardinality：Cardinality/n_row_in_table应尽可能接近1

Sub_part：是否为部分索引。整列索引时为NULL

Cardinality

索引的选择性：按性别进行查询时，可取值一般只有M、F。因此SQL语句得到的结果可能是该表50%的数据假如男女比例1 : 1，这时添加B+树索引是完全没有必要的。相反，如果某个字段的取值范围很广，几乎没有重复，属于高选择性。则此时使用B+树的索引是最合适的。例如对于姓名字段，基本上在一个应用中不允许重名的出现。

索引的选择性在数据库中表示为Cardinality/n_row_in_table，该值越接近1，索引的选择性越高。

由于每种存储引擎对B+Tree的索引实现不同，对Cardinality统计时放在存储引擎层进行。

更新策略：Cardinality是一个预估值，不保证准确性。对于更新频繁的索引，数据库不可能每次更新都重新计算该值。且对于规模较大的数据，计算该值花费的时间可能较长。

需要更新Cardinality时，可以使用ANALYZE TABLE命令。

快速索引创建(Fast Index Creation，FIC)

MySQL5.5之前，创建索引时需要创建一张新表并把原表中的数据复制到其中，在这期间，数据库服务不可用。

FIC：对于辅助索引的创建，InnoDB在需要创建索引的引擎上加一个S锁(共享锁)，此时读操作是被允许的(写操作依然不可用)。

对于主键索引的创建和删除，依然需要重建一张表。

3联合索引(复合索引)

联合索引即在一张表中有多个索引。

4覆盖索引(covering index)

从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录，避免了回表查询。

覆盖索引是通过联合索引实现的，其思想很暴力，就是索引即数据。

聚合索引和辅助索引的区别在于，聚合索引的叶节点存放了整行记录，而辅助索引只包含了定义索引的那一列。

注：很容易搞错的一点，辅助索引的叶节点中虽然没有像聚集索引那样包含所有数据，但作为索引，其叶节点一定包含了作为索引的那一列的数据。

对于任一查询，并不总是要获得整行记录，通常只需获取其中几列即可。当需要获取的列作为索引时，其数据已经包含在B+Tree的叶节点中，不需要再到聚集索引中查询(回表)。

例

CREATE TABLE user(

id INT(20) NOT NULL AUTO_increment,

name VARCHAR(25) NOT NULL,

psw VARCHAR(255),

PRIMARY KEY(id),

index idx_name(name)

)ENGINE=InnoDB;

执行SQL语句

SELECT id FROM user WHERE name='sqlboy';

此时，由于id是主键(聚集索引)，name是辅助索引，它们的值都已经包含在索引树(B+Tree)中，不需要再回表查询，即索引覆盖了查询的需求。

执行SQL语句

SELECT psw FROM user WHERE name='crudboy';

此时，psw字段不是索引，根据条件name='crudboy'，走辅助索引name，查询得到符合条件的记录对应的主键，通过回表在聚集索引树上找到满足条件的记录中的psw。

如果psw是使用很频繁的字段，可以将其加入联合索引，避免回表，这就是覆盖索引。

5MRR优化(Multi-Range Read)

5.6新特性。

充分利用了磁盘的特性，随机访问和顺序访问存在着较大的性能差异，前面提到，InnoDB聚集索引的键值在物理上并不连续，只是在逻辑上连续。

InnoDB中MRR的工作方式

将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。

将缓存中的键值根据RowID进行排序。

根据RowID的排序顺序来访问实际的数据文件。

6索引下推(Index Condition Pushdown，ICP)

5.6新特性。

在5.6以前，当进行索引查询时，首先根据索引来查找记录，然后再回表根据WHERE条件来过滤记录。

依然是这张表(主键为id，索引为name，psw)

SELECT * FROM user WHERE name='crud%' and psw=‘123456’;

根据最左前缀匹配原则，name是一个范围，只有name可以用上索引，psw不能走索引，此时根据索引name查询得到聚集索引的主键，回表根据条件psw='123456'来逐个筛选结果。

其实不难发现，psw也是索引，只是对于这个查询，由于最左前缀匹配原则，这个索引没有能发挥作用，但事实上，psw列的数据依然保存在索引树中，而WHERE条件需要的字段正是psw，此时可以在取出索引的阶段立刻过滤不满足psw='123456'的主键。

参考资料：《MySQL技术内幕 InnoDB存储引擎 第2版》

​     《高性能MySQL》

​     《MySQL实战45讲》