MySQL 索引
简介
- mysql中的索引是用于提升查询效率的数据结构，其通过额外的存储空间换取查询的效率;
- 通过索引可以快速的定位存储的数据，而不用每次都去遍历；
- 典型的MyISA和Innodb存储引擎均使用B+Tree来实现索引;
分类
- 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
- 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
- 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
- 按「字段个数」分类：单列索引、联合索引。
按数据结构：
- B+Tree索引：支持范围查询；
- Hash索引：只支持精确查询，不支持范围查询；
- Full-text索引：倒排索引；目前只有MyISAM引擎支持，主要用于提升文本的模糊查询效率较低的问题；
- RTree：
按物理存储：
- 聚簇索引: 聚簇索引和数据放到了一块, 索引的叶子节点就是数据节点，找到索引也就找到了数据; 聚簇索引一般通过B+Tree来实现；
- 非聚簇索引：非聚簇索引又叫二级索引（辅助索引），非聚簇索引的叶子节点仍然是索引节点，只不过是指向对应数据块的指针索引和数据分开存储结构，索引中存储的是主键ID，找到索引ID后，还需要通过回表再查询数据；MyISAM使用B+Tree实现的非聚簇索引；
按字段特性：
- 主键索引: 一张表只能有一个主键索引，不允许重复、不允许为 NULL；
- 辅助索引:
- 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
- 普通索引: 一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；
- 前缀索引:
按字段个数分：
- 单列索引: 一个索引只包含一个列，一个表可以有多个单例索引。
- 联合索引: 又叫复合索引、组合索引。一个组合索引包含两个或两个以上的列。查询的时候遵循 “最左前缀”原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。
| - | InnoDB | MyISAM | Memory |
|---|---|---|---|
| B+tree索引 | √ | √ | √ |
| Hash索引 | × | × | √ |
| Full-text索引 | √（MySQL5.6+） | √ | × |
聚簇索引
聚簇索引的叶子节点存储的是行记录；
每张表只能有一个聚集索引；
- 如果表设置了主键，则主键就是聚簇索引；
- 如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引；
- 以上都没有，则会默认创建一个隐藏的row_id作为聚簇索引；
聚簇索引的查询效率高，因为可以直接定位到行记录；
聚集索引在一般使用自增主键来建索引，非自增键用作聚簇索引可能会由于分裂而导致性能地下；
非聚簇索引
- 非聚簇索引又叫二级索引（辅助索引）；
- 非聚簇索引的叶子节点是主键的指针，行数据分开存储；
- 非聚簇索引查询时需要回表再查询数据；
- MyISAM使用B+Tree实现的非聚簇索引；
- 一张表可以有多个非聚集索引；
- 非聚簇索引的插入，删除，更新等操作效率会比聚集索引高；
覆盖索引
- 覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取；
- 如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引；
联合索引
- 联合索引又叫复合索引，是指在两个或以上列上建立的索引；
- 联合索引从左到右的使用索引中的字段；
- 联合索引查询时，遵循最左匹配原则，即一个查询只使用索引中的一部份，但只能是最左侧部分；
全文索引
- 全文索引(FullText)是基于倒排的索引结构，一般用于文本搜索；
- 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引；
索引下推
- 索引下推(
Index Condition Pushdown，简称ICP), 是MySql 5.6 版本后的一个特性； - 索引下推是在联合索引查询时，将查询过滤条件从sever层下推到引擎层，从而减少回表数据的行为；
- 索引下推能减少回表查询次数，提高查询效率；
索引失效
- 联合索引字段，查询时违反最左前缀原则；
- 使用反向查询（!=, <>, NOT LIKE）的时候会导致全表扫描，无法使用索引，覆盖索引除外；
- like以通配符开头时，索引失效；
- 对索引列做了其他操作，例如数值计算、使用函数、（手动或自动）类型转换等操作，会导致索引失效；