对于普通程序员来说最大的工作量就在CURD上了,并发量不高、数据量不大的情况下,SQL性能差一点无所谓,但是随着技术的提升就不应该仅仅满足于能用的SQL了,而应该写出更好的SQL。
网上有很多SQL优化方案的文章,但是我觉得知识不内化,知道也用不了。而知道原理后就能举一反三,在实际工作中用上。
为了这次总结SQL优化方案,之前写了一个 B+Tree总结 ,这次我们也是根据B+Tree原理去理解思考如何优化SQL。
profiling
SQL性能好不好,最硬的指标就是执行时间,这时可以使用到profiling查看具体SQL的执行时间。
使用方法也很简单:
- SET SESSION profiling = 1;
- SQL语句
- show profiles;
返回结果的Duration就是执行时间,时间精确到了小数点后8位,可以更明显的看出SQL执行耗时的差异。
执行计划 EXPLAIN
profiling只能看到SQL执行的耗时,但是我们需要有一个东西看看我们的SQL究竟走没走索引,走了哪个索引,这时我们就需要使用到explain命令。
对于结果我们通常关注一下几个内容:
type
访问类型,常用的类型有: ALL、index、range、 ref、eq_ref、const、system(从左到右,性能从差到好)。
- system一般在只有1条数据是出现。
- const在使用唯一索引或者主键,返回记录一定是1行记录的等值where条件时出现。
- eq_ref在连接表时字段是第二个表的主键或唯一索引时出现。
- ref在连接表时不是主键或唯一索引时出现,ref和eq_ref的差别在于是否只匹配到唯一的1条记录。
- range索引范围扫描,常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。
- index索引全表扫描,把索引从头到尾扫一遍。
- all全表扫描,没有走索引。
possible_keys
列出该SQL可以使用到的索引,但是只有其中1个会被使用到。
key
实际使用到的索引,肯定包含在possible_keys中。
key_len
索引中使用的字节数,可通过该列计算查询中使用的索引的长度。
如果是单列索引,则为该索引的长度;如果是联合索引,为实际使用到的长度,通过这个长度我们就可以知道是否合理创建/使用到了联合索引。
ref
如果是使用的常数等值查询,这里会显示const,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func。
Extra
辅助信息,根据辅助信息可以知道是否回表,性能消耗是否严重等。
- Using where:使用了where条件过滤数据。
- Using index:SQL所需要返回的所有列数据均在一棵索引树上,不用回表。
- Using filesort:排序时无法使用到索引时,就会出现这个。常见于order by和group by。
- Using temporary:表示使用了临时表存储中间结果。
- Using index condition:使用了索引,但是需要回表找到需要的数据。
优化方案分析
为了测试,我创建了一个user表并插入了10条数据:
CREATE TABLE `user` (
`id` bigint(20) NOT NULL COMMENT '主键ID',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
不要使用select *,应该列出具体使用的字段。
这应该是网上最常见的说法了,但是不使用select * 就一定更快吗?这个就要具体分析一下了。
根据网上的说法,select * 造成“慢”主要是有3个原因:
- 数据库需要把 * 翻译成字段名。
- 因为数据库是通过网络连接,所以多余字段的数据会增加网络传输的时间。
- 一般来说不能使用到覆盖索引,需要回表。
对于原因1,我们使用profiling测试一下两条SQL:
select * from user;
select id,name,age,email from user;
得到结果:
10 0.00019702 select * from user
11 0.00020811 select id,name,age,email from user
多次测试发现*甚至比列出字段名更快。
查询了一下,有人表示MySQL对*有优化,一般来说原因1并不一定成立。
对于原因2,因为涉及到网络传输,不好测试,但是理论是成立的,数据量大传输耗时就长。
但是我们的程序和数据库都是在同一个内网中,并且一般很少遇到某个字段的数据量非常大的情况。
所以原因2一般也不是主要原因。
对于原因3,如果需要回表,就会在聚簇索引上进行额外的IO操作。
select id,name,age from user where name='ZhangSan' and age=12;
不加索引的explain结果:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user ALL 10 Using where
然后我们创建一个联合索引测试一下:
ALTER TABLE `user`
ADD INDEX `idx_name_age` (`name`, `age`) USING BTREE ;
加索引的explain结果:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user ref idx_name_age idx_name_age 128 const,const 1 Using where; Using index
Extra多了一个Using index,说明在该索引上就能直接找到需要的数据,不需要回表。
如果我们再加一个不在联合索引中的值再看看:
select id,name,age,email from user where name='ZhangSan' and age=12;
explain结果:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user ref idx_name_age idx_name_age 128 const,const 1 Using index condition
Using index condition,如果有不在联合索引中的字段,则需要回表。
如果要对这3个原因的影响大小进行排序的话,从1到3的影响依次从小到大。
很显然原因3才是造成select * “慢”的主要原因。
当然,如果你没有实现覆盖索引,那么可以认为select *与列出字段的方式性能差别不大。
对于如何实现覆盖索引可以看看MySQL索引原理 B+Tree总结。
最左原则
最左原则是针对联合索引的使用来说的,并不是说where后面多个条件的左边。
以index(name, age)为例,构建的B+Tree是先根据name字段创建,然后再针对相同name的值的数据,再以age字段创建一个B+Tree。
所以我们写SQL时,如果想用到age的B+Tree,那么就一定需要先使用name的B+Tree。
例子SQL就是之前的SQL:
select id,name,age from user where name='ZhangSan' and age=12;
explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user ref idx_name_age idx_name_age 128 const,const 1 Using where; Using index
可以看到使用到了idx_name_age索引,并且key_len为128。
当然,联合索引我们也可以只使用左边的索引而不使用右边的索引:
select id,name,age from user where name='ZhangSan';
explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user ref idx_name_age idx_name_age 123 const 1 Using where; Using index
此时我们看到仍然使用了idx_name_age索引,而key_len为123,说明只用到了name,而没有用到age。
另外我们也可以推断出,如果key_len为128,那么则既使用到了name,又使用到了age。
根据最左原则,我们可以推测出,如果我们只想使用age,但不使用name,是不会走索引的。
select id,name,age from user where age=13;
explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user index idx_name_age 128 10 Using where; Using index
等等,这是什么情况?possible_keys为null,但是key使用到了索引,并且key_len为128,说明name和age都使用到了。但是ref为10,因为我们的表里只有10条记录,
这种情况是因为我们需要的字段在index(name, age)上都有,MySQL引擎认为对index(name, age)进行全部数据遍历比扫描全表的开销低,所以对索引进行了全索引的扫描。
我们只要换成*或者加上email字段explain就变成了:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user ALL 10 Using where
table为all,进行了全表扫描。
所以要正确使用联合索引,必须遵循最左原则。
单列索引和联合索引的选择
index(name, age)的情况下,我们不能跳过name直接使用age,但如果必须要直接使用age,就必须对age创建单列索引。
那么我们可不可以将index(name, age)拆分成index(name)和index(age)呢?
能是能,但是我们需要注意业务情况。
拆分后对于SQL:
select id,name,age from user where name='ZhangSan' and age=12;
就只能走1个索引:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user ref index_name,index_age index_name 123 const 1 Using index condition; Using where
可以看到possible_keys有2个,分别是index_name,index_age,但实际使用到的是index_name。
这是MySQL自己的选择,与数据内容有一定的关系,因为根据数据name字段上的区分度大于age,所以使用了index_name。
如果我们将数据改一下,多几个ZhangSan,让age=12只有1个:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user ref index_name,index_age index_age 5 const 1 Using where
此时MySQL又选择了使用index_age。
单列索引有个缺点,那就是一个SQL只能使用1个B+Tree,如果我们的数据name、age每个单列的区分度,但联合起来的区分度高,比如很多人叫张三,很多人12岁,但是叫张三并且12岁的人只有1个,这种情况我们使用单列索引就不能达到很好的效果。
此时就必须根据具体情况,创建index(name, age)或者index(age, name)的联合索引。
like模糊查询
很多时候我们会对name进行模糊查询。比如查询一个名字中有'a'的人:
select * from user where name like '%a%';
explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user ALL 10 Using where
此时是不能使用索引的,原因也很简单,因为我们需要找中间任意位置有'a'的值,不能使用排序好的索引,即使使用到了索引,也会扫描全部索引的。
但是如果我们改成查询名字以'Z'开头的人:
select * from user where name like 'Z%';
explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user range index_name_age index_name_age 123 2 Using index condition
此时type为range,说明使用了范围查询,key为index_name_age说明使用到了index(name, age)这个索引,key_len为123说明只使用了name字段上的索引。
也就是说'x%'这种形式是可以走索引的,原因也很简单,在name的B+Tree上找到以x开头的索引值,然后向后遍历链表,直到name值不以x开头即可停止。
索引下推
在上面的情况中我们还会收到一种需求,查询名字以'Z'开头,并且年龄为12岁的人:
select * from user where name like 'Z%' and age=12;
在这种情况中,可能就会用到索引下推来提高查询性能。
首先需要查看你的MySQL版本select VERSION()
,如果是5.6以上则支持索引下推。
其实5.6出来已经很久了,基本不是特别老的项目都是使用的5.6以上的MySQL。
在5.6之前,也就是没有索引下推时,根据'Z%'找到2条结果,于是拿着这2条结果回表查询age=12,满足条件则返回。
而使用索引下推后,在匹配到'Z%'的2条结果后,会继续在索引中判断age是否为12。
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user range index_name_age index_name_age 128 2 Using index condition
这一点我们从索引的key_len可以看出来。
不要在索引字段上做运算或函数操作。
以1年后年龄为12岁的需求为例,sql中会写age + 1 = 12,虽然我们对age做了索引,但是没有对age + 1做索引,MySQL优化器还没有那么智能,我们必须要对等式进行处理,写成age = 12 - 1,即age = 11。
如果必须要使用order by,尽量对order by的列创建索引。
因为索引已经是有序了的,并且所有叶子节点都使用双链表连接,遍历很容易。
而不使用索引,就需要数据库对查出来的数据再进行一次排序,就会消耗性能,这点从Extra字段可以看出:Using filesort
。
谨慎对待网上的各种"SQL优化方案"
我在写这边博客的时候也查询了大家的一些方案,但是根据我自己实测,网上很多的“方案”其实并不正确。
这与MySQL的版本也有一定的关系,毕竟MySQL团队也在让MySQL变得更好。
另外也与网上各种博客都是抄来抄去有关。
所以我认为不要将网上别人总结的各种方案奉为瑰宝,一定要自己理解原理,并且根据explain自己去实验才能得到最准确的结论。
另外还有一些SQL优化方案,但是与B+Tree关系不大,就不再列出了。