从磁盘IO的角度聊聊MYSQL的索引

230 阅读2分钟

持续创作,加速成长!这是我参与「掘金日新计划 · 6 月更文挑战」的第13天

image.png

  • InoDB存储引擎中,主键尽量避免使用很长的字段;每个叶子节点中除了记录索引值,也存储了这个索引值对应的数据记录的指向。例子:用id等于某个主键值来表示数据记录的指向,事实上根据mysql存储引擎的不同,数据记录的指向,存储内容也是不同的

  • 在保障索引区分度的情况下,被索引的字段被索引的字段尽量不要太长

  • 索引的区分度:不重复的索引值(基数)/总记录数,,取值范围(0,1]之间,越接近1,表示索引列重复次数越少,索引的使用越有价值。

比如:select count(distinct(name)) /count(*) as selecttivity from t_student;

select count(distinct(left(name,3))) /count(*) as selecttivity from t_student;

在实际应用中,我们可以逐步缩短索引字段的长度,计算索引区分度的方式,来找到既能保持索引的区分度,又相对短一些的索引。 在mysql Inodb中, Btree索引的每个 节点都是一个磁盘页面,又被成为page。 通过show variables like ‘inodb_page_size’

  • 查询中可以利用索引覆盖,从而避免不必要的回表。 回表:通过二级索引无法查到整条的数据记录,需要我们根据二级索引中查到的主键,再去主键索引中查找,这个过程成为回表。如果查询的所需的信息,恰巧在二级索引中能够得到,则可以省去回表的过程,从而减少回表所需的磁盘I/O次数。

举例:select * from student where age = 10;

select id, name,age from student where age = 10; age\name是联合索引

  • 不要建立太多索引,索引并不是万能的,建立索引是有代价的,数据表中任何一条数据的写操作,都有可能索引树,随着索引数目的增加,数据表的更新操作,会浪费更多的磁盘I/O。 而且如果在索引区分度非常低字段建立索引,对查询效率不但没有多少提升,反而会降低插入,删除,更新等数据表操作的效率。