MySql索引原理解析马龙组PPT格式课件下载.pptx

1、InnoDB存储引擎（2）非聚集索引（non-clustered index）：MyISAM存储引擎逻辑分类（重点）:（1）普通索引或者单列索引（2）唯一索引（3）主键索引（4）组合索引,磁盘IO与预读,磁盘IO：磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2=4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫

2、秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17=9ms左右，听起来还挺不错的，但要知道一台500-MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。,磁盘IO与预读,预读：考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而且把相邻的数据也都读取到内存缓冲区内，因为局当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页（p

3、age）。部预读性原理告诉我们，具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。,数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B-tree及其变种B+tree。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到

4、索引的本质：索引是数据结构。,什么是索引,索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是独立于表的对象，可以存放在与表不同的表空间（TABLESPACE）中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的I/O操作比对表进行操作要少很多。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。,B+tree索引的结构,7,索引的本质,目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。這里主要介绍使用较为广泛的B+Tree,B+树详解,左边的图中，其中17表示一个磁盘文件的文件名；小红方块表示这个1

5、7文件内容在硬盘中的存储位置；p1表示指向17左子树的指针。我们假设一个盘块刚好只能存储一个结点，那么左图中一个结点就表示一个盘块，其子树指针就是指向另一个盘块的地址。现在我们来模拟查找文件29的过程：根据根结点指针找到文件目录的根磁盘块1，将其中的信息导入内存。【磁盘IO操作 1次】此时内存中有两个文件名17、35和三个存储其他磁盘页面地址的数据。,B+树查找过程,根据算法我们发现：172935，因此我们找到指针p2。根据p2指针，我们定位到磁盘块3，并将其中的信息导入内存。【磁盘IO操作 2次】此时内存中有两个文件名26，30和三个存储其他磁盘页面地址的数据。根据算法我们发现：262930

6、，因此我们找到指针p2。根据p2指针，我们定位到磁盘块8，并将其中的信息导入内存。【磁盘IO操作 3次】此时内存中有两个文件名28，29。根据算法我们查找到文件名29，并定位了该文件内存的磁盘地址。分析上面的过程，发现需要3次磁盘IO操作和3次内存查找操作。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。,B+树性质,1.通过上面的分析，我们知道IO次数取决于b+树的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=（m+1）N，

7、当数据量N一定的情况下，m越大，h越小；而m=磁盘块的大小/数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。,B+树性质,2.当b+树的数据项是复合的数据结构，比如：（name,age,sex）的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当（张三,20,F）这样的数据来检

8、索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当（20,F）这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当（张三,F）这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。,带有顺序访问指针的B+Tree,一般在数据库系统或文件系统中使用的B+Tree结构都在

9、经典B+Tree的基础上进行了优化，增加了顺序访问指针。,在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如上图中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。,B+tree原理解析,一棵 m 阶B+树可以定义如下：树中每个非叶结点最多有 m 棵子树,m个关键字；根结点（非叶结点）至少有 2 棵子树。除根结点外,其它的非叶结点至少有|m/2|棵子树；有 n 棵子树的非叶结点有 n 个关键码。所有叶结点都处

10、于同一层次上，包含了全部关键码及指向相应数据对象存放地址的指针，且叶结点本身按关键码从小到大顺序链接；若设结点可容纳最大关键码数为 x，则指向对象的地址指针也有x个。结点中的子树棵数 n 应满足 n 属于m/2,m若根结点同时又是叶结点，则结点格式同叶结点。,B+tree原理解析,所有的非叶结点可以看成是索引部分，非叶子结点的子树指针Pi，指向关键字值属于Ki,Ki+1）的子树（B-树是开区间），构成对子树（即下一层索引块）的索引项 特别地，子树指针 P0 所指子树上所有关键码均小于 K1。结点格式同B树。叶结点中存放的是对实际数据对象的索引。在B+树中有两个头指针：一个指向B+树的根结点，一

11、个指向关键码最小的叶结点。所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。b+tree的下一层节点中，包含上层节点里所有的key,存储引擎简介,在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，主流存储引擎有:MyISAM和InnoDB两个存储引擎。在MySQL中，InnoDB引擎表是（聚集）索引组织表（clustered index organize table），支持事务处理，聚集索引（clustered index）；而MyISAM引擎表则是堆组织表（heap organize table

12、），不支持事务处理高级特性，非聚集索引。（non-clustered index）。聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序，而非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。,我们来看看两种存储形式的不同之处：简单说，IOT表里数据物理存储顺序和主键索引的顺序一致，所以如果新增数据是离散的，会导致数据块趋于离散，而不是趋于顺序。而HOT表数据写入的顺序是按写入时间顺序存储的。IOT表相比HOT表的优势是：范围查询效率更高;数据频繁更新（聚集索引本身不更新）时，更不容易产生碎片;特别适合有一小部分热点数据频繁读写的场景;

13、通过主键访问数据时快速可达;,IOT表的不足则有：数据变化如果是离散为主的话，那么效率会比HOT表差;HOT表的不足有：大部分数据读取时随机的，无法保证被顺序读取，开销大;一般来说：当你的数据库有大量的写入、更新操作而查询比较少或者数据完整性要求比较高的时候就选择mysiam表。当你的数据库主要以查询为主，相比较而言更新和写入比较少，并且业务方面数据完整性要求不那么严格，就选择innoDb表。,20,假如我们创建了一个 user表：CREATE TABLE user（id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR（50）NOT NULL,age I

14、NT,addr VARCHAR（200）,image MEDIUMBLOB（2000）;,索引的优点,我们随机向里面插入了10000000条记录，其中有五条：id，name，age,addr，image 2000001，陈诗华，20，广西，帅 4000002，李佳，20，北京，帅 6000003，刘伟杰，20，天津，帅 8000004，周文兵，20，上海，帅 10000005，孔敢，20，深圳，帅 下面我们将给大家演示一下如何利用索引从数据库中快速找到这几条数据！,索引的类型一：逻辑分类,索引可分普通索引，唯一索引，主键索引和组合索引。（1）普通索引CREATE INDEX indexName

15、 ON user（name（length）;如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。（2）唯一索引CREATE UNIQUE INDEX indexName ON user（username（length）唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓（lname）上创建了唯一索引，则任何两个员工都不能同姓。,添加普通索引的效果,没有添加索引的用时,为name添加普通索引后查询的用时,索引的类型,（3）主键索引：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：,主键索引