一 关于t1表和testtb的索引设计

二 把主键放到二级索引的后面，会否占据更多的物理空间？

三 InnoDB的主键该如何选择，业务ID和自增ID做主键有何区别？


一 关于t1表和testtb的索引设计

1. CREATE TABLE
2.   `id` int(11) NOT NULL
3.   `a` int(11) DEFAULT NULL,  
4.   `b` int(11) DEFAULT NULL,  
5.   `c` int(11) DEFAULT NULL,  
6.   PRIMARY KEY
7.   KEY
8. ) ENGINE=InnoDB DEFAULT
9. CREATE TABLE
10.   `id` int(11) NOT NULL
11.   `a` int(11) DEFAULT NULL,  
12.   `b` int(11) DEFAULT NULL,  
13.   `c` int(11) DEFAULT NULL,  
14.   PRIMARY KEY
15.   KEY
16. ) ENGINE=InnoDB DEFAULT

t1表和testtb表的差别只在于索引i2 和i3的索引字段，

前者和后者的区别是更新记录的时候，t1表由于a字段是随机的，而id的顺序自增的.

i2的分裂成本要比i3的成本高，在高并发更新的情况下，testtb表的性能会比t1表的高。

索引

插入性能对比

实际业务场景对比

KEY

a字段是随机写入，i2索引的b-tree分裂成本相对i3的顺序写入要高

对于select b,c from table where a=10;的查询，可以直接用到i2索引

KEY

id是顺序自增,在a字段插入数据的时候,b-tree的分裂成本相对i2要低

对于select b,c where a=10;的查询，用不到i2索引

实际业务场景对比，可能会有同学说，那select b,c where id=? and a=?将会怎么运行呢？答案是查询优化器会直接选择主键索引,我们可以看一个简单的例子.

结论:

1. 在大部分的业务场景下，业务的sql一般是只用到where a=?来进行查询，如果是组合条件，比如where id=? and a=?这种情况，而没有where a=?的查询条件，我一般直接就让sql走主键索引，而不会再额外建一个(id,a)的索引。

2. 如果业务的sql是只有where a=?的查询条件，建了(id,a)是会让SQL用不到这个索引的，只能单独建一个(a)索引。

二 把主键放到二级索引的后面？

彭爷的博文的最后，提到这个语句SELECT * FROM t WHERE d=x1 AND b=x2 ORDER BY a;以及建议所有的DBA建索引的时候，都在业务要求的索引字段后面补上主键字段

建了两张字段结构一样，记录一样，二级索引不一样的两个表

CREATE TABLE `test1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
 KEY `i4` (`a`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56908 DEFAULT CHARSET=utf8;
CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
 KEY `i4_no_id` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=56908 DEFAULT CHARSET=utf8

观察test1和test2的物理空间大小

12KB test1.frm
13332 KB test1.ibd
12KB test2.frm
13332 KB test2.ibd

物理空间不会因为把id放在二级索引的后面而变大，那以后建表的时候，可以考虑直接把ID加进到二级索引的后面

三 InnoDB的主键该如何选择，业务ID和自增ID做主键有何区别？

这个topic，我把9.23即将在北京 阿里技术嘉年华IData论坛要分享的内容直接贴上来

a表

b表

CREATE TABLE `a` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`message_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`msg` varchar(1024) DEFAULT NULL,
`gmt_create` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`,`message_id`),
KEY `idx_gmt_create` (`gmt_create`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;


CREATE TABLE `b` (
  `user_id` int(11) NOT NULL,
  `message_id` int(11) NOT NULL,
  `msg` varchar(1024) DEFAULT NULL,
  `gmt_create` datetime NOT NULL,
 PRIMARY KEY (`user_id`,`message_id`),
KEY `idx_gmt_create` (`gmt_create`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;


物理空间

优点

缺点

适用场景

a表(500万记录)

509M

主键ID自增,在写入数据的时候,Btree分裂成本低,写性能高

1. 物理空间相对较多

如果根据user_id来查记录,需要走两次IO

写操作较多的场景

b表(500万记录)

361M

1.物理空间相对减少

2.根据user_id查数据,直接走主键拿到数据，无需回表

(user_id,message_id)为随机写入,Btree分裂成本高,写性能低

写少读多的场景,例如从hadoop回流到MySQL的统计结果表，这种统计结果一般数据较多，但主要是读

如何用一张图表示主键索引和二级索引的关系？请见下图