MySQL

MySQL索引底层原理

• 在我们的线上系统，经常可能会碰上一些慢查询，当碰到了慢查询，第一时间想到的就是建索引，哪个地方没有建索引，或者说 索引建的不合适。当建了索引之后，这条慢查询 确确实实 性能查找的效率 提高了非常多，那为什么建了个索引之后，哪怕这张表超过千万行，只要 使用索引得当，它依然几百毫秒就能查出我们的结果，如果没有建索引，你去一张千万级别的表里面去查，可能要几十秒，这就需要彻底理解索引底层的一个原理。
• 一张千万级别的表，如果索引用的比较合适，查询效率是非常高的，否则会很慢。那索引 提升数据查询效率 的底层原理是什么，如何把索引 建的更加 有效率一点，以及 工作中用的比较多的联合索引底层结构是什么样的。在做一些大数据量表的开发时，经常可能会有慢查询，涉及到这些慢查询，在MySQL中是如何优化的。

索引相关概述：

索引相关的几种查找算法：

• 遍历：暴力查找；
• 二分：B+树的基础算法；
• 键查找：hash查找；

数据的一般存储方式：

• 内存存储：任何数据处理都必须经过内存，内存可供CPU直接使用，适合小数据量存储；内存资源有限，是非常宝贵的，查询速度快。
• 磁盘存储：所有的静态数据资源 都是存储在磁盘中的，适合大数据量；查询时 需要 先做磁盘I/O，把数据load到内存中再使用，查询速度慢；
• 所以，减少磁盘I/O操作，对性能提升 起到至关重要的作用。

磁盘的相关概念：

• 运转方式：速度 + 旋转；
• 磁盘页的概念：MySQL中默认每一页大概16KB；

数据库索引检索原理：

• 数据库表中的数据，是存储在磁盘上的；数据库表中 通过 数据结构 所维护的 索引(元素的)文件，也是存储在磁盘上的；
• 当我们 通过索引 去查询数据库表中的 数据时，实际上，需要先把 索引文件中的 根节点 数据页(维护索引的 数据结构中的 一个节点)，全部加载load到内存RAM中(根节点是常驻内存的)，之后在这个根节点 里面的 索引元素的范围，找到 下一层 数据页节点的 磁盘文件位置，加载到内存中继续查找，以这样的方式，最终找到 所要查询的数据结果。

索引的定义

索引的 教科书式定义：

• 索引 类似于 一本教科书前面的目录页，方便根据目录页的索引序列页数，定位到要查找的内容。但这种解释，只能停留在 一个大学时 教科书式的解释，是非常浅层次的，听完也不知道 索引到底是什么东西。那要揭开索引的本质，就必须理解索引的底层的数据结构 以及 相关的查找算法。

索引的本质：

• 索引 实际上 是帮助我们数据库(比如MySQL) 获取数据的 排好序的 一种数据结构。索引的本质就是排好序的数据结构。

索引相关的数据结构：

• 那先从数据结构说起，数据结构 无非就是 存储数据的 一种结构，比如 二叉树，红黑树，Hash表，B-Tree等。
• 国外有一所大学 开发了一个 数据结构的学习网站，这个网站 是学习数据结构的一个教学网站，非常非常好用，大部分的数据结构，在这个网站里都有一些个介绍，或者说演示。

二叉树(Binary Search Tree)：

• 二叉树有一个特性，就是 右边的子元素 是大于等于 它的父节点元素的，然后 左边的子元素 是小于 它的父节点元素的。

红黑树(Red-Black Tree)：

HASH表：

• HASH是一种散列算法，实际上就是事先把要查找的元素，通过HASH运算得到一个散列值，把这个散列值放到一个HASH映射表中去维护。当要查找的元素的时候，只需做一个hash运算，得到了一个散列值，然后去到事先维护的hash表里面，快速的就可以定位到要查找的数据。
• 可以认为 要查找的元素 和 对应的散列值 以及要查找的数据，它们是一一映射关系，只要算出它的散列值，马上就能够 定位到 需要查找的数据位置，或者说 那个数据的磁盘文件地址指针。也就是说，HASH的查询性能是非常高的，哪怕存储10亿的数据，也只要经过一次hash运算，就能够快速的定位到要查找的数据。
• HASH的应用非常广泛，比如常见的有，用户登录系统的 那个密码，一般来说，要用MD5做一次hash运算，MD5就是一种hash算法。实际上，HASH算法有很多种，什么CRC16，CRC32，都是hash算法，那mysql底层也有自己的hash算法。

B-Tree：

不同数据结构的索引：

那索引为什么是一种数据结构，因为 通过选用合理的数据结构，可以尽量减少mysql对磁盘I/O的操作，从而提升数据库的整体性能。

无索引表

• 假如我有一张 没有建立任何索引 的裸表，那么 要查找数据，MySQL底层会怎么查询，逐行去比对遍历查找。也就是 如果这张表 没有建立任何索引的情况下，查找表中的第n行数据的次数为n次，时间复杂度为n。

示例分析：

• 比如 我有一张2列7行的 非常简单的表，这张表没有建立任何索引，
  

• 那么select * from t where t.col2 = 89;，查询这条SQL语句，也就是 查找表中第6行 这行数据，MySQL底层会逐行去比对查找，查找第一行col2=34，不对，再查找第2行，逐行查找，直到查找到第6行col2=89，这行数据，比对OK，一共查询了6次。

基于二叉树维护的索引：

• 如果 有一张数据库表，建立一个索引。那假设 现在用 二叉树 这种数据结构 来承载这个索引字段，也就是 基于二叉树的基本特性：左子元素 < 父节点 <= 右子元素，把索引字段 有规则的存放到 二叉树 数据结构中 来维护。当根据索引 查询某行数据时，一般来说 都是从 根节点 开始，逐层比对大小去查找。所以 二叉树的层数 决定了查找的次数，二叉搜索树 的时间复杂度，
• 最好为O(logN)：非叶子节点所有层高都放满元素的时候；
• 最坏为O(N)：所有层高只有一个元素的时候。

二叉树节点存储索引的结构：

• 那二叉树里面怎么存储索引，实际上 二叉树中一个节点load 在存储索引时，大概是一个key-value的结构，key实际上 就是存放了一个 索引字段，而 value实际上 存放的就是 data数据，这个data，可以把它理解为 这个索引元素 所在行的 磁盘文件地址指针 或者 这个索引元素 所在行的 完整的行记录数据(所有字段的集合)。因为一张表的数据 是存放在磁盘上面的，那就肯定有一个 磁盘地址。

示例分析：

• 那比如 这张2列7行的数据库表，假设 现在用 二叉树 这种数据结构 来承载col2这个索引字段，建了一个索引col2，把col2放到索引里面去，放到一个二叉树上面去。也就是 在这张表里面，把col2提取出来，放到一个二叉树结构里面，
  

