索引基础知识

什么是索引？

帮助存储引擎快速获取数据的一种数据结构，具体来说，索引是数据的目录就像一本的目录一样。它体现了一种以空间换时间的设计思想。

• 存储引擎：实现了对数据的存储、更新和查询并为数据建立索引。存储引擎有很多，常见的有MyISAM、InnoDB、Mermory。MySQL5.5之后，InnoDB成为默认的存储引擎

索引的分类

索引可以根据四个角度进行分类：

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

• 当我们创建一张表时，InnoDB存储引擎会根据不同的场景选择不同的列作为索引。如果有主键，主键作为聚簇索引的索引键；没有主键，就选择第一个不包含null值的唯一列作为聚簇索引的索引键；如果上面两种情况都没有，InnoDB则会自动生成一个隐藏列row_id作为聚簇索引的索引键
• 其它索引都为二级索引，主键索引和二级索引默认都是B+树索引

• 覆盖索引：能够在二级索引里面查询到我们需要的数据的过程就叫覆盖索引，也就是不需要回表操作到主键索引中去查找数据（只需要查找一个B+树）

• 前缀索引是指取字符类型字段的前几个字符建立的索引，而不是在整个字段上建立索引，这样可以减少索引占用的存储空间，提升查询效率。

• 联合索引会按照从左往右的字段的顺序进行排序，也就是在查询B+树的时候采用最左匹配原则，会先按照最左边的字段进行索引的匹配。

联合索引中索引失效的情况

• where条件要满足最左匹配原则，也就是一定要带上最左边的列，并且其它列的顺序和定义二级索引时一致（其实可以不一致，查询优化器会进行优化，所以实际上并不存在因为where字句的顺序问题造成的索引失效）

where a=1；（生效）
where a=1 and b=2 and c=3；（生效）
where a=1 and b=2；（生效）

• where条件不满足最左匹配原则时，联合索引不生效

where b=2；（失效）
where c=3；（失效）
where b=2 and c=3；（失效）

• 联合索引中的字段带范围时，可能出现部分字段索引失效的情况

设想在联合索引为（a,b）的条件下

1. select * from t_table where a > 1 and b = 2

在a > 1的二级索引记录的范围中，b是无序的，所以只有a字段用到了索引，因为在a >1的数据中，b 是无序的。（可以从上图观察出来，a 字段值为 5、6、7 时，该记录的 b 字段值分别为 8、10、5）,MySQL会根据a字段在联合索引中找到第一条满足 a > 1的记录，然后通过链表向后查找，直到a <= 1为止

Tip：在此处还涉及“索引下推”的知识。也就是当我们在联合索引中查找到满足条件的a > 1的第一条记录时，还需要判断 b == 2这个条件，那么应该在联合索引中做判断还是拿着主键值取主键索引中做判断？
MySQL5.6之前会先回表再对联合索引的其它字段做判断
MySQL5.6之后，则会在联合索引中对联合索引中的其它字段先做判断，过滤掉一些不满足条件的记录，从而减少回表的次数

2. select * from t_table where a >= 1 and b = 2

因为联合索引中，a字段相同时，会根据b字段进行排序，因为条件中a是>=，所以可以使用a,b两个字段在联合索引中找到a == 1并且 b == 2的第一条记录（可以通过 b==2减少扫描范围），然后沿着链表继续向后扫描

3. SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2

不同数据库对于Between…and的处理方式有所差异，对于MySQL，会将Between…and等同于>= …<=，所以和第二种情况是一样的，还是a,b两个字段都用到联合索引

4. SELECT * FROM t_user WHERE name like 'j%' and age = 22

MySQL会在联合索引中找到开始字符为j的记录，并且不会因此而停止匹配，而是还会用到age = 22这个字段条件，减少扫描范围

索引区分度

• 区分度 = 索引字段不同值的个数 / 表的总行数
• 建立联合索引要尽量将区分度大的字段排在前面，这样区分度大的字段越有可能被更多的SQL使用到。（不会出现联合索引中部分字段索引失效的情况）
• 实际上，即使我们对某个字段建立了索引，查询优化器发现该字段的区分度比较低时，这个时候是会忽略索引，进行全表扫描的

