InnoDB索引底层原理剖析

目录

1、索引数据结构
2、InnoDB索引的设计
（1）计算机原理
（2）查找过程
（3）聚族索引与二级索引
（4）创建索引及索引的类型
（5）索引的缺点
（6）外键及作用

---

1、索引数据结构

哈希索引(Hash indexes)采用哈希表来对键值进行查找，时间复杂度为O(1)。1、使用哈希索引时对于键值的等值查询是非常快的，但是其他类型的查询如范围查询、模糊查询、排序等是不能使用哈希索引的。这是哈希索引使用比较少的主要原因。2、在数据量很大的情况下，内存无法加载全部的数据索引。3、Hash 索引遇到大量Hash值相等（hash碰撞）的情况后性能并不一定就会比B-Tree索引高。

二叉搜索树：优点：可以解决大量数据索引无法一次加载进内存中的问题，二叉搜索树可以批量加载数据进内存。缺点：1、检索时间与树的高度有关，树的高度越高，检索次数及时间相对就会越久。2、极端情况下，如果数据本身就是有序的，二叉搜索树会退化成链表，性能会急剧降低。红黑树：红黑树是一种自平衡二叉树，主要解决二叉搜索树在极端情况下退化成链表的情况。

B树：B树是一种多路搜索树（返回整个表时需要往上回查），每个子节点可以拥有多于2个子节点，M路的B树最多可拥有M个子节点。设计成多路，其目的是为了降低树的高度，降低查询次数，提高查询效率。1、虽然多路可以降低树的高度，但是如果设计成无限多路，就会退化成有序数组，一般B树的使用场景是用于文件的索引，这些索引会存放于硬盘中，有时内存是无法一次性加载完，此时就无法进行查找。2、如果全部在内存中，红黑树的查找效率要高于B树，但是涉及到磁盘操作，B树要优于红黑树，所以在JDK1.8版本的HashMap中，如果单个桶的链表长度多于8或全部桶的链表总长度多余64，会将链表转换成红黑树。

B+树：B+树是对于B树进行优化的多路搜索树，主要设计是将数据全部存放于叶子节点，并将叶子节点用指针进行链表链接。

• 数据库的索引一般数据量不小，同时又存放于磁盘中，采用多路搜索树，可以降低树的高度，同时在大数据量下可以分批载入内存，提高查询效率。
• 不同于B树的使用场景，数据库的查询中，我们一般查询的数量不会是单条数据，例如列表常用查询中的分页查询--查询第1页的10条数据，此时如果采用B树，需要进行树的中序遍历，可能需要跨层访问。

• 而B+树的所有数据全部存放于叶子节点上，且叶子节点之间采用指针进行链表链接，一次查询多条时，确定首尾位置，便可以方便的确定多条数据位置。

• 内节点不存储data，只存储key：更大的出度；叶子节点不存储指针；一般来说，B+Tree比B-Tree更适合实现外存储索引结构

2、InnoDB索引的设计

（1）计算机原理

在计算机系统中一般包含两种类型的存储，计算机主存（RAM）和外部存储器（如硬盘、CD、SSD等）。在设计索引算法和存储结构时，我们必须要考虑到这两种类型的存储特点。主存的读取速度快，相对于主存，外部磁盘的数据读取速率要比主从慢好几个数量级，具体它们之间的差别后面会详细介绍。 上面讲的所有查询算法都是假设数据存储在计算机主存中的，计算机主存一般比较小，实际数据库中数据都是存储到外部存储器的。
           一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
           磁盘读取数据靠的是机械运动，当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间，最后便是对读取数据的传输。 所以每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分。
           局部性原理与磁盘预读：为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。

（2）查找过程

过程：如果要查找数据项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+树比B树适合做索引：B+内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。但这不是最主要的因素，关键是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。一种解释很好：B+树还有一个最大的好处，方便扫库，B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持。这是数据库选用B+树的最主要原因。 另外B树也好B+树也好，根或者上面几层因为被反复query，所以这几块基本都在内存中，不会出现读磁盘IO，一般已启动的时候，就会主动换入内存。  真实数据库中的B+树应该是非常扁平的，可以通过向表中顺序插入足够数据的方式来验证InnoDB中的B+树到底有多扁平。通常是单表在千万级，大小几十个G的情况下，高度是3及高度是4的情况通常实际业务达不到已经分表了。

（3）聚族索引与二级索引

每个InnoDB的表都拥有一个特殊索引，此索引中存储着行记录（称之为聚簇索引Clustered Index），一般来说，聚簇索引是根据主键生成的。聚簇索引按照如下规则创建：1、当定义了主键后，InnoDB会利用主键来生成其聚簇索引；2、如果没有主键，InnoDB会选择一个非空的唯一索引来创建聚簇索引；3、如果这也没有，InnoDB会隐式的创建一个自增的列(rowid)来作为聚簇索引。

除了主键索引之外的索引，称为二级索引（Secondary Index）。二级索引可以有多个，二级索引建立在经常查询的列上。与聚簇索引的区别在于二级索引的叶子节点中存放的是除了这几个列外用来回表的主键信息（指针）。

所谓回表：就是在使用二级索引时，因为二级索引只存储了部分数据，如果根据键值查找的数据不能包含全部目标数据，就需要根据二级索引的键值的主键信息，去聚簇索引的全部数据。然后根据完整数据取出所需要的列。这种在二级索引不能找到全部列的现象称为“非索引覆盖”，需要两次B+树查询，反之称为索引覆盖。所以索引需要平衡考虑，多建索引有利于查询，但是占用空间大还影响写入性能。即索引要精有用。