• 那再查找select * from t where t.col2=89;，这条SQL 语句，

• 那么MySQL底层一看 就知道了col2是索引字段，就会先去 索引那个二叉树里面，快速过滤要查找的元素，那此时 从根节点开始去找，第一个节点为34，那89大于34，基于二叉树的特性，应该从根节点的右边子节点去找，一找发现 刚好就是我们要找的89元素，找到之后，根据二叉树存储索引的结构，key对上了，只要把value拿出来，而value存储的就是，这个89字段 所在的那一行数据的 磁盘文件地址指针，拿到这个指针 直接去磁盘文件上面 去找一次，做一次磁盘I/O就行了。

• 那这样去查找，用一个巧妙的索引 去存储某一个索引字段，再根据 索引的这条SQL语句查找，只需查找2次 就找到数据了，而且如果表中的数据越多，它带来的性能提升 会更加明显，但是要遗憾的告诉你，我们真正的MySQL索引，比如 把某一个字段 建个索引，它的底层数据结构 并不是用二叉树，可以直接告诉你结论，它用的是B+树，或者叫B树系列的这种数据结构。

• 那是为什么 MySQL底层不用 二叉树 作为索引，说白了 肯定是 在某些特定的 业务场景下，二叉树 不一定能满足 索引的要求，比如 假设把表中col1这个字段 作为我们的索引字段，也用二叉树 来存储，那有可能 会是一个什么样的结果，可以通过 数据结构学习网站 来演示如下：
  

• 那现在把 col1作为 索引字段，用二叉树来维护，然后 以 123456789依次递增的趋势，逐行来插入数据，根据二叉树的特性，那依次递增的数据场景，或者说 字段是这种单边增长的 递增趋势的数据字段，这个二叉树 最终会 演变成了 一个链表。这时，假设用col1这个索引字段来查找，select * from t where t.col1=6;，既然建了索引，那肯定是 用索引字段去查找，查询效率才高，但在这种场景下，查找col1=6，需要查找6次，实际上 跟表没有索引时的 依次轮询查找，没有什么区别。

• 那因为 表里面的数据 是存在磁盘上面，实际上 索引存储的数据结构，它也在磁盘上面，如果它出现这种 单边增长 的数据 放到一个二叉树里面来，实际上 二叉树最终变为 链表，那再去查找的时候，就跟这张表没有建立索引，逐行去比对查找的方式，没有什么区别，性能上面没有任何提高。也就是说，用二叉树做索引的底层的 数据的 承载的一种结构，在某些特定场景，像这种单边增长的场景，就不是特别合适，那有没有更好的数据结构，来承载索引底层的一个数据结构。那比如 红黑树，如果用 红黑树来存储 索引字段 会不会更好一点。

基于红黑树维护的索引：

• 红黑树也应用 在jdk1.8里面的HashMap，jdk在1.8之后，对HashMap底层的链表的数据结构做了一个大的改进，就把它优化成了红黑树，这样效率会提高很多。
• 那如果我们用红黑树，Red/Black Tree这种数据结构，来存储 递增趋势的数据，那它的效果如下，
  
• 那么以 红黑树作为索引的存储结构，依次插入1234567，因为 插入 比父节点大 的数据，一定会 往右边去插入，这也是 二叉树的特性，实际上 红黑树 也是一个二叉树，但是区别是，当它一边元素如果太多了，如果 这棵树 左右失衡的太严重，比如 最右边 比 最左边 的子元素树高 超过了2层，这个树 出现单边失衡的话，这时 再往里面插入元素，它会自动帮你做一个旋转，实现树的一个平衡，但其实红黑树，也是一种二叉树，它叫做二叉平衡树，它会自动平衡，不让一棵树 单边增长的太过分了，这个树可能不稳会倒，所以它会底层 通过平衡算法 帮你做一些平衡。当然它底层平衡算法，这里不去讨论。
• 那用红黑树 来存储MySQL索引col1这个字段，那再来查找一下select * from t where t.col1=6;，这条SQL语句，索引col1=6的这个值，只需经过3次查找，就找到了 这个索引的元素，根据 二叉树节点存储索引的key-value结构，找到这个索引的节点，也就是找到key，那value 存的是这个索引 所在那个行的 磁盘文件地址指针，根据这个指针，就找到了 这个索引 所在那一行的记录，就拿到了我们真正的结果了。那通过红黑树存储索引 只要在磁盘上面找3次就搞定了，相对于二叉树结构，性能又优化一点。但实际上，MySQL索引 最终底层用的是B树 这样的数据结构。
• MySQL底层索引，没有用红黑树的原因，无非就是某些场景下，也不是特别合适。那红黑树 来作为我们数据表里面 索引的底层数据结构，随着数据库表中 数据量越大，那么红黑树的高度也会增大，在这种场景下，树的深度 或者说 树的高度，是没办法控制的。那一般来说，我们生产数据库表，动辄几十万，甚至几百万行数据。
• 假设一张表 有100w行记录，不断的往这个红黑树里面插入元素，假设我把这颗树我插满了，那 这棵树的高度大概是多少，就是2的n次方，这个n就是树的高度。假设存100w的数据，它的树的高度，假设可能是需要20，那20的树高度，我查找的元素位于中间还好；在极端情况下，假设我要查找的元素是在叶子节点，也就是 要查找 树的高度 这么多次，才能查找到我要的元素。也就是说，如果数据量比较大，红黑树它也是搞不定的，一条SQL我走了索引，你还在磁盘上面，通过节点逐个去轮询 查个几十次，这肯定不合适的。

基于红黑树的改造思路：

• 那有没有更好的方案去存储我们的索引，如果作为一颗树来存储的话，即便查找再多的数据，查找的次数，我希望是可控的。或者更具体的说，假设存1000w行数据，或者几千万行数据，如果还用树这种数据结构，是否可以把 查找次数 控制在3到5次之间，也就是，让树的高度能控制在3到5层之间，就能够 存放几千万行 数据，答案是肯定的。

基于B树维护的索引

• 假设就在 红黑树上面去改造，那么首先，这个数据结构肯定是存储在磁盘上面的，根据二叉树节点存储索引的key-value结构，一个节点里面只放一个索引，一个data。那对此节点的存储索引的结构 稍微改造一下，可以改变 节点的广度，也就是，能否让 一个节点里面 可以存放 更多的key-value结构的 索引元素，构成一个大节点。然后 大节点中的数据 从左到右依次递增。
• 一个大节点中的 每相邻的 两个小节点 索引之间 通过指针 指向树的 下一层中的 一个节点，下一层中的每个节点，也让它分配一样容量的大小空间，以同样的规则存储索引。这样就可以 快速定位 存有 目标索引元素的 下一层 节点，从而通过二分查找等算法 快速找到对应的索引数据。也就是 这棵树的纵向，我是要控制的，那我又要存储很大的数据量，我完全可以在 这棵树的横向 做文章，让它的横向 可以存储 更多的元素，那实际上，对红黑树 稍微做一点点 这样的改造之后，就得到了B树。
  
