mysql索引

在mysql中，索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。

通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列即可。

通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。

可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。

因此，使用索引可以很大程度上提高数据库的查询速度，还有效的提高了数据库系统的性能。

索引的优缺点

索引有其明显的优势，也有其不可避免的缺点。

优点

索引的优点如下：

1、通过创建唯一索引可以保证数据库表中每一行数据的唯一性。

2、可以给所有的 MySQL 列类型设置索引。

3、可以大大加快数据的查询速度，这是使用索引最主要的原因。

4、在实现数据的参考完整性方面可以加速表与表之间的连接。

5、在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

缺点

增加索引也有许多不利的方面，主要如下：

1、创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

2、索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

3、当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

使用索引时，需要综合考虑索引的优点和缺点。

页是 InnoDB 管理存储空间的最小单位。一个页的大小一般是 16 KB。InnoDB 有许多种页用于不同的作用。其中数据页则是用于存储数据。数据页存储的内容为:

其中 Infimum + supremum 以及 User Records 为页中存储数据的部分。其中 Infimum 表示页中的最小记录，而 supremum 表示页中的最大记录。这两个记录不存储实际的值，而仅仅表示开头以及结尾。User Records 部分按行存储数据。User Records 中的每一条记录格式为:

插入到页中的记录是按主键大小进行排序。利用其中的 next_record 可以查找到下一条记录。在不考虑索引的情况下，如果我们要寻找其中的某条记录可以通过遍历链表的方式进行查找。但是如果当页中的数据过多，o(n) 的时间复杂度明显不满足快速查找的需求。因此 InnoDB 在页中设计了页目录。页目录中有多个槽，其规则如下:

因此实际搜索时，可以利用槽进行二分搜索，将算法复杂度降到了 。这个结构有点类似于一个两层的跳跃表。

由于一个页中实际能存储的数据有限，因此记录会被分配到多个页进行存储。页与页之间有着双向链表的结构。

在 innodb 中使用 B+ 树作为索引。实际上索引在 mysql 中也是作为页进行管理的。例如:

索引页与数据页类似，只是索引页中一条记录只存在两列。分别是页对应的最我号，以及页的页编号。当然，一个 b+ 树肯定存在多个级别，因此实际上的存存储格式为:

这里可以看出索引页与数据页其实并没有太多的区别。只不过数据页中存储着真实的数据，而索引页只存储索引。这里也可以看出主键索引实际上是聚集索引，当查找到最终的数据页时是可以直接获得数据。

许多个页组成的空间之为页空间。每个表空间对应着一个真实的文件 表名.ibd。每一个独立表空间中又会分为多个区。每一个区实际上是 64 个连续的页组成。每256个区划又会分为一组。

为什么会提出区的概念呢？原因是查找数据的时候，在页与页之间会通过双向链表进行查找。如果两个页随机分配物理地址，则其之间的物理位置可能非常远。那么在查找的时候无疑会形成大量的随机 IO。降低磁盘的性能。因此，当表中数据过大的时候，以区为单位进行分配连续的磁盘空间，可以减少随机 IO 的数量。

表空间中还有段的概念，当我们利用索引进行查询的时候。很多时候实际上是利用 B+ 树的叶子节点进行范围扫描。但是如果将索引页和数据页都存放在一个区中，那么数据页不一定是连续的磁盘空间。因此当进行范围扫描的时候又会存在随机 IO 的情况。因此索引页和数据页实际上是存放在不同的区中。存放索引页的区的集合又成为一个段，当然非索引页存放的区的集合则为另一个段。

我们知道，磁盘的速度是远远小于内存的速度。因此 InnoDB 会将查询的页缓存在内存 Buffer Pool 中，以免每一次请求都从磁盘中获取，加快查询速度。当然，内存不可能无止尽的使用。因此 InnoDB维护了一个 free 链表。 free 链表指向 Buffer Pool 中可用的部分。

当页面进行修改之后，缓存的中的页页不会马上落盘，这样的页称为脏页。InnoDB 维护了一个 flush 链表指向了脏页。当 buffer 的空间不足时，InnoDB 会进行刷页 *** 作，将脏页写入到磁盘中，腾出内存空间供新的页缓存使用。

一般来说，数据有冷热之分。如果经常刷新热点数据到磁盘中，肯定不划算。因为热点数据经常被查询修改，当写入到磁盘中后又会很快读入到缓存中，做了很多无用功。因此 InnoDB 采用了 LRU 算法统计哪些是热点数据，哪些是非热点数据。每次刷盘时从首先 LRU 链表的尾部将热点数据刷入到磁盘中。

InnoDB 并不是采用最简单的链表，而是划分区域的链表。其设计的原因是，InnoDB 在某些时候会采取预读的 *** 作，将一个区的数据全部读入到内存中。这些数据就会出现在 LRU 链表的头部。如果这些预读的数据最终不能被查询，那么真正的热点数据反而被挤到了链表的尾部，这样一旦存在预读行为 LRU 链表的功能就丧失了。同样，当用户进行扫描全表的 *** 作时，大量的页也会被加载到缓存中将 Buffer 占满。因此 InnoDB 将 LRU 分为两个区域-热数据(young 区)以及冷数据(old 区)。

对于第一种情况，当页被缓存到 Buffer 时首先会被放在 old 区。如果该页后续被继续访问，则会被放到 young 区中。而如果该页后续没有被继续访问到，则会逐渐移动到 old 区尾部。

对于扫描全表的情况，扫描全表有一个特点。即页中的每一条数据都会被访问到，同一个页第一次访问到最后一次访问的间隔时间一定很短。因此 InnoDB 设计了一个策略，如果当一个页加载到内存中，并且该页在第一此访问与最后一次访问间隔相差小于 1s (默认值)，则该页就不会被加入到 young 区中。因此这种方式可以避免全表扫描时对 LRU 链表的污染。

---