【Mysql 探究索引底层原理】简单理解

一、InnoDB行格式

• 存储引擎负责对表中的数据进行读取和写入，常用的存储引擎有：InnoDB,MyISAM，Memory等，不同的存储引擎有着自己的特性，数据在不同的存储引擎中存放格式也是不同的，比如：Memory都不用磁盘来存储数据。
• 在InnoDB，数据会存储到磁盘上，在真正需要数据时才将数据加载到内存中，表中读取某些记录时，在InnoDB存储引擎不需要一条条把记录从磁盘上读取出来，InnoDB采用的方式是：将数据划分为若干页，以页为磁盘和内存之间的基本单位，InnoDB页的大小一般为16kB

二、局部性原理

• 在我们的程序里面，我们一行指令或者一行代码可能只取一个字节的数据，你的业务逻辑可能就只需要这一个字节的数据，但是我们计算机用局部性原理：虽然你现在可能只需要这一个字节的数据，但是你等下可能也会用到这个字节相邻的其它数据。所以我们计算机会把这个字节的相邻数据也从磁盘拿出来，放到内存中去。
  下次如果你取数据，正好符合了这个理论，那就不需要从磁盘中取数据，直接从内存中取数据就好了！更通俗地说：取数据时候会多取点数据一起放到内存中，以备后用

取多少呢？

• 如果你取出的某个字节是属于某一页的，那么就会把这一页取出来，一页大概就是：4KB大小

• 用处：比方说我们select 筛选数据的时候，可能我们只需要某一行的数据，但InnoDB也是用局部性原理，会多取几行数据放在内存中，以备后用。

三、数据页结构和行格式

• 页是InnoDB的存储空间的基本单位，一个页的大小默认16kB。

（一）页结构

重要三大块：

• 1、User Records：用户记录
• 2、Free Space：空闲空间，当我们插入记录时，会把插入记录所占空间变成用户记录空间
  也就是User Records
• 3、Page Directory：（底下会讲）

（二）行格式

行格式分为Compact、 Redundant、 Dynamic、 Compressed 4种
mysql一行记录最多存储65536个字节，不包括隐藏列和记录头信息
隐藏列：

• now_id 行id，唯一表示一条记录
• transaction_id 事务id
• roll_pointer 回滚指针

（三） 数据页结构如何存储数据

• 我们从上面知道，mysql一行记录做多存储65536个字节，而InnoDB一页是16KB，16384个字节，这就是行溢出，如何存储呢？

• 方式1、一页存不下，那就分好几页存，每页存储的是：部分数据+下一页的地址

• 重点方式2、 第一页我们不存数据，只存下一页的地址，数据放到下一页。这样做的好处是什么？
  回答：内存地址占用内存小，举例：那就是说我们可以在第一页存好多行数据的内存地址，这样做有什么用？底下会讲

四、索引

在此之前，我们来创建两个表t1,t2给大家看看：
mysql> create table t1(
-> a int primary key,
-> b int,
-> c int,
-> d varchar(10)
-> ) engine = InnoDB;

mysql> create table t2(
-> a int primary key,
-> b int,
-> c int,
-> d varchar(10)
-> )engine = MyISAM;

• 两张表 a 都为主键，除了存储引擎不同其余都一样，我们再给两张表插入几条相同的数据看看运行结果
• insert into t1 values(1,1,1,‘a’),(4,3,1,‘d’),(2,2,2,‘b’);
• insert into t2 values(1,1,1,‘a’),(4,3,1,‘d’),(2,2,2,‘b’);

运行结果：
mysql> select * from t1;
±–±-----±-----±-----+
| a | b | c | d |
±–±-----±-----±-----+
| 1 | 1 | 1 | a |
| 2 | 2 | 2 | b |
| 4 | 3 | 1 | d |
±–±-----±-----±-----+

mysql> select * from t2;
±–±-----±-----±-----+
| a | b | c | d |
±–±-----±-----±-----+
| 1 | 1 | 1 | a |
| 4 | 3 | 1 | d |
| 2 | 2 | 2 | b |
±–±-----±-----±-----+

