1、索引是什么及优缺点
2、索引的底层数据结构
- 2.1 Hash表
- 2.2 B+树
3、一些关于索引的思考题
4、索引的分类
5、索引的设计原则
6、创建索引的注意事项与使用索引的建议
- 6.1 创建索引的注意事项
- 6.2 使用索引的一些建议
7、索引失效的情况

1、索引是什么及优缺点

索引是什么：
MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。
索引的本质是数据结构。它是对数据库表中一个或多个列的值进行排序的数据结构。它类似于书的目录。比如字典，如果没有目录，那么只能一页一页的去找需要查找的字，速度很慢，但是有了目录，就只需要先去目录里找字的位置，然后直接定位到那一页即可。
索引的优点：

使用索引可以大大加快数据的检索速度（大大减少检索的数据量），提高数据检索的效率，降低数据库IO成本，这也是创建索引的最主要的原因。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
在实现数据的参考完整性方面，可以加速表和表之间的连接。也就是说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序时间，降低了cpu的消耗。

索引的缺点：

创建索引和维护索引需要耗费许多时间。并且随着数据量的增加，耗费的时间也会增加。
索引会占用磁盘空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
索引在提高查询速度的同时，会降低更新表的速度。对表中数据进行增删改的时候，索引也要动态地维护，这样降低了SQL的执行效率。
2、索引的底层数据结构
MySQL中，索引主要有B+树结构和Hash表结构，在不同的存储引擎中，使用不同的结构。比如在InnoDB和MyISAM中，索引是B+树结构，在memory引擎中，所以是Hash表结构。
2.1 Hash表

2.2 B+树

B+树是B树的一种变体。B树称为多路平衡查找树。
B+树和B树的差异：

3、一些关于索引的思考题

1、为了减少IO，索引树会一次性加载进内存吗?
不会。数据库索引是存储在磁盘上的，如果数据量很大，那么索引也会很大，超过几个G都有可能。那么我们利用索引查询到时候，是不可能将全部几个G的索引全都加载进内存的，而是逐一加载每个磁盘页，因为磁盘页对应着索引树的节点。
2、B+树的存储能力如何，为什么说一般查找行记录，做多只需要1-3次磁盘IO？

3、

4、

、

4、索引的分类

按照物理实现方式，索引分为：聚簇索引和非聚簇索引。
按照作用的字段划分，索引分为：单列索引和联合索引。
按照功能逻辑划分，索引分为：普通索引、唯一索引、主键索引、全文索引、空间索引。

4.1 聚簇索引和非聚簇索引

聚簇索引：
又叫聚集索引。即：索引结构和数据一起存放的索引。主键索引属于聚集索引。
优点：

数据访问更快。因为聚簇索引把索引和数据保存在同一个B+树中，定位了到索引的节点，也就定位到了数据。因此从聚簇索中获取数据比非聚簇索引更快。
对于主键的排序查找和范围查找速度非常快。
按照聚簇索引排列顺序，查询一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的IO操作。

缺点：

进行更新时代价很大。如果对索引列的数据进行修改，那么对应的索引也将被修改，而且聚簇索引的叶子结点还存放着真实数据，修改代价很大；还会导致被更新的行进行移动。因此对于主键索引来说，主键一般都是不可以被修改的。
它依赖于有序的数据，插入速度严重依赖于插入顺序。因为B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果是整型还好，否则类似于字符串或者uuid这种，插入或者查找的速度会比较慢。因此，我们一定定义一个自增的ID列为主键。

非聚簇索引：
又叫非聚集索引，二级索引，辅助索引。是索引结构和数据分开存放的索引。
优点：

更新代价比聚簇索引小。因为非聚簇索引的叶子结点不存放真实的所有的行数据，而是只存放主键和索引位的值，不像聚簇索引存放了所有真实数据，所以更新代价相对较小。

缺点：

跟聚簇索引一样，非聚簇索引也依赖于有序的数据。
可能会需要进行回表操作（二次查询）。当查询到索引对应的指针或者主键后，可能还需要根据指针或者主键，再到数据文件或者表中查询。

什么是回表：
回表就是进行二次查询，简单来说，就是先查询到主键索引，然后再用索引定位到数据。
在非聚簇索引进行查询时，假设有A、B、C、D四个字段，其中A是主键，我们将B设为索引。那么在索引的叶子结点中，数据页存放的数据值只有主键A和索引字段B的数据，如果我们向根据索引字段B的值查找完整的各个字段记录，就仍然需要到聚簇索引中再查一遍，这个过程称为回表。也就是根据B列的值查询一条完整的记录，需要使用到两颗B+树。
为什么我们还需要一回表操作呢？直接把完整的用户记录放到叶子节点不OK吗？
这样的话，太占用磁盘空间，相当于每建立一颗B+树都要把完整的记录再拷贝以便，太浪费空间；同时，如果进行数据修改操作，那么数据文件和索引文件都需要修改。
如何避免回表：
使用覆盖索引。所谓覆盖索引就是指索引中包含了查询中的所有字段，这种情况下就不需要再进行回表查询了。因为在非聚簇索引中，叶子结点会保存主键数据以及设为索引字段的字段的数据，这样设置为覆盖索引后，就可以直接查到所有需要的数据。

