索引

索引的分类 #

从分类入手，从底层索引的物理结构开始讨论构建索引的自己规范：

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。 按「字段个数」分类：单列索引、联合索引。

索引建立标准 #

  • 从业务：经常where/group by / order by
  • 从物理结构: 字段区分度要大(最好是唯一性)
  • 从物理结构: 非经常变更的字段，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。
  • 自增类型

从业务 #

从物理结构 #

// TODO 添加 索引底层页的物理结构 // TODO 添加 联合索引的物理结构

首先创建索引和维护索引要耗费时间:

  • 占用物理空间，数量越多，占用空间越大；
  • B+ 树为了维护索引有序性，都需要进行动态维护。

(聚簇)主键索引最好是自增的 #

InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

举个例子，假设某个数据页中的数据是1、3、5、9，且数据页满了，现在准备插入一个数据7，则需要把数据页分割为两个数据页：

(非聚簇)二级索引 #

区分度太小的不适合 #

// 区分度太小的也不适合做索引，比如sex字段，它的值一般不超过三个，区分度太小，你就算找到了sex=man的，你还是需要在它剩下的里面查找b,c,这种相当于查找了三分之一个表。还需要回表。

联合索引的顺序要挑选最常用的在最前面 #

  • (a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。
    • 联合索引的最左匹配原则，在遇到范围查询（>、<、between、like 包括like ‘林%‘这种）的时候，就会停止匹配，也就是范围列可以用到联合索引，但是范围列后面的列无法用到联合索引。

// 就知道了它对于联合索引的一些问题，比如a,b,c; where b,c的时候就不会进过索引。

  • 在 MySQL 5.6 之前，只能从 ID2 （主键值）开始一个个回表，到「主键索引」上找出数据行，再对比 b 字段值。
  • 而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

索引最好设置为 NOT NULL #

为了更好的利用索引，索引列要设置为 NOT NULL 约束。有两个原因：

第一原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。

第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，会导致更多的存储空间占用，因为 InnoDB 默认行存储格式COMPACT，会用 1 字节空间存储 NULL 值列表，如下图的黄色部分：

索引 #

  • what: A database index is a data structure that improves the speed of operations in a table

    • 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。
    • 索引中范围条件右边的字段会全部失效。
    • MySQL在使用!=或者<>的时候无法使用索引会导致全表扫描。
    • is null、is not null也无法使用索引。
    • like以通配符开头%abc索引失效会变成全表扫描。
    • 字符串不加单引号索引失效。
    • 少用or，用它来连接时会索引失效。