结果分析：我们看到，存储引擎为InnoDB的表按照主键给我们把插入记录进行排序；而存储引擎为MyISAM的存储引擎则未作改变！

（一）InnoDB如何存储数据

B+树

• insert into t1 values(1,1,1,‘a’),(4,3,1,‘d’),(2,2,2,‘b’);
  *为标明主键,我在数字后加了_ 下划线
  
  我们上面知道，当我们插入数据时，InnoDB会按照主键给我们把插入记录进行排序，但查询效率依然比较低，因为每次还是要从头开始查找，怎么优化呢？看下面

优化

页目录：（上面提到了）

• 优点： 页目录会把数据进行分组，比如说看图：它会把 1_11 a 和2_2 2 b分为一组，每两个分为一组，我们可以把这每一组看成书的不同章节；页目录就是每章节的目录，存储每章开始的页码（这里指主键），然后查找直接在页目录查找，先确定查找内容在那一组，在组里面再查找，提高效率！

再优化

• 我们从上面知道，页目录也是一个从小到大排序的数列，学过编程的人都知道，对有序的数列，要提高查询效率，我们想到的是二分法

• 当我们数据较多时，一页存不下的时候，就需要多页，那么问题来了，假设我们要查找某条数据时，我们必须先知道它在哪一页，然后按照上面思路进行，页数如何查找呢？

目录页：

我们可以看到，目录页保存页的页数，和此页的最小”章节数页码“。
上面我画的这种结构也就是我们平时所看到的这种结构：大家重在理解

• 还有一个问题，当我们数据很多的时候二叉树满屋不了我们的需求，那就要多分几支，但很可能目录页也会一直变化，Mysql是如何解决的呢？
  
• 回答：在建一个表的时候，mysql提前创建好第一个空白页，然后添加数据到第一个空白满，然后会把第一页复制一份，然后开辟第二个空白页，再把第一页改成我们的目录页，这样做就保证了，目录页是唯一的不会改变的！

（二）InnoDB和MyISAM存储引擎建立索引

一、MyISAM

主键索引：

辅助索引：

1、主键索引（建立在主键上）叶子节点存储：建立索引的属性和数据对应的地址。
2、辅助索引（建立在非主属性上）叶子节点存储：和主键索引一样，在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
3、MyISAM存储引擎下的索引我们称作：非聚簇型索引

二、InnoDB

主键索引：

辅助索引：

1、主键索引（建立在主键上）叶子节点存储：建立索引的主键和主键对应的数据。
2、辅助索引（建立在非主属性上）叶子节点存储：建立索引的属性和主键
3、InnoDB存储引擎下的索引我们称作：聚簇型索引
4、聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

三、区别

1、存储结构（主索引／辅助索引）

• InnoDB的数据文件本身就是主索引文件。而MyISAM的主索引和数据是分开的。

• InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

• innoDB是聚簇索引，数据挂在逐渐索引之下。

2、主键

• MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址

• InnoDB如果没有设定主键或非空唯一索引，就会自动生成一个6字节的主键，数据是主索引的一部分，附加索引保存的是主索引的值

（三）索引的优缺点和建立原则

缺点：

• 第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
• 第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
• 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

优点：

• 第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 第二，对一列或者多列数据进行排序，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 第四，在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
  哪些情况不适合建立索引：
• 第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
• 第二，对于那 些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
• 第三，对于那些定义为text和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
  第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

（四）Mysql为什么采用B+树索引存储结构呢？（面试必问）

1、B树的每一个节点，存关键字和对应数据地址；而B+树的非叶子节点只存关键字。因此B+树的每一个非叶子节点存储的关键字远远多于B树，B+树的叶子节点存放关键字和数据，因此，从树的高度来说，B+树的高度小于B树，使用的磁盘 I/O次数少，因此查询更快些
2、B树由于每个节点存储关键字和数据，因此离根节点近的数据，查询就快，反之就慢；B+树所有数据都在叶子节点上，因此在B+树上搜索关键字，找到对应的数据的时间是平均的，没有快慢之分。
3、在B树上如果做区间查找，遍历节点是很多的，B+树所有叶子节点被连接成了有序的链表结构，因此做整张表的遍历和查找是非常容易的。
（后续还会往里面添加）