4.2 单列索引和联合索引

单列索引：
在表中的单个字段上创建索引，单列索引只根据该字段进行索引。
单列索引可以是普通索引，也可以是唯一索引，还可有是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个单列索引。
联合索引：
又叫多列索引、组合索引。是在表的多个字段上组合创建一个索引。该索引指向创建时对应的多个字段，可以通过这个几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段才会被使用。
例如：表中id、name、和gander上建立一个多列索引idx_id_name_gender，只有在查询条件中使用了字段id时，该索引才会被使用。
使用联合索引时遵循最左前缀原则。

什么是最左前缀原则：
最左前缀原则，是指在使用联合索引时，MySQL会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配。如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直到联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如>、<、between和以%开头的like查询等条件，才会停止匹配。
索引我们再使用联合索引时，可以讲区分度高的字段放在最左边，这样可以过滤更多数据。

4.3 普通、唯一、主键、全文、空间索引

普通索引：
普通索引不附加任何限制条件，只用于提高查询效率。普通所以可以创建在任何数据类型中，索引字段的值是否唯一、是否为空，由字段本身的完整性约束条件决定。建立缩阴后，可以通过索引进行查询。
唯一索引：
使用UNIQUE参数可以设置索引为唯一索引。在创建唯一索引时，限制该索引的值必须是唯一的，但是允许空值。在一张表里可以有多个唯一索引。
主键索引：
主键索引是一种特殊的唯一索引。在唯一索引的基础上增加了不为空的约束，也就是NOT NULL + UNIQUE，一张表里最多只有一个主键索引。
为什么一张表里只能有一个主键索引？这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。
全文索引：
全文索引时搜索引擎使用的一种技术，了解一下，现在都用ES了。
空间索引：
空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。

5、索引的设计原则

5.1 哪些情况适合创建索引以及一些细节

字段的数值有唯一性的限制：

业务上具有唯一性的字段，可以添加唯一索引。当然，如果设计字段时，已经把字段设计为UNIQUE的了，那么会自动添加唯一索引。

频繁作为WHERE查询条件的字段：

某个字段在SELECT语句中的WHERE条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅度提高查询效率。

经常GROUP BY和ORDER BY的字段：

索引的结构就是让数据按照某种顺序进行存储或者检索，因此我们使用GROUP BY对数据进行分组查询，或者ORDER BY对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引。

UPDATE、DELETE的WHERE条件：

对数据按照某个条件进行查询后再进行UPDATE或DELETE操作，如果对WHERE条件的字段创建了索引，就能大幅提升效率。原理是因为我们需要现根据WHERE条件检索出来这条/这些记录，然后再对他们进行更新或者删除。
如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这时因为非索引字段的更新不需要对索引进行维护。

DISTINCT字段需要创建索引：

有时候我们需要对某个字段进行去重，使用DISTINCT，那么对这个字段创建索引，也会提升查询效率。
这是因为索引会对数据按照某种顺序进行排序，所以去重的时候也会快很多。

多表JOIN连接操作时，创建索引注意事项：

首先，链接表的数量尽量不要超过三张，因为每增加一张表就相当于增加可一次钱套的循环，数量级增长会非常快，严重影响查询的效率。
其次，是要对WHERE条件创建索引，因为WHERE才是对数据的过滤。如果在数据量大的情况下，没有WHERE条件过滤是很可怕的。
最后，可以对用于连接到字段创建索引，并且该字段在多张表中类型必须一致。

使用列的类型小的创建索引：

这里说的类型小，是指字段的类型表示的数据范围的大小。
数据类型越小，在查询时进行的比较操作越快。
数据类型越小，索引占用的存储空间就越小，在一个数据页内就可以放下更多的记录，从而减少磁盘IO带来的性能损耗。

使用字符串前缀创建索引：

假设字段内容的字符串很长，那么存储一个字符串就需要占用很大的存储空间。如果为这个字符串建立索引，那么意味着索引底层的B+树中面临两个问题：
1，B+树索引中的记录需要把该列的完整字符串存储起来，更加费时。且字符串越长，在索引中占用的存储空越大。
2，如果B+树索引中索引列存储的字符串很长，那在做字符串之间的比较时，会占用更多的时间。
基于这些，我们可以截取字段的前面一部分内容建立索引，这就叫前缀索引。这样子查找记录时，虽然不能精确的定位到记录的位置，但是能定位到响应前缀所在的位置，然后根据前缀相同的记录的主键值，回表操作，查询处完整的字符串的值。这样既节约空间，又减少了字符串的比较时间，还大体上能解决排序的问题。
不过要注意，一定情况下，使用前缀索引，无法支持索引排序。

区分度高（散列性高）的列适合作为索引：
使用最频繁的列放到联合索引的左侧：

这样也可以较少的建立一些索引。同时由于最左前缀原则，可以增加联合索引的使用率。

在多个字段都要创建索引的情况下，联合索引优于单值索引：
要限制索引的数目：

