阅读 137

你真的了解索引吗(下)?|mysql 系列(7)

上一篇我们认识了下什么是索引,今天我们来聊聊索引的使用和优化

索引种类

聚簇索引

  1. 「主键索引」PRIMARY KEY):主键索引一般都是在创建表的时候指定,不指定的话会默认生成一个(row_id)「一个表只有一个主键索引」,特点是**「唯一、非空」**。

  2. 「不为空的唯一索引」: 如果存在非空的唯一索引,不会生成row_id

非聚簇索引(二级索引)

  1. 「唯一索引」UNIQUE):唯一索引具有的特点就是唯一性,可以在创建表的时候指定,也可以在创建表后创建。

  2. 「普通索引」INDEX):普通索引唯一的作用就是加快查询。

  3. 「组合索引」INDEX):组合索引是创建一个**「多个字段的索引」,这个概念是相对于上上面的单列索引而言,组合索引查询遵循「最左前缀原则」**。

全文索引

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

比如我们创建一张表


中文描述

id
序号
自增主键
age
年龄

name
名称

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

用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值需要回表,这不是吃力不讨好么,还不如直接去扫描聚簇索引(也就是全表扫描)。

索引优化

  • 使用联合索引
  1. 这也是减少回表,回表会增加随机IO,如上面的例子,我们查询id和name 就使用name 的二级索引,就不会回表了。因为叶子节点已经有我们想要的数据。
  • 最左匹配原则
  1. 如果我们使用 name age 的联合索引,当where 条件中只有age 的时候,索引是不起作用的。必须有name.
  • 匹配列前缀
  1. 为某个列建立索引的意思其实就是在对应的B+树的记录中使用该列的值进行排序,字符串也是排序的。
  • 不在索引上使用表达式
  1. 这样的话用不到建立的索引存储引擎会依次遍历所有的记录,计算这个表达式的值 

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

  • 索引列尽量小
  1. ‍能用char 不用varchar,能用INT 不用BIGINT,有两方面的考虑:
    1. 数据类型越小,在查询时进行的比较操作越快(这是CPU层次的东东)
    1. 数据类型越小,索引占用的存储空间就越少,在一个数据页内就可以放下更多的记录,从而减少磁盘I/O带来的性能损耗,也就意味着可以把更多的数据页缓存在内存中,从而加快读写效率。

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

欢迎关注、点赞、留言、拍砖, 关注公众号【小汪哥写代码】获取更多干货

文章分类
后端
文章标签