索引使用之KEY_LEN

PS:如果你对mysql很熟，只是不知道key_len是什么。。。可以直接拉到 key_len计算公式 一节。

索引

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。MYSQL的索引使用B+树结构存储。具体就不要再过多描述了。。。看到标题还能进来的应该都懂。

语句用到的索引

数据表建表语句
mysql> show create table ts\G
*************************** 1. row ***************************
       Table: ts
Create Table: CREATE TABLE `ts` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  KEY `id_name_age_index` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

复制

两种查询方式用到的索引是：id_name_age_index

mysql> explain select * from ts where id=1 and name = "小红"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ts
   partitions: NULL
         type: ref
possible_keys: id_name_age_index
          key: id_name_age_index
      key_len: 37
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.01 sec)

mysql>
mysql> explain select * from ts where id>1 and name = "小红" and age >20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ts
   partitions: NULL
         type: range
possible_keys: id_name_age_index
          key: id_name_age_index
      key_len: 4
          ref: NULL
         rows: 6
     filtered: 14.29
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

复制

题外话：

mysql sql 中如果使用了 not in ， not exists ， （<> 不等于 ！=） 这些不走索引

< 小于 > 大于 <= >= 这个根据实际查询数据来判断，如果全盘扫描速度比索引速度要快则不走索引 。

key_len计算公式

首先我们来看看下面几个内容

数据类型本身占字节长度

int(11)  4
tinyint(4)  1
timestamp  4
datetime  8

复制

索引字段的附加信息

定长类型：char\int\datetime等，需要有是否为空的标记，占用1个字节。如果字段定义为非空（not null）时，不占用字节。
变长类型：varchar等，需要是否为空的标记和长度信息，共占用2~3个字节。

复制

char和varchar是日常使用最多的字符类型。char（N）用于保存固定长度的字符串，长度最大为255，比指定长度大的值将被截短，而比指定长度小的值将会用空格进行填补。

varchar（N）用于保存可以变长的字符串，长度最大为65535，只存储字符串实际实际需要的长度（它会增加一个额外字节来存储字符串本身的长度），varchar使用额外的1~2字节来存储值的的长度，如果列的最大长度小于或者等于255，则用1字节，否则用2字节。

不同字符集字节数不一

gbk编码为：1个字符2个字节
utf8编码为：1个字符3个字节
utf8mb4编码为：1个字符4个字节

复制

看完上面的解释不知道你是否已经知道key_len如何计算呢

mysql> explain select * from ts where id=1 and name = "小红"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ts
   partitions: NULL
         type: ref
possible_keys: id_name_age_index
          key: id_name_age_index
      key_len: 37
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.01 sec)

复制

拿上面的这个查询语句为例：

可以看到查询语句用到了联合索引，key_len长度是37，id和name字段都用了索引，数据表编码utf8编码为：1个字符3个字节
，现在我们来看看怎么算出来的37

1. 首先id int(10) not null 就是 4个字节 +1字节 = 5字节
2. 其次 name varchar(10) 可以为空（为空多占用一个字节） 就是 10字符*3字节 + 1字节为空 +1字节值长度= 32字节
3. 最后得出长度是 5+32 = 37

下面我们再看一下另外一个语句：

mysql> explain select * from ts where id>1 and name = "小红" and age >20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ts
   partitions: NULL
         type: range
possible_keys: id_name_age_index
          key: id_name_age_index
      key_len: 4
          ref: NULL
         rows: 6
     filtered: 14.29
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

复制

key_len 长度是4，一般来讲>
是否使用索引需要看情况，mysql自己会进行优化查询，从explain的结果看来 可以很明显的看出联合索引中只用到id字段索引，所以ref 的值也是NULL

key_len作用

主要是为了在联合索引中查看本次查询是否用到了对应的索引，用到了索引中的哪些字段。

题外话-索引长度限制(内容来源网络)

关于索引长度的限制，最主要的因素就是存储引擎和字符集。字符集的影响在于，不同的字符集，单个字符包含的最大字节数有所不同。比如 utf8 字符集，一个字符最多包含 3 个字节。而 utf8mb4 一个字符最多包含 4 个字节。

对于 InnoDB 引擎来说：

1. Mysql 版本 <=5.7.6

如果是单字段索引，则字段长度不应超过 767 字节。如果是联合索引，则每个字段长度都不应超过 767 字节，且所有字段长度合计不应超过 3072 字节。

这种情况下，常见字符集和引擎组合后的字符长度限制如下：

1. Mysql 版本 >=5.7.7

在该版本情况下，由于 InnoDB 引擎的 innodb_large_prefix 等选项默认值改变，单字段索引长度限制增大。

如果是单字段索引，则字段长度不应超过 3072 字节。如果是联合索引，则每个字段长度都不应超过 3072 字节，且所有字段长度合计不应超过 3072 字节。

这种情况下，常见字符集和引擎组合后的字符长度限制如下：

对于 MyISAN 引擎来说：如果 Mysql 版本 <=5.5.4 则其默认存储引擎为 MyISAN。

如果是单字段索引，则字段长度不应超过 1000 字节。如果是联合索引，则每个字段长度都不应超过 1000 字节，且所有字段长度合计不应超过 1000 字节。

这种情况下，常见字符集和引擎组合后的字符长度限制如下：

二、解决方法 

(1)修改存储引擎

(2)修改字符集

(3)修改索引字段的字符长度 在 Mysql 版本 <=5.7.6 且字符集为 utf8mb4 的情况下，设置需要添加索引的字段长度为 191。

(4)在 Mysql<=5.7.6 的情况，修改 InnoDB 选项，将单字段索引长度限制由 767 字节（bytes）提高到 3072 字节。

先查看当前默认选项值：

SHOW VARIABLES LIKE 'innodb_large_prefix';
SHOW VARIABLES LIKE 'innodb_file_per_table';
SHOW VARIABLES LIKE 'innodb_file_format';

复制

确认选项值是否是：

innodb_large_prefix=ON
innodb_file_per_table=ON
innodb_file_format=Barracuda

复制

如果不是，进行相应的修改：

SET GLOBAL innodb_large_prefix = 1;
SET GLOBAL innodb_file_per_table = 1;
SET GLOBAL innodb_file_format = barracuda;

复制

上面两项的值设置为 1 而不是 ON，是因为在 Mysql5.6 之前，布尔值的变量还无法识别 ON 或者 OFF，只能是数字 1 或者 0。并且，在创建新表时还要指定 ROW_FORMAT=DYNAMIC 或 ROW_FORMAT=COMPRESSED。如：

CREATE TABLE `test` (
`a` varchar(767),
`b` varchar(1),
`c` varchar(200),
`d` varchar(168),
`e` varchar(5),
KEY `a_index` (`a`,`b`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

复制

或修改当前表：

ALTER TABLE [...], ROW_FORMAT=DYNAMIC;

(5)在创建索引时限制字段上的前缀索引长度：

CREATE TABLE `test` (
`a` varchar(767),
`b` varchar(1),
`c` varchar(200),
`d` varchar(168),
`e` varchar(5),
KEY `a_index` (`a`(191),`b`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
复制