innodb索引设计小结

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

二把主键放到二级索引的后面&＃xff0c;会否占据更多的物理空间&＃xff1f;

三 InnoDB的主键该如何选择&＃xff0c;业务ID和自增ID做主键有何区别&＃xff1f;

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

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

t1表和testtb表的差别只在于索引i2 和i3的索引字段&＃xff0c;

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

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

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

结论:

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

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

二把主键放到二级索引的后面&＃xff1f;

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

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

CREATE TABLE &＃96;test1&＃96; (

 &＃96;id&＃96; int(11) NOT NULL AUTO_INCREMENT,

  &＃96;a&＃96;int(11) DEFAULT NULL,

  &＃96;b&＃96;int(11) DEFAULT NULL,

  &＃96;c&＃96;int(11) DEFAULT NULL,

 PRIMARY KEY (&＃96;id&＃96;),

 KEY &＃96;i4&＃96; (&＃96;a&＃96;,&＃96;id&＃96;)

) ENGINE&＃61;InnoDB AUTO_INCREMENT&＃61;56908DEFAULT CHARSET&＃61;utf8;

CREATE TABLE &＃96;test2&＃96; (

 &＃96;id&＃96; int(11) NOT NULL AUTO_INCREMENT,

  &＃96;a&＃96;int(11) DEFAULT NULL,

  &＃96;b&＃96;int(11) DEFAULT NULL,

  &＃96;c&＃96;int(11) DEFAULT NULL,

 PRIMARY KEY (&＃96;id&＃96;),

 KEY &＃96;i4_no_id&＃96; (&＃96;a&＃96;)

) ENGINE&＃61;InnoDB AUTO_INCREMENT&＃61;56908DEFAULT CHARSET&＃61;utf8

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

12KB         test1.frm

13332KB  test1.ibd

12KB         test2.frm

13332KB  test2.ibd

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

三 InnoDB的主键该如何选择&＃xff0c;业务ID和自增ID做主键有何区别&＃xff1f;

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

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