当我们试图优化SQL查询性能时,一项关键任务是确保数据库引擎充分利用了索引。虽然对于一些经验丰富的开发人员来说,仅凭查看表结构和SQL语句就能够判断出索引的使用情况,但对于不太熟悉这方面知识的人来说,这可能会有些困难。
幸运的是,我们有一项强大的工具可以帮助我们进行这项工作,那就是EXPLAIN语句。通过执行EXPLAIN语句,我们可以获取数据库引擎执行查询的详细计划,其中包括使用的索引信息。这就让我们能够更清晰地了解数据库引擎在执行查询时所做的决策。
其中一个关键指标是key_len,它表示索引字段的长度。通过分析key_len,我们可以判断数据库引擎是否充分利用了索引。通常情况下,如果key_len的值等于索引中使用的所有字段的总长度,那么就表明索引被完全使用了。
尽管计算key_len可能有些复杂,但我可以分享一些技巧和方法,帮助您更轻松地理解和分析这一指标。通过这些方法,您将能够更加自信地优化您的SQL查询,提升数据库性能。
上面的表结构非常简单,有个主键索引,也就是id字段,还有一个辅助索引,也就是name字段,下面我们执行一条SQL,并分析一下执行计划,看看到底key_len如何计算的。表中就3条记录:
下面进行explain进行查看key_len的长度(这里只讲解key_len的计算)
可以看到key_len的长度是60,那么这个60是如何计算出来的。当然如果是单列索引我们不用去计算,因为没有意义,如果是组合索引,那么知道这里的长度就是非常有意义的,我们先简单来看看这个单列索引的key_len等于60是如何计算的。
还记得前面我的表结构里面name字段的定义么? `name` char(20) NOT NULL DEFAULT '',我定义了char(20),且非空。好,现在我们来计算一下,首先我的表用的utf8字符集,那么大家都知道utf8字符集占用3个字节,那么我又定义char(20),知道结果了么?聪明的你一定知道了。
key_len=20*3=60,计算简单吧,这个情况确实简单,还有复杂的情况。
我们下面继续看下一条SQL,我们把name这个字段的索引去掉,添加一个联合索引,key(name,name1)
我们再来进行一条查询:
看到第一条查询和第二条的查询的执行计划有什么不同了么?没错,key_len及ref列不一样了。why?以及为什么第二条SQL语句的key_len为121,这个是如何计算的?嘿嘿,如果还用上面的计算方法你肯定计算不出来的。让我来告诉你。还记得name1字段的定义么?
`name1` char(20) DEFAULT NULL, 可以发现name1字段的定义为DEFAULT NULL,其他没变化。所以MySQL需要1个字节来标识NULL,所以第二条SQL的key_len=20 * 3 + (20 * 3 +1)=121,通过计算,我们知道2个字段的索引完全用上了。
下面我们再继续看看其他的情况,给表添加一个字段,并添加一个联合索引,我们进行一个范围的查询。
现在的表结构这样了。
看SQL
可以看见用到了我创建的联合索引idx_key_add_time_name3,但是真的完全用到了么。其实一眼就知道没有用到,因为前面是一个范围查询,后面字段的索引就用不到,如果我这里不order by null,还会看到Using filesort。但是我还是想说说key_len是如何计算的,大家都很清楚timestamp占用4字节吧。那么答案显而易见,看见key_len是4,说明只用到了联合索引idx_key_add_time_name3中的add_time字段。
我们再来看一种情况,是char字段和varchar字段组成的一个联合索引。
SQL如下:
可以看见key_len的长度是123。那么索引完全用到了么?当然有点索引常识都知道完全用到了。我这里只是为了告诉大家key_len到底如何计算的。
上面是2个字段的定义,1个允许NULL,一个NOT NULL,一个char,一个varchar
所以key_len=(20*3 + 1)+(20 * 3 + 2)= 123
由此来判断这个组合索引已经完全使用。相信有同学会问了,+1是干嘛,+2是干嘛。这就告诉大家,+1是因为MySQL需要1个字节标识NULL,+2是因为name3字段为varchar,是变长字段需要+2。
写到这里相信大家都有一个基本认识了吧。好了,多的不说了,公式放出来给大家,自己套用公式,多做几次测试就明白了。
扫码关注 了解更多