数据库 索引类型及其区别

逻辑上:

Single column 单行索引

Concatenated 多行索引

Unique 唯一索引

NonUnique 非唯一索引

Function-based函数索引

Domain 域索引

物理上:

Partitioned 分区索引

NonPartitioned 非分区索引

B-tree:

Normal 正常型B树

Rever Key 反转型B树

Bitmap 位图索引

索引结构:

B-tree:适合与大量的增、删、改(OLTP);

不能用包含OR操作符的查询;

适合高基数的列(唯一值多)

典型的树状结构;

每个结点都是数据块;

大多都是物理上一层、两层或三层不定,逻辑上三层;

叶子块数据是排序的,从左向右递增;

在分支块和根块中放的是索引的范围;

Bitmap:

适合与决策支持系统;

做UPDATE代价非常高;

非常适合OR操作符的查询;

基数比较少的时候才能建位图索引;

树型结构:索引头

开始ROWID,结束ROWID(先列出索引的最大范围)

BITMAP

每一个BIT对应着一个ROWID,它的值是1还是0,如果是1,表示着BIT对应的ROWID有值;

B*tree索引的话通常在访问小数据量的情况下比较适用,比如你访问不超过表中数据的5%,当然这只是个相对的比率,适用于一般的情况。bitmap的话在数据仓库中使用较多,用于低基数列,比如性别之类重复值很多的字段,基数越小越好。

### 数据库中常见索引类型及其适用场景 #### B-tree 索引 B-tree 是一种平衡树结构,广泛应用于关系型数据库中。它能够高效地支持范围查询、精确匹配以及排序操作。PostgreSQL 默认创建的索引就是 B-tree 类型[^1]。 优点在于它可以处理等值查询范围查询,并保持良好的性能。例如,在一个商品表中按价格区间筛选产品时,B-tree 索引能显著提升查询速度。 ```sql CREATE INDEX idx_price ON products(price); ``` #### 主键索引 主键索引是一种特殊的唯一索引,通常基于 B-tree 实现。它的主要作用是确保每一行数据具有唯一的标识符。由于 Innodb 表采用索引组织表的形式存储,因此主键实际上决定了物理存储顺序[^2]。如果未显式定义主键,则 MySQL 会自动创建一个隐藏的聚簇索引来管理数据。 ```sql ALTER TABLE users ADD PRIMARY KEY (id); ``` #### Hash 索引 Hash 索引利用哈希函数将键映射到固定大小的空间地址上,适合执行相等条件的查找操作。然而,这种索引无法满足范围查询需求,也不支持部分列匹配。需要注意的是,Hash 索引仅限于某些特定存储引擎(如 Memory),而不被 InnoDB 支持[^4]。 ```sql CREATE HASH INDEX idx_user_id ON sessions(user_id); ``` #### Full-Text 索引 Full-Text 索引专为大规模文本检索设计,允许用户执行复杂的自然语言搜索或布尔模式匹配。该功能特别适用于新闻网站、论坛系统以及其他涉及大量非结构化文字内容的应用场合。 ```sql CREATE FULLTEXT INDEX idx_article_content ON articles(content); SELECT * FROM articles WHERE MATCH(content) AGAINST('database optimization'); ``` #### Spatial 索引 Spatial 索引依赖于 R-Tree 结构来加速几何图形对象之间的比较运算。尽管当前仅有 MyISAM 引擎提供对此类索引的支持,但在地理信息系统(GIS)领域仍然占据重要地位。 ```sql CREATE SPATIAL INDEX idx_coordinates ON locations(coordinates); SELECT ST_Distance_Sphere(pointA, pointB) AS distance FROM points; ``` #### Bitmap 索引 Bitmap 索引通过位图数组表示各个值的存在状态,非常适合低基数字段上的频繁过滤操作。不过随着记录数增长,其维护开销也会相应增加[^3]。 --- ### 总结 以上介绍了五种主流数据库索引技术及其典型应用场景。实际开发过程中应综合考虑业务逻辑特性与硬件资源限制等因素合理选用合适的方案。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值