1.mysql索引优化实践

索引的本质解析

慢查询现象：一条sql原来执行几十毫秒，现在却执行了几十秒甚至更长。
这种现象，最先想到的就是加索引.不要遇到问题就直接分库分表，可能加索引就能搞定。

比如我有2列7行的一张表。如果要查找col2=89的数据，就是写“select * from t where t.col2 = 89”，在没有索引的情况下，会一行一行的比对，直到找到结果或者全部比对一遍之后找不到。因为数据是存在磁盘上的，不做任何优化的话，全部比对一遍会引发大量的磁盘io。
如果像上图一样，使用二叉树作为索引，插入的时候，往二叉树中插入，查询的时候就能通过二叉树快速的查找我们的元素。比如查找89，从根节点开始，如果大于根节点那就去右子树查找，如果小于就去左子树查找，重复这个过程直到找到。
每一个节点都是k-v键值对，k是值，比如89，77，value就是数据在磁盘上的位置（理解为地址指针）。
总结：不加索引，逐行查找，加了索引，按照数据结构的特性去查找，大大减少查找遍历次数，
注意：mysql的底层不是用的二叉树。一般是b树或者b+树。因为二叉树在某些场景下有弊端。
推荐一个好的数据结构学习网站：

使用这个工具测试二叉树作为索引的弊端，假设我们的数据从1开始递增。

如果数据单边增长，二叉树就变成链表了，失去了二叉树的意义。链表的查找又变成从头开始遍历了，跟不加索引一个效果。

红黑树和B+树画图解析

JDK里面有个集合叫做hashmap，jdk1.8之后把之前的底层的链表实现优化为了红黑树实现。
红黑树现象：只要单边的元素多到2层就会帮你自动平衡一下，解决了传统二叉树对单边增长的数据不友好的问题。如下图：

红黑树的奥义在于这种自动的平衡，红黑树是升级版本的二叉树，叫二叉平衡树。
但是红黑树，也没有被mysql选中作为索引的数据结构实现，有些场景是不适合的。比如当数据量上来之后，比如500W，树的高度会十分的高，如果查抄的元素位于底部的叶子节点，那么经历的磁盘io次数也会很多，查找的效率也很低。
总结：红黑树无法解决在数据量很大时树的高度问题，高度高了查找效率就降低下来了。
希望高度越小越好，又想要大数据量，那么可以考虑多叉树（每个节点多几个索引）。思路也很简单，纵向有限制，高度不能太高，就横向发力，增加叉的数量。

B树的思想，一个节点有很多数据，不连续的就空出来指针指向子节点，一次性load一个巨型的节点，然后从内存中找到想找的元素或者该去哪个子节点的指针。这种比多次load数据效率要高。
但是，凡事都有个度，不能把所有数据都放进一个节点里，能不能载入内存是个问题，时间也是问题，哪怕载入了，就又变成了链表索引了。从计算机组成原理的角度来讲，一次磁盘io没办法取出来太大的元素。所以一个节点大小设置要适中，不能太大，也不能太小。mysql设置的节点大小是16k：

mysql不是选的b-树，是对b-树做了改造。得到了一种b+树的数据结构。

非叶子节点都是冗余索引，叶子节点包括所有的索引字段。冗余索引为了效率更高。
为什么只有叶子节点才存储data？
因为非叶子节点大小要适中，data挪走了之后，就能横向存更多的索引了。
mysql主键：bigint。叶子节点大小是16k，一个索引是4b，可以放1170个索引，如果树的高度是3，全部放满了，则可以存放多少数据？
假设一个叶子节点有16个索引+data的组合。
公式：1170117016=21,902,400‬ 大于2kw。
也就是说2kw的数据，可以把树的高度控制在3，效率还是十分可以的。这就可以解释为什么几千万的表几百毫秒就出结果了。经过2-3次的磁盘io就能找到结果。如果不加索引这个io次数会十分恐怖。

