面试官：聊一下你对MySQL索引的理解？

青旅半醒 2023-10-06 11:22 24阅读 0赞

![format_png][]

作者：浪人

来源：http://t.cn/AiKmcEef

MySQL索引？这玩意儿还能简单聊？明显是在挖坑，幸好老夫早有准备，切听我一一道来。

#### 一、索引是什么? ####

索引是帮助MySQL高效获取数据的数据结构。

####     ####

#### 二、索引能干什么? ####

索引非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引能够轻易将查询性能提高好几个数量级，总的来说就是可以明显的提高查询效率。

####     ####

#### 三、索引的分类? ####

1、从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。这里所描述的是索引存储时保存的形式，

2、从应用层次来分：普通索引，唯一索引，复合索引

3、根据中数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引。

平时讲的索引类型一般是指在应用层次的划分。

就像手机分类：安卓手机，IOS手机 与 华为手机，苹果手机，OPPO手机一样。

**普通索引****：**即一个索引只包含单个列，一个表可以有多个单列索引

**唯一索引：**索引列的值必须唯一，但允许有空值

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

**聚簇索引(聚集索引)：**并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。

**非聚簇索引：**不是聚簇索引，就是非聚簇索引

#### 四、索引的底层实现 ####

mysql默认存储引擎innodb只显式支持B-Tree( 从技术上来说是B+Tree)索引，对于频繁访问的表，innodb会透明建立自适应hash索引，即在B树索引基础上建立hash索引，可以显著提高查找效率，对于客户端是透明的，不可控制的，隐式的。

> 不谈存储引擎，只讨论实现(抽象)

##### Hash索引 #####

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

![format_png 1][]

##### B-Tree索引（MySQL使用B+Tree） #####

B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。

![format_png 2][]

B+Tree索引

是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

![format_png 3][]

> 结合存储引擎来讨论（一般默认使用B+Tree）

案例：假设有一张学生表，id为主键

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>birthday</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Tom</td> 
   <td>1996-01-01</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Jann</td> 
   <td>1996-01-04</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Ray</td> 
   <td>1996-01-08</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Michael</td> 
   <td>1996-01-10</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>Jack</td> 
   <td>1996-01-13</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>Steven</td> 
   <td>1996-01-23</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>Lily</td> 
   <td>1996-01-25</td> 
  </tr> 
 </tbody> 
</table>

**在MyISAM引擎中的实现**（二级索引也是这样实现的）

![format_png 4][]

在InnoDB中的实现

![format_png 5][]

![format_png 6][]

####     ####

#### 五、为什么索引结构默认使用B+Tree，而不是Hash，二叉树，红黑树？ ####

B-tree：因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；

Hash：虽然可以快速定位，但是没有顺序，IO复杂度高。

二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树：树的高度随着数据量增加而增加，IO代价高。

####     ####

#### 六、为什么官方建议使用自增长主键作为索引？ ####

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

插入连续的数据：

![format_png 7][]

插入非连续的数据

![format_png 8][]

#### 七、总结 ####

1.  MySQL使用B+Tree作为索引数据结构。
2.  B+Tree在新增数据时，会根据索引指定列的值对旧的B+Tree做调整。
3.  从物理存储结构上说，B-Tree和B+Tree都以页(4K)来划分节点的大小，但是由于B+Tree中中间节点不存储数据，因此B+Tree能够在同样大小的节点中，存储更多的key，提高查找效率。
4.  影响MySQL查找性能的主要还是磁盘IO次数，大部分是磁头移动到指定磁道的时间花费。
5.  MyISAM存储引擎下索引和数据存储是分离的，InnoDB索引和数据存储在一起。
6.  InnoDB存储引擎下索引的实现，(辅助索引)全部是依赖于主索引建立的(辅助索引中叶子结点存储的并不是数据的地址，还是主索引的值，因此，所有依赖于辅助索引的都是先根据辅助索引查到主索引，再根据主索引查数据的地址)。
7.  由于InnoDB索引的特性，因此如果主索引不是自增的(id作主键)，那么每次插入新的数据，都很可能对B+Tree的主索引进行重整，影响性能。因此，尽量以自增id作为InnoDB的主索引。

--------------------

【END】

关注下方二维码，订阅更多精彩内容

![format_png 9][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9Icld3Nlp1WENzaWFIejV5dXVGNFp5NVI4aWFmaGdpYVdHYkF3VnVkeFg4R3Jick5UUDFPcE16NTJmY1JxMWZCd1JqQThSZzFSYm9ZY2tiOFJTNXN2emliTUEvNjQw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy90TzdORU43d2pyNGI0ZFhWRnFvVXloN2NHOUNQVVNOOXBpYXVwME8xMjVoc2FLQTJVRUVtSVFEaWE2d2VGYk5CemhmM2VpYlB6cTBIVnZ0MWNpYTNIUkJYcHcvNjQw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy90TzdORU43d2pyNGI0ZFhWRnFvVXloN2NHOUNQVVNOOWxIbFBTSVB0ZThadzFib0xpY3dWM2ZlQlZlWG1FNndaeHRtZ2ZJcnV6MWttbFhiTUl0aWJpYXlZQS82NDA?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy90TzdORU43d2pyNGI0ZFhWRnFvVXloN2NHOUNQVVNOOTZQcjk2WktlQndwaWFpYzhsS1NvaWFycERpYm5iTGJiOXlUV3lmb3VPaWFQSUkwMVNnODI5Mm9nM2dRLzY0MA?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy90TzdORU43d2pyNGI0ZFhWRnFvVXloN2NHOUNQVVNOOVJpY0R0RzBCSTZnOEFZNHlSa1l5WklTQjg5akFDeWlhOXU3MzlsM3FVSlRHb0RYbkRoSWFncnZ3LzY0MA?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy90TzdORU43d2pyNGI0ZFhWRnFvVXloN2NHOUNQVVNOOUpIanUwZ0xhY2h6S1ZGcXNzVmlhZjlhdzh1eDlMaFJOcTZCWGRhdFVhTmljUmljTEFiMkd4cGljNVEvNjQw?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy90TzdORU43d2pyNGI0ZFhWRnFvVXloN2NHOUNQVVNOOWlhVTg2cTR0QjNDMnlYYTlVQmxKbzhNdFZBUmlieUFmbENQZnhEVWE4YWJMNE04N2MwMjVhMjZnLzY0MA?x-oss-process=image/format,png
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi90TzdORU43d2pyNGI0ZFhWRnFvVXloN2NHOUNQVVNOOXIxaGJtdEdUWVcxbmljVDBuRGNINk91N1pkQ1NyT2VtdW94SVVSNTAyaGlhOGliYW9wcUVxSlRRdy82NDA?x-oss-process=image/format,png
[format_png 8]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi90TzdORU43d2pyNGI0ZFhWRnFvVXloN2NHOUNQVVNOOU1iQjlpYWdTcGxPaWMxaWJRcVZMd1NyMEZCTEN6cVpuS1dzaWFUNjU5NWR2QmpYdDF2UnBYTUZpYU5BLzY0MA?x-oss-process=image/format,png
[format_png 9]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9zWnlBSnRpYWVzV2NqajRZNmIyUnNZampPNGNtc0E3OXZKY2RyY080RDZxb2t6V1l6NjQ4S2ZQVFNsSWhCeWVKbGZzQmtLaWFzWHR4a2JSTFRUSUxRcGlidy82NDA?x-oss-process=image/format,png