浅谈InnoDB索引

合集 - MySQL(9)

1.浅谈InnoDB索引2023-04-20

2.聊聊MySQL锁2023-04-203.MySQL数据库事务2023-04-194.MyISAM 和 InnoDB 的区别2023-04-235.Explain执行计划分析2023-04-236.Innodb实现事务流程2023-04-247.前缀索引2023-04-248.MySQL8分析利器Explain analyze2023-04-249.MySQL性能优化2023-04-24

收起

概念

Navicat上基于InnoDB引擎除了主键索引外可以建立四种类型(UNIQUE,NORMAL,FULLTEXT,SPATIAL)的索引,可以选择两种索引方法(B+Tree,Hash)
其中B+树索引是目前关系型数据库最常见最有效的索引;B+树索引又可以区分聚集索引和非聚集索引

• 聚集索引是基于表的主键建立B+树索引,树的叶子结点记录了一行记录的所有数据,查找时只要找到了这条记录就找到了这条记录的所有数据
• 非聚集索引是基于其他索引字段建立的索引,叶子结点除了索引字段外只会存储表的主键,获取非索引字段需要回表查

主键索引和UNIQUE类型的索引又叫唯一索引,数据库会保证在数据表中根据索引字段能找到唯一的一条数据
FULLTEXT类型的索引,也叫全文索引,只能基于字符相关的字段建立索引
SPATIAL类型的索引叫做空间索引,只能基于空间地理信息相关的字段建立索引

B+Tree VS Hash

• B+树是将索引字段排序后存储,所以在范围查询和结果需要排序的查询占据优势,而Hash是将数据经过哈希函数计算后落到相应的块,所以不太适合范围查询和排序
• B+树数据增减时需要调整索引结构,维护成本比较高,Hash只需计算哈希值找到数据操作即可,维护成本低
• B+树为顺序存储,查询时可以通过磁盘预读提高性能
• B+树节点只需要存键值对,空间利用率高,Hash需要额外存储哈希值和指针,空间利用率会降低

B+Tree的特征和优势

• 平衡树,查找效率高
• 数据存储在叶子结点,范围查询只需遍历叶子结点
• 顺序存储,可以将查询结果快速排序
• 非叶子结点不存储实际数据,节省存储空间
• 非叶子结点用指针连接叶子结点,方便范围查询
• 叶子结点双向链表连接,方便范围查询

B+Tree数据结构演示网站的链接,可以自己亲手操作感受下

索引设计的原则

• 基于查询的频率和效率建立索引
• 选择合适的类型和方法
• 考虑联合索引
• 避免过多的索引
• 建立长度合适的索引