mysql关于索引那些事

  • 索引介绍
  • 优缺点
  • 建立索引的场景
  • 索引类型
  • 索引的数据结构
  • 聚族索引&非聚族索引
  • 主索引&辅助索引
  • 注意事项
  • 最左前缀原则
  • 创立索引的原则
  • 创建索引时需要注意什么


索引介绍

  介绍一个东西前，首先要明白它是什么。

  索引是一种特殊的文件，它们包含着对数据表里所有记录的引用指针。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。

  更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

优缺点

  优点:

  • 可以创建唯一性索引，保证数据库表中每一行数据的唯一性
  • 大大加快数据的检索速度，这是创建索引的最主要的原因
  • 加速数据库表之间的连接，特别是在实现数据的参考完整性方面特别有意义
  • 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
  • 通过使用索引，可以在查询中使用优化隐藏器，提高系统的性能

  缺点:

  • 可以创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
  • 索引需要占用物理空间，除了数据表占用数据空间之外，每一个索引还要占一定的物理空间，如果建立聚簇索引，那么需要的空间就会更大
  • 当对表中的数据进行增加、删除和修改的时候，索引也需要维护，降低数据维护的速度

建立索引的场景

这些情况可以考虑建立索引了

  • 如果SQL查询中出现在关键字order by、group by、distinct后面的字段，建立索引。
  • 在union等集合操作的结果集字段上，建立索引。其建立索引的目的同上。
  • 经常用作查询选择 where 后的字段，建立索引。
  • 经常用作表连接 join 的属性上，建立索引。
  • 考虑使用索引覆盖。对数据很少被更新的表，如果用户经常只查询其中的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描改变为索引的扫描。

索引类型

  • 主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
  • 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
  • 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引。
  • 全文索引: 是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

索引的数据结构

  1. B-tree(B树):

    B-tree（多路搜索树，并不是二叉的）是一种常见的数据结构,也就是我们常说的B树。使用B-tree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。按照翻译，B 通常认为是Balance的简称。这个数据结构一般用于数据库的索引，综合效率较高。

mysql索引实战 mysql索引介绍_mysql索引实战

    定义一条数据记录为一个二元组 [key, data]，B-Tree 是满足下列条件的数据结构：

  • 所有叶节点具有相同的深度，也就是说 B-Tree 是平衡的；
  • 一个节点中的 key 从左到右非递减排列；
  • 如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，且不为 null，则该指针指向节点的（所有 key ≥ keyi） 且（key ≤ keyi+1）。

    查找算法：首先在根节点进行二分查找，如果找到则返回对应节点的 data，否则在相应区间的指针指向的节点递归进行查找。

    由于插入删除新的数据记录会破坏 B-Tree 的性质，因此在插入删除时，需要对树进行一个分裂、合并、旋转等操作以保持 B-Tree 性质。

  1. B+ tree(b+树):

mysql索引实战 mysql索引介绍_mysql索引实战_02

    与 B-Tree 相比，B+Tree 有以下不同点：

  • 每个节点的指针上限为 2d 而不是 2d+1（d 为节点的出度）；
  • 内节点不存储 data，只存储 key；
  • 叶子节点不存储指针。

    与此同时，b+树还有一个特性，那就是顺序访问指针。

mysql索引实战 mysql索引介绍_mysql_03

    一般在数据库系统或文件系统中使用的 B+Tree 结构都在经典 B+Tree 基础上进行了优化，在叶子节点增加了顺序访问指针，做这个优化的目的是为了提高区间访问的性能。

  1. 哈希索引:

    InnoDB 引擎有一个特殊的功能叫 “自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

    哈希索引能以 O(1) 时间进行查找，但是失去了有序性，它具有以下限制：

  • 无法用于排序与分组；
  • 只支持精确查找，无法用于部分查找和范围查找；

聚族索引&非聚族索引

  • 聚簇索引： 将数据存储与索引放到了一块，找到索引也就找到了数据
  • 非聚簇索引： 将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。

主索引&辅助索引

  InnoDB 的 B+Tree 索引分为主索引和辅助索引。

  主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

mysql索引实战 mysql索引介绍_数据_04

  辅助索引的叶子节点的 data域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

mysql索引实战 mysql索引介绍_mysql_05

注意事项

最左前缀原则

  顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

  最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

创立索引的原则

  索引虽好，但也不是无限制的使用，最好符合一下几个原则最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。较频繁作为查询条件的字段才去创建索引更新频繁字段不适合创建索引若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。定义有外键的数据列一定要建立索引。对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。对于定义为text、image和bit的数据类型的列不要建立索引。

创建索引时需要注意什么

  • 非空字段： 应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
  • 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
  • 索引字段越小越好： 数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。