为什么mysql能支持千万级数据查询

       首先，在设计用户中心系统的数据库时，先创建一张用户基础表user，如下：

同时往表里插入8条数据

然后创建一个联合索引index_age_birth如下：

现在，我们开始分析：为什么MySQL能够支撑千万数据规模的快速查询？

影响MySQL查询性能的因素非常多，比如，索引、optimizer、query cache、lock、各种buffer等等，这些都会影响到MySQL查询的性能，主要分析下索引这个玩意儿，因为它在我们日常的工作中用到的最多。

我们都知道MySQL的索引结构和它的存储引擎密不可分。日常工作中，我们用的最多的存储引擎就是MyISAM和InnoDB，比较少用的还有Memory、Federated、Merge等等。其中，最主流的存储引擎还是InnoDB，所以，详细讲解一下InnoDB引擎在MySQL中的索引结构是什么样的？

索引定义

       索引是用来提高数据库性能的，用于快速找出某个列中有一特定值的行，如果不使用索引，MySQL必须从第1条记录开始读完整个表直到找出相关的行，表越大，花费的时间越多，如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜索数据文件，没有必要看全部数据，这对性能将有极大的提升。

       再用一道数据题来理解：如果表中的一条记录在磁盘上占用 1000字节的话，我们对其中10字节的一个字段建立索引，那么该记录对应的索引块的大小只有10字节。我们知道，MySQL的最小空间分配单元是“页(Page)”，一个页在磁盘上默认占用16K空间，那么这一个页可以存储上述记录16条，但可以存储索引1600条。现在我们要从一个有16000条记录的表中检索符合某个条件的记录，如果没有索引的话，我们可能需要遍历16000条×1000字节/16K字节=1000个页面才能够找到结果。如果在检索字段上有上述索引的话，那么我们可以在16000条×10字节/16K字节=10个页面中就检索到满足条件的索引块，然后根据索引块上的指针逐一找到结果数据块，这样IO访问量要少的多，可见索引对性能的提升作用。

索引结构。

       InnoDB引擎的索引结构主要分为两种：聚簇索引和辅助索引。它们长什么样儿呢？下面就以上面那张用户表为例，来看看这两种索引的样子。

聚簇索引

       聚簇索引在InnoDB中的存储结构是一棵B-Tree。

 

（1）非叶节点：其中，最上层的节点称为根节点，其他节点中，发起指针指向的节点称为父节点，指针指向的节点称为孩子节点。该节点包含了多个<主键id,page_no>元组组成的记录，同时，它自身也有个页号，其中page_no元素有个指针，指向了下面的一个非叶节点或叶子节点，多个元组之间组成的一个单向链表，每个非叶节点之间组成了一个双向链表。

例1：如上图，根节点页1包含两条元组记录<1,2>和<5,3>，自身页号为1，第一个元组记录中的page_no元素指向页2这个节点，第二个元组记录中的page_no元素3指向页3这个节点。

例2：如上图，页2节点包含两条元组记录<1,4>和<3.5>，第一个元组记录中的page_no元素4指向页4这个节点，第二个元组记录中的page_no元素5指向页5这个节点，两个元组之间通过单向指针连接，组成一个单向链表。

例3：页2节点和页3节点组成了一个双向链表。

（2）叶子节点：该节点包含多个<主键id,非主键字段值>元组组成的记录，同时，它自身也有个页号，多个元组之间组成一个单向链表，每个叶子节点之间组成了一个双向链表。

例1：如上图，图中，非主键字段值我只列了两个字段：user_id和user_name,其他用......省略了，省略字段参见最上面部分创建用户表的SQL，以页7节点为例，该节点包含两条元组记录<7,10007,I'm Mike,1,17,2006-07-01>和<8,10008,Henry,1,16,2007-06-07>,这两个元组组成了一个单向链表。

例2：页6和页7两个节点组成一个双向链表。

聚簇索引有个特点：所有节点内的记录按照主键id升序排列。

辅助索引

辅助索引在InnoDB中的存储结构也是一颗B-Tree

（1）非叶节点：其中，最上层的节点称为根节点，其他节点中，发起指针指向的节点称为父节点，指针指向的节点称为孩子节点。该节点包含多个<索引列植，page_no>元组组成的记录，同时，它自身也有个页号，其中page_no元素有个指针，指向了下面的一个非叶节点或叶子节点，多个元组之间组成一个单向链表，每个非叶节点之间组成了一个双向链表。

由于本文开头，我建了一个联合索引index_age_birth，索引列为（age，birthday）,所以，上图B-Tree非叶节点中的元组结构为<age,birthday，page_no>。

