mysql二级索引加主键_关于MySQL InnoDB表的二级索引是否加入主键列的问题解释

关于MySQL InnoDB表的二级索引是否加入主键，总结如下：

1对于MySQL InnoDB表的二级索引是否加入主键，官方也有明确的说明，建议线上MySQL的二级索引创建时强制加入主键所有的列，可以做到所有的MySQL 版本统一。

2.MySQL 5.6.9之前，InnoDB引擎层是会对二级索引做自动扩展，但是优化器不能识别出扩展的主键。

3.MySQL 5.6.9开始InnoDB引擎层是会对二级索引做自动扩展，优化器能识别出扩展的主键。

4.索引的大小一样，二级索引有没有加入主键列，在InnoDB引擎层二级索引都会自动扩展主键，这个跟版本无关。

5.有无加入主键列，二级索引的组织结构和物理大小是一样，因为在存储引擎层面组织结构是一样的。

6.在优化器层面，5.6.9之前是无法识别自动扩展的主键列，从5.6.9开始优化器的开关 use_index_extensions=on是可以识别扩展的主键列，所以在二级索引加入主键列有有利的。这也可以做到与版本无关，做到所有MySQL版本统一。

总结：加主键列，有利无害。

*下面是我的演示实例：

一.下面是在MySQL 5.5.36-log：

xxx 5.5.36-log test 11:33:54>CREATE TABLE t1 (

-> i1 INT NOT NULL DEFAULT 0,

-> i2 INT NOT NULL DEFAULT 0,

-> d DATE DEFAULT NULL,

-> PRIMARY KEY (i1, i2),

-> INDEX k_d (d)

-> ) ENGINE = InnoDB;

Query OK, 0 rows affected (0.07 sec)

插入了25行数据后：

xxxx 5.5.36-log test 11:40:01>EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t1

type: ref

possible_keys: PRIMARY,k_d

key: k_d

key_len: 4

ref: const

rows:5

Extra: Using where; Using index

分析：key_len 是4，只用到了d这列(date类型key长度是3byte，key_len=3+1byte长度)没有扩展主键。 ref：只有一个const：表明优化器只用到了i1这列。 using where;using index：已经回表了。

************************************************************************************************************************************************

下面我添加索引：`k_d_2`(d,i1,i2)

alter table t1 add key`k_d_2`(d,i1,i2);

xxx 5.5.36-log test 11:36:11>EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t1

type: ref

possible_keys: PRIMARY,k_d,k_d_2

key: k_d_2

key_len: 8

ref: const,const

rows: 1

Extra: Using where; Using index

分析：key: k_d_2和key_len 是8，说明扩展主键。 ref：有2个const：表明优化器用到了i1这列。rows:1 也说明用到了主键。

二. 同时我在MySQL5.6.16-log也做了创建同样的表：

lxxx  5.6.16-log test 08:20:46>show variables like '%optimizer_switch%';

firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on....

use_index_extensions 已经打开。

xxx 5.6.16-log test 08:20:46>CREATE TABLE t1 (

->   i1 INT NOT NULL DEFAULT 0,

->   i2 INT NOT NULL DEFAULT 0,

->   d DATE DEFAULT NULL,

->   PRIMARY KEY (i1, i2),

->   INDEX k_d (d)

-> ) ENGINE = InnoDB;

Query OK, 0 rows affected (0.00 sec)

xxx 5.6.16-log test 08:21:04>EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t1

type: ref

possible_keys: PRIMARY,k_d

key: k_d

key_len: 8

ref: const,const

rows:1

Extra: Using index

分析：key: k_d_2和key_len 是8，说明MySQL 自动对二级索引做了扩展主键。 ref：有2个const：表明优化器识别了扩展主键。

三.索引大小：

在二级索引后面加上主键列，存储空间不会增加。

下面是我的分析：

一.下面是MySQL 5.6.16：

CREATE TABLE `t1` (

`i1` int(11) NOT NULL DEFAULT '0',

`i2` int(11) NOT NULL DEFAULT '0',

`d` date DEFAULT NULL,

PRIMARY KEY (`i1`,`i2`),

KEY `k_d` (`d`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `tt1` (

`i1` int(11) NOT NULL DEFAULT '0',

`i2` int(11) NOT NULL DEFAULT '0',

`d` date DEFAULT NULL,

PRIMARY KEY (`i1`,`i2`),

KEY `k_d` (`d`,`i1`,`i2`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

通过储存过程对表插入数据：

call proc_insert(500000); 插入50w行数据：

下面是索引的大小，大小一样：

xxx test 03:38:36>SELECT index_length FROM information_schema.TABLES WHERE table_schema='test' and table_name='t1';

+--------------+

| index_length |

+--------------+

| 8929280 |

+--------------+

1 row in set (0.00 sec)

xxx 5.6.16-log test 03:43:42>SELECT index_length FROM information_schema.TABLES WHERE table_schema='test' and table_name='tt1';

+--------------+

| index_length |

+--------------+

| 8929280 |

+--------------+

1 row in set (0.01 sec)

数据文件大小，大小也是一样的：

-rw------- 1 mysql myinstall 36M 1月 23 15:38 t1.ibd

-rw------- 1 mysql myinstall 36M 1月 23 15:39 tt1.ibd

二.下面是MySQL 5.5.36：

表t1、tt1和上面的结构一致。

索引大小：

xxx 5.5.36-log (none) 03:48:05>SELECT index_length FROM information_schema.TABLES WHERE table_schema='test' and table_name='t1';

+--------------+

| index_length |

+--------------+

| 8929280 |

+--------------+

1 row in set (0.00 sec)

xxx5.5.36-log (none) 03:48:06>SELECT index_length FROM information_schema.TABLES WHERE table_schema='test' and table_name='tt1';

+--------------+

| index_length |

+--------------+

| 8929280 |

+--------------+

1 row in set (0.00 sec)

数据文件大小：也是一样

-rw-rw----. 1 mysql myinstall 36M 1月 23 15:39 tt1.ibd

-rw-rw----. 1 mysql myinstall 36M 1月 23 15:39 t1.ibd

引申：

关于key_len及create table的规范：

key_len:

1.对于定长数据类型(int、char(N)、date等)实际字段类型的字节数，如果字段不是not null，则还需1byte存储字段是否为空。

2.对于不定长数据类型(varchar(N)、datetime(mysql 5.6开始是变长)等)实际字段类型的字节数 + 2byte储存字段长度，如果字段不是not null，则还需1byte存储字段是否空。

所以在创建表的时候：

create table txxx(

id int ...

c1 varchar(30) not null default '0000'

)

也可以起到减少二级索引的长度。