MySQL如何创建一个好索引?创建索引的5条建议

过滤效率高的放前面

对于一个多列索引,它的存储顺序是先按第一列进行比较,然后是第二列,第三列…这样。查询时,如果第一列能够排除的越多,那么后面列需要判断的行数就越少,效率越高。

关于如何判断哪个列的过滤效率更高,可以通过选择性计算来决定。例如我们要在books表创建一个name列和author列的索引,可以计算这两列各自的选择性:

select count(distinct name) / count(*) as name, count(distinct author) / count(*) as author from books;

最后得出结果如下:

Name

author

0.95

0.9

显然name字段的选择性更高,那么如果把name放第一列,在name条件过滤时就可以排除更多的列,减少接下来 author的过滤。

使用频率高的放前面

其实该建议比上一个建议优先级更高

例如一个商品管理页面,一般都是基于该店家的上架或已下架的商品,再添加其他的查询条件等等。由于所有的查询都需要带有shopid和status条件,此时应该优先将这两个条件作为基本前缀,这样就可以方便复用索引。

例如一个(shopid, status, createdat)的索引,当查询条件只有shopid和status时,也可以使用该索引。如果完全根据字段的过滤效率来决定索引,就需要创建很多不同的索引。

避免排序

索引的值都是有序排列的,在创建索引时还可以显式指定每个列的排序方式,例如

create index idx_books_author_created_at on books (author, created_at DESC);

此时,如果执行下面的的查询

select * from books where author = \'author\' order by created_at DESC;

由于满足auhtor的索引的created_at列都是有序排列的,所以不需要再进行额外的排序操作。

 

当结果数据集很大时,应该尽可能的通过索引来避免查询的额外排序,因为当内存排序空间(sort_buffer_size)不够用时,就需要把一部分内容放到硬盘中,此时会很影响性能。

例如一个分页查询每页显示100条,按从大到小的顺序显示,当浏览到第100页时,如果查询是file sort的,数据库需要使用堆排序先计算出这个表里面前100 * 100 = 10000条最大的数据,然后取9900 – 10000之间的数据返回给客户端,在计算的过程中,这个最大堆如果放不下就需要保存到磁盘中,但是又需要频繁比较和替换。

减少随机IO

在之前对硬盘知识了解后可以知道,一次随机读会有10ms的寻址延迟,如果一次查询涉及达到多次的随机读,会很大程度的限制查询性能。常见的sql查询造成随机IO的包括回表和join

例如下面的查询

select * from books where author = \'author1\';

如果author1有100本书,但是这100本书并不是连续录入的,也就是说这100本书在硬盘中的存储是分离的。那么在有二级索引(author, created_at)的情况下,MySQL先通过二级索引找到满足author1的所有books的id,然后再通过id在聚簇索引中找到具体数据。

在这一过程中,二级索引的存储可以认为是连续的,那么二级索引耗时就是10ms + 100 * 0.01 = 11ms,包含一次寻址以及接下来的顺序读。而主键索引回表造成的随机IO最差情况是10ms * 100 = 1000ms。那么一共就需要11ms + 1000ms = 1011ms

通常减少随机IO的一种方式就使用覆盖索引。例如上面的查询中,如果我们只是想要该作者的书名,可以将(author, createdat)扩展为(author, createdat,name),然后将sql修改如下

select name from books where author = \'author1\';

由于索引中已经有name的信息,此时就不会再次回表,查询耗时就变成了10ms + 100 * 0.01 = 11ms

 

值得一提的是mysql5.6新增一个叫做索引条件下推的优化,例如在有索引(author, created_at,name)的情况下,进行下面的查询:

select name from books where author = \'author1\' and name like \'%name%\' and created_at > \'2020-01-01\';

根据最左匹配原则,这个查询只能用到索引的author字段,如果没有索引条件下推优化,数据库需要在二级索引找到满足author条件的所有列id,然后回表找到剩余信息后,再过滤name和created_at条件。

有了索引条件下推,在找到满足author条件的所有索引后,会再用索引的name字段进行普通过滤,尽量减少回表的次数,减少随机IO

避免重复索引

减少随机IO中的查询为例,我们最终是把(author, createdat)扩展为(author, createdat,name),而不是创建一个新的(author, name)的索引。

在实际应用场景中也有类似的情况,例如创建一个userid的外键索引,然后又创建(userid, xxx)的索引。由于索引存储的顺序性,其实可以将这两个索引进行合并,如果我们先创建(userid, xxx)的索引,然后再添加userid的外键,mysql会自动使用前面创建索引。

索引是否越多越好呢?

显然不是,因为索引是对原表的数据冗余,那么他就必须要保证数据的一致性。如果原表增加了一条数据,索引也需要增加。如果原表修改了一条数据,那么对应的索引可能也要修改内容以及排序的位置,这可能会造成页分裂或页合并。一个表如果索引过多,那么维护索引与表的数据一致性也是不小的压力。通常建议在满足需求前提下,索引越少越好。

 

版权声明:本文为chenkeyu原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://www.cnblogs.com/chenkeyu/p/12799207.html