MySQL索引知识点整理

MySQL索引

1、索引概念

mysql官方对索引的定义是：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级的查找算法，这种数据结构就是索引。

如下图所示：

在没有索引的情况下，一共有七行两列记录，最左边表示的是数据记录的物理地址。为了加快col2列的查找，数据库系统可以维护一个右边那样的二叉查找树。
建立类似于右边的索引之后，每个节点都分别包含索引键值和一个指向数据物理地址的指针，这样就可以使用二叉查找树快速获取到相应的数据。

索引往往都是很大的，不可能全部存储在内存当中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用道具。

2、索引的优势和劣势

优势：
1、索引就像书籍的目录，可以提高检索的效率，降低数据库的IO成本
2、通过索引对列排序，降低数据的排序成本，降低CPU的消耗。

劣势：
1、索引实际上也是一张表。表中包含主键和索引字段，并且指向实体类的记录，所以索引是要占用空间的。
2、索引虽然提高了查询的效率，但是对UPDATE、INSERT、DELETE等对记录产生影响的操作不太友好，降低了更新表的速度。因为MySQL不仅要保存数据，还要保存索引文件每次更新之后，添加了索引列的字段，会调整因为更新之后所带来的键值变化后的索引信息。

3、索引的结构

索引是在存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。MySQL目前提供下面几种索引：

• BTREE索引：最常见的索引类型，大部分所以都支持B树索引。
• HASH索引：只有Memory引擎支持，使用场景简单。
• R-tree索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。
• Full-text（全文索引）：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引 ，InnoDB从Mysql5.6版本开始支持全文索引。

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH索引	不支持	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

平常我们说的索引，如果没有特别的指明，都是B+树（多路搜索树，不一定是二叉树）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+树索引，统称为索引。

3.1 BTREE结构

BTREE也叫多路平衡搜索树，一颗m叉的BTree特性如下：

• 树中每个节点最多包含m个孩子。
• 除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子（ceil是向上取整）。
• 若根节点不是叶子节点，则至少有两个孩子。
• 所有的叶子节点都在同一层。
• 每个非叶子节点由n个key与n+1个指针组成，其中指针的数量在一个范围之内，[ceil (m/2) - 1] <= n <= m-1

以5叉BTree树为例，key的数量：公式推导[ceil (m/2) - 1] <= n <= m-1。所以2 <= n <= 4。
当 n < 4时，中间节点分裂到父节点，两边节点分裂。（ceil是向上取整）
也就是说指针的数量最多是5，所以key的数量最多是4。

比如说下面这样：
插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例。

演变过程如下：
（1）插入前4个字母 C N G A

那么有4个key和5个指针

（2）插入H之后，n > 4，那么这里就是其中重要的一个点，中间元素G字母向上分裂到新的节点。

（3）继续插入E，K，Q，这个过程中不需要分裂

插入E的时候，先和G比较，比G小，到左分支，其他两个字母也如此操作。

（4）插入M，大于G，到右分支，右分支需要放到K和N中间，此时n > 4，中间元素是M，那么向上分裂到父节点G。

（5）插入F、W、L、T，这四个字母不需要分裂

（6）插入Z，Z大于M，到右分支，放在W之后，此时当前节点的n > 4，中间元素是T，T向上分裂到父节点中。

（7）插入D，中间元素D向上分裂到父节点中。然后插入P、R、X、Y，不需要分裂

（8）最后插入S，应该加到NPQR之后，所以中间元素Q分裂到父节点，此时父节点DGMQT，n > 4，中间元素M继续向上分裂成为父节点。

到这里，BTREE树就已经构建完成了，BTREE树和二叉树相比，查询数据的效率更高，因为相对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度更快。

3.2 B+TREE 结构

B+Tree是BTree的变种，B+Tree和BTree的区别为：
（1）n叉B+Tree最多有n个key，而BTree最多有n-1个key
（2）B+Tree的叶子节点保存的所有的key信息，按照key的大小顺序排列
（3）所有的非叶子节点都可以看做是key的索引部分。

下面是B+树的结构

由于B+树只有叶子节点保存key信息，查询任何key都要从根节点走到叶子节点。所以B+树的查询效率更加稳定。

3.3 MySQL中的B+树

MySQL索引数据结构对经典的B+树进行了优化。在原有的B+树的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+树，提高区间访问的性能。

MySQL中的B+树示意图：

4、索引分类

单值索引：一个索引包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：一个索引包含多个列

5、索引语法

关于这里我整理了一篇博客

MySQL索引指令速查

6、索引的设计原则

• 对查询频次较高，并且数据量比较的表建立索引
• 索引字段的选择。最佳候选列应该从where子句的条件中提取，如果where子句中的组合比较多，就应该挑选最常用、过滤效果最好的列组合。
• 使用唯一索引，区分度越高，使用索引的效率就越好。
• 索引虽然可以提高查询效率，但是也不是越多越好，索引越多，维护索引的代价也就越大。对于插入、更新、删除等DML操作比较频繁的表来说，索引越多，DML操作的效率越低，消耗的时间也就越多。除此之外，如果索引多了的话，MySQL也会犯选择困难症，虽然最终会找到一个合适的索引，但是提高了选择的代价。
• 使用短索引，表创建之后也是使用硬盘存储的，因此提升索引的IO效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在固定大小的存储块内，可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的IO效率。
• 使用左前缀。N个列组合成的组合索引，相当于创建了N个索引，如果查询时where子句中使用了该索引的前几个字段，那么这条查询的SQL就可以使用组合索引提高查询效率。

下面举个创建复合索引的例子：

CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

上面这句话相当于
对name创建索引
对name，email创建索引
对name，email，status创建索引