一关于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的顺序写入要高 |
|
|
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=56908DEFAULT 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=56908DEFAULT CHARSET=utf8
观察test1和test2的物理空间大小
12KB test1.frm
13332KB test1.ibd
12KB test2.frm
13332KB 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的统计结果表，这种统计结果一般数据较多，但主要是读 |
如何用一张图表示主键索引和二级索引的关系？请见下图