Java 面试题——MySQL 索引基础篇

1、Mysql数据存储在什么地方？

磁盘

2、查询数据比较慢，一般情况下卡在哪里？

IO

3、如果提高 IO 效率？

两个方面：

• 减少 IO 次数
• 减少 IO 量

4、从磁盘中读取数据时，是需要多少用多少吗？

不是

• 局部性原理
  数据和程序都有聚集成群的倾向，同事之前被访问的数据很可能再次被查询，这种聚集可以分为两类：空间局部性 ，时间局部性
• 磁盘预读
  内存和磁盘在发生数据交互的时候，一般情况下有一个最小的逻辑单元，称之为页（dataPage），一般是 4k 或者 8k（具体由操作系统来定），而我们再进行数据交互时，可以取页的整数倍来进行读取，innodb 存储引擎每次读取数据为 16k

5、索引存在哪里？

磁盘，查询数据的时候会优先将索引加载到内存中

6、索引在存储的时候需要哪些信息？需要存储什么字段值？

• key：实际数据行中存储的值
• 文件地址
• offset：偏移量

7、索引数据结构对比分析？

PS：
OLAP：联机分析处理，对海量历史数据进行分析产生决策性的影响，例：数据仓库，Hive
OLTP：联机事物处理，要求在很短的时效内反馈对应的结果，例：关系型数据库，MySQL，Oracle，DB2
显然，索引更适用于 OLTP

首先我们知道，根据上面对索引存储值的分析，数据结构肯定是 K-V 结构的，那么可以对以下数据结构进行分析

哈希表是否合适

不合适
1、哈希冲突会数据散列不均匀，会产生大量的线性查询，比较浪费时间
2、不支持范围查询，当进行范围查询时，必须要挨个遍历
3、Hash 结构必须存储在内存中，对内存空间要求比较高
4、等值查询，那么非常快
在 MySQL中其实是存在 Hash 索引的

• Memory 存储引擎中使用的就是 Hash 索引
• InnoDB 支持自适应 hash，人为无法干预

树是否合适

二叉树、BST 树、AVL 树、红黑树分析

二叉树

• 有且仅有两个分支
• 本身无序
  BST 树
• 在保证二叉树特性的前提下
• 必须有序，左子树必须小于根节点，右子树必须大于根节点

如果数据插入是递增或递减的顺序时，树形结构会退化成链表，效率降低

AVL 树

• 在保证 BST 树特性的前提下
• 经过左旋或者右旋，使树平衡起来
• 最长子树和最短子树差不能超过 1

为了保持平衡，在插入数据的时候必须进行旋转，牺牲插入性能来弥补查询性能，适用于读请求多于写请求的操作，但是当读写请求几乎平衡时，树的旋转频率会变高从而效率降低

红黑树

• 最长子树不能超过最短子树长度的 2 倍

查询性能和读写性能近似平衡，但是随着数据的增多，树的深度会越深，这就意味着 IO 次数越多，影响数据读取效率

树的旋转演示

B 树、B+树

上面分析了树的深度会随着数据量的增大而变深，这个原因在于以上所有分析的树都是有且仅有两个分支，如果在此基础上再所增加树的分支，那么树的深度就会变浅，将原来有序的二叉树，变成有序的多叉树，就出现了 B 树
B 树通过数据范围划分分支，使用degree 表示每个节点可以存储几条记录

B 树实际上怎么存储数据？

• key，对应数据列的值
• 完整的数据行
• 节点指针

最终引出B+树

• 完整数据行只存储在叶子节点中
• 非叶子节点中不存储数据
• 叶子节点有一条首位相接的链表
• 一般情况下三到四层就足以，超出就需要分库分表了

那怎么样才能尽量保持在三层？
索引占用较少空间，空间占用越少，数据量占用越多，这也就是为什么在设计数据结构时会精确到字段类型使用 int 或者 varchar，并且长度也要精确

什么是存储引擎？

存储引擎表示不同的数据在磁盘的不同组织形式

