Python学习————MySQL索引

MySQL索引：

索引原理：

MySQl索引的目的是为了提高数据查询效率，本质是为了能够通过不断地缩小想要获取的数据范围
以便筛选出最终想要的数据结果，同时把随机的事件变成有顺序的事件，这样我们就可以用同一种
方式来锁定数据。

磁盘IO和预读：

磁盘读取数据是靠机械运动，每次读取数据花费的时间分为 寻道时间，旋转延迟，传输时间三个部分，寻道时
间指的是 磁臂移动到指定磁道所需要的时间， 旋转延迟是指磁盘转速，传输时间指的是从磁盘读出或将数据
写入磁盘的时间（这个时间基本是零点几毫秒）
访问一次磁盘的时间就是一个磁盘IO，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到
内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据时，与其相邻的数据也会很快被
访问到，每一次IO读取的数据我们称之为一页，具体一页的数据量有多少这个和操作系统有关，所以我们在读取
一页内的数据的时候，实际上只发生了一次IO

索引的数据结构：

是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个

磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1

包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘

块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、

60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35

并不真实存在于数据表中。

b+树的查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，

在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短

（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，

发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，

发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的

b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有

索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

b+树性质:

1.索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的

数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，

h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，

如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引

字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据

放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。

当数据项等于1时将会退化成线性表。

2.索引的最左匹配特性：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是

按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较

name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；

但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的

时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张

三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把

名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左

匹配特性。

聚集索引和辅助索引：

在数据库中，B+树的高度一般在2~4层 ，这也就是说查找某个键值的行记录时最多需要2~4次IO，

现在的机械硬盘每秒可以做到至少100次的IO，这也就是说着2~4次IO也不过零点几秒。

在数据苦衷的B+树索引可以分为聚集索引（clustered index）和辅助索引(secondary index)，

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，及高度是

平衡的，叶子节点存放着所有的数据。

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

InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是
按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为
数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都
通过一个双向链表来进行链接。
    
#如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使
用它作为聚簇索引。
    
#如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

#由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多少情况下，查询优化
器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑
顺序，聚集索引能够特别快地访问针对范围值得查询。

聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录

聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

辅助索引：

除了聚集索引其他所以都是辅助索引（Secondary Index，也成为非聚集索引）辅助索引与聚集索引

的区别是：辅助索引的叶子节点不包含行记录的全部数据。

叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用

来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。

由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集

索引键，

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一

个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针

获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定

主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的

行数据所在的页，因此一共需要6次逻辑IO访问才能得到最终的一个数据页。