• 实际上，在B树中，每一个小节点的左右分叉里面，它还是一个二叉树，依然满足二叉树的特性，右边的 子节点里面的所有的元素，都是大于等于 它的父节点 的，然后 左边的 子节点的 所有元素 都是小于 它的父节点的。也就是 每个大节点中的所有元素，都满足 小于 它右边的父节点，但是 大于等于 他左边的父节点。B树 看上去就是一个多叉的，或者说多路的形态。所以，B树它就是一棵 多叉平衡树，或者说 全名叫做多叉平衡树。
• 那这时，假设我要同时存储100w行记录的索引，用B树这样的数据结构，只要把B树上面每个大节点的横向 推算到一个合适 的值，那么 它的树的高度，就肯定是可控的。如果 这棵树的 每个大节点的 横向 可以存储100个 索引节点，高度为3的话，那这个树最多可以存放多少个数据元素，也就是S10=10x(1-10^3)/(1-10)=111w。也就是说，只要每个节点横向存储，3层树高，就可以同时存储100w行记录的索引。

基于B+树维护的索引：

• 那实际上，我们真正MySQL底层索引，它就借助了B树的这种数据结构，横向可以存储更多元素的数据结构，但是它在这个结构上面，又做了 一点点改造。真正MySQL底层 索引的数据结构，它用的是B+树。
  

B+树 对 B树的改造和区别：

• 那么B+树 对 B树做了哪些改造，它们的区别如下：

1. B+树 在叶子节点 之间多了一个指针 (详见 B+树索引对范围查找的支持(叶子节点间指针的作用))。
2. B+树的 非叶子节点 不是key-value结构，只存储key，没有value值，也就是 只存储索引元素，而没有data数据(磁盘文件地址指针 或 完整的行记录)。
3. B+树的 叶子节点 存有一份 完整的 全量索引元素，非叶子节点 有很多的冗余索引；而 B树索引是没有冗余的。也就是说，如果一个索引是唯一索引，在B树里面只会出现一次，但是在B+树里面可会出现多次。

• 实际上，B+树是B树的一个变种，也就是 把B树的非叶子节点里面的data元素，给它挪到了叶子节点来，中间存储的这些索引元素，是一些冗余的索引元素。部分冗余索引 作为 非叶子节点的 一部分，完整的索引 和 数据 放到叶子节点。因为 这一个节点，它大小是有限制的，如果我把一些多余的，像data元素 挪走了，意味着 这个节点，就可以存储更多的索引元素。也就是 非叶子节点，只存储索引 这样的 一个小数据，比如，同等大小都是16KB，B+树 的 非叶子节点 可以存储的索引 绝对 比B树 存储的 索引的节点 更多。

二分算法对B+树的支持

• B+树的叶子节点 存有一份 完整的 全量索引元素，然后 通过多次的二分算法实现，把属于一些 中间位置的 索引元素，提上去 放到 非叶子节点 作为冗余来存放，所以，非叶子节点都是冗余的索引元素；B树 的 索引元素在整个树只出现一次，不会冗余出现。

MySQL可以存储多少索引数据

• MySQL底层使用了B+树 作为 维护索引的数据结构，那么，这棵B+树 的高度是多少，以及 每个节点可以存放多少个索引。这决定了MySQL 到底可以存储多少数据量。
• 实际上，MySQL底层对B+树的每一个节点的大小，是有个默认值的，每个节点的大小是相同的，可以通过如下语句查询：

SHOW GLOBAL STATUS like ‘Innodb_page_size’;

• 查询结构如下：

Variable_name Value
Innodb_page_size 16384

• 通过这条SQL，就显示了Innodb的page_size，也就是MySQL中一个大节点的内存大小，或者 可以把它认为MySQL的一个页节点，它默认设置了一个大小，16384个字节，也就是16KB。

为什么不把所有的索引都保存到内存中

• 那么MySQL为什么要设置这个页节点的大小，又为什么是16KB。既然在节点横向做文章，树的高度就可以控制，而树的高度越小，从磁盘I/O查找的次数就越少，那也可以把横向做足文章，假设我有1000w行数据，那就把这1000w行数据的所有索引元素，都存放在这个大节点上面去，这么存储，那树的高度就是1，当根据某一个索引字段 查找某一行记录时，只要把这个大节点一次性load加载到内存，之后在内存里去查找，岂不是更快。
• 其实，把所有元素都存到一个大节点有意义么，

1. 首先，内存资源是非常宝贵的。我们常用的数据库记录，可能就那么一点点，如果 一次性把所有的索引元素都加载到内存，非常浪费内存。
2. 其次，这么多元素，几千w行数据放到一个节点里面，一次加载，那得加载多久，又是非常浪费时间的。
3. 最后，如果一次性加载到内存，树的高度很多，也没关系。按照这个思路，那即便用红黑树，也可以 把红黑树，事先一次性 初始化到 内存里面去，因为我在内存里查找元素，是非常快的。

• 所以，如果把所有元素都存到一个大节点，既浪费内存，浪费时间，又没有意义。
• 那么，想象一下，我们的数据库表里面，几千万行记录，真正用的是多少，没有多少的，所以我们一般不会用这种方式。一般来说，对于这个树的节点，是要有一个大小限制的，不能让它太大，太大会有很多问题，所以，这个节点MySQL给我们默认设置是16KB。

MySQL的一个非叶子节点可以存储多少个索引

• 那它为什么设置16KB，或者说 这个16KB的设置是不是很合适。一般来说，我们设计数据库里面的 一张表，主键可能 都是用int或bigint，那假设我举大一点，用bigint。比如，设计一张用户表，它有用户id，数据库主键用bigint，那bigint 在mysql数据库里面 占的容量有多大，8个字节，8byte，或者说 64位也是正确的。
• 也就是说，假设 这张表里面 建了一个主键，这个主键索引 就用B+树来维护，主键的类型为bigint，也就是占8个字节，而每个索引旁边，还有一个指针，实际上就指向下一个节点的一个磁盘文件地址指针，那这一个指针在MySQL中，可以看一下MySQL源码，给它分配的一个存储空间，大概是6个字节，也就是，它只存储这个节点，在磁盘上面的一个文件地址，用6个字节，6byte，就足够了。
• 那根据B+树的结构，一个大节点里面，每存储一个小的索引节点，意味着 它旁边就有一个 指向下一层节点的 磁盘文件地址指针，也就是说，我们这里面有多少 索引节点，跟它旁边的这个地址指针，可以认为 它是成对出现。那按照一个bigint索引是占8个字节，然后 索引旁边 指向下一层元素节点的 指针，大概是占6个字节，那么，存储一个索引大概有多少空间，8kb+6kb=14kb，14个字节。
• 然后，MySQL默认一个大节点是16KB，也就是16k个字节，那这个大节点，按照bigint这种整型的主键来存储的话，大概能放多少个索引元素，16kb除以14个字节，16kb/14b大概多少，大概就是1170，也就是说，MySQL的一个大节点里面，如果只存储索引的话，可以存储1170个索引。

