Mysql索引的前世今生

今天佛系的叫兽去面试PHP工程师，被问了一个关于mysql索引的问题，他有点懵逼……

 

问：有没有用过mysql的索引（开玩笑，搞php的怎么会没用过索引）

答：有

问：用那种索引

答：BTree（这还是记得的）

问：组合索引哔哔叭啦…?(问题问得很奇怪，没懂什么意思，没记住)

答：当搜索条件中有多个字段的时候，组合成一个索引

……（经过一番你来我往的装逼）

结论：叫兽对组合索引还是有点误解，学艺不精，只知其一不知其二。叫兽果然是佛系啊，干了这么多年的php，与mysql数据库打过无数次的交道，也多次遇到组合索引的问题，但是每次都是不了了之，没有深究。

叫兽觉得这次一定要把组合索引搞个一清二楚！

时间复杂度概念

先从BTree开始

BTree是Balance Tree 的缩写，翻译过来就是平衡树，这没什么好说的

属于算法的范畴，找《算法导论》来看看

书中第五部分 高级数据结构的第18章 B树有记载，叫兽感觉找到了答案

然并卵！看了几段文字，叫兽哪里看得懂这些，“每棵含n个结点的B树的高度为O(lgn)”这说的是人话吗？叫兽一脸懵逼。

叫兽知道O(lgn)表示的是好像是复杂度为lgn的意思，先翻到前面的去找找看

 

书中第28页有载，然并软！看不懂！经过一番百度，找到一个看得懂的，原来是算法渐进符号

算法分析渐进符号(O、o、Θ、Ω、ω)总结：

渐近记号包括：

(1)Θ（西塔）：紧确界。            相当于"="

(2)O （大欧）：上界。              相当于"<="

(3)o（小欧）：非紧的上界。       相当于"<"

(4)Ω（大欧米伽）：下界。          相当于">="

(5)ω（小欧米伽）：非紧的下界。 相当于">"

给出一些例子：

O(n^2)可以是n,2n,1,2n^2等。

Θ(n^2)可以是n^2,3n^2等。

ω(n^2)可以是n^3,n^10等,但不能是n^2。

Ω(n^2)可以是n^2,n^3,n^10等。

o(n^2)可以是n,1,3n等,但不能是n^2

算法的时间复杂度记做T(n)=O(f(n))

算法的时间复杂度(大O阶)的计算方法为： 

1、用常数1取代运行时间中的所有加法常数。
2、在修改后的运行次数函数中，只保留高阶项。
3、如果最高阶项存在且不是1，则去除与这个项相乘的常数。

 

举个实例来说明如何计算程序的时间复杂度：

int n = 100000; //执行了1次
for(int i = 0; i < n; i++){  //执行了n+1次
  for(int j = 0; j < n; j++) //执行了n*(n+1)次
  {
      printf("i = %d, j = %d", i, j); //执行了n*n次
  }
}

for(int i = 0; i < n; i++){  //执行了n+1次
  printf("i = %d", i); //执行了n次
}

printf("Done"); //执行了1次

 

按上面推导"大O阶"的步骤我们先来第一步："用常数1取代运行时间中的所有加法常数"，则上面的算式变为:
执行总次数 = 2n^2 + 3n + 1;

第二步："在修改后的运行次数函数中，只保留最高阶项"，这里的最高阶项是n的二次方
所以算式变为：
执行总次数 = 2n^2;

第三步："如果最高阶项存在且不是1，则去除与这个项相乘的常数"，这里n的二次方不是1所以
要去除这个项相乘的常数算式变为:
执行总次数 = n^2;

因此，最后我们得到上面的那段代码的算法时间复杂度表示为: O(n^2); 

总算搞懂时间复杂度这个概念了

 

B树的时间复杂度计算

回到前面那个问题，为什么B树的时间复杂度是O(lgn)，要弄懂这个，就得分析B树纠结是个什么样的算法了

因为B树和红黑树类似，所以第18章B树 没有给出推导过程，得去看看红黑树

红黑树在第13章，书中云：“红黑树是一种二叉树，但在每个结点上增加一个存储位表示结点的颜色，可以是RED（红）或（黑）。”，于是就得先看第12章二叉树了。

……

叫兽决定先去学习算法导论了，学完了再来解决索引的问题，就是这么佛系！

------------------------------------------------------------------------------

二叉树的查询的时间复杂度就是其深度，二叉树的深度，因为二叉树第一层是1个，第二层是2个，第三层是4个，第x层就是个。

 n = 1+2+4+…+

n =  +  +  + … +

 2n=       +  + … +

 2n -n = -1 +

 n+1 =

 x = 

所以时间复杂度可以写成 O()

---

mysql索引的数据结构 - 解决为什么索引会失效的底层原理

比如：没遵循最佳左前缀法则、范围查询的右边会失效、like查询用不到索引等等

单值索引

组合索引

从本质上来说，联合索引也是一个B+树，和单值索引不同的是，联合索引的键值对不是1，而是大于1个。

a, b 排序分析

a顺序：1，1，2，2，3，3

b顺序：1，2，1，4，1，2

大家可以发现a字段是有序排列，b字段是无序排列（因为B+树只能选一个字段来构建有序的树）

一不小心又会发现，在a相等的情况下，b字段是有序的。

大家想想平时编程中我们要对两个字段排序，是不是先按照第一个字段排序，如果第一个字段出现相等的情况，就用第二个字段排序。这个排序方式同样被用到了B+树里。

分析最佳左前缀原理

先举一个遵循最佳左前缀法则的例子

select * from testTable where a=1 and b=2

分析如下：

首先a字段在B+树上是有序的，所以我们可以通过二分查找法来定位到a=1的位置。

其次在a确定的情况下，b是相对有序的，因为有序，所以同样可以通过二分查找法找到b=2的位置。

再来看看不遵循最佳左前缀的例子

select * from testTable where b=2

分析如下：

我们来回想一下b有顺序的前提：在a确定的情况下。

现在你的a都飞了，那b肯定是不能确定顺序的，在一个无序的B+树上是无法用二分查找来定位到b字段的。

所以这个时候，是用不上索引的。大家懂了吗？

范围查询右边失效原理

举例

select * from testTable where a>1 and b=2

分析如下：

首先a字段在B+树上是有序的，所以可以用二分查找法定位到1，然后将所有大于1的数据取出来，a可以用到索引。

b有序的前提是a是确定的值，那么现在a的值是取大于1的，可能有10个大于1的a，也可能有一百个a。

大于1的a那部分的B+树里，b字段是无序的（开局一张图），所以b不能在无序的B+树里用二分查找来查询，b用不到索引。

like索引失效原理

where name like "a%"

where name like "%a%"

where name like "%a"

我们先来了解一下%的用途

• %放在右边，代表查询以"a"开头的数据，如：abc

• 两个%%，代表查询数据中包含"a"的数据，如：cab、cba、abc

• %放在左边，代表查询以"a"为结尾的数据，如cba

为什么%放在右边有时候能用到索引

• %放右边叫做：前缀

• %%叫做：中缀

• %放在左边叫做：后缀

没错，这里依然是最佳左前缀法则这个概念