基于B+Tree原理总结SQL优化常见法则 - ZhangTory's NoteBlog - 张耀誉的笔记博客

基于B+Tree原理总结SQL优化常见法则

对于普通程序员来说最大的工作量就在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. 数据库需要把 * 翻译成字段名。
  2. 因为数据库是通过网络连接,所以多余字段的数据会增加网络传输的时间。
  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关系不大,就不再列出了。

添加新评论

电子邮件地址不会被公开,评论内容可能需要管理员审核后显示。