MySQL的一个叶子节点可以存储多少个索引

• 按照B+树的结构，这棵树的 叶子节点 和 非叶子 稍微有一点点区别，就叶子节点来说，因为它没有下一层节点，所以每一个叶子节点里面的 索引元素，就没有 类似 非叶子节点里 每个索引旁边的 指向下一层的 磁盘文件地址指针。这里 需要注意的是 叶子节点里面，虽然没有了指针，但它里面 除了存储的索引元素之外，还有索引对应的那个data元素，这个data元素，可以理解为 这个索引所在行的磁盘文件地址指针，或者是 这个索引所在行的其他字段的集合 全部存储在这里。
• 也就是说，B+树的 非叶子节点，只存储索引元素key，并不存储数据data；而 叶子节点，既存储了索引元素key，又存储了数据data，也就是 索引元素所在行的 磁盘文件地址指针 或 其他所有字段的集合。那假设 叶子节点里的每个索引的key-value结构的 小节点，大概占用的空间，就取大一点 1KB。因为 不同的MySQL存储引擎，这个data里面放的元素不同，有的存储引擎 放的只是索引 所在行的 磁盘文件地址指针，那可能 容量不是特别大，但有的 放的是 索引所在行的 其他所有字段，全部放在data这里面来，保守一点估计，就假设占用 1KB。
• MySQL默认设置的 一个大节点的容量是16KB，非叶子节点和叶子节点 都是相同的。这样 叶子节点里的 每个索引和它对应的data数据，大概占用1KB空间，也就是说 一个叶子节点 总共可以存储16个索引元素，16KB/1KB=16。

MySQL基于3层树高的B+树可以维护多少索引

• 根据B+树的结构可知，这棵树 所有的叶子节点，存储了我们整张表的索引元素的全量索引，非叶子节点都只是些，处于中间位置的一些 冗余索引，那按树的高度为3来说，假设这个树现在被存满了， 也就是叶子节点全部存满了，那这一棵树它的 所有叶子节点里面，一共可以存储的索引元素，大概是多少。
• 根据分析，MySQL的每个非叶子节点，都可以存储1170个索引元素；而 每个叶子节点，都可以存储16个索引元素。那按照树高为3来计算，当这棵树如果被撑满了，大概大概所有叶子节点里面，可以存储多少索引元素，也就是 计算第2层 到 第3层 有多少个分支，也就意味着 有多少个叶子节点。
• 第1层 到 第2层 有1170个分支，也就是第2层 有1170个非叶子节点；而每个非叶子节点又有1170个分支，也就是第3层有 1170x1170=1368900个分支，也就是第3层 有大概140w个叶子节点；而 每个叶子节点，又可以存储16个索引元素，也就是 叶子节点一共可以存储 1170x1170x16=21902400个索引，大概2190w索引 或者说 2000多万索引。
• 所以 MySQL默认设置的这个节点16KB，是有道理的。树的高度h=3时，就轻轻松松已经能存2000w行的索引元素了。实际上日常我们用的数据库表，可能几百万行，上千万行就差不多了，对于一两千万行数据的表，我用B+树去做索引来存储的话，树的高度 只要等于3就可以了，那意味着这棵树的查询效率得有多高。任何一个数据 只要从磁盘上面load加载3次到内存，实际上只需要load两次，做两次磁盘I/O即可。因为根节点是常驻内存的，在初始化时已经加载完成了。
• 这就是 为什么建了个索引之后，哪怕你这张表超过千万行，只要使用索引得当，它依然几百毫秒 就能查出我们的结果，如果没有建索引，去查一张千万级别的表里面，可能要几十秒。

B+树示例演示：

• MySQL中 节点设置16KB，一个非叶子节点 大概可以存储1170个索引，一个叶子节点 大概可以存储16个索引和数据，当我要查找的数据元素的时候，就逐个去把节点load出来即可。假设我们把非叶子和叶子节点可以存储的小一点，比如每个节点可以存储的元素不超过3个，，然后依次插入1234567，这7个元素。那么也可以 动态去演示一下 B+树的维护数据的具体细节。
  

• 这就是B+树的数据结构大概的一个效果。也就是给红黑树的横向做了文章，只要让每个节点存储适当的元素，就可以控制树的高度，从而减少查询次数，提高查询效率。那用B+树 来存储MySQL索引col1这个字段，那再来查找一下select * from t where t.col1=6;，这条SQL语句，索引col1=6的这个值，只需经过2次查找，就找到了 这个索引的元素，这比红黑树，又进步了很多。

• 根据 数据库索引检索原理，一般来说，树这种数据结构存储元素，这个整颗树，实际上是放到磁盘里面的，当我要从 这个树里面 查找某一个元素的时候，它肯定是从磁盘里面去load加载元素到内存中，一个一个节点去load出来。首先，它会从根节点开始，而根节点一般会事先放到内里面，就常驻在内存RAM中，这一个节点的所有元素，它会事先放到RAM里面去。

• 比如，select * from t where t.col1=6;，这条SQL语句，查找6这个索引元素，那首先从我们的内存里面去比对，跟3去比对一下，发现大于3，满足二叉树的特性，往后去找，再跟5去比较，发现还大于5，应该走右边的子节点，那它再根据 右边的指针 找到下一个节点，找到之后，把这一个节点，再一次性的load到内存RAM里面去，load之后，在内存里面再去比对，发现6大于5，一下就找到6这个元素了，根据这个元素下面，也就是叶子节点下面，实际上有一个data数据，data里面可能存的是 这个索引所在行的 磁盘文件地址指针，也可能存储 这个索引所在行的 所有其他的字段，根据这个data，去磁盘上面去load一下地址，马上就找到要查找的那行记录了。

MySQL存储引擎

• MySQL数据库底层是用B+树来存储索引的，到底怎么运用B+树来存储的，以及当我在数据库表 写一条sql去查找数据的时候，它的底层到底是怎么走索引的，这都离不开数据库的存储引擎。
• MySQL有很多存储引擎，比如 MyISAM存储引擎 和 InnoDB存储引擎。在建表的时候，如果用Navicat作为连接MySQL数据库的工具，可以任意选中一张表，右键打开 设计表后，可以看到很多 选择按钮，比如 索引，外键，触发器等，其中就有一个选项按钮，点开之后，就可以看到 引擎 下拉选项， 里面就 很多存储引擎 可供选择，一般来说，默认就InnoDB。
  

