这句简单的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

（编辑：济南站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!