关于InnoDB表如何设计索引的小结

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

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

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

看到了@淘宝丁奇的《关于InnoDB的索引大小》和@plinux 的《InnoDB一定会在索引中加上主键吗》之前在阿里DBA内部分享过一个InnoDB表该如何建主键索引的PPT，借这个机会再整理和思考一次。

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

1. CREATE TABLE `t1` (  

2.   `id` int(11) NOT NULL AUTO_INCREMENT,  

3.   `a` int(11) DEFAULT NULL,  

4.   `b` int(11) DEFAULT NULL,  

5.   `c` int(11) DEFAULT NULL,  

6.   PRIMARY KEY (`id`),  

7.   KEY `i2` (`a`,`id`)  

8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;    

9. CREATE TABLE `testtb` (  

10.   `id` int(11) NOT NULL AUTO_INCREMENT,  

11.   `a` int(11) DEFAULT NULL,  

12.   `b` int(11) DEFAULT NULL,  

13.   `c` int(11) DEFAULT NULL,  

14.   PRIMARY KEY (`id`),  

15.   KEY `i3` (`id`,`a`)  

16. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  

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

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

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

索引	插入性能对比	实际业务场景对比
KEY `i2` (`a`,`id`)	a字段是随机写入，i2索引的b-tree分裂成本相对i3的顺序写入要高	对于select b,c from table where a=10;的查询，可以直接用到i2索引
KEY `i3` (`id`,`a`)	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的统计结果表，这种统计结果一般数据较多，但主要是读

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