不啰嗦,直接入正题。问题是这样的。请问下面的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 ii = 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工具。这个工具能够输出一些有用的信息。下面是相关的部分返回值的意义。
(1) select_type
表示SELECT的类型,常见的取值有:
(2) type
表示MySQL在表中找到所需行的方式,或者叫访问类型。常见访问类型如下,从下到上,性能越来越差。
(3) Extra
End
可以看到,在我们创建了多个索引的时候,mysql的查询优化,并不一定能够进行智能的解析、用到最优的方式,需要使用force index指定索引。
mysql中的索引,主要就用在where条件中和排序动作中。分两种情况。
我想,mysql并不能够了解到这两个过程,到底谁快谁慢,于是选了一个最通用的方式,直接选用了第一种。甚至在索引非常多的时候,直接晕菜了。索引建多了,你可能间接把mysql给害了。这是现象,至于深层次的原因,欢迎读过mysql相关源码的给解释一下。
这对经常变换字段进行排序的代码来说,并不是一个好的信号。考虑到程序的稳定性,我想应该要尽量减少where条件过滤后的结果集。这种情况下,创建一个(a,b)的联合索引,或许是一个折衷的方式。
最近需要在Linux系统上使用MySQL记录一下安装过程吧为后来的同学排除一些问题、 ...
新写的网站用UEditor做编辑器,结果发现每次编辑完都会多出首尾空行,像这样: ...
Windows 10提供二种命令行界面:命令提示符和PowerShell。Windows PowerShell是...
a标签主要用来实现页面跳转,可以通过href属性实现,也可以在onclick事件里实现...
方法1:设置readonly属性为true。 INPUT value=readonly readOnly 方法2:设置di...
Java学习笔记系列——孙不坚1208 【详细超基...
通过Response.Flush()实现对服务端文件的下载时,会失败,不能正常弹出IE下载框...
为了上班方便,去年我把自己在北郊的房子租出去了,搬到了南郊,这样离我上班的...
昨晚被一则新闻刷屏:北京时间 4 月 10 日今晚 9 点,人类首张黑洞照片正式发布...
一、 idea中配置git 先配置好git的本地地址,然后test,出现版本号说明测试成功...