MySQL中的索引

• 什么是索引？

索引是帮助数据库高效获取数据的数据结构。

• MySQL中有哪些索引？
  • 从数据结构角度
    • B+Tree：BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型；
    • R-Tree：R-TREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找；
    • HASH：由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引；
    • FULLTEXT：即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
  • 从物理存储角度
    • 聚集索引：以InnoDB作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，引擎也会帮你键一个隐式的主键。这是因为InnoDB会把数据放在B+树中，而B+树的键值就是主键，在B+树的叶子节点中，存储了表中的所有数据，这种以主键作为B+树索引的键值而构成的B+树索引我们称之为聚集索引；
    • 非聚集索引：以主键以外的列值作为键值构建的B+索引，我们称之为非聚集索引；
    • 非聚集索引和聚集索引的区别在于非聚集索引的叶子节点不存储数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程我们称之为回表。
  • 从逻辑角度
    • 主键索引：主键索引是一种特殊的唯一索引，不允许有空值；
    • 普通索引或单列索引：是最基本的索引，它没有任何限制，仅加速查询；
    • 多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合；
    • 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；
    • 空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。
• 索引有什么优缺点？
  • 优点：
    • 大大加快数据的检索速度;
    • 创建唯一性索引，保证数据库表中每一行数据的唯一性;
    • 加速表和表之间的连接;
    • 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
  • 缺点：
    • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；
    • 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大；
    • 以表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度。
• 我们常用的索引有哪些？
  • 最常用的应该是B+树索引了，这里着重介绍一下。在说B+树之前我们先说一下另外几个相关概念，因为B+树跟他们有一定渊源。
  • AVL树是一种平衡二叉树（又被叫做Self-Balancing Binary Search Tree），因为他的发明者名字是Adelson-Velskii 和 Landis，平衡二叉树的定义如下：
    • 非叶子节点最多拥有两个子节点；
    • 非叶子节值大于左边子节点、小于右边子节点；
    • 树的左右两边的层级数相差不会大于1;
    • 没有值相等重复的节点。
    • 下面这张图片中左边的是AVL树，它的任何节点的两个子树的高度差别都<=1；而右边的不是AVL树，因为7的两棵子树的高度相差为2(以2为根节点的树的高度是3，而以8为根节点的树的高度是1）

• AVL树的查找、插入和删除在平均和最坏情况下都是O(logn)。如果在AVL树中插入或删除节点后，使得高度之差大于1。此时，AVL树的平衡状态就被破坏，它就不再是一棵二叉树；为了让它重新维持在一个平衡状态，就需要对其进行旋转处理。
• B树（Balanced Tree）和平衡二叉树稍有不同的是B树属于多叉树又名平衡多路查找树，通常我们说的m（m≥2）阶的B树，他必须要满足如下条件：
  • 每个节点最多有m个子节点；
  • 除根节点和叶节点外，B树中的每个节点至少包含m/2个子节点；
  • 根节点必须至少有2个节点；
  • 所有叶节点必须处于同一级别。
  • 如下图是一棵3阶B树:

• • • 为什么会出现B树呢？
      • 我们知道AVL树和B树的时间复杂度都是O(log2n)，那么这种情况下为什么选择用B树而不用AVL呢？我们假设B+树一个节点可以有100个关键字，那么3层的B树可以容纳大概1000000多个关键字（100+101*100+101*101*100）。而AVL要存储这么多数据可能至少要20层。所以使用B树相对于AVL可以大幅度降低IO操作。
  • 下面说一下本篇的重点---B+树
    • B+树是B树的一种变形，在B树上做了一些改进，下面是B树和B+树的区别：
      • B树中同一键值不会出现多次，它有可能出现在叶子节点上，也有可能出现在内节点上；而B+树的键一定会出现在叶子节点上，同时也有可能在叶子节点中重复出现。简单来说，B+树的内节点存储的都是键值，键值对应的具体数据都存储在叶子节点上。由于B树的每一个节点都存储了真实的数据，会导致每一个节点存储的数据量变小，所以整个B树的层数就会相对变高，当数据量变大之后，维护代价是比较大的，而且层数越高，搜索或修改的性能就会越低；而在B+树中的内节点中，只存储键值，相对而言，一个内节点存储的记录个数比B树多很多。由于B+树是横向扩展的，所以随着其中 数据量的增长，最终会成长为一个矮胖子，不像B树一样是纵向扩展，最终只会变成一个 瘦高个子。这样相对而言，B+树在搜索时，从上到下直到叶子节点只需要遍历层数个节点而已，因此性能比较高。
      • B树的查询效率与键在B树中的位置有关，（在叶子节点的时候）最大时间复杂度与B+树相同，最小时间复杂度为1（在根节点的时候），而B+树的复杂度对某个建成的树是固定的。
      • B树中，键的位置不固定，且键在整个树结构中只出现一次，虽然可以节约存储空间，但却使得插入，删除等操作复杂度明显增加。而且性能不平衡，有可能很快找到合适的位置，也有可能需要做比较多的IO操作才能找到。而B+树相对来说是一种较好的折中，因为内节点相对叶子节点而言，相当于是一个索引，在插入的过程中，只需要通过在每一层搜索一个节点，依次找到叶子结点之后，在叶子节点处做插入操作即可，只是在遇到一个节点存储满了的情况下会进行B+树分裂，但总体而言性能还是比较稳定的。
      • B树中，所有的数据都只存储一份；而B+树中，除了存储了所有数据的叶子节点外，还有存储键值数据的内节点，所以，在占用空间量方面，B+树比B树会多占用一些空间，这部分空间就是B+树内节点的所有空间，但B+树通过这种方式提高了整体性能，更适合于性能要求很高的文件检索。
      • 内部节点中的key都按照从小到大的顺序排列，对于内部节点中的一个key，左树中的所有key都＜它，右树中的所有key都 ≥它。叶子节点中记录也按照key的大小排列。
      • 每个叶子节点都存有相邻叶子节点的指针，叶子节点本身依关键字大小自小而大顺序链接。
      • 下图是一个3阶B+树（实际的B+树叶子节点是双向链表 ）

• • • B+树相比B树的优势：
      • b+树中间节点存储的是数据在叶节点的索引，可以包含的关键字更多,单一节点存储更多的元素，使得查询的IO次数更少；
      • 所有查询都要查找到叶子节点，查询性能稳定；
      • 所有叶子节点形成有序链表，便于范围查询。
      • B+树与B树最大的不同是内部节点不保存数据，只用于索引，所有数据都保存在叶子节点中。
• 一个页面至少要插入几条数据？为什么？
  • 《MySQL运维内参》p99-p109有给出解释，但未理解。
  • 但是猜想这样规定是为了避免B+树层级过高，InnoDB要求所有的数据页至少可以容纳两条记录。
  • 试想一下，极端条件下，如果每个页面只存储一条记录，那么我们得到的是一个什么结构呢？是一个既包含索引又包含数据的链表了吧。链表的时间复杂度我们知道是O(n)，随着的数据量的不断增加，查询的时间也会随之大幅攀升，已经失去索引的实际意义。这不是我们希望看到的，更不是MySQL所允许的。
  • 如果存两条记录，其实这跟AVL树就挺像了，也是棵树，只不过在数据量特别大的时候，树一定也很高，需要更多的IO操作，这样虽然被MySQL接受，但不推荐。
• 一棵树最多存储多少条数据呢？
  • 这个我们可以估算一下，下面以user表为例进行测算：
  • 我们知道InnoDB引擎有自己的最小存储单元，称之为页，一个页的默认大小16KB;
  • 确定一个主键的所占空间大小，user表的主键id是bigint（8B)，指针大小在 InnoDB 源码中设置为 6 B, 8B + 6B = 14B;
  • 确定记录数据大小

