按在地上摩擦面试官之mysql索引(精讲)

简介： 

在编码过程中，我们尝尝会遇到一些复杂关系的查询，在这里最容易出问题，尤其是性能方面，因此对查询语句的优化显然是重中之重。说起提高查询性能，就不得不提到索引了。

▶索引定义

 

索引在MySQL中也叫做“键”或者"key"（primary key，unique key，还有一个index key），是存储引擎用于快速找到记录的一种数据结构。 索引相当书的目录，如果要查某一章，如果不使用目录，那么就需要一页一页的找。

▶索引盲区

 

索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。一些开发人员总是在事后才想起添加索引----我一直认为，这源于一种错误的开发模式。如果知道数据的使用，从一开始就应该在需要处添加索引。当然索引也并不是越多越好。

▶索引的影响

 

▪在表中有大量数据的前提下，创建索引速度会很慢

 

▪在索引创建完毕后，对表的查询性能会发幅度提升，但是写性能会降低

 

数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

▶磁盘IO与预读

 

我们都知道磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，目前默认16k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

面试点：MySQL的InnoDB存储引擎索引数据结构？

▶索引的数据结构

 

现在我们来看看索引怎么做到减少IO，加速查询的。我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生（B+树是通过二叉查找树，再由平衡二叉树，B树演化而来）。

🔼

　

 

如上图，是一颗b+树，最上层是树根，中间的是树枝，最下面是叶子节点，关于b+树的定义可以参见B+树，第一层包含数据项6和11，包含指针P1、P2、P3，P1表示小于6的磁盘块，P2表示在6和11之间的磁盘块，P3表示大于11的磁盘块。真实的数据存在于叶子节点即1、3、4、5、6、7、8、9、10、11、13、15、17、18.非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项。

▶b+树的查找过程

 

 

如图所示，如果要查找数据项8，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定9在6和11之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，8小于9，锁定磁盘块3的P1指针，通过指针加载磁盘块7到内存，发生第三次IO，8大于7，锁定磁盘块7的P2指针，指向数据8，发生第四次IO。如果没有索引，每个数据项都要发生一次IO，显然成本非常非常高。除了叶子节点，其他的树根啊树枝啊保存的就是数据的索引，他们是为你建立这种数据之间的关系而存在的。数据是用链表链接。

 

▶聚集索引与辅助索引

 

MyISAM存储引擎在创建表的时候会在硬盘上生成.frm.MYD.MYI结尾的三个文件，frm结尾的是表结构，MYD结尾的是数据文件，MYI结尾的就是索引文件，也就是说索引也是存在硬盘上的。

 

那InnoDB引擎呢，创建一个表，在硬盘上会生成.frm.idb结尾的两个文件。InnoDB引擎的表，它的索引和数据都在同一个文件里面，使用InnoDB存储引擎的时候，每建一个表，就需要给一个主键，是因为这个主键是InnoDB存储引擎的.idb文件来组织存储数据的依据或者说方式，也就是说InnoDB存储引擎在存储数据的时候默认就按照索引的那种树形结构来帮你存。这种索引，我们就称为聚集索引。

 

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

 

聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息

面试点：Innodb一颗b+树能放多少数据？

▶总结

 

磁盘扇区、文件系统、InnoDB存储引擎都有各自的最小存储单元。

在MySQL中我们的InnoDB页的大小默认是16k，当然也可以通过参数设置：

show variables like 'innodb_page_size';

 

数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？假设一行数据的大小是1k，那么一个页可以存放16行这样的数据。

 

这里我们先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数*单个叶子节点记录行数。

 

上文我们已经说明单个叶子节点（页）中的记录数=16K/1K=16。（这里假设一行记录的数据大小为1k，实际上现在很多互联网业务数据记录大小通常就是1K左右）

 

那么现在我们需要计算出非叶子节点能存放多少指针，其实这也很好算，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。那么可以算出一棵高度为2的B+树，能存放1170*16=18720条这样的数据记录。

 

根据同样的原理我们可以算出一个高度为3的B+树可以存放：1170117016=21902400条这样的记录。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

 

大家好，我是山虎，目前在大厂做架构师。喜欢数学，编码，算法，股票，AI。东西不要死记硬背，要做到自己真正的理解。年轻人就要折腾，年轻人就要折腾，年轻人就要折腾。原创不易，帮忙转发。

