mysql源码主从搭建

3t4342

• 关于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