MySQL数据库表 与 存储引擎 的关系：

• 在Navicat里，我们是从表设计中，找到的存储引擎的选项，实际上，存储引擎就是形容数据库表的。虽然数据库本身，也可以设置一个存储引擎，但它最终是具体到表级别的，如果打开表的DDL语句，就可以看到 每一张表都有 指定了自己存储引擎。
  

MySQL底层 数据结构 与 存储引擎 的关系：

• 那么，数据库有很多存储引擎，实际上，每一种存储引擎 底层还可以选择一些 不同的数据结构 来维护索引字段。比如 B+树 和 HASH表。在Navicat工具，可以任意选中一张InnoDB的表，右键打开 设计表后，可以看到很多 选择按钮，比如 索引，外键，触发器等，其中 索引 按钮，点开之后，就可以看到 索引方法 下拉选项， 里面有 BTREE 和 HASH 两个选择，一般来说，默认就BTREE。
  

MySQL索引和数据的文件存储结构：

• 不同的数据库存储引擎，底层索引 可能使用的 是同一种数据结构，但还是有些区别的。比如InnoDB和MyISAM存储引擎底层都可以使用B+树来维护索引。那么不同的存储引擎，它底层的索引到底怎么来用的，这就必须先了解MySQL底层索引和数据的文件存储结构。那分别结合：

• 一张innodb的表test_innodb_lock：
  

• 一张myisam的表test_myisam：
  

• 通过 这两张表的具体分析 它们的文件存储结构。首先，数据库表里面 可能有很多数据，数据表的数据定义，以及表里面的那些数据，是存储在我们磁盘上面的。比如本地装了一个mysql的服务 mysql-5.6.33-winx64版本，它的根目录下有个data文件夹：
  

• 打开这个data文件夹，data文件夹下面有很多子文件夹，这些子文件 与 打开的mysql的本地服务中具体的不同mysql的数据库实例，刚好是一一对应的关系：
  

• 那一个数据库中不同实例的相关数据 就是存放在这个data文件夹，比如test库，就放到test文件夹下面，那打开这个test文件夹下面就会发现，里面的文件 就是 和 本地mysql服务中的数据库表 一一对应，但是会发现，一个表对应的有多个文件，比如，这个test_innodb_lock这张表，对应的有两个文件，然后test_myisam这张表，对应的有3个文件：
  

MyISAM存储引擎的文件存储结构：

• 那看myisam这张表test_myisam，它对应的文件有3个，分别是test_myisam.frm，test_myisam.MYD 和 test_myisam.MYI，这3个文件其实看名字也大概能猜出来，
• 第一个frm，框架frame的缩写，实际上就是这张表里面的 那些表结构的 一些定义的 那些数据，就放在这个文件里面。
• 第二个MYD文件，MY实际上就是myisam的简写，那D就是data。那myd文件，存的是我们这张表里面的 所有的行数据。
• 第三个MYI文件，MY就是myisam，I是index。那myi文件，存储的就是我们的索引的数据。比方test_myisam这张表里面，有个PRIMARY_KEY (`id`)，PRIMARY_KEY；主键它自身就是个索引，那这个主键 这个字段里面的 索引元素，就是存储在MYI文件里面。
• 其实光看英文字母，就可以看出来这些文件 什么意思，不需要去背这些东西，理解就好了。

InnoDB存储引擎的文件存储结构：

• 再看innodb这张表test_innodb_lock，那innodb的存储引擎的表对应的磁盘文件，发现和myisam的 有一点点不同，它底层的文件只有两个，test_innodb_lock.frm 和 test_innodb_lock.ibd。
• 那这两个文件，第一个 frm文件，肯定跟myisam是一样的，是表结构文件。剩下的只有一个文件 叫ibd文件，它里面存储的什么东西，从myisam的文件存储结构，可以想到，一张表无非就那么几个东西，表结构，表里面的数据行 和 索引元素。
• 说白了，这个ibd文件就是myisam存储结构的myd和myi这两个文件的一个合集。mysql官方在设计innodb的时候，它这么来设计，把索引和数据 合并放到一个文件，也就ibd文件。

MySQL底层索引和数据的检索原理

• MySQL底层索引和数据的存储原理，也就是 MySQL底层 存储 索引 和 数据 的文件里面，用哪一种 数据结构 来维护 索引 和 组织 数据的。通过 MySQL底层 数据结构 与 存储引擎的关系，可知一种 存储引擎 是可以 选择不同的 数据结构 来维护索引的，比如 InnoDB存储引擎 的索引结构 既可以选择BTREE，也可以选择HASH表。MyISAM存储引擎也是一样。
• 如果InnoDB存储引擎 和MyISAM存储引擎，都选用了BTREE来作为维护索引的数据结构，通过 MySQL索引和数据的文件存储结构，可知，MyISAM的索引和数据分别对应MYI和MYD两个文件，是分开存储的；而InnoDB只有一个ibd文件，把 索引和数据 合并到一个文件中存储。那么，这些文件底层到底是怎么来存储索引和数据的。MyISAM和InnoDB都用了B+树这种数据结构，它们之间的区别是什么，以及它们底层查找数据的执行过程又是怎么样的。下面就对这两种存储引擎进行更深入的理解：

MyISAM存储引擎之B+树检索原理：

• 根据 MyISAM的文件存储结构 已知，MYI文件，它是一个索引文件，而索引文件底层是用一种数据结构来存储的，如果用B+树来存储的话，也就是在MYI文件里面，通过B+树来组织，存储test_myisam这张表中的主键索引字段，那myisam表它的底层的一个索引的存储的一个结构，如图：
  

• 结合这张图，那图中右侧 有一张表，表中col1是索引字段，这张表对应的就是MYD文件存储的内容；图中左侧是 对索引字段 所维护的 索引结构，这个索引结构对应的就是MYI文件存储的内容。也就是，MYI文件里面存的就是主键索引字段，这个文件里面它底层存储具体数据的存储方式结构，就是用B+树来组织的。表里面活生生的一行行数据，就是放我们那个MYD文件里面。

• 当执行一条sql语句时，比如，select * from t where col1 = 49;，col1作为索引，这条sql走了索引，是用索引字段来查找。那实际上，MySQL底层的执行过程就是，它先看下是不是索引字段，如果是索引字段，那它先去索引文件里面 快速的 根据B+树的特性去查找，这里查找索引col1=49的元素。

