本文内容:
- MySQL索引分类
- B+树索引
- 全文索引
- 面试题目
MySQL索引分类
索引是一种为实现快速查询数据的数据结构
- 聚簇索引 与 非聚簇索引:索引中存放数据还是指向数据的指针
- 辅助索引(二级索引):索引中存放主键值,需要再查聚簇索引来拿到完整行记录(回表)
- 唯一索引 与 普通索引:索引值是否唯一
- B+树索引 与 哈希索引:索引使用的存储结构是B+树还是哈希表
- 联合索引:索引中存放多列字段值
- 覆盖索引:索引中包含了所有的查询字段,不需要回表查询
- 最左匹配原则:只有where语句字段与联合字段最左端部分完全匹配,才触发联合索引
- 全文索引:在一篇文档中查询单词出现的位置
自适应hash索引:某个检索条件到某个热点数据页的哈希表
原因:B+树3-4层,hash:O(1),所以如果Innodb认为hash能带来性能提升,就自动为热点查询建立hash索引
B+树索引
概述:B+树是专门为磁盘设计的数据结构,为减小磁盘IO,树设计得更加矮胖,多路平衡N叉树
计算树高:每个key指上的区间是[当前key, 下一个key) ,左闭右开的区间,所以n个节点、k个key的树高为
B+树和B树的区别
B+树 B树 非叶节点 非叶节点不存储数据,页可以存放更多节点,更加矮胖,减少IO次数 非叶节点也存储数据 范围查询 叶子节点间有指针相连,范围查询时可以通过指针减小IO次数 每个值都得查整颗树 稳定性 数据都在叶子节点上,查询稳定 不稳定 存储 数据都在叶子节点上,不需要把整棵树装进内存 B+树和Hash索引的区别
B+树 Hash 精确的等值查询 O() O(1) 排序 支持 不支持 模糊、范围查询 支持 不支持 稳定性 稳定、适合于大多数情况 性能不稳定,大量重复键=哈希碰撞
自增主键的好处
- 避免从业务数据中强行找出一个主键列
- 插入新数据时是在旧页上顺序插入,旧页满则开辟新页,避免频繁的合并、分裂
全文索引
概述:在一个或多个文档中查找单词出现的位置,类似书籍后面的单词索引,
实现方式:倒排索引, 全局遍历一次,记下每个单词出现的位置
实施例:比如下面对这张文档-内容表建的索引
存放单词的信息:(文档ID:出现位置-是文档的第几个单词)
面试题目
- 在使用索引的情况下,会存在使用表级锁的情况吗?
存在,当索引字段重复率过高(如性别字段只有男女),MySQL优化器会选择放弃使用索引,整表扫描(因为走索引最后还得再查表),然后就加了表锁
注:InnoDB行锁是通过索引上的索引项来实现的,不用索引时加锁加的是表级锁,因为此时MySQL必须扫描整张表来处理语句,表的每个记录被锁定,防止其他用户插入到这个表
- 什么时候需要建立索引?
- 经常用作表连接的字段
- 经常排序的字段
- 频繁作为查询的字段
- 范围搜索的字段
- 重复率低的、字段较短
- 什么时候不需要建立索引?
- 数据重复率高时
- 数据较长
- 不常被用于where语句中访问的字段
- 联合索引应用情况分析
满足最左匹配时,>是走范围range索引;
如果查询字段都包含在where里,满足最左匹配+覆盖索引时,or走index,索引遍历,否则走ALL全表遍历
- 索引失效:or, >, !=, 索引字段计算