【索引】对索引的理解

什么是索引？

索引是存储引擎用于快速找到数据的一种数据结构，比如一本书有500页，我们要查询其中的一个知识点，在没有目录的情况下要查很久，有目录就可以很快查到，索引就相当于目录。

索引的优缺点

优点：减少服务器需要扫描的数据量、帮助服务器避免排序和建立临时表、将随机IO变为是顺序IO

缺点：物理角度：创建索引需要占用存储空间；时间角度：创建和维护索引需要大量的时间，对数据进行增删改的时候都需要维护索引

索引分类

• 数据结构角度
  • 树索引
  • 哈希索引
• 物理存储角度
  • 聚簇索引：聚簇索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的就是整张表的行记录数据，聚簇索引的叶子节点被称为数据页
  • 非聚簇索引：非聚簇索引的结构和聚簇索引基本相同（非叶子节点存储的都是索引指针），区别在于叶子节点存放的不是行数据而是指向数据行的主键。因此在使用非聚簇索引进行查找时，需要先查到主键值，然后再到聚簇索引中进行查找或者直接到数据表中查找对应的行数据（回表）
• 逻辑角度
  • 普通索引
  • 唯一索引：建立在 unique 字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突
  • 主键索引
  • 联合索引
  • 全文索引

 索引底层使用的数据结构

MySQL 中常用的是 Hash 和 B+树索引

• Hash 索引：基于哈希表实现，非常快，但是不支持范围查找和排序，在 MySQL 中支持的哈希索引是自适应的，不能人为创建
• B+树索引：B+树索引就是传统意义上的索引，是目前关系型数据库中查找最为常用和最为有效的索引。B+树索引能够加快访问数据的速度，因为存储引擎不在需要进行全表扫描来获取需要的数据。B+树索引是顺序组织存储的，所以很适合查找范围数据。B+树索引分为聚簇索引（主键索引）和非聚簇索引（二级索引）

 索引的设计原则

1. 为经常作为查询条件的字段建立索引：如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度
2. 为经常需要排序、分组和联合操作的字段建立索引：经常需要 order by、group by、distinct 和 union 等操作的字段，排序会浪费很多时间。如果为其建立索引，可以有效地避免排序操作
3. 创建唯一性索引：唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录
4. 限制索引的数目：每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大，修改表时，对索引的重构和更新很麻烦
5. 小表不建议索引（如数量级在百万以内）：由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果
6. 尽量使用前缀索引：如果索引的值很长，那么查询速度会受到影响
7. 删除不再使用或者使用很少的索引

索引使用策略 

1. 独立的列：索引使用不当会导致索引失效（查询中实际没有使用索引）。如果查询中的列不是独立的，MySQL 不会使用索引。独立的列指查询时索引列不能是表达式的一部分，也不能是函数的参数，这两种情况都会导致索引失效
2. 前缀索引：使用前缀索引可以节约索引空间，从而提高索引效率，但是需要平衡索引的选择性
3. 使用联合索引：使用联合索引可以避免回表，实现覆盖索引，可以减少大量 IO 操作
4. 合适的索引列顺序：创建联合索引时，不同的列顺序会影响索引的性能，通常将选择性高的列放在最前面
5. 合适的主键：最好选择不会修改的列作为主键，不考虑分库分表的情况最好使用自增主键
6. 不建立重复的索引和不使用的索引

索引失效的情况 

1. 查询条件包含 or，or 的前后不都是索引
2. 使用 like 查询，%在前面且没有走覆盖索引
3. 对索引列使用函数
4. 索引列使用运算
5. 索引列使用不等于（< > != not in）
6. 出现隐式类型转换，如字符型索引使用数字匹配
7. 联合索引没有使用最左匹配
8. 全表扫描比索引快（数量很少）

判断 SQL 语句是否走了索引

 在 SQL 语句前加 explain 

explain 各属性的含义：

• type：访问类型
  • ALL：扫描全表
  • index：扫描全部索引树
  • range：扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于 between、等的查询
  • ref：使用非唯一索引或非唯一索引前缀进行查找
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  • const,system：单表中最多有一个匹配行，查询起来非常迅速，例如根据主键或唯一索引查询。system是const类型的特例，当查询的表只有一行的情况下，使用system
  • NULL：不用访问表或者索引，直接就能得到结果，如 select 1 from test where 1
• key：显示MySQL实际决定使用的索引。如果没有索引被选择，是NULL
• key_len：使用到索引字段的长度，key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的
• Extra：执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息
  • Using index：表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，这个叫覆盖索引
  • Using where：表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where