借助联合索引进行排序

select * from order where status = 1 order by create_time asc
该SQL查询的时候，为了提高效率，我们不能只是对status建立索引，因为create_time还需要排序，所以，我们可以建立status和create_time的联合索引，联合索引会在status相同的情况下，对create_time进行排序，这样可以避免使用文件排序？

什么时候需要/不需要创建索引？

• 索引的缺点

一方面是占用物理空间；另一方面是，数据量较大的时候，索引的创建和维护耗费时间。索引需要维护是因为要保存B+树的有序性

什么时候使用索引？

1. 字段有唯一限制的，如身份证
2. 经常用于where查询条件的字段，如果是多个字段则建立联合索引
3. 经常用于group by 和order by 的字段，因为索引本身会将记录排好序，索引我们可以通过索引来帮助我们排序，而不需要进行文件排序（filesort）

什么时候不需要创建索引？

1. 索引是为了提高查询效率，如果数据量不大的情况下，可以不需要使用索引
2. 创建了索引就需要维护，如果某个字段经常需要更新（比如用户的余额字段），那么为了维持B+树的有序性会导致频繁重建索引，影响数据库的性能
3. 再有，从字段的区分度入手，也就是字段的不同值占表总行数的比例，如果比较低（像性别字段，只有男女之分），所以如果采用了这样的字段做索引，由于数据分布均匀，所以可能会搜索到一半的数据，这样其实还不如不要索引（其实MySQL数据库本身就会对字段的区分度进行判断，比较低时，一般是30%，这个时候就会忽略索引，走全表扫描）
4. 最后，就是起不到定位作用的字段，也就是不会经常用于where、group by、order by的字段，不需要建立索引

有什么索引优化的方法？

前缀索引优化

使用前缀索引可以减小索引字段的大小，同时也会增加一个索引页中存储的索引值（一个节点有更多分支），有利于提高查询效率

• 局限性：

1. order by不能使用前缀索引，因为前缀索引只是去了字符串的前面一部分数据
2. 无法将前缀索引用在覆盖索引

覆盖索引优化

尽量建立我们要查询的字段的联合索引，减少回表操作

主键索引最好是自增的

主键索引的叶子节点中的记录都是按照主键的大小排序的，如果我们使用了主键自增，在添加一条记录的时候，就可以直接追加，而不用移动其它数据来进行插入操作，同时，不采用主键自增还可能出现页分裂，需要将数据从一个页面复制到另一个页面，另外，插入一个数据就可能需要从重新开辟一个新的页面，因此可能带来许多内存碎片，导致索引结构不紧凑，造成存储空间的浪费

• 另外，主键长度不用太长，因为二级索引中的叶子结点包含主键值，主键长度比较小可以减少二级索引占用的空间

索引最好设置为Not Null

• 一方面，索引列存在null会导致优化器在做索引选择的时候更加复杂，因为可为null的列会使得索引、索引统计和值比较都比较复杂
• 另一方面允许null值存在时还记录行中还需要一个至少1字节的空间来存储Null值列表

防止索引失效

• 索引失效的情况：

1. 使用左模糊查询或者左右模糊查询时，即 like %xx 或者 like %xx%
2. 对索引列进行了计算、函数、类型转换操作
3. 不满足最左前缀法则
4. where字句中，or前的条件列是索引列，or后条件列表示索引列

• 实际情况下，还可能出现其它索引失效的情况，可以通过explain关键字查询SQL的执行计划

• possible_keys 字段表示可能用到的索引；
• key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
• key_len 表示索引的长度；
• rows 表示扫描的数据行数。
• type 表示数据扫描类型，我们需要重点看这个。

对于type字段：

• All（全表扫描）；
• index（全索引扫描）；
• range（索引范围扫描）；
• ref（非唯一索引扫描）；
• eq_ref（唯一索引扫描）；
• const（结果只有一条的主键或唯一索引扫描）。

由上至下，执行效率由低到高，我们尽量让SQL查询在range及以上

对于extra字段：

• Using filesort：使用了文件排序
• Using temporary：使用了临时表保存中间结果
• Using index：覆盖索引