记录固定属性	大小
File Header	38B
Page Header	56B
Infimum + supremum	26B
User Records	用户记录，即行记录，见下表
Free Space	空闲空间，大小不固定，给它50B
Page Directort	页目录，大小不固定，给它50B
File Trailer	文件结尾信息，固定8字节。
总计	228B

字段名称	大小
user_id	8B
email	128B
password	255B
test_account	1B
user_valid	1B
auth_key	32B
registration_ip	45B
blocked_at	4B
create_time	8B
update_time	8B
总计	490B

• • 从上面两个表格可以看出要存储一条记录共需要228B + 490B = 718B，16KB / 718B ≈ 22,也就是说一个叶子节点可以存储22条数据；
  • 确定一个内节点可以存储多少个key，16KB * 1024 / 14 = 16384B / 14 ≈ 1170个；
  • 也就是说根页面（高度为1）可以存储1170个key，如果高度为2的话，可以存储1170 * 22 = 25740条数据；
  • 三层的话大概在 1170 * 1170 * 22 = 30115800条，三层高度存了三千多万条数据！即便叶子结点平均每行记录占内存1KB，也就是每个叶子节点存储16条记录， 1170 * 1170 * 16 = 21902400,两千多万条！要达到这个量级，看上去还需几十年乃至几代人的不懈努力。💪💪💪
• 索引使用的原则：
  • • 全值匹配；
    • 最左前缀；
    • 不在索引列上做任何操作(包括但不限于，计算，函数，类型转换)，会导致对应列索引失效；
    • 不适用索引中范围条件右边的列；
    • 尽量使用覆盖索引；
    • 使用!=或者not in 的时候会全表扫描；
    • is null，not null也无法使用索引；
    • like尽量匹配‘a%’ 如果实在不行尽量转换覆盖索引；
    • 字符串不加单引号容易索引失效(如整形会隐式类型转换)；
    • 少用or，在某些引擎或者版本下 or 并不会匹配对应的单值索引，尽量使用 union。
• 参考资料
  • http://blog.codinglabs.org/articles/theory-of-mysql-index.html
  • https://www.cnblogs.com/chafanbusi/p/10647110.html
  • https://www.cnblogs.com/skywang12345/p/3576969.html
  • https://www.cs.usfca.edu/~galles/visualization/AVLtree.html（AVL树演示）
  • https://www.cs.usfca.edu/~galles/visualization/BTree.html（B树演示）
  • https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html（B+树演示）
  • 《MySQL运维内参》