你真的了解索引吗（下）？|MySQL 系列（7）

作者 | 小汪哥写代码
来源 | 小汪哥(ID：xwgcoding)

索引种类

聚簇索引

• 「主键索引」(PRIMARY KEY)：主键索引一般都是在创建表的时候指定，不指定的话会默认生成一个(row_id)「一个表只有一个主键索引」，特点是「唯一、非空」。
• 「不为空的唯一索引」: 如果存在非空的唯一索引，不会生成row_id

非聚簇索引(二级索引)

• 「唯一索引」(UNIQUE)：唯一索引具有的特点就是唯一性，可以在创建表的时候指定，也可以在创建表后创建。
• 「普通索引」(INDEX)：普通索引唯一的作用就是加快查询。
• 「组合索引」( INDEX)：组合索引是创建一个「多个字段的索引」，这个概念是相对于上上面的单列索引而言，组合索引查询遵循「最左前缀原则」。

全文索引

底层的数据结构：全文索引底层使用倒排索引来实现，倒排索引和B+树索引一样，都是一种索引结构。这种索引结构会创建一个辅助表，这个“辅助表”里面存储单词和单词所在的一个或者多个文档之间的映射。这个辅助表通常使用关联数组来实现。

比如我们创建一张表

主键索引：完整的数据在叶子节点上，非叶子节点只有主键和页号等相关信息

用name 创建二级索引 ：叶子节点包括name和id，非叶子节点只有name和页号等相关信息

用name 和age 创建组合索引 ：叶子节点包括name，age和id，非叶子节点只有name、age和页号等相关信息

关于回表

什么是回表查询呢?回表查询简单来说「通过二级索引查询数据，得不到完整的数据行，需要再次查询主键索引来获得数据行」。

例如来查询:

select * from t where name ='aa';

我们用 index_name 这个二级索引查询有这么两个特点：

• 会使用到两个B+树索引，一个二级索引，一个聚簇索引。
• 访问二级索引使用顺序I/O，访问聚簇索引使用随机I/O。

需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引。比方说name值在Asa～Barlow之间的用户记录数量占全部记录数量90%以上，那么如果使用idx_name索引的话，有90%多的id值需要回表，这不是吃力不讨好么，还不如直接去扫描聚簇索引(也就是全表扫描)。

索引优化

使用联合索引

这也是减少回表，回表会增加随机IO，如上面的例子，我们查询id和name 就使用name 的二级索引，就不会回表了。因为叶子节点已经有我们想要的数据。

最左匹配原则

如果我们使用 name age 的联合索引，当where 条件中只有age 的时候，索引是不起作用的。必须有name.

匹配列前缀

为某个列建立索引的意思其实就是在对应的B+树的记录中使用该列的值进行排序,字符串也是排序的。

不在索引上使用表达式

这样的话用不到建立的索引存储引擎会依次遍历所有的记录，计算这个表达式的值

比如：select * from t where age/2>22 就无法用 index_age

索引列尽量小

能用char 不用varchar,能用INT 不用BIGINT，有两方面的考虑：

数据类型越小，在查询时进行的比较操作越快(这是CPU层次的东东)

数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

索引列的区分度要高

因为所有值都一样就无法排序，无法进行快速查找了，而且如果某个建立了二级索引的列的重复值特别多，那么使用这个二级索引查出的记录还可能要做回表操作，这样性能损耗就更大了。