简介： 

在编码过程中，我们尝尝会遇到一些复杂关系的查询，在这里最容易出问题，尤其是性能方面，因此对查询语句的优化显然是重中之重。说起提高查询性能，就不得不提到索引了。

▶索引定义

 

索引在MySQL中也叫做“键”或者"key"（primary key，unique key，还有一个index key），是存储引擎用于快速找到记录的一种数据结构。 索引相当书的目录，如果要查某一章，如果不使用目录，那么就需要一页一页的找。

▶索引盲区

 

索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。一些开发人员总是在事后才想起添加索引----我一直认为，这源于一种错误的开发模式。如果知道数据的使用，从一开始就应该在需要处添加索引。当然索引也并不是越多越好。

▶索引的影响

 

▪在表中有大量数据的前提下，创建索引速度会很慢

 

▪在索引创建完毕后，对表的查询性能会发幅度提升，但是写性能会降低

 

数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

▶磁盘IO与预读

 

我们都知道磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，目前默认16k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

面试点：MySQL的InnoDB存储引擎索引数据结构？

▶索引的数据结构

 

现在我们来看看索引怎么做到减少IO，加速查询的。我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生（B+树是通过二叉查找树，再由平衡二叉树，B树演化而来）。

🔼

　

 

如上图，是一颗b+树，最上层是树根，中间的是树枝，最下面是叶子节点，关于b+树的定义可以参见B+树，第一层包含数据项6和11，包含指针P1、P2、P3，P1表示小于6的磁盘块，P2表示在6和11之间的磁盘块，P3表示大于11的磁盘块。真实的数据存在于叶子节点即1、3、4、5、6、7、8、9、10、11、13、15、17、18.非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项。

▶b+树的查找过程

 

 

如图所示，如果要查找数据项8，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定9在6和11之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，8小于9，锁定磁盘块3的P1指针，通过指针加载磁盘块7到内存，发生第三次IO，8大于7，锁定磁盘块7的P2指针，指向数据8，发生第四次IO。如果没有索引，每个数据项都要发生一次IO，显然成本非常非常高。除了叶子节点，其他的树根啊树枝啊保存的就是数据的索引，他们是为你建立这种数据之间的关系而存在的。数据是用链表链接。

 

▶聚集索引与辅助索引

 

MyISAM存储引擎在创建表的时候会在硬盘上生成.frm.MYD.MYI结尾的三个文件，frm结尾的是表结构，MYD结尾的是数据文件，MYI结尾的就是索引文件，也就是说索引也是存在硬盘上的。

 

那InnoDB引擎呢，创建一个表，在硬盘上会生成.frm.idb结尾的两个文件。InnoDB引擎的表，它的索引和数据都在同一个文件里面，使用InnoDB存储引擎的时候，每建一个表，就需要给一个主键，是因为这个主键是InnoDB存储引擎的.idb文件来组织存储数据的依据或者说方式，也就是说InnoDB存储引擎在存储数据的时候默认就按照索引的那种树形结构来帮你存。这种索引，我们就称为聚集索引。

 

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

 

聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息

面试点：Innodb一颗b+树能放多少数据？

▶总结

 

磁盘扇区、文件系统、InnoDB存储引擎都有各自的最小存储单元。

在MySQL中我们的InnoDB页的大小默认是16k，当然也可以通过参数设置：

show variables like 'innodb_page_size';

 

数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？假设一行数据的大小是1k，那么一个页可以存放16行这样的数据。

 

这里我们先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数*单个叶子节点记录行数。

 

上文我们已经说明单个叶子节点（页）中的记录数=16K/1K=16。（这里假设一行记录的数据大小为1k，实际上现在很多互联网业务数据记录大小通常就是1K左右）

 

那么现在我们需要计算出非叶子节点能存放多少指针，其实这也很好算，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。那么可以算出一棵高度为2的B+树，能存放1170*16=18720条这样的数据记录。

 

根据同样的原理我们可以算出一个高度为3的B+树可以存放：1170117016=21902400条这样的记录。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

 

大家好，我是山虎，目前在大厂做架构师。喜欢数学，编码，算法，股票，AI。东西不要死记硬背，要做到自己真正的理解。年轻人就要折腾，年轻人就要折腾，年轻人就要折腾。原创不易，帮忙转发。