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

关于创建索引的五条建议

　　1. 过滤效率高的放前面

　　2. 使用频率高的放前面

　　3. 避免排序

　　4. 避免随机IO

　　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会自动使用前面创建索引。

索引是否越多越好呢？

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