• 首先，先定位到这个元素，从树的根节点开始定位，一般根节点是常驻内存的，然后，从内存里面按照元素的某个特性逐一对比，先跟15比对，大于15 再跟后面的元素56比对，小于56，那么 应该取15和56之间指针，找下一个节点，把这个节点load到内存，load到内存之后，再去按照这个节点元素比对，大于15，大于20，走后面元素，发现找到了49，根据二叉树的特性，那应该从它的右边的子节点定位，找到下一个指针，那这个指针对应的磁盘文件地址指指向的节点给load到内存，load出来之后，再到内存里面去比对，这样就比对到了 49这个元素对应的叶子节点。

• 那一旦找到了索引元素对应的节点，这个节点是一个key-value结构，里面的key存储的是49这个索引元素，里面的data存储的就是 这个索引 所在的那一行的 磁盘文件地址指针0x90，拿到这个磁盘文件地址指针之后，然后，去到对应的MYD文件里面，快速的定位到对应的数据行，这就是myisam存储引擎 一条sql语句的 底层的 一个完整的 执行过程。

InnoDB存储引擎之B+树检索原理

• MyISAM存储引擎的B+树索引原理，实际上也就是myisam底层对一条sql语句通过索引进行查询的执行过程，那InnoDB这种存储引擎，底层对一条SQL语句通过索引进行查询的执行过程，跟myisam有一点区别。MySQL工程师 在设计的时候，实际上把InnoDB的表数据的文件，把这个文件 本身就用了一个B+树 来组织并存储数据，也就是 把InnoDB表的数据和索引放到了一个文件中，就是ibd文件。那还是通过主键索引来进行举例，它本身大概的一个结构，如图：
  

• 从这张结构图，就可以看出InnoDB的ibd文件，它本身就是一个索引结构，而且它还把 这张表里面 那些索引的数据，包括表的数据，都存放到这一棵B+树上面来，这个B+树的 非叶子节点 和 叶子节点的 索引元素，跟myisam的存储引擎是一样的，但是叶子节点这个地方，有一点区别。叶子节点这里的key-value结构中的data部分，不再是放磁盘文件地址指针，它放的是具体的数据。

• 那么，InnoDB和MyISAM索引存放的区别，就在这个叶子节点的data元素上面，InnoDB存储引擎，它是把 索引所在的 这一行的 其他剩余的 所有的字段，全部放到 叶子节点的 data这个地方来，也就是索引和数据合并了，它是怎么合并的，就是 把数据放到 也放到 叶子节点上面。那这个ibd文件，它底层组织的一个方式，就是 索引 和 数据，它们是存储在一起的。

聚集索引 和 非聚集索引：

• 通过 MySQL底层 对InnoDB 和 MyISAM存储引擎的 索引 和 数据 的 文件存储结构，以及 检索原理，可以引申出两个概念，聚集索引 和 非聚集索引。
• 聚集索引，又叫做聚簇索引，指的是 索引 和 数据 聚集在一个文件里面，innodb存储引擎的 主键索引，就是一个聚集索引。
• 非聚集索引，指的是 索引 和 数据 分开存储，没有聚集在一个文件里，在不同的文件中，myisam存储引擎的主键索引，就是一个非聚集索引。
• 那它们查找数据的区别在于，非聚集索引，它要查找两次，要先过滤myi文件，然后还要过滤myd文件。而聚集索引，它就只要在一个ibd文件里面进行查找，一个文件全部搞定。因此 聚集索引的查找效率，肯定比 非聚集索引 性能要高。

HASH表索引和数据的检索原理

• 根据MySQL底层 数据结构 与 存储引擎的关系，MySQL底层的一种存储引擎可以用很多数据结构。对于InnoDB存储引擎，底层索引结构，不仅可以选择BTREE结构(默认)，还可以选择HASH结构。
• 那实际上HASH 结构 用的很少，所以当建立一个索引时，MySQL会默认选择BTREE。但如果底层 选用HASH表来存储索引，当插入数据的时候，它会把这个索引的每一个字段，做一个HASH运算HASH(5)，得到一个结果值，也就是一个散列值，然后把这个散列值 跟 它的这一行数据的磁盘文件地址指针，把它们都存储到 一个HASH的映射表里面去。
• 也就是 在这张 HASH映射表 里面存储的，就是 索引列的每一个字段 通过某一个HASH运算 得到的一个散列值，以及 这个索引所在行的 磁盘文件地址的 一个映射关系。因此，不管你数据库表有多少行，假设你有10亿行数据，我要查找某一行数据，也只要经过一次hash运算，就能够快速的 定位到 这一行数据的 磁盘文件地址指针。
• 如果 单从定位的角度考虑，可以说 hash的定位的效率远比B树更高，但HASH只适用于 精确的查找某一行数据，如果是范围查找，HASH就没辙了。所以，hash索引的缺点，就在于它对范围查找的支持是非常差的。

B+树索引对范围查找的支持(叶子节点间指针的作用)：

• 假设用HASH表来存储索引，还是这张表：
  

HASH 范围查找的缺点

• 如果把这个col1作为索引，再来查这条sql语句的话，select * from t where t.col1=6，用索引来查找，col1是索引，而且是hash索引，那要查找这个元素很简单，通过对要查找的元素 做一个hash运算，只要经过一次HASH运算，得到了一个散列值，然后去到我们的hash表里面，快速的就可以定位到 这个索引所在那一行的 磁盘文件地址指针。所以HASH索引 是非常非常快的。但实际的工作中，99.9%以上的情况，都是用B树索引，而不是用hash。
• hash索引用来精确的 查找某一行数据时，确确实实比B树是快的但如果把，select * from t where t.col1 = 6，这条sql改成，select * from t where col1>6，要查询这条sql的结果集，那再用hash索引来查，一次hash运算，只能定位到6，那大于6的元素 到底是哪些，根本就没办法去算，也就是 范围查找，hash索引 就没辙了。但是往往工作中的 业务系统，是不可能离开范围查找的。你说你没有范围查找，那我得想象一下，你这个数据系统，你这个业务系统的业务得多简单，所以不可能说 没有范围查找。一般来说，都需要范围查找。
• 那hash索引的缺点，就在于它对范围查找的支持是非常差的。那B+树，能不能够 很好的支撑 范围查找，
  

B+树 范围查找的优势：

• 根据 B+树 对 B树的改造，B+树的结构中的 叶子节点 里面就有这么一个指针，假设要查找col>20，它的查找过程，首先 快速定位到叶子节点中 20这个元素，定位到20之后，那要再查找 大于20 的元素，是不是相当相当easy。那根据BTREE的特性，叶子节点的所有元素，从左到右依次递增，所以 完完全全就可以 先把这个当前大节点的 20后面的 所有元素 放到这么一个结果集里面，然后，通过每个叶子节点之间的指针，指针也存储在这个节点，按照这个指针顺藤摸瓜，把后面的所有的元素，全部放到这么一个集合里面来，如果你面还有元素，就全部放过来了，这样就相当就容易支持了范围查找。

B树 范围查找的缺点：

