Mysql中的索引详解

索引

索引的优缺点

优点	缺点
加快查询效率（很好理解）	索引需要占用磁盘空间
加快排序效率（每次需要排序操作时，不用再进行一次排序了，B+树索引是排序好的，直接取就行。）	索引需要维护，在更新表的时候，对应的索引也需要更新。

索引种类

索引结构	描述
B+Tree索引	最常见的索引，大部分引擎都支持B+树索引
Hash索引	底层数据结构是哈希表，精确查询有效，不适合范围查询。
R-tree（空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。
Full-text（全文索引）	是一种通过建立倒排索引，快速匹配文档的方式，类似于Lucene、Solr、ES

我们使用的MySQL的innodb引擎就是使用的B+树索引，Memory引擎使用的是hash索引。

索引分类

按照功能维度划分

主键索引： 主键索引一般是自动创建的，如果一个表没有主键，那么innodb也会对表记录的隐藏主键字段创建一个主键索引。主键索引一般就是数据存储的地方，就是聚簇索引。

唯一索引： 主键索引就是唯一索引，只要这个字段被设置成了唯一的，那么就可以对这个字段创建一个唯一索引。

普通索引： 普通索引的话就是对普通字段（非唯一、非主键）创建的索引。

按照数据存储维度划分

分类依据：索引中是否存储了数据。

聚簇索引： 聚簇索引的叶子节点保存着这行记录的所有字段的数据。一般主键索引就是聚簇索引，找数据也是从聚簇索引中读取到的。

非聚簇索引： 非聚簇索引中不保存具体的数据，叶子节点中记录的是该行记录的主键值，需要二次回表到主键索引（聚簇索引）中查找到对应的数据。

按照设计维度划分

分类依据：一个索引中包含的字段列数。

单列索引： 索引只包含一个字段列。

联合索引： 索引包含多个字段列。联合索引可以结合覆盖索引。如果业务中我们需要频繁查询一个表的某几个字段数据，那么为了实现覆盖索引，我们就可以给这几个字段建立一个联合索引，然后查询的时候就会去走这个联合索引，可以避免回表查询。

注： 联合索引有最左前缀原则。比如有索引 (a, b, c)，则以下都可以使用该索引：

• WHERE a = ?
• WHERE a = ? AND b = ?
• WHERE a = ? AND b = ? AND c = ?
• WHERE a = ? AND b > ?（范围查询也可以）

但不能用在 WHERE b = ?、WHERE c = ?，因为跳过了最左列。

覆盖索引

覆盖索引就是说我们从一个非聚簇索引查找数据的时候不再需要回表，直接在二级索引中就能查找到对应的目标字段值，一般就需要我们使用联合索引来实现。就比如：

-- 创建联合索引 (name, age)
CREATE INDEX idx_name_age ON user(name, age);
SELECT name, age FROM user WHERE name = 'Tom';

查询用到了索引的前缀字段 name ，查询的字段 name 和 age 都包含在索引 (name, age) 中， 所以不需要回表，直接从索引页中取出 name 和 age 值即可。 可以减少I/O次数。

索引是越多越好？

并不是的。大多数情况下我们建立索引是希望他能给我带来查询效率的提升，但是我们要注意索引建立了之后我们是要维护的，每次对一张表的修改（更新、删除、增加），我们都是要同时维护与这个表中所有字段相关的索引的，如果索引太多，我们需要维护的索引就会很多。

并且，有的时候表明明本身就很小，全表扫描速度就很高，没必要去给它建立索引。

哪些字段应该建立索引？

首先，索引是提高了查询效率，所以我们也是需要对那些频繁作为查询条件的字段建立索引。经常用于where条件、join条件、order by、group by等的都可以建立成索引。

其次，我们要注意要给区分度高的字段建立索引，什么是区分度高呢？就是这个字段中重复的值不要太多，这样我们才可以利用B+树的性质给这个字段进行排序然后建立索引。像性别字段就不适合做索引，因为只有男女两个，不是很好区分。

还有我们不要给频繁更新的字段建立索引，因为索引需要维护，我们建立的索引最好是对查询多余更新的字段。这样才能够真正地提升效率。

小表也不需要建立索引，就算对这张表进行全表扫描我们可能也并不需要进行几次I/O，那么建立索引反而会造成空间浪费，维护成本增大。

大字段也不要建立索引，大字段本身做成索引的话存储就很耗费空间，反而可能导致IO次数变多，性能下降。