myisam存储引擎索引实现解析

扫盲：数据库，表，索引，表里的数据都是在磁盘上的data文件夹！

存储引擎是形容表的，不是数据库！尽管数据库有设置存储引擎的选项，但是最终还是以表为主。下图为图形化工具的设置界面。

看一下建表的语句也可以证明：

以myisam存储引擎的表为例，磁盘上有三个文件：

frm：表的框架性质的信息存储在这个文件。
myd：存储的是数据行的记录。
myi：表的索引所在的位置，i是index索引。

数据文件存在myd文件，索引存在myi文件这是myisam存储引擎的特点。
b+树的data域存储的是磁盘的地址指针。

对于innodb存储引擎的表，数据文件如下：只有2个。

ibd：数据文件跟索引文件的合并。data域不再是地址指针，而是该条记录的所有数据。

innodb存储引擎索引实现解析

innodb这种索引和数据放在一个文件中的形式，不用回表了，省去了这一步骤。

—扫盲—
聚集（聚簇索引）索引：innodb的主键索引，就可以认为是一个聚集索引。叶节点包含了完整的数据记录。
非聚集索引：myisam的主键索引就是非聚集索引。
聚集和非聚集可以理解为指的是索引文件和数据文件是否是一个文件。
为什么innodb表必须要有主键，并且推荐使用整型自增的主键？
因为设计如此，innodb的主键索引就是用b+树来组织的（也是必须用b+树）。所以必须要有主键。没有主键数据就没有一个结构去存储。
如果你没有建立主键，那么mysql会自动选择一个不重复的字段作为innodb表的隐式主键。用这个字段来维护整个表里的所有数据。
如果整张表里的字段都选不到唯一的，那么mysql会在后台给你这张表生成一列唯一的数据。有点类似rowid字段。
为什么innodb的表主键推荐用整型自增？有的还是用uuid(非整型自增)，这个有没有什么说法?
之所以是整型，是因为load节点到内存中之后，需要不断的比大小，整型的比较效率是很高的，比非整型（字符串）效率高，所以主键推荐用整型。字符串比较大小，需要转换成ASCII码来比对，效率很低（逐个字母比对）。从存储空间上来讲，整型也有更多的优势，同样空间可以存更多。

索引是怎么支撑千万级别表的快速查找

索引列推荐为整型可以理解了，那么为什么还要自增呢？
因为b+树相比较b树，叶子节点之间还有指针的关联，这是他俩的区别。
扩展一下：mysql表的索引除了有BTREE还有HASH。hash是每插入一次元素，就会对这个元素做一次hash运算，把运算的结果值存放到另一张hash表里面，这个表里面也有数据的磁盘地址，可以这么理解：查找的时候对一行的某个值做一次hash运算，就能快速的找到这一行所在的磁盘文件地址，这就是hash索引。mysql底层实现了自己的hash散列算法。
hash算法比BTREEE方法快得多（上亿的数据也很快，hash冲突mysql也解决的很好），为什么大部分还是用BTREE呢？
因为他对范围查找就无可奈何了，只能具体的查找where col = xxx的场景。但是where col > 6 就完蛋了。
B树应对范围查找也很乏力，但是B+树就可以，就是因为叶子节点有指针（双向指针，图不准确）的关联，而且有递增的趋势。

上面提的都是单值索引，工作中用的比较多的还有联合索引。那么联合索引的底层结构长啥样？
如果能想明白，那么按照原因去写sql，会比单纯的背一些sql优化方法要好很多。理解 = 不用背百分之80的索引优化原则。

mysql索引优化最佳实践

联合索引如上图所示，先从左侧开始匹配，如果第一个字段相同，就往后找。
联合索引，必须加上第一个索引字段，只用后面的字段不触发索引。查询速度得不到提升。

结合一下最左前缀法则，可以从底层数据结构的角度理解联合索引。
如何确定你的sql有没有走索引？