六合开奖结果记录历史|六彩开奖结果今晚直播|11kj开奖现场开奖直播

这句简单的 sql 如何加索引?颠覆了我多年的认知

发布日期:2019-09-11 17:05   来源:未知   阅读:

  •   2019年六开彩今晚开奖,不啰嗦,直接入正题。问题是这样的。请问下面的sql语句,要想加快查询速度,该怎么创建索引?以下,以mysql数据库为准。

      结果可能会出乎你的意料。我们首先准备一下运行环境,然后按照最左前缀原则和explain关键字来进行验证。结果真是颠覆了我多年的认知。

      为了进行验证,我们创建一个简单的数据表。里面有a、b、c三个简单的int字段。

      接下来,写一个简单的存储过程,来插入10w条数据。等待大约1分钟,数据插入完毕。

      由于mysql有最左前缀原则,我们对abc三列进行了全排列,创建了6个索引。这6个索引涵盖了所有的根据abc查询的情况。

      首先,我们拿上面的sql语句进行验证。结果发现,查询使用了索引idx_a_b_c,只用到了前缀a,b。而extra部分,则用到了filesort,也就是性能非常差的方式。

      这次索引自动选择了idx_b_a_c,但依然使用的filesort,查询效果是一样的。按照上面的逻辑,不是应该选择idx_b_c_a么?

      我们使用force index来指定使用的索引。这次效果非常好,显示使用了index,使用了where,只在索引上就完成了操作。但扫描的行数却增加了。

      但是,这与我们的经验是相悖的。idx_c_b_a的索引,是在字段(c,b,a)上创建的。按照最左原则,支持的搜索条件有:c,cb,cba。在这个例子中,order by后面的参数,却被当作了前缀的头部信息。

      我们删掉其他索引,只留下idx_c_b_a,然后去掉force index部分。结果发现,mysql现在能够自动的选择索引了。

      结果如上,使用idx_b_c_a,不走filesort。其他索引都不是最优。

      我们得出上面的结论,是根据mysql自己提供的explain工具。这个工具能够输出一些有用的信息。下面是相关的部分返回值的意义。

      表示MySQL在表中找到所需行的方式,或者叫访问类型。常见访问类型如下,从下到上,性能越来越差。

      system,const表只有一行记录(等于系统表),这是const类型的特列。

      ref非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。

      range只检索给定范围的行,使用一个索引来选择行,key列显示使用了哪个索引。这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引。

      表示相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率不错。如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。

      说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。

      使用了用临时表保存中间结果,mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

      可以看到,在我们创建了多个索引的时候,mysql的查询优化,并不一定能够进行智能的解析、用到最优的方式,需要使用force index指定索引。

      mysql中的索引,主要就用在where条件中和排序动作中。分两种情况。

      我想,mysql并不能够了解到这两个过程,到底谁快谁慢,于是选了一个最通用的方式,直接选用了第一种。甚至在索引非常多的时候,直接晕菜了。索引建多了,你可能间接把mysql给害了。这是现象,至于深层次的原因,欢迎读过mysql相关源码的给解释一下。

      这对经常变换字段进行排序的代码来说,并不是一个好的信号。考虑到程序的稳定性,我想应该要尽量减少where条件过滤后的结果集。这种情况下,创建一个(a,b)的联合索引,或许是一个折衷的方式。返回搜狐,查看更多