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