MySQL索引

mysql数据库的索引分为B+树和Hash

Hash

对字段的值进行hash，然后存在索引中，在查找的时候对查找对象进行hash得到那一行数据的地址，进而查找那一行的数据

slect * from table where f_sp_name='天才'

但是当遇到这样的sql时就无能为力了

slect * from table where f_sp_name like '%天才%'

hash的特点是可以快速的精确的查询，但是不能范围或者模糊查询
就算是做成了索引，速度也很慢，需要全表扫描，比较适合等值查询的情况，只有key，value的情况，像redis

上面的无序的hash表，还有有序数组的索引，在等值查询和范围查询的时候都很优秀，但是存在缺点，增删改数据需要移动结构，成本高，适合用来做静态存储引擎，用来保存静态数据，即确定不会发生修改的数据，比如，2019的年度账单，淘宝购买记录，订单快照之类的

二叉树

二叉树我们可以做到有序，可以做到范围查找

但是它的时间复杂度为O(log(N)),为了维持这个时间复杂度，更新时间也得是O(log(N))，那就得保证这个树为完全平衡二叉树，但是平衡二叉树并不适合做索引，索引不止在内存里存储，还要落地持久化，如果数据多了树高会很高，查询的成本就会随着树高的增加而增加

而且为了节省成本很多公司的磁盘还是采用机械硬盘，这样一次千万级别的查询差不多就要10秒，emmmm

B树

在同样的的元素下，B树表示要比完全平衡二叉树矮，因为B树中的一个节点可以存储多个元素

B+树

看一下B+树的结构

在同样的元素的元素，B+树比B树更胖，因为B+树种的非叶子节点会在冗余一份在叶子节点中，并且叶子节点之间用指针相连

B树一个节点可以存储多个元素，相对于完全平衡二叉树整体的树高降低了，磁盘I/O效率更高

B+树是B树的升级版，只是把飞叶子节点冗余了提高范围查找时的效率，因为会有指针指向下一个节点的叶子节点

总结：B+树这种数据结构作为索引，可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的

那么一个B+树中的一个节点多大合适？

B+树中的一个节点为一页或者页的倍数最为合适

为什么？

如果一个节点的大小小于1页，那么读取这个节点的时候会读取到第二页，但其实第二页是不需要的，造成资源上的浪费

如果节点的大小大于大于1页，读取之后可能会发现目标不在第一页，需要都第二页才能找到自己需要的数据，造成浪费

页的概念

mysql 的基本存储结构是页（记录都存在页中）

• 各个数据页可以组成一个双向链表
• 每个数据页的记录又可以组成一个单向链表
• 每个数据页会存储在自身的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后在遍历该槽对应分组中的记录即可快速找到指定的记录
• 其他列（非主键）作为搜索条件：只能从记录最小依次遍历单链表中的每一条记录

所以在我们写这样的sql的时候并没有进行任何sql的优化，

select * from user where name = 'Alex'

• 定位记录所在页（遍历双向链表找到所在页）
• 在所在页中找到对应的记录（由于不是根据主键只能遍历所在页的单链表）

数据量大的时候很慢

回表

当我们现在有一个主键为ID的索引和一个普通的name字段的索引我们在普通字段上搜索

select * from user where name = 'Alex'

执行过程 先查询name索引上“Alex”，然后找到他的ID为22，最后通过主键索引找到ID为22的对应的数据，可以使用覆盖索引避免

覆盖索引：刚才我们是 select * ，查询所有的，我们如果只查询ID那，其实在Name字段的索引上就已经有了，那就不需要回表了

覆盖索引可以减少树的搜索次数，提升性能，他也是我们在实际开发过程中经常用来优化查询效率的手段。

很多联合索引的建立，就是为了支持覆盖索引，特定的业务能极大的提升效率。

索引最左匹配原则

• 索引可以简单的如一列，也可以是多列，即联合索引

• 如果是联合索引，那么key也有多列组成，索引只能key是否存在或者说相等，到遇到范围查询的时候（<,>between,like %???%）等情况的时候，不能进一步匹配，退化线性查询

• 因此，列的排列顺序决定命中索引的列数

• 如有索引 (a,b,c,d)，查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中a、b、c，无法命中d。(c已经是范围查询了，d肯定是排不了序了)

总结

• 最左前缀匹配原则。这是非常重要、非常重要、非常重要（重要的事情说三遍）的原则，MySQL会一直向右匹配直到遇到范围查询 （>,<,BETWEEN,LIKE）就停止匹配。
• 尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col)/COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。
• 索引列不能参与计算，尽量保持列“干净”。比如， FROM_UNIXTIME(create_time)=‘2016-06-06’ 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ：create_time=UNIX_TIMESTAMP(‘2016-06-06’)。
• 尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。
• 单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引(经指正，在MySQL5.0以后的版本中，有“合并索引”的策略，翻看了《高性能MySQL 第三版》，书作者认为：还是应该建立起比较好的索引，而不应该依赖于“合并索引”这么一个策略)。
• “合并索引”策略简单来讲，就是使用多个单列索引，然后将这些结果用“union或者and”来合并起来