当前位置: 首页 > 科技观察

年轻人不讲武德,乱用索引,你到底走了多少弯路-

时间:2023-03-14 23:25:20 科技观察

年轻人不讲武功,乱用指标。走了多少弯路?转载本文请联系码上Java公众号。前言在上一篇文章中,我们讨论了索引的底层原理以及为什么我们经常使用B+树作为索引的数据结构。这篇文章,我们一起来学习一下索引创建的原理,看看什么时候适合创建索引?什么时候不适合创建索引?什么情况下适合创建索引1、字段的值有唯一性限制。比如用户名索引本身就可以起到约束的作用,比如唯一索引,主键索引可以起到唯一约束,所以在我们的数据表中,如果一个字段是唯一的,那么可以直接创建唯一索引,或者primary关键索引。2.经常被用作WHERE查询条件的字段,尤其是在数据表很大,数据量很大的情况下,SQL查询的WHERE条件中经常会用到一个字段,那么就需要为这个字段创建索引。创建一个普通的索引可以大大提高数据查询的效率。3.需要频繁使用GROUPBY和ORDERBY的列索引是按照一定的顺序存储或检索数据,所以当我们使用GROUPBY对数据进行分组,或者使用ORDERBY对数据进行排序时,我们需要对分组或排序的字段进行分组索引。4、UPDATE和DELETE的WHERE条件列在执行UPDATE或DELETE操作前,一般需要先创建索引,根据一定的条件查询数据。如果为WHERE字段创建索引,效率可以大大提高。原理是因为我们需要根据WHERE条件列来检索这条记录,然后更新或者删除。如果更新时更新的字段是非索引字段,效率提升会更加明显,因为非索引字段的更新不需要维护索引。但是,在实际工作中,我们也需要注意平衡。如果索引太多,在更新数据的时候,如果涉及到索引更新,就会造成负担。5、DISTINCT字段需要创建索引。有时我们需要对某个字段进行去重。使用DISTINCT,为该字段创建索引,也会提高查询效率。6、在做多表JOIN连接操作时,创建索引需要注意以下原则。连表的数量不要超过3个,因为每增加一个表就相当于增加了一个嵌套循环,数量级会增长得非常快。严重影响查询效率。在WHERE条件上创建索引,因为WHERE是数据条件的过滤器。如果数据量非常大,没有WHERE条件过滤就惨了。在用于连接的字段上创建索引,该字段的类型在多个表中必须保持一致。什么时候适合建立索引1、频繁更新的字段不适合建立索引。为什么是这样?你需要知道为什么索引可以提高查询效率。这是因为索引在插入数据时对数据进行了排序。因此,如果涉及频繁更新的字段,此时不适合做索引。这里我们以B+树索引为例(B+树索引是数据库中最常见的索引数据结构,几乎所有的关系型数据库都支持),B+树在插入时必须对数据进行排序,其实就是排序的开销并不大,那是因为排序是一个CPU操作(目前的CPU在一个时钟周期内可以处理数亿条指令)。如果按顺序或者倒序插入数据都可以,此时B+树索引维护成本比较低,因为叶子节点是从左到右插入的,比如自增ID插入,时间插入(如果在auto-increment在增量ID上创建索引,在时间列上创建索引,那么B+树插入通常会更快)。但是,如果插入的数据是乱序的,为了保持排序,B+树需要进行分页、旋转页面等代价高昂的操作。此外,即使是固态硬盘,随机写入的性能也不如顺序写入,因此磁盘性能也较低。会受到很大的影响。比如用户昵称,每个用户注册的时候,大部分的昵称都是随机选择的。如果在昵称上创建索引,插入会乱序,索引维护所需的开销会比较大。2、WHERE条件中没有用到的字段不适合做索引。为什么是这样?索引的价值在于快速定位。如果定位不到的字段通常不需要创建索引。3、数据比较少的时候不适合做索引。为什么是这样?这很简单。因为在数据量小的情况下,没有必要使用索引。全表扫描的速度已经非常快了。即使使用索引,索引也不起作用。没用的。4、唯一性差的字段不适合做索引。为什么是这样?这也很简单。你可以想象一个非常极端的情况。假设数据表中的数据是一样的(不可能),你用索引也没用。我们可以以性别领域为例。性别无非就是两种可能,男或者女(这里不要用bar),因为访问索引需要付出额外的IO开销,而从索引中得到的只是地址(记住只是地址),如果你想真正访问数据,你仍然需要对表进行IO。如果要从表的100万行数据中获取一些数据,那就用索引快速定位,访问索引的IO开销也是非常值得的。但是如果从100万行数据中提取50万行数据,比如性别字段,那么就需要访问索引50万次,然后访问表50万次。增加的开销可想而知。不亚于直接全表扫描。所以即使你用了索引,也没用。5.涉及到列计算的列不适合建立索引。为什么是这样?这是因为,如果对索引列进行函数操作,索引将不会被使用,即索引失效。比如你在T表上有一个索引Y,但是你的查询语句是这样的SELECT*FROMTWHEREFUN(Y)=XXX。这时候就不会用到索引了,因为你要查询的列中的所有行都需要计算一次。小结今天,我们简单梳理一下索引使用的相关原则。记住:索引可以提高SQL查询的效率,但索引不是万能的,必须遵循基本原则。