2021秋招：MySQL索引相关知识

基础知识储备：

1、局部性原理：

（1）空间局部性：发现程序和数据的访问都有聚集成群的倾向，在一个时间段内，仅使用其中一小部分。

（2）时间局部性：最近访问过的程序代码和数据，很快又被访问的可能性很大。

2、磁盘预读：一般比如要读取字符A，不仅会读取字符A，会访问A在内的一块数据。（一般预读的长度为页的数据）

Note：咋许多操作系统中，页大小为4k.主存和磁盘以页为单位交换数据。

一、索引是什么？索引为什么使用了B+树？

首先说下索引是什么：

• 索引是帮助MySQL高效获取数据的数据结构
• 索引存储在文件系统中
• 索引的文件存储形式与存储引擎有关（Innodb的Data和Index文件在一个文件里（idb文件），MyISAM的数据在MYD文件里，Index在MYI文件里）
• 索引文件的结构：hash、二叉树、B树、B+树

那么，底层数据结构为什么用了B＋树:

首先说下其他数据结构的缺点：无论是二叉树还是红黑树，都会因为树的深度过深二造成IO次数变多，影响数据读取的效率。

B+树：

B+树是为磁盘或其他直接存储辅助设备设计的一种平衡查找树。

B+树中，所有记录节点都是按照键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。

二、索引的分类

普通索引：仅加速查询

唯一索引：加速查询+列值唯一（可以由null）

主键索引：加速查询+列值唯一（不可以有null）+表中只有一个

组合索引：多列值组成一个索引，专门用于组合索引，其效率大于索引合并

全文索引：对文本的内容进行分词，进行搜索

三、MySQL是怎么优化table scan（全表扫描）的？

对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引。

避免全表扫描的优化方案:

1、避免在where子句对字段进行null值判断，会导致搜索引擎放弃索引，全表扫描；

2、避免使用 !=或<>操作符；

3、避免用or来连接条件；

4、in 或not in 慎用；

5、避免在where子句中对字段进行表达式操作。

四、某一个表近千万的数据，CRUD比较慢，如何优化？

1、可以做表拆分，减少单表字段数量，优化表结构；

2、在保证主键有效的情况下，检查主键索引的字段顺序，使得查询语句中条件的字段顺序和主键索引的字段顺序保持一致；

3、建立合理的索引；

4、可以结合Redis，Memchache等缓存服务，把复杂的SQL进行拆分，充分利用二级缓存，减少数据库的IO操作。