【引言】

索引，是我们在优化查询sql的场景中最先想到的解决方案。

什么情况下需要建立索引，建立什么类型的索引，在什么字段上建立索引，这都是我们需要考虑的问题。

对于上述问题，如果对索引的相关理论有很清晰的认识，就很容易处理了；反之，则只能是丈二和尚摸不着头脑，成不成功都靠运气。

下面就总结一些关于索引的知识。

索引是什么

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

索引的分类

MySQL数据库中，我们从不同的层面上划分，可总结为以下几种类型：

  1. 从存储层面上看，可分为聚簇索引和非聚簇索引。
  2. 从逻辑层面上看，可分为主键索引，复合索引，唯一索引等。
  3. 从数据结构上看，可分为B+树，Hash索引，R-Tree等。
各类型索引的含义
  1. 聚簇索引：对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。
    在MySQL中，Innodb存储引擎下的主键索引就是一种聚簇索引，既存储索引值，又在叶子中存储行的数据。
  2. 非聚簇索引：表中行的物理顺序与键值的逻辑顺序不匹配。
  • 聚簇索引和非聚簇索引的区别：聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。
    举个例子，字典默认按字母顺序排序，读者如知道某个字的读音可根据字母顺序快速定位。因此聚集索引和表的内容是在一起的。如读者需查询某个生僻字，则需按字典前面的索引，举例按偏旁进行定位，找到该字对应的页数，再打开对应页数找到该字。这种通过两个地方而查询到某个字的方式就如非聚集索引。
  1. 主键索引：数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的主键，并将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
  2. 唯一索引：不允许其中任何两行具有相同索引值的索引。
  3. 复合索引：在MySQL中，可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组<A,B, …, N>，其中各个元素均为数据表的一列。
  4. B+树：B+树与B树的不同在于每个节点的指针上限为2d而不是2d+1。内节点不存储data，只存储key；叶子节点不存储指针。
    Hash索引：哈希表是另外一种用作索引的数据结构，这些索引通常被称为哈希索引。使用哈希索引的原因是，在寻找值时哈希表效率极高。所以，如果使用哈希索引，对于比较字符串是否相等的查询能够极快的检索出的值。
索引的好处
  1. 索引大大减少了存储引擎需要扫描的数据量
  2. 索引可以帮助我们进行排序以避免使用临时表
  3. 索引可以把随机I/O变为顺序I/O
使用索引的代价
  1. 索引会占用空间，表越大，索引占用的空间越大。
  2. 性能损失（主要值更新操作），当在表中添加、删除或者更新行数据的时候， 在索引中也会有相同的操作。
建立索引的原则

基本原则是如果表中某列在查询过程中使用的非常频繁，那就在该列上创建索引。

对于一下一些情况，不建议建立索引：

  1. 对于那些查询中很少涉及的列，重复值比较多的列不要建索引
  2. 表记录比较少
  3. 索引的选择性较低
如何分析索引的使用

使用Explain分析SQL，结果如下：

mysql in索引在数据量大的情况下 mysql索引能存多少数据_聚簇索引

  1. select_type：表示SELECT的类型

类型

说明

SIMPLE

简单表，不使用表连接或子查询

PRIMARY

主查询，即外层的查询

UNION

UNION中的第二个或者后面的查询语句

SUBQUERY

子查询中的第一个

  1. table：输出结果集的表（表别名）
  2. type：表示MySQL在表中找到所需行的方式，或者叫访问类型。常见访问类型如下，从上到下，性能由差到最好：

类型

说明

ALL

全表扫描

index

索引全扫描

range

索引范围扫描

ref

非唯一索引扫描

eq_ref

唯一索引扫描

const,system

单表最多有一个匹配行

NULL

不用扫描表或索引

  1. possible_keys: 表示查询可能使用的索引
  2. key: 实际使用的索引
  3. key_len: 使用索引字段的长度
  4. ref: 使用哪个列或常数与key一起从表中选择行。
  5. rows: 扫描行的数量
  6. filtered: 存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例(百分比)
  7. Extra: 执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

是否使用到我们加的索引，关注Key的结果列即可。

【总结】

通过总结，明白了一些以前不知道的概念，这是收获。同时，也发现了其他一些不太懂的东西，还需要学习。