索引

索引分类

InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身；
MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址；
按「数据结构」分类：
- B+tree索引
- Hash索引
- Full-text索引
按「物理存储」分类：
- 聚簇索引（主键索引）：叶子节点存放的是实际数据
  - 有主键，默认会使用主键作为聚簇索引的索引键（key）；
  - 没有主键，就选择第一个不包含 NULL 值作为索引的索引键（key）；
  - 都没有，自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；
- 二级索引（辅助索引）：除了主键索引都是，子节点存放的是主键值和索引值
  - 回表：先检二级索引中的索引值，找到对应的叶子节点，获取主键值，再通过主键索引查询到对应的叶子节点，获取整行数据。
  - 覆盖索引：查询的数据能在二级索引的叶子节点里查询到，不用再回表
按「字段特性」分类：
- 主键索引：建立在主键字段上的索引，默认建立，唯一
  - PRIMARY KEY (index_column_1) USING BTREE
- 唯一索引：建立在 UNIQUE 字段上的索引
  - 索引列的值必须唯一，但是允许有空值。
  - UNIQUE KEY(index_column_1,index_column_2,...)
  - CREATE UNIQUE INDEX index_name ON table_name(index_column_1,...);
- 普通索引：建立在普通字段上的索引，平平无奇
  - INDEX(index_column_1,index_column_2,...)
- 前缀索引：对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引
  - 可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。
  - INDEX(column_name(length))
  - CREATE INDEX index_name ON table_name(column_name(length));
按「字段个数」分类：
- 单列索引：建立在单列上
- 联合索引：建立在多列上，先按第一列排序，再按第二个，以此类推
  - 最左匹配原则：按照最左优先的方式进行索引的匹配，要求有左边的字段，不要求where的顺序，优化器自动优化。左匹配原则会一直向右匹配直到遇到「范围查询>、<」就会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。
  - 索引下推：截断的字段不会在 Server 层进行条件判断，而是会被下推到「存储引擎层」进行条件判断。在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
  - 索引区分度：distinct/count，区分度大的字段尽量排在前面。
  - 用联合索引可以避免order by 再次排序
  - INDEX index_name (column1,column2)
  - CREATE INDEX index_product_no_name ON product(product_no, name);

为什么选择 B+tree 作为索引的数据结构？

B+树：

层数少：只有叶子节点存放数据，减少读盘次数
- 二叉树、B树等做不到
可顺序读取：叶子节点使用双向链表连接
- Hash做不到

联合索引小栗子：联合索引（a, b）

select * from t_table where a > 1 and b = 2
- a用到了索引，b没有
select * from t_table where a >= 1 and b = 2
- a 和 b 字段都用到了联合索引，从符合 a = 1 and b = 2 条件的第一条记录开始扫描
SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2
- a 和 b 字段都用到了联合索引
SELECT * FROM t_user WHERE a like 'j%' and b = 22
- a 和 b 字段都用到了联合索引

索引的缺点：

需要占用物理空间；
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

什么时候需要索引？

字段有唯一性限制的，比如商品编码
经常用于 WHERE 查询条件的字段，如果查询条件不是一个字段，可以建立联合索引
经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序

什么时候不需要索引？

WHERE 条件，GROUP BY，ORDER BY 里用不到的字段
字段中存在大量重复数据
表数据太少的时候
经常更新的字段

B+树

为什么不使用B树？

所有节点都存储数据
- 读取节点存在很多无用数据，只有索引字段是有用的
- 分叉数目变少，树高度增加，读盘次数增多
- 插入、删除效率低，树结构变化大
顺序遍历麻烦，需要遍历树节点，B+树直接双向链表

B+特点：

只有叶子节点存数据，非叶子节点存放目录项作为索引。
所有节点按照索引键大小排序，每一层构成一个双向链表，便于范围查询；
子节点包含父节点的索引的值，并且是在子节点中所有索引的最大（或最小）。
- 冗余节点，得删除一个节点时，直接从叶子节点中删除，甚至可以不动非叶子节点，这样删除非常快，
非叶子节点中有多少个子节点，就有多少个索引；

索引优化

前缀索引：使用某个字段中字符串的前几个字符建立索引，减小索引字段大小
- 缺点：order by 就无法使用前缀索引、无法把前缀索引用作覆盖索引；
覆盖索引：query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到，避免回表。
主键索引最好是自增的
- 每次插入一条新记录，都是追加操作，不需要重新移动数据，效率非常高。
- 非自增主键：页分裂、内存碎片，导致索引结构不紧凑，从而影响查询效率。
主键长度不要太大：二级索引占用的空间也就小。
索引最好设置为 NOT NULL
- NULL 导致优化器难以优化，使索引、索引统计和值比较都更复杂，比如count 会省略值为NULL 的行。
- NULL 值是一个没意义的值,至少会用 1 字节空间存储 NULL 值列表
防止索引失效

索引失效

左或者左右模糊匹配 like %xx 或者 like %xx%；
- 特殊情况：select * from s where name like "%xxxx"
  - 当name为二级索引，索引覆盖时，走name索引全表扫描，type index
  - 因为二级索引树的记录东西很少，就只有「索引列+主键值」，而聚簇索引记录的东西会更多，比如聚簇索引中的叶子节点则记录了主键值、事务 id、用于事务和 MVCC 的回滚指针以及所有的剩余列。再加上，这个 select * 不用执行回表操作，因此 MySQL 选择了「全扫描二级索引树」的方式查询数据。
对索引列做了计算、函数、类型转换操作；
- MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引
  - alter table t_user add key idx_name_length ((length(name)));
- 类型转换：MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。
  - 不走索引：select * from t_user where phone = 1300000001; phone是varchar
  - 走索引：select * from t_user where id = '1'; id 是int
联合索引要遵循最左匹配原则。
OR 前的条件列是索引列，而在 OR 后的条件列不是索引列。

【MySQL】索引

索引

索引分类

B+树

索引优化

索引失效