索引

索引：数据的目录

索引分类

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引
按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）
按「字段特性」分类：

主键索引（建立在主键字段上的索引，通常在创建表时一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值）

PRIMARY KEY (index1) USING BTREE

唯一索引（建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但允许有空值）

UNIQUE KEY(index_1,index_2,…)

普通索引（建在普通字段上的索引，既不要求字段为主键也不要求字段为UNIQUE）

INDEX(index_1,index_2,…)

前缀索引（对字符类型字段的前几个字符建立的索引，前缀索引可以建立在字段类型为char/varchar/binary/varbinary列上。目的：减少索引占用的存储空间，提升查询效率）

按「字段个数」分类：单列索引、联合索引（通过将多个字段组合成一个索引，最左匹配原则）

1
CREATE INDEX index_product_no_name ON product(product_no, name);

联合索引左匹配原则

按照最左优先的方式进行索引的匹配。不遵循「最左匹配原则」联合索引会失效。
在遇到范围查询（如 >、<）时就会停止匹配，也就是范围查询的字段可以用到联合索引，但在范围查询字段的后面字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配

索引下推

索引下推优化（Using index condition），可以在联合索引遍历过程中对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

创建表时InnoDB如何选列作聚簇索引的索引键

  • 有主键：用主键
  • 没主键：选第一个不包含 NULL 值的唯一列
  • 上面两个都没有：InnoDB自动生成一个隐式自增 id 列

聚簇索引和二级索引

  • 聚簇索引B+树的叶子节点存的是实际数据；
  • 二级索引B+树的叶子节点存的是主键值而不是实际数据。需要回表：如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点获取到主键值后，然后再检索主键索引才能查到数据。可用覆盖索引优化。

覆盖索引

在查询时使用二级索引，如果查询数据能在二级索引里查得到，就不需要回表了

B+树vsB树

B+树只在叶子节点存数据，而B树非叶子节点也要存储数据，所以 B+树单个节点的数据量更小，在相同磁盘I/O次数下能查更多节；

B+树叶子节点采用双链表连接，适合MySQL中常见的基于范围的顺序查找，而B树无法做到。

B+树vs二叉树

对于有N个叶子节点的B+树的搜索复杂度为O(logdN)，其中d=节点允许的最大子节点数。实际应用中d>100，就保证了即使数据达到千万级别时，B+树高度依然维持在34层左右，也就是说一次数据查询操作只需做34次磁盘I/O操作。

而二叉树的每个父节点的子节点个数只能2个，搜索复杂度为 O(logN)，比B+树高所以二叉树检索到目标数据所经历磁盘I/O次数要更多。

B+树vsHash

B+树索引要比Hash表索引有着更广泛的适用场景，Hash做等值查询时效率快，搜索复杂度 O(1)，但Hash表不适合做范围查询而更适合做等值查询

什么时候不需要创建索引

  • WHERE 条件，GROUP BY，ORDER BY 里用不到的字段（索引价值是快速定位，如果起不到定位的字段通常不需要创建索引，因为索引会占物理空间）
  • 字段中存在大量重复数据，不需要创建索引（查询优化器发现某个值出现在表的数据行中百分比很高时，一般会忽略索引进行全表扫描）
  • 表数据少
  • 经常更新的字段不用创建索引。为了维护B+Tree有序性就需要频繁重建索引，会影响数据库性能

优化索引方法

  • 前缀索引优化：为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。
    前缀索引局限性：order by 就无法使用前缀索引；无法把前缀索引用作覆盖索引
  • 覆盖索引优化：建一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表（不需要查询出包含整行记录的所有信息，减少大量I/O操作）
  • 主键索引最好是自增的：如果使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。都是追加操作而无需重新移动数据，效率高；非自增主键容易产生页分裂导致造成大量内存碎片，导致索引结构不紧凑从而影响查询效率。
  • 索引最好设为NOT NULL：索引列存在NULL会导致优化器在做索引选择时更复杂，更难优化；NULL 值没意义但会占物理空间，所以会带来的存储空间问题
  • 防止索引失效：索引失效场景（1、使用左或左右模糊匹配 2、在查询条件中对索引列做了计算、函数、类型转换操作 3、联合索引要能正确使用需要遵循最左匹配原则，否则会导致索引失效 4、WHERE子句中如果在OR前的条件列是索引列，而在OR后的条件列不是索引列，那么索引会失效）