MySQL如何创建一个好索引?创建索引的5条建议
过滤效率高的放前面
对于一个多列索引,它的存储顺序是先按第一列进行比较,然后是第二列,第三列...这样。查询时,如果第一列能够排除的越多,那么后面列需要判断的行数就越少,效率越高。
关于如何判断哪个列的过滤效率更高,可以通过选择性计算来决定。例如我们要在books表创建一个name列和author列的索引,可以计算这两列各自的选择性:
select count(distinct name) / count(*) as name, count(distinct author) / count(*) as author from books;
最后得出结果如下:
显然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会自动使用前面创建索引。
索引是否越多越好呢?
显然不是,因为索引是对原表的数据冗余,那么他就必须要保证数据的一致性。如果原表增加了一条数据,索引也需要增加。如果原表修改了一条数据,那么对应的索引可能也要修改内容以及排序的位置,这可能会造成页分裂或页合并。一个表如果索引过多,那么维护索引与表的数据一致性也是不小的压力。通常建议在满足需求前提下,索引越少越好。