要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。原因如下：

每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间越大。
索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中数据进行更改的同时，索引也会进行调整和更新，会造成负担。
优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划的时间，降低查询性能。
5.2 哪些情况不适合创建索引

在WHERE条件中使用不到的字段，不要设置索引：

WHERE条件、ORDER BY条件、GROUP BY条件里面用不到的字段不要需要创建索引。
索引的价值是快速定位，如果起不到定位到字段通常不需要创建索引。

数据量小的表最好不要使用索引：

比如少于一千行。

有大量重复数据的列上不要创建索引：

当数据重复度大，比如高于10%的时候，也不需要对这个字段使用索引。
例如性别字段，一百万条数据，50万都是男，一旦建立了索引，需要先访问五十万次索引，然后再访问50万次数据表，这样加起来的开销比不适用索引可能还要大。

避免对经常更新的表创建过多的索引：

第一层含义：频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引。如果索引太多，在更新索引的时候也会造成负担，从而影响效率。
第二层含义：避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。因为这时虽然提高了查询速度，同时却会降低更新表的速度。

不建议用无序的值作为索引：

例如UUID、无序长字符串等，虽然满足唯一非空的特征，但是在索引比较时需要转换为ASCII，并且插入时可能造成页分裂。

删除不再使用或者很少使用的索引：

索引不再使用就将其删除，以免影响磁盘空间，同时减少索引对更新操作的影响。

不要定义冗余或者重复的索引：

冗余索引：
无意或者有意对同一列创建了多个索引。
重复索引：
对某个列重复建立索引。

5.3 小结

索引是一把双刃剑，可以提高查询效率，但是也会降低插入和更新的速度，并且占用磁盘空间。
选择索引的最终目的是为了使查询速度变快。

6、创建索引的注意事项与使用索引的建议

6.1 创建索引的注意事项

选择合适的字段创建索引：
1. 不为NULL的字段：索引字段的数据应该尽量不为NULL。因为对于数据为NULL的字段，数据库较难优化。如果字段频繁被查询，但是又避免不了为NULL，建议使用0,1，true，false这种语义较为清晰的短值或者短字符作为替代。
2. 被频繁查询的字段；
3. 被作为查询条件的字段；
4. 被频繁排序的字段；
5. 被频繁用于连接到字段。
被频繁更新的字段应该慎重建立索引：

索引虽然能提高查询效率，但是维护索引成本很大。更新数据的时候也需要更新索引。

尽可能考虑建立联合索引而不是单列索引：

索引也是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗B+树。
如果一个表的字段过多，索引过多，那么这个表数据达到一个体量后，索引占用的空间也很多；并且修改索引时，耗费的时间也很多。如果是联合索引，多个字段在一个索引上，会节约很大的磁盘空间，并且修改数据的操作的效率也会提升。

避免冗余索引：

冗余索引是指索引的功能相同。比如能够命中索引（a，b），那么就肯定能命中索引（a），此时索引（a）就是冗余索引。在大多情况下，都应该尽量扩展已有的索引而不是创建新索引。

考虑在字符串类型的字段上使用前缀索引代替普通索引：

前缀索引仅限于字符串类型，相较于普通索引会占用更小的空间。

6.2 使用索引的一些建议

对于中到大型表，索引都是非常有效的，但是特大型表的话，维护开销会很大，不适合建索引。
避免WHERE自己中对字段施加函数，这会造成无法命中索引。
在使用InnoDB时，用与业务无关的自增主键作为主键。
删除长期不用的索引。
使用limit offset查询缓慢时，可以借助索引来提高性能。

7、索引失效的情况
联合索引的情况下，要遵循最左前缀原则，如果没有遵循，那么查询会失效。

同时，联合索引的情况下，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

SQL语句中对列进行计算或者使用函数，会导致索引失效。
进行类型转换（自动或手动），也就是说，不匹配的数据类型，会导致索引失效。

比如列的类型是字符串，那么一定要在条件中将数据用引号引起来，否则索引会失效。

范围条件右边的列索引失效。

比如在age、classId和name上创建联合索引。

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

此时，name字段就没有被索引查询。
解决方法，涉及索引时，将这种范围的、不确定的字段放在索引创建时的后面。

WHERE条件中，使用不等于（！=或者<>）时，索引失效。
WHERE条件中，使用IS NULL可以使用索引，但是使用IS NOT NULL时索引失效。
like查询时，以%开头，索引失效。
在WHERE条件中，使用OR，而且OR的前后存在不是索引的列，那么其他列的索引将失效。
数据库和表的字符集要统一使用utf8mb4/utf8mb3，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换，会造成索引失效。

一些建议：

对于单列索引，尽量选择针对当前查询过滤性更好的索引。
在选择组合索引时，当前查询中过滤性最好的字段在索引字段的顺序中，位置越靠前越好。
在选择组合索引时，尽量选择能够包含当前查询中，WHERE字句中更多字段的索引。
在选择阻塞索引时，如果某个字段可能出现范围查询时，尽量把这个字段放在索引的后面。

总之，尽量避免索引失效的情况。

索引