• B+树叶子节点之间的指针，是对B树的改造之一，原生的B树是没有这一个指针的，那B树 实际上对范围查找，也是支持的不好。如果是B树，没有这个指针，它要支持范围查找，是很麻烦的，大于20，先从根节点 定位到20的这个元素，找到20的元素后， 把当前节点后面这几个元素放到一个集合里面，由于当前节点的后面没有指针，那要再一次，从根节点 再次定位到 后面的元素，放到集合里面后，还要又回到根节点，再次去定位后面的元素，相比于B+树 多麻烦。

基于B+树建立更精准的高性能索引。

• 由于 B+树 相比于 HASH表 对范围查找的支持，和 对原生B树进行的种种改造 后的各种优势，所以，B+树 作为MySQL的底层索引结构，是非常适合 各种 sql查找的 各种业务场景，而且它是一个非常折中，而且 性能又非常高的 一种数据结构。这也就是为什么 使用mysql的innodb存储引擎时，大多数的 数据表底层，会用B+树这种数据结构来存储索引，说白了，它太优秀了。

MySQL的InnoDB表必须有主键索引

• 那前述所有MySQL索引相关的各种概念和原理，都是把主键索引作为案例，或者说，都是单值索引。那如果 一张表 没有任何索引，这里只讨论InnoDB的表。那如果一张InnoDB的表没有主键索引怎么办。
• 实际上，MySQL在设计的时候，它的数据就是按照B+树去组织的，MySQL它要求一张InnoDB的表，必须要有一个主键，而主键一定是索引。如果没有主键，这个数据 它是没办法组织的。当然，我们建一张InnoDB的表，如果没有建任何主键，也能建成功。但没有建主键，不代表它没有主键。
• 当在MySQL中建了一张InnoDB的表，如果没有建主键，MySQL 它会在你的表里面，选择一个 唯一标识索引的列，如果没有 这个唯一标识索引的列，就选择一个数据唯一标识的列，它会找这样的一列，后台默认给你建一个主键，如果它找不到 可以这样标识唯一数据的 那一列，ok，它会在你的表里面 给你默认加一列数据，它来帮你维护这个唯一的主键索引，比如1234…，类似这样的整型自增的列。它在后台来帮我们维护，我们是看不到的。
• 所以，InnoDB的表必须有主键索引 这么一列，来帮我们组织整张表的数据。也就是InnoDB的表，它是必须要有主键索引的，因为它设计如此。

InnoDB主键推荐使用整型且自增的

• 如果InnoDB的表没有任何主键，又没有唯一索引，那么MySQL会默认加一列数据，作为唯一标识的主键索引来维护，而且这个默认的主键就是整型且自增的。
• InnoDB的表必须有主键，那么当我们自己建这个主键时，也推荐使用整型且自增的。早期很多公司会选用uuid作为InnoDB表的主键，这样使用起来相当easy，非常简单。而用uuid刚好与推荐的整型自增，是一个反例情况。下面就通过与uuid对比阐述下整型自增主键的优势在哪里。

为什么要使用整型主键

• 首先，当通过 B+树，或者说 在二叉树中 去查找一个元素的时候，这个查找过程，要从根节点开始，不断的比较大小，找打下一个节点的位置，再去比较。那么，如果在找某一个索引节点元素，中间有大量的比较这样的操作。
• 如果用uuid去做比较的话，uuid本身是一长串字符串，比如abc什么什么，bcd什么什么。字符串比较大小，它要先转换成ASCII码，也就是国标码，然后，按照国标码的那个排序，一个一个去比较一下大小，如果前面几位都相同的话，它还要比较后面的位数，相当麻烦；而 整型是数字，比如1<2，这样的比较。显然，uuid比较大小，绝对没有 整型比较大小快 的效率高，性能好。
• 另外，uuid位数特别长。跟整型来比较的话，整型占用的空间，远远小于uuid。从我们磁盘的那个占用空间的角度来说，如果要节约一点的话，使用整型也更好一点。这对于dbm而言，推荐使用整型，能给我节约空间，我少花一点钱买磁盘。无论从性能，还是磁盘使用率的角度，整型都是更好的选择。

为什么要使用自增主键

B+树索引结构的 从左到右 递增趋势

• 那为什么要使用自增主键，这需要从B+树自身的一个特性说起，B+树，它有一个特点，如果 B+树的每一个小的分叉，其实都符合二叉树的特点，也就是一颗小二叉树，右边节点的这个元素 是大于等于 它的左边节点父元素的；而 左边节点的所有元素，是小于 它的右边节点的父元素。也就是 当插入某一行数据，在维护索引的时候，它会按照 这么一个树的 特性，来维护这个索引结构中的 各个节点。
• 如果有这么一个特性的话，在这棵树里面的每一层的节点，它从左到右 也是按照 递增的顺序来维护；然后，任何一个节点里面，它从左到右，也是一个递增的 一个趋势 来存放元素位置的，这也是B+树的一个特性。那也就是，在每一个节点，它从左到右是依次递增的，然后 这两个节点之间，它也是递增的，就右边的节点的所有元素，肯定是大于等于左边的元素。等于时，因为它中间有个父节点。
• 那么，它的整个结构，它的特性就是从 每一层，到每一个节点，再到每个节点里面，从左到右，都是以 一个递增的趋势，依次递增的，也有可能是相等的。也就是 既然 每一个大节点，大节点 与 大节点 之间，它也是 从左到右 依次递增的 一个趋势，那我 这一个索引元素，在叶子节点里面，从左到右，应该来说，也是一个递增的 趋势。

自增主键的优势

• 那基于B+树对索引的维护的一个特点，就是 从左到右 依次递增的这个特点。如果主键索引是 自增趋势的，也就是 InnoDB的表在插入数据时，是以 主键索引 依次递增的顺序 来插入数据的，这刚好与 B+树 对索引元素 以从左到右 依次递增的这个特点 相吻合。
• 那么再回到uuid，如果用uuid做主键索引的话，因为uuid是随机生成的，所以uuid的大小也是随机的，一定不是自增的。假设一张InnoDB的表里面，现在已经有很多行数据了，那我再插入一行数据，如果是用uuid这种来插入的话，那插入的这一行数据，就要把uuid这一个索引字段 维护到 B+树里面来。
• 根据B+树的特点，它是依次递增维护的，那 新插入的uuid 这个索引，按照这个从左到右依次递增的顺序，它很有可能要放到 B+树中间位置的 某一个叶子节点中，MySQL中每个节点占16KB内存空间，这时 很有可能 这个节点的16KB已经放满了，但是我们现在 又必须要保证 InnoDB表的这个B+树索引结构，这个叶子节点 从左到右依次递增的 这个顺序，那现在要把新加入 这一行uuid数据的 索引字段，维护进B+树里面去，就会引发一个问题，节点的分裂 与 树的自动平衡问题。
• 那如果 这个主键是一个递增的，是一个 递增插入的 顺序，那就永永远远都是 往最后面 去加入元素，那就基本上 很小的概率，会导致 往这个节点 的中间 去插元素，然后，节点没有空间 去存储了，让它去做 一个分裂，分裂后，可能 还要再做 树平衡。而节点的分裂 和 树的自动平衡，都是有大量的运算要做的，对性能 是有很大影响的。所以，dbm会推荐尽量使用自增的主键，这样，插入数据时，永远都在 最后面去加元素，要造成 某一个节点 分裂的概率，非常非常小。

