MySQL索引重要知识点

1.什么是索引？

       索引在项目中是比较常见的，它是帮助MySQL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的I0成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗。

2.索引的底层数据结构

       MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引，选择B+树的主要的原因是:

• 阶数更多，路径更短：B+树的每个节点可以有更多的子节点（即阶数更高），这意味着树的高度更低，路径更短，从而加快查询速度。
• 磁盘读写代价更低：B+树的非叶子节点只存储键值和指针，而叶子节点存储实际数据。这样设计的好处是，可以在一个节点中存储更多的键值，减少了磁盘I/O操作，因为查询时大多数操作都发生在非叶子节点上。
• 便于扫库和区间查询：B+树的叶子节点形成一个双向链表，使范围查询和顺序遍历更加高效。通过遍历叶子节点链表，可以快速访问某个范围内的所有数据。

3.B树与B+树的对比

       B树：

（1）节点存储：

• 内部节点：存储键值和指向子节点的指针。
• 叶子节点：存储键值和实际数据。

（2）查询：

• 可以在任何节点上结束查询，因为所有节点（包括内部节点和叶子节点）都存储实际数据。
• 查询效率较高，但路径较长时效率可能会降低。

（3）插入和删除：

• 插入和删除操作需要在树的内部节点和叶子节点上进行调整，这可能会涉及到多次磁盘I/O操作。

B+树：

（1）节点存储：

• 内部节点：仅存储键值和指向子节点的指针，不存储实际数据。
• 叶子节点：存储键值和实际数据，并通过链表连接。

（2）查询：

• 所有查询必须进行到叶子节点才能获取实际数据。
• 查询路径较长，但每个节点存储更多的键值和指针，减少了树的高度。

（3）插入和删除：

• 插入和删除操作只会影响叶子节点，不需要调整内部节点，减少了磁盘I/O操作。

B树与B+树的对比总结：

• 查询效率B+树更稳定：B树可以在内部节点结束查询，而B+树需要进行到叶子节点。因此，B树的某些查询可能更快，但B+树通过更高的阶数和较低的树高来弥补这一点。
• B+树便于扫库和区间查询：B+树的叶子节点通过链表连接，支持高效的范围查询和顺序访问，B树在这方面效率较低。
• 插入和删除：B+树的插入和删除操作相对简单，只需要调整叶子节点，而B树可能需要调整多个节点。
• 磁盘读写代价B+树更低：B+树的内部节点只存储键值和指针，可以减少磁盘I/O操作次数，而B树内部节点存储实际数据和键值，可能增加I/O操作。

4.聚集索引和非聚集索引（二级索引）

• 聚集索引：聚集索引主要指将数据与索引放到了一块，B+树的叶子节点保存了整行数据。聚集索引必须有，而且只能有一个，因为数据的物理顺序只能有一种，一般情况下是主键作为聚簇索引的。
• 二级索引：二级索引是将数据与索引分开存储，B+树的叶子节点保存的是对应的主键，二级索引可以存在多个，一般我们自定义的索引是二级索引。

聚集索引选取规则:

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

5.什么是回表查询？

       回表查询跟聚簇索引和非聚簇索引是有关系的，回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表。

6.什么是覆盖索引？

       覆盖索引是指select查询语句使用了索引，在返回的列，必须在索引中全部能够找到，如果我们使用id查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

       如果按照二级索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select*，尽量在返回的列中都包含添加索引的字段。

7.MySQL超大分页怎么处理？

       超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决。

       先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以，因为查询id的时候，走的覆盖索引，一次索引扫描，直接返回数据，所以效率可以提升很多。

8.索引创建原则

       索引创建原则很多，不过都有一个大前提，就是表中的数据要超过10万以上，我们才会创建索引，并且添加索引的字段是查询比较频繁的字段，一般也是像作为查询条件，排序字段或分组的字段这些。还有就是，我们通常创建索引的时候都是使用复合索引来创建，一条sql的返回值，尽量使用覆盖索引，如果字段的区分度不高的话，我们也会把它放在组合索引后面的字段。如果某一个字段的内容较长，我们会考虑使用前缀索引来使用，当然并不是所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导致新增改的速度变慢。

       原则：

1). 针对于数据量较大，且查询比较频繁的表建立索引。（单表超过十万数据）

2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候使用覆盖索引，节省存储空间，避免回表，提高查询效率。

6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

9.什么情况下索引会失效？

       索引失效的场景很多，比如索引在使用的时候没有遵循最左匹配法则，第二个是，糊查询，如果%号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。我们之前还遇到过一个就是，如果使用了复合索引，中间使用了范围查询右边的条件索引也会失效；所以，通常情况下，想要判断出这条sql是否有索引失效的情况，可以使用explain执行计划来分析。

       索引失效情况：

• 违反最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中间的列。
• 范围查询右边的列，不能使用索引。中间使用了>,<范围查询，右边的列不生效。
• 不要在索引列上进行运算操作， 索引将失效
• 字符串不加单引号，造成索引失效。（字符串不加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。）
• 以%开头的Like模糊查询，索引失效