关于InnoDB的索引大小

     这篇文章来自在一个啾啾群纠结的问题。

 

 

背景

         关于InnoDB内的索引大小。对于表

CREATE TABLE `testtb` (
  `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 `i1` (`a`),
  KEY `i2` (`a`,`id`),
  KEY `i3` (`id`,`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

 

由于InnoDB在存储索引的时候会自动取出重复的主键，源码分析见这里

先说几个结论

1)       Index i2 由于索引定义中已经包含pk  id，因此不会存两份，实际就是(a, id)

2)       Index i1 本身要包含主键id，因此也是(a, id)，与 i2 相同

3)       同理1,Index i3 里存的也只是(id, a)

 

异常

         按照上面的结论，以下操作能发现“异常”

 

 

 

 

    

CREATE TABLE `t1` (
  `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 `i2` (`a`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `testtb` (
  `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 `i3` (`id`,`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  

          接下来往这个两个表中各插入5w行记录，插入语句类似

 

 Foreach I in 1 to 5w
     {
       Set @v = rand() * 100;
       Insert into testtb(a,b,c) values(@v, @v, @v);
     }

 

         插入数据完成后，这个两个表索引大小是否相同？

 

         从show table status like ‘%t1%’; 和 show table status like ‘%t2%’;的index_length可以看出来，是不同的，t1大一些。

         存储的行数相同，数据也相同，为什么大小不一样？

 

原因说明

         主要在于插入顺序导致的分裂。在上面的例子中，由于a,b,c的值都是随机值，导致索引 (a, id)是随机的。 Id是递增的，所以(id, a)是递增的。

         随机插入索引和顺序递增插入索引之所以有差别，就在于随机插入会导致更多的Btree分裂。

 

这也就是为什么在某些场景下，我们建议在表比较大的应用中，用自增id替代unique key (并非唯一的原因，也不是固定的规范，需要具体分析)。

 

验证

有了上面的分析，要验证就比较简单了，把两个表清空(truncate)，

插入数据改为如下的语句

 

Foreach I in 1 to 5w
     {
       Set @v = i;
       Insert into testtb(a,b,c) values(@v, @v, @v);
     }

 

 

这样插入的每行都是(n,n,n,n), n为1到5w的递增。这样索引(a, id)也是顺序递增方式，与(id,a)一样紧凑，再看show table status能发现一样了。

 

有个工具

         分两个表验证比较麻烦。这个是之前写过的一个分析文件利用率的工具 ibd_used，可以看一个表上各个索引的大小和索引上page的利用率。

         用法

./ibd_used testtb.ibd 0 N > k

 

说明：第一个参数是要分析的ibd文件

              第二、三个参数是起始、结束page_no。  如果你要分析整个文件，N可以输入一个很大的数就行。

               结果中会输出每个page的利用率，因此比较多，记得重定向输出

 

         最后几行是整个索引的统计结果。

 

 

用文章开头的例子和随机值插入的case，得到的表中，执行

 

ibd_used data/test/testtb.ibd 0 99999999 > k
最后几行如下
index_id:1517 rate 1751652/1949696=0.898423
index_id:1518 rate 701314/1212416=0.578443
index_id:1519 rate 701314/1212416=0.578443
index_id:1520 rate 700828/770048=0.910109

 

 

 

 

 按顺序对应索引，可以看出，i1和i2的利用率一模一样，但跟i3 比起来就差多了。

 

grep -Po " index_id:\d+" k | sort | uniq -c                                    
    120 index_id:1517
     75 index_id:1518
     75 index_id:1519
     48 index_id:1520

 

 

    这个命令看每个索引占用多少page，与我们的结论一致：i3因为紧凑，所以占用更少的page。

评论

8 楼 7先生 2013-04-01  

请问ibd_used这个工具去哪下载？你给的链接好像已经取消分享了。

7 楼 zhoujy 2012-09-21  

丁林.tb 写道

zhoujy 写道

houjy@zhoujy:~$ ll ibd_used
-rwxr-xr-x 1 zhoujy zhoujy 11087 2012-09-20 15:38 ibd_used
zhoujy@zhoujy:~$ md5sum  ibd_used
b90f6a038ea162c102f5abccc332453c  ibd_used

http://pan.baidu.com/share/link?shareid=61749&uk=1191507911 这里下的。

这都对哦。。对了， 你是不是32位系统。。？

是的。原来不适合32位的系统啊，那我还到64位的试试。

6 楼 丁林.tb 2012-09-20  

zhoujy 写道

houjy@zhoujy:~$ ll ibd_used
-rwxr-xr-x 1 zhoujy zhoujy 11087 2012-09-20 15:38 ibd_used
zhoujy@zhoujy:~$ md5sum  ibd_used
b90f6a038ea162c102f5abccc332453c  ibd_used

http://pan.baidu.com/share/link?shareid=61749&uk=1191507911 这里下的。

这都对哦。。对了， 你是不是32位系统。。？

5 楼 zhoujy 2012-09-20  

houjy@zhoujy:~$ ll ibd_used
-rwxr-xr-x 1 zhoujy zhoujy 11087 2012-09-20 15:38 ibd_used
zhoujy@zhoujy:~$ md5sum  ibd_used
b90f6a038ea162c102f5abccc332453c  ibd_used

http://pan.baidu.com/share/link?shareid=61749&uk=1191507911 这里下的。

4 楼 丁林.tb 2012-09-20  

./ibd_used

zhoujy 写道

已经是可执行文件了，但是还是报这个问题。
root@zhoujy:/home/zhoujy# ./ibd_used  /var/lib/mysql/cms/cms_new_block.ibd 0 99999999  > /home/zhoujy/aaa.txt
-bash: ./ibd_used: 无法执行二进制文件

b90f6a038ea162c102f5abccc332453c  ibd_used md5没错吧？

ll ibd_used   输出什么

3 楼 zhoujy 2012-09-20  

已经是可执行文件了，但是还是报这个问题。
root@zhoujy:/home/zhoujy# ./ibd_used  /var/lib/mysql/cms/cms_new_block.ibd 0 99999999  > /home/zhoujy/aaa.txt
-bash: ./ibd_used: 无法执行二进制文件

2 楼 丁林.tb 2012-09-20  

zhoujy 写道

ibd_used 这个工具好像很好用，但是怎么执行报错：
-bash: ./ibd_used: 无法执行二进制文件 

chmod 755 ibd_used

1 楼 zhoujy 2012-09-20  

ibd_used 这个工具好像很好用，但是怎么执行报错：
-bash: ./ibd_used: 无法执行二进制文件