InnoDB表 整型自增主键 示例演示：

• B+树的整型自增主键 可以通过 数据结构学习网站 来演示如下：
  

• B+树 已经插入了1234567，假设再来插入一个9 和10，
  

• 然后，再插入一个8，按道理来说，我们这个7，9，10，这个节点，只能放3个元素，我再插入8，按照递增顺序插入的规则，现在插一个8，那要维护这个索引 按照从左到右依次递增的 这么一个 顺序的话，8应该插入7和9之间这个位置，那插入这个8，看一下会是一个什么样的效果，
  

• 会发现 哇塞，原来那一个大节点，给分裂了，变成两个小节点，而且 树还做了一个 自动平衡，2层 变成了 3层树，节点分裂 和 树平衡 是各个节点 重新排序，那这都是有性能开销的。

联合索引

• 主键索引是单值索引，我们工作中用的最多的就是联合索引。我们公司里面一般都有规范，尽量不要建单值索引，一般是要几个字段，建成一个联合索引，一起来使用。
• 联合索引，就是把几个字段，按照出现的顺序，放到一个节点里面去存储。比如a、b、c，这3个字段，共同建立一个联合索引，KEY `idx_a_b_c` (`a`,`b`, `c`) USING BTREE，那么，联合索引的几个字段，在索引节点里面的存储顺序，就是 a在前面，b在中间，c在后面。
• 实际上，联合索引 和 单值索引 的原理都是一样的，只不过联合索引 的排序规则，或者说，索引节点比较大小的规则，与单值索引 有些区别。联合索引 的排序规则，是按照 联合索引中 字段出现的顺序 逐个来比较，如果第一个字段能区分大小，那就按第一个字段的比较结果来排序。如果第一个字段的值是一样的，那就并列，接下来 以同样的规则 去比较后面的 联合索引字段。

联合索引示例：

• 那假设现在有一个InnoDB的 联合主键索引，有3个字段。结构示意图如下：
  

• 那 联合索引的 3个字段，按照 出现的顺序 a在前面，b在中间，c在后面，那依次的，在索引节点里面10002就是a字段，Staff就是b字段，1996-08-03就是c字段，把几个联合索引 放到一个 这样的节点里面 去存储。然后 非叶子节点里面 存放 索引，那 叶子节点里面，它就放索引和数据，如果是一个InnoDB的联合主键索引，那它就是这么按顺序 来放 abc3个联合索引元素，叶子节点的data里面 放的就是 这个联合索引 所在行的 其他字段。

• 联合索引中的每个字段 在一个节点中 是按照 字段出现的顺序 来放的，联合索引 它的排序规则是 按照 字段 逐个来比较的。比方，第一个字段是整型的，如果能区分大小，那它就按照 第一个字段的比较结果 这么来排序。那如果 我前面的 第一个字段 是一样的，比如 左边子节点的 三行记录，它的第一个字段 是一样的，那就并列排。接下来比第二个字段，第二个字段 是一个字符串，varchar类型的，那就按照varchar类型来比较。那如果第二个字段也一样的，那就第三种情况，左边子节点的 两行数据，这个节点里面，连它的前面 两个字段 都一样，那就按照最后一个字段，去比大小，这就是联合索引 排序的整个过程。

基于联合索引的SQL优化实践：

• 单值索引 底层的数据结构，它的查找索引的过程 相对简单，工作中用的最多的是联合索引，那么理解 底层的数据结构，这个非常重要。MySQL索引常见的优化原则，优化军规，少则几十条，多则上百条，这些乱七八糟的MySQL索引优化原则，80%以上，都可以 从联合索引底层的数据结构 上面去理解。

• MySQL的优化原则有很多很多，想要机械的区背它们，是不切实际的，而且背下来也容易忘，没意义。所有的索引优化原则，都结合索引的底层数据结构，只要思考一遍，把它们思考清楚，从索引的数据结构，去理解它们。你都理解了，就在也不要去背了，已经深深的印在你的脑海里。

• 那现在就通过 联合索引 的底层数据结构，去理解 mysql最佳优化的手册，其中有一个叫做 全值匹配，以及最左前缀法则。
  

• 比方说 这张表，有3个字段 name，age，position，建了一个联合索引，KEY `idx_name_age_position` (`name`,`age`, `position`) USING BTREE，如下图：
  

• 那分析，EXPLAIN SELECT * FROM employees WHERE age = 22 AND position=‘manager’;，这一条sql，它是不会走索引的，这里面就涉及到 最左前缀法则。如果索引了多列，要遵守最左前缀法则，它指的是 查询从 索引的最左前列开始 并且不跳过索引中间列。结合 联合索引 底层存储结构，就可以很轻松 把这条最左前缀原则 思考清楚。

分布式主键实现简述：

• 如果是单库主键，可以很轻松的使用整型自增主键。那如果是分布式主键，系统将来要做了分库分表，或者说，做分布式架构的一个改造。那分库分表就好像没办法用自增主键了，因为一张表拆到不同库，它还怎么自增，或者说用整型自增。而uuid又有那么多问题，性能问题，以及它浪费空间。
• 那实际上对于分库分表，这种场景，对于分布式架构来说，也有使用整型的方式来生成主键，一是 可以用雪花算法，它也是一个自增的整型的一个主键，但它比较复杂。当然，其实除了雪花算法 支持分库分表之外，还可以用redis 来实现自增的主键，而且它还可以方便的 运用在我们的 分库分表场景。
• 对于 并发很高的，有几百张表，而且每张表的操作 都很频繁。那怎么对redis去优化，用redis来做，支撑这种高并发系统，多库多表之间，频繁高并发 生成主键的，这么一个场景。就是另外一个话题了。
• 总之，对于分库分表，分布式架构的场景。不一定非要用雪花算法，用redis也是可以搞定的。

.参考文献

图灵课堂-诸葛老师

1.一文吃透MySql的底层数据结构（满满都是干货）.

2.mysql底层数据结构.

3.MySQL底层数据结构.

4.MySQL数据页为什么是16K mysql数据页存储数据结构.

5.B+树相关数据结构和算法.

6.关于查找树的总结【BST、AVL、红黑树、B树、B+树】.

7.平衡二叉树与红黑树.