这样的优势：1、由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。2 、辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位，后面会涉及）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。

（4）创建索引及索引的类型

（1）普通索引：1 直接创建索引   2修改表结构的方式添加索引  3、创建表的时候创建索引

CREATE INDEX index_name ON table(column(length))

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `salt` varchar(50) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `type` int(11) DEFAULT NULL COMMENT '0-普通用户; 1-超级管理员; 2-版主;',
  `status` int(11) DEFAULT NULL COMMENT '0-未激活; 1-已激活;',
  `activation_code` varchar(100) DEFAULT NULL,
  `header_url` varchar(200) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_username` (`username`(20)),
  KEY `index_email` (`email`(20))
) ENGINE=InnoDB AUTO_INCREMENT=160 DEFAULT CHARSET=utf8

（2）唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：1 直接创建索引   2修改表结构的方式添加索引  3、创建表的时候创建索引

（1）
CREATE UNIQUE INDEX indexName ON table(column(length))
（2）
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
（3）
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

（3）主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

（4）组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

联合索引。一般索引只有一个字段，联合索引可以为多个字段创建一个索引。它的原理也很简单，比如，我们在（a,b,c）字段上创建一个联合索引，则索引记录会首先按照A字段排序，然后再按照B字段排序然后再是C字段，因此，联合索引的特点就是：1、第一个字段一定是有序的；2、当第一个字段值相等的时候，第二个字段又是有序的，依次类推。

联合索引的查询：在（a,b,c）字段上建了一个联合索引，所以这个索引是先按a 再按b 再按c进行排列的，所以：以下的查询方式都可以用到索引

select * from table where a=1；
select * from table where a=1 and b=2；
select * from table where a=1 and b=2 and c=3；

上面三个查询按照 （a ）, （a，b ）,（a，b，c ）的顺序都可以利用到索引，这就是最左前缀匹配。

如果查询语句是：那么只会用到索引a。

select * from table where a=1 and c=3； 

如果查询语句是：因为没有用到最左前缀a，所以这个查询是用不到索引的。

select * from table where b=2 and c=3； 

如果用到了最左前缀，但是顺序颠倒会用到索引码？比如：

select * from table where b=2 and a=1；
select * from table where b=2 and a=1 and c=3；

如果用到了最左前缀而只是颠倒了顺序，也是可以用到索引的，因为mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。但我们还是最好按照索引顺序来查询，这样查询优化器就不用重新编译了。

（5）索引的缺点

数据库索引的作用：加快查找速度，约束数据的值，如唯一索引。

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。

2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

什么时候不建议使用索引：1）数据唯一性差的字段：比如性别，意味着二叉树级别少；2）频繁更新的字段：比如登录次数，频繁变化导致索引也频繁变化，增大数据库的工作量，降低效率。3）数据不再where语句出现时：只有在where语句出现，mysql才会用索引；4）数据量少的表不要用索引。

什么时候要使用索引？1）主键自动建立唯一索引；2）经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；3）作为排序的列要建立索引；4）查询中与其他表关联的字段，外键关系建立索引；5）高并发条件下倾向组合索引；6）用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

（6）外键及作用

外键的定义：表的外键是另一张表的主键。将两张表联系到一起。

作用：简单的说是为了保证数据的完整性与一致性。

主键和索引是不可少的，不仅可以优化数据检索速度，开发人员还省下其它的工作；外键考虑有两个问题：一个是如何保证数据库数据的完整性和一致性；二是第一条对性能的影响。

外键的要求：
1.父表（student）与子表(sc_class)必须具有相同的存储引擎，而且禁用使用临时表
2.数据表的存储引擎必须为InnoDB
3.外键列与参照列必须具有相似的数据类型，其中数字的长度或有无符号位必须相同，而字符的长度可以不同。
4.外键列和参照列必须创建索引，如果外键列不存在索引，mysql会自动创建索引。

优势：
1、由数据库自身保证数据一致性，完整性，更可靠，因为程序很难100％保证数据的完整性，而用外键即使在数据库服务器当机或者出现其他问题的时候，也能够最大限度的保证数据的一致性和完整性。 eg：数据库和应用是一对多的关系，Ａ应用会维护他那部分数据的完整性，系统一变大时，增加了Ｂ应用，Ａ和Ｂ两个应用也许是不同的开发团队来做的。他们如何协调保证数据的完整性，而且一年以后如果又增加了C应用呢？
2、有主外键的数据库设计可以增加ER图的可读性，这点在数据库设计时非常重要。 3、外键在一定程度上说明的业务逻辑，会使设计周到具体全面。

劣势：
1、可以用触发器或应用程序保证数据的完整性
2、过分强调或者说使用主键／外键会平添开发难度，导致表过多等问题
3、不用外键时数据管理简单，操作方便，性能高（导入导出等操作，在insert, update, delete 数据的时候更快）eg:在海量的数据库中想都不要去想外键，试想，一个程序每天要insert数百万条记录，当存在外键约束的时候，每次要去扫描此记录是否合格，一般还不止一个字段有外键，这样扫描的数量是成级数的增长！我的一个程序入库在3个小时做完，如果加上外键，需要28个小时！

外键的性能问题：
1.数据库需要维护外键的内部管理；
2.外键等于把数据的一致性事务实现，全部交给数据库服务器完成；
3.有了外键，当做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，而不得不消耗资源；
4.外键还会因为需要请求对其他表内部加锁而容易出现死锁情况；