当前位置：首页 > news >正文

mysql B+树索引

news 来源：原创 2024/5/15 18:07:07

数据库索引用于提高查询性能和数据访问效率。索引可以加速数据的查找和筛选，减少查询的时间复杂度。数据库索引有很多类型，这里不展开也不比较，只介绍最常见一种索引结构B+树索引。mysql中InnoDB引擎默认使用的就是BTREE索引。

B+树数据结构

B+树是一种常见的用来作为数据库索引的数据结构。下图是一张简单的B+树。
在这里插入图片描述

对照着图片来看下B+树的特点。B+树是一个平衡树（自平衡），所有的节点是有序的。这个特点使得B+树特别适合精确查找或者范围查找。

B+树不同于二叉树，每个节点存储数据数量可以是多个。这样存储数据可以大大降低树的高度，查找具体值时二分查找次数更少，效率更高。

另外B+树所有的数据都存放在最底层叶子节点上，非叶子节点上的数据会重复在叶子节点上存储一份。这样有序输出所有数据只需从左到右遍历所有的叶子节点。

前面两个特点其实是B树的特性，最后一个才是B+树特有的。B+树的插入和删除要保证操作后树依然有序。这可能会导致叶的分裂或合并。下面结合InnoDB引擎来具体看下Btree索引。

聚簇索引（Clustered Index)

聚簇索引的意思就是索引和对应的行数据存储在一块。在InnoDB表中，每个表都有一个聚簇索引来组织存储表数据。索引值有序的构建在索引所有非叶子节点上，然后所有的行数据存储在叶子节点上。

InnoDB优先使用主键索引来作为聚簇索引，如果没有主键，InnoDB会尝试使用第一个not null的唯一索引来作为聚簇索引，如果都没有。InnoDB会自动生成一个隐藏的索引列rowId来作为聚簇索引，这个列的大小大概5、6bytes。

所以说很多时候一张表定义一个自增的主键是非常有必要的，这样聚簇索引易于维护。如果主键是uuid不仅节点数据所占空间大，在插入或更新索引列时，由于不是顺序插入，如插入位置是一个已满的叶子节点，会导致页分裂，会导致行数据变的稀疏且占用更多的空间。

默认情况下B+树的一个索引页的大小是16kb，可以通过innodb_page_size参数来设置索引页的大小。修改该参数必须在数据库实例化之前完成。当向InnoDB聚簇索引中插入新记录时，InnoDB会尝试将页面的1/16留作未来插入和更新索引记录使用。如果按顺序（升序或降序）插入索引记录，则结果的索引页约有15/16已经被使用。如果以随机顺序插入记录，则页面的使用情况在1/2到15/16之间不等。

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| innodb_page_size | 16384 |

辅助索引（Secondary Index）

除了聚簇索引，其它索引都是辅助索引。辅助索引可以是多列联合索引。辅助索引树上除了按照关联列进行按顺序存储外，还存有该行数据的主键值，便与查找到该行数据的所有内容。如果主键值比较长的话，同样的会导致辅助索引所占用空间变大。可见设置合适主键的重要性。

索引的维护

创建和删除索引有两种方式

ALTER TABLE 方式

ALTER TABLE 表名 ADD [KEY|INDEX] 索引名 (列名...)
ALTER TABLE 表名 DROP [KEY|INDEX] 索引名;

CREATE/DROP方式

CREATE [UNIQUE] INDEX 索引名 ON 表名 (列名,...);
DROP INDEX 索引名 ON 表名;

这里主键索引最好在建表语句中就指定好。还有唯一索引在

索引一般不修改，如果要修改索引最好是删除旧的然后再新增。

大小限制

一个表最大允许有64个辅助索引，索引key值最大长度是3072bytes（和row format有一定关系，默认DYNAMIC）。这个长度是字节具体类型要考虑字符集问题。如一个text类型要设置为索引列，需要设置前缀长度

CREATE TABLE test1 (ucode text ,UNIQUE INDEX (ucode(1000)));

如果使用u8字符集，1000前缀长度已经差不多最大了。这里最大长度3072是在默认innodb_page_size 参数是16kb的前提下，如果调整页大小，索引key最大长度也需要等比例缩放(3kb/16kb)。如8kb索引key最大长度就是1536 bytes。

多列联合索引最大允许列16个，超过也会报错。

这里可能会问了，innodb一个页大小16kb，像text，blob这种可变大字段列如果超过了16kb是如何存储的。这里也有特殊处理，如果一行数据小于一个数据页的一半，则整行数据会存储在页内，如果超过了页的一半，可变长度列会被依次摘出存储在额外的空间（off-page）直到剩下行数据大小小于页的一般。这页间接的约束表定义时候不可变列最大长度合计不超过8kb（相对页大小16kb），更详细后面学习行记录存储时候再说，不同的行格式有些差异，这里只知道个大概就行，超长字段会被抽出存储在额外页空间。

索引信息查看

如何查看当前索引使用了多少页？

查看具体某一个索引大小每找到方法，查询一个表上所有索引大小可以使用SHOW TABLE STATUS命令来计算。

mysql> show table status where name='test' \G;
*************************** 1. row ***************************Name: testEngine: InnoDBVersion: 10Row_format: CompactRows: 0Avg_row_length: 0Data_length: 16384
Max_data_length: 0Index_length: 16384Data_free: 0Auto_increment: NULLCreate_time: 2024-01-15 14:32:01Update_time: NULLCheck_time: NULLCollation: utf8_general_ciChecksum: NULLCreate_options:Comment:

这里看到Index_length代表当前索引占用空间大小，除以innodb_page_size每页的大小就是索引占用页大小。

查看表上索引信息

mysql> show index from test \G;
*************************** 1. row ***************************Table: testNon_unique: 0Key_name: ucodeSeq_in_index: 1Column_name: ucodeCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
Index_comment:

Non_unique：是否是非唯一索引

Column_name：索引列名称

Collation：索引列排列规则，A升序，D是降序，null是不排序。Btree索引总是A。

Cardinality：基数趋势的意思，表示索引列上数据不重复数据数量。比如订单ID这种数据该值就会比大，但是订单状态这个值就会很小。Cardinality的值是一个统计数据，不是特别精确。执行ANALYZE TABLE 会更新该值。在一些连表操作上，Cardinality值越大该索引更可能被使用。

Sub_part：表示是否是部分前缀索引，如果使用的整列数据该值为null，如果是前缀索引，该值为前缀索引的前导长度。