例1：如上图，根节点页1中包含两条元组记录<15,2008-02-03,2>和<17,2006-03-03,3>,自身页号为1，第一个元组记录中的page_no元素2指向页2节点，第二个元组记录中的page_no元素3指向页3节点。

例2：如上图，页2节点包含两条元组记录<15,2008-02-03,4>和<16,2007-06-06,5>，第一个元组记录中的page_no元素4指向页4节点，第二个元组记录中的page_no元素5指向页5节点，两个元组之间通过单向指针连接，组成一个单向链表。

例3：页2节点和页3节点组成了一个双向链表。

（2）叶子节点：该节点包含多个<索引列值，主键id>元组组成的记录，同时，它自身也有个页号，多个元组之间组成一个单向链表，每个叶子节点之间组成了一个双向链表。

同样，由于所有index_age_birth的索引为（age,birthday）,所以，上图B-Tree叶子节点中的元组结构为<age,birthday,id>.

例1：如上图，页7节点包含两条元组记录<18,2005-03-05,4>和<25,1998-01-02,1>,这两个元组组成了一个单向链表。

例2：页6和页7两个节点组成一个双向链表。

辅助索引的特点是:所有节点内的记录按照索引列值升序排列，比如：index_age_birth索引，首先，记录按照age升序排列，如果age相同，再按照birthday升序排列。

查找算法

       讲完InnoDB的索引结构，我们通过上面的结构，大概可以推断出为什么MySQL查询一条或多条记录那么快了。

1.无论是聚簇索引还是辅助索引，都是一棵B-Tree，所以，通过二分查找，我们可以快速定位到所要查询的记录。

（1）主键查询：根据id二分搜索聚簇索引，可以快速定位到叶子节点上的记录。

（2）辅助索引列查询：参考本文《索引结构》中辅助索引B-Tree的图，我以查找age=25的记录为例，对该图新增了查询过程的箭头走向，见下图：

我们先看下红色箭头：

之前我讲过了这棵B-Tree的节点内元组记录的结构，在这里为了方便大家快速理解下面的流程，我重新明确一下：这个B-Tree的非叶节点内记录的结构为<age,birthday，page_no>,叶子节点内记录的结构为<age,birthday,id>。

a.页1->页3：由于页1内第二条记录的age元素为17，查询条件age=25,25>17，走右分支，所以，沿着页1指向页3的指针，定位到页3节点。

b.页3->页7：由于页3内第二条记录的age元素为18，查询条件age=25,25>18，走右分支，所以，沿着页3指向页7的指针，定位叶子节点页7。

c.在页7节点中，第二条元组记录中的age元素值为25，满足age=25的查询条件，所以，定位到记录<25,1998-01-02,1>为辅助索引中索要查找的记录。

d.最后，拿主键id=1到聚簇索引进行二分查找，定位到叶子节点上主键id=1的完整记录。之前说了，网上关于聚簇索引查找过程的资料很多，所以，这里就不详细说明了。

2. 由于辅助索引B-Tree上的节点内部的记录升序排列，记录与记录之间组成单向链表，节点之间组成双向链表，所以，我可以通过线性查找（即按列值顺序查找），快速完成一个范围查询。

比如，我现在要执行下面这条SQL：

SELECT * FROM user WHERE age >= 15

见上图绿色箭头：

（1）页1-> 页2：由于页1节点中第一个元组记录的age元素为15，查询条件age>=15,15>=15，所以，沿着页1指向页2的指针，定位到页2节点。

（2）页2-> 页4：由于页2节点中第一个元组记录的age元素为15，查询条件age>=15,同样15>=15，所以，沿着页2指向页4的指针，定位到页4节点。

（3）页4-> 页5-> 页6-> 页7：页4、页5、页6和页7节点之间通过双向指针（正向和逆向）连接组成双向链表，每个节点内部所有记录通过一个单向指针（正向）连接组成单向链表，且所有记录按照索引index_age_birth内列值升序排列，即页4-页7节点内所有记录的age元素一定都大于等于15且升序排列，所以，我们只需从页4内的第一条记录开始遍历其指针连接的所有后续记录，找到这些age>=15的记录的主键id，即1 ~ 8，最后，根据这些主键id去聚簇索引查找相应记录就行了。

综上所述，我们得到了为什么MySQL查询一条或多条记录那么快的原因：

1. 二分查找：过滤了搜索过程中无需遍历的节点

2. 线性查找：无需反复从根节点搜索满足条件的节点记录，而是直接遍历满足叶子节点中满足查询条件的第一条记录的所有后继节点

小结：

我主要讲解了InnoDB的索引结构，包含聚簇索引和辅助索引，两者的共同点：都是B-Tree结构。

两者的区别我用一个小表格来展示一下：

同时，基于B-Tree结构，得出了两种提升查询索引结构性能的算法：二分查找和线性查找。