• explain key_len


    通常在优化SQL查询的时候,我们都会使用explain分析SQL执行计划,通常来说当用到组合索引的时候我们如何判断索引完全用上呢?当然高手看看表结构及SQL语句就知道到底用到了几个字段,对于不熟悉的同学呢?我们还是可以看看key_len的长度,当然这个计算还是有点复杂的,不过在你看过我这篇博客以后,相信你肯定会计算的,这难不倒聪明的你。

    废话不多说了,我们直接上例子。表结构如下。^_^

    复制代码
    mysql [localhost] {msandbox} (yayun) > show create table t1G
    *************************** 1. row ***************************
           Table: t1
    Create Table: CREATE TABLE `t1` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` char(20) NOT NULL DEFAULT '',
      `name1` char(20) DEFAULT NULL,
      `name3` varchar(20) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`),
      KEY `name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    
    mysql [localhost] {msandbox} (yayun) > 
    复制代码

    上面的表结构非常简单,有个主键索引,也就是id字段,还有一个辅助索引,也就是name字段,下面我们执行一条SQL,并分析一下执行计划,看看到底key_len如何计算的。
    表中就3条记录:

    复制代码
    mysql [localhost] {msandbox} (yayun) > select * from t1;
    +----+-------+-------+-----------+
    | id | name  | name1 | name3     |
    +----+-------+-------+-----------+
    |  1 | atlas | yayun | dengyayun |
    |  2 | alex  | talex | jalex     |
    |  3 | je    | jetom | tomje     |
    +----+-------+-------+-----------+
    3 rows in set (0.00 sec)
    
    mysql [localhost] {msandbox} (yayun) > 
    复制代码

    下面进行explain进行查看key_len的长度(这里只讲解key_len的计算,其他选项的意思请参看我前面的博客)

    复制代码
    mysql [localhost] {msandbox} (yayun) > explain select * from t1 where name='atlas';
    +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
    +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
    |  1 | SIMPLE      | t1    | ref  | name          | name | 60      | const |    1 | Using index condition |
    +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
    1 row in set (0.03 sec)
    
    mysql [localhost] {msandbox} (yayun) > 
    复制代码

    可以看到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)

    复制代码
    mysql [localhost] {msandbox} (yayun) > alter table t1 drop key name;
    Query OK, 0 rows affected (0.15 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql [localhost] {msandbox} (yayun) > alter table t1 add key idx_key_name_name1 (name,name1);
    Query OK, 0 rows affected (0.29 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql [localhost] {msandbox} (yayun) > 
    复制代码

    我们再来进行一条查询:

    复制代码
    mysql [localhost] {msandbox} (yayun) > explain select * from t1 where name='atlas';
    +----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-----------------------+
    | id | select_type | table | type | possible_keys      | key                | key_len | ref   | rows | Extra                 |
    +----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-----------------------+
    |  1 | SIMPLE      | t1    | ref  | idx_key_name_name1 | idx_key_name_name1 | 60      | const |    1 | Using index condition |
    +----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-----------------------+
    1 row in set (0.00 sec)
    
    mysql [localhost] {msandbox} (yayun) > explain select * from t1 where name='atlas' and name1='yayun';
    +----+-------------+-------+------+--------------------+--------------------+---------+-------------+------+-----------------------+
    | id | select_type | table | type | possible_keys      | key                | key_len | ref         | rows | Extra                 |
    +----+-------------+-------+------+--------------------+--------------------+---------+-------------+------+-----------------------+
    |  1 | SIMPLE      | t1    | ref  | idx_key_name_name1 | idx_key_name_name1 | 121     | const,const |    1 | Using index condition |
    +----+-------------+-------+------+--------------------+--------------------+---------+-------------+------+-----------------------+
    1 row in set (0.04 sec)
    
    mysql [localhost] {msandbox} (yayun) > 
    复制代码

    看到第一条查询和第二条的查询的执行计划有什么不同了么?没错,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个字段的索引完全用上了。

    下面我们再继续看看其他的情况,给表添加一个字段,并添加一个联合索引,我们进行一个范围的查询。

    mysql [localhost] {msandbox} (yayun) > alter table t1 add add_time timestamp;
    Query OK, 0 rows affected (1.44 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    mysql [localhost] {msandbox} (yayun) > alter table t1 add key idx_key_add_time_name3 (add_time,name3);        
    Query OK, 0 rows affected (0.19 sec)
    Records: 0  Duplicates: 0  Warnings: 0

    现在的表结构这样了。

    复制代码
    mysql [localhost] {msandbox} (yayun) > show create table t1G
    *************************** 1. row ***************************
           Table: t1
    Create Table: CREATE TABLE `t1` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` char(20) NOT NULL DEFAULT '',
      `name1` char(20) DEFAULT NULL,
      `name3` varchar(20) NOT NULL DEFAULT '',
      `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      KEY `idx_key_name_name1` (`name`,`name1`),
      KEY `idx_key_add_time_name3` (`add_time`,`name3`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
    1 row in set (0.01 sec)
    
    mysql [localhost] {msandbox} (yayun) > 
    复制代码

    看SQL,废话不多说。

    复制代码
    mysql [localhost] {msandbox} (yayun) > explain select * from t1 where add_time >='2014-09-10 02:36:46' and add_time <='2014-09-11 02:36:46' group by name3 order by null;
    +----+-------------+-------+-------+------------------------+------------------------+---------+------+------+----------------------------------------+
    | id | select_type | table | type  | possible_keys          | key                    | key_len | ref  | rows | Extra                                  |
    +----+-------------+-------+-------+------------------------+------------------------+---------+------+------+----------------------------------------+
    |  1 | SIMPLE      | t1    | range | idx_key_add_time_name3 | idx_key_add_time_name3 | 4       | NULL |    2 | Using index condition; Using temporary |
    +----+-------------+-------+-------+------------------------+------------------------+---------+------+------+----------------------------------------+
    1 row in set (0.00 sec)
    
    mysql [localhost] {msandbox} (yayun) > 
    复制代码

    可以看见用到了我创建的联合索引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字段组成的一个联合索引。

    mysql [localhost] {msandbox} (yayun) > alter table t1 add key idx_key_name1_name3 (name1,name3);
    Query OK, 0 rows affected (0.27 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql [localhost] {msandbox} (yayun) > 

    SQL如下:

    复制代码
    mysql [localhost] {msandbox} (yayun) > explain select * from t1 where name1='yayun' and name3='dengyayun';
    +----+-------------+-------+------+---------------------+---------------------+---------+-------------+------+-----------------------+
    | id | select_type | table | type | possible_keys       | key                 | key_len | ref         | rows | Extra                 |
    +----+-------------+-------+------+---------------------+---------------------+---------+-------------+------+-----------------------+
    |  1 | SIMPLE      | t1    | ref  | idx_key_name1_name3 | idx_key_name1_name3 | 123     | const,const |    1 | Using index condition |
    +----+-------------+-------+------+---------------------+---------------------+---------+-------------+------+-----------------------+
    1 row in set (0.00 sec)
    
    mysql [localhost] {msandbox} (yayun) > 
    复制代码

    可以看见key_len的长度是123。那么索引完全用到了么?当然有点索引常识都知道完全用到了。我这里只是为了告诉大家key_len到底如何计算的。
    `name3` varchar(20) NOT NULL DEFAULT ''

    `name1` char(20) DEFAULT NULL,

    上面是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。

    写到这里相信大家都有一个基本认识了吧。好了,多的不说了,公式放出来给大家,自己套用公式,多做几次测试就明白鸟。

    key_len的长度计算公式:
    varchr(10)变长字段且允许NULL    =  10 * ( character set:utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
    varchr(10)变长字段且不允许NULL =  10 *( character set:utf8=3,gbk=2,latin1=1)+2(变长字段)

    char(10)固定字段且允许NULL        =  10 * ( character set:utf8=3,gbk=2,latin1=1)+1(NULL)
    char(10)固定字段且不允许NULL        =  10 * ( character set:utf8=3,gbk=2,latin1=1)

    作者:Atlas

    出处:Atlas的博客 http://www.cnblogs.com/gomysql

    作者:Atlas

    出处:Atlas的博客 http://www.cnblogs.com/gomysql

    您的支持是对博主最大的鼓励,感谢您的认真阅读。本文版权归作者所有,欢迎转载,但请保留该声明。如果您需要技术支持,本人亦提供有偿服务。

  • 相关阅读:
    Xcode 4.1~4.6 + iOS 5、iOS 6免证书(iDP)开发+真机调试+生成IPA全攻略
    Java程序员快速入门Go语言
    企业站常用的点击后弹出下拉菜单导航
    企业站常用漂亮横向导航菜单
    点击弹出弹性下拉菜单效果
    很酷的伸缩导航菜单效果,可自定义样式和菜单项。
    导航条点击按钮切换效果
    不错的二级导航菜单特效
    商城常用产品分类导航条
    css实现鼠标经过导航文字偏位效果
  • 原文地址:https://www.cnblogs.com/cjjjj/p/12723644.html
Copyright © 2020-2023  润新知