MySQL索引 校招知识点总结

本文内容：

1. MySQL索引分类
2. B+树索引
3. 全文索引
4. 面试题目

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, >, !=, 索引字段计算