MySQL必须搞懂的索引

定义

• MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构

索引类型

1. 从数据结构的角度
   • B+Tree索引：主要的索引方式，后面详解，查询时间复杂度O(log(n))
   • hash索引：检索效率非常高，仅能满足=，in这些方式查询，不能使用范围查询，只有Memory存储引擎显示支持hash索引
   • FULLTEXT索引：myisam和innodb都支持
   • R-tree索引：用于对GIS数据类型创建SPATIAL索引
2. 从物理存储角度
   索引是通过二叉树的数据结构来描述的，我们可以这么理解
   聚簇索引：索引的叶节点就是数据节点。
   而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
   1. 聚簇索引
      正文内容按照特定的维度排序，这个特定的维度就是聚集索引，比如主键
      Innodb存储引擎中行记录就是按照聚集索引维度顺序存储的，Innodb的表也称为索引表；因为行记录只能按照一个维度进行排序，所以一张表只能有一个聚集索引。
   2. 非聚簇索引
      非聚簇索引索引项顺序存储，但是索引项的对应内容是随机存储的。
      例如一个User表的Name字段，Arla和Arle两个名字在索引表中的位置可能是相邻的，但是实际存储位置可能相差很远。
      name索引表的节点按照name排序，检索的是每一行数据的主键。聚集索引表按照主键id排序，检索的是每一行的内容。
3. 从逻辑角度
   1. 主键索引
      根据主键建立索引，不允许重复，不允许空值
   2. 普通索引
      用表中普通的列构建索引，没有任何限制
   3. 唯一索引
      唯一索引的值必须唯一，但允许有控制，如果是组合索引，则列值的组合必须唯一
   4. 组合索引
      又叫联合索引，用多个列组合构建索引，这多个列中的值不允许有空值。可以在创建表的时候指定，也可以修改表结构，创建的时候应该遵从最左原则，查询频率最高的应该放在左侧
   5. 全文索引
      仅可用于MyISAM表，针对较大的数据，生成全文索引非常消耗时间和空间(在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时根据这个单词清单来索引)

索引树是如何维护的

目前大部分数据库系统和文件系统都是采用B-Tree或其变种B+Tree作为索引结构，那么索引树是如何维护的？

1. 查找结构进化史
   • 线性查找：遍历查找，慢
   • 二分查找：要求有序，简单，插入非常慢
   • HASH查找：占用空间过大，不适合存储大规模数据
   • 二叉树查找：插入和查询都很快；无法存大规模数据，复杂度退化
   • 平衡树查找：解决BST退化问题，树是平衡的，节点非常多的时候，树很高
   • 多路查找树：一个父亲多个子节点
   • 多路平衡查找树 B-Tree

B-Tree

特点：

• 定义任意非叶子结点最多只有M个儿子；且M>2;
• 根节点的儿子数为[2,M]
• 除根结点以外的非叶子节点的儿子数为[M/2, M]
• 每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
• 非叶子结点的关键字个数=指向儿子的指针个数-1；
• 非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
• 非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
• 所有叶子结点位于同一层；
• 每个k对应一个data。如：（M=3）相当于一个2–3树，2–3树是一个这样的一棵树， 它的每个节点要么有2个孩子和1个数据元素，要么有3个孩子和2个数据元素，叶子节点没有孩子，并且有1个或2个数据元素。

B+Tree

索引建立的原则

• 在经常用作过滤器的字段上建立索引；
• 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引；
• 在不同值较少的字段上不必要建立索引，如性别字段；
• 对于经常存取的列避免建立索引；
• 用于联接的列（主健/外健）上建立索引；
• 在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定；
  缺省情况下建立的是非簇集索引，但在以下情况下最好考虑簇集索引，如：含有有限数目（不是很少）唯一的列；进行大范围的查询；充分的利用索引可以减少表扫描I/0的次数，有效的避免对整表的搜索。
• 经常用在WHERE子句中的数据列；
• 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用；
• 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引；
• 对于定义为text、image和bit的数据类型的列不要建立索引；
• 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
• 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

索引失效的几种情况

• 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
• 对于多列索引，不是使用的第一部分(第一个)，则不会使用索引
• like查询是以%开头
• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
• 如果mysql估计使用全表扫描要比使用索引快,则不使用索引