专栏首页架构专题这句简单的sql,如何加索引?颠覆了我多年的认知

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

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

select * from test where a=? and b>? order by c limit 0,100

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

准备阶段

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

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

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

DROP PROCEDURE IF EXISTS test_initData;
DELIMITER $
CREATE PROCEDURE test_initData()
BEGIN>
    DECLARE i INT DEFAULT 1;
    WHILE i<=100000 DO
        INSERT INTO test(id,a,b,c) VALUES(i,i*2,i*3,i*4);
        SET i = i+1;
    END WHILE;
END $
CALL test_initData();

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

create INDEX idx_a_b_c on test(a,b,c);
create INDEX idx_a_c_b on test(a,c,b);
create INDEX idx_b_a_c on test(b,a,c);
create INDEX idx_b_c_a on test(b,c,a);
create INDEX idx_c_a_b on test(c,a,b);
create INDEX idx_c_b_a on test(c,b,a);

使用Explain进行验证

1、自动选用索引

 explain select * from test where a>10 and b >10  order by c

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

我们尝试换一下查询参数的位置。

 explain select * from test where c>10 and b >10   order by a

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

2、指定索引

接下来使用force index方式,强制指定索引。 这里直接给出结果,就是下面的sql。

 explain select * from test
    FORCE INDEX(idx_c_b_a)    where a>10 and b >10  order by c

结果如下。

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

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

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

在看另外一种情况,order by上有两个参数。

 explain select * from test
    FORCE INDEX(idx_b_c_a)
    where a>10   order by b,c

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

3、explain部分返回值意义

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

select_type 表示SELECT的类型,常见的取值有:

SIMPLE 简单表,不使用表连接或子查询。 PRIMARY 主查询,即外层的查询。 UNION UNION中的第二个或者后面的查询语句。 SUBQUERY 子查询中的第一个。

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

system,const 表只有一行记录(等于系统表),这是const类型的特列。 eq_ref 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。 ref 非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。 range 只检索给定范围的行,使用一个索引来选择行,key列显示使用了哪个索引。这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引。 index Full Index Scan,Index与All区别为index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。 all 全表扫描,性能最差

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

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

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

End

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

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

1、先过滤,再排序,会用到过滤条件中的索引参数,但是排序会使用较慢的外部排序。因为这个结果集是经过过滤的,并没有什么索引参与。

2、先排序,再过滤,可以使用同一个索引,排序的优先级高于过滤的优先级。选择合适的索引,在过滤的同时就把这个事给办了。但是扫描的行数会增加。

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

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

本文分享自微信公众号 - 小姐姐味道(xjjdog),作者:小姐姐养的狗

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-08-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 2年java,蚂蚁一面,卒

    作为程序员,我也希望做一枚运营狗。可惜并没多少时间,所以每到过节都发水文。端午快乐。

    xjjdog
  • Java堆外内存排查小结

    这几天遇到一个比较奇怪的问题,觉得有必要和大家分享一下。我们的一个服务,运行在docker上,在某个版本之后,占用的内存开始增长,直到docker分配的内存上限...

    xjjdog
  • 上厅房,下厨房,ElasticSearch有的忙

    强烈建议先读一下本公众号《也浅谈下分布式存储要点》,对ES会有更好的认识。ES融合了倒排索引、行存、列存的诸多特点,已经不再是一个简单的全文搜索引擎。

    xjjdog
  • 面试官:谈谈你对mysql索引的认识?

    其实这下面每个问题,我都可以讲一篇文章出来!而且这些问题,不是我凭空编的。如下图所示(注意看第三题)

    乔戈里
  • MySQL 数据库设计总结

    MySQL支持很多种不同的数据类型,并且选择正确的数据类型对于获得高性能至关重要。本文将对MySQL 数据库设计总结,希望与大家共同探讨。

    漆洪凯
  • 利用pandas进行数据分析(二):索引与层次化索引

    继上一节的基本数据结构的介绍之后,本节继续介绍中操作和的基本手段。一个最常用的操作就是索引,如何根据分析目的对和进行索引访问得到数据是利用进行数据分析的基本技能...

    企鹅号小编
  • 面试技巧,如何通过索引说数据库优化能力,内容来自Java web轻量级开发面试教程

           如果我们需要招个Java方面的高级程序员,一方面看年限(本科3年),具体到数据库方面的技能要求,包括如下三个方面:        第一,是否会...

    用户1153489
  • mysql索引使用技巧及注意事项

    一.索引的作用       一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂...

    猿人谷
  • MySQL InnoDB创建索引

    InnoDB的索引基于B+树实现,每张InnoDB的表都有一个特殊的索引,叫做聚簇索引(Clustered Index),聚簇索引存储了表中的真实数据。索引项的...

    devsql
  • Oracle 索引

    create table bigdata as select rownum as id, TO_CHAR(sysdate,'yyyy-mm-dd hh24:m...

    郑小超.

扫码关注云+社区

领取腾讯云代金券