存储引擎	表结构	数据	索引
InnoDB	frm 后缀文件	idb 后缀文件	idb 后缀文件
MyISAM	frm 后缀文件	MYD 后缀文件	MYI 后缀文件

聚簇索引和非聚簇索引区别

是否是聚簇索引取决于数据和索引是否存放在一起

• InnoDB 只能有一个聚簇索引，但是有很多非聚簇索引，向 InnoDB 中插入数据的时候必须要包含一个 key 值。这个索引可以是主键，如果没有主键，就是唯一键，如果没有唯一键，则会自动生成一个 6 字节的 rowid，这个 rowid 对用户是不可见的
• MyISAM 非聚簇索引
• InnoDB 中叶子节点存放的是数据，MyISAM 中叶子节点存放的是实际数据行地址

InnoDB 和 MyISAM 的区别有哪些？

方面	InnoDB	MyISAM
索引存储结构	B+树	Hash 表
索引存放位置	磁盘	内存
锁	表锁、行锁	表锁
外键支持	是	否
事物支持	是	否

为什么 InnoDB 中只有一个聚簇索引？

多个聚簇索引如果叶子节点都存放数据会造成数据冗余，且浪费空间
在 InnoDB 存储引擎中，新建的其他索引叶子节点存放的是数据所在的索引的 key 的值，比如：主键索引已存在 为自增列，再创建一个 name 列为普通索引，name索引的叶子节点存放应该是对应行的自增的值

Mysql 中有几个索引？

至少一个

索引分哪几类？

• 主键索引——主键
• 唯一索引——唯一键
• 普通索引——出去上面两个其他列所建的索引
• 组合索引——多个列组成的索引
• 全文索引——大文本索引

名词解释

假设有一张表person，列有 id，name，age，gender 四个字段，id 是主键，name 是索引列

什么是回表？

select * from person where name = 'zhangsan'; 

先根据 name 查询 id，再根据 id 查询整行记录，走了2 颗 B+树，这种现象叫做回表

当根据普通索引查询到聚簇索引的 key 值之后，再根据 key 值在聚簇索引中获取所有行记录的操作叫做回表，可以理解为除非指定查询普通索引字段，否则最终数据的查询都要回到聚簇索引中来找表数据

什么是索引覆盖？

select id, name from person where name = 'zhangsan'; 

根据 name 可以直接查询到 id，name 两个列的值，直接返回即可，不需要从聚簇索引查询任何数据，这种现象叫做索引覆盖，由此可见索引覆盖比回表效率更高

假设有一张表person，列有 id，name，age，gender 四个字段，id 是主键，name 和 age 是组合索引

什么是最左匹配？

组合索引在使用的时候是先匹配 name，在匹配 age（也就是说，是从最左边往最右边依次匹配每个索引），那么可以延伸出一下四种 sql 语句

-- 走 name 列，age 列组合索引
select * from table where name = ? and age = ? 
-- 走 name 列索引
select * from table where name = ? 
-- mysql 优化器会将 age 和 name 列调换顺序，所以会走 name 列和 age 列组合索引
select * from table where age = ? and name = ? 
-- 不走索引
select * from table where age = ? 

组合索引的存储结构是什么样的？

跟单个索引是一样的，不过在计算位置分布时需要从左到右比较每列的数值大小在确定位置

什么是索引下推？

select * from table where name = ? and age = ? 

观察这条 sql 的执行情况：
没有索引下推之前，先根据 name 条件从存储引擎中获取符合条件的数据，然后在 server 层根据 age 条件对数据进行过滤

有索引下推之后：根据 name 条件和 age 条件从存储引擎中获取符合条件的数据

情况二就是索引下推
可以理解为，对于组合索引的处理，将索引从服务层下方到存储引擎进行处理，这样做减少了 Server 层和存储引擎层的交互次数，从而减少了 IO 量

更多内容后续会陆续奉上，敬请期待～，记得点赞关注哦！