關於MySQL InnoDB表的二級索引是否加入主鍵列的問題解釋

標籤：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，只用到了i1這列（int類型key長度是4byte）沒有擴充主鍵。 ref：只有一個const：表明最佳化器只用到了i1這列。 using where：已經回表了。

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

下面我添加索引：`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 也說明用到了主鍵。

二. 同時我在MySQL 5.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) 

xxx 5.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

本文出自 “My DBA life” 部落格，請務必保留此出處http://huanghualiang.blog.51cto.com/6782683/1607496

關於MySQL InnoDB表的二級索